Found non-critical error in Database structure

table itemData
field valueID - type TEXT

table itemDataValues
field valueID - type INTEGER
  • 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'.
  • 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
  • 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.
  • 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:

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



    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
  • 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.
Sign In or Register to comment.