I am trying to reproduce the work done notably by LucasD some years ago when he successfully integrated InfoQube with the SQLite database which underlies the Zotero reference management system.
The problem which I wish to report is that when I attempt to synchronise my IQbase with that SQLite database, absolutely nothing happens. There is no error message but nor is any data imported.
1. What follows is the story of my investigation so far.
2. I have taken a copy of the Zotero SQLite database, and it is that which I am using in my investigations.
3. I have installed both 64-bit and 32-bit SQLite ODBC drivers, as found at: http://www.ch-werner.de/sqliteodbc/. The reason I have installed both is that the operating system is 64-bit but I strongly suspect that the Jet engine underlying InfoQube is 32 bit. Firefox remains 32-bit for the time being – but see https://wiki.mozilla.org/Firefox/win64.
4. Question: should I uninstall the 64-bit SQLite ODBC driver?
5. From the InfoQube website, I have downloaded the sample SQLNotes issues tracking database and its associated SQLsync.xml file.
However, this is no longer useful. An error is reported when the IQbase is first loaded, stating that the format cannot be upgraded. This does not appear to cause problems, because the IQbase opens normally. I have then created a grid, called item, based upon item and deleted all items so as to have a clean IQbase and to be able to retry the SQLsynch.xml file using tools/database management/synch with external database. When I do so, I get an error message:
Cannot connect to server (application-defined or object-defined error).
No doubt this is because the Mantis database has either been moved or is no longer in use. Since the documentation continues to refer to this sample, I think it would be best if the sample were corrected to refer to a current MySQL database. (I also think that this has low priority as an issue.)
6. I have wanted to reuse the work by LucasD with as little change as possible, at least initially while I am testing that the approach still works. So far as I can understand what is required from the rather limited documentation which is available, the changes necessary to integrate Zotero access into my test IQbase, phase1.SNDB, should be the following:
7. Create fields in my IQbase which correspond to the field names mentioned in the script produced by LucasD. The following screenshot shows only the last page of Zotero field definitions:
[Once again, I cannot upload a perfectly-ordinary .png file! It is an attachment to this post.]
8. I have made the assumption that all fields should be of type text. I have in fact tried both text and number for the field Zotero_ID, but this makes no difference to the problem I am reporting.
9. I have then created a grid called bibliography which is based on the field Zotero_ID.
The text of the SQLSynch.xml file, which I am editing using the editor Notepad++, is as follows:
<ConnectionString>
C:\Users\Admin\AppData\Roaming\Mozilla\Firefox\Profiles\a1iw9b2s.default\zotero\zotero - Copy.sqlite;
[datemodified] dateupdate
[Type] z12_ItemType
[Author(s)] z1a_Authors
[Editor(s)] z1b_Editors
[Book Title] z13_BookTitle
[Journal] z15_Journal
[Volume] z16_Volume
[Abstract] z19_Abstract
[Thesis Type] z18_Diss_type
[Publisher] z23_Publisher
[Collections] z28_Collections
[parentCollectionID] z29_collectionparentID
[Attachments] z30_Attachments
[Related Sources] z31_Related_Sources
[Related Notes] z32_Related_Notes
IMPORT IDImportType
kingdata0a.lastname, kingdata0e.lastname, kingdata1a.lastname, kingdata1e.lastname, kingdata2a.lastname, kingdata2e.lastname, kingdata3a.lastname, kingdata3e.lastname," ")
kingdata0a.firstname, kingdata0e.firstname, kingdata1a.firstname, kingdata1e.firstname, kingdata2a.firstname, kingdata2e.firstname, kingdata3a.firstname, kingdata3e.firstname," ")
(select ", " where authormaincreatordata.firstname is not null) ||
(select ", " where editormaincreatordata.firstname is not null) ||
substr(coalesce(yearvalues.value,"[ ]"), 1, 4) as Year,
group_concat(distinct collections.collectionName) as Collections,
on items.itemid=StandaloneNotes.itemid and StandaloneNotes.sourceitemid is null
on creators.creatordataid=EditorMainCreatorData.creatordataid and itemcreators.creatortypeid=3
on creators.creatordataid=AuthorMainCreatorData.creatordataid and itemcreators.creatortypeid=1
on items.itemid=itemcreators0.itemid and itemcreators0.orderindex=0
on items.itemid=itemcreators1.itemid and itemcreators1.orderindex=1
on items.itemid=itemcreators2.itemid and itemcreators2.orderindex=2
on items.itemid=itemcreators3.itemid and itemcreators3.orderindex=3
(creators0.creatordataid=Kingdata0a.creatordataid and itemcreators0.creatortypeid=1)
(creators0.creatordataid=Kingdata0e.creatordataid and (itemcreators1.creatortypeid!=1 OR itemcreators1.creatortypeid is null) AND (itemcreators2.creatortypeid!=1 OR itemcreators2.creatortypeid is null) AND (itemcreators3.creatortypeid!=1 OR itemcreators3.creatortypeid is null) AND itemcreators0.creatortypeid=3)
(creators1.creatordataid=Kingdata1a.creatordataid and itemcreators1.creatortypeid=1)
(creators1.creatordataid=Kingdata1e.creatordataid and itemcreators0.creatortypeid!=1 AND itemcreators2.creatortypeid!=1 AND itemcreators3.creatortypeid!=1 AND itemcreators1.creatortypeid=3)
(creators2.creatordataid=Kingdata2a.creatordataid and itemcreators2.creatortypeid=1)
(creators2.creatordataid=Kingdata2e.creatordataid and itemcreators0.creatortypeid!=1 AND itemcreators1.creatortypeid!=1 AND itemcreators3.creatortypeid!=1 AND itemcreators2.creatortypeid=3)
(creators3.creatordataid=Kingdata3a.creatordataid and itemcreators3.creatortypeid=1)
(creators3.creatordataid=Kingdata3e.creatordataid and itemcreators0.creatortypeid!=1 AND itemcreators1.creatortypeid!=1 AND itemcreators2.creatortypeid!=1 AND itemcreators3.creatortypeid=3)
on items.itemid=abstractdata.itemid and abstractdata.fieldid=90
on items.itemid=booktitledata.itemid and booktitledata.fieldid=115
on items.itemid=journaldata.itemid and journaldata.fieldid=12
on items.itemid=journalvolumedata.itemid and journalvolumedata.fieldid=4
on journalvolumedata.valueid=journalvolumedatavalues.valueid
on items.itemid=journalissuedata.itemid and journalissuedata.fieldid=5
on items.itemid=publisherdata.itemid and publisherdata.fieldid=8
on seealsoout.linkeditemid=seealsooutTitledata.itemid and seealsooutTitledata.fieldid=110
on seealsoin.itemid=seealsoinTitledata.itemid and seealsoinTitledata.fieldid=110
on seealsooutTitledata.valueid=seealsooutTitledatavalues.valueid
on seealsoinTitledata.valueid=seealsoinTitledatavalues.valueid
<RecordStatus>IDimportType ADD update</RecordStatus>
<edit> I should point out that I have tested the SQL using SQLite manager in Firefox, and it works fine. </edit>
10. Some questions which I have concerning this configuration file include:
What is the significance of the RecordStatus line towards the end of the configuration file; and is it correct?
What datatype should the field Zotero_ID have?
11. And then of course the really big question, what I done wrong and what can I do to fix it?
Thanks as always.
Comments
I have spent years looking for, and hoping for, a better relational database management system for individuals and small groups than Microsoft Access. I am deeply unimpressed by most of the alternative solutions that are on offer. Quite simply, they do not offer the combination of ease-of-use, power of UX and cost of ownership that Access does. I use Access for structured data and IQ for semistructured data and for information management where hierarchies and multiple parents are essential – Microsoft Access does these but only with great difficulty. At long last, Microsoft Access is web-accessible (version 2013). I shall persist in its usage for a little longer, and would love to be able to integrate Microsoft Access and InfoQube and Zotero/SQLite at one and the same time. With Pierre's active help, I anticipate getting the InfoQube and Zotero integration working well in the near future. I shall report on my experiences later, but I am already using InfoQube very well both for classification ("what kind of a thing is it? is it a bird, is it a plane, or is it just Superman" – mutually exclusive) and for categorisation / tagging - is it blue?, is it yellow, does it fly?, does it love journalists? (Superman has all of these properties, a canary has two of them, and I have none.) To be able to integrate Microsoft Access, and InfoQube, and SQLite at one and the same time would be great. I can for now survive with choosing which database – SQLite or Microsoft Access – to integrate with each time that I use the product.