Bug: Bad foreign key definition for table fields

ngirard

The schema for the table 'fields' is currently (as of zotero 1.0.4):

CREATE TABLE fields (
fieldID INTEGER PRIMARY KEY,
fieldName TEXT,
fieldFormatID INT,
FOREIGN KEY (fieldFormatID) REFERENCES fieldFormat(fieldFormatID)
);

The FOREIGN KEY references a non-existing table fieldFormat (how the *hell* could such a thing possibly happen ? Looks like a good candidate for an sqlite bug report... anyway)

The correct FOREIGN KEY definition should be:

FOREIGN KEY (fieldFormatID) REFERENCES fieldFormats(fieldFormatID)

The following SQL statements solve the problem:

CREATE TEMPORARY TABLE fieldsTemp AS SELECT * FROM fields;
DROP TABLE fields;
CREATE TABLE IF NOT EXISTS fields (
fieldID INTEGER PRIMARY KEY,
fieldName TEXT,
fieldFormatID INT,
FOREIGN KEY (fieldFormatID) REFERENCES fieldFormats(fieldFormatID)
);
INSERT INTO fields SELECT * FROM fieldsTemp;
DROP TABLE fieldsTemp;

dstillman

The FOREIGN KEY references a non-existing table fieldFormat (how the *hell* could such a thing possibly happen ? Looks like a good candidate for an sqlite bug report...

Foreign key constraints in SQLite are currently parsed but not interpreted or enforced. (The fieldFormats table is also unused in Zotero, for what it's worth.)

Anyhow, thanks for the heads-up. We'll fix it.

ngirard

Thanks in advance. A consistent schema is necessary for ORM tools like SQLalchemy to create an object representation of the data using reflection.