Anyone deciphered SQLite3 yet?

I have a CMS that is in dire need of upgrade to SQLite3

I have converted the SQLite2 database to SQLite3 using the software in the SQLite web site, no problem, however, this does not solve the problem ot updating some CMS that is still using and can only read SQLite2 databases because the software is only written with code that is now defunct thanks to PHP dropping SQLite2 support.

The other issue is the CMS developer is 404 and appears to have 302’d, where, I don’t know… Its not a simple task of installing a new CMS, too much data in the old DB that people still access and read.

So, has anyone come up with a fix / workaround for these functions to access SQLite3 from the unsupported SQLite2 functions?

Oh boy! sounds like a lot of work.

Especially if the author is no longer supporting the CMS

2 choices
migrate to a different CMS
modify the code

One might not be possible (as you have suggested*), the other a major PITA
*are you sure a different CMS would break the links?

I think what I would be most likely to do is “upgrade” the code myself. This could entail putting all database code in try/catch and running it on localhost (set up to replicate the live server) to find broken code.

How extensive is the CMS code?

Ok, I am wondering how this situation came about. Did the web host drop support for an older php version?

I have used sqlite3 quite a bit but from python, not php.


Where do I start…

PHP support is based on my choice in the cPanel settings as the host I use offers the option to use a version of PHP like an older version, however, at some point this will not be available as the older version will be replaced automatically, so its borrowed time.

The CMS still functions, however the fact that major CMS’s use MySQL and very few offer SQLite support makes migration of database a problem as does the fact that no two CMS databases are alike and structures are never the same.

Whilst the CMS is very simple in its operation, back end appears to be a quagmire of bolted on elements as and when modules were upgraded, code modified. The other problem is the user of this CMS likes the software, they understand it. I had prior to that CMS tried 20 or so that were either overly complicated to do a simple job or didn’t offer what the site operator wanted to do or just didn’t work.

Anyway, I did think of writing a connector to push data in to the new database so I could move to another CMS, problem was that the data may have been in the database but the host CMS ignored it.

It would appear that in the world of CMS, the term KISS is something that developers are oblivious to.

What would be nice is if someone knows of a set of PHP functions that mimic the defunct functions like some of the MySQL functions that help people with the swap over to MySQLi suite of functions.

I have looked on the PHP site and the information about these functions is available but one problem is that they do not adequately explain the use as this is a leap in to the realms of an OO style use of functions.

So any help, signposting to real examples, etc, would be appreciated.

My approach would be to download this:

This gives you a command line tool that will probably work even on a sqlite2 database. (I think I read somewhere that sqlite3 had pledged about maintaining backward compatibility.) Then you can have a look at the structure of the database and, as you were saying, create equivalent structure for mysql database. Then it remains to find the sqlite calls and change them over to mysql calls.

Have you tried getting the extension via PECL?

What I am looking for is anyone who has created work arounds that can just be an include file so that I don’t have to write code, so when sqlite_query() ceases to be supported in PHP (which it has in many upgrades) the include file will offer up a work around that sees sqlite_query() has ceased to be a supported function and steps in… in its simplest of terms, a bit like the XMLHttpRequest() work around in JavaScript.

I think Salathe’s suggestion will solve your problem without requiring that you write any code.

If I understand correctly, the sqlite extension still exists and still works, but it no longer comes with PHP by default. You (or your host) will have to download and install it from PECL.

My host allows you to use an earlier version of PHP or the latest version of PHP, I am using the older version of PHP that is the version before the one that drops SQLite support, so when the web host updates PHP to the next latest stable version, the current one I am using updates and what was the latest version then becomes the old version.

The CMS that is in use has its own function that you call that uses the standard SQLite2 functions, so when SQLite3 is the only version that works, I will be stuck because the SQLite2 functions remain in the CMS and they don’t work with SQLite3

SO I need a file that I can include that simply replaces the SQLite2 functions, like masking so that they will work but the functions that replace will call SQLite3 functions, eg.

// under SQLite2
$db = sqlite_open("mysqlitedb", 0666, $sqliteerror);
$results = sqlite_query($db,"SELECT * FROM membership WHERE member='moderator';--");

// under SQLite3
$db = new SQLite3('mysqlitedb.db');
$results = $db->query("SELECT * FROM membership WHERE member='moderator';--");

So I was needing a library that would replace the sqlite_open( blah ) eg

function sqlite_open($dbname){
return new SQLite3($dbname);
function sqlite_query($db,$query){
return $db->query($query);

and so on, for the whole SQLite2 support to continue, it would save allot of rewriting if anyone out in cyberville has already done this work as I don’t want to have to take the site offline while I go through a couple of hundred files of PHP line by line on the off chance that it has reference to a PHP function that has been declared defunct.

I google around a bit for you, but I couldn’t find what you’re looking for. You might have to bite the bullet and write some code. :confused:

Perhaps a bit off topic, but I strongly suggest doing the work in a local dev environment and under source control, then update the live site through a single automated task. That way you don’t need to take the site offline, and you don’t need to risk live updating.

Also, checking line by line through hundreds of files sounds tedious. You might have an easier time with a regular expression search. Something like: sqlite_\w+\(

I will have a go at the regexp method, would possibly be easier.

The other side of the problem is I am using Abyss web server which uses a version of PHP that they have for their web server and… you guessed it, it does not support SQLite2

The XAMPP that I have is the horrid nanny version that is more problem trying to sort out to work than the effort needed in getting it functioning, so I only use the SQL side of things when I have something that needs MySQL.

Well thanks for that, it was on the look of it productive, it seems that the CMS author added in SQLite3 on their last and final update before absconding.

Going to try a simple database switch and swap out the SQLite2 with the version thats been converted to SQLite3.