Rev 3 | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
#! /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;