Error encountered when using SQLite for the archive

[tphillip913]

I have gallery-dl 1.12.1 installed on Centos7 (Up to date) and I've set the following line in the gallery-dl.cof file in the extractor section:

"archive": "/home/archivist/bin/gallery-dl-cache.sqlite3",

When I run the following command:

gallery-dl -d rips/reddit/verylargeimages https://www.reddit.com/r/verylargeimages/new

I get the following error:

[reddit][warning] Failed to open download archive at '/home/archivist/bin/gallery-dl-cache.sqlite3' ('OperationalError: near "WITHOUT": syntax error')

The account the command is run on has full rights to the directory specified and a 0 byte file is written when the command starts.

Am I missing a step in either configuring or initializing the database?

[mikf]

What's the output of

• sqlite3 --version and
• python3 -c 'import sqlite3; print(sqlite3.sqlite_version)'?

I'm guessing your installed version of SQLite is older than 3.8.2 and therefore doesn't understand the WITHOUT ROWID clause at the end of table initialization. (see WITHOUT ROWID compatibility)

[tphillip913]

You would be correct:
/usr/bin/sqlite3 --version
3.7.17 2013-05-20 00:56:22 118a3b35693b134d56ebd780123b7fd6f1497668

Seems Centos7 requires 3.7.

Time to install the latest. Hopefully everything will pick up the right version. :)

python3 -c 'import sqlite3; print(sqlite3.sqlite_version)'
3.7.17

[mikf]

WITHOUT ROWID is not necessary, it just reduces the SQLite file size by quite a lot.

Before you put in all the effort of installing a new version, you could just remove the clause from your local gallery-dl installation in

gallery-dl/gallery_dl/util.py

Lines 743 to 744 in 1afb913

	self.cursor.execute("CREATE TABLE IF NOT EXISTS archive "
	"(entry PRIMARY KEY) WITHOUT ROWID")

and I'll add a fallback into the next version.

[tphillip913]

Thanks for the suggestion. Centos7 really depends on 3.7 and trying to upgrade to the latest produces a lot of packages going "You SURE you want to do this?".

[tphillip913]

Thank you very much. Looks to be working now.

Looking forward to your next release so I don't have to track this direct edit. It may be time to upgrade to Centos8 too.

[AlttiRi]

Oops, a wrong topic. Moved to: #1183 (comment)

---

BTW

WITHOUT ROWID is not necessary, it just reduces the SQLite file size by quite a lot.

Yes, it increases the size by 70 %, however, with it the entries are ordered by the insert time.

Using of a separate table for each service will also reduce the size of the DB, in this case no need to add a prefix with the service name to each row.