triggers.sql (10722B)
1 -- 18 2 3 -- Copyright (c) 2009 Center for History and New Media 4 -- George Mason University, Fairfax, Virginia, USA 5 -- http://zotero.org 6 -- 7 -- This file is part of Zotero. 8 -- 9 -- Zotero is free software: you can redistribute it and/or modify 10 -- it under the terms of the GNU Affero General Public License as published by 11 -- the Free Software Foundation, either version 3 of the License, or 12 -- (at your option) any later version. 13 -- 14 -- Zotero is distributed in the hope that it will be useful, 15 -- but WITHOUT ANY WARRANTY; without even the implied warranty of 16 -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 17 -- GNU Affero General Public License for more details. 18 -- 19 -- You should have received a copy of the GNU Affero General Public License 20 -- along with Zotero. If not, see <http://www.gnu.org/licenses/>. 21 22 23 -- ";---" is an ugly hack for Zotero.DB.executeSQLFile() 24 25 -- Triggers to validate date field 26 DROP TRIGGER IF EXISTS insert_date_field; 27 CREATE TRIGGER insert_date_field BEFORE INSERT ON itemData 28 FOR EACH ROW WHEN NEW.fieldID IN (14, 27, 52, 96, 100) 29 BEGIN 30 SELECT CASE 31 CAST(SUBSTR((SELECT value FROM itemDataValues WHERE valueID=NEW.valueID), 1, 4) AS INT) BETWEEN 0 AND 9999 AND 32 SUBSTR((SELECT value FROM itemDataValues WHERE valueID=NEW.valueID), 5, 1) = '-' AND 33 CAST(SUBSTR((SELECT value FROM itemDataValues WHERE valueID=NEW.valueID), 6, 2) AS INT) BETWEEN 0 AND 12 AND 34 SUBSTR((SELECT value FROM itemDataValues WHERE valueID=NEW.valueID), 8, 1) = '-' AND 35 CAST(SUBSTR((SELECT value FROM itemDataValues WHERE valueID=NEW.valueID), 9, 2) AS INT) BETWEEN 0 AND 31 36 WHEN 0 THEN RAISE (ABORT, 'Date field must begin with SQL date') END;--- 37 END; 38 39 DROP TRIGGER IF EXISTS update_date_field; 40 CREATE TRIGGER update_date_field BEFORE UPDATE ON itemData 41 FOR EACH ROW WHEN NEW.fieldID IN (14, 27, 52, 96, 100) 42 BEGIN 43 SELECT CASE 44 CAST(SUBSTR((SELECT value FROM itemDataValues WHERE valueID=NEW.valueID), 1, 4) AS INT) BETWEEN 0 AND 9999 AND 45 SUBSTR((SELECT value FROM itemDataValues WHERE valueID=NEW.valueID), 5, 1) = '-' AND 46 CAST(SUBSTR((SELECT value FROM itemDataValues WHERE valueID=NEW.valueID), 6, 2) AS INT) BETWEEN 0 AND 12 AND 47 SUBSTR((SELECT value FROM itemDataValues WHERE valueID=NEW.valueID), 8, 1) = '-' AND 48 CAST(SUBSTR((SELECT value FROM itemDataValues WHERE valueID=NEW.valueID), 9, 2) AS INT) BETWEEN 0 AND 31 49 WHEN 0 THEN RAISE (ABORT, 'Date field must begin with SQL date') END;--- 50 END; 51 52 53 -- Don't allow empty creators 54 DROP TRIGGER IF EXISTS insert_creatorData; 55 CREATE TRIGGER insert_creators BEFORE INSERT ON creators 56 FOR EACH ROW WHEN NEW.firstName='' AND NEW.lastName='' 57 BEGIN 58 SELECT RAISE (ABORT, 'Creator names cannot be empty');--- 59 END; 60 61 DROP TRIGGER IF EXISTS update_creatorData; 62 CREATE TRIGGER update_creators BEFORE UPDATE ON creators 63 FOR EACH ROW WHEN NEW.firstName='' AND NEW.lastName='' 64 BEGIN 65 SELECT RAISE (ABORT, 'Creator names cannot be empty');--- 66 END; 67 68 69 -- Don't allow collection parents in different libraries 70 DROP TRIGGER IF EXISTS fki_collections_parentCollectionID_libraryID; 71 CREATE TRIGGER fki_collections_parentCollectionID_libraryID 72 BEFORE INSERT ON collections 73 FOR EACH ROW BEGIN 74 SELECT RAISE(ABORT, 'insert on table "collections" violates foreign key constraint "fki_collections_parentCollectionID_libraryID"') 75 WHERE NEW.parentCollectionID IS NOT NULL AND 76 NEW.libraryID != (SELECT libraryID FROM collections WHERE collectionID = NEW.parentCollectionID);--- 77 END; 78 79 DROP TRIGGER IF EXISTS fku_collections_parentCollectionID_libraryID; 80 CREATE TRIGGER fku_collections_parentCollectionID_libraryID 81 BEFORE UPDATE ON collections 82 FOR EACH ROW BEGIN 83 SELECT RAISE(ABORT, 'update on table "collections" violates foreign key constraint "fku_collections_parentCollectionID_libraryID"') 84 WHERE NEW.parentCollectionID IS NOT NULL AND 85 NEW.libraryID != (SELECT libraryID FROM collections WHERE collectionID = NEW.parentCollectionID);--- 86 END; 87 88 89 -- collectionItems libraryID 90 DROP TRIGGER IF EXISTS fki_collectionItems_libraryID; 91 CREATE TRIGGER fki_collectionItems_libraryID 92 BEFORE INSERT ON collectionItems 93 FOR EACH ROW BEGIN 94 SELECT RAISE(ABORT, 'insert on table "collectionItems" violates foreign key constraint "fki_collectionItems_libraryID"') 95 WHERE (SELECT libraryID FROM collections WHERE collectionID = NEW.collectionID) != (SELECT libraryID FROM items WHERE itemID = NEW.itemID);--- 96 END; 97 98 DROP TRIGGER IF EXISTS fku_collectionItems_libraryID; 99 CREATE TRIGGER fku_collectionItems_libraryID 100 BEFORE UPDATE ON collectionItems 101 FOR EACH ROW BEGIN 102 SELECT RAISE(ABORT, 'update on table "collectionItems" violates foreign key constraint "fku_collectionItems_libraryID"') 103 WHERE (SELECT libraryID FROM collections WHERE collectionID = NEW.collectionID) != (SELECT libraryID FROM items WHERE itemID = NEW.itemID);--- 104 END; 105 106 107 -- Don't allow child items to exist explicitly in collections 108 DROP TRIGGER IF EXISTS fki_collectionItems_itemID_parentItemID; 109 CREATE TRIGGER fki_collectionItems_itemID_parentItemID 110 BEFORE INSERT ON collectionItems 111 FOR EACH ROW BEGIN 112 SELECT RAISE(ABORT, 'insert on table "collectionItems" violates foreign key constraint "fki_collectionItems_itemID_parentItemID"') 113 WHERE NEW.itemID IN (SELECT itemID FROM itemAttachments WHERE parentItemID IS NOT NULL UNION SELECT itemID FROM itemNotes WHERE parentItemID IS NOT NULL);--- 114 END; 115 116 DROP TRIGGER IF EXISTS fku_collectionItems_itemID_parentItemID; 117 CREATE TRIGGER fku_collectionItems_itemID_parentItemID 118 BEFORE UPDATE OF itemID ON collectionItems 119 FOR EACH ROW BEGIN 120 SELECT RAISE(ABORT, 'update on table "collectionItems" violates foreign key constraint "fku_collectionItems_itemID_parentItemID"') 121 WHERE NEW.itemID IN (SELECT itemID FROM itemAttachments WHERE parentItemID IS NOT NULL UNION SELECT itemID FROM itemNotes WHERE parentItemID IS NOT NULL);--- 122 END; 123 124 -- When making a standalone attachment a child, remove from any collections 125 DROP TRIGGER IF EXISTS fku_itemAttachments_parentItemID_collectionItems_itemID; 126 CREATE TRIGGER fku_itemAttachments_parentItemID_collectionItems_itemID 127 BEFORE UPDATE OF parentItemID ON itemAttachments 128 FOR EACH ROW WHEN OLD.parentItemID IS NULL AND NEW.parentItemID IS NOT NULL BEGIN 129 DELETE FROM collectionItems WHERE itemID = NEW.itemID;--- 130 END; 131 132 -- When making a standalone note a child, remove from any collections 133 DROP TRIGGER IF EXISTS fku_itemNotes_parentItemID_collectionItems_itemID; 134 CREATE TRIGGER fku_itemNotes_parentItemID_collectionItems_itemID 135 BEFORE UPDATE OF parentItemID ON itemNotes 136 FOR EACH ROW WHEN OLD.parentItemID IS NULL AND NEW.parentItemID IS NOT NULL BEGIN 137 DELETE FROM collectionItems WHERE itemID = NEW.itemID;--- 138 END; 139 140 141 -- itemAttachments 142 DROP TRIGGER IF EXISTS fki_itemAttachments; 143 CREATE TRIGGER fki_itemAttachments 144 BEFORE INSERT ON itemAttachments 145 FOR EACH ROW BEGIN 146 SELECT RAISE(ABORT, 'insert on table "itemAttachments" violates foreign key constraint "fki_itemAttachments"') 147 WHERE NEW.parentItemID IS NOT NULL AND 148 (SELECT libraryID FROM items WHERE itemID = NEW.itemID) != (SELECT libraryID FROM items WHERE itemID = NEW.parentItemID);--- 149 150 -- Make sure this is an attachment item 151 SELECT RAISE(ABORT, 'item is not an attachment') 152 WHERE (SELECT itemTypeID FROM items WHERE itemID = NEW.itemID) != 14;--- 153 154 -- Make sure parent is a regular item 155 SELECT RAISE(ABORT, 'parent is not a regular item') 156 WHERE NEW.parentItemID IS NOT NULL AND (SELECT itemTypeID FROM items WHERE itemID = NEW.parentItemID) IN (1,14);--- 157 158 -- If child, make sure attachment is not in a collection 159 SELECT RAISE(ABORT, 'collection item must be top level') 160 WHERE NEW.parentItemID IS NOT NULL AND (SELECT COUNT(*) FROM collectionItems WHERE itemID=NEW.itemID)>0;--- 161 END; 162 163 DROP TRIGGER IF EXISTS fku_itemAttachments; 164 CREATE TRIGGER fku_itemAttachments 165 BEFORE UPDATE ON itemAttachments 166 FOR EACH ROW BEGIN 167 SELECT RAISE(ABORT, 'update on table "itemAttachments" violates foreign key constraint "fku_itemAttachments"') 168 WHERE NEW.parentItemID IS NOT NULL AND 169 (SELECT libraryID FROM items WHERE itemID = NEW.itemID) != (SELECT libraryID FROM items WHERE itemID = NEW.parentItemID);--- 170 171 -- Make sure parent is a regular item 172 SELECT RAISE(ABORT, 'parent is not a regular item') 173 WHERE NEW.parentItemID IS NOT NULL AND (SELECT itemTypeID FROM items WHERE itemID = NEW.parentItemID) IN (1,14);--- 174 END; 175 176 177 -- itemNotes 178 DROP TRIGGER IF EXISTS fki_itemNotes; 179 CREATE TRIGGER fki_itemNotes 180 BEFORE INSERT ON itemNotes 181 FOR EACH ROW BEGIN 182 SELECT RAISE(ABORT, 'insert on table "itemNotes" violates foreign key constraint "fki_itemNotes_libraryID"') 183 WHERE NEW.parentItemID IS NOT NULL AND 184 (SELECT libraryID FROM items WHERE itemID = NEW.itemID) != (SELECT libraryID FROM items WHERE itemID = NEW.parentItemID);--- 185 186 -- Make sure this is a note or attachment item 187 SELECT RAISE(ABORT, 'item is not a note or attachment') WHERE 188 (SELECT itemTypeID FROM items WHERE itemID = NEW.itemID) NOT IN (1,14);--- 189 190 -- Make sure parent is a regular item 191 SELECT RAISE(ABORT, 'parent is not a regular item') WHERE 192 NEW.parentItemID IS NOT NULL AND (SELECT itemTypeID FROM items WHERE itemID = NEW.parentItemID) IN (1,14);--- 193 194 -- If child, make sure note is not in a collection 195 SELECT RAISE(ABORT, 'collection item must be top level') WHERE 196 NEW.parentItemID IS NOT NULL AND (SELECT COUNT(*) FROM collectionItems WHERE itemID=NEW.itemID)>0;--- 197 END; 198 199 DROP TRIGGER IF EXISTS fku_itemNotes; 200 CREATE TRIGGER fku_itemNotes 201 BEFORE UPDATE ON itemNotes 202 FOR EACH ROW BEGIN 203 SELECT RAISE(ABORT, 'update on table "itemNotes" violates foreign key constraint "fku_itemNotes"') 204 WHERE NEW.parentItemID IS NOT NULL AND 205 (SELECT libraryID FROM items WHERE itemID = NEW.itemID) != (SELECT libraryID FROM items WHERE itemID = NEW.parentItemID);--- 206 207 -- Make sure parent is a regular item 208 SELECT RAISE(ABORT, 'parent is not a regular item') WHERE 209 NEW.parentItemID IS NOT NULL AND (SELECT itemTypeID FROM items WHERE itemID = NEW.parentItemID) IN (1,14);--- 210 END; 211 212 213 -- Make sure tags aren't empty 214 DROP TRIGGER IF EXISTS fki_tags; 215 CREATE TRIGGER fki_tags 216 BEFORE INSERT ON tags 217 FOR EACH ROW BEGIN 218 SELECT RAISE(ABORT, 'Tag cannot be blank') 219 WHERE TRIM(NEW.name)='';--- 220 END; 221 222 DROP TRIGGER IF EXISTS fku_tags; 223 CREATE TRIGGER fku_tags 224 BEFORE UPDATE OF name ON tags 225 FOR EACH ROW BEGIN 226 SELECT RAISE(ABORT, 'Tag cannot be blank') 227 WHERE TRIM(NEW.name)='';--- 228 END;