Supporting SQLite

Development-related discussion, including bundled plugins
Jking
Bear Rating Trainee
Bear Rating Trainee
Posts: 5
Joined: 05 Sep 2016, 17:47

Supporting SQLite

Postby Jking » 05 Sep 2016, 18:34

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.

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Supporting SQLite

Postby fox » 05 Sep 2016, 18:40

>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
Bear Rating Trainee
Bear Rating Trainee
Posts: 5
Joined: 05 Sep 2016, 17:47

Re: Supporting SQLite

Postby Jking » 05 Sep 2016, 19:41

[quote="fox"]>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.

[quote="fox"]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.