1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
|
#!/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$}{}; }
use lib "$ROOT/lib";
use VNDB::Schema;
my $schema = VNDB::Schema::schema;
my $template = join '', <DATA>;
sub gensql {
my $item = shift;
# table_name_without_hist => [ column_names_without_chid ]
my %ts = map
+($_, [ map "\"$_->{name}\"", grep $_->{name} !~ /^chid$/, @{$schema->{"${_}_hist"}{cols}} ]),
map /^${item}_/ && /^(.+)_hist$/ ? $1 : (), keys %$schema;
my %replace = ( item => $item, itemtype => $schema->{$item}{dbentry_type} );
$replace{createtemptables} = join "\n", map sprintf(
" CREATE TEMPORARY TABLE edit_%s (LIKE %s INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING GENERATED);\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 = nitemid;\n".
" INSERT INTO %1\$s (id, %2\$s) SELECT nitemid, %2\$s FROM edit_%1\$s;\n".
" INSERT INTO %1\$s_hist (chid, %2\$s) SELECT nchid, %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 nchid, %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 = nitemid;",
$item, join(', ', @{$ts{$item}}), join(', ', map "$_ = x.$_", @{$ts{$item}});
$template =~ s/{([a-z]+)}/$replace{$1}/gr;
}
open my $F, '>', "$ROOT/sql/editfunc.sql" or die $!;
print $F "-- Automatically generated by util/sqleditfunc.pl. DO NOT EDIT.\n";
print $F gensql $_ for sort grep $schema->{$_}{dbentry_type}, keys %$schema;
__DATA__
CREATE OR REPLACE FUNCTION edit_{itemtype}_init(xid vndbid, 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(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(out nchid integer, out nitemid vndbid, out nrev integer) AS $$
BEGIN
IF (SELECT COUNT(*) FROM edit_{item}) <> 1 THEN
RAISE 'edit_{item} must have exactly one row!';
END IF;
SELECT itemid INTO nitemid FROM edit_revision;
-- figure out revision number
SELECT MAX(rev)+1 INTO nrev FROM changes WHERE itemid = nitemid;
SELECT COALESCE(nrev, 1) INTO nrev;
-- insert DB item
IF nitemid IS NULL THEN
INSERT INTO {item} DEFAULT VALUES RETURNING id INTO nitemid;
END IF;
-- insert change
INSERT INTO changes (itemid, rev, requester, ip, comments, ihid, ilock)
SELECT nitemid, nrev, requester, ip, comments, ihid, ilock FROM edit_revision RETURNING id INTO nchid;
-- insert data
{copyfromtemp}
{copymainfromtemp}
PERFORM edit_committed(nchid, nitemid, nrev);
END;
$$ LANGUAGE plpgsql;
|
