News for hackers. Stuff that matters.
Re:SQLite Release 3.25.0 adds support for window functions by yread on Wednesday August 15, 2018 @06:16AM (#17764895)
A bit offtopic but has anyone tried to replicate sqlite databases? Using rqlite https://github.com/rqlite/rqlite or something else?
Re:SQLite Release 3.25.0 adds support for window functions by newusertoday on Wednesday August 15, 2018 @07:02AM (#17765135)
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.
Re:SQLite Release 3.25.0 adds support for window functions by zip1234 on Wednesday August 15, 2018 @10:51AM (#17766440)
What they mean by that comment is that it doesn't have users in the sense that mysql, SQL Server, etc have. https://www.sqlite.org/whentouse.html The documentations says that it works well for websites. For client server, you just need to make an API that handles the interactions with the database.
Re:SQLite Release 3.25.0 adds support for window functions by move-on-by on Wednesday August 15, 2018 @09:23AM (#17765814)
I got thrown into a legacy web project that used sqlite as the database. It was a small internal-only app, I guess the original developer(s) figured it was so small that sqlite would be plenty and it would reduce the environment complexity.

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.

Re:SQLite Release 3.25.0 adds support for window functions by zip1234 on Wednesday August 15, 2018 @10:39AM (#17766316)
Since 2010, SQLite has had Write-Ahead-Logging. Perhaps your project was using an older version of SQLite? https://www.sqlite.org/wal.html
Re:SQLite Release 3.25.0 adds support for window functions by misframer on Wednesday August 15, 2018 @10:14AM (#17766135)
Expensify uses SQLite for their core database.

https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...

Re:SQLite Release 3.25.0 adds support for window functions by yread on Wednesday August 15, 2018 @11:16AM (#17766658)
Interesting discussion of bedrock

https://news.ycombinator.com/item?id=12739771

Re:SQLite Release 3.25.0 adds support for window functions by siscia on Wednesday August 15, 2018 @12:04PM (#17767095)
Most applications don't need the level of concurrency that Pg or MySQL provide and they will be just fine with something simple to operate, which is why I wrote https://redisql.com/
Re:SQLite Release 3.25.0 adds support for window functions by siscia on Wednesday August 15, 2018 @11:59AM (#17767058)
Shameless plug since I am the author, but you could be interested in https://redisql.com/

Basically an Redis module that embed SQLite, I offer replication on the PRO version, if you want to try it out you can download the trial version for free.

If the trial version is not enough, send me an email and we could work something out ;)

Re:SQLite Release 3.25.0 adds support for window functions by yread on Wednesday August 15, 2018 @05:11PM (#17769818)
Just a heads up, you should probably have someone proof read your copy. There is a typo in every paragraph.
Re:SQLite Release 3.25.0 adds support for window functions by siscia on Wednesday August 15, 2018 @06:23PM (#17770266)
Thanks I will proceed soon!
Re:SQLite Release 3.25.0 adds support for window functions by mamcx on Wednesday August 15, 2018 @10:15AM (#17766146)
Almost all my main apps have a sync history. Before was using batch processing and a version field:

customer: name, ...., version = 1

update customer: name, ...., version = 2

and sync manually. Now I use a log/event sourcing like setup, where every write to the BD is also stored in a log table and sync from there. Much better!

Re:SQLite Release 3.25.0 adds support for window functions by blattimwind on Wednesday August 15, 2018 @10:29AM (#17766232)
Make your logged events invertible and get an undo/redo function for free. That's how I kill three to four birds with one stone.
Re:SQLite Release 3.25.0 adds support for window functions by mamcx on Wednesday August 15, 2018 @10:51AM (#17766437)
invertible? What is that?
Re:SQLite Release 3.25.0 adds support for window functions by blattimwind on Wednesday August 15, 2018 @10:53AM (#17766454)
Say you record an event like "name of customer X has been changed to FooCo". Instead, record "name of customer X has been changed to FooCo from Foo & Sons Co". If you want to undo it, just swap from/to (=invert it).
Re:SQLite Release 3.25.0 adds support for window functions by zbentley on Wednesday August 15, 2018 @11:58AM (#17767055)
A common pattern to implement this is an audit table: https://dba.stackexchange.com/questions/15186/what-is-an-aud...

...which is itself a special case of an audit trail or log: https://en.wikipedia.org/wiki/Audit_trail

Re:SQLite Release 3.25.0 adds support for window functions by blattimwind on Friday August 17, 2018 @05:58AM (#17781570)
In my experience the usefulness of audit tables diminishes as the table becomes more complex (more columns; composite and document columns, like arrays, json, xml), because it becomes difficult to find what exactly changed. Another problem is related rows; audit tables aren't very good to capture the context of changes. You can work around this with more complex triggers, but it quickly becomes a lot easier to do this from the application and not in the database, capturing semantics and not just data changes.

The issue with any application-based approach is of course that it can't provide total access mediation.

Re:SQLite Release 3.25.0 adds support for window functions by atombender on Wednesday August 15, 2018 @03:10PM (#17768822)
Bedrock [1] by Expensify is supposed to be solid. Master/slave async replication (i.e. all slaves are read only), pluggable, provides full SQLite access via the MySQL protocol.

[1] http://bedrockdb.com

Re:SQLite Release 3.25.0 adds support for window functions by onli on Wednesday August 15, 2018 @01:58PM (#17768205)
Related: Is there a ruby client for rqlite? I can't find one.