| 1 | use Exporter; | ||
| 2 | use strict; | ||
| 3 | |||
| 4 | # I've rewritten this from scratch, but I still think this is a bit | ||
| 5 | # dirty. As it is now, I'm mixing perl, HTML, HTML::Template-code | ||
| 6 | # ... and even the kludgy parameters to DBIx::Recordset and | ||
| 7 | # DBIx::CGITables. Sort of ironically as that was what I was trying | ||
| 8 | # to avoid in the first place. I suggest either some kind of template | ||
| 9 | # system for making the templates ... or maybe this can be done in an | ||
| 10 | # object-oriented way. | ||
| 11 | |||
| 12 | # I've splitted this one in two parts - first one part for reading DD | ||
| 13 | # code and/or stored metadata from a file, then a part for writing | ||
| 14 | # templates and the parameter file. | ||
| 15 | |||
| 16 | # It's going to make two templates, one for viewing/updating a single | ||
| 17 | # item/object/database row (a pre-filled form) alternatively an empty | ||
| 18 | # form (for searching), and one for viewing a listing. In addition | ||
| 19 | # it's going to create a parameter file for DBIx::CGITables / | ||
| 20 | # DBIx::Recordset. | ||
| 21 | |||
| 22 | # The %metadata hash contains all metadata - with redundancy for quick | ||
| 23 | # & easy lookups. The format of %metadata is: | ||
| 24 | |||
| 25 | # All parameters to be written directly to the parameter file: | ||
| 26 | # $metadata{'!params'}=[ "$key=$value", ...] | ||
| 27 | # $metadata{$database}->{'!params'} | ||
| 28 | # $metadata{$database}->{$tablename}->{'!params'} | ||
| 29 | |||
| 30 | # Column metadata: | ||
| 31 | # $metadata{$database}->{$table}->{$column}->{$key}=$value; | ||
| 32 | |||
| 33 | # Where $key=$value can be: | ||
| 34 | |||
| 35 | # input=textarea|textbox|select[ db.t.name]|hidden | ||
| 36 | # output | ||
| 37 | # list_output | ||
| 38 | # input_max=$size | ||
| 39 | # input_size=$size (defaults to max or 4 for numeric or 10 for date | ||
| 40 | # or 19 for timestamp or 30 for textbox) | ||
| 41 | # input_html_options=$opts | ||
| 42 | # references="$database.$table.$column" | ||
| 43 | # referenced_by="$database.$table.$column" | ||
| 44 | # title=1 | ||
| 45 | # primkey=1 | ||
| 46 | # substring_searchable=1 | ||
| 47 | # startstring_searchable=1 | ||
| 48 | |||
| 49 | # Table metadata (mostly redundant) | ||
| 50 | # $metadata{$database}->{$table}->{$key}=$value; | ||
| 51 | |||
| 52 | # Where $key=$value can be: | ||
| 53 | |||
| 54 | # !order=[$key1, $key2, ...] | ||
| 55 | # !foreign_keys={$foreign_key=>"$referenced_db.$referenced_table.$referenced_column", ...} | ||
| 56 | # !referenced_keys={$referenced_key=>"$referencing_db.$referencing_table.$foreign_key", ...} | ||
| 57 | # !title=$title_key | ||
| 58 | # !primkey=$primary_key | ||
| 59 | |||
| 60 | use vars qw | | ||
| 61 | @EXPORT @ISA $output_dir $param_dir $new_search_button $head | ||
| 62 | $form_head $list_head $tail $form_tail $list_tail $usertable | ||
| 63 | %metadata|; | ||
| 64 | |||
| 65 | |||
| 66 | @ISA=qw(Exporter); | ||
| 67 | @EXPORT=qw(make); | ||
| 68 | |||
| 69 | # The template files will be put into $output_dir, defaulting to ".". | ||
| 70 | $output_dir="."; | ||
| 71 | |||
| 72 | # The parameter file will be put into $param_dir, defaulting to ".". | ||
| 73 | $param_dir="."; | ||
| 74 | |||
| 75 | # The table where users can be found | ||
| 76 | $usertable="defaultdb.personell"; | ||
| 77 | |||
| 78 | # The $new_search_button is how to represent a "get a fresh search form-button". | ||
| 79 | $new_search_button=' | ||
| 80 | <form method="post"> <!-- Better to use get when debugging --> | ||
| 81 | <TMPL_IF NAME="!UserName"> | ||
| 82 | <input type="hidden" name="!UserName" value="<TMPL_VAR NAME="!UserName">"> | ||
| 83 | </TMPL_IF> | ||
| 84 | <TMPL_IF NAME="!Password"> | ||
| 85 | <input type="hidden" name="!Password" value="<TMPL_VAR NAME="!Password">"> | ||
| 86 | </TMPL_IF> | ||
| 87 | <input type="hidden" name="!SearchForm" value="1"> | ||
| 88 | <input type="submit" value="Blank form"> | ||
| 89 | </form> | ||
| 90 | '; | ||
| 91 | |||
| 92 | # Head is how the head of the templates should look. Eventually there | ||
| 93 | # should also be a title in the header, but I haven't figured out any | ||
| 94 | # smart way to do it yet. | ||
| 95 | |||
| 96 | $head=' | ||
| 97 | <head> | ||
| 98 | <title><TMPL_VAR name="!Table"></title> | ||
| 99 | </head> | ||
| 100 | <body> | ||
| 101 | <h1><TMPL_VAR name="!Table"></h1>'. $new_search_button; | ||
| 102 | |||
| 103 | # Oposite of head: | ||
| 104 | $tail='</body></html> | ||
| 105 | '; | ||
| 106 | |||
| 107 | $list_head="$head | ||
| 108 | <table>"; | ||
| 109 | $list_tail="</TMPL_LOOP></table> | ||
| 110 | $tail"; | ||
| 111 | |||
| 112 | # Form_head is the start of the form that is used when | ||
| 113 | # viewing/updating/adding one object/row/item. | ||
| 114 | $form_head=' | ||
| 115 | <form method="post"> | ||
| 116 | <TMPL_LOOP default> | ||
| 117 | '; | ||
| 118 | |||
| 119 | |||
| 120 | |||
| 121 | # Form_tail is the tail of the form | ||
| 122 | |||
| 123 | $form_tail=' | ||
| 124 | </TMPL_LOOP> | ||
| 125 | <TMPL_IF name="?count"> | ||
| 126 | <input type=submit name="=update" value="Update"><br> | ||
| 127 | <TMPL_ELSE> | ||
| 128 | <input type=submit name="=search" value="Search"><br> | ||
| 129 | <input type=submit name="=add=" value="add"><br> | ||
| 130 | </TMPL_IF> | ||
| 131 | Login: <input name="!UserName" value="<TMPL_VAR name="!UserName">"><br> | ||
| 132 | Password: <input name="password" name="!Password" value="<TMPL_VAR name="!Password">"><br> | ||
| 133 | '; | ||
| 134 | |||
| 135 | |||
| 136 | |||
| 137 | sub make { | ||
| 138 | fetch_params(shift || $ENV{'DBI_DSN'} || undef); | ||
| 139 | write_templates(); | ||
| 140 | # store_params(); | ||
| 141 | } | ||
| 142 | |||
| 143 | |||
| 144 | |||
| 145 | sub fetch_params { | ||
| 146 | warn "This is under development. Foreign keys and a lot of other features are not supported yet."; | ||
| 147 | |||
| 148 | # I'll use those variables when playing around with the sqldd: | ||
| 149 | |||
| 150 | my $dsn=shift; | ||
| 151 | |||
| 152 | push @{$metadata{'!params'}}, "!DataSource=$dsn" | ||
| 153 | if (defined $dsn); | ||
| 154 | |||
| 155 | |||
| 156 | my ($tablename, $schema_name, $in_table); | ||
| 157 | |||
| 158 | # Reading the input file | ||
| 159 | |||
| 160 | while (<>) { | ||
| 161 | |||
| 162 | # Chop away doublequotes - we probably don't need them, | ||
| 163 | # and mysql doesn't have them anyway: | ||
| 164 | s/\"//g; | ||
| 165 | |||
| 166 | # Leading and trailing whitespace is hardly relevant | ||
| 167 | s/^\s*//; | ||
| 168 | s/\s*$//; | ||
| 169 | |||
| 170 | if (/^(\#|--)(\s*)END$/) { | ||
| 171 | # End of sql dd | ||
| 172 | last; | ||
| 173 | } | ||
| 174 | |||
| 175 | elsif (/^\s*\#(.*)Database: (\w+)/) { | ||
| 176 | # Mysql hides the schema name in the comments | ||
| 177 | $schema_name=$2; | ||
| 178 | } | ||
| 179 | |||
| 180 | elsif (/^(\s*(\);(\s*))?)$/ || /^(-- |\#|\;)/) { | ||
| 181 | # Probably nothing interessting. | ||
| 182 | } | ||
| 183 | |||
| 184 | elsif (/^use (\w+)\;$/) { | ||
| 185 | $schema_name=$1; | ||
| 186 | } | ||
| 187 | |||
| 188 | elsif (/^create(\s*)table/i) { | ||
| 189 | # New table | ||
| 190 | $_=$'; | ||
| 191 | /^(\s*)(\w+)(\.(\w+))?/ || die "syntax error in inputs?" ; | ||
| 192 | $tablename=$4 ? $4 : $2; | ||
| 193 | $schema_name=$4 ? $2 : $schema_name; | ||
| 194 | } | ||
| 195 | |||
| 196 | elsif (/^(\w+)(\s*)(int|varchar|blob|char|date|timestamp|time)/i) { | ||
| 197 | # (probably) a normal variable | ||
| 198 | my ($key, $type)=($1, $3); | ||
| 199 | my $length=($type =~ /^int/i) ? 4 : ($type =~ /timestamp/i) ? 19 : undef; | ||
| 200 | $metadata{$schema_name}->{$tablename}->{$key}->{input_size}=$length; | ||
| 201 | $metadata{$schema_name}->{$tablename}->{$1}->{'list_output'}=1; | ||
| 202 | $metadata{$schema_name}->{$tablename}->{$key}->{input}= | ||
| 203 | ($type =~ /(LONG(\s*)VARCHAR)|(blob)/i) ? 'textarea' : 'textbox'; | ||
| 204 | } | ||
| 205 | |||
| 206 | elsif (/^PRIMARY KEY \((\w+)\)/i) { | ||
| 207 | $metadata{$schema_name}->{$tablename}->{$1}->{'!primkey'}=1; | ||
| 208 | $metadata{$schema_name}->{$tablename}->{$1}->{'output'}=1; | ||
| 209 | $metadata{$schema_name}->{$tablename}->{$1}->{'input'}='hidden'; | ||
| 210 | $metadata{$schema_name}->{$tablename}->{'!primkey'}=$1; | ||
| 211 | push @{$metadata{$schema_name}->{$tablename}->{'!params'}}, "!PrimKey=$1"; | ||
| 212 | } | ||
| 213 | |||
| 214 | else { | ||
| 215 | warn "Unknown input"; | ||
| 216 | } | ||
| 217 | } | ||
| 218 | |||
| 219 | } # end of sub fetch_params | ||
| 220 | |||
| 221 | |||
| 222 | |||
| 223 | |||
| 224 | |||
| 225 | |||
| 226 | |||
| 227 | |||
| 228 | |||
| 229 | |||
| 230 | |||
| 231 | |||
| 232 | |||
| 233 | |||
| 234 | |||
| 235 | |||
| 236 | sub write_templates { | ||
| 237 | |||
| 238 | my $multiadd_feature=0; # Stubbed | ||
| 239 | |||
| 240 | for my $schema_name (keys %metadata) { | ||
| 241 | next if $schema_name =~ /^\!/; | ||
| 242 | |||
| 243 | for my $tablename (keys %{$metadata{$schema_name}}) { | ||
| 244 | next if $tablename =~ /^\!/; | ||
| 245 | |||
| 246 | my $list_row="<TMPL_LOOP default><tr><td>"; | ||
| 247 | |||
| 248 | # Open the files: | ||
| 249 | open (PARAMF, ">$param_dir/$schema_name.$tablename.param.dbt") || die; | ||
| 250 | open (FOUNDMORE, ">$output_dir/$schema_name.$tablename.found_more.dbt")|| die; | ||
| 251 | open (SINGLE, ">$output_dir/$schema_name.$tablename.dbt")|| die; | ||
| 252 | open (MULTIADD, ">$output_dir/$schema_name.$tablename.multiadd.dbt") || die | ||
| 253 | if ($multiadd_feature); | ||
| 254 | |||
| 255 | # Print headers: | ||
| 256 | print PARAMF qq|\ | ||
| 257 | !Table=$tablename | ||
| 258 | %? \$max=100 | ||
| 259 | !IgnoreEmpty=2 | ||
| 260 | !ContentType=text/html | ||
| 261 | %RGV PreserveCase=1 | ||
| 262 | %T die_on_bad_params=0 | ||
| 263 | |; | ||
| 264 | print PARAMF join ("\n", @{$metadata{'!params'}}, "\n") | ||
| 265 | if exists $metadata{'!params'}; | ||
| 266 | print PARAMF join ("\n", @{$metadata{$schema_name}->{'!params'}}, "\n") | ||
| 267 | if exists $metadata{$schema_name}->{'!params'}; | ||
| 268 | print PARAMF join ("\n", | ||
| 269 | @{$metadata{$schema_name}->{$tablename}->{'!params'}}, "\n") | ||
| 270 | if exists $metadata{$schema_name}->{$tablename}->{'!params'}; | ||
| 271 | |||
| 272 | print SINGLE $head; | ||
| 273 | |||
| 274 | # For MultiAdd: | ||
| 275 | # print SINGLE "<TMPL_IF AddSelectCount>You added | ||
| 276 | # <TMPL_VAR AddSelectcount> combinations..</TMPL_IF>"; | ||
| 277 | |||
| 278 | print SINGLE $form_head; | ||
| 279 | |||
| 280 | print FOUNDMORE $list_head; | ||
| 281 | if (my $primkey=$metadata{$schema_name}->{$tablename}->{'!primkey'}) { | ||
| 282 | $list_row.=qq|<a href="$schema_name.$tablename.dbt?$primkey=<TMPL_VAR $primkey>"><TMPL_VAR $primkey></a>|; | ||
| 283 | print FOUNDMORE "<th>$primkey</th>"; | ||
| 284 | } | ||
| 285 | |||
| 286 | # # the multiadd-feature is not supported yet. This is from my earlier system. | ||
| 287 | # print MULTIADD $head, $form_head, q| | ||
| 288 | # <input type="hidden" name="skip" value="1"> | ||
| 289 | # <input type="hidden" name="commit" value="addselect"> | ||
| 290 | # | | ||
| 291 | # if ($multiadd_feature); | ||
| 292 | |||
| 293 | |||
| 294 | for my $column (keys %{$metadata{$schema_name}->{$tablename}}) { | ||
| 295 | next if $column =~ /^\!/; | ||
| 296 | |||
| 297 | my $inputopts=""; | ||
| 298 | |||
| 299 | my $input =$metadata{$schema_name}->{$tablename}->{$column}->{input}; | ||
| 300 | my $output=$metadata{$schema_name}->{$tablename}->{$column}->{output}; | ||
| 301 | my $list_output=$metadata{$schema_name}->{$tablename}->{$column}->{list_output}; | ||
| 302 | |||
| 303 | if ($input eq 'textbox') { | ||
| 304 | # input_size and input_max | ||
| 305 | if (my $length=$metadata{$schema_name}->{$tablename}->{$column}->{input_size}) { | ||
| 306 | $inputopts.="size=\"$length\""; | ||
| 307 | if (my $max=$metadata{$schema_name}->{$tablename}->{$column}->{input_max}) { | ||
| 308 | $inputopts .= " maxlength=\"$length\""; | ||
| 309 | } | ||
| 310 | } | ||
| 311 | print SINGLE "$column: <input name=\"$column\" value=\"<TMPL_VAR $column>\" $inputopts><br>\n"; | ||
| 312 | } | ||
| 313 | |||
| 314 | elsif ($input eq 'textarea') { | ||
| 315 | print SINGLE "$column:<br><textarea name=\"$column\" rows=10 cols=60 wrap=soft><TMPL_VAR $column></textarea><br>\n"; | ||
| 316 | } | ||
| 317 | |||
| 318 | elsif ($input eq 'hidden') { | ||
| 319 | print SINGLE "<TMPL_IF $column><input type=\"hidden\" name=\"$column\" value=\"<TMPL_VAR $column>\"></TMPL_IF>"; | ||
| 320 | } | ||
| 321 | |||
| 322 | # OnUpdate - Postponed, not supported by Recordset as for now | ||
| 323 | if (0 && $column =~ /^(LAST)?(UPDATED|CREATED)(TIMESTAMP?)$/i) { | ||
| 324 | print PARAMF "(...)\\$column=now()\n"; | ||
| 325 | print SINGLE "\\$column: (...)<br>\n"; | ||
| 326 | } | ||
| 327 | |||
| 328 | # same with this one | ||
| 329 | elsif (0 && $column =~ /(CREATED|ADDED|UPDATED)BY/i) { | ||
| 330 | |||
| 331 | my $au=($1 =~ /^ADDED$/i) ? 'add' : 'update'; | ||
| 332 | |||
| 333 | print PARAMF | ||
| 334 | "_$au\_$column\_Function_Substitute=(select personellid from personell where login='\$user')\n"; | ||
| 335 | next; | ||
| 336 | } | ||
| 337 | |||
| 338 | if ($list_output) { | ||
| 339 | print FOUNDMORE "<th>$column</th>\n"; | ||
| 340 | $list_row.="<td><TMPL_VAR $column></td>"; | ||
| 341 | } | ||
| 342 | |||
| 343 | if ($output) { | ||
| 344 | print SINGLE "<TMPL_IF $column>$column: <TMPL_VAR $column><br></TMPL_IF>\n"; | ||
| 345 | } | ||
| 346 | |||
| 347 | } | ||
| 348 | |||
| 349 | # Let's finish and close those templates | ||
| 350 | print FOUNDMORE "\n",$list_row,"</tr>\n", $list_tail, $tail; | ||
| 351 | close (FOUNDMORE); | ||
| 352 | print SINGLE $form_tail; | ||
| 353 | # Handle references: stub! | ||
| 354 | print SINGLE $tail; | ||
| 355 | print MULTIADD qq| | ||
| 356 | </table><INPUT type="submit" value="MultiAdd!"></form> | ||
| 357 | |, $tail if ($multiadd_feature); | ||
| 358 | close (SINGLE); | ||
| 359 | close (PARAMF); | ||
| 360 | close (MULTIADD) | ||
| 361 | if ($multiadd_feature); | ||
| 362 | } | ||
| 363 | } | ||
| 364 | } # End of the write_templates sub | ||
| 365 | |||
| 366 | |||
| 367 | __END__ |