Question: How To execute "All Platforms (Advanced)" section????

WA
I have a question about how to execute the "All Platforms (Advanced)" section of "Manual Zotero Database Repair Instructions" at https://www.zotero.org/utils/dbfix/manual.

This section consists of a series of SQL statements, which I assume to be an SQL script to be invoked from the sqlite prompt. The primary purpose of the script appears to be to delete "Child" rows from specified tables for which the "Parent" table does not contain the "Child" row's foreign key.

Having copied all the SQL statements to MS Notepad, I assume I can change the extension to .sql if necessary.

The closest "solution" I have come across for executing the "Advanced..." section is the "The Tcl interface to the SQLite library" http://www.sqlite.org/tclsqlite.html#transaction. The closest dbcmd may be "eval". The examples for "eval" appear to be line by line, although one mentions "SCRIPT".

Could someone kindly tell me how to execute the "Advanced..." section? The "closest solution" above is too mired in ignorance (mine) and uncertainty to let rip against a live database. Even if it works technically, I have no idea what landmines might remain if I execute it in the wrong way. (I have not noticed a "log" function.)

Thank you for any help you can give.

Environment: Windows XP SP3, Firefox 10.0.1, Zotero 3.0.2
  • adamsmith
    we can't fully support this, no. Have you seen these instructions?
    http://forums.zotero.org/discussion/2607/
  • WA
    edited February 15, 2012
    Adam. Thanks. Yes, I have. In fact, I used them (and another discussion) to document a command-line script which I executed line-by-line. The Zotero database still worked even though it still had integrity errors.

    What I need to find out is how to execute the block of SQL statements in "All Platforms (Advanced)". I suspect it can be done through the dbcmd within sqlite, referencing the SQL statements as a script.

    But I am uncertain, and would like some expert advice. Thanks again.
  • dstillman
    edited February 15, 2012
    You just need to run the commands in an SQLite client—that's all. If you need general help with using an SQLite client, you'll have to ask elsewhere.
  • WA
    OK, Dan and Adam. Thanks for your patience with me.
  • dstillman
    Note, though, that the manual instructions tell you exactly what to do:
    To run these commands manually, open your database in an SQLite client (e.g., 'sqlite3 zotero.sqlite') and paste in the following block of statements
    If that doesn't make sense to you, ask for SQLite client help somewhere. None of the stuff you mention above is what you want to be using.
  • WA
    edited February 16, 2012
    I will try this later this morning (12:39 am now). Previously, I had tried "Paste" to no effect ("Paste" being ^V after picking up external text with ^C). I'll let you know (here) what happens.

    You are exactly right about dbcmd ?SCRIPT?. Your suggestion prompted me to think about the discovery below:

    Late last evening, I found that the Windows "cmd" window had a right-click context menu from the top border, in which a "paste" command was to be found in the sub-menu of "Edit".
  • mronkko
    WA: You need an Sqlite client. Google for

    sqlite client for windows

    You will find several software that you can use. You do not need the windows command prompt or the Sqlite programming interfaces.
  • dstillman
    edited February 16, 2012
    The official SQLite client is a command-line program. I assume that's what WA is referring to regarding "cmd".

    (But yes, there are plenty of GUI alternatives.)
  • WA
    Dan,
    Your suggestion worked. The SQL statements in "All Platforms (Advanced)"
    were pasted into the Windows command shell window using Edit->Paste command under the context menu (right-click the top border of the command window).

    "Check Database Integrity" under Preferences - Advanced reported no problems. It had been dirty up to this point.

    The sequence was:
    Disable Zotero Add-on and close Firefox.
    Backup zotero.sqlite and zotero.sqlite.bak
    Start-Run-"cmd" (w/o quotes)
    >CD {path to Zotero storage where sqlite3.exe, zotero.sqlite and zotero.sqlite.bak} in quotes as necessary.
    >sqlite3.exe zotero.sqlite
    Copy the SQL statements in "All Platforms (Advanced) as one block.
    sqlite> {Context Menu Edit->Paste}, {enter}

    This will take a couple of minutes for ~250 MB.

    Hearty thanks to all!

    Environment: Windows XP SP3, Firefox 10.0.1, Zotero 3.0.2, sqlite3.exe in Zotero storage.
Sign In or Register to comment.