In which database table are the abstracts stored?

cheflo

I am using the Zotero5 beta on Linux, and I am trying to query the item title, author(s), and abstracts for keywords using the sqlite database. I have found the authors in the table with the same name, and the title is in the itemNotes-table. However, I can't seem to find the abstracts anywhere. I am new to sqlite, and I could not find an effective way of searching all the tables in the database for an abstract-column.

I found the fieldName 'abstractNote' in the fields-table, but I could not find the actual text of the abstract anywhere. I also checked the documentation at http://zomark.github.io/zotero-marc/schema/trunk/columns.byTable.html, but could not find mention of an abstract column anywhere (although that is from 2010, so might have been added since).

These are the tables I have in my zotero database:


annotations itemNotes
baseFieldMappings itemRelations
baseFieldMappingsCombined itemTags
charsets itemTypeCreatorTypes
collectionItems itemTypeFields
collectionRelations itemTypeFieldsCombined
collections itemTypes
creatorTypes itemTypesCombined
creators items
customBaseFieldMappings libraries
customFields proxies
customItemTypeFields proxyHosts
customItemTypes relationPredicates
deletedItems savedSearchConditions
feedItems savedSearches
feeds settings
fieldFormats storageDeleteLog
fields syncCache
fieldsCombined syncDeleteLog
fileTypeMimeTypes syncObjectTypes
fileTypes syncQueue
fulltextItemWords syncedSettings
fulltextItems tags
fulltextWords transactionLog
groupItems transactionSets
groups transactions
highlights translatorCache
itemAttachments users
itemCreators version
itemData zoteroDummyTable
itemDataValues


bwiernik

What exactly are you trying to do with the sqlite que writes? What's the goal?

cheflo

I don't want to write anything to the database via sqlite, I read that this was not recommended. I just want to be able to use the command line to query the database and return items matching keywords in the author, title, and abstract fields.

adamsmith

I don't know much about sqlite, but the fieldID for abstractNote is 90. Every abstract for every item is then assigned a valueID in itemData and that value ID is mapped to a value in ItemDataValues.

Check out https://github.com/smathot/qnotero which does something along the lines of what it seems like you want to do in python.

cheflo

Thanks, I couldn't really figure out the database setup, so I ended up querying my library through pyzotero in the end.

Gurdas_Sandhu

Just curious, why would you need to query the database directly, or use qnotero, when the same search can be done in Zotero using the advanced search feature? Are direct searches faster?

adamsmith

Qnotero is incredibly fast and runs without firing up Zotero, so that is very convenient, yes.
The other reason one might want to read the db directly would be any sort of integration with other software locally. One example is e.g. Mendeley's ability to sync the Zotero library into Mendeley.

Gurdas_Sandhu

Thanks, good to know that Mendeley can do one time import or continuously sync with Zotero sqlite file.

cheflo

Personally, I spend much of my time at the command line, and it feels unnecessary to launch the entire Zotero interface when I just want to read a paper or glance at a figure again. There is a great software called zotero-cli (https://github.com/jbaiter/zotero-cli) for querying Zotero from the command line, and I was trying to extend some of its functionality.