Subversion Repositories phpLibraryV2

#! /usr/bin/env perl

# sql2admin_hash.pl

#

# Converts a standard MySQL dump file to a PHP file ready to be used by 

# DBDatabase.class.php

# NOTE: don't use comma's or parentheses in your comments

#

# v1.1 RWR 20170728

# modified to use env and strict/warnings.

use strict;

use warnings;

#use Data::Dumper;

my $version = '1.1';

my $secondary = 0;

my $indentationChar = '   ';

my $inputFileName = '';

my $localOverrideFile = 'DatabaseDefinition.local.php';

# converts a field or table name to a display value by separating

# words, based on underscores, then doing a camel case on each

# word.

sub camelCase {

   my $name = shift;

   $name =~ s/_/ /gi; # convert underscore to spaces

   $name =~ s/^ //gi; # remove leading spaces

   $name =~ s{ \b(\w+) }{\u\L$1}gx; # Each "word" capitalized

   return $name;

}

sub getFieldInfo {

   my $fieldName = shift;

   my $fieldInfo = shift;

#   $original = $fieldInfo;

   my $primary_key = shift;

   my @keys;

   my @output;

   my $default;

   my $type;

   my $size;

   push @output, "'display name' => '" . &camelCase($fieldName) . "'";

   # 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'";

      push @output, "'table' => '$1'";

      push @output, "'keyfield' => '$2'";

      push @output, "'display_field' => 'name'";

      $fieldInfo =~ s/references\s+([a-z0-9-_]+)\s*\(\s*([a-z0-9-_]+)\s*\)//i

   } else {

      push @output, "'keyfield' => true" if ( $primary_key);

      if ( $fieldInfo  =~ m/unique/ ) { # this field must be unique

        push @output, "'unique'  =>  true";

        push @keys,  $fieldName;

        $fieldInfo  =~ s/unique//;

      }

      if ($fieldInfo =~ m/comment *\'([^']+)\'/i ) {

         push @output, "'comment' => '$1'";

         $fieldInfo =~ s/comment *\'([^']+)\'//i;

      }

      if ($fieldInfo =~ m/not\s+null/i ) {

         push @output, "'required' => true";

         $fieldInfo =~ s/not\s+null//i;  # remove the not null from the string

      }

      if ($fieldInfo =~ m/null/i ) {

         push @output, "'required' => false";

         $fieldInfo =~ s/null//i;  # remove the not null from the string

      }

      if ($fieldInfo =~ m/default +(\'.*\')/i ) {

         $default = $1;

         $fieldInfo =~ s/default +(\'.*\')//i;

         push @output, "'default' => $default";

      } elsif ($fieldInfo =~ /default +([^ ]+)/i ) {

         $default = $1;

         $fieldInfo =~ s/default +([^ ]+)//;

         push @output, "'default' => $default";

      }

      if ($fieldInfo =~ m/auto_increment/i ) {

         push @output, "'readonly' => true";

         push @output, "'autoincrement' => true";

         $fieldInfo =~ s/auto_increment//i;

      }

      # at this point, we should only have the data type left

      if ( $fieldInfo =~ m/ *([a-z]+)( *\(\d+\))?/i ) {

         $type = $1;

         $size = $2 ? $2 : 0;

         $type =~ s/(varchar)|(char)/string/i;

         $type = 'string' unless $type;

         push @output, "'type' => '$type'";

         $size =~ s/[^0-9]//gi;

         $size = 0 unless $size;

         push @output, "'width' => $size";

      }

   }

#   if ( $fieldName eq 'username' ) {

#      die "$original\n" . join(" , ", @output) . "\n";

#   }

   #return "\n//$original\n\n" . join(" , ", @output);

   return ( "\n\t\t\t" . join(" ,\n\t\t\t", @output) . "\n\t\t");

}

sub getTableDef {

   my $table = shift;

   # 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\n";

   $table =~ m/create table ([a-z0-9_]+) *\((.+)\)([^\(\)]*)$/i; # 

   $tableInformation{'table name'} = $1;

   print STDERR "[$tableInformation{'table name'}]\n";

   $table = $2;

   my $tableCommentStuff = $3;

   if ( $tableCommentStuff and $tableCommentStuff =~ m/comment.*\'(.*)\'/i ) {

      $tableInformation{'comment'} = $1;

   }

   if ( $table =~ m/$primary_key_regex/i ) {

      $tableInformation{'primary keys'} = $1;

      $table =~ s/$primary_key_regex//i;

   }

   if ( $table =~ m/$unique_key_regex/i ) {

      $tableInformation{'unique keys'} .= $1;

      $table =~ s/$unique_key_regex//i;

   }

   my @columnInformation = split(',',$table);

   my %fieldInfo;

   my @fieldOrder; # I use an array here so we can do display, etc... in original order

   for ( my $i = 0; $i < @columnInformation; $i++) {

      $columnInformation[$i] =~ s/^ +//;

      if ( $columnInformation[$i] =~ m/ *([a-z0-9_]+) +(.*)/i )  {

         my $fieldName = $1;

         push @fieldOrder, $fieldName;

         my $fieldDef = $2;

         $fieldInfo{$fieldName}{'data'} = 

           &getFieldInfo($fieldName, $fieldDef, $tableInformation{'primary keys'} 

              ? ($tableInformation{'primary keys'} =~ m/$fieldName/) 

              : 0

           );

      }

   } # for

   #$tableInformation{'fields'} = \%fieldInfo;

   #print Dumper(\%fieldInfo );

   # create the output

   my $output = '';

   if ( $secondary ) {

      $output = "\$DATABASE_DEFINITION['" . $tableInformation{'table name'} . "'] = array(\n";

   } else {

      $output .= "'" .  $tableInformation{'table name'} . "' => array( \n";

   }

   $output .= "\t'table name' => '" . $tableInformation{'table name'} . "',\n";

   $output .= "\t'display name' => '" . &camelCase($tableInformation{'table name'}) . "',\n";

   if ( $tableInformation{'comment'} ) {

      $output .= "\t'comment' => '$tableInformation{'comment'}',\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";

      } else {

         $output .= "\t'key field' => '" . $tableInformation{'primary keys'} . "',\n";

      }

   }

   if ($tableInformation{'unique keys'}) {

      if ( $tableInformation{'unique keys'} =~ m/,/ ) {

         #following line takes all items in a comma delimited list and quotes them

         $output .= "\t'unique fieldss' => array('" . join("','", split( ',',$tableInformation{'unique keys'})) . "'),\n";

      } else {

         $output .= "\t'unique fields' => '" . $tableInformation{'unique keys'} . "',\n";

      }

   }

   # do the display columns, basically all columns

   #@fields = keys %fieldInfo;

   #$fieldNames = join( ',',@fields );

   $output .= "\t'display columns' => array(\n\t\t'" . join( "',\n\t\t'",@fieldOrder ) . "'\n\t),\n";

   $output .= "\t'display query' => 'select " . join( ',',@fieldOrder ) . " from " . $tableInformation{'table name'} . "',\n";

   my @fieldInfo;

   foreach my $thisField ( @fieldOrder ) {

      push @fieldInfo,  ($fieldInfo{$thisField}{'comment'} ? "\t\t/* " . $fieldInfo{$thisField}{'comment'} . " */\n" : '') .

                        "\t\t'$thisField' => array(" . $fieldInfo{$thisField}{'data'} . ")";

   }

   $output .= "\t'field info' => array(\n" . join( ",\n", @fieldInfo) . "\n\t)\n)";

   return $output;

}

sub processCommandLine {

   while ( my $parameter = shift ) {

      if ($parameter =~ m/-f(.*)/i) {

         $inputFileName = $1;

      } elsif ( $parameter =~ m/-s/i) {

         $secondary = 1;

      }

   }

}

&processCommandLine( @ARGV );

# die "Input Filename == $inputFileName, secondary = $secondary\n";

my @input;

if ($inputFileName) { # they want us to read from this file

   open DATA, "$inputFileName" or die "could not open $inputFileName";

   @input = <DATA>;

   close DATA;

} else { # no filename entered, so read STDIN

   @input = <STDIN>;

}

@input = grep( !/^(--)|(\/\*\!)/, @input ); # remove all comments

chomp @input; # get rid of all line endings

# 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 = grep(/^create table/i, split( ';', join( '', @input) ));

#foreach my $thisTable (@tables) {

#   print "$thisTable\n";

#}

#exit 1;

my @results;

#push @results, &getTableDef( shift @tables );

foreach my $thisTable( @tables ) {

   $thisTable =~ s/\`//gi;

   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;

}

# now, actually print out the results

print "<?php

/* 

   Auto generated from SQL script by $0 version $version. 

   do not edit this file. Edit $localOverrideFile as changes

   to that file will not be overwritten by subsequent runs of this

   script.

*/

global \$DATABASE_DEFINITION; // make variable available to all scripts

";

if ( $secondary ) { # this is an auxilary file

   print join( ";\n", @results) ;

} else { # this is the main, or only database.php file

   print "

define ( 'MAX_INPUT_FIELD_DISPLAY', 40 ); // this is the maximum input field size

define ( 'FILES_DIRECTORY', '/files/' );  // relative URL where pictures are stored

define ( 'EDIT_IMAGE_HEIGHT', 100 );      // height for thumbnail of picturse

define ( 'MAX_UPLOAD_FILE_SIZE', 1024*1024*10 ); // 10 meg

define ( 'DEFAULT_TEXTAREA_HEIGHT', 5 );

define ( 'DEFAULT_TABLE', 'FILL ME IN');

";

   print "\$DATABASE_DEFINITION = array(\n"; 

   print join( ",\n", @results) ;

   print "\n);";

}

print "\ninclude('$localOverrideFile');\n?>\n";

1;