Jump to content
futuresplash

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

Recommended Posts

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 ?

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites

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

  • Like 2

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites

I would like PW to run with MongoDB. Just kidding ;)

  • Like 4

Share this post


Link to post
Share on other sites

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?

  • Like 1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

  • Like 2

Share this post


Link to post
Share on other sites

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

  • Like 3

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites

+1

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

Any info *IF* this will ever happen?

thx

andy

  • Like 1

Share this post


Link to post
Share on other sites

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? :P

  • Like 2

Share this post


Link to post
Share on other sites

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)

  • Like 1

Share this post


Link to post
Share on other sites

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

  • Like 2

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

  • Like 5

Share this post


Link to post
Share on other sites

Would there be much demand for SQLite support?

Oh yes! :)

This would be downright amazing. Because convenience. 

  • Like 1

Share this post


Link to post
Share on other sites

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?

  • Like 2

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Juergen
      Hello @ all,
      I am creating a new inputfield/fieldtype to store opening hours, but I am struggeling to save values from multiple dynamic created inputfields in 1 column of the database.
      Scenario:
      The user can enter one or more opening times per day in a UI.
      Fe:
      Monday open from 08:00 to 12:00 and from 14:00 to 17:00 Tuesday open from 08:00 to 12:00 and from 14:00 to 19:00 and so on
      Via a little JavaScript you can add as much opening times as you need per day - the additional inputfield will be created dynamically.
      After form submission all the values are in the POST array -> this works (see example below):
      ProcessWire\WireInputData Object ( [openinghours_mo-0-start] => 09:00 [openinghours_mo-0-finish] => 13:00 [openinghours_mo-1-start] => 14:00 [openinghours_mo-1-finish] => 18:00 [openinghours_mo-2-start] => 21:00 [openinghours_mo-2-finish] => 23:00 [openinghours_tu-0-start] => 09:00 [openinghours_tu-0-finish] => 13:00 [openinghours_tu-1-start] => 14:00 [openinghours_tu-1-finish] => 18:00 [openinghours_we-0-start] => 09:00 [openinghours_we-0-finish] => 13:00 [openinghours_we-1-start] => 14:00 [openinghours_we-1-finish] => 18:00 [openinghours_th-0-start] => 09:00 [openinghours_th-0-finish] => 13:00 [openinghours_th-1-start] => 14:00 [openinghours_th-1-finish] => 18:00 [openinghours_fr-0-start] => 09:00 [openinghours_fr-0-finish] => 13:00 [openinghours_fr-1-start] => 14:00 [openinghours_fr-1-finish] => 18:00 [openinghours_sa-0-start] => [openinghours_sa-0-finish] => [openinghours_so-0-start] => [openinghours_so-0-finish] => ) The property name is always the name attribute of the field 😉 . If the property is empty means closed on that day.
      Now I need to combine all those values into 1 array (or json array) and store it in the database in 1 column called 'hours' in my case (see screenshot below):

      In my ___processInput(WireInputData $input) method I have tried to make it work like this:
      public function ___processInput(WireInputData $input): self { $name = $this->attr('name'); $value = $this->attr('value'); //input object includes always every input on the page, so lets filter out only inputs from this field //we need to do this, because the number of values is variable - so extract only values that starts with $name.'_' $nameAttributes = []; foreach($input as $key=>$value){ if(substr($key, 0, strlen($name.'_')) === $name.'_'){ $nameAttributes[$key] = $value; } } // loop through all inputfields of this fieldtype $time_values = []; foreach($nameAttributes as $nameAttr => $value) { $time_values[$nameAttr] = $value; } } //save it in the database $input->set('hours', serialize($time_values)); return $this; } The only important part of this code is the last part with the serialize function.
      After saving it will create a record in the database, but the value is always NULL (default value) (see below).

      Checking $time_values returns all the values, but printing out "$this" shows me that the property "hours" inside the Openinghours object is empty (see below) - so the mistake must be there, but I dont know where?!?!?!?
      [title] => Home [openinghours] => ProcessWire\OpeningHours Object ( [data] => Array ( [hours] => ) ) If I check the sleepValue() method or the sanitizeValue() - they are also empty. So it seems that the values will not reach these methods. I havent found a clear documentation of whats going on behind the saving process of an inputfield.
      As far as I know the saving process starts with the form submission. The values are in the POST array and will be processed by the processInput() method. Before they will be saved in the database they will be sanitized by the sanitizeValue() mehtod and afterwards they will be prepared for storage in the sleepValue() method.  The last step is the storage itself.
      Has someone an idea what is missing by storing values from multiple fields into 1 database column or has someone a working example of such a scenario on github to help me out.
      A clear explanation of the storage process will be also helpful.
      Thanks and best regards
    • By Pip
      Hi Everyone 
      I've been working on Processwire for two months now. Structuring the website as needed. Unsure why but I'm getting this one now. Seems my fields has crashed. 
      I've tried googling some answers but can't seem to find a step by step guide on how to rectify this. Any advise? Practically new on this. 
      TIA. 

    • By Rodd
      Hi everyone!
      I have a website in a production environment and I want to duplicate it in a local environment. I exported the content of the website (with the 'Site Profile Exporter' module) but I cannot use it actually. I've got an issue with the database. I imported this one in MAMP then.

      I also exported the pages (with the 'ProcessPagesExportImport' module), but I cannot import it to my local website because the fields don't exist. So I created this fields, but I have this error :
      How can I use the elements that already exist and are presents in my database? How can I duplicate correctly the templates, fields and pages?
      Thanks by advance
      PS: Sorry if my english is bad
       
    • By DooM
      Hello guys,
      I'm trying to figure out how to sync fields and templates between staging and production environments.
      I've found Migrations module by Lostkobrakai, but with use of it all the fields and templates must be created by API, which is kind of uncomfortable.
      I also tried ProcessDatabaseBackups module which can export only certain tables, but I don't think it's the best practice to do that.
      How do you guys solve this problem? It's very annoying to setup everything three times (dev, staging, production).
      Thanks a lot :)
    • By iipa
      Hi everybody!
      I have been reading about Multisite, but it kinda bugs me that every topic talks about having both admin and database same for multiple sites.
      I have a project where customer tests it by adding content to the site, while I still need to do some changes here and there in code, maybe some in database. If something crashes for a while, customer can't keep testing, which is a bit problematic.
      Is there any way that I could have two separate versions of one site ("production" and development) that share the same database, but are otherwise independent? Just the thought of having to migrate database every time I want to show client something new gives me anxiety 😁
×
×
  • Create New...