SQLite3

[tinoest]

Link to Mod

This Modification will add support for SQLite3 from within SMF.

It will also attempt to convert your current 2.8 database to sqlite 3.3.x

You can either run the following command from the command line

Code Select Expand

sqlite OLD.DB .dump | sqlite3 NEW.DB

Or you can extract the convert_database.php package from the
modification and put it in your smf directory and run it from the web
browser. This will create a new database with .db3 as the suffix,
rather than SMF's .db

Once complete you need to update your Settings.php to

Code Select Expand

$db_type = 'sqlite3';

Also change your database name if you changed it from the current,
otherwise leave it as is.

[w5hro]

Hello, after running this modification I'm getting the occasional error below. My SQLite version is 3.3.6

Notice: Undefined variable: err_msg in /home/mylocation/public_html/Sources/Subs-Db-sqlite3.php on line 374 database is locked

Not sure what that is, but if I look at line 374 I see some code above and below it that looks like it might be some kind of delay or overload timing for the database connection. Any idea what that is and how to correct it? If I hit the back arrow on my browser and then go there again the problem is gone.

Line 374 is: $ret = smf_db_error($db_string . '#!#' . $err_msg, $connection);

Code Select Expand

$st = microtime();
// Don't overload it.
$db_cache[$db_count]['q'] = $db_count < 50 ? $db_string : '...';
$db_cache[$db_count]['f'] = $file;
$db_cache[$db_count]['l'] = $line;
$db_cache[$db_count]['s'] = array_sum(explode(' ', $st)) - array_sum(explode(' ', $time_start));
}

$ret = @$connection->query($db_string);
if ($ret === false && empty($db_values['db_error_skip']))
$ret = smf_db_error($db_string . '#!#' . $err_msg, $connection);

// Debugging.
if (isset($db_show_debug) && $db_show_debug === true)
$db_cache[$db_count]['t'] = array_sum(explode(' ', microtime())) - array_sum(explode(' ', $st));

return $ret;
}

[tinoest]

Hello, after running this modification I'm getting the occasional error below. My SQLite version is 3.3.6

Notice: Undefined variable: err_msg in /home/mylocation/public_html/Sources/Subs-Db-sqlite3.php on line 374 database is locked

Not sure what that is, but if I look at line 374 I see some code above and below it that looks like it might be some kind of delay or overload timing for the database connection. Any idea what that is and how to correct it? If I hit the back arrow on my browser and then go there again the problem is gone.

Line 374 is: $ret = smf_db_error($db_string . '#!#' . $err_msg, $connection);

Code Select Expand

$st = microtime();
// Don't overload it.
$db_cache[$db_count]['q'] = $db_count < 50 ? $db_string : '...';
$db_cache[$db_count]['f'] = $file;
$db_cache[$db_count]['l'] = $line;
$db_cache[$db_count]['s'] = array_sum(explode(' ', $st)) - array_sum(explode(' ', $time_start));
}

$ret = @$connection->query($db_string);
if ($ret === false && empty($db_values['db_error_skip']))
$ret = smf_db_error($db_string . '#!#' . $err_msg, $connection);

// Debugging.
if (isset($db_show_debug) && $db_show_debug === true)
$db_cache[$db_count]['t'] = array_sum(explode(' ', microtime())) - array_sum(explode(' ', $st));

return $ret;
}

Hi ,

Can you try amending it to the following:

Code Select Expand


  $ret = @$connection->query($db_string);
  if ($ret === false && empty($db_values['db_error_skip'])) {
    $err_msg = $connection->lastErrorMsg();
    $ret = smf_db_error($db_string . '#!#' . $err_msg, $connection);
  }


[w5hro]

Hi ,

Can you try amending it to the following:

Code Select Expand


  $ret = @$connection->query($db_string);
  if ($ret === false && empty($db_values['db_error_skip'])) {
    $err_msg = $connection->lastErrorMsg();
    $ret = smf_db_error($db_string . '#!#' . $err_msg, $connection);
  }


Ok, I just changed it to the below and nothing broke. I will try it for awhile and see what happens. I assume I did it right?

Thanks...

Code Select Expand

$st = microtime();
// Don't overload it.
$db_cache[$db_count]['q'] = $db_count < 50 ? $db_string : '...';
$db_cache[$db_count]['f'] = $file;
$db_cache[$db_count]['l'] = $line;
$db_cache[$db_count]['s'] = array_sum(explode(' ', $st)) - array_sum(explode(' ', $time_start));
}

$ret = @$connection->query($db_string);
if ($ret === false && empty($db_values['db_error_skip'])) {
$err_msg = $connection->lastErrorMsg();
$ret = smf_db_error($db_string . '#!#' . $err_msg, $connection);
}

// Debugging.
if (isset($db_show_debug) && $db_show_debug === true)
$db_cache[$db_count]['t'] = array_sum(explode(' ', microtime())) - array_sum(explode(' ', $st));


[tinoest]

Yes, that's correct.

Let me know if it works or there are any other issues and I'll look at updating the modification.

[w5hro]

Yes, that's correct.

Let me know if it works or there are any other issues and I'll look at updating the modification.

I just received a database is locked message. It comes up in a plain white page and just says the 3 words, "database is locked"

I've noticed it only happens now when another member is logged in and trying to post or do something at the same time I am. I enabled the "Load Balancing" feature yesterday and I made the thresholds very large and that helped the most, but I'm still getting the "database is locked" error when another member is on.

P.S. I'm also using SMF 2.0.7

[tinoest]

Unfortunately, I think that is going to be an issue with choosing SQLite as it only allows one connection whilst writing to the database.

In case of write, a single write to the database does lock the database for a short time, nothing, even reading, can access the database file at all.

If you can upgrade to SQLite 3.7.0 then you can use this feature http://www.sqlite.org/draft/wal.html which should help to alleviate this issue.

[w5hro]

Unfortunately, I think that is going to be an issue with choosing SQLite as it only allows one connection whilst writing to the database.

In case of write, a single write to the database does lock the database for a short time, nothing, even reading, can access the database file at all.

If you can upgrade to SQLite 3.7.0 then you can use this feature http://www.sqlite.org/draft/wal.html which should help to alleviate this issue.

Thanks... that makes sense. I just went ahead too and disabled the load balancing to see if the code correction solved the problem. My hosting company has 3.3.6 installed on the server so there is nothing much I can do about that.

What's funny thought is I had installed SMF 2.0.7 on the server not knowing what version they had on the server and it created the SQLite 2.X database even though 3.X was on the server. I didn't start getting the error until converting the database to SQLite 3 via your mod which is really strange.

Let me see how it goes today and if I don't get the original error page again with the load balancing disabled I will come back and let you know.

[tinoest]

Hi,

You can try setting the busyTimeout when you instantiate the db to allow it to wait a bit before returning the database is busy error message.

Changing the smf_db_check_connection function to the following:

Code Select Expand


function smf_db_check_connection ( $connection )
{
  global $db_name;

  if(!is_object($connection)) {
    if (substr($db_name, -4) != '.db3')
      $db_name .= '.db3';

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


    return $connection;
}


And amending the starting function

Code Select Expand

// Initialize the database settings
function smf_db_initiate($db_server, $db_name, $db_user, $db_passwd, $db_prefix, $db_options = array())
{
global $smcFunc, $mysql_set_mode, $db_in_transact, $sqlite_error;

// Map some database specific functions, only do this once.
if (!isset($smcFunc['db_fetch_assoc']) || $smcFunc['db_fetch_assoc'] != 'sqlite_fetch_array')
$smcFunc += array(
'db_query' => 'smf_db_query',
'db_quote' => 'smf_db_quote',
'db_fetch_assoc' => 'smf_db_fetch_array',
'db_fetch_row' => 'smf_db_fetch_row',
'db_free_result' => 'smf_db_free_result',
'db_insert' => 'smf_db_insert',
'db_insert_id' => 'smf_db_insert_id',
'db_num_rows' => 'smf_db_num_rows',
'db_data_seek' => 'sqlite_seek',
'db_num_fields' => 'sqlite_num_fields',
'db_escape_string' => 'smf_db_escape_string',
'db_unescape_string' => 'smf_db_unescape_string',
'db_server_info' => 'smf_db_libversion',
'db_affected_rows' => 'smf_db_affected_rows',
'db_transaction' => 'smf_db_transaction',
'db_error' => 'smf_db_last_error',
'db_select_db' => '',
'db_title' => 'SQLite',
'db_sybase' => true,
'db_case_sensitive' => true,
'db_escape_wildcard_string' => 'smf_db_escape_wildcard_string',
);

if (substr($db_name, -4) != '.db3')
$db_name .= '.db3';

  if (!empty($db_options['persist']))
    $connection = new SQLite3($db_name);
  else
    $connection = new SQLite3($db_name);

// Something's wrong, show an error if its fatal (which we assume it is)
if (!$connection)
{
if (!empty($db_options['non_fatal']))
return null;
else
db_fatal_error();
}
$db_in_transact = false;

// This is frankly stupid - stop SQLite returning alias names!
  @$connection->exec('PRAGMA short_column_names = 1');

// Make some user defined functions!
$connection->createFunction('unix_timestamp', 'smf_udf_unix_timestamp', 0);
$connection->createFunction('inet_aton', 'smf_udf_inet_aton', 1);
$connection->createFunction('inet_ntoa', 'smf_udf_inet_ntoa', 1);
$connection->createFunction('find_in_set', 'smf_udf_find_in_set', 2);
$connection->createFunction('year', 'smf_udf_year', 1);
$connection->createFunction('month', 'smf_udf_month', 1);
$connection->createFunction('dayofmonth', 'smf_udf_dayofmonth', 1);
$connection->createFunction('concat', 'smf_udf_concat');
$connection->createFunction('locate', 'smf_udf_locate', 2);
$connection->createFunction('regexp', 'smf_udf_regexp', 2);

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

return $connection;
}


Might help.

[w5hro]

Hi,

You can try setting the busyTimeout when you instantiate the db to allow it to wait a bit before returning the database is busy error message.

Changing the smf_db_check_connection function to the following:

Code Select Expand


function smf_db_check_connection ( $connection )
{
  global $db_name;

  if(!is_object($connection)) {
    if (substr($db_name, -4) != '.db3')
      $db_name .= '.db3';

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


    return $connection;
}


And amending the starting function

Code Select Expand

// Initialize the database settings
function smf_db_initiate($db_server, $db_name, $db_user, $db_passwd, $db_prefix, $db_options = array())
{
global $smcFunc, $mysql_set_mode, $db_in_transact, $sqlite_error;

// Map some database specific functions, only do this once.
if (!isset($smcFunc['db_fetch_assoc']) || $smcFunc['db_fetch_assoc'] != 'sqlite_fetch_array')
$smcFunc += array(
'db_query' => 'smf_db_query',
'db_quote' => 'smf_db_quote',
'db_fetch_assoc' => 'smf_db_fetch_array',
'db_fetch_row' => 'smf_db_fetch_row',
'db_free_result' => 'smf_db_free_result',
'db_insert' => 'smf_db_insert',
'db_insert_id' => 'smf_db_insert_id',
'db_num_rows' => 'smf_db_num_rows',
'db_data_seek' => 'sqlite_seek',
'db_num_fields' => 'sqlite_num_fields',
'db_escape_string' => 'smf_db_escape_string',
'db_unescape_string' => 'smf_db_unescape_string',
'db_server_info' => 'smf_db_libversion',
'db_affected_rows' => 'smf_db_affected_rows',
'db_transaction' => 'smf_db_transaction',
'db_error' => 'smf_db_last_error',
'db_select_db' => '',
'db_title' => 'SQLite',
'db_sybase' => true,
'db_case_sensitive' => true,
'db_escape_wildcard_string' => 'smf_db_escape_wildcard_string',
);

if (substr($db_name, -4) != '.db3')
$db_name .= '.db3';

  if (!empty($db_options['persist']))
    $connection = new SQLite3($db_name);
  else
    $connection = new SQLite3($db_name);

// Something's wrong, show an error if its fatal (which we assume it is)
if (!$connection)
{
if (!empty($db_options['non_fatal']))
return null;
else
db_fatal_error();
}
$db_in_transact = false;

// This is frankly stupid - stop SQLite returning alias names!
  @$connection->exec('PRAGMA short_column_names = 1');

// Make some user defined functions!
$connection->createFunction('unix_timestamp', 'smf_udf_unix_timestamp', 0);
$connection->createFunction('inet_aton', 'smf_udf_inet_aton', 1);
$connection->createFunction('inet_ntoa', 'smf_udf_inet_ntoa', 1);
$connection->createFunction('find_in_set', 'smf_udf_find_in_set', 2);
$connection->createFunction('year', 'smf_udf_year', 1);
$connection->createFunction('month', 'smf_udf_month', 1);
$connection->createFunction('dayofmonth', 'smf_udf_dayofmonth', 1);
$connection->createFunction('concat', 'smf_udf_concat');
$connection->createFunction('locate', 'smf_udf_locate', 2);
$connection->createFunction('regexp', 'smf_udf_regexp', 2);

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

return $connection;
}


Might help.

That crashes my board, I get "Fatal error: Call to undefined method SQLite3Result::busyTimeout()"

[tinoest]

What version of PHP are you running?

[w5hro]

What version of PHP are you running?

5.3.27

[tinoest]

Hmmm, thats been available since 5.3.3, $connection->busyTimeout(1000); works fine on my install here.

If you haven't already revert the changes, with busyTimeout. Did it say on which line it failed with the fatal error?

[w5hro]

Did it say on which line it failed with the fatal error?

Yes, Fatal error: Call to undefined method SQLite3Result::busyTimeout() directed to that line added in the file.

Maybe, that won't work with 5.3.27 It would be great to have something like that working though. Just so members wouldn't have the database is locked error message come up.

[w5hro]

Sorry, I had to re-uplaod the change to see the error again.

It's the return $connection; line under // Debugging.

[tinoest]

Sorry, you've confused me.

I don't see a // Debugging comment anywhere.

I was just curious as to if it was in both functions or just one of them really.

There is no reason I can think of it won't run on 5.3.27 as thats a newer install than 5.3.3 where it was introduced.

[w5hro]

Sorry, you've confused me.

I don't see a // Debugging comment anywhere.

I was just curious as to if it was in both functions or just one of them really.

Sorry, I opened up the unmodified file by mistake after I saw which line it was. The correct line is below.

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

Also, what is int sqlite3_busy_timeout(sqlite3*, int ms); ?

I'm still pretty new to the code, but the above is mentioned via the following link: http://sqlite.org/c3ref/busy_timeout.html

[tinoest]

Hi,

I think we are confusing each other, I understand what function call it is that is failing. (Which is the one I asked you to add) However I was curious as to if it was telling you which one of the two I asked you to add were failing. Or both.

You have looked at the C implementation there, which is saying that you need to pass the Sqlite3 object  and a integer variable as the second parameter.

The php function that I asked you to add is described here http://www.php.net/manual/en/sqlite3.busytimeout.php

[w5hro]

The $connection->busyTimeout(1000); // Measured in ms so this is 1 Second. line that gives me the error is the one we added below under the function smf_db_check_connection

Does that mean the other one we added worked?

Code Select Expand

function smf_db_check_connection ( $connection )
{
global $db_name;

if(!is_object($connection)) {
if (substr($db_name, -4) != '.db3')
$db_name .= '.db3';

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

return $connection;
}

[tinoest]

The $connection->busyTimeout(1000); // Measured in ms so this is 1 Second. line that gives me the error is the one we added below under the function smf_db_check_connection

Does that mean the other one we added worked?

Code Select Expand

function smf_db_check_connection ( $connection )
{
global $db_name;

if(!is_object($connection)) {
if (substr($db_name, -4) != '.db3')
$db_name .= '.db3';

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

return $connection;
}

Oh, I see the issue now.

Its because the connection being passed in to that function is of a Sqlite3Resource not a Sqlite3. I missed that in the error message, should of looked more carefully.

Can you try the following:

Code Select Expand

function smf_db_check_connection ( $connection )
{
global $db_name;

if(!is_object($connection)) {
if (substr($db_name, -4) != '.db3')
$db_name .= '.db3';

$connection = new SQLite3($db_name);

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

}


return $connection;
}


[w5hro]

Ok, I tried that and it no longer crashes. I will try it for a while and see what happens.

Can you check my attached file, I want to make sure it's correct.

Thanks...

[tinoest]

That looks correct, let me know how it goes.

[w5hro]

I just found a serious problem. Personal messages are no longer coming up. If you go to your inbox or sent items box and click on the subject nothing happens. The conversion must have corrupted it somehow

[tinoest]

Sorry , what conversion?

The adding of the additional SQL and correcting the error's?

[w5hro]

It must have been the conversion utility that converted my database from dB to db3.

I did notice it reduced the size of my database by about 1/2, but I thought version 3 was just more efficient. Looks like it did something to all of the PM's. They are still listed, but are gone now.

Thanks for all of your help, but I've decided to just bite the bullet and do a fresh install using MY SQL instead. I really wanted to use SQLite because I had a phpBB3 My SQL board for a long time and I got tired of the occasional connection errors and members complaining about slow and crashed uploads. If I was on a dedicated server it would probably be fine, but it's an issue when on a shared server. SQLite is really better, but most of the world has been brainwashed for so long that they think they just gotta have My SQL. As a result developers just don't spend a lot of time with it and most Mods won't work with it as a result either.

Anyway, it's either that or I just install phpBB3 with SQLlite 3 which I may do before its over. I'll see how SMF works with MY SQL first. Maybe its not a resource hog like phpBB3 is, but we will see.

[tinoest]

Which utility? Was it the one provided by SQLite? Or the one I put in with this mod?

I'd like to fix it if it was the one given with the mod that's all.

[w5hro]

Which utility? Was it the one provided by SQLite? Or the one I put in with this mod?

I'd like to fix it if it was the one given with the mod that's all.

Yes, it was the one in your mod package here. I just had not tired to click on a PM in a few days so I didn't realize there was a problem until yesterday evening.

[tinoest]

Thanks, I'll see if I can replicate it here then.

Do you still have your old database?

If you have the SQLite admin installed can you see if any data is contained within the database tables for pm's?

[w5hro]

I'll look at it, but give me couple of days. I was looking at it last night and either they are gone or they just got disconnected somehow. I still have the old SQLite board up, I just moved it to a different folder within the root so I can create the new one to match it. The I will manually export to old posts and user accounts. Luckily the board was only up for a couple of months so it only had a 100+ posts.

[w5hro]

Ok, I looked through the db3 file and the PM's are still there. They must somehow just be disconnected from where they need to connect.

Anyway, I already have the My SQL databease setup and everything imported so it's done now.

Thanks...

[tinoest]

Thanks for the feedback.

I'm yet to be able to reproduce the problem, I'm trying to figure out where it went wrong.

I'll fix the other issues that you raised, I'm assuming that they improved the performance overall.

I hope it goes well for you with MySQL

[w5hro]

When I went to my inbox and clicked on my received messages or even the ones in sent items nothing would display and/or pop up. They were listed, but wouldn't come up.

Hope that helps...

[tinoest]

Hi,

I tried that and it all went well for me. Have you tried sending any new pm's and seeing if they appear.

I do appreciate your time and feedback

[w5hro]

Hi,

I tried that and it all went well for me. Have you tried sending any new pm's and seeing if they appear.

I do appreciate your time and feedback

Yes, that's when I noticed the problem. I sent a PM and went to check my sent items box and it would not come up. Then I realized non of the PM's would display any longer, only the subject titles.

[indigious]

Hi, tinoest! Do you plan to update this useful mod for the latest versions of SMF? I mean v1.1.21 and v2.0.11?

[vbgamer45]

It should still work for 2.0.11

[tinoest]

Looking through the changes, it should still work with 2.0.11, it never supported 1.1.x as that didn't have SQLite Support in it either and was just MySQL.

There is only one change I can see thats in DbSearch-sqlite3.php where on line 72 the preg replace was changed to MEMORY from HEAP which is still in this modification.

I also never got to the bottom of the character encoding issues.

[indigious]

tinoest
vbgamer45
OK, thank you!