SQLite has FTT. Could it be a viable alternative database backend for PW?

[futuresplash]

Hi,

I read on another thread that fulltext searching is crucial for PW. That requirement prevents other databases (such as PostgreSQL and SQLite) to be used as alternative backends for PW.

Well, SQLite does support fulltext searching and indexing!

Please see:

http://sqlite.org/fts3.html

Could SQLite be, perhaps, a good candidate for an alternative SQL backend for PW ?

[ryan]

If it supports fulltext searching, it might be technically possible in that regard. Though the implementation definitely looks different than MySQL. But I don't know much about SQLite beyond that, as I've never come across a hosting environment where SQLite was an option and MySQL wasn't. Would there be much demand for SQLite support?

[jbroussia]

I'm using SQLite for my own programming, it's a very nice little tool. Wouldn't hurt to have it as an option, in fact I've been wondering for long why we have to use such a monster as MySQL when 95% and more of the time, something like SQLite would be enough. But that's an extra-work and PW probably a lots of more important stuff to do before thinking to convert to SQLite... What would be nice though is that you keep PW "as compatible as possible" so that one day - when the list of priority improvements my be empty - it will be "easily" possible to adapt.

[futuresplash]

SQLite has some advantages over MySQL, such as:

1. Lower memory footprint

2. No need to have a dedicated server process running.

3. Easier deployment: no need to install additional software besides PHP and Apache.

4. Great for local development (easier setup).

5. Easier to migrate content from the local machine / staging server to the production server and vice-versa (you only have to FTP one file, no need for data migration tools and SSH tunnels).

6. Easier to backup/restore.

7. Easier to configure and maintain (less knobs and dials to finetune).

8. More flexible schema (columns are not statically bound to a specific type) and simpler data types.

9. Has good read concurrency (enough for small/medium websites).

And SQLite does support triggers, foreign keys, multi-column and multi-datatype (per column) indexes, constraints, cascade updates/deletions, transactions, and other features (much more than people usually assume).

It also has decent performance, if the database is correctly indexed and if the SQL queries follow some optimization rules.

Weak points:

1. It's not good for write-heavy applications, as tables are locked when writing.

(note 1: most websites are usually read-heavy, and write to the database much less frequently).

(note 2: there is a new operational mode for SQLite that allows for simultaneous read and write but I'm not sure if it can be enabled in PHP).

2. Join performance is not as good as with other, more powerful, databases.

As for the level of demand for SQLite support, let's see what the community has to say ;-)

I just wanted to bring this possibility (of SQLite as an alternative) to your awareness, so that if the community starts asking for SQLite, you will be able to reply "Perhaps" instead of "Not possible".

Regards

[jbroussia]

Another big drawback is the very limited ALTER TABLE command

[ryan]

Thanks for following up on this, it sounds like there are some good things about SQLite. This is a great summary you posted Futuresplash. I've never looked into it, so glad to learn more here. While there's not real strong incentive to support other databases right now, it is part of what the underlying framework is designed for and may be a possibility in the future. I will keep looking into this.

Thanks,

Ryan

[porl]

Is this what is meant by full text searching? Is there something about PostgreSQL's search that is lacking for ProcessWire?

I am just curious, not trying to push the issue or anything.

[netcarver]

Hi porl,

yes, fulltext indexes are available in multiple other DB systems (SQLite, Postgres and InnoDB on MySQL to name a few) and there has been a discussion of this with Ryan. Although I don't see it on the roadmap at the moment, I think that Ryan has commented positively about the possibility of making PW flexible enough to use different DB backends although this might not be as easy as it sounds due to the fact that not all SQL dialects are the same.

Just to let people know that I have personally managed to get PW running using the InnoDB engine on MySQL v5.6 with very minimal changes to the PW code. Once MySQL 5.6 goes mainstream in linux distros we will, at least, have the choice of a transactional DB engine for PW.

[diogo]

I would like PW to run with MongoDB. Just kidding

[jukooz]

I'm new to PW and just creating my first website using it, but was courious if I could use SQLite. The reason is deploying websites would be much easier and faster (no database setup required). For small websites it would be perfect, just copy & paste and you are ready. It would then compete with ie GetSimple CMS with simplicity of deployment (that I found about from this forum) but would be much more powerful. And great for having very fast to deploy templates. I believe a lot of people that are creating html templates, having no PHP/MySQL skills, could use it, as it would be so easy and fast to do.

Some slogans from other CMSs that could apply to PW then:

"add content management to any site in five minutes"

"just copy to server and start using"

I see that WolfCMS had support for SQLite as a plugin and now it has been bundled with the core.

I am not knowledgable about PDO, just read little bit about it, but maybe it would be good idea if it was possible? It would support many databases and be more future proof?

[SiNNuT]

I'm new to PW and just creating my first website using it, but was courious if I could use SQLite. The reason is deploying websites would be much easier and faster (no database setup required). For small websites it would be perfect, just copy & paste and you are ready. It would then compete with ie GetSimple CMS with simplicity of deployment (that I found about from this forum) but would be much more powerful. And great for having very fast to deploy templates. I believe a lot of people that are creating html templates, having no PHP/MySQL skills, could use it, as it would be so easy and fast to do.

Some slogans from other CMSs that could apply to PW then:

"add content management to any site in five minutes"

"just copy to server and start using"

I see that WolfCMS had support for SQLite as a plugin and now it has been bundled with the core.

I am not knowledgable about PDO, just read little bit about it, but maybe it would be good idea if it was possible? It would support many databases and be more future proof?

PW's database layer currently uses MySQLi. I'm no expert on the subject but i guess it would be easier to support multiple databases if this was PDO.

[netcarver]

Hi jukooz,

please see this post for more information.

In theory: great idea.

In practice: could be more difficult than expected, not necessarily due to PDO (which does allow good connectivity between backend DBs) but perhaps due to the little differences in the dialects of SQL that they all use which could be sources of incompatibility. This needs careful investigation as, AFAIK, ProcessWire's selectors can generate some pretty complex SQL.

[Pete]

That said, it's not difficult to set up a MySQL database.

[Sunny]

I think a sqlite compatibility would be fantastic for smaller sites. It is so common to be working on a client's shared host to find mysql running painfully slow. In contrast, sqlite will respond instantly. Drop http://getcockpit.com/ into a sluggish shared hosting environment and watch what happens.

[tiptronic]

+1

Using SQLite would be the killer for PW (at least for me). 

Any info *IF* this will ever happen?

thx

andy

[horst]

I think a sqlite compatibility would be fantastic for smaller sites. It is so common to be working on a client's shared host to find mysql running painfully slow. In contrast, sqlite will respond instantly. Drop http://getcockpit.com/ into a sluggish shared hosting environment and watch what happens.

This sounds a bit shizophren to me: clients want to have a powerful and great website but are not willing to pay 10$ per month for a good working shared host?

This is similar to people that buy a big windows machine for 2.000+ with viodeoadapter(s) that cost 500 per unit. Buy games that cost (I don't know) but want to save 29,95 for a commercial Antivirus-App.

---

And also with this sluggish shared hosts you will run into other serious problems like not enough memory for php (when it comes to image manipulation), very restrictive settings or using a not well configured apache with php as fastcgi (wath is in fact the opposite of fast), etc. etc.

It doesn't depend on the DB alone, so what will be next that would be great to build into PW to _help_ the clients save 5$ per month?

[Joss]

I have to say, I have not had a problem with MySQL/MariaDB on proper business shared hosting, which is very cheap these days. 

It is worth using caching where you can and I bung most sites through Cloud Flare (just the free version) and that  helps a bit. I can't see me using SQLite in a hurry.

Strangely, where I have had the odd MySQL slowdown, it has turned out to be me ... (Embarrassed cough)

[tiptronic]

I think there's no problem with shared hosting involved here.

But:

- A big advantage using SQLite is moving/replicating a site to a different server/domain (you name it).

- Also updating to new contents is a matter of replacing one file (the .db file itself, without the need of messing with a server).

- And last, not least, it's pretty convenient in a development environment, since you can run your dev-tools without cluttering your local backend with databases.

just my 0.02

[horst]

These three are valid points.

But it would depend on if the SQL-syntax of SQLite covers all of what is used in PW with MySQL. I'm not familiar with this and don't know it, neither what PW needs to use nor if this is supported by SQLite.

[teppo]

Just for the record, since it's closely related to this thread, there's a pull request in GitHub about SQLite support (opened back in May).

There are quite a few changes and most likely it isn't in sync with current dev branch (it's based on the master branch), but still works as a nice proof of concept for SQLite support. If any SQLite user here is able to get that one running and could run a few tests to see how well it behaves (the questions Ryan asked in his comment to the PR, like how well selectors work etc.) it would be interesting to hear.

[Yannick Albert]

Uh, yeah... I'm really interested in what we will end here

Btw: There's a good readup about its implementation limits https://www.sqlite.org/limits.html

[Osorio]

Would there be much demand for SQLite support?

Oh yes!

This would be downright amazing. Because convenience. 

[blynx]

Although I don't believe this is would be coming any time soon, I'd just like to leave a note here that I would really really really enjoy sqlite for processwire very much. It would be so much easier to handle and synchronise smaller sites just by version controlling the files of the project.

*sigh*

... could the FileCompiler offer a new approach on creating a universal compatibility especially for 3rd party modules?

[blynx]

!

[cramdesign]

I would also like sqlite for reasons of easier local development and portability.