r/sqlite

All about testing

There are a lot of SQLite clients out there, from very simple viewers to full database tools, but I’m curious where people here draw the line between “basic functionality” and something worth paying for.

Things like editing data, multiple databases/connections, import/export, backups, schema tools, autocomplete, query history, etc.

Which features do you think should just be included for free in a good SQLite client?

And what would you actually be happy to pay for?

i have a whatsapp sqlite file but what do i do with it i cant convert it, can view it in a mess

i just want to convert it into something readable.

I have a bit of a specific situation with how my workplace structures SQLite databases that I'm hoping to find a better solution for, mainly related to efficient querying across many database files.

Where I work, we handle a lot of trail camera data (images) that have associated data for what's in the image stored in SQLite databases—all of this is manually filled out as someone goes through image by image. Right now, we're nearing 100 total database files, each storing records for between 300k and 1 million images. We use the program Timelapse from UC Calgary to tag all the images and continually go back in to update things or add new data, so our individual database files have to basically stay as is for this to remain functional.

In the past, we've had people export CSV files from the databases then someone merged them all together with a bunch of really annoying, fragile code. This is, of course, annoying but also doesn't reflect any new changes that happen after that export. I've come in with more expertise in general on programming than they usually get and showed that you can pull things directly from each database with R (we're biologists, that's just the language we're used to). Because of this, we want to come up with a solution that lets us do this across all the databases as needed and stop doing that CSVs.

Right now, running a simple query that results in ~3k rows across most (not all, ~70%) of the individual databases takes about 3 minutes which is fine if you just need a big export, but we'd like to have some interactive visualization tools where a 3 minute wait for something you might decide to change to look at something else right after wouldn't be good for.

Originally I hoped for a way to establish a single connection in a "master" database that would remove some of this latency, but ATTACH which would be the solution isn't going to work for us in about 1-2 years when we exceed the maximum on the number of attachable databases at once. The other potential solution is to have a master database that contains copies of everything in the other database files that updates each night, but we already have issues keeping enough storage available with how much space images take up (and this kinda feels like the lazy solution). For any solution, something we can feasibly manage ourselves after some the initial setup would be preferential because IT can be quite slow to act and we are the ones who will be accessing and editing this data 99% of the time.

Talking to a family member (former IT worker) led me to possibly Microsoft SQL server or some sort of local Oracle database, but unsure if any of this would fit our needs and the suggestions were based on their experience from like 15+ years ago. Whatever we use needs to be able to connect with R since that's what existing pipelines use and many of the ecology-related tools we use aren't in any other language. Really in the end we just want some way to make things run faster and ideally combine things into a single connection point to simplify how we get to the data. Even if it's too complex for us, we still need to give IT a plan with enough details to get them going... or we'll be forever in CSV land.

Really just looking for any guidance since googling didn't get me very far, most people seem to have big issues with write speeds but it actually seems quicker to overwrite a whole column in each of our databases via R than it would be to do a simple query from them all?

One final note: these are currently on a network drive which is probably adding to the latency, but solutions like a local NAS in our office are dubious on if they'd work as a solution or even be in the budget with storage media prices right now.

Interesting article

Hi Gang, we would like our project to be able to read/query DB files downloaded from our CDN. We are currently using SQLite4Unity3d on Android and iOS and it seems to be working fine.

For legacy reasons we're using gilzoide/unity-sqlite-net for WebGL and it has a limitation where the only way to open a standalone db file is via a memory stream, but the issue is that the whole file has to stay resident in memory for as long as the connection is open. We will need to be able to make unpredictable reads to the DB file during our whole game, and our files are projected to be 10-20mb large, so that's out of the question.

Is there a single package that fulfills all of these requirements?

1. open downloaded/standalone db files for read-only operations
2. complete file contents do not need to be loaded in memory at all times
3. works for webgl/android/ios in a single code path

My platform expects multiple writes from various users belonging to one organization.

What's the best way to serialize the writes? Scale is 50 to 100 users wanting to write around the same time.