41–50 of 116 posts
Not sure if SQLite is still a 'lite' database
Earlier quoted context omitted.
Well if you don't mind rewriting tables, just create a table in the new structure, insert data from the old table to the new one, drop the old table, and rename the new table.
Any idea on how to do this without blocking writes when SQLite is embedded in a server process?
https://www.windowfunctions.com is a good introduction to window functions. Besides that, the comprehensive testing and evaluation of SQLite never ceases to amaze me. I'm usually hesitant to call software development "engineering", but SQLite is definitely well-engineered.
A lot of material I've seen has been like the classic image of "How to draw an owl. First draw two circles, then draw the rest of the owl", where they tell you the super basic stuff, then assume you know everything.
I don't even know what that means but again, because it is SQLite, I up vote automatically ^_^
https://www.windowfunctions.com is a good introduction to window functions. Besides that, the comprehensive testing and evaluation of SQLite never ceases to amaze me. I'm usually hesitant to call software development "engineering", but SQLite is definitely well-engineered.
This looks great, but I couldn't get through the first question on aggregate functions. Are there any SQL books/tutorials that go over things like this? A lot of material I've seen has been like the classic image of "How to draw an owl. First draw two circles, then draw the rest of the owl", where they tell you the super basic stuff, then assume you know everything.
https://www.red-gate.com/simple-talk/sql/t-sql-programming/w...
Earlier quoted context omitted.
Any idea on how to do this without blocking writes when SQLite is embedded in a server process?
Create new empty table, set up trigger on old table to copy across any inserted/updated rows, backfill the remaining rows incrementally, and finally use ALTER TABLE to atomically replace the old table with the new one.
When you wrote "backfill the remaining rows incrementally", did you mean having some background process or thread that reads a batch of rows from the old table (for example a few thousands of row), then inserts them in the new table, then commits, and keep doing this until all rows are copied? This way, other writers will be blocked only for the duration of each incremental transaction and will have an opportunity to lock the database for themselves between two batches?
Do you have experience doing this on production servers?
This is super cool. Does anyone know how to upgrade python3's sqlite module to the latest version?
A bit offtopic but has anyone tried to replicate sqlite databases? Using rqlite https://github.com/rqlite/rqlite or something else?
I am interested in this topic as well, although sqlite explicitly says that it is not meant for client-server configuration. I still want to see if it is feasible and someone is using it in production.
Unsurprisingly they were wrong. It was small, but sqlite couldn't handle multiple users. I believe this was before sqlite had WAL support, so reading would lock the DB. The 'solution' was to split the sqlite database into many smaller DBs that would allow users to use the site at the same time as long as they were in different areas. This greatly added to the complexity of the application. Some reports would need to access multiple databases to get what it needed, so it would still lock out people. Complexity was much higher then having all the data in a single postgresql/mysql database. All the users hated the system and often ran into DB lock issues.
> sqlite explicitly says that it is not meant for client-server configuration
They are right and their advice should be heeded.
https://www.windowfunctions.com is a good introduction to window functions. Besides that, the comprehensive testing and evaluation of SQLite never ceases to amaze me. I'm usually hesitant to call software development "engineering", but SQLite is definitely well-engineered.
This looks great, but I couldn't get through the first question on aggregate functions. Are there any SQL books/tutorials that go over things like this? A lot of material I've seen has been like the classic image of "How to draw an owl. First draw two circles, then draw the rest of the owl", where they tell you the super basic stuff, then assume you know everything.
Having an understanding of relational algebra (not the symbols, but the concept; math is always about the concept) generally helps a lot as well; it's the reason why compsci database lectures often start with relational algebra.
> We would like to find the total weight of cats grouped by age. But only return those groups with a total weight larger than 12.
The total weight of cats grouped by age.
SELECT sum(weight), age
FROM cats
GROUP BY age
SELECT sum(weight), age
FROM cats
GROUP BY age
HAVING sum(weight) > 12
SELECT sum(weight), age
FROM cats
GROUP BY age
HAVING sum(weight) > 12
ORDER BY age
SELECT sum(weight) AS total_weight, age
FROM cats
GROUP BY age
HAVING sum(weight) > 12
ORDER BY ageSQLite guys, please add FDW support ala Postgres and easy foreign function support for Python and R, and you’ll corner most of analytics and data science.
SQLite competes with fopen; not SQL. [1] It's great for embedded systems and small single-user apps. It's not when your data doesn't fit in memory. [1]: https://www.sqlite.org/whentouse.html
Now, where SQLite starts to fall down in analytics workloads is that it’s row-oriented rather than column oriented. Performance could be better. Still, even for analytic workloads SQLite can be good enough for medium-sized data!