misc/db2html.pl

Code
Comments
Other
Rev Date Author Line
3033 13 Jan 09 gregory 1 #!/usr/bin/perl -w
3033 13 Jan 09 gregory 2 # 
3033 13 Jan 09 gregory 3 # Quick and dirty script which generates a html page of tables within a proteios SE database
3033 13 Jan 09 gregory 4 # Author: Gregory Vincic
3033 13 Jan 09 gregory 5 #
3085 06 Feb 09 gregory 6 # grep "hibernate.class" * | awk -F'table=' '{ print $1 $2 }' | awk -F'`' '{ print $1 $2 }' | perl -ane 's/\.java:\s*\*?\s*\@hibernate\.class\s\"/ /g; s/Data//g; /(\w+)\s(\w+)/; print "\"$2\" " ,"=>", " [\"$1\"]", "\n"' > map.txt
3085 06 Feb 09 gregory 7
3033 13 Jan 09 gregory 8 use strict;
3033 13 Jan 09 gregory 9
3033 13 Jan 09 gregory 10 my @tables;
3033 13 Jan 09 gregory 11 my $sql;
3033 13 Jan 09 gregory 12 my @table_txt;
3033 13 Jan 09 gregory 13 my $db;
3085 06 Feb 09 gregory 14 # Exclude the Data suffix of classes
3085 06 Feb 09 gregory 15 my %map = (
3085 06 Feb 09 gregory 16         "Acquisitions" => ["Acquisition"],
3085 06 Feb 09 gregory 17 "Annotations" => ["Annotation"],
3085 06 Feb 09 gregory 18 "AnnotationSets" => ["AnnotationSet"],
3085 06 Feb 09 gregory 19 "AnnotationTypes" => ["AnnotationType"],
3085 06 Feb 09 gregory 20 "BioMaterials" => ["BioMaterial"],
3085 06 Feb 09 gregory 21 "BioMaterialEvents" => ["BioMaterialEvent"],
3085 06 Feb 09 gregory 22 "Clients" => ["Client"],
3085 06 Feb 09 gregory 23 "ClienDefaultSettings" => ["ClientDefaultSetting"],
3085 06 Feb 09 gregory 24 "Contexts" => ["Context"],
3085 06 Feb 09 gregory 25 "ProcessingSteps" => ["ProcessingStep"],
3085 06 Feb 09 gregory 26 "DigestParameters" => ["DigestParameter"],
3085 06 Feb 09 gregory 27 "Directories" => ["Directory"],
3085 06 Feb 09 gregory 28 "DiskUsage" => ["DiskUsage"],
3085 06 Feb 09 gregory 29 "Experiments" => ["Experiment"],
3085 06 Feb 09 gregory 30 "Files" => ["File"],
3085 06 Feb 09 gregory 31 "FileMaps" => ["FileMap"],
3085 06 Feb 09 gregory 32 "FileTypes" => ["FileType"],
3085 06 Feb 09 gregory 33 "GlobalDefaultSettings" => ["GlobalDefaultSetting"],
3085 06 Feb 09 gregory 34 "Groups" => ["Group"],
3085 06 Feb 09 gregory 35 "Hardware" => ["Hardware"],
3085 06 Feb 09 gregory 36 "HardwareTypes" => ["HardwareType"],
3085 06 Feb 09 gregory 37 "Hits" => ["Hit"],
3085 06 Feb 09 gregory 38 "InputSpectra" => ["InputSpectra"],
3085 06 Feb 09 gregory 39 "InstrumentConfigurations" => ["InstrumentConfiguration"],
3085 06 Feb 09 gregory 40 "Jobs" => ["Job"],
3085 06 Feb 09 gregory 41 "Keys" => ["Key"],
3085 06 Feb 09 gregory 42 "Labels" => ["Label"],
3085 06 Feb 09 gregory 43 "Messages" => ["Message"],
3085 06 Feb 09 gregory 44 "MimeTypes" => ["MimeType"],
3085 06 Feb 09 gregory 45 "Modifications" => ["Modification"],
3085 06 Feb 09 gregory 46 "News" => ["News"],
3085 06 Feb 09 gregory 47 "OMSSAParameterSetStorage" => ["OMSSAParameterSetStorage"],
3085 06 Feb 09 gregory 48 "ParameterValues" => ["ParameterValue"],
3085 06 Feb 09 gregory 49 "Passwords" => ["Password"],
3085 06 Feb 09 gregory 50 "Peaks" => ["Peak"],
3085 06 Feb 09 gregory 51 "PeakLists" => ["PeakList"],
3085 06 Feb 09 gregory 52 "PeakListSets" => ["PeakListSet"],
3085 06 Feb 09 gregory 53 "Plates" => ["Plate"],
3085 06 Feb 09 gregory 54 "PlateEvents" => ["PlateEvent"],
3085 06 Feb 09 gregory 55 "PlateEventTypes" => ["PlateEventType"],
3085 06 Feb 09 gregory 56 "PlateGeometries" => ["PlateGeometry"],
3085 06 Feb 09 gregory 57 "PlateMappings" => ["PlateMapping"],
3085 06 Feb 09 gregory 58 "PlateTypes" => ["PlateType"],
3085 06 Feb 09 gregory 59 "PluginConfigurations" => ["PluginConfiguration"],
3085 06 Feb 09 gregory 60 "PluginDefinitions" => ["PluginDefinition"],
3085 06 Feb 09 gregory 61 "PluginTypes" => ["PluginType"],
3085 06 Feb 09 gregory 62 "PolyPeptides" => ["PolyPeptide"],
3085 06 Feb 09 gregory 63 "Precursors" => ["Precursor"],
3085 06 Feb 09 gregory 64 "Projects" => ["Project"],
3085 06 Feb 09 gregory 65 "Protocols" => ["Protocol"],
3085 06 Feb 09 gregory 66 "ProtocolTypes" => ["ProtocolType"],
3085 06 Feb 09 gregory 67 "Quota" => ["Quota"],
3085 06 Feb 09 gregory 68 "QuotaTypes" => ["QuotaType"],
3085 06 Feb 09 gregory 69 "Roles" => ["Role"],
3085 06 Feb 09 gregory 70 "SchemaVersion" => ["SchemaVersion"],
3085 06 Feb 09 gregory 71 "Searchbases" => ["Searchbase"],
3085 06 Feb 09 gregory 72 "SearchResults" => ["SearchResult"],
3085 06 Feb 09 gregory 73 "SeparationMethods" => ["SeparationMethod"],
3085 06 Feb 09 gregory 74 "Sessions" => ["Session"],
3085 06 Feb 09 gregory 75 "Software" => ["Software"],
3085 06 Feb 09 gregory 76 "SoftwareTypes" => ["SoftwareType"],
3085 06 Feb 09 gregory 77 "SpectrumSearches" => ["SpectrumSearch"],
3085 06 Feb 09 gregory 78 "UserClientSettings" => ["UserClientSetting"],
3085 06 Feb 09 gregory 79 "Users" => ["User"],
3085 06 Feb 09 gregory 80 "UserDefaultSettings" => ["UserDefaultSetting"],
3085 06 Feb 09 gregory 81 "Wells" => ["Well"],
3085 06 Feb 09 gregory 82 "XTandemParameterSetStorage" => ["XTandemParameterSetStorage"]
3085 06 Feb 09 gregory 83         );
3033 13 Jan 09 gregory 84 $db = $ARGV[0] || "proteios";
3033 13 Jan 09 gregory 85 @tables = `mysql -e "use $db; show tables"`;
3033 13 Jan 09 gregory 86
3033 13 Jan 09 gregory 87 shift @tables;
3033 13 Jan 09 gregory 88
3033 13 Jan 09 gregory 89 $sql = "use $db;";
3033 13 Jan 09 gregory 90 my $tbl;
3033 13 Jan 09 gregory 91 foreach $tbl (@tables)
3033 13 Jan 09 gregory 92 {
3033 13 Jan 09 gregory 93         chomp $tbl;
3033 13 Jan 09 gregory 94         $sql .= "describe \\\`" . $tbl . "\\\`;";
3033 13 Jan 09 gregory 95 }
3033 13 Jan 09 gregory 96  
3033 13 Jan 09 gregory 97 @table_txt = `mysql -e "$sql"`;
3033 13 Jan 09 gregory 98
3033 13 Jan 09 gregory 99 my $line;
3033 13 Jan 09 gregory 100 my $index = -1;
3199 01 Apr 09 gregory 101 my $jdoclink = "<tr><td colspan=\"6\" class=\"jdoc\">Class: <a href=\"http://www.proteios.org/htdocs/javadoc/core/org/proteios/core/data/%sData.html\">org.proteios.core.data.%sData</a></td></tr>";
3033 13 Jan 09 gregory 102 my $header = "<a name=\"%s\"></a><h2>%s</h2>\n";
3033 13 Jan 09 gregory 103 my $row = "<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>\n";
3033 13 Jan 09 gregory 104 my $rowh = "<tr><th>%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s</th></tr>\n";
3033 13 Jan 09 gregory 105 my $gen = "Date: <b>%s</b><br/>Schema version: <b>%s</b><br/>Build: <b>%s</b><br/>";
3033 13 Jan 09 gregory 106 print <<DATA;
3033 13 Jan 09 gregory 107 <html><head>
3033 13 Jan 09 gregory 108 <style type="text/css">
3085 06 Feb 09 gregory 109 table { border: 1px solid #d2d2d2; width: 500px; margin-top: 5px }
3033 13 Jan 09 gregory 110 th { text-align: left }
3033 13 Jan 09 gregory 111 a { text-decoration: none }
3377 20 Jul 09 gregory 112
3377 20 Jul 09 gregory 113 #gen { float: right }
3377 20 Jul 09 gregory 114 #index { font-size: 0.8em }
3377 20 Jul 09 gregory 115 h2 { width: 500px; margin-bottom: 5px }
3377 20 Jul 09 gregory 116 td.jdoc { font-size: 14px; font-weight: normal; border-bottom: 1px solid #d2d2d2 }
3033 13 Jan 09 gregory 117 div div { background-color: #d2d2d2; padding: 10px; float: left;  margin-right: 1px; margin-bottom: 1px }
3033 13 Jan 09 gregory 118 </style>
3144 26 Feb 09 gregory 119 <style type="text/css" media="print">
3144 26 Feb 09 gregory 120 #gen { display: none }
3144 26 Feb 09 gregory 121 </style>
3033 13 Jan 09 gregory 122 </head>
3033 13 Jan 09 gregory 123 <body>
3033 13 Jan 09 gregory 124 <div id="gen">(<i>Generated by misc/db2html.pl script, Do not edit this file!</i>)</div>
3033 13 Jan 09 gregory 125 DATA
3033 13 Jan 09 gregory 126
3033 13 Jan 09 gregory 127 # Statistics
3033 13 Jan 09 gregory 128
3033 13 Jan 09 gregory 129 my @schema = `mysql -e "use $db; select * from SchemaVersion"`;
3033 13 Jan 09 gregory 130 my (undef, undef, $version, $build) = split(/\t/, $schema[1]);
3033 13 Jan 09 gregory 131 printf $gen, `date`, $version ,$build;
3033 13 Jan 09 gregory 132 my $cols = 5;
3377 20 Jul 09 gregory 133 my $rows = int(($#tables + ($#tables+1) % $cols) / $cols);
3033 13 Jan 09 gregory 134 my $r = $rows;
3033 13 Jan 09 gregory 135 my $count = 1;
3033 13 Jan 09 gregory 136 print "<div id=\"index\">";
3033 13 Jan 09 gregory 137 foreach $tbl ( @tables)
3033 13 Jan 09 gregory 138 {
3033 13 Jan 09 gregory 139         if($r == $rows)
3033 13 Jan 09 gregory 140         {
3033 13 Jan 09 gregory 141                 print "<div>";
3033 13 Jan 09 gregory 142         }
3033 13 Jan 09 gregory 143         print $count . ". <a href=\"#" . $tbl . "\">" . $tbl . "</a><br/>";
3033 13 Jan 09 gregory 144         $count++;
3033 13 Jan 09 gregory 145         $r--;
3033 13 Jan 09 gregory 146         if($r == 0)
3033 13 Jan 09 gregory 147         {
3033 13 Jan 09 gregory 148                 print "</div>";
3033 13 Jan 09 gregory 149                 $r = $rows;
3033 13 Jan 09 gregory 150         }
3033 13 Jan 09 gregory 151 }
3033 13 Jan 09 gregory 152 if($r > 0)
3033 13 Jan 09 gregory 153 {
3033 13 Jan 09 gregory 154         print "</div>";
3033 13 Jan 09 gregory 155 }
3033 13 Jan 09 gregory 156 print "</div><br clear=\"all\"/>";
3034 13 Jan 09 gregory 157
3034 13 Jan 09 gregory 158 my %keys;
3033 13 Jan 09 gregory 159 foreach $line (@table_txt)
3033 13 Jan 09 gregory 160 {
3033 13 Jan 09 gregory 161         chomp $line;
3034 13 Jan 09 gregory 162         my @create;
3034 13 Jan 09 gregory 163         my @cons = ();
3033 13 Jan 09 gregory 164         my ($f, $t, $n, $k, $d, $e) = split(/\t/, $line);
3033 13 Jan 09 gregory 165         if($line eq "Field  Type  Null  Key  Default  Extra")
3033 13 Jan 09 gregory 166         {
3034 13 Jan 09 gregory 167                 %keys = ();
3033 13 Jan 09 gregory 168                 $index++;
3033 13 Jan 09 gregory 169                 if($index > 0)
3033 13 Jan 09 gregory 170                 {
3033 13 Jan 09 gregory 171                         print "</table>";
3033 13 Jan 09 gregory 172                 }
3034 13 Jan 09 gregory 173                 my $name = $tables[$index];
3034 13 Jan 09 gregory 174                 printf $header, $name, $name;
3085 06 Feb 09 gregory 175                 print "<table>";
3085 06 Feb 09 gregory 176
3085 06 Feb 09 gregory 177                 my $class = $name;
3085 06 Feb 09 gregory 178                 my $links = $map{$name} ;
3085 06 Feb 09 gregory 179         
3085 06 Feb 09 gregory 180                 if($links)
3085 06 Feb 09 gregory 181                 {
3085 06 Feb 09 gregory 182                 
3085 06 Feb 09 gregory 183                 my $counter = $#{$links};
3085 06 Feb 09 gregory 184                 foreach (@{$links}) { printf $jdoclink, $_, $_; if($counter) { print " | "; }; $counter--; }
3085 06 Feb 09 gregory 185                                 }
3034 13 Jan 09 gregory 186                 @create = split(/\\n/, `mysql -e "use $db; show create table \\\`$name\\\`"`);
3034 13 Jan 09 gregory 187                 foreach(@create)
3034 13 Jan 09 gregory 188                 {
3034 13 Jan 09 gregory 189                         if(m/^\s*CONSTRAINT.*/)
3034 13 Jan 09 gregory 190                         {
3034 13 Jan 09 gregory 191                                 push(@cons, $_);
3034 13 Jan 09 gregory 192                         }
3034 13 Jan 09 gregory 193                 }
3034 13 Jan 09 gregory 194                 
3034 13 Jan 09 gregory 195                 foreach(@cons)
3034 13 Jan 09 gregory 196                 {
3034 13 Jan 09 gregory 197                         my @cols = split(/\`/, $_);
3034 13 Jan 09 gregory 198                         my $key = $cols[3];
3034 13 Jan 09 gregory 199                         my $val = $cols[5];
3034 13 Jan 09 gregory 200                         $keys{$key} = $val;
3034 13 Jan 09 gregory 201                 }
3033 13 Jan 09 gregory 202                 printf $rowh, $f, $t, $n, $k, $d, $e;
3033 13 Jan 09 gregory 203         }
3033 13 Jan 09 gregory 204         else
3033 13 Jan 09 gregory 205         {
3034 13 Jan 09 gregory 206                 $f =~ s/\s//g;
3034 13 Jan 09 gregory 207                 my $a = $f;
3034 13 Jan 09 gregory 208                 if($keys{$f})
3034 13 Jan 09 gregory 209                 {                        
3034 13 Jan 09 gregory 210                         $a = "<a href=\"#" . $keys{$f} . "\" title=\"".$keys{$f} . "\">" . $f . "</a>";
3034 13 Jan 09 gregory 211                 }
3034 13 Jan 09 gregory 212                 printf $row, $a, $t, $n, $k, $d, $e;
3033 13 Jan 09 gregory 213         }
3033 13 Jan 09 gregory 214 }
3033 13 Jan 09 gregory 215
3033 13 Jan 09 gregory 216 print "</body></html>";

Generated on Thu Jul 20 09:48:42 2023 (UTC) by svndigest 0.11.2pre (dev build 1672)