commit d92e3661d51dd001f5678d09669e17e8c3711a7d
parent 7236aaf58ed0fdd4db4c52fef1f890aa8246bc36
Author: Dan Stillman <dstillman@zotero.org>
Date: Sat, 9 Feb 2008 09:46:29 +0000
- Added Zotero.ID.get(table) method for getting a primary key id -- it first tries to find the lowest unused integer in the PK column and falls back to using MAX() + 1 if it can't find one quickly
- Removed Zotero.getRandomID() (moved into Zotero.ID, but unused)
- Purge itemDataValues on item delete
Diffstat:
4 files changed, 263 insertions(+), 56 deletions(-)
diff --git a/chrome/content/zotero/xpcom/data_access.js b/chrome/content/zotero/xpcom/data_access.js
@@ -877,7 +877,7 @@ Zotero.Item.prototype.save = function(){
valueStatement.reset();
if (!valueID) {
- valueID = Zotero.getRandomID('itemDataValues', 'valueID', 2097152); // stored in 3 bytes
+ valueID = Zotero.ID.get('itemDataValues');
insertStatement.bindInt32Parameter(0, valueID);
if (Zotero.ItemFields.getID('accessDate') == fieldID
@@ -983,9 +983,12 @@ Zotero.Item.prototype.save = function(){
//
// Primary fields
//
- sqlColumns.push('itemID');
- var itemID = Zotero.getRandomID('items', 'itemID');
- sqlValues.push(itemID);
+ var itemID = Zotero.ID.get('items');
+ // If available id value, use it -- otherwise we'll use autoincrement
+ if (itemID) {
+ sqlColumns.push('itemID');
+ sqlValues.push(itemID);
+ }
sqlColumns.push('itemTypeID');
sqlValues.push({'int':this.getField('itemTypeID')});
@@ -1009,7 +1012,10 @@ Zotero.Item.prototype.save = function(){
sql = sql.substring(0,sql.length-1) + ")";
// Save basic data to items table
- Zotero.DB.query(sql, sqlValues);
+ var lastInsertID = Zotero.DB.query(sql, sqlValues);
+ if (!itemID) {
+ itemID = lastInsertID;
+ }
this._data['itemID'] = itemID;
Zotero.History.setAssociatedID(itemID);
@@ -1045,7 +1051,7 @@ Zotero.Item.prototype.save = function(){
valueStatement.reset();
if (!valueID) {
- valueID = Zotero.getRandomID('itemDataValues', 'valueID', 2097152); // stored in 3 bytes
+ valueID = Zotero.ID.get('itemDataValues');
insertValueStatement.bindInt32Parameter(0, valueID);
if (Zotero.ItemFields.getID('accessDate') == fieldID
@@ -2034,7 +2040,7 @@ Zotero.Item.prototype.removeTag = function(tagID){
Zotero.DB.beginTransaction();
var sql = "DELETE FROM itemTags WHERE itemID=? AND tagID=?";
- Zotero.DB.query(sql, [this.getID(), tagID]);
+ Zotero.DB.query(sql, [this.getID(), { int: tagID }]);
Zotero.Tags.purge();
Zotero.DB.commitTransaction();
Zotero.Notifier.trigger('modify', 'item', this.getID());
@@ -2956,7 +2962,11 @@ Zotero.Items = new function(){
Zotero.Creators.purge();
Zotero.Tags.purge();
Zotero.Fulltext.purgeUnusedWords();
- // TODO: purge itemDataValues?
+
+ // Purge unused values
+ var sql = "DELETE FROM itemDataValues WHERE valueID NOT IN "
+ + "(SELECT valueID FROM itemData)";
+ Zotero.DB.query(sql);
}
@@ -3695,7 +3705,7 @@ Zotero.Collections = new function(){
var parentParam = parent ? {'int':parent} : {'null':true};
- var rnd = Zotero.getRandomID('collections', 'collectionID');
+ var rnd = Zotero.ID.get('collections');
var sql = "INSERT INTO collections VALUES (?,?,?)";
var sqlValues = [ {'int':rnd}, {'string':name}, parentParam ];
@@ -3882,7 +3892,7 @@ Zotero.Creators = new function(){
Zotero.DB.beginTransaction();
var sql = 'INSERT INTO creators VALUES (?,?,?,?)';
- var rnd = Zotero.getRandomID('creators', 'creatorID');
+ var rnd = Zotero.ID.get('creators');
var params = [
rnd, fieldMode ? '' : {string: firstName}, {string: lastName},
fieldMode ? 1 : 0
@@ -4161,7 +4171,7 @@ Zotero.Tags = new function(){
Zotero.DB.beginTransaction();
var sql = 'INSERT INTO tags VALUES (?,?,?)';
- var rnd = Zotero.getRandomID('tags', 'tagID');
+ var rnd = Zotero.ID.get('tags');
Zotero.DB.query(sql, [{int: rnd}, {string: tag}, {int: type}]);
Zotero.DB.commitTransaction();
@@ -4182,6 +4192,7 @@ Zotero.Tags = new function(){
notifierData[this.id] = { old: this.toArray() };
if (oldName == tag) {
+ // Convert unchanged automatic tags to manual
if (oldType != 0) {
var sql = "UPDATE tags SET tagType=0 WHERE tagID=?";
Zotero.DB.query(sql, tagID);
@@ -4947,6 +4958,238 @@ Zotero.ItemFields = new function(){
}
+Zotero.ID = new function () {
+ this.get = get;
+
+ _available = {};
+
+ /*
+ * Gets an unused primary key id for a DB table
+ */
+ function get(table, notNull) {
+ switch (table) {
+ // Autoincrement tables
+ //
+ // Callers need to handle a potential NULL for these unless they
+ // pass |notNull|
+ case 'items':
+ var id = _getNextAvailable(table);
+ if (!id && notNull) {
+ return _getNext(table);
+ }
+ return id;
+
+ // Non-autoincrement tables
+ //
+ // TODO: use autoincrement instead where available in 1.5
+ case 'creators':
+ case 'collections':
+ case 'itemDataValues':
+ case 'savedSearches':
+ case 'tags':
+ var id = _getNextAvailable(table);
+ if (!id) {
+ // If we can't find an empty id quickly, just use MAX() + 1
+ return _getNext(table);
+ }
+ return id;
+
+ default:
+ throw ("Unsupported table '" + table + "' in Zotero.ID.get()");
+ }
+ }
+
+
+ /*
+ * Returns the lowest available unused primary key id for table
+ */
+ function _getNextAvailable(table) {
+ if (!_available[table]) {
+ _loadAvailable(table);
+ }
+
+ var arr = _available[table];
+
+ for (var i in arr) {
+ var id = arr[i][0];
+ // End of range -- remove range
+ if (id == arr[i][1]) {
+ arr.shift();
+ }
+ // Within range -- increment
+ else {
+ arr[i][0]++;
+ }
+
+ // Prepare table for refresh if all rows used
+ if (arr.length == 0) {
+ delete _available[table];
+ }
+
+ return id;
+ }
+ return null;
+ }
+
+
+ /*
+ * Get MAX(id) + 1 from table
+ */
+ function _getNext(table) {
+ var column = _getTableColumn(table);
+ var sql = 'SELECT MAX(' + column + ') + 1 FROM ' + table;
+ return Zotero.DB.valueQuery(sql);
+ }
+
+
+ /*
+ * Loads available ids for table into memory
+ */
+ function _loadAvailable(table) {
+ Zotero.debug("Loading available ids for table '" + table + "'");
+
+ var numIDs = 3; // Number of ids to compare against at a time
+ var maxTries = 3; // Number of times to try increasing the maxID
+ var maxToFind = 1000;
+
+ var column = _getTableColumn(table);
+
+ switch (table) {
+ case 'creators':
+ case 'items':
+ case 'itemDataValues':
+ case 'tags':
+ break;
+
+ case 'collections':
+ case 'savedSearches':
+ var maxToFind = 100;
+ break;
+
+ default:
+ throw ("Unsupported table '" + table + "' in Zotero.ID._loadAvailable()");
+ }
+
+ var maxID = numIDs;
+ var sql = "SELECT " + column + " FROM " + table
+ + " WHERE " + column + "<=? ORDER BY " + column;
+ var ids = Zotero.DB.columnQuery(sql, maxID);
+ // If no ids found, we have maxID unused ids
+ if (!ids) {
+ Zotero.debug('none found');
+ var found = Math.min(maxID, maxToFind);
+ Zotero.debug("Found " + found + " available ids in table '" + table + "'");
+ _available[table] = [[1, found]];
+ return;
+ }
+
+ // If we didn't find any unused ids, try increasing maxID a few times
+ while (ids.length == maxID && maxTries>0) {
+ Zotero.debug('nope');
+ maxID = maxID + numIDs;
+ ids = Zotero.DB.columnQuery(sql, maxID);
+ maxTries--;
+ }
+
+ // Didn't find any unused ids
+ if (ids.length == maxID) {
+ Zotero.debug('none!');
+ Zotero.debug("Found 0 available ids in table '" + table + "'");
+ _available[table] = [];
+ return;
+ }
+
+ var available = [], found = 0, j=0, availableStart = null;
+
+ for (var i=1; i<=maxID && found<maxToFind; i++) {
+ // We've gone past the found ids, so all remaining ids up to maxID
+ // are available
+ if (!ids[j]) {
+ Zotero.debug('all remaining are available');
+ available.push([i, maxID]);
+ found += (maxID - i) + 1;
+ break;
+ }
+
+ // Skip ahead while ids are occupied
+ if (ids[j] == i) {
+ Zotero.debug('skipping');
+ j++;
+ continue;
+ }
+
+ // Advance counter while it's below the next used id
+ while (ids[j] > i && i<=maxID) {
+ Zotero.debug('b');
+ if (!availableStart) {
+ availableStart = i;
+ }
+ i++;
+
+ if ((found + (i - availableStart) + 1) > maxToFind) {
+ break;
+ }
+ }
+ if (availableStart) {
+ available.push([availableStart, i-1]);
+ // Keep track of how many empties we've found
+ found += ((i-1) - availableStart) + 1;
+ availableStart = null;
+ }
+ j++;
+ }
+
+ Zotero.debug("Found " + found + " available ids in table '" + table + "'");
+
+ _available[table] = available;
+ Zotero.debug(available);
+ }
+
+
+ /**
+ * Find a unique random id for use in a DB table
+ *
+ * (No longer used)
+ **/
+ function _getRandomID(table, max){
+ var column = _getTableColumn(table);
+
+ var sql = 'SELECT COUNT(*) FROM ' + table + ' WHERE ' + column + '= ?';
+
+ if (!max){
+ max = 16383;
+ }
+
+ max--; // since we use ceil(), decrement max by 1
+ var tries = 3; // # of tries to find a unique id
+ for (var i=0; i<tries; i++) {
+ var rnd = Math.ceil(Math.random() * max);
+ var exists = Zotero.DB.valueQuery(sql, { int: rnd });
+ if (!exists) {
+ return rnd;
+ }
+ }
+
+ // If no luck after number of tries, try a larger range
+ var sql = 'SELECT MAX(' + column + ') + 1 FROM ' + table;
+ return Zotero.valueQuery(sql);
+ }
+
+
+ function _getTableColumn(table) {
+ switch (table) {
+ case 'itemDataValues':
+ return 'valueID';
+
+ case 'savedSearches':
+ return 'savedSearchID';
+
+ default:
+ return table.substr(0, table.length - 1) + 'ID';
+ }
+ }
+}
+
diff --git a/chrome/content/zotero/xpcom/schema.js b/chrome/content/zotero/xpcom/schema.js
@@ -916,7 +916,7 @@ Zotero.Schema = new function(){
var values = Zotero.DB.columnQuery("SELECT DISTINCT value FROM itemData");
if (values) {
for (var j=0; j<values.length; j++) {
- var valueID = Zotero.getRandomID('itemDataValues', 'valueID', 2097152); // Stored in 3 bytes
+ var valueID = Zotero.ID.get('itemDataValues');
Zotero.DB.query("INSERT INTO itemDataValues VALUES (?,?)", [valueID, values[j]]);
}
}
@@ -935,7 +935,7 @@ Zotero.Schema = new function(){
if (rows) {
for (var j=0; j<rows.length; j++) {
for (var j=0; j<values.length; j++) {
- var valueID = Zotero.getRandomID('itemDataValues', 'valueID', 2097152); // Stored in 3 bytes
+ var valueID = Zotero.ID.get('itemDataValues');
Zotero.DB.query("INSERT INTO itemDataValues VALUES (?,?)", [valueID, values[j]]);
Zotero.DB.query("UPDATE itemData SET valueID=? WHERE itemID=? AND fieldID=?", [valueID, rows[j]['itemID'], rows[j]['fieldID']]);
}
@@ -1021,14 +1021,14 @@ Zotero.Schema = new function(){
if (i==22) {
if (Zotero.DB.valueQuery("SELECT COUNT(*) FROM items WHERE itemID=0")) {
- var itemID = Zotero.getRandomID('items', 'itemID');
+ var itemID = Zotero.ID.get('items', true);
Zotero.DB.query("UPDATE items SET itemID=? WHERE itemID=?", [itemID, 0]);
Zotero.DB.query("UPDATE itemData SET itemID=? WHERE itemID=?", [itemID, 0]);
Zotero.DB.query("UPDATE itemNotes SET itemID=? WHERE itemID=?", [itemID, 0]);
Zotero.DB.query("UPDATE itemAttachments SET itemID=? WHERE itemID=?", [itemID, 0]);
}
if (Zotero.DB.valueQuery("SELECT COUNT(*) FROM collections WHERE collectionID=0")) {
- var collectionID = Zotero.getRandomID('collections', 'collectionID');
+ var collectionID = Zotero.ID.get('collections');
Zotero.DB.query("UPDATE collections SET collectionID=? WHERE collectionID=0", [collectionID]);
Zotero.DB.query("UPDATE collectionItems SET collectionID=? WHERE collectionID=0", [collectionID]);
}
@@ -1051,7 +1051,7 @@ Zotero.Schema = new function(){
var values = Zotero.DB.columnQuery("SELECT DISTINCT value FROM itemData");
if (values) {
for (var j=0; j<values.length; j++) {
- var valueID = Zotero.getRandomID('itemDataValues', 'valueID', 2097152); // Stored in 3 bytes
+ var valueID = Zotero.ID.get('itemDataValues');
Zotero.DB.query("INSERT INTO itemDataValues VALUES (?,?)", [valueID, values[j]]);
}
}
@@ -1071,7 +1071,7 @@ Zotero.Schema = new function(){
var value = Zotero.Date.strToMultipart(rows[j]['value']);
var valueID = Zotero.DB.valueQuery("SELECT valueID FROM itemDataValues WHERE value=?", rows[j]['value']);
if (!valueID) {
- var valueID = Zotero.getRandomID('itemDataValues', 'valueID', 2097152);
+ var valueID = Zotero.ID.get('itemDataValues');
Zotero.DB.query("INSERT INTO itemDataValues VALUES (?,?)", [valueID, value]);
}
Zotero.DB.query("UPDATE itemData SET valueID=? WHERE itemID=? AND fieldID=?", [valueID, rows[j]['itemID'], rows[j]['fieldID']]);
@@ -1145,7 +1145,7 @@ Zotero.Schema = new function(){
if (i==33) {
var rows = Zotero.DB.query("SELECT * FROM itemNotes WHERE itemID NOT IN (SELECT itemID FROM items)");
if (rows) {
- var colID = Zotero.getRandomID('collections', 'collectionID');
+ var colID = Zotero.ID.get('collections');
Zotero.DB.query("INSERT INTO collections VALUES (?,?,?)", [colID, "[Recovered Notes]", null]);
for (var j=0; j<rows.length; j++) {
@@ -1164,7 +1164,7 @@ Zotero.Schema = new function(){
}
var exists = Zotero.DB.valueQuery("SELECT COUNT(*) FROM itemNotes WHERE itemID=?", rows[j].note);
if (exists) {
- var noteItemID = Zotero.getRandomID('items', 'itemID');
+ var noteItemID = Zotero.ID.get('items', true);
}
else {
var noteItemID = rows[j].note;
diff --git a/chrome/content/zotero/xpcom/search.js b/chrome/content/zotero/xpcom/search.js
@@ -134,8 +134,7 @@ Zotero.Search.prototype.save = function(fixGaps) {
else {
var isNew = true;
- this._savedSearchID
- = Zotero.getRandomID('savedSearches', 'savedSearchID');
+ this._savedSearchID = Zotero.ID.get('savedSearches');
var sql = "INSERT INTO savedSearches (savedSearchID, savedSearchName) "
+ "VALUES (?,?)";
diff --git a/chrome/content/zotero/xpcom/zotero.js b/chrome/content/zotero/xpcom/zotero.js
@@ -59,7 +59,6 @@ var Zotero = new function(){
this.arrayToHash = arrayToHash;
this.hasValues = hasValues;
this.randomString = randomString;
- this.getRandomID = getRandomID;
this.moveToUnique = moveToUnique;
// Public properties
@@ -813,40 +812,6 @@ var Zotero = new function(){
}
- /**
- * Find a unique random id for use in a DB table
- **/
- function getRandomID(table, column, max){
- if (!table){
- throw('SQL query not provided');
- }
-
- if (!column){
- throw('SQL query not provided');
- }
-
- var sql = 'SELECT COUNT(*) FROM ' + table + ' WHERE ' + column + '=';
-
- if (!max){
- max = 16383;
- }
-
- max--; // since we use ceil(), decrement max by 1
- var tries = 3; // # of tries to find a unique id
- do {
- // If no luck after number of tries, try a larger range
- if (!tries){
- max = max * 128;
- }
- var rnd = Math.ceil(Math.random()*max);
- var exists = Zotero.DB.valueQuery(sql + rnd);
- tries--;
- }
- while (exists);
- return rnd;
- }
-
-
function moveToUnique(file, newFile){
newFile.createUnique(Components.interfaces.nsIFile.NORMAL_FILE_TYPE, 0644);
var newName = newFile.leafName;
