WebSVN – php_library – Diff – /sql2admin_hash.pl

 #! /usr/bin/perl -w
+# Converts a standard MySQL dump file to a PHP file ready to be used by library.php.
+# NOTE: don't use comma's or parentheses in your comments
 #use Data::Dumper;
 sub getFieldInfo {
    my $fieldInfo = shift;
    my $primary_key = shift;
    my @output;
+   my $comment = '';
+   # For a lookup table, we will just build it
+   if ( $fieldInfo =~ m/references\s+([a-z0-9-_]+)\s*\(\s*([a-z0-9-_]+)\s*\)/i ) { # create a lookup
+      push @output, "'type' => 'lookup', 'table' => '$1', 'keyfield' => '$2', 'display_field' => 'name'";
+   } else { # this is just a standard field
-   push @output, "'keyfield' => 1" if ( $primary_key);
+      push @output, "'keyfield' => true" if ( $primary_key);
-   if ($fieldInfo =~ m/not null/i ) {
+      if ($fieldInfo =~ m/not null/i ) {
-      push @output, "'required' => true";
+         push @output, "'required' => true";
-      $fieldInfo =~ s/not null//i;  # remove the not null from the string
+         $fieldInfo =~ s/not null//i;  # remove the not null from the string
-   }
+      }
-   if ($fieldInfo =~ m/comment *\'([^']+)\'/i ) {
+      if ($fieldInfo =~ m/comment *\'([^']+)\'/i ) {
-      $comment = $1;
+         $comment = $1;
-      $fieldInfo =~ s/comment *\'([^']+)\'//i
+         $fieldInfo =~ s/comment *\'([^']+)\'//i
-   } else {
+      } else {
-      $comment = '';
+         $comment = '';
-   }
+      }
-   $fieldInfo =~ s/default +null//i; # we just ignore default null
+      $fieldInfo =~ s/default +null//i; # we just ignore default null
-   if ($fieldInfo =~ m/default +(\'.*\')/i ) {
+      if ($fieldInfo =~ m/default +(\'.*\')/i ) {
-      $default = $1;
+         $default = $1;
-      $fieldInfo =~ s/default +(\'.*\')//i;
+         $fieldInfo =~ s/default +(\'.*\')//i;
-      push @output, "'default' => $default";
+         push @output, "'default' => $default";
-   } elsif ($fieldInfo =~ /default +([^ ]+)/i ) {
+      } elsif ($fieldInfo =~ /default +([^ ]+)/i ) {
-      $default = $1;
+         $default = $1;
-      $fieldInfo =~ s/default +([^ ]+)//;
+         $fieldInfo =~ s/default +([^ ]+)//;
-      push @output, "'default' => $default";
+         push @output, "'default' => $default";
-   }
+      }
-   if ($fieldInfo =~ m/auto_increment/i ) {
+      if ($fieldInfo =~ m/auto_increment/i ) {
-      push @output, "'readonly' => true";
+         push @output, "'readonly' => true";
-      $fieldInfo =~ s/auto_increment//i;
+         $fieldInfo =~ s/auto_increment//i;
-   }
+      }
-   # at this point, we should only have the data type left
+      # at this point, we should only have the data type left
-   if ( $fieldInfo =~ m/ *([a-z]+)( *\(\d+\))?/i ) {
+      if ( $fieldInfo =~ m/ *([a-z]+)( *\(\d+\))?/i ) {
-      $type = $1;
+         $type = $1;
-      $size = $2;
+         $size = $2;
-      $type =~ s/(varchar)|(char)/string/i;
+         $type =~ s/(varchar)|(char)/string/i;
-      push @output, "'type' => '$type'";
+         push @output, "'type' => '$type'";
-      if ($size) {
+         if ($size) {
-         $size =~ s/[^0-9]//gi;
+            $size =~ s/[^0-9]//gi;
-         push @output, "'width' => $size";
+            push @output, "'width' => $size";
-      } # if
+         } # if
+      }
+   }
    #return "\n//$original\n\n" . join(" , ", @output);
    return ($comment, join(" , ", @output));
+}
 sub getTableDef {
    my $table = shift;
-   print "$table\n";
+   # print "[$table]\n";
    my $unique_key_regex = 'unique key [a-z0-9]+ \(([^)]+)\),?';
    my $primary_key_regex = 'primary key \(([^)]+)\),?';
    my $comment_regex = "comment *'([^']+)'";
    my %tableInformation;
    $table =~ s/ +/ /gi; # remove duplicate spaces
    $table =~ s/ ,/,/gi; # remove all spaces before comma's
-   print "$table\n";
+   #print "[$table]\n\n";
    $table =~ m/create table ([a-z0-9_]+) *\((.+)\)([^\(\)]*)$/i; #
    $tableInformation{'table name'} = $1;
+   print STDERR "[$tableInformation{'table name'}]\n";
    $table = $2;
    $tableCommentStuff = $3;
    if ( $tableCommentStuff and $tableCommentStuff =~ m/comment.*\'(.*)\'/i ) {
       $tableInformation{'comment'} = $1;
+   }
          ($fieldInfo{$fieldName}{'comment'},$fieldInfo{$fieldName}{'data'}) = &getFieldInfo($fieldDef, $tableInformation{'primary keys'} ? ($tableInformation{'primary keys'} =~ m/$fieldName/) : 0 );
+      }
    } # for
    #$tableInformation{'fields'} = \%fieldInfo;
    #print Dumper(\%fieldInfo );
+   # create the output
-   my $output = "'" .  $tableInformation{'table name'} . "' => array( \n";
+   my $output = '';
    if ( $tableInformation{'comment'} ) {
       $output .= "/* $tableInformation{'comment'} */\n";
+   }
+   $output .= "'" .  $tableInformation{'table name'} . "' => array( \n";
    $output .= "\t'table name' => '" . $tableInformation{'table name'} . "',\n";
    if ($tableInformation{'primary keys'}) {
       if ( $tableInformation{'primary keys'} =~ m/,/ ) {
          #following line takes all items in a comma delimited list and quotes them
          $output .= "\t'key field' => array('" . join("','", split( ',',$tableInformation{'primary keys'})) . "'),\n";
    #$fieldNames = join( ',',@fields );
    $output .= "\t'display columns' => array('" . join( "','",@fields ) . "'),\n";
    $output .= "\t'display query' => 'select " . join( ',',@fields ) . " from " . $tableInformation{'table name'} . "',\n";
    my @fieldInfo;
    foreach $thisField (keys %fieldInfo ) {
+      push @fieldInfo, ;
-      push @fieldInfo, "/* " . $fieldInfo{$thisField}{'comment'} . " */" if $fieldInfo{$thisField}{'comment'};
+      push @fieldInfo,  ($fieldInfo{$thisField}{'comment'} ? "\t\t/* " . $fieldInfo{$thisField}{'comment'} . " */\n" : '') .
-      push @fieldInfo,  "'$thisField' => array(" . $fieldInfo{$thisField}{'data'} . ")";
+                        "\t\t'$thisField' => array(" . $fieldInfo{$thisField}{'data'} . ")";
+   }
-   $output .= "\t'field info' => array(\n\t\t" . join( ",\n\t\t", @fieldInfo) . "\n\t)\n)";
+   $output .= "\t'field info' => array(\n" . join( ",\n", @fieldInfo) . "\n\t)\n)";
    return $output;
+}
+my $indentationChar = '   ';
 my $inputFileName = shift;
 if ($inputFileName) {
    open DATA, "$inputFileName" or die "could not open $inputFileName";
    @input = <DATA>;
    close DATA;
+}
 @input = grep( !/^(--)|(\/\*\!)/, @input );
 chomp @input;
+# next line read all input, joins it with null,then splits it based on a semi-colon
+# this places all sql statements into one array entry
+# It then filters for only those statements that start with create table
-my @tables = split( ';', join( '', @input) );
+my @tables = grep(/^create table/i, split( ';', join( '', @input) ));
+#foreach my $thisTable (@tables) {
+#   print "$thisTable\n";
+#}
+#exit 1;
 my @results;
 #push @results, &getTableDef( shift @tables );
 foreach $thisTable( @tables ) {
    $thisTable =~ s/\`//gi;
-   push @results, &getTableDef( $thisTable );
+   push @results, &getTableDef( $thisTable ) if $thisTable;
+}
+# apply proper indentation. The code inserts a tab char and does a decent job of indenting
+# we now replace the tab chars with $indentationChar, and add one more to the beginning
+for (my $line = 0; $line < @results; $line++ ) {
+   $results[$line] = "\t" . $results[$line];
+   $results[$line] =~ s/\n/\n\t/g;
+   $results[$line] =~ s/\t/$indentationChar/g;
+}
 print "<?php\n";
 print "define ( MAX_INPUT_FIELD_DISPLAY, 40 ); // this is the maximum input field size
 define ( IMAGE_DIRECTORY, '/pictures/' );  // relative URL where pictures are stored
 define ( EDIT_IMAGE_HEIGHT, 100 );         // height for thumbnail of pictuers
 define ( MAX_UPLOAD_FILE_SIZE, 1024*1024*10 ); // 10 meg
 define (DEFAULT_TEXTAREA_HEIGHT, 5 );
-define define ( DEFAULT_TABLE, 'FILL ME IN');
+define ( DEFAULT_TABLE, 'FILL ME IN');
 global \$DATABASE_DEFINITION;
 ";
 print "\$DATABASE_DEFINITION = array(\n" . join( ",\n", @results) . "\n);";
 print "\n?>\n";

Subversion Repositories php_library

(root)/sql2admin_hash.pl @ 30 – Rev 5 → 30