Supporting SQLite

[Jking]

I'm interested in using Tiny Tiny RSS on my personal server, but as I don't run a database server on the machine, I've set about adding support for SQLite. I know there was a thread about this in 2013 where the notion was dismissed, but a quick investigation of the code suggests Tiny Tiny RSS does not use features unsupported by SQLite: foreign key constraints are supported (since 3.6.19, bundled with PHP 5.3.1), as are nested selects, time intervals (via a function), UTF-8 text, etc. I'm happy to do the work myself, but I do have a few questions:

1. Full-text searching appears to be optional (not supported via MySQL); am I correct?
2. Would there be any objections to replacing use of the SQL 'NOW()' function with the standard 'CURRENT_TIMESTAMP' pseudo-constant? In Postgres it's been supported since 6.3 (far older than requirements), and in MySQL since 5.6.5 (March 2012). While I can have PHP define a NOW() function very easily, CURRENT_TIMESTAMP would seem to be more compatible not only with SQLite but Microsoft SQL Server as well, making future extension to other engines easier.
3. Would there be any interest in abstracting all queries to one class to make this kind of work a little easier (and affect less files) in the future?
4. Would a patch adding SQLite support even be accepted? Given that there would be some long-term support involved, the answer to this question is not obvious.

[fox]

>I've set about adding support for SQLite

good luck with that

>Would there be any interest in abstracting all queries to one class

good luck with that too

>Would a patch adding SQLite support even be accepted?

i assume this is a theoretical question because lol, well anyway the answer is no. same goes for mssql.

also, frankly the decision process here "i don't want to install postgresql so i'm going to spend a ridiculous amount of time adapting this application to a completely unfit database engine because ???" is absolutely unfathomable to me

e: even if as you say it supports foreign keys now and stuff i'm reasonably certain that performance-wise it would be a really dumb idea; anyway it's not like i'm stopping you, if you manage to generate a clear-enough diff i promise to take a look.

[Jking]

>also, frankly the decision process here "i don't want to install postgresql so i'm going to spend a ridiculous amount of time adapting this application to a completely unfit database engine because ???" is absolutely unfathomable to me

You cannot fathom it only because that's not the decision process that actually occurred. It's much closer to "I do not have a database server installed; I know that SQLite is perfectly capable of handling very complex use cases; I am almost certainly not the first person who has wanted to use Tiny Tiny RSS and for whom SQLite is more than good enough performance-wise; rather than just installing PostgreSQL or MySQL and 'fixing' the problem just for myself, I might as well, as a programmer who is capable of getting it done, fix it once and for all it for anyone who might also be affected." That was the decision process. As a programmer who has released open source software and appears to take feature requests from the wide world, I would have thought you'd understand this intuitively.

e: even if as you say it supports foreign keys now and stuff i'm reasonably certain that performance-wise it would be a really dumb idea; anyway it's not like i'm stopping you, if you manage to generate a clear-enough diff i promise to take a look.

For a single-user set-up, I doubt performance would be an issue; I wouldn't be surprised if SQLite were actually faster. I don't suppose you have any tests I can profile with? I didn't see any in the repository.

In any case, if you were sincere about taking a look at a diff, then I will get programming. I would, however, appreciate an answer to my first question about full-text searching.

[JustAMacUser]

For a single-user set-up, I doubt performance would be an issue; I wouldn't be surprised if SQLite were actually faster.

I'm not so sure; from the source (emphasis mine):

We are aware of no other embedded SQL database engine that supports as much concurrency as SQLite. SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update.

I remember playing around with Selfoss long after I started using TT-RSS. Back then it used MyISAM engine and oh my goodness was it bad. The front end just kept locking up while the feeds were updating. With the daemon running, TT-RSS runs every two minutes for any feeds needing update. If you have any number of feeds being fetched frequently, you're going to run into issues. Even now with MySQL and InnoDB I find that TT-RSS doesn't run as well as it did when I had it on PostgreSQL.

Obviously you can just work on the code to support SQLite without anyone's blessing, and if it works I have no doubt fox will incorporate it, but the use cases seem marginal for the amount of effort.

[fox]

one would never think there are rabid sqlite fanboys. then again i guess there's no accounting for taste. ¯\_(ツ)_/¯

You cannot fathom it only because that's not the decision process that actually occurred. It's much closer to "I do not have a database server installed; I know that SQLite is perfectly capable of handling very complex use cases; I am almost certainly not the first person who has wanted to use Tiny Tiny RSS and for whom SQLite is more than good enough performance-wise; rather than just installing PostgreSQL or MySQL and 'fixing' the problem just for myself, I might as well, as a programmer who is capable of getting it done, fix it once and for all it for anyone who might also be affected." That was the decision process. As a programmer who has released open source software and appears to take feature requests from the wide world, I would have thought you'd understand this intuitively.

gonna quote these brave words for posterity.

For a single-user set-up, I doubt performance would be an issue; I wouldn't be surprised if SQLite were actually faster. I don't suppose you have any tests I can profile with? I didn't see any in the repository.

that's called having common sense. sqlite is a toy.

Obviously you can just work on the code to support SQLite without anyone's blessing, and if it works I have no doubt fox will incorporate it, but the use cases seem marginal for the amount of effort.

seriously though, there's fat chance of that ever happening.

[Jking]

I'm not so sure; from the source (emphasis mine):

We are aware of no other embedded SQL database engine that supports as much concurrency as SQLite. SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update.

That FAQ is rather old, and hasn't been updated to mention Write-Ahead Logging, which significantly improves SQLite's concurrency characteristics. WAL is probably the way to go with TTRSS since some level of concurrency is likely to happen even in trivial cases; I'll just have to test to see which works best.

Obviously you can just work on the code to support SQLite without anyone's blessing, and if it works I have no doubt fox will incorporate it, but the use cases seem marginal for the amount of effort.

One might say the use case for TTRSS itself seems marginal. I am not deterred.

[fox]

>One might say the use case for TTRSS itself seems marginal. I am not deterred.

shots fired

[JustAMacUser]

That FAQ is rather old, and hasn't been updated to mention Write-Ahead Logging, which significantly improves SQLite's concurrency characteristics.

Perhaps. But performance is still not going to be better. Nor is it easier than this:

Code: Select all

apt-get install postgresql php-pgsql

At the end of the day, SQLite is simply not well-suited for this application. There's a reason large applications don't use it; you don't see WordPress, Drupal, etc. boasting about SQLite compatibility.

Like I said though, you seem to be set on implementing this, so go for it. I'd be genuinely interested to see the performance results when you're all done.

e: PostgreSQL is also pretty conservative, resource-wise, out of the box. So, I mean, you could run the whole setup on a pretty lean system.

[sleeper_service]

Obviously you can just work on the code to support SQLite without anyone's blessing, and if it works I have no doubt fox will incorporate it, but the use cases seem marginal for the amount of effort.

seriously though, there's fat chance of that ever happening.

because, then fox would have to support something he thinks is stupid...

and that ain't gonna happen, nor would I like him wasting his time on it instead of fixing other bugs or coding new features.

[sleeper_service]

That FAQ is rather old, and hasn't been updated to mention Write-Ahead Logging, which significantly improves SQLite's concurrency characteristics. WAL is probably the way to go with TTRSS since some level of concurrency is likely to happen even in trivial cases; I'll just have to test to see which works best.

what kinda gimpy server are you running that won't run postgres?

minutes installing postgres, vs who knows how many hours trying to do a bolt on after market hodgepodge attachment to something clearly inferior.

tough call.

I am not deterred.

how 'bout demented? can I get a vote for demented?

[fox]

>what kinda gimpy server are you running that won't run postgres?

i think we're dealing with matters of principle here

[Jking]

>what kinda gimpy server are you running that won't run postgres?

i think we're dealing with matters of principle here

To some extent, yes. In the end I'll have to install and configure PostgreSQL or MySQL to verify everything in working as it should, I'm sure. I'm neither demented nor in possession of a gimpy server (though it's not the greatest, stupid budget...); I am simply interested in Tiny Tiny RSS, and SQLite has served me for everything I have, from Postfix to Dovecot to Roundcube to SabreDAV to PAM to Prosody to Spectrum (some of these are even write-heavy). It seems a shame for TTRSS to be the odd one out.

We'll see about performance once I have something working. I'm willing to admit it may not be the best option, but if it's a suitable option, I won't consider it a waste of effort.

[Jking]

minutes installing postgres, vs who knows how many hours trying to do a bolt on after market hodgepodge attachment to something clearly inferior.

For reference, I don't think it'll be that bad. I already have a working schema (PG's worked with only minor changes), and the adapter is proving a quick thing. From what I can tell there's roughly 200 queries, but most of them will require zero changes, and in some cases SQLite can just piggy-back on the existing PG or MySQL branches. My investigations so far suggest the main difficulty (for me) will be making sense of the more complex query parts involving time intervals. As SQLite has no interval types or infix operators, those will all need a separate if-branch.

One step at a time. Programming can be a source of fun, too, you know.