I have worked with SQL-bots, GPT4 and Langchain. Super easy to get started. I do not know how the Zotero backend works, why would you need SQL?

Longing for a RAG setup to find references fast.

"why would you need SQL?" - to get the metadata, which are stored in a sqlite db.

I'm now looking into a setup that exports a selected group library to a json(l) file with the key fields I need (including the cached .ft and the path to the pdf) and then a) uses the .ft text in the RAG; AND b) (maybe) feeds the pdfs through GROBID for 'cleaner' text (it does a pretty good job in stripping out just the text - as well as all sorts of other elements). The nice thing is that this will then allow us to

My main stumbling block right now is that I am not sure where Zotero stores the groupID. I looked for a particular itemID (which I got from the 'key' column in the 'items' table) in all tables, but I can't find the zg there. I do see things like libraryID but I'm not sure that's the right field. And when I look for the zg of that item, I also can't find it. The point is of course to do a query on that groupID so at to work with JUST those (I have a quite gigantic Zotero collection).

So I'd appreciate it if somebody could confirm that that libraryID is indeed the group identifier. And then I'll take it from there.

UPD - I wrote some code to show me the items from that libraryID, and it is indeed the right one. It is '200', which I thought was an overly suspicious 'round' number, but I guess it IS the right number! :)

Nobody?

Here is my current understanding (but please correct me if I'm wrong!) on how keys, library IDs, and collection IDs relate within Zotero's sqlite database structure, and how they correlate to the physical storage paths of items and their associated files.

What I THINK I was able to figure out

• Item Key (key): Unique identifier for each item within Zotero. I THOUGHT this key was the one used to name folders within the Zotero storage directory, but that appears NOT to be the case


• Library ID (libraryID): Identifies a specific library within Zotero. A single Zotero account can have multiple libraries (e.g., personal library, group libraries).


• Collection ID (collectionID): Identifies collections within a library. Collections are a way to organize items and can be nested.

What I STILL haven't been able to figure out

• in which table in the db I can find the key that
  
  
  • corresponds to the folder name in my Zotero storage
  
  
  • will allow me to add the automatically extracted content of the '.zotero-ft-cache' file in that folder to the corresponding row in the dataframe that I WAS able to extract from the sqlite db
  
  


• how (once I can 'hook up' the rows in my df to the correct Zotero storage folder) I can then 'feed'  all the pdfs iknto gribid and get (hopefully) 'cleaner' text - again in another column in my df..


• how I might be able to bypass pandas dataframes altogether and to just 'hook up' a RAG the 'right way' to the sqlite dd

I fully realize this is an uncommon request, but I would still be very grateful of somebody could provide some more clarity on this...

Thanks!

 

Thanks Sebastian! That's useful info. So I'll try to focus on the itemAttachments table and get the path from there (see here for the schema Zotero ERD)... 

Fingers crossed!

Just an update -
A reverse search on my entire Zotero sqlite db for a known folder name in my Zotero
storage folder from the collection I'm working with, tells me that the 'items' table has a 'key' column with that very content. I'm now trying to find a way to write code that can use this to add the .zotero-ft-cache to a 'full_text' column in my df_zotero... So something like: look in my db for all itemIDs in this collectionID, then use that to find the right 'key', and then find that sub-folder in my Zotero storage folder and add the text to my df... Hope springs eternal!

Haven't had much time this weekend, but as I said last time, I had already managed to get working code to get all items of the selected Zotero collection into a dataframe that looks like this:

plaintext
Copy code
RangeIndex: 1241 entries, 0 to 1240
Data columns (total 24 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 itemID 1241 non-null int64
1 author_last_name 1241 non-null object
2 author_first_name 1241 non-null object
3 abstractNote 1239 non-null object
4 accessDate 1241 non-null object
5 date 1241 non-null object
6 extra 1241 non-null object
7 language 1241 non-null object
8 libraryCatalog 1241 non-null object
9 numPages 1241 non-null int64
10 place 1241 non-null object
11 rights 1241 non-null object
12 shortTitle 889 non-null object
13 thesisType 1241 non-null object
14 title 1241 non-null object
15 university 1241 non-null object
16 url 1241 non-null object
17 path 1187 non-null object
18 degreeType 1241 non-null object
19 thesisTypeFull 1241 non-null object
20 key 1241 non-null object
21 folder_name 1187 non-null object
22 pdf_name 1187 non-null object
23 zotero-ft-cache 1187 non-null object
dtypes: int64(2), object(22)

I then made a second notebook that first of all feeds the pdfs from that collection from their storage path through GROBID to 'clean them up'. GROBID is an open source free machine learning library from AI2 (the guys and gals behind Semantic Scholar) for extracting, parsing, and restructuring raw documents such as academic publications into structured and semantically rich XML/TEI encoded documents. The library does a(n IMO) reasonable job with Header Extraction (extracting metadata from the headers of academic papers, like titles, authors, abstracts, keywords, etc.); Reference Parsing (parsing out and structuring the bibliographic references); but especially also Full-text Structuring (GROBID not only works on headers and references but can also structure the full text of articles, breaking it down into sections like introduction, methodology, results, etc. ) The results of all of this (in my Jupyter Notebook) is a 'results' dictionary containing all of that information - each row essentially being an xml file. Since the XML/TEI schema is VERY complex, this is what threw me for a loop until I found s2orc's very nice 'tei2json' script. The second part of this notebook therefore uses that script to create a simple dataframe that looks like this:

plaintext
Copy code
RangeIndex: 1106 entries, 0 to 1105
Data columns (total 10 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 paper_id 1106 non-null object
1 pdf_hash 1106 non-null object
2 metadata 1106 non-null object
3 abstract 1106 non-null object
4 body_text 1106 non-null object
5 back_matter 1106 non-null object
6 bib_entries 1106 non-null object
7 ref_entries 1106 non-null object
8 raw_abstract_text 1106 non-null object
9 raw_body_text 1106 non-null object
dtypes: object(10)

This gives us a nice 'raw_body_text' column to work with. So the rest of that notebook then uses fasttext to identify the language used in the 'raw_body_text' column and store than in a 'lang' column; which we then use to have Facebook's 'facebook/mbart-large-50-many-to-many-mmt' language model to translate all of the dissertations from their original language to English in a new 'raw_body_text_en' that looks like this:

plaintext
Copy code
Language Truncated Original Body Text \
0 en Energy has recently become a very important it...
1 es En los últimos tres años, se ha observado una ...
2 fr Je remercie tout d'abord mes directeurs de rec...
3 tr Muammer Kaddafi'nin elde ettiği petrol gelirle...
4 ru 1 Зaпaдный бepeг pеки Иоpдaн -peгион нa Ближнe...

Truncated Translated Body Text
0 Energy has recently become a very important it...
1 In the last three years, there has been a tran...
2 I thank my research directors Michel Gagnon an...
3 Let's review Libya and what it does for the pe...
4 One of the main thrusts of this project is to ...

I then moved on to spaCy's 'en_core_web_lg' language model in order to sentencize all dissertations, creating a new dataframe with the index number of the actual dissertation from the original df, and then a column with the first sentence of the first diss, with the second sentence being in the second row, etc.

I then generated the embeddings of these sentences with 'BAAI/bge-large-en-v1.5' and started to get all of this into a weaviate vector database. But I then figured we'd miss too much of the context and we'd be better off with paragraphs. So I played around with paragraphs as well (splitting the texts on '\n's). That gave me the following stats:

Average number of paragraphs per dissertation: 444.1907775768535
Minimum number of paragraphs in a dissertation: 0
Maximum number of paragraphs in a dissertation: 2333
Dissertations with fewer than 50 paragraphs: 42
So that's not half bad.

But then I started reading up on semantic chunking (like here) and these overlapping (and semantically enhanced) chunking methods, which sound ideal to me. So next time I find some time that's where I plan to take this.

As always - I put my notebooks on my github. And so if somebody with more experience on RAGs is willing to pick it up from there (and/or to clean up my code) - please be my guest!

Some as Reor (https://github.com/reorproject/reor) does?
It sounds amazing...

@nicolasld - count me in!