SQLite compatibility

[norv]

SMF 2.0 is only supports officially SQLite 2.8.
SQLite 3.x is backwards incompatible (or not guaranteed) with 2.x.
The standard PHP driver for SQLite 3.x is the PDO driver. Which SMF 2.0 database layer does not support.

So the question is: are we going to implement SQLite 3.x support for 2.1, or just fix 2.8?
My previous answer: support 3.x in any next version of SMF.

Notes:
2.x is extremely outdated. (this is an understatement)
3.x is almost certainly on the hosts supporting SQLite.
Once a database is migrated to 3.x, one cannot go back.
They both can be installed in parallel and work side by side on a server. SQLite has designed its 3.x so that it has different functions to create/open/work with a database, to enable that.
SMF uses the SQLite 2 functions, i.e. install.php first lines: sqlite_open()
http://www.php.net/manual/en/function.sqlite-open.php

(PHP 5 < 5.4.0, PECL sqlite >= 1.0.0)
This function only support database of SQLite 2 or below. For SQLite 3, you must use PDO.

Ref:
http://www.php.net/manual/en/sqlite.installation.php

[Spuds]

Hummm ... no comments ... I would have thought the 2 people using sqlite would have chimed in :D ....

Yes it should be moved to 3/PDO however I don't think its worth the effort to make the change for such a seldom used feature. Also given is scant usage, testing it will take forever ... really we are still finding postgre and sqlite errors from the early days of 2.0 which is all about test coverage and real life usage.

IMO in 3.0 I would drop support for all but MySQL, this was a nice experiment but the gain vs the effort or development and support is just not worth it. If thats not possible, maybe move on to a more robust abstraction layer, or maybe you have!

[IchBin]

The database abstraction at the time was a nice shiny new object. Everyone thought it was the coolest things since sliced bread. After seeing how little it's been used and how much trouble it's been to fix and support, I'd agree at this point to let it go the way of the wind. Would be better if we just left it abstract enough that someone else can plug into it if they so desire. Off with it's head!

[Fustrate]

Instead of dropping support for database engines, we should be supporting them through PDO.

We also need to get rid of this horrible abstraction layer and use something more standard. I don't care if it's our own or if we use a well-established code base, but the regex-based replacement stuff we have now is crazy, IMO.

[IchBin]

That sounds great for SMF3.0 :)

[Fustrate]

but let's not drop support for it in the meantime. This looks terrible:

• SMF < 2.0: No
• SMF 2.0: Yes
• SMF 2.1 - 3.0: No
• SMF 3.0+: Yes

[emanuele45]

Support SQLite 2.x is probably completely useless.

I have finally setup a VM with (hopefully, I still don't know if it works) SQLite 3.
I'll try to work on it, but don't know if I'll be able to do anything.

[norv]

nend has worked lately with these drivers on SMF 2.0, and may be interested in the issue.

/cc @sicommnend

[sicommnend]

IMHO PHP's implementation of SqLite 3 isn't all that great. Also on PDO, you can use a SqLite DB but be warned, you may be using either SqLite 2 or 3 depending on how the server is set up. So with PDO your not going to be guaranteed use of the most current version of SqLite on the server.

[sicommnend]

Here is what I use for SqLite 3 as I can't use PDO because on my shared host it uses SqLite 2.
http://php.net/manual/en/book.sqlite3.php

[norv]

What host is it, and what version(s) of PHP does it have?

This particular Sqlite3 extension is PHP 5.3.0+ only, SMF 2.1 minimum required PHP version is 5.1.0.

[sicommnend]

PHP 5.3.6 and the host is GoDaddy.

PDO drivers mysql, sqlite, sqlite2
PDO Driver for MySQL 5.0.77
PDO Driver for SQLite 3.x 3.7.4

Strange though shows SqLite 3, might have to check it out once again, I swear it used to use SqLite 2.

[Oldiesmann]

Before we drop support for Postgres and SQLite 2.x we need to find out how many people are actually using them. If they're being used, we shouldn't drop support for them or it will irk those who are using them.

[Fustrate]

As I said before, it looks really bad to not support something, then support it, then drop support again.

[emanuele45]

I don't think we need to remove anything of what we have at the moment. We just need a bit of testing. For sure on postgre because I'm pretty sure it's buggy, at least the upgrade, probably the installer too...or it wat SQLite that was buggy?...or both? Don't remember any more.

To those that know more about SQLite (I'm almost completely ignorant about it): would be possible (and if so how difficult would be) to install SMF with the current SQLite 2 implementation and then switch to SQLite 3 for example with a mod?

[sicommnend]

SQLite 2 is compatible with SQLite 3 in that order. However a SQLite 3 database will not work with SQLite 2. So basically once you are upgraded there is no going back.

[sicommnend]

You know I haven't done a database upgrade though, I know there where major changes in version 3 mainly with the formatting of the file. I think in the middle though you will have to have a conversion script to upgrade or downgrade because the database file contains the version. I haven't tried yet though but I believe SQLite will emulate the version that is in the db file.

[sicommnend]

http://www.sqlite.org/version3.html

Here is a overview of changes. May be simpler to support both and give the option on install, because if this is done with a mod and the user uninstalls the mod then this may cause allot of headaches.

But basically the document is saying they are incompatible both ways. So in order to upgrade you will have to dump the database then reinsert the data in the version your upgrading or downgrading from.

[emanuele45]

So, even if we'd be able to support 3.x in SMF 2.1 we couldn't drop the support for 2.8 unless we create a converter before.

/me thinks SQLite 3.x is for SMF 3.0.

[emanuele45]

Small bump to point those interested to tinoest job:
http://www.simplemachines.org/community/index.php?topic=483878.0

[jdarwood007]

We can continue support for SQLite 2.8 and 3.0 both. We simply name a new database abstraction file that uses sqlite3 specifically. This is better as it allows 2.8 to be used and for 3.0 to be implanted and migrated over properly. Having us try to migrate it seems like a troublesome task.

[emanuele45]

Yes, the new layer is the only way because we support php...5.1? And so we have to support people without 3.x.

Though, we already have so many bugs (and limitations) in the the current Postgre and SQLite layers that I would think 3 times before adding yet another one (even though it should basically be a clone of one of the other three) just because we have the code...

I realize that this is yet another problem with php 5.4, but as it is now SMF would rather difficult to use anyway with php 5.4 (at least the error log would be filled up rather quickly)...

[Arantor]

Well, I see SQLite3 files in the repo, I have no idea what state they are in, much as I'm not sure what state PGSQL support is in. I would like to know if anyone does know what state this is in.

Regarding the comments above about 'supporting multiple backends', that's problematic because PDO doesn't rewrite queries for you, you still have to tweak different queries for different environments like we currently have in place. Some of the changes required for proper abstraction aren't going to happen any time soon e.g. MSSQL's lack of a native LIMIT x, y clause which we make heavy use of.

As far as future support goes, I have absolutely no qualms about dropping SQLite in SMF 3. On the one hand, it doesn't look great, true, but on the other hand I'd much rather do one or two things very well than trying to spread too thinly. Never mind the practical consequences of trying to use SQLite for anything other than a test forum. I'd drop PostgreSQL too given half a chance; right now we're still too busy trying to rewrite MySQL queries into other things and either we need to stop messing about and do it completely properly (and PDO won't magically fix that, neither with ADODB), or we need to concentrate on what we're good at.

[NanoSector]

Any update on this? Seems like there are SQLite3 files included with the package nowadays.

[Oldiesmann]

Yep. I added SQLite3 support back in August. See fab5186.

[Arantor]

Does it work? Has it been tested lately?

[Oldiesmann]

It did at the time the initial commits were made. I haven't tested it recently though.

[illori]

doing install with sqlite3

Fatal error: Call to a member function fetchArray() on a non-object in F:\wamp\www\github2.111111\Sources\Subs-Db-sqlite3.php on line 487

[illori]

some additional sqlite3 issues, on the board index it shows the number 1 after the name of the category.

Type of error: Database
Database Error: database is locked
database is locked
http://localhost:8080/github2.111111/index.php?action=viewsmfile;filename=current-version.js
File: F:/wamp/www/github2.111111/Sources/Session.php
Line: 169

Type of error: General
2: Cannot modify header information - headers already sent
http://localhost:8080/github2.111111/index.php?action=viewsmfile;filename=current-version.js
File: F:/wamp/www/github2.111111/Sources/Subs.php
Line: 3063

Type of error: General
2: Cannot modify header information - headers already sent
http://localhost:8080/github2.111111/index.php?action=viewsmfile;filename=current-version.js
File: F:/wamp/www/github2.111111/Sources/Subs.php
Line: 3064

Type of error: General
2: Cannot modify header information - headers already sent
http://localhost:8080/github2.111111/index.php?action=viewsmfile;filename=current-version.js
File: F:/wamp/www/github2.111111/Sources/Subs.php
Line: 3070

Type of error: General
2: Cannot modify header information - headers already sent
http://localhost:8080/github2.111111/index.php?action=viewsmfile;filename=current-version.js
File: F:/wamp/www/github2.111111/Sources/Subs.php
Line: 3074

[Oldiesmann]

The "database is locked" error is something we'll have to look into a bit more. Here's a reference page on it from the SQLite manual: https://sqlite.org/cvstrac/wiki?p=DatabaseIsLocked

The "headers already sent" errors are due to the first one being displayed (displaying an error message causes PHP to send headers, so when you try to send them again, it breaks)

[illori]

also the upgrade.php script does not include sqlite3 at all so it fails on a few levels

[illori]

Type of error: General
8: Object of class SQLite3Result could not be converted to int
http://localhost:8080/github2.111111/index.php?action=login2
File: F:/wamp/www/github2.111111/Sources/Subs-Auth.php
Line: 98

[Oldiesmann]

Looking at the code, I have no idea what's causing either of these errors. Does the "Database is locked" error occur all the time or is it just random?

I don't know what's causing the "could not be converted to int" error as there is no query anywhere near line 98 of Subs-Auth.php.

[illori]

the database is locked has happened after the install and after the upgrade [using sqlite sql]

[tinoest]

The database is locked is quite a common SQLite error, especially with multiple users and queries. There are various things you can do to get around it which I'll find the code for shortly. What version of sqlite3 is it?

[illori]

whatever version comes with php 5.5.12

[Arantor]

phpinfo() should tell you.

[illori]

3.8.4.3

[tinoest]

Adding $databaseResource->busyTimeout(1000); // Measured in ms so this is 1 Second.

Should remove the database is locked error's to a large extent.

http://www.php.net/manual/en/sqlite3.busytimeout.php

It's 5.3.3 or above though so not sure what the minimum requirement is for SMF. Although SQlite3 is 5.3 only anyway.

[Arantor]

SMF 2.1 is 5.3.8+

[Oldiesmann]

tinoest, where should that be added?

[tinoest]

I would add it just before you return the initial connection.

// Fix for database is locked error
$connection->busyTimeout(1000); // Measured in ms so this is 1 Second.
return $connection;

And also as part of the check_connection function.

    $connection = new SQLite3($db_name);
    $connection->busyTimeout(1000); // Measured in ms so this is 1 Second.

It has go inside the is_object / is_resource part. Otherwise it will throw errors' on some result objects that get passed into this function.

[Oldiesmann]

That's been merged in now. @illori can you try to get more info about that "cannot be converted to int" error? There are some queries in Subs-Auth.php but none anywhere near that line.

[illori]

how do you want me to get more info? that was also after i tried to use the sqlite sql to do the upgrade as currently there is no upgrade sql for sqlite3. maybe if there was this would be fixed?

[Oldiesmann]

Go to Admin -> Maintenance -> Logs -> Log Settings and make sure "Include database query in the error log" is checked. That should help track down this issue.

Looking at the code, it appears the query in question is probably the one from sessionDestroy() in Sources/Session.php, but that's a DELETE query and it shouldn't be returning any results (per the PHP manual, sqlite3::query() will return either an sqlite3result object if there are results or either true or false depending on whether the query was successful).

[illori]

no query is logged. this error occurs when logging back in. or possibly logging out.

the error is not a database error type

[Oldiesmann]

I'm wondering if for some reason it's returning the number of rows affected.

Try this and see if it works at all.

Sources/Subs-Db-Sqlite3.php

Find

    $ret = @$connection->query($db_string);

Replace

    if (!stristr($db_string, 'SELECT '))
        $ret = @$connection->exec($db_string);
    else
        $ret = @$connection->query($db_string);

According to the PHP manual, "exec" executes a result-less query and returns TRUE or FALSE depending on whether the query was successful.

[illori]

that leads to this when i log out [or maybe refresh the page, i went to log out as first action]

Fatal error: Call to a member function fetchArray() on a non-object in F:\wamp\www\github2.111111\Sources\Subs-Db-sqlite3.php on line 303

/**
 * 
 * @param resource $handle
 */
function smf_db_fetch_array($handle)
{
    return $handle->fetchArray();
}

the return is line 303

[Oldiesmann]

Yeah, that's my fault. I forgot that stirstr() returns 0 if it's at the beginning of the string.

Change that part inside the if() to stristr($db_string, 'SELECT ') === FALSE.

[illori]

same error on line 303 with that change

[Oldiesmann]

Latest commit should fix the issue with "object could not be converted to int" error.

[Oldiesmann]

Closing per #1916.