Forum

Hi, I'm trying to get the SQLite3 support working (I converted the DB) but I have run into some errors, first on sqlite3_connection.php I had to add exception: use \SQLite3 as SQLite3;
since the: namespace Banshee\Database;
causes Class 'Banshee\Database\SQLite3' not found error on the browser but then I got hit with this:

Internal errors x
SQLite3::query(): Unable to prepare statement: 1, no such function: UNIX_TIMESTAMP
line 56 in /var/www/banshee/libraries/database/sqlite3_connection.php
SQL query: select value, UNIX_TIMESTAMP(timeout) as timeout from cache where "key"='banshee_settings::last_updated' limit 1
Error message:
SQLite3::query(): Unable to prepare statement: 1, near "limit": syntax error
line 56 in /var/www/banshee/libraries/database/sqlite3_connection.php
SQL query: delete from cache where "key"='banshee_settings::last_updated' limit 1
Error message:
SQLite3::query(): Unable to prepare statement: 1, no such function: UNIX_TIMESTAMP
line 56 in /var/www/banshee/libraries/database/sqlite3_connection.php
SQL query: select value, UNIX_TIMESTAMP(timeout) as timeout from cache where "key"='banshee_settings::last_updated' limit 1

Is the SQLite3 still supported on version 6 or did I just mess something up?

Banshee can use SQLite3 databases, but all the SQL queries are written for MySQL. If you want to use SQLite as the main Banshee database, you must rewrite those queries.

You could say that SuiteCRM can use PostgreSQL database as long as you rewrite the whole code base related to MySQL queries

But thank you, I will use MySQL even if it is a bit overkill for the project.

As a by the by; still sweeping into some of the corners, but conversion of Banshee to work with SQLite3, took about 3 hours, as a PHP and SQL amateur. Well it took an hour, a sleep on it, and a few more hours. Bumped into an issue with UNIX_TIME and SQLite's strftime. strftime needs a %s for epoch seconds, and a security check in query strings doesn't like %s. ;-)

Current working draft of SQLite Banshee 7.2b, litebanshee, worked around the nested %s's with a few strategically placed preg_replace lines in database/database_connection.php make_query().

$format = preg_replace('/UNIX_TIMESTAMP\(([^)]*)\)/', 'strftime(#s#, $1)', $format);

And

$tmp = str_replace("#s#", "'%s'", $tmp);

After the vsprint which needs the %s too. :-)

Most of the other changes were

TRUNCATE TABLE to DELETE FROM
the setup CREATE TABLES needed reworking to avoid KEY ans used the SQLite PRIMARY KEY (f1, f2...) syntax instead.
A few other odds and sods, like concat() to the || operator, and substring to substr.

It was just a few hours of tinker time, and a few refreshes to get a database setup, setup.

I ended up with a handcrafted sqlite.sql for initial build, and more work to do if this was ever going to be a public release with smooth setup.php steps. From an amateur at PHP and MySQL, it was just tinkering, and then bingo, litebanshee.

Oh, and changing all the connections from MySQLi to SQLIte3 in the utilities and admin support files.

I'm sure I've missed a few things that will come out in the sweeping of corners and testing all the routes and buttons.

Have good, make well.