Advertisement:

Author Topic: SQLite3  (Read 13637 times)

Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 178
SQLite3
« on: September 15, 2012, 01:35:13 PM »
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]
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]
$db_type = 'sqlite3';
Also change your database name if you changed it from the current,
otherwise leave it as is.

w5hro

  • Guest
Re: SQLite3
« Reply #1 on: April 06, 2014, 10:54:52 AM »
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]
$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;
}

Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 178
Re: SQLite3
« Reply #2 on: April 06, 2014, 11:13:42 AM »
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]
$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]

  $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

  • Guest
Re: SQLite3
« Reply #3 on: April 06, 2014, 11:21:51 AM »

Hi ,

Can you try amending it to the following:

Code: [Select]

  $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]
$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));

Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 178
Re: SQLite3
« Reply #4 on: April 06, 2014, 11:24:40 AM »
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

  • Guest
Re: SQLite3
« Reply #5 on: April 06, 2014, 11:36:00 AM »
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

Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 178
Re: SQLite3
« Reply #6 on: April 06, 2014, 11:48:08 AM »
Unfortunately, I think that is going to be an issue with choosing SQLite as it only allows one connection whilst writing to the database.

Quote
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

  • Guest
Re: SQLite3
« Reply #7 on: April 06, 2014, 11:59:44 AM »
Unfortunately, I think that is going to be an issue with choosing SQLite as it only allows one connection whilst writing to the database.

Quote
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.

Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 178
Re: SQLite3
« Reply #8 on: April 06, 2014, 12:18:34 PM »
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]
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]
// 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

  • Guest
Re: SQLite3
« Reply #9 on: April 06, 2014, 12:53:51 PM »
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]
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]
// 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()"


Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 178
Re: SQLite3
« Reply #10 on: April 06, 2014, 12:55:51 PM »
What version of PHP are you running?

w5hro

  • Guest
Re: SQLite3
« Reply #11 on: April 06, 2014, 01:00:40 PM »
What version of PHP are you running?

5.3.27

Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 178
Re: SQLite3
« Reply #12 on: April 06, 2014, 01:13:41 PM »
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

  • Guest
Re: SQLite3
« Reply #13 on: April 06, 2014, 01:26:53 PM »
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

  • Guest
Re: SQLite3
« Reply #14 on: April 06, 2014, 01:34:20 PM »
Sorry, I had to re-uplaod the change to see the error again.

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

Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 178
Re: SQLite3
« Reply #15 on: April 06, 2014, 01:45:49 PM »
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

  • Guest
Re: SQLite3
« Reply #16 on: April 06, 2014, 01:52:15 PM »
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

Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 178
Re: SQLite3
« Reply #17 on: April 06, 2014, 01:56:08 PM »
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

  • Guest
Re: SQLite3
« Reply #18 on: April 06, 2014, 02:00:17 PM »
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]
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;
}

Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 178
Re: SQLite3
« Reply #19 on: April 06, 2014, 03:00:50 PM »
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]
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]
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;
}