3033 |
13 Jan 09 |
gregory |
#!/usr/bin/perl -w |
3033 |
13 Jan 09 |
gregory |
2 |
# |
3033 |
13 Jan 09 |
gregory |
# Quick and dirty script which generates a html page of tables within a proteios SE database |
3033 |
13 Jan 09 |
gregory |
# Author: Gregory Vincic |
3033 |
13 Jan 09 |
gregory |
5 |
# |
3085 |
06 Feb 09 |
gregory |
# 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 |
# 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 |
#gen { float: right } |
3377 |
20 Jul 09 |
gregory |
#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 |
#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 |
# 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>"; |