commit deea149235c02404218bc042d6f0de228ce28b95
parent a75fb2ddb670d59b1f22529c9b8fd1a2fac79f2a
Author: Dan Stillman <dstillman@zotero.org>
Date: Sat, 20 May 2006 15:18:36 +0000
Moved creatorTypeID into itemCreators and changed code accordingly; updated sample data
Added Scholar.CreatorTypes with methods getTypes() (multi-dim array with 'id' and 'name') and getTypeName(creatorTypeID)
Fixed bug in Scholar.Creators.purge() causing SQL error when deleting a non-existent creator
Fixed incorrect field order for itemCreators INSERT queries in save()
Changed setCreator() to take empty creator names (causing DELETE in itemCreators but without shifting up creators below it (or down and above, depending on your perspective) like removeCreators())
Diffstat:
4 files changed, 113 insertions(+), 81 deletions(-)
diff --git a/chrome/chromeFiles/content/scholar/data_access.js b/chrome/chromeFiles/content/scholar/data_access.js
@@ -183,12 +183,12 @@ Scholar.Item.prototype.setCreator = function(orderIndex, firstName, lastName, cr
this._loadCreators();
}
- if (!creatorTypeID){
- creatorTypeID = 1;
+ if (!firstName){
+ firstName = '';
}
- if (!firstName && !lastName){
- throw ('Name not provided for creator');
+ if (!lastName){
+ lastName = '';
}
if (this._creators.has(orderIndex) &&
@@ -198,6 +198,10 @@ Scholar.Item.prototype.setCreator = function(orderIndex, firstName, lastName, cr
return true;
}
+ if (!creatorTypeID){
+ creatorTypeID = 1;
+ }
+
var creator = new Array();
creator['firstName'] = firstName;
creator['lastName'] = lastName;
@@ -222,7 +226,8 @@ Scholar.Item.prototype.removeCreator = function(orderIndex){
}
this._creators.remove(orderIndex);
- for (var i=orderIndex,len=this._creators.length; i<=len; i++){
+ // Go to length+1 so we clear the last one
+ for (var i=orderIndex, max=this._creators.length+1; i<max; i++){
var next =
this._creators.items[i+1] ? this._creators.items[i+1] : false;
this._creators.set(i, next);
@@ -444,10 +449,7 @@ Scholar.Item.prototype.save = function(){
var creator = this.getCreator(orderIndex);
- // If empty, delete at position and shift down any above it
- //
- // We have to do this immediately so old entries are
- // cleared before other ones are shifted down
+ // If empty, delete at position
if (!creator['firstName'] && !creator['lastName']){
sql2 = 'DELETE FROM itemCreators '
+ ' WHERE itemID=' + this.getID()
@@ -459,37 +461,43 @@ Scholar.Item.prototype.save = function(){
// See if this is an existing creator
var creatorID = Scholar.Creators.getID(
creator['firstName'],
- creator['lastName'],
- creator['creatorTypeID']
+ creator['lastName']
);
// If not, add it
if (!creatorID){
creatorID = Scholar.Creators.add(
creator['firstName'],
- creator['lastName'],
- creator['creatorTypeID']
+ creator['lastName']
);
}
+ // If there's a creator at this position, update
+ // with new creator data
sql2 = 'SELECT COUNT(*) FROM itemCreators'
+ ' WHERE itemID=' + this.getID()
+ ' AND orderIndex=' + orderIndex;
if (Scholar.DB.valueQuery(sql2)){
sql += 'UPDATE itemCreators SET creatorID='
- + creatorID + ' WHERE itemID=' + this.getID()
+ + creatorID + ', creatorTypeID='
+ + creator['creatorTypeID'] + ', '
+ + 'WHERE itemID=' + this.getID()
+ ' AND orderIndex=' + orderIndex + ";\n";
}
+ // Otherwise insert
else {
sql += 'INSERT INTO itemCreators VALUES ('
- + creatorID + ',' + itemID + ',' + orderIndex
+ + itemID + ', ' + creatorID + ', '
+ + creator['creatorTypeID'] + ', ' + orderIndex
+ ");\n";
}
}
// Append the SQL to delete obsolete creators
+ //
+ // TODO: fix this so it actually purges the internal memory
sql += Scholar.Creators.purge(true) + "\n";
}
@@ -595,29 +603,27 @@ Scholar.Item.prototype.save = function(){
var creator = this.getCreator(orderIndex);
// If empty, skip
- if (typeof creator['firstName'] == 'undefined'
- && typeof creator['lastName'] == 'undefined'){
+ if (!creator['firstName'] && !creator['lastName']){
continue;
}
// See if this is an existing creator
var creatorID = Scholar.Creators.getID(
creator['firstName'],
- creator['lastName'],
- creator['creatorTypeID']
+ creator['lastName']
);
// If not, add it
if (!creatorID){
creatorID = Scholar.Creators.add(
creator['firstName'],
- creator['lastName'],
- creator['creatorTypeID']
+ creator['lastName']
);
}
sql += 'INSERT INTO itemCreators VALUES ('
- + creatorID + ',' + itemID + ',' + orderIndex
+ + itemID + ',' + creatorID + ','
+ + creator['creatorTypeID'] + ', ' + orderIndex
+ ");\n";
}
}
@@ -757,7 +763,8 @@ Scholar.Item.prototype._loadCreators = function(){
throw ('ItemID not set for item before attempting to load creators');
}
- var sql = 'SELECT C.creatorID, C.*, orderIndex FROM itemCreators IC '
+ var sql = 'SELECT C.creatorID, C.*, creatorTypeID, orderIndex '
+ + 'FROM itemCreators IC '
+ 'LEFT JOIN creators C USING (creatorID) '
+ 'WHERE itemID=' + this.getID() + ' ORDER BY orderIndex';
var creators = Scholar.DB.query(sql);
@@ -774,6 +781,7 @@ Scholar.Item.prototype._loadCreators = function(){
creator['firstName'] = creators[i]['firstName'];
creator['lastName'] = creators[i]['lastName'];
creator['creatorTypeID'] = creators[i]['creatorTypeID'];
+ // Save creator data into Hash, indexed by orderIndex
this._creators.set(creators[i]['orderIndex'], creator);
}
@@ -1159,7 +1167,7 @@ Scholar.Folders = new function(){
Scholar.Creators = new function(){
- var _creators = new Array; // indexed by first%%%last%%%creatorTypeID hash
+ var _creators = new Array; // indexed by first%%%last hash
var _creatorsByID = new Array; // indexed by creatorID
this.get = get;
@@ -1192,17 +1200,17 @@ Scholar.Creators = new function(){
/*
* Returns the creatorID matching given name and type
*/
- function getID(firstName, lastName, creatorTypeID){
- var hash = firstName + '%%%' + lastName + '%%%' + creatorTypeID;
+ function getID(firstName, lastName){
+ var hash = firstName + '%%%' + lastName;
if (_creators[hash]){
return _creators[hash];
}
- var sql = 'SELECT creatorID FROM creators WHERE firstName=? AND '
- + 'lastName=? AND creatorTypeID=?';
+ var sql = 'SELECT creatorID FROM creators '
+ + 'WHERE firstName=? AND lastName=?';
var params = [
- {'string': firstName}, {'string': lastName}, {'int': creatorTypeID}
+ {'string': firstName}, {'string': lastName}
];
var creatorID = Scholar.DB.valueQuery(sql,params);
@@ -1219,11 +1227,11 @@ Scholar.Creators = new function(){
*
* Returns new creatorID
*/
- function add(firstName, lastName, creatorTypeID){
+ function add(firstName, lastName){
Scholar.debug('Adding new creator', 4);
var sql = 'INSERT INTO creators '
- + 'VALUES (?,?,?,?)';
+ + 'VALUES (?,?,?)';
// Use a random integer for the creatorID
var tries = 10; // # of tries to find a unique id
@@ -1242,10 +1250,11 @@ Scholar.Creators = new function(){
while (exists);
var params = [
- {'int': rnd}, {'int': creatorTypeID},
- {'string': firstName}, {'string': lastName},
+ {'int': rnd}, {'string': firstName}, {'string': lastName}
];
- return Scholar.DB.query(sql,params);
+
+ Scholar.DB.query(sql, params);
+ return rnd;
}
@@ -1260,7 +1269,7 @@ Scholar.Creators = new function(){
var toDelete = Scholar.DB.columnQuery(sql);
if (!toDelete){
- return false;
+ return returnSQL ? '' : false;
}
sql = 'DELETE FROM creators WHERE creatorID NOT IN '
@@ -1286,8 +1295,7 @@ Scholar.Creators = new function(){
if (!creator){
return false;
}
- return creator['firstName'] + '%%%' + creator['lastName']
- + '%%%' + creator['creatorTypeID'];
+ return creator['firstName'] + '%%%' + creator['lastName'];
}
}
@@ -1438,6 +1446,26 @@ Scholar.ItemFields = new function(){
}
}
+
+
+
+Scholar.CreatorTypes = new function(){
+ this.getTypes = getTypes;
+ this.getTypeName = getTypeName;
+
+ function getTypes(){
+ return Scholar.DB.query('SELECT creatorTypeID AS id, '
+ + 'creatorType AS name FROM creatorTypes order BY creatorType');
+ }
+
+ function getTypeName(creatorTypeID){
+ return Scholar.DB.valueQuery('SELECT creatorType FROM creatorTypes '
+ + 'WHERE creatorTypeID=' + creatorTypeID);
+ }
+}
+
+
+
/*
var items = Scholar.Items.getAll();
diff --git a/chrome/chromeFiles/content/scholar/db.js b/chrome/chromeFiles/content/scholar/db.js
@@ -411,11 +411,6 @@ Scholar.DB = new function(){
// update SCHOLAR_CONFIG['DB_VERSION'] to the target version
for (var i=parseInt(fromVersion) + 1; i<=toVersion; i++){
- // For now, just wipe and recreate
- if (i==8){
- _initializeSchema();
- }
-
if (i==9){
Scholar.DB.query("DROP TABLE IF EXISTS objectCreators; "
+ "DROP TABLE IF EXISTS objectData; DROP TABLE IF EXISTS objectKeywords; "
@@ -424,7 +419,12 @@ Scholar.DB = new function(){
_updateDBVersion(i);
}
+ // For now, just wipe and recreate
if (i==10){
+ _initializeSchema();
+ }
+
+ if (i==11){
// do stuff
// _updateDBVersion(i);
}
diff --git a/chrome/chromeFiles/content/scholar/scholar.js b/chrome/chromeFiles/content/scholar/scholar.js
@@ -1,7 +1,7 @@
const SCHOLAR_CONFIG = {
GUID: 'scholar@chnm',
DB_FILE: 'scholar.sqlite',
- DB_VERSION: 9, // must match version at top of schema.sql
+ DB_VERSION: 10, // must match version at top of schema.sql
DB_REBUILD: false, // erase DB and recreate from schema
DEBUG_LOGGING: true,
DEBUG_TO_CONSOLE: true // dump debug messages to console rather than (much slower) Debug Logger
diff --git a/schema.sql b/schema.sql
@@ -1,4 +1,4 @@
--- 9
+-- 10
DROP TABLE IF EXISTS version;
CREATE TABLE version (
@@ -77,11 +77,9 @@
DROP TABLE IF EXISTS creators;
CREATE TABLE creators (
creatorID INT,
- creatorTypeID INT DEFAULT 1,
firstName TEXT,
lastName TEXT,
- PRIMARY KEY (creatorID),
- FOREIGN KEY (creatorTypeID) REFERENCES creatorTypes(creatorTypeID)
+ PRIMARY KEY (creatorID)
);
DROP TABLE IF EXISTS creatorTypes;
@@ -94,10 +92,12 @@
CREATE TABLE itemCreators (
itemID INT,
creatorID INT,
+ creatorTypeID INT DEFAULT 1,
orderIndex INT DEFAULT 0,
PRIMARY KEY (itemID, creatorID),
FOREIGN KEY (itemID) REFERENCES items(itemID),
FOREIGN KEY (creatorID) REFERENCES creators(creatorID)
+ FOREIGN KEY (creatorTypeID) REFERENCES creatorTypes(creatorTypeID)
);
DROP TABLE IF EXISTS folders;
@@ -179,40 +179,44 @@
INSERT INTO "itemData" VALUES(2, 8, 347);
INSERT INTO "itemData" VALUES(2, 9, '0-205-32145-3');
- INSERT INTO "creators" VALUES(1, 1, 'Susan B.', 'Barnes');
- INSERT INTO "creators" VALUES(2, 1, 'J.S.', 'Bassard');
- INSERT INTO "creators" VALUES(3, 1, 'Mary', 'Chayko');
- INSERT INTO "creators" VALUES(4, 1, 'Michael', 'Civin');
- INSERT INTO "creators" VALUES(5, 1, 'Paul', 'DiMaggio');
- INSERT INTO "creators" VALUES(6, 1, 'Leon', 'Festinger');
- INSERT INTO "creators" VALUES(7, 1, 'Stanley', 'Schachter');
- INSERT INTO "creators" VALUES(8, 1, 'Kurt', 'Back');
- INSERT INTO "creators" VALUES(9, 1, 'Steven G.', 'Jones');
- INSERT INTO "creators" VALUES(10, 1, 'J.C.R.', 'Licklider');
- INSERT INTO "creators" VALUES(11, 1, 'Robert W.', 'Taylor');
- INSERT INTO "creators" VALUES(12, 1, 'Yuliang', 'Lui');
- INSERT INTO "creators" VALUES(13, 1, 'Sherry', 'Turkle');
- INSERT INTO "creators" VALUES(14, 1, 'J.', 'Vallee');
- INSERT INTO "creators" VALUES(15, 1, 'Barry', 'Wellman');
-
- INSERT INTO "itemCreators" VALUES(1, 1, 0);
- INSERT INTO "itemCreators" VALUES(2, 1, 0);
- INSERT INTO "itemCreators" VALUES(3, 2, 0);
- INSERT INTO "itemCreators" VALUES(4, 3, 0);
- INSERT INTO "itemCreators" VALUES(5, 4, 0);
- INSERT INTO "itemCreators" VALUES(6, 5, 0);
- INSERT INTO "itemCreators" VALUES(7, 6, 0);
- INSERT INTO "itemCreators" VALUES(8, 9, 0);
- INSERT INTO "itemCreators" VALUES(9, 10, 0);
- INSERT INTO "itemCreators" VALUES(10, 12, 0);
- INSERT INTO "itemCreators" VALUES(11, 13, 0);
- INSERT INTO "itemCreators" VALUES(12, 13, 0);
- INSERT INTO "itemCreators" VALUES(13, 14, 0);
- INSERT INTO "itemCreators" VALUES(14, 15, 0);
- INSERT INTO "itemCreators" VALUES(15, 15, 0);
- INSERT INTO "itemCreators" VALUES(7, 7, 1);
- INSERT INTO "itemCreators" VALUES(7, 8, 2);
- INSERT INTO "itemCreators" VALUES(9, 11, 1);
+ INSERT INTO "creatorTypes" VALUES(1, "author");
+ INSERT INTO "creatorTypes" VALUES(2, "contributor");
+ INSERT INTO "creatorTypes" VALUES(3, "editor");
+
+ INSERT INTO "creators" VALUES(1, 'Susan B.', 'Barnes');
+ INSERT INTO "creators" VALUES(2, 'J.S.', 'Bassard');
+ INSERT INTO "creators" VALUES(3, 'Mary', 'Chayko');
+ INSERT INTO "creators" VALUES(4, 'Michael', 'Civin');
+ INSERT INTO "creators" VALUES(5, 'Paul', 'DiMaggio');
+ INSERT INTO "creators" VALUES(6, 'Leon', 'Festinger');
+ INSERT INTO "creators" VALUES(7, 'Stanley', 'Schachter');
+ INSERT INTO "creators" VALUES(8, 'Kurt', 'Back');
+ INSERT INTO "creators" VALUES(9, 'Steven G.', 'Jones');
+ INSERT INTO "creators" VALUES(10, 'J.C.R.', 'Licklider');
+ INSERT INTO "creators" VALUES(11, 'Robert W.', 'Taylor');
+ INSERT INTO "creators" VALUES(12, 'Yuliang', 'Lui');
+ INSERT INTO "creators" VALUES(13, 'Sherry', 'Turkle');
+ INSERT INTO "creators" VALUES(14, 'J.', 'Vallee');
+ INSERT INTO "creators" VALUES(15, 'Barry', 'Wellman');
+
+ INSERT INTO "itemCreators" VALUES(1, 1, 1, 0);
+ INSERT INTO "itemCreators" VALUES(2, 1, 1, 0);
+ INSERT INTO "itemCreators" VALUES(3, 2, 1, 0);
+ INSERT INTO "itemCreators" VALUES(4, 3, 1, 0);
+ INSERT INTO "itemCreators" VALUES(5, 4, 1, 0);
+ INSERT INTO "itemCreators" VALUES(6, 5, 1, 0);
+ INSERT INTO "itemCreators" VALUES(7, 6, 1, 0);
+ INSERT INTO "itemCreators" VALUES(8, 9, 1, 0);
+ INSERT INTO "itemCreators" VALUES(9, 10, 1, 0);
+ INSERT INTO "itemCreators" VALUES(10, 12, 1, 0);
+ INSERT INTO "itemCreators" VALUES(11, 13, 1, 0);
+ INSERT INTO "itemCreators" VALUES(12, 13, 1, 0);
+ INSERT INTO "itemCreators" VALUES(13, 14, 1, 0);
+ INSERT INTO "itemCreators" VALUES(14, 15, 1, 0);
+ INSERT INTO "itemCreators" VALUES(15, 15, 1, 0);
+ INSERT INTO "itemCreators" VALUES(7, 7, 1, 1);
+ INSERT INTO "itemCreators" VALUES(7, 8, 1, 2);
+ INSERT INTO "itemCreators" VALUES(9, 11, 1, 1);
INSERT INTO folders VALUES (1241, 'Test Folder');
INSERT INTO folders VALUES (3262, 'Another Test Folder');
