Found non-critical error in Database structure

dr_avk · February 14, 2018

table itemData
field valueID - type TEXT

table itemDataValues
field valueID - type INTEGER

dstillman · February 15, 2018

Thanks, but that's not the case. If a column type isn't specified, it defaults to NUMERIC affinity. If you do a "SELECT *, TYPEOF(valueID) FROM itemData;" you should see that all values are of type 'integer'.

dr_avk · February 15, 2018

Dstillmann - It is not the case, really.
I found it, because I use MSAccess to connect to Zotero DB and tried to create query.
Access couldn't complete the query joing due to itemData.value.ID was defined as MEMO. So, I need to change it type to integer in Zotero

dstillman · February 15, 2018

I can't help you with Access. I'm just telling you how SQLite works. The values in that column are stored as integers, which you can verify for yourself in SQLite.

dr_avk · February 15, 2018

Hi, dstillman

At first, I am personally don't need help. I already correct this issue.

At second, if it is important...

I don't know SQLile, so I need some other software.
I work with Access.

However, I tried to use 2 different type of database software:
- MS Access 2003
- DB Browser for SQLite 3.10.1
Last is freeware:
https://github.com/sqlitebrowser/sqlitebrowser/releases
or
http://sqlitebrowser.org/

Both of them (MS Access 2003 and DB Browser for SQLite ) indicate, that field valueID in the table itemData

IS NOT INTEGER

Screenshots:
http://www.triacon.org/tale/ms_access.jpg
http://www.triacon.org/tale/db_browser.jpg

You can try it by yourself with DB Browser for SQLite 3.10.1

However, it can be in my zotero database only, possible.
And again - I don't need help with correction.
I changed it type to INTEGER by DB Browser for SQLite (Access doesn't allow)

Sincerely yours
Andrey

dstillman · February 15, 2018

Actually, I misspoke slightly, though the result is the same. While it's true that the type is unspecified, that gives it an affinity of type BLOB (formerly called NONE). But due to SQLite's column affinity, that still means that the values are inserted unchanged, and Zotero inserts them as integers. In the 40 or so test database I have lying around dating back to 2011, all have the column defined the same way, and all contain only integer values ("SELECT DISTINCT TYPEOF(valueID) FROM itemData;").

If it's actually defined as TEXT for you (and "PRAGMA table_info(itemData);" in the official client would be the reliable way to check), I think this must've been something you changed yourself somehow (e.g., by running something that rebuilt the database with no-type columns set to TEXT rather than BLOB/NONE), since the line where the column type is defined hasn't changed since 2007 when it was created.