commit c73513576a20a494c1270f4e0e1cc2a7e446fb67
parent 5990ac061891e5ca1173e693e632b488bb05ca0b
Author: Dan Stillman <dstillman@zotero.org>
Date: Fri, 15 Aug 2008 05:32:18 +0000
- Fix inability to add existing tags to item
- Fix error if a local tag matches a different remote tag
- Add additional triggers to enforce referential integrity
Diffstat:
3 files changed, 283 insertions(+), 12 deletions(-)
diff --git a/chrome/content/zotero/xpcom/data/tag.js b/chrome/content/zotero/xpcom/data/tag.js
@@ -280,13 +280,26 @@ Zotero.Tag.prototype.save = function () {
key
];
- var sql = "REPLACE INTO tags (" + columns.join(', ') + ") VALUES ("
- + placeholders.join(', ') + ")";
- var insertID = Zotero.DB.query(sql, sqlValues);
- if (!tagID) {
- tagID = insertID;
+ if (isNew) {
+ var sql = "INSERT INTO tags (" + columns.join(', ') + ") VALUES ("
+ + placeholders.join(', ') + ")";
+ var insertID = Zotero.DB.query(sql, sqlValues);
+ if (!tagID) {
+ tagID = insertID;
+ }
+ }
+ else {
+ // Remove tagID from beginning
+ columns.shift();
+ sqlValues.shift();
+ sqlValues.push(tagID);
+
+ var sql = "UPDATE tags SET " + columns.join("=?, ") + "=?"
+ + " WHERE tagID=?";
+ Zotero.DB.query(sql, sqlValues);
}
+
// Linked items
if (this._changed.linkedItems) {
var removed = [];
@@ -392,8 +405,7 @@ Zotero.Tag.prototype.serialize = function () {
*
* Tags.erase() should be used externally instead of this
*
- * Actual deletion of tag occurs in Zotero.Tags.purge(),
- * which is called by Tags.erase()
+ * Actual deletion of tag occurs in Zotero.Tags.purge()
*/
Zotero.Tag.prototype.erase = function () {
Zotero.debug('Deleting tag ' + this.id);
diff --git a/chrome/content/zotero/xpcom/sync.js b/chrome/content/zotero/xpcom/sync.js
@@ -135,6 +135,18 @@ Zotero.Sync = new function() {
}
+ this.removeFromUpdated = function (updated, ids) {
+ ids = Zotero.flattenArguments(ids);
+ var index;
+ for each(var id in ids) {
+ index = updated.indexOf(id);
+ if (index != -1) {
+ updated.splice(index, 1);
+ }
+ }
+ }
+
+
/**
* @param object lastSyncDate JS Date object
* @return mixed Returns object with deleted ids
@@ -647,6 +659,9 @@ Zotero.Sync.Server = new function () {
xml.updated, lastLocalSyncDate, uploadIDs
);
+ //Zotero.debug(xmlstr);
+ //throw('break');
+
if (xmlstr === false) {
Zotero.debug("Sync cancelled");
Zotero.DB.rollbackTransaction();
@@ -660,8 +675,6 @@ Zotero.Sync.Server = new function () {
Zotero.debug(xmlstr);
}
- //throw('break1');
-
Zotero.Sync.Server.lastRemoteSyncTime = response.getAttribute('timestamp');
if (!xmlstr) {
@@ -1312,6 +1325,7 @@ Zotero.Sync.Server.Data = new function() {
typeloop:
for each(var xmlNode in xml[types][type]) {
+ var isNewObject;
var localDelete = false;
// Get local object with same id
@@ -1319,6 +1333,8 @@ Zotero.Sync.Server.Data = new function() {
if (obj) {
// Key match -- same item
if (obj.key == xmlNode.@key.toString()) {
+ isNewObject = false;
+
var objDate = Zotero.Date.sqlToDate(obj.dateModified, true);
// Local object has been modified since last sync
@@ -1417,6 +1433,8 @@ Zotero.Sync.Server.Data = new function() {
// Key mismatch -- different objects with same id,
// so change id of local object
else {
+ isNewObject = true;
+
var oldID = parseInt(xmlNode.@id);
var newID = Zotero.ID.get(types, true);
@@ -1466,6 +1484,8 @@ Zotero.Sync.Server.Data = new function() {
// Object doesn't exist locally
else {
+ isNewObject = true;
+
// Check if object has been deleted locally
for each(var pair in uploadIDs.deleted[types]) {
if (pair.id != parseInt(xmlNode.@id) ||
@@ -1507,6 +1527,23 @@ Zotero.Sync.Server.Data = new function() {
// Create or overwrite locally
obj = Zotero.Sync.Server.Data['xmlTo' + Type](xmlNode, obj);
+ // If a local tag matches the name of a different remote tag,
+ // delete the local tag and add items linked to it to the
+ // matching remote tag
+ if (isNewObject && type == 'tag') {
+ var tagName = xmlNode.@name.toString();
+ var tagType = xmlNode.@type.toString()
+ ? parseInt(xmlNode.@type) : 0;
+ var linkedItems = _deleteConflictingTag(tagName, tagType, uploadIDs);
+ if (linkedItems) {
+ obj.dateModified = Zotero.DB.transactionDateTime;
+ for each(var id in linkedItems) {
+ obj.addItem(id);
+ }
+ Zotero.Sync.addToUpdated(uploadIDs.updated.tags, parseInt(xmlNode.@id));
+ }
+ }
+
if (localDelete) {
// TODO: order reconcile by parent/child?
@@ -2348,7 +2385,7 @@ Zotero.Sync.Server.Data = new function() {
}
tag.name = xmlTag.@name.toString();
- tag.type = parseInt(xmlTag.@type);
+ tag.type = xmlTag.@type.toString() ? parseInt(xmlTag.@type) : 0;
if (!skipPrimary) {
tag.dateModified = xmlTag.@dateModified.toString();
tag.key = xmlTag.@key.toString();
@@ -2361,6 +2398,38 @@ Zotero.Sync.Server.Data = new function() {
}
+ /**
+ * @param {String} name Tag name
+ * @param {Integer} type Tag type
+ * @return {Integer[]|FALSE} Array of itemIDs of items linked to
+ * deleted tag, or FALSE if no
+ * matching tag found
+ */
+ function _deleteConflictingTag(name, type, uploadIDs) {
+ var tagID = Zotero.Tags.getID(name, type);
+ if (tagID) {
+ var tag = Zotero.Tags.get(tagID);
+ var linkedItems = tag.getLinkedItems(true);
+ Zotero.Tags.erase(tagID);
+ // DEBUG: should purge() be called by Tags.erase()
+ Zotero.Tags.purge();
+
+ Zotero.Sync.removeFromUpdated(
+ uploadIDs.updated.tags, tagID
+ );
+
+ uploadIDs.deleted.tags.push({
+ id: tagID,
+ key: tag.key
+ });
+
+ return linkedItems;
+ }
+
+ return false;
+ }
+
+
function _xmlize(str) {
return str.replace(/[\u0000-\u0008\u000b\u000c\u000e-\u001f\ud800-\udfff\ufffe\uffff]/g, '\u2B1A');
}
diff --git a/triggers.sql b/triggers.sql
@@ -1,4 +1,4 @@
--- 2
+-- 3
-- Triggers to validate date field
DROP TRIGGER IF EXISTS insert_date_field;
@@ -45,7 +45,7 @@ DROP TRIGGER IF EXISTS fku_annotations_itemID_itemAttachments_itemID;
CREATE TRIGGER fku_annotations_itemID_itemAttachments_itemID
BEFORE UPDATE OF itemID ON annotations
FOR EACH ROW
-BEGIN
+ BEGIN
SELECT RAISE(ABORT, 'update on table "annotations" violates foreign key constraint "fku_annotations_itemID_itemAttachments_itemID"')
WHERE NEW.itemID IS NOT NULL AND (SELECT COUNT(*) FROM itemAttachments WHERE itemID = NEW.itemID) = 0;
END;
@@ -58,6 +58,13 @@ CREATE TRIGGER fkd_annotations_itemID_itemAttachments_itemID
WHERE (SELECT COUNT(*) FROM annotations WHERE itemID = OLD.itemID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_itemAttachments_itemID_annotations_itemID;
+CREATE TRIGGER fku_itemAttachments_itemID_annotations_itemID
+ AFTER UPDATE OF itemID ON itemAttachments
+ FOR EACH ROW BEGIN
+ UPDATE annotations SET itemID=NEW.itemID WHERE itemID=OLD.itemID;
+ END;
+
-- collections/parentCollectionID
DROP TRIGGER IF EXISTS fki_collections_parentCollectionID_collections_collectionID;
CREATE TRIGGER fki_collections_parentCollectionID_collections_collectionID
@@ -83,6 +90,13 @@ CREATE TRIGGER fkd_collections_parentCollectionID_collections_collectionID
WHERE (SELECT COUNT(*) FROM collections WHERE parentCollectionID = OLD.collectionID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_collections_collectionID_collections_parentCollectionID;
+CREATE TRIGGER fku_collections_collectionID_collections_parentCollectionID
+ AFTER UPDATE OF collectionID ON collections
+ FOR EACH ROW BEGIN
+ UPDATE collections SET parentCollectionID=NEW.collectionID WHERE parentCollectionID=OLD.collectionID;
+ END;
+
-- collectionItems/collectionID
DROP TRIGGER IF EXISTS fki_collectionItems_collectionID_collections_collectionID;
CREATE TRIGGER fki_collectionItems_collectionID_collections_collectionID
@@ -108,6 +122,13 @@ CREATE TRIGGER fkd_collectionItems_collectionID_collections_collectionID
WHERE (SELECT COUNT(*) FROM collectionItems WHERE collectionID = OLD.collectionID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_collections_collectionID_collectionItems_collectionID;
+CREATE TRIGGER fku_collections_collectionID_collectionItems_collectionID
+ AFTER UPDATE OF collectionID ON collections
+ FOR EACH ROW BEGIN
+ UPDATE collectionItems SET collectionID=NEW.collectionID WHERE collectionID=OLD.collectionID;
+ END;
+
-- collectionItems/itemID
DROP TRIGGER IF EXISTS fki_collectionItems_itemID_items_itemID;
CREATE TRIGGER fki_collectionItems_itemID_items_itemID
@@ -133,6 +154,13 @@ CREATE TRIGGER fkd_collectionItems_itemID_items_itemID
WHERE (SELECT COUNT(*) FROM collectionItems WHERE itemID = OLD.itemID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_items_itemID_collectionItems_itemID;
+CREATE TRIGGER fku_items_itemID_collectionItems_itemID
+ AFTER UPDATE OF itemID ON items
+ FOR EACH ROW BEGIN
+ UPDATE collectionItems SET collectionID=NEW.itemID WHERE collectionID=OLD.itemID;
+ END;
+
-- creators/creatorDataID
DROP TRIGGER IF EXISTS fki_creators_creatorDataID_creatorData_creatorDataID;
CREATE TRIGGER fki_creators_creatorDataID_creatorData_creatorDataID
@@ -158,6 +186,14 @@ CREATE TRIGGER fkd_creators_creatorDataID_creatorData_creatorDataID
WHERE (SELECT COUNT(*) FROM creators WHERE creatorDataID = OLD.creatorDataID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_creatorData_creatorDataID_creators_creatorDataID;
+CREATE TRIGGER fku_creatorData_creatorDataID_creators_creatorDataID
+ BEFORE UPDATE OF creatorDataID ON creatorData
+ FOR EACH ROW BEGIN
+ SELECT RAISE(ABORT, 'update on table "creatorData" violates foreign key constraint "fku_creatorData_creatorDataID_creators_creatorDataID"')
+ WHERE (SELECT COUNT(*) FROM creators WHERE creatorDataID = OLD.creatorDataID) > 0;
+ END;
+
-- fulltextItems/itemID
DROP TRIGGER IF EXISTS fki_fulltextItems_itemID_items_itemID;
CREATE TRIGGER fki_fulltextItems_itemID_items_itemID
@@ -183,6 +219,14 @@ CREATE TRIGGER fkd_fulltextItems_itemID_items_itemID
WHERE (SELECT COUNT(*) FROM fulltextItems WHERE itemID = OLD.itemID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_items_itemID_fulltextItems_itemID;
+CREATE TRIGGER fku_items_itemID_fulltextItems_itemID
+ AFTER UPDATE OF itemID ON items
+ FOR EACH ROW BEGIN
+ UPDATE fulltextItems SET itemID=NEW.itemID WHERE itemID=OLD.itemID;
+ END;
+
+
-- fulltextItemWords/wordID
DROP TRIGGER IF EXISTS fki_fulltextItemWords_wordID_fulltextWords_wordID;
CREATE TRIGGER fki_fulltextItemWords_wordID_fulltextWords_wordID
@@ -208,6 +252,14 @@ CREATE TRIGGER fkd_fulltextItemWords_wordID_fulltextWords_wordID
WHERE (SELECT COUNT(*) FROM fulltextItemWords WHERE wordID = OLD.wordID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_fulltextWords_wordID_fulltextItemWords_wordID;
+CREATE TRIGGER fku_fulltextWords_wordID_fulltextItemWords_wordID
+ BEFORE UPDATE OF wordID ON fulltextWords
+ FOR EACH ROW BEGIN
+ SELECT RAISE(ABORT, 'update on table "fulltextWords" violates foreign key constraint "fku_fulltextWords_wordID_fulltextItemWords_wordID"')
+ WHERE (SELECT COUNT(*) FROM fulltextItemWords WHERE wordID = OLD.wordID) > 0;
+ END;
+
-- fulltextItemWords/itemID
DROP TRIGGER IF EXISTS fki_fulltextItemWords_itemID_items_itemID;
CREATE TRIGGER fki_fulltextItemWords_itemID_items_itemID
@@ -233,6 +285,13 @@ CREATE TRIGGER fkd_fulltextItemWords_itemID_items_itemID
WHERE (SELECT COUNT(*) FROM fulltextItemWords WHERE itemID = OLD.itemID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_items_itemID_fulltextItemWords_itemID;
+CREATE TRIGGER fku_items_itemID_fulltextItemWords_itemID
+ AFTER UPDATE OF itemID ON items
+ FOR EACH ROW BEGIN
+ UPDATE fulltextItemWords SET itemID=NEW.itemID WHERE itemID=OLD.itemID;
+ END;
+
-- highlights/itemID
DROP TRIGGER IF EXISTS fki_highlights_itemID_itemAttachments_itemID;
CREATE TRIGGER fki_highlights_itemID_itemAttachments_itemID
@@ -258,6 +317,13 @@ CREATE TRIGGER fkd_highlights_itemID_itemAttachments_itemID
WHERE (SELECT COUNT(*) FROM highlights WHERE itemID = OLD.itemID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_itemAttachments_itemID_highlights_itemID;
+CREATE TRIGGER fku_itemAttachments_itemID_highlights_itemID
+ AFTER UPDATE OF itemID ON itemAttachments
+ FOR EACH ROW BEGIN
+ UPDATE highlights SET itemID=NEW.itemID WHERE itemID=OLD.itemID;
+ END;
+
-- itemAttachments/itemID
DROP TRIGGER IF EXISTS fki_itemAttachments_itemID_items_itemID;
CREATE TRIGGER fki_itemAttachments_itemID_items_itemID
@@ -283,6 +349,13 @@ CREATE TRIGGER fkd_itemAttachments_itemID_items_itemID
WHERE (SELECT COUNT(*) FROM itemAttachments WHERE itemID = OLD.itemID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_items_itemID_itemAttachments_itemID;
+CREATE TRIGGER fku_items_itemID_itemAttachments_itemID
+ AFTER UPDATE OF itemID ON items
+ FOR EACH ROW BEGIN
+ UPDATE itemAttachments SET itemID=NEW.itemID WHERE itemID=OLD.itemID;
+ END;
+
-- itemAttachments/sourceItemID
DROP TRIGGER IF EXISTS fki_itemAttachments_sourceItemID_items_itemID;
CREATE TRIGGER fki_itemAttachments_sourceItemID_items_itemID
@@ -308,6 +381,13 @@ CREATE TRIGGER fkd_itemAttachments_sourceItemID_items_itemID
WHERE (SELECT COUNT(*) FROM itemAttachments WHERE sourceItemID = OLD.itemID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_items_itemID_itemAttachments_sourceItemID;
+CREATE TRIGGER fku_items_itemID_itemAttachments_sourceItemID
+ AFTER UPDATE OF itemID ON items
+ FOR EACH ROW BEGIN
+ UPDATE itemAttachments SET sourceItemID=NEW.itemID WHERE sourceItemID=OLD.itemID;
+ END;
+
-- itemCreators/itemID
DROP TRIGGER IF EXISTS fki_itemCreators_itemID_items_itemID;
CREATE TRIGGER fki_itemCreators_itemID_items_itemID
@@ -333,6 +413,13 @@ CREATE TRIGGER fkd_itemCreators_itemID_items_itemID
WHERE (SELECT COUNT(*) FROM itemCreators WHERE itemID = OLD.itemID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_items_itemID_itemCreators_itemID;
+CREATE TRIGGER fku_items_itemID_itemCreators_itemID
+ AFTER UPDATE OF itemID ON items
+ FOR EACH ROW BEGIN
+ UPDATE itemCreators SET itemID=NEW.itemID WHERE itemID=OLD.itemID;
+ END;
+
-- itemCreators/creatorID
DROP TRIGGER IF EXISTS fki_itemCreators_creatorID_creators_creatorID;
CREATE TRIGGER fki_itemCreators_creatorID_creators_creatorID
@@ -358,6 +445,13 @@ CREATE TRIGGER fkd_itemCreators_creatorID_creators_creatorID
WHERE (SELECT COUNT(*) FROM itemCreators WHERE creatorID = OLD.creatorID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_creators_creatorID_itemCreators_creatorID;
+CREATE TRIGGER fku_creators_creatorID_itemCreators_creatorID
+ AFTER UPDATE OF creatorID ON creators
+ FOR EACH ROW BEGIN
+ UPDATE itemCreators SET creatorID=NEW.creatorID WHERE creatorID=OLD.creatorID;
+ END;
+
-- itemCreators/creatorTypeID
DROP TRIGGER IF EXISTS fki_itemCreators_creatorTypeID_creatorTypes_creatorTypeID;
CREATE TRIGGER fki_itemCreators_creatorTypeID_creatorTypes_creatorTypeID
@@ -383,6 +477,14 @@ CREATE TRIGGER fkd_itemCreators_creatorTypeID_creatorTypes_creatorTypeID
WHERE (SELECT COUNT(*) FROM itemCreators WHERE creatorTypeID = OLD.creatorTypeID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_creatorTypes_creatorTypeID_itemCreators_creatorTypeID;
+CREATE TRIGGER fku_creatorTypes_creatorTypeID_itemCreators_creatorTypeID
+ BEFORE UPDATE OF creatorTypeID ON creatorTypes
+ FOR EACH ROW BEGIN
+ SELECT RAISE(ABORT, 'update on table "creatorTypes" violates foreign key constraint "fku_creatorTypes_creatorTypeID_itemCreators_creatorTypeID"')
+ WHERE (SELECT COUNT(*) FROM itemCreators WHERE creatorTypeID = OLD.creatorTypeID) > 0;
+ END;
+
-- itemData/itemID
DROP TRIGGER IF EXISTS fki_itemData_itemID_items_itemID;
CREATE TRIGGER fki_itemData_itemID_items_itemID
@@ -408,6 +510,13 @@ CREATE TRIGGER fkd_itemData_itemID_items_itemID
WHERE (SELECT COUNT(*) FROM itemData WHERE itemID = OLD.itemID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_items_itemID_itemData_itemID;
+CREATE TRIGGER fku_items_itemID_itemData_itemID
+ AFTER UPDATE OF itemID ON items
+ FOR EACH ROW BEGIN
+ UPDATE itemData SET itemID=NEW.itemID WHERE itemID=OLD.itemID;
+ END;
+
-- itemData/fieldID
DROP TRIGGER IF EXISTS fki_itemData_fieldID_fields_fieldID;
CREATE TRIGGER fki_itemData_fieldID_fields_fieldID
@@ -433,6 +542,14 @@ CREATE TRIGGER fkd_itemData_fieldID_fields_fieldID
WHERE (SELECT COUNT(*) FROM itemData WHERE fieldID = OLD.fieldID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_fields_fieldID_itemData_fieldID;
+CREATE TRIGGER fku_fields_fieldID_itemData_fieldID
+ BEFORE UPDATE OF fieldID ON FIELDS
+ FOR EACH ROW BEGIN
+ SELECT RAISE(ABORT, 'update on table "fields" violates foreign key constraint "fku_fields_fieldID_itemData_fieldID"')
+ WHERE (SELECT COUNT(*) FROM itemData WHERE fieldID = OLD.fieldID) > 0;
+ END;
+
-- itemData/valueID
DROP TRIGGER IF EXISTS fki_itemData_valueID_itemDataValues_valueID;
CREATE TRIGGER fki_itemData_valueID_itemDataValues_valueID
@@ -458,6 +575,14 @@ CREATE TRIGGER fkd_itemData_valueID_itemDataValues_valueID
WHERE (SELECT COUNT(*) FROM itemData WHERE valueID = OLD.valueID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_itemDataValues_valueID_itemData_valueID;
+CREATE TRIGGER fku_itemDataValues_valueID_itemData_valueID
+ BEFORE UPDATE OF valueID ON itemDataValues
+ FOR EACH ROW BEGIN
+ SELECT RAISE(ABORT, 'update on table "itemDataValues" violates foreign key constraint "fku_itemDataValues_valueID_itemData_valueID"')
+ WHERE (SELECT COUNT(*) FROM itemData WHERE valueID = OLD.valueID) > 0;
+ END;
+
-- itemNotes/itemID
DROP TRIGGER IF EXISTS fki_itemNotes_itemID_items_itemID;
CREATE TRIGGER fki_itemNotes_itemID_items_itemID
@@ -483,6 +608,13 @@ CREATE TRIGGER fkd_itemNotes_itemID_items_itemID
WHERE (SELECT COUNT(*) FROM itemNotes WHERE itemID = OLD.itemID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_items_itemID_itemNotes_itemID;
+CREATE TRIGGER fku_items_itemID_itemNotes_itemID
+ AFTER UPDATE OF itemID ON items
+ FOR EACH ROW BEGIN
+ UPDATE itemNotes SET itemID=NEW.itemID WHERE itemID=OLD.itemID;
+ END;
+
-- itemNotes/sourceItemID
DROP TRIGGER IF EXISTS fki_itemNotes_sourceItemID_items_itemID;
CREATE TRIGGER fki_itemNotes_sourceItemID_items_itemID
@@ -508,6 +640,13 @@ CREATE TRIGGER fkd_itemNotes_sourceItemID_items_itemID
WHERE (SELECT COUNT(*) FROM itemNotes WHERE sourceItemID = OLD.itemID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_items_itemID_itemNotes_sourceItemID;
+CREATE TRIGGER fku_items_itemID_itemNotes_sourceItemID
+ AFTER UPDATE OF itemID ON items
+ FOR EACH ROW BEGIN
+ UPDATE itemNotes SET sourceItemID=NEW.itemID WHERE sourceItemID=OLD.itemID;
+ END;
+
-- itemSeeAlso/itemID
DROP TRIGGER IF EXISTS fki_itemSeeAlso_itemID_items_itemID;
CREATE TRIGGER fki_itemSeeAlso_itemID_items_itemID
@@ -533,6 +672,13 @@ CREATE TRIGGER fkd_itemSeeAlso_itemID_items_itemID
WHERE (SELECT COUNT(*) FROM itemSeeAlso WHERE itemID = OLD.itemID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_items_itemID_itemSeeAlso_itemID;
+CREATE TRIGGER fku_items_itemID_itemSeeAlso_itemID
+ AFTER UPDATE OF itemID ON items
+ FOR EACH ROW BEGIN
+ UPDATE itemSeeAlso SET itemID=NEW.itemID WHERE itemID=OLD.itemID;
+ END;
+
-- itemSeeAlso/linkedItemID
DROP TRIGGER IF EXISTS fki_itemSeeAlso_linkedItemID_items_itemID;
CREATE TRIGGER fki_itemSeeAlso_linkedItemID_items_itemID
@@ -558,6 +704,13 @@ CREATE TRIGGER fkd_itemSeeAlso_linkedItemID_items_itemID
WHERE (SELECT COUNT(*) FROM itemSeeAlso WHERE linkedItemID = OLD.itemID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_items_itemID_itemSeeAlso_linkedItemID;
+CREATE TRIGGER fku_items_itemID_itemSeeAlso_linkedItemID
+ AFTER UPDATE OF itemID ON items
+ FOR EACH ROW BEGIN
+ UPDATE itemSeeAlso SET linkedItemID=NEW.itemID WHERE linkedItemID=OLD.itemID;
+ END;
+
-- itemTags/itemID
DROP TRIGGER IF EXISTS fki_itemTags_itemID_items_itemID;
CREATE TRIGGER fki_itemTags_itemID_items_itemID
@@ -583,6 +736,13 @@ CREATE TRIGGER fkd_itemTags_itemID_items_itemID
WHERE (SELECT COUNT(*) FROM itemTags WHERE itemID = OLD.itemID) > 0;
END;
+DROP TRIGGER IF EXISTS fkd_items_itemID_itemTags_itemID;
+CREATE TRIGGER fkd_items_itemID_itemTags_itemID
+ AFTER UPDATE OF itemID ON items
+ FOR EACH ROW BEGIN
+ UPDATE itemTags SET itemID=NEW.itemID WHERE itemID=OLD.itemID;
+ END;
+
-- itemTags/tagID
DROP TRIGGER IF EXISTS fki_itemTags_tagID_tags_tagID;
CREATE TRIGGER fki_itemTags_tagID_tags_tagID
@@ -608,6 +768,13 @@ CREATE TRIGGER fkd_itemTags_tagID_tags_tagID
WHERE (SELECT COUNT(*) FROM itemTags WHERE tagID = OLD.tagID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_tags_tagID_itemTags_tagID;
+CREATE TRIGGER fku_tags_tagID_itemTags_tagID
+ AFTER UPDATE OF tagID ON tags
+ FOR EACH ROW BEGIN
+ UPDATE itemTags SET tagID=NEW.tagID WHERE tagID=OLD.tagID;
+ END;
+
-- savedSearchConditions/savedSearchID
DROP TRIGGER IF EXISTS fki_savedSearchConditions_savedSearchID_savedSearches_savedSearchID;
CREATE TRIGGER fki_savedSearchConditions_savedSearchID_savedSearches_savedSearchID
@@ -633,6 +800,13 @@ CREATE TRIGGER fkd_savedSearchConditions_savedSearchID_savedSearches_savedSearch
WHERE (SELECT COUNT(*) FROM savedSearchConditions WHERE savedSearchID = OLD.savedSearchID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_savedSearches_savedSearchID_savedSearchConditions_savedSearchID;
+CREATE TRIGGER fku_savedSearches_savedSearchID_savedSearchConditions_savedSearchID
+ AFTER UPDATE OF savedSearchID ON savedSearches
+ FOR EACH ROW BEGIN
+ UPDATE savedSearchConditions SET savedSearchID=NEW.savedSearchID WHERE savedSearchID=OLD.savedSearchID;
+ END;
+
-- syncDeleteLog/syncObjectTypeID
DROP TRIGGER IF EXISTS fki_syncDeleteLog_syncObjectTypeID_syncObjectTypes_syncObjectTypeID;
CREATE TRIGGER fki_syncDeleteLog_syncObjectTypeID_syncObjectTypes_syncObjectTypeID
@@ -658,6 +832,14 @@ CREATE TRIGGER fkd_syncDeleteLog_syncObjectTypeID_syncObjectTypes_syncObjectType
WHERE (SELECT COUNT(*) FROM syncDeleteLog WHERE syncObjectTypeID = OLD.syncObjectTypeID) > 0;
END;
+DROP TRIGGER IF EXISTS fku_syncObjectTypes_syncObjectTypeID_syncDeleteLog_syncObjectTypeID;
+CREATE TRIGGER fku_syncObjectTypes_syncObjectTypeID_syncDeleteLog_syncObjectTypeID
+ BEFORE DELETE ON syncObjectTypes
+ FOR EACH ROW BEGIN
+ SELECT RAISE(ABORT, 'update on table "syncObjectTypes" violates foreign key constraint "fku_syncObjectTypes_syncObjectTypeID_syncDeleteLog_syncObjectTypeID"')
+ WHERE (SELECT COUNT(*) FROM syncDeleteLog WHERE syncObjectTypeID = OLD.syncObjectTypeID) > 0;
+ END;
+
-- proxyHosts/proxyID
DROP TRIGGER IF EXISTS fki_proxyHosts_proxyID_proxies_proxyID;
CREATE TRIGGER fki_proxyHosts_proxyID_proxies_proxyID
@@ -682,3 +864,11 @@ CREATE TRIGGER fkd_proxyHosts_proxyID_proxies_proxyID
SELECT RAISE(ABORT, 'delete on table "proxies" violates foreign key constraint "fkd_proxyHosts_proxyID_proxies_proxyID"')
WHERE (SELECT COUNT(*) FROM proxyHosts WHERE proxyID = OLD.proxyID) > 0;
END;
+
+DROP TRIGGER IF EXISTS fku_proxies_proxyID_proxyHosts_proxyID;
+CREATE TRIGGER fku_proxies_proxyID_proxyHosts_proxyID
+ BEFORE DELETE ON proxies
+ FOR EACH ROW BEGIN
+ SELECT RAISE(ABORT, 'update on table "proxies" violates foreign key constraint "fku_proxies_proxyID_proxyHosts_proxyID"')
+ WHERE (SELECT COUNT(*) FROM proxyHosts WHERE proxyID = OLD.proxyID) > 0;
+ END;
