Support new database SQLite adaptor #3914

I'm developing a new "plugin" or "how you want to be named" that allow to use SQLite instead of MySQL. Currently, works with all queries except the queries that use variables inside (@variable).

The SQLite support requires to add 2 files:

• core/Db/Adapter/Pdo/Sqlite.php
• core/Db/Schema/Sqlite.php

It's in alfa state, but I want to be usable in a short time.

If some one want to test it, please, email me to josep.sanz@saltos.net.

Josep.
Keywords: SQLite MySQL Database

Can you just post a link to a public repository here?

Here is the link to the public repository:

• https://github.com/josepsanzcamp/sqlite-for-piwik

It's in alfa version. I have to solve some problems yet!!!

Nice to know Piwik will run on SQLite, I wouldn't dream of it being possible actually ;-)

Good luck with the fork, let us know if there's anything we can change in core to make your life easier.

The current commit works fine now with the tracker. I had to modify the Tracker.php to add to the factory function the Pdo_Sqlite switch case for create the database object. It's a good idea to think how to do this automaticaly without modify the core piwik code. This is the first thing that would be nice to change in the piwik core.

thanks for suggestion. I would prefer to make all changes at once. Maybe you could send a patch at the end of your testing and we can improve + commit to Piwik git master? that would be awesome (and we can help making patch better, but would help to get started)

Dear Joseph, do you plan to maintain the fork in the future? if most features that we use would work wity SQLite, maybe we could add the support into Piwik core itself?

If it's as easy as adding one or two files in Piwik, maybe we could try them and enable our full tests suite to run using SQLite and check all features still work. Let us know if you're interested in that!

Hi Matthieu.

I paused the development of this plugin because I thought nobody used it. If you want to add the SQLite driver to the main code, I will continue with the development of this driver for SQLite and finish the pending tasks.

Where I can find information about how prepare a local test environment??? My objective is to execute the same unit tests that you are executing with MySQL database, but using the SQLite dababase.

Josep.

We've updated the FAQ about other databases recently: http://piwik.org/faq/how-to-install/faq_55/
it also mentions we'd like to support SQLite :)

you can install webserver + Php + Mysql,
Alternatively you could also prepare a local test environment using this project: https://github.com/piwik/piwik-dev-environment#piwik-development-environment

Then to setup unit tests and be able to run the tests see: https://github.com/piwik/piwik/blob/master/tests/README.md

Let me know if you have any question!

If you make some progress and find that SQLIte support is possible without too much work, we will definitely consider it for Piwik core :) cheers!

I would love to test the sqlite version, I also posted about this in forums last year and its good to see someone is working on this.

How is the SQlite support ? Does the module made by Anonymous-piwik-user work ? Can I tanke the files from https://github.com/josepsanzcamp/sqlite-for-piwik and use them without any issues ?

@Astaoth Likely not, there will be issues, since it's not official and not maintained since 2013.

Would be nice to actually get this. Lack of SQLite support is literally the only reason I haven't been running piwik on some websites for 2+ years now.

3+ years later this is still the primary reason why I'm not using Piwik/Matomo. The overhead of running an entire MySQL instance simply makes no sense for my use case.

My two cents from a seasoned developer. SQLite is a database that performs extremely well on high-speed reads, but not great at all on high-speed writes, especially writes from multiple PHP connections at the same time (which is essentially what would be happening on an active campaign). In fact, the bulk of Piwik/Matomo activities would be in writes, not reads. Reads are something it would do for checking uniqueness of a connection, and for admin activities (like getting stats in a report to see how a campaign is going). Most of the time, you want that Piwik/Matomo to be high-availability on writes or you'll start losing traffic data due to not being available. This is where MySQL, and especially PostgreSQL, performs very well. So, one may be thrilled to get it going with SQLite, but don't count on it to handle a high-volume campaign very well. You may be thrilled to see the admin interface be snappier, but at the expense of data loss for data that never had a chance to make it into the database because the SQLite database was overwhelmed with too many connections at the same time on a highly active campaign.

So, no, I don't recommend SQLite at all for this kind of project. You can learn the architecture of how SQLite saves data, versus how MySQL or PostgreSQL saves data, and see how it's functional but not fast at all. Turn it around on reads, however, and SQLite is blazingly fast against MySQL and PostgreSQL on simultaneous connections.

I’m well aware of the perf impact of using SQLite vs. MySQL. I’m not looking to use Piwik on Google.com. I’m looking to use it on some internal websites that get at best 100 hits a day just to get a sense of what the most popular pages are.