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.

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 1

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 3

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?

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.

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 1

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)

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 1

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. 

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 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • 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 😁
    • By Falk
      Hi!
      After temporarily using Module Image Extra, which I completely removed, I had some troubles with my imagefield (unused table columns).
      So I just imported a previous version of this column via PHPmyAdmin, which worked pretty well.
      Anyways, in Processwire Backend all image tags are gone, although they are OK in the database.
      Other image related things work (thumbnail, title etc are OK).
      Is there any way to recreate all the images or something? Or may this be an cache-related issue?
       
      Thanks in advance 😃
       
    • By anttila
      We have many booking calendars made with ProcessWire (own databases) and I want to do a web app (SQL) which allows user to log in. First, the user chooses the right calendar and then (s)he have to log in. The user can be from any of those calendars and the app is not running on ProcessWire (it can if necessary). So if there any way to make sure that the user has rights to the calendar (s)he tries to log in and if the password is correct.
      Is there any better way to do this? I could also use PIN codes or something, but those need to be encrypted too.
      Multiple ProcessWires A lot of users per ProcessWire Everyone can log in to the web app (when using right calendar)
    • By nuel
      Hi there
      Basically I want to call code within a ProcessWire page that isn't used as a template. Example: www.mypwpage.com/myphpfile.php
      I have a working PW Website with a couple of pages like /artists, /releases, /videos etc. Now I need a page /download without any editable fields in the backend, just calling some PHP code (that was coded by another guy) containing a form that checks unique download-codes in a second database and starts the download of the desired file. The script is working fine right now as part of a static website, but since I built PW behind the site, this independent «Download Section» of the page doesn't work anymore.
      Right now I have the main file download.php as a page template on a newly created empty page called /download, so until now the form is working (wow). After sending the form containing the download-code, the file check_code.php in a subfolder /site/templates/download is called and that's where I get an error.
      Any help?
×
×
  • Create New...