diff options
Diffstat (limited to 'util/sqleditfunc.pl')
-rwxr-xr-x | util/sqleditfunc.pl | 112 |
1 files changed, 112 insertions, 0 deletions
diff --git a/util/sqleditfunc.pl b/util/sqleditfunc.pl new file mode 100755 index 00000000..773214f9 --- /dev/null +++ b/util/sqleditfunc.pl @@ -0,0 +1,112 @@ +#!/usr/bin/perl + +use strict; +use warnings; +use List::Util 'any'; + +use Cwd 'abs_path'; +our $ROOT; +BEGIN { ($ROOT = abs_path $0) =~ s{/util/sqleditfunc\.pl$}{}; } + +my %tabletypes; # table_name => dbentry_type +my %tables; # table_name => [ column_names ] +my %items; # item_name => { tables_without_hist => [ data_column_names ] } + + +# Fills %tables +sub readschema { + open my $F, '<', "$ROOT/util/sql/schema.sql" or die $!; + my $table = ''; + while(<$F>) { + chomp; + if(/^\s*CREATE\s+TABLE\s+([^ ]+)/) { + $table = $1; + $tables{$table} = []; + $tabletypes{$table} = $1 if /--.*\s+dbentry_type=(.)/; + } elsif($table && /^\s+("?[^\( ]+"?)\s/ && !/^\s+PRIMARY\s+KEY/) { + push @{$tables{$table}}, $1; + } + } +} + + +sub gensql { + my($template, $item) = @_; + + # table_name_without_hist => [ column_names_without_chid ] + my %ts = map +($_, [ grep !/^chid$/, @{$tables{"${_}_hist"}} ]), map /^${item}_/ && /^(.+)_hist$/ ? $1 : (), keys %tables; + + my %replace = ( item => $item, itemtype => $tabletypes{$item} ); + $replace{createtemptables} = join "\n", map sprintf( + " CREATE TEMPORARY TABLE edit_%s (LIKE %s INCLUDING DEFAULTS INCLUDING CONSTRAINTS);\n". + " ALTER TABLE edit_%1\$s DROP COLUMN %s;", + $_, $_ eq 'staff_alias' ? ($_, 'id') : ("${_}_hist", 'chid') # staff_alias copies from the non-_hist table, because it needs the sequence + ), sort keys %ts; + $replace{temptablenames} = join ', ', map "edit_$_", sort keys %ts; + $replace{loadtemptables} = join "\n", map sprintf( + " INSERT INTO edit_%s (%s) SELECT %2\$s FROM %1\$s_hist WHERE chid = xchid;", + $_, join ', ', @{$ts{$_}}), sort keys %ts; + $replace{copyfromtemp} = join "\n", map sprintf( + " DELETE FROM %1\$s WHERE id = r.itemid;\n". + " INSERT INTO %1\$s (id, %2\$s) SELECT r.itemid, %2\$s FROM edit_%1\$s;\n". + " INSERT INTO %1\$s_hist (chid, %2\$s) SELECT r.chid, %2\$s FROM edit_%1\$s;", + $_, join ', ', @{$ts{$_}}), grep $_ ne $item, sort keys %ts; + $replace{copymainfromtemp} = sprintf + " INSERT INTO %1\$s_hist (chid, %2\$s) SELECT r.chid, %2\$s FROM edit_%1\$s;\n". + " UPDATE %1\$s SET locked = (SELECT ilock FROM edit_revision), hidden = (SELECT ihid FROM edit_revision),\n". + " %3\$s FROM edit_%1\$s x WHERE id = r.itemid;", + $item, join(', ', @{$ts{$item}}), join(', ', map "$_ = x.$_", @{$ts{$item}}); + + $template =~ s/{([a-z]+)}/$replace{$1}/g; + $template; +} + + +readschema; +my $template = join '', <DATA>; + +open my $F, '>', "$ROOT/util/sql/editfunc.sql" or die $!; +print $F "-- Automatically generated by util/sqleditfunc.pl. DO NOT EDIT.\n"; +print $F gensql $template, $_ for sort keys %tabletypes; + + +__DATA__ + +CREATE OR REPLACE FUNCTION edit_{itemtype}_init(xid integer, xrev integer) RETURNS void AS $$ +DECLARE + xchid integer; +BEGIN + -- create tables, based on existing tables + BEGIN +{createtemptables} + EXCEPTION WHEN duplicate_table THEN + TRUNCATE {temptablenames}; + END; + -- Create edit_revision table and get relevant change ID. + SELECT edit_revtable('{itemtype}', xid, xrev) INTO xchid; + -- new entry, load defaults + IF xchid IS NULL THEN + INSERT INTO edit_{item} DEFAULT VALUES; + -- otherwise, load revision + ELSE +{loadtemptables} + END IF; +END; +$$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION edit_{itemtype}_commit() RETURNS edit_rettype AS $$ +DECLARE + r edit_rettype; +BEGIN + IF (SELECT COUNT(*) FROM edit_{item}) <> 1 THEN + RAISE 'edit_{item} must have exactly one row!'; + END IF; + SELECT INTO r * FROM edit_commit(); +{copyfromtemp} +{copymainfromtemp} + PERFORM edit_committed('{itemtype}', r); + RETURN r; +END; +$$ LANGUAGE plpgsql; + |