#!/usr/bin/perl

# FreeSQL 0.2 by Eric Ries (eries@users.sourceforge.net)
# Try the following commands to get started. 
#
# SELECT studentid,bar FROM student WHERE studentid > 0;
# INSERT INTO student(studentid, foo, bar) VALUES( 1, 56, 'foobar' );
# SELECT studentid,foo,bar FROM student WHERE studentid=1;
# INSERT INTO student(studentid, foo, bar) VALUES( 2, 32, 'fooji' );
# SELECT studentid,foo FROM student WHERE bar like "foo%";
# INSERT INTO school(schoolid, ack) VALUES(1,2);
# INSERT INTO school(schoolid, ack) VALUES(2,75);
# SELECT studentid,foo FROM student, school WHERE studentid = school.ack;

# Other commands that are supported:
# CREATE TABLE tablename( field1 type, field2 type, ... PRIMARY KEY(fieldN), KEY indexname (fieldX,fieldY,...);
#
# UPDATE tablename SET field1=val,field2=val,... WHERE where_condition;
#
# DELETE FROM tablename WHERE where_condition;
#
# Please report bugs, suggestions, comments! Thanks for downloading...

# Some restrictions:
# 1. The data is not returned in a very useful format, sorry about that
# 2. You must already have Freenet installed. See http://freenet.sourceforge.net
# 3. I cannot find a way to turn off all the debug spew that the freenet commands use, if you figure this out let me know.
# 4. You may have to modify a few variables below in order to get this to work
# 5. This code is GPL. No warranties, yadda yadda. http://www.gnu.org
# 6. Read Jessica Litman's excellent book _Digital Copyright_ before you have an opinion about digital copyright


# BEGIN CONFIG SECTION




# for now, we hard-code the list of tables. this is no longer necessary,
# as FreeSQL supports the CREATE directive. Still, these tables are
# useful if you just want to start playing in interactive mode.
%tables = ( student => 
            { defined => 1,
              fields => ["studentid", "foo", "bar"],
              primary_key => "studentid" 
            },
            school =>
            { defined => 1,
              fields => ["schoolid", "ack", "back"],
              primary_key => "schoolid"
              }
        );

use Cwd;

$workingdir = cwd() . '/';

$platform = 'UNKNOWN';
if( $workingdir =~ /c:[\/]/i ) {
    $platform = 'WIN32';
} else {
    $platform = 'UNIX';
}
${$platform} = 1;

print "Running on platform $platform\n";


# where is your freenet executables/batchfiles located?
$freenet_dir = $WIN32 ? "/program files/freenet/" : "/usr/local/bin/freenet";

# what should I use for insert?
# on UNIX, you probably want 'freenet_insert'
$finsert = $WIN32 ? 'finsert.exe' : "freenet_insert";

# what should I use for request?
# on UNIX, you probably want 'freenet_request'
$frequest = $WIN32 ? 'frequest.exe' : "freenet_request";

# use this line if you want to use fcpget: freeweb.sourceforge.net/fcpget.zip 
# Because I'm a nice person, I'll try this check for you
if( $WIN32 && -e $freenet_dir . "fcpget.exe" ) {
    $frequest = "fcpget.exe"
}

# where is your Freenet node running? hostname:port
$freenet_addr = 'localhost:30348'; 


#use XMLRPC::Lite;
#print XMLRPC::Lite
#    -> proxy("http://$freenet_addr")
#    -> call('simple.getFile( freenet:KSK@test, 20)' )
#    -> result;




# where can I write out some temporary files?
# on UNIX you probably want "/tmp/"
$temp_dir = $WIN32 ? "/temp/" : "/tmp/";

# what should my command-line parameters be?
# the first %d will be the htl value
# the two %s are keyname and filename respectively
# use this command line with the standard Freenet tools
$freenet_rcmd = "$frequest -verbosity 0 -logging error -serverAddress $freenet_addr -htl %d %s %s";

# use this one if you are using fcpget: freeweb.sourceforge.net/fcpget.zip 
if( $frequest =~ /fcpget/ ) {
    $freenet_rcmd = "$frequest -htl%d %s %s";
}

$freenet_icmd = "$finsert -verbosity 0 -logging error -htl 1 -serverAddress $freenet_addr ";

# make this > 0 for debug spew. Take it to higher numbers, up to 5, to get even more spew
$DEBUG = 0;

# END CONFIGURATION DIRECTIVES

close STDERR unless $DEBUG;
chdir $freenet_dir;

# if you screw up your database and want to start over (there is no DROP in freesql), you can 
# just change the following prefix.
$kskprefix = 'KSK@freesql1/';

$version_file = $temp_dir . "version_file.txt";

%versionNumberCache = ();
if( -e $version_file ) {
    restoreVersionCache( $version_file );
}



my $fkey = $kskprefix . "schema";
my $ver = fetchLatestVersionNumber( $fkey );
$dbSchema = freenetRequest( $fkey, $ver );
@schemaCommands = split "\n", $dbSchema;

print "> ";
while( $line = shift @schemaCommands || <STDIN> ) {
    chomp( $line );

    if( $line ) {
	$line = $prefix . $line;
    }
    $prefix = "";

    if( $line =~ /(.+);/g ) {
	$cmd = $1;
	if( $cmd =~ /SELECT\s+(.+)\s+FROM\s+(.+)\s+WHERE\s+(.+)\s*/i ) {
	    doSelect( $1, $2, $3 );
        } elsif( $cmd =~ /SELECT\s+(.+)\s+FROM\s+(.+)/i ) {
            doSelect( $1, $2 );
	} elsif( $cmd =~ /INSERT\s+INTO\s+(\w+)\s*(\((.+)\))?\s+VALUES\s*\((.+)\)/i ) {
            print "'$1', '$2' '$3' '$4'\n";
	    doInsert( $1, $3, $4 );
	} elsif( $cmd =~ /UPDATE\s+(\w+)\s+SET\s+(.+)\s+WHERE\s+(.+)/i ) {
            doUpdate( $1, $2, $3 );
        } elsif( $cmd =~ /CREATE TABLE\s*(.+?)\s*\(\s*(.+)\s*\)/i ) {
            createTable( $1, $2 );
        } elsif( $cmd =~ /DELETE FROM (.+) WHERE (.+)/i ) {
            deleteRecords( $1, $2 );
        } elsif( $cmd =~ /quit/i ) {
            print "Bye, thanks for using FreeSQL\n";
            last;
	} else {
	    print "Invalid command\n";
	}
    } else {
	$prefix = "$line ";
    }

    print "$prefix|\n";
    print "> ";
}

saveVersionCache( $version_file );

sub saveVersionCache
{
    my $file = shift;
    
    open OUTFILE, "> $file" or die $!;
    print OUTFILE "# Cache of version numbers for FreeSQL. Feel free to delete this file if it gets to big.\n";
    print OUTFILE '%versionNumberCache = (';
    my @arr = ();
    while( my( $k, $v ) = each( %versionNumberCache ) ) {
        push @arr, "q{$k} => $v";
    }
    print OUTFILE join ",\n", @arr;
    print OUTFILE ");\n";
    close OUTFILE;
}

sub restoreVersionCache
{
    my $file = shift;
    open INFILE, $file or die $!;
    my @stuff = <INFILE>;
    my $stuff = join "", @stuff;
    print $stuff if $DEBUG > 1;
    eval( $stuff );
    die "Error: $@" if $@;
    close INFILE;
}

sub primaryKeyName
{
    my $table = shift;
    my $ret = $tables{$table}{primary_key} or $tables{$table}{fields}[0];
    #if( $ret =~ /(.+?),(.+)/ ) {
    #    return $1;
    #}
    return $ret;
}

sub fetchLatestVersion
{
    my ($table, $field, $value ) = @_;
    my $fkey = $kskprefix . "$table/$field/$value";
    my $ver = fetchLatestVersionNumber( $fkey );
    return freenetRequest( $fkey, $ver );
}


# give me a table and a primary key value, and I will give you all the data I can find for that key
sub fetchFromKey
{
    my( $table, $key, @fields ) = @_;

    my $pkey = primaryKeyName( $table );
    print "fetchSingleRecord( $table, $pkey, $key )\n" if $DEBUG;

    my @data = fetchLatestVersion( $table, $pkey, $key );
    my %data = ();
    my $ret = "";
    if( $#fields == 0 ) {
        @fields = split ",", $fields[0];
    }

    # FIXME: put this back the way it was, with a much more efficient hash lookup!
    foreach my $line (@data) {
        while( $line =~ /(\w+)=(.+)/g ) {
            print "hashing up $1 and $2\n" if $DEBUG > 3;
            foreach my $field (@fields) {
                $field = strip( $field );
                print "asked for field '$field'\n" if $DEBUG > 3;
                print "$1 ?= $field\n" if $DEBUG > 4;
                my $test = $1;
                my $val = $2;
                if( $test =~ /^$field/ ) {
                    print "Found $test=$val\n";
                    $ret .= "$test=$val\n";
                }
            }
        }
    }

    print "fetchFromKey($table, $key, @fields) returning '$ret'\n" if $DEBUG > 0;
    return $ret;
}

sub strip
{
    my $val = shift;
    
    if( $val =~ /^\s*(\S+)\s*$/ ) {
        return $1;
    }
    return $val;
}

sub indexLookup
{
    my ($table, $field, $value) = @_;

    my $safevalue = $value;
    $safevalue =~ tr/A-Za-z_0-9//cd;
    my $key = $kskprefix."index/$table/$field/$value";
    my $ver = fetchLatestVersionNumber( $key );
    return freenetRequest( $key, $ver );
}

# give me a table and list of conditions, and I will try and find the relevant primary keys
sub fetchPrimaryKey
{
    my ( $table, @conds ) = @_;

    my $count = 0;
    my %countHash = ();
    my %joins = ();
    my $operators = '<>==!';
    my %toEval = ();
    my $field, $val, $op;
    my $qq = "\"'";
    foreach $cond (@conds) {
        %toEval = ();

	print "Processing condition $count $cond\n" if $DEBUG;
	if( $cond =~ /(.+)\s*={1}\s*(.+)/ ) {
	    $field = $1;
	    $val = $2;
            if( $val =~ /\./ ) {
                print "Found join $field = $val\n" if $DEBUG;
                $joins{$field} = $val;
                next;
            }
            $toEval{$val} = "";
	} elsif( $cond =~ /([\w\s+]+?)\s*([$operators]+)\s*(.+)/ ) {
            ($field, $op, $val) = ($1,$2,$3);
            if( $field =~ /(\w+)\W/ ) {
                $field = $1;
            }
            my @data = fetchValues( $table, $field );
            foreach my $d (@data) {
                $toEval{$d} = "$d $op $val";
            }
        } elsif( $cond =~ m/(.+?)\s*like\s*([$qq])(.+)\2/ ) {
            ($field, $val) = ($1, $3);
            $val =~ s/%/.*/g;
            my @data = fetchValues( $table, $field );
            foreach my $d (@data) {
                $toEval{$d} = qq{"$d" =~ /^$val\$/};
            }
        } else {
	    print "Invalid where condition: $cond\n";
	    return ();
	}


        while( my($val, $code) = each( %toEval ) ) {
            my $isTrue = $code ? eval( $code ) : 1;
            die "Error: $@" if $@;
            print "eval()ed $code and got $isTrue\n" if $code && $DEBUG;
            next unless $isTrue;

            if( $field eq primaryKeyName( $table ) ) {
                my $data = fetchFromKey( $table, $val, $field );
                if( $data ) {
                    $countHash{$val}++;
                }
            } else {
                my @keys = split "\n", indexLookup( $table, $field, $val );
                print "$cond yields ".($#keys+1)." results " if $DEBUG;
                foreach my $key (@keys) {
                    print "$key," if $DEBUG;
                    $countHash{$key}++;
                }
                print "\n" if $DEBUG;
            }
        }

        $count++;	
    }

    my @ret = ();
    my %ret = ();
	
    if( $count ) {
        while( my( $key, $c ) = each( %countHash ) ) {
            print "$c ?= $count\n" if $DEBUG;
            if( $c >= $count ) {
                if( fetchFromKey( $table, $key, primaryKeyName( $table ) ) ) {
                    push @ret, $key;
                    $ret{$key} = 1;
                }
            }
        }
    } else {
        @ret = fetchValues( $table, primaryKeyName( $table ) );
        foreach my $i (@ret) {
            if( fetchFromKey( $table, $i, primaryKeyName( $table ) ) ) {
                $ret{$i} = 1;
            }
        }
    }

    # now, we have a list of all the records that matched the regular criteria
    # let's try the joins - eliminate all non-matches
    my %newconds = ();
    my $field1, $field2, $table1, $table2;
    while( my ($k, $v) = each( %joins ) ) {
        print "trying $k -> $v\n" if $DEBUG;
        if( $k =~ /\./ ) {
            ($table1, $field1) = split '.', $k;
        } else {
            $table1 = $table;
            $field1 = $k;
        }
        if( $v =~ /\./ ) {
            ($table2, $field2) = split /\./, $v;
        } else {
            $table2 = $table;
            $field2 = $v;
        }
        
        print "$table1, $table2 : $field1 = $field2\n" if $DEBUG;
        if( $table1 eq $table2 ) {
            print "$table1 == $table2 - not a join\n";
            next;
        }
        unless( $table1 eq $table or $table2 eq $table ) {
            print "neither $table1 nor $table2 is $table\n";
            next;
        }
        if( $table2 eq $table ) {
            $table2 = $table1;
            my $tempfield = $field2;
            $field2 = $field1;
            $table1 = $table;
            $field1 = $tempfield;
        }
        
        foreach my $pkey (@ret) {
            my $data = fetchFromKey( $table, $pkey, $field1 );
            unless( $data ) {
                print "Error, no data found for $table:$pkey:$field1\n";
                next;
            }
            print "JOIN fetchFromKey( $table, $pkey, $field1 ) yields $data\n" if $DEBUG;
            if( $data =~ /$field1=(.+)/ ) {
                my @pkeys = fetchPrimaryKey( $table2, "$field2=$1" );
                print "fetchPrimaryKey( $table2 ... ) yields '@pkeys'\n" if $DEBUG;
                unless( @pkeys ) {
                    delete $ret{$pkey};
                }
            }
        }
    }
            
    
    return keys(%ret);
}


sub parseWhereList
{
    my $where = shift;
    $where =~ s/AND/and/g;
    
    my @where = split " and ", $where;

    return @where;
}

sub doSelect
{
    my ($what_to_select, $from, $where) = @_;

    my $table;
    if( $from =~ /,/ ) {
	#print "Multi-table joins not supported yet\n";
	#return;
        my @tables = split ",", $from;
        $table = $tables[0];
    } else {
        $table = $from;
    }
    if( $tables{$table} ) {
	if( $what_to_select eq '*' ) {
            my @fs = @{$tables{$table}{"fields"}};
            print "@fs\n" if $DEBUG > 4;
	    $what_to_select = join ',', @fs;
	} 
        @whats = split ',', $what_to_select  or ($what_to_select);
    } else {
	print "No such table $table\n";
        return;
    }

    print "what is $what_to_select and where is $where\n";
    @results = fetchPrimaryKey( $table, parseWhereList( $where ) );

    if( $#results > -1 ) {
	print "Found ".($#results+1)." results\n";
        my $i = 0;
	foreach $key (@results) {
            $i++;
	    $data = fetchFromKey( $table, $key, @whats );
            if( $data ) {
                print "Record $i (primary key $key):\n";
                print "$data\n";
            }
	}
    } else {
	print "No results found\n";
    }

    print "You'd like me to select $what_to_select from $from where $where\n";
}

sub doUpdate
{ 
    my ( $table, $set, $where ) = @_;
    
    unless( $tables{$table} ) {
        print "No such table $table\n";
        return;
    }

    my @results = fetchPrimaryKey( $table, parseWhereList( $where ) );
    my @setList = split ',', $set;
    my %setHash = ();
    foreach my $s (@setList) {
        if( $s =~ /^\s*(.+)\s*=\s*(.+)\s*$/ ) {
            print "UPDATE Gonna set $1 to $2\n" if $DEBUG;
            $setHash{$1} = $2;
        } else {
            print "Invalid set value: $s\n";
        }
    }

    foreach my $r (@results) {
        my $pkey = primaryKeyName( $table );

        my @data = split "\n", fetchFromKey( $table, $r, $pkey, keys(%setHash) );
        my $changecount = 0;
        foreach my $d (@data) {
            my ($k,$v) = split '=', $d;
            print "Testing $k\n";
            if( $setHash{$k} ) {
                print "Updating $k to $setHash{$k}\n" if $DEBUG;
                $d =~ s/$k=$v/$k=$setHash{$k}/;
                unless( $k eq $pkey ) {
                    updateIndex( $table, $k, $v, $r );
                }
                updateValues( $table, $k, $v );
                
            } 
        }

        my $fkey = $kskprefix."$table/$pkey/$r";
        my $ver = fetchLatestVersionNumber( $fkey ) + 1;
        
        if( freenetInsert( "$fkey/$ver", @data ) ) {
            print "Updated $table $r\n";
            $versionNumberCache{$fkey} = $ver;
        } else {
            print "Error inserting data $values\n";
        }
    }
}

sub deleteRecords
{
    my ( $table, $where ) = @_;

    unless( $tables{$table} ) {
        print "No such table $table\n";
        return;
    }

    my @results = fetchPrimaryKey( $table, parseWhereList( $where ) );

    my $pkey = primaryKeyName( $table );
    foreach my $r (@results) {
        my $fkey = $kskprefix."$table/$pkey/$r";
        my $ver = fetchLatestVersionNumber( $fkey ) + 1;
        
        if( freenetInsert( "$fkey/$ver", () ) ) {
            print "Deleted $pkey = $r from $table\n";
            $versionNumberCache{$fkey} = $ver;
        } else {
            print "Error inserting data $values\n";
        }
    }
}
sub freenetRequest
{
    my $key = shift;
    my $version = shift;
    my $htl = 1;

    $key = "$key/$version" if $version;

    if( defined( $requestCache{$key} ) ) {
        $cache = @requestCache{$key};

        print "Found cached entry for $key $cache\n" if $DEBUG;
        return $cache;
    }
    
    $requestCache{$key} = "";
    my $tmpfile = $temp_dir . "request-temp.txt";

    if( -e $tmpfile ) {
        print "$tmpfile exists, aborting\n";
        return "";
    }

    my $cmd = sprintf( $freenet_rcmd, $htl, $key, $tmpfile );
    if( $UNIX ) {
        my $cmd = sprintf( "$freenet_rcmd 2&> /dev/null", $htl, $key, $tmpfile );
    }
    print "Requesting: $key\n" if $DEBUG;
    my $res = system( $cmd );
    print "Request result was $res [$version]\n" if $DEBUG;
    if( $res ) {
        print "freenetRequest failed for $key\n" if $DEBUG;
        return "";
    }

    open INFILE, $tmpfile;
    my $ret = "";
    while( my $l = <INFILE> ) {
        $ret .= $l;
        print "Found value: $l" if $DEBUG;
    }
    close INFILE;
    unlink $tmpfile;
    $requestCache{$key} = $ret;
    return $ret;
}

sub freenetInsert
{
    my $key = shift;
    my @data = @_;

    my $data_out;
    if( $#data ) {
        $data_out = join "\n", @data;
    } else {
        $data_out = $data[0];
    }

    $data_out .= "\n";
    print "DATA_OUT is $data_out" if $DEBUG > 1;
    
    my $tmpfile = $temp_dir . "insert-temp.txt";

    if( -e $tmpfile ) {
        print "$tmpfile exists, aborting\n";
        unlink $tmpfile;
        return ();
    }

    # strip out extra newlines
    while( $data_out =~ s/\n\n/\n/g ) {}

    open OUTFILE, "> $tmpfile" or die $!;
    print OUTFILE $data_out;
    close OUTFILE;

    my $cmd = "$freenet_icmd freenet:$key $tmpfile";
    if( $UNIX ) {
        my $cmd = "$freenet_icmd freenet:$key $tmpfile 2&> /dev/null";
    }

    print "Insert: $key\n" if $DEBUG;
    my $res = system( $cmd );
    print "Insert result was $res [key]\n";

    $requestCache{$key} = $data_out;

    unlink $tmpfile;
    
    return !$res;
}

sub fetchLatestVersionNumber
{
    my $key = shift;
    
    if( $versionNumberCache{$key} ) {
        my $cache = $versionNumberCache{$key};
        print "CACHE Found version number cached for $key\n" if $DEBUG;
        return $cache;
    }
        
    my $ver = 0;
    my $failures = 3;
    my $retval = 0;
    my $failuresInARow = 0;
    while( $failuresInARow < $failures ) {
        $data = freenetRequest( $key, $ver, 2 );
        print "fetchLatestVersionNumber received $data\n" if $DEBUG > 1;
        if( $data ) {
            $failuresInARow = 0;
            $retval = $ver;
        } else {
            $failuresInARow++;
            print "$failuresInARow failures in a row, ver = $retval\n" if $DEBUG > 1;
        }
        $ver++;

    }
    $versionNumberCache{$key} = $retval;
    return $retval;
}

# fetch all values for this table/field pair
sub fetchValues
{
    my( $table, $field ) = @_;
    my $key = $kskprefix."values/$table/$field";
    my $ver = fetchLatestVersionNumber( $key );
    my @data = split "\n", freenetRequest( $key, $ver );

    return @data;
}

sub updateValues
{
    my $i = 0;
    foreach my $item (@_) {
        if( $item =~ m/^\s*('")(.+)\1\s*/ ) {
           $_[$i] = $1;
        }
        $i++;
    } 

    my( $table, $field, $value, $delete ) = @_;

    #my @data = fetchValues( $table, $field )
    my $key = $kskprefix."values/$table/$field";
    my $ver = fetchLatestVersionNumber( $key );
    my @data = split "\n", freenetRequest( $key, $ver );
    foreach my $d (@data) {
        if( $d eq $value ) {
            if( $delete ) {
               undef $d;
            } else {
              print "$value already in $table:$field value list, returning\n" if $DEBUG;
              return 1;
            }
        }
    }

    push @data, $value;

    $ver++;

    unless( freenetInsert( "$key/$ver", @data ) ) {
        print "Failed to insert values for $key\n";
        return 0;
    } 

    $versionNumberCache{$key} = $ver;
    return 1;
}


sub updateIndex
{
    my $i = 0;
    foreach my $item (@_) {
        if( $item =~ m/^\s*('")(.+)\1\s*/ ) {
           $_[$i] = $1;
        }
        $i++;
    } 

    # extra must be either 'delete' or 'unique'
    my( $table, $indexname, $value, $pkey, $extra ) = @_;

    $$extra = 1 if $extra;
    my $safevalue = $value;
    $safevalue =~ tr/A-Za-z_0-9//cd;

    unless( $table and $indexname and $value and $pkey ) {
        print "Error: invalid parameters to updateIndex( $table, $indexname, $value, $pkey, $extra )\n";
        return 0;
    }

    my $key = $kskprefix."index/$table/$indexname/$safevalue";
    my $ver = fetchLatestVersionNumber( $key );
    my $data = freenetRequest( $key, $ver );

    my @data = split "\n", $data;
    foreach my $d (@data) {
        if( $d == $pkey ) {
            if( $delete ) {
              undef $d;
            } else {
               print "$pkey already in index, returning\n" if $DEBUG;
               return 1;
            }
        } elsif( $unique ) {
          print "Unique index $indexname already has value $safevalue\n";
          return 0;
        }
    }

    push @data, $pkey;

    $ver++;

    unless( freenetInsert( "$key/$ver", @data ) ) {
        print "Failed to insert index for $key\n";
        return 0;
    } 

    $versionNumberCache{$key} = $ver;
    return 1;

}

sub doInsert
{
    my ($table, $keys, $values ) = @_;

    if( %table = $student{$table} ) {
	@fields = @{$tables{$table}{"fields"}};
    } else {
	die "no such table $table";
    }
    if( $keys ) {
	@keys = split ',', $keys;
    } else {
	@keys = @fields;
	$keys = join ',', @fields;
    }

    @vals = split ',', $values;

    unless( $#keys == $#vals ) {
        print "Fields $#keys does not match $#vals $values";
        return;
    }
    
    my %insert_hash;
    for( $i = 0; $i <= $#keys; $i++ ) {
        my $k = $keys[$i];
        my $v = $vals[$i];
        $insert_hash{$k} = $v;
    }

    $pkey = primaryKeyName( $table );
    my $pkeyValue = "";
    if( $pkey =~ /,/ ) {
        my @pkeys = split ',', $pkey;
        foreach my $p (@pkeys) {
            unless( $insert_hash{$p} ) {
                print "Cannot do an insert without a value for primary key '$p'\n";
                return 0;
            }
            $pkeyValue .= $insert_hash{$p};
        }
    } else {
        unless( $insert_hash{$pkey} ) {
            print "Cannot do an insert without a value for primary key '$pkey'\n";
            return;
        }
        $pkeyValue = $insert_hash{$pkey};
    }

    my @data = ();
    while( my( $k, $v) = each( %insert_hash ) ) {
        unshift @data, "$k=$v";
        print "$k = $v\n";
        unless( $k eq $pkey ) {
            updateIndex( $table, $k, $v, $pkeyValue );
        }
        updateValues( $table, $k, $v );
    }

    print "Updating indexes, if any\n" if $DEBUG > 1;
    $insert_hash{$pkey} = $pkeyValue;
    my %indexes = %{$tables{$table}{indexes}};
    foreach my $indexname (keys(%indexes)) {
        my $unique = $indexes{$indexname}{unique} ? "unique" : 0;
        my $indexfields = $indexes{$indexname}{fields};
        my $val = "";
        print "Processing index $indexname ($indexfields)\n" if $DEBUG;
        foreach my $f (split ",",$indexfields ) {
            $val .= $insert_hash{$f};
        }
        unless( updateIndex( $table, $indexfields, $val, $pkeyValue, $unique ) ) {
            print "Error updating index $indexname ($indexfields) for $table\n";
            return 0;
        }

    }

    my $fkey = $kskprefix."$table/$pkey/$pkeyValue";
    my $ver = fetchLatestVersionNumber( $fkey ) + 1;
    
    if( freenetInsert( "$fkey/$ver", @data ) ) {
        print "Inserted $values into $table [keys = $keys]\n";
        $versionNumberCache{$fkey} = $ver;
    } else {
        print "Error inserting data $values\n";
    }
}

sub createTable
{
    my( $table, $fieldlist ) = @_;

    my @fields = split ',', $fieldlist;
    print "Field list is $fieldlist\n" if $DEBUG;

    $table = strip( $table );
    $tables{$table}{defined} = 1;

    my %indexes = ();
    if( $fieldlist =~ /PRIMARY\s+KEY\s*\((.+?)\)/i ) {
        my $pkey = $1;
        #$fieldlist =~ s///;
        if( $pkey =~ /,/ ) {
            #print "Error: Multi-field primary keys not supported yet\n";
            #return;
        }

        $indexes{"primary_key"} = { name => "primary_key", fields => $pkey, unique => 0 };
        $tables{$table}{"primary_key"} = $pkey;
        print "Found primary key $pkey\n" if $DEBUG;
    }

    my @fs = ();
    my %fsHash = ();

    #foreach my $f (@fields) {
    $fieldlist .= ",";
    while( $fieldlist =~ /\s*([^,]+?(\s*\(\w+?,\w+?\))?)\s*,/g ) {
        $f = $1;
        $f = strip( $f );
        print "Trying $f\n" if $DEBUG >4 ;
        if( $f =~ /PRIMARY KEY/i ) {
            next;
        } elsif( $f =~ /\s*KEY\s+(.+)\s+\((.+)\)\s*/i ) {
            print "KEY Found key $1 ($2)\n" if $DEBUG > 2;
            $indexes{$1} = { name => $1, fields => $2, unique => 0 };
            next;
        } elsif( $f =~ /\s*UNIQUE\s+(.+)\s+\((.+)\)\s*/i ) {
            print "KEY Found unique key $1 ($2)\n" if $DEBUG > 2;
            $indexes{$1} = { name => $1, fields => $2, unique => 1 };
            next;
        } elsif( $f =~ /^\s*(\w+)\s*(.*)\s*$/ ) {
            print "FIELD Found $name [$type] for $table\n" if $DEBUG;
            $name = $1;
            $type = $2;
            unless( defined( $fsHash{$name} ) ) {
                push @fs, $name;
                $fsHash{$name} = $type;
            }
        }
    }
    
    $tables{$table}{indexes} =  { %indexes };

    print "@fs\n" if $DEBUG > 4;
    $tables{$table}{"fields"} = [@fs];

    my $fkey = $kskprefix . "schema";
    my $ver = fetchLatestVersionNumber( $fkey );
    
    my $data = freenetRequest( $fkey, $ver );
    my $new =  "CREATE TABLE $table ($fieldlist);";
    if( $data =~ /$new/ ) {
        print "Exact schema already exists for $table\n";
        return 1;
    }

    if( $data =~ /(CREATE TABLE $table .+?;)/i ) {
        my $old = $1;
        if( $old eq $new ) {
            # we're done
            print "Exact schema already exists for $table\n";
            return 1;
        }
        print "Replace '$old' with '$new'\n" if $DEBUG > 2;
        $data =~ s//$new/;
    } else {
        $data .= $new;
    }
    
    $ver++;

    if( freenetInsert( "$fkey/$ver", $data ) ) {
        print "Inserted schema for $table\n" if $DEBUG > 2;
        $versionNumberCache{$fkey} = $ver;
    } else {
        print "Error inserting data $values\n";
    }    
    
    
    print "Created table '$table' with @fs\n";
}