www

commit 5d30b8c7261cc4e0ab41c3e9bd66139bd910029d
parent 124eadc9ae2f373f8a8791c7530e3da99f954327
Author: Dan Stillman <dstillman@zotero.org>
Date:   Thu, 16 Jun 2011 04:57:37 +0000

Have the database integrity check look for the schema-related inconsistencies that the DB Repair Tool fixes


Diffstat:
M
chrome/content/zotero/preferences/preferences.js
 | 
3
+++
M
chrome/content/zotero/xpcom/schema.js
 | 
53
+++++++++++++++++++++++++++++++++++++++++++++++++++++

2 files changed, 56 insertions(+), 0 deletions(-)
diff --git a/chrome/content/zotero/preferences/preferences.js b/chrome/content/zotero/preferences/preferences.js
@@ -1189,6 +1189,9 @@ function runIntegrityCheck() {
 		.getService(Components.interfaces.nsIPromptService);
 	
 	var ok = Zotero.DB.integrityCheck();
+	if (ok) {
+		ok = Zotero.Schema.integrityCheck();
+	}
 	var str = ok ? 'passed' : 'failed';
 	
 	ps.alert(window,
diff --git a/chrome/content/zotero/xpcom/schema.js b/chrome/content/zotero/xpcom/schema.js
@@ -1038,6 +1038,59 @@ Zotero.Schema = new function(){
 	}
 	
 	
+	this.integrityCheck = function () {
+		// There should be an equivalent SELECT COUNT(*) statement for every
+		// statement run by the DB Repair Tool
+		var queries = [
+			"SELECT COUNT(*) FROM annotations WHERE itemID NOT IN (SELECT itemID FROM items)",
+			"SELECT COUNT(*) FROM collectionItems WHERE itemID NOT IN (SELECT itemID FROM items)",
+			"SELECT COUNT(*) FROM fulltextItems WHERE itemID NOT IN (SELECT itemID FROM items)",
+			"SELECT COUNT(*) FROM fulltextItemWords WHERE itemID NOT IN (SELECT itemID FROM items)",
+			"SELECT COUNT(*) FROM fulltextItemWords WHERE itemID NOT IN (SELECT itemID FROM fulltextItems)",
+			"SELECT COUNT(*) FROM highlights WHERE itemID NOT IN (SELECT itemID FROM items)",
+			"SELECT COUNT(*) FROM itemAttachments WHERE itemID NOT IN (SELECT itemID FROM items)",
+			"SELECT COUNT(*) FROM itemCreators WHERE itemID NOT IN (SELECT itemID FROM items)",
+			"SELECT COUNT(*) FROM itemData WHERE itemID NOT IN (SELECT itemID FROM items)",
+			"SELECT COUNT(*) FROM itemNotes WHERE itemID NOT IN (SELECT itemID FROM items)",
+			"SELECT COUNT(*) FROM itemSeeAlso WHERE itemID NOT IN (SELECT itemID FROM items)",
+			"SELECT COUNT(*) FROM itemSeeAlso WHERE linkedItemID NOT IN (SELECT itemID FROM items)",
+			"SELECT COUNT(*) FROM itemTags WHERE itemID NOT IN (SELECT itemID FROM items)",
+			"SELECT COUNT(*) FROM itemTags WHERE tagID NOT IN (SELECT tagID FROM tags)",
+			"SELECT COUNT(*) FROM savedSearchConditions WHERE savedSearchID NOT IN (select savedSearchID FROM savedSearches)",
+			"SELECT COUNT(*) FROM items WHERE itemTypeID IS NULL",
+			
+			"SELECT COUNT(*) FROM itemData WHERE valueID NOT IN (SELECT valueID FROM itemDataValues)",
+			"SELECT COUNT(*) FROM fulltextItemWords WHERE wordID NOT IN (SELECT wordID FROM fulltextWords)",
+			"SELECT COUNT(*) FROM collectionItems WHERE collectionID NOT IN (SELECT collectionID FROM collections)",
+			"SELECT COUNT(*) FROM itemCreators WHERE creatorID NOT IN (SELECT creatorID FROM creators)",
+			"SELECT COUNT(*) FROM itemTags WHERE tagID NOT IN (SELECT tagID FROM tags)",
+			"SELECT COUNT(*) FROM itemData WHERE fieldID NOT IN (SELECT fieldID FROM fields)",
+			"SELECT COUNT(*) FROM itemData WHERE valueID NOT IN (SELECT valueID FROM itemDataValues)",
+			
+			"SELECT COUNT(*) FROM itemData WHERE fieldID NOT IN (SELECT fieldID FROM itemTypeFields WHERE itemTypeID=(SELECT itemTypeID FROM items WHERE itemID=itemData.itemID))",
+			
+			"SELECT COUNT(*) FROM items WHERE itemTypeID=14 AND itemID NOT IN (SELECT itemID FROM itemAttachments)",
+			
+			"SELECT COUNT(*) FROM itemAttachments WHERE sourceItemID IN (SELECT itemID FROM items WHERE itemTypeID IN (1,14))",
+			"SELECT COUNT(*) FROM itemNotes WHERE sourceItemID IN (SELECT itemID FROM items WHERE itemTypeID IN (1,14))",
+			
+			"SELECT COUNT(*) FROM tags NATURAL JOIN itemTags JOIN items USING (itemID) WHERE IFNULL(tags.libraryID, 0)!=IFNULL(items.libraryID,0)",
+			
+			"SELECT COUNT(*) FROM itemTags WHERE tagID IS NULL",
+			"SELECT COUNT(*) FROM itemAttachments WHERE charsetID='NULL'"
+		];
+		
+		for each(var sql in queries) {
+			if (Zotero.DB.valueQuery(sql)) {
+				return false;
+			}
+		}
+		
+		return true;
+	}
+	
+	
+	
 	/////////////////////////////////////////////////////////////////
 	//
 	// Private methods