SQL: Fix editing + func.sql + triggers.sql + autocreate editing funcs

This changes quite a bit to the way the editing functions work. Because
these functions are very repetitive and it's easy to keep things out of
sync, I created a script to generate them automatically. I had to rename
a few function and table names for consistency to make this work.

Since database entries don't have a 'latest' column anymore, and since
the order in which tables are updated doesn't have to be fixed, I
dropped many of the SQL triggers and replaced them with a
edit_committed() function which is called from edit_*_commit() and
checks for stuff to be done.

Don't forget to run 'make' before importing the update script.

1 files changed, 16 insertions, 10 deletions

diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index b43079dc..960b1978 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -1,6 +1,6 @@
 -- Convention for database items with version control:
 --
---   CREATE TABLE items (
+--   CREATE TABLE items ( -- dbentry_type=x
 --     id        SERIAL PRIMARY KEY,
 --     locked    boolean NOT NULL DEFAULT FALSE,
 --     hidden    boolean NOT NULL DEFAULT FALSE,
@@ -11,10 +11,12 @@
 --     -- item-specific columns here
 --   );
 --
--- The history of the 'locked' and 'hidden' flags is recorded in the changes
--- table.  It's possible for 'items' to have more item-specific columns than
--- 'items_hist'. Some columns are caches or otherwise autogenerated, and do not
--- need to be versioned.
+-- The '-- dbentry_type=x' comment is required, and is used by
+-- util/sqleditfunc.pl to generate the correct editing functions.  The history
+-- of the 'locked' and 'hidden' flags is recorded in the changes table.  It's
+-- possible for 'items' to have more item-specific columns than 'items_hist'.
+-- Some columns are caches or otherwise autogenerated, and do not need to be
+-- versioned.
 --
 -- item-related tables work roughly the same:
 --
@@ -31,6 +33,10 @@
 -- tables related to the item are just a cache/view into the latest versions.
 -- All modifications to the item tables has to go through the edit_* functions
 -- in func.sql, these are also responsible for keeping things synchronized.
+--
+-- Note: Every CREATE TABLE clause and each column should be on a separate
+-- line. This file is parsed by util/sqleditfunc.pl, and it doesn't implement a
+-- full SQL query parser.
 
 
 -- affiliate_links
@@ -74,7 +80,7 @@ CREATE TABLE changes (
 );
 
 -- chars
-CREATE TABLE chars (
+CREATE TABLE chars ( -- dbentry_type=c
   id         SERIAL PRIMARY KEY,
   locked     boolean NOT NULL DEFAULT FALSE,
   hidden     boolean NOT NULL DEFAULT FALSE,
@@ -172,7 +178,7 @@ CREATE TABLE notifications (
 );
 
 -- producers
-CREATE TABLE producers (
+CREATE TABLE producers ( -- dbentry_type=p
   id         SERIAL PRIMARY KEY,
   locked     boolean NOT NULL DEFAULT FALSE,
   hidden     boolean NOT NULL DEFAULT FALSE,
@@ -224,7 +230,7 @@ CREATE TABLE quotes (
 );
 
 -- releases
-CREATE TABLE releases (
+CREATE TABLE releases ( -- dbentry_type=r
   id         SERIAL PRIMARY KEY,
   locked     boolean NOT NULL DEFAULT FALSE,
   hidden     boolean NOT NULL DEFAULT FALSE,
@@ -377,7 +383,7 @@ CREATE TABLE sessions (
 );
 
 -- staff
-CREATE TABLE staff (
+CREATE TABLE staff ( -- dbentry_type=s
   id         SERIAL PRIMARY KEY,
   locked     boolean NOT NULL DEFAULT FALSE,
   hidden     boolean NOT NULL DEFAULT FALSE,
@@ -574,7 +580,7 @@ CREATE TABLE users_prefs (
 );
 
 -- vn
-CREATE TABLE vn (
+CREATE TABLE vn ( -- dbentry_type=v
   id         SERIAL PRIMARY KEY,
   locked     boolean NOT NULL DEFAULT FALSE,
   hidden     boolean NOT NULL DEFAULT FALSE,