summaryrefslogtreecommitdiff
path: root/util/sqleditfunc.pl
diff options
context:
space:
mode:
Diffstat (limited to 'util/sqleditfunc.pl')
-rwxr-xr-xutil/sqleditfunc.pl112
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;
+