search

mtlynch / picoshare

A minimalist, easy-to-host service for sharing images and other files
https://demo.pico.rocks
Other
2.34k stars 169 forks source link

Replace sqlite3 driver with ncruces version #567

Open mtlynch opened 7 months ago

mtlynch commented 7 months ago

Resolves #566

Current status (2024-09-10): Code works locally, fails in e2e and some unit tests

ncruces commented 2 months ago

Hi!

Do you intend to use both drivers together, or port to my driver?

Using two different instances/versions of SQLite in the same process is a really bad idea. Due to the way POSIX locking works it can easily lead to data corruption, missed updates, etc.

mtlynch commented 2 months ago

Hey, @ncruces! Nice surprise that you found this! Thanks for your work on your driver.

Do you intend to use both drivers together, or port to my driver?

Using two different instances/versions of SQLite in the same process is a really bad idea. Due to the way POSIX locking works it can easily lead to data corruption, missed updates, etc.

I'm trying to port entirely over to your driver.

The issue I'm running into is that I only need a SQLite-specific API in one spot. Everywhere else, I'd like to continue using the generic databases/sql API.

What would you recommend for this scenario?

ncruces commented 2 months ago

Good that you're not trying to mix drivers. That's a little known SQLite pitfall.

I would recommend you use the writeblob function from the blobio extension. See an example.

You can bind an io.Reader by replacing message in this line with sqlite3.Pointer(reader).

The reader will be copied to the blob.

The blobio extension can similarly be used to “stream” blobs from the database. That's also in the example.

mtlynch commented 2 months ago

@ncruces - Thanks so much! I'll give it a shot.

ncruces commented 2 months ago

No problem.

Heads up, I'll be improving the extension on the next release: https://github.com/ncruces/go-sqlite3/commit/356dd56e5f1d79c3dec914cb981e9cea362608f9

Writing the blob with writeblob is essentially unchanged; reading can now be done simpler with readblob; openblob stays for more advanced scenarios.