Consider defaulting to SQLite instead of MySQL

[liilac]

At the moment, readme instructs users to install MySQL to get pomf to function (though mentions other databases may work).

MySQL, or any full SQL server, is completely unnecessary (though of course there's nothing wrong with using it) for something like pomf. It is an unnecessary dependency, and complicates installation. Many might lazily set it up, leaving it in a potentially insecure configuration.

SQLite is completely self-contained, and requires no configuration. The only change necessary to the code, would be changing the placeholder references to a MySQL server/database in settings.inc.php, to a suitable URI pointing to a location for the SQLite database file.

[liilac]

#17

[lesderid]

This is a pretty big change, I wouldn't just accept a pull request without some discussion first.

[liilac]

I'm aware. I have discussed it with @ewhal and their suggestion was to submit a PR.

This change would not affect existing users if they did not want to switch, though i have written instructions for if they did.

[liilac]

@lesderid in terms of code, it's exceptionally minor
see: https://github.com/pomf/pomf/pull/13/files

[lesderid]

I'm aware. Giving instructions to set up SQLite and providing a schema is fine, but I'm not sure if I'd make it the default.

[liilac]

Why? It is a much more sane default than MySQL IMO

much simpler to setup and maintain, less resource usage, not a network service, less dependencies, closer to evetual goal of no SQL DB

pomf hardly needs a full DB server

[lesderid]

I'm mostly worried about performance issues. Have you tried SQLite with a number of concurrent uploads? Is it as fast or faster than MySQL/MariaDB, even for large databases (such as pomf.se's or mixtape.moe's)? If so, go ahead and merge.

[liilac]

@lesderid not the best answer. but
https://www.sqlite.org/whentouse.html
"Checklist For Choosing The Right Database Engine
...
Otherwise → choose SQLite!

For device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always a better solution. SQLite is fast and reliable and it requires no configuration or maintenance. It keeps thing simple. SQLite "just works". "

[lesderid]

I'd prefer to see some real-world benchmarks for pomf first. Shouldn't be too hard to do.

[liilac]

Any suggestions for how to do so? SQLite performs excellently on not-enormous DBs. Even mixtape.moe is HARDLY that.

Also, see: http://blog.devart.com/increasing-sqlite-performance.html

[lesderid]

Populate a database with fake data (I'd say something like 100k files at least).
Generate some random files (as many as possible) and time how long it takes to upload them concurrently with something like curl.
Then just compare the results with a MySQL-based pomf.

[liilac]

Also may be worth looking at https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems

[liilac]

https://pbs.twimg.com/media/CkIq-CEXEAAhecf.jpg:large

Reading performance won't be the issue with SQLite. If we assume uncached requests are uploads/initial downloads, then these would be the only ones that involwe writing.

If we divide by 2 because half would be initial downloads 1720580 / 2 = 860290
860290 / (30 days * 86400 sec per day) = 1 request every ~3 sec

https://www.sqlite.org/faq.html#q19
SQLite say that on a 7200RPM desktop hard drive (hardly representative), SQLite can do 60 transactions per second. That is 180x what mixtape.moe was experiencing, on desktop HDDs.

Note that this can be improved significantly, still, as explained in a link above.

[jithatsonei]

Closing as said in latest comment in #19