SQLite instead of MySQL

[dginev]

A recurring theme at NIST is keeping setups as small, robust and secure as possible.

This also includes databases, and indeed my main reason for switching NNexus over to SQLite was the reluctance of the sys admins here to deploy (and give permissions to) a separate MySQL server.

In order to make Planetary more friendly to such environments, it would be awesome to have Drupal interface with an SQLite file rather than a MySQL server. Could there already be such an option for Drupal?

MediaWiki supports SQLite together with MySQL and asks you of your preference at configuration time, which I found really convenient.

[dginev]

On a related note, could we make the Planetary installation as painless as the MediaWiki one? Somehow, MediaWiki deployment is as easy as downloading a directory and running:

php --server localhost:8000 --docroot mediawiki-1.20.5/

The rest of the configuration is carried out inside the Wiki itself. This setup was very convincing and trumps the elaborate Planetary installation process.

[holtzermann17]

I've set up a Mediawiki site before and I wish I'd known about that! However, it's not entirely fair to compare that to setting up Planetary, I'd say it's more akin to setting up a bare instance of Drupal.

Anyway, Drupal does have support for SQLite. I haven't tested it but it should work (almost) out of the box & be relatively independent from other Planetary features.

[dginev]

True, a bare MediaWiki installation should be compared to a bare Drupal installation, and Planetary should be compared to installing MediaWiki extensions.

[cdavid]

I would like to chip in here, regarding MySQL and SQLite. I've used both of them and I can relate to what is described here: http://stackoverflow.com/questions/3630/sqlite-vs-mysql - the most important being the lack of users (an unsanitized malicious query can turn up to be a DROP DATABASE drupal;) and the fact that it does not scale well, especially with Drupal which does ~50 queries / page.

[dginev]

Yep, I am completely in agreement with that post - use MySQL for production use, but have SQLite for development/hosting small sites.

[holtzermann17]

@dginev - the idea that we could get an entire dev site set up with zero configuration is indeed a nice/interesting goal. The stuff that @jucovschi has started with the planetary_libs module seems quite nice in this respect. You can use a drush make command to install a bunch of custom dependencies. However, so far that only works for PHP and JS libraries, not for all the other peripherals. And it doesn't currently work for all of the PHP/JS, see #371. However, it could, in theory, I think, be used in a one-liner to build an instance of the entire Planetary system (apart from required apt-get commands).

[dginev]

Well, if you take this goal seriously, I would support an extra effort invested in keeping things as close to PHP and JS as possible. For example, I remember the ARC2 PHP library was offering both an RDFa-parsing support and a built-in PHP triple store. With Virtuoso out of the way, things could become quite well-contained.

Having a minimal installation and deployment setup would be crucial for attracting new developers that are not familiar with the project. Being able to start hacking on MediaWiki extensions in 2 minutes or less is an amazing feeling.

[holtzermann17]

If we want to have full SQLite-based stack, I think Perl's RDF:Trine may be our best bet.

[dginev]

Using a Perl library from PHP will make things just as external as having a MySQL server though. And SQLite is quite far form a good triple store backend.

[holtzermann17]

Well, we definitely have some Perl dependencies - no real way to get around those... though of course not all developers will need to install them, and can typically use already-existing web service versions when that's needed.

The triple store might be less easy to web-service-ize.

But what I should have said above is that I think ARC2 only uses MySQL -- so if we want a zero-configuration out-of-the-box setup that relies only on SQLite, then we need something else.

[dginev]

If you're referring to LaTeXML, it gets integrated through HTTP communication, not through a pure Perl interface. If you want to use RDF::Trine you would need to implement a similar HTTP web service to have the same graceful integration, and having a separate web service just to read from a SQLite file is kind of awkward.

Then again, it might be a feasible way forward, I'm not sure.