diff options
| author | yorhel <yorhel@1fe2e327-d9db-4752-bcf7-ef0cb4a1748b> | 2008-08-20 10:13:48 +0000 |
|---|---|---|
| committer | yorhel <yorhel@1fe2e327-d9db-4752-bcf7-ef0cb4a1748b> | 2008-08-20 10:13:48 +0000 |
| commit | 3dc8780c15985ff86699addff1f4dbe43c6be19a (patch) | |
| tree | cd1eda194c47776071c32318f908f46b69dd9b20 /util | |
| parent | cefb812260f70ba7976723b175b632c24888b877 (diff) | |
Another internal database change: changes.causedby accepts NULL values and properly references changes.id
git-svn-id: svn://vndb.org/vndb@99 1fe2e327-d9db-4752-bcf7-ef0cb4a1748b
Diffstat (limited to 'util')
| -rw-r--r-- | util/dump.sql | 3 | ||||
| -rw-r--r-- | util/updates/update_1.22.sql | 10 |
2 files changed, 12 insertions, 1 deletions
diff --git a/util/dump.sql b/util/dump.sql index abb04935..cca3eaee 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -38,7 +38,7 @@ CREATE TABLE changes ( requester integer NOT NULL DEFAULT 0, ip inet NOT NULL DEFAULT '0.0.0.0', comments text NOT NULL DEFAULT '', - causedby integer NOT NULL DEFAULT 0 + causedby integer ); -- producers @@ -275,6 +275,7 @@ CREATE TABLE wlists ( ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE changes ADD FOREIGN KEY (causedby) REFERENCES changes (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE producers ADD FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE producers_rev ADD FOREIGN KEY (id) REFERENCES changes (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE producers_rev ADD FOREIGN KEY (pid) REFERENCES producers (id) DEFERRABLE INITIALLY DEFERRED; diff --git a/util/updates/update_1.22.sql b/util/updates/update_1.22.sql index cc475b32..96228955 100644 --- a/util/updates/update_1.22.sql +++ b/util/updates/update_1.22.sql @@ -12,3 +12,13 @@ ALTER TABLE vn ALTER COLUMN rgraph DROP NOT NULL; ALTER TABLE vn ALTER COLUMN rgraph SET DEFAULT NULL; UPDATE vn SET rgraph = NULL WHERE rgraph = 0; + +-- add foreign table constraint to changes.causedby +ALTER TABLE changes ALTER COLUMN causedby DROP NOT NULL; +ALTER TABLE changes ALTER COLUMN causedby SET DEFAULT NULL; +UPDATE changes c SET causedby = NULL + WHERE causedby = 0 + -- yup, there are some problems caused by deleted revisions in older versions of the site + OR NOT EXISTS(SELECT 1 FROM changes WHERE c.causedby = id); +ALTER TABLE changes ADD FOREIGN KEY (causedby) REFERENCES changes (id) DEFERRABLE INITIALLY DEFERRED; + |