Advertisement:

Author Topic: SMF SQLite3 Wrapper  (Read 35948 times)

Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 267
SMF SQLite3 Wrapper
« on: August 12, 2012, 11:24:04 AM »
Hi,

I recently upgraded my version of php to php-5.4.4 which doesn't include the sqlite_ functions natively anymore , so I wrote a wrapper class that I then load if the functions don't exist.

To change from a sqlite 2.8 to a sqlite 3.x database is quite easy

Code: [Select]
sqlite OLD.DB .dump | sqlite3 NEW.DB
More information is found here http://www.sqlite.org/version3.html

  • If you then download the attached file and insert it into your Sources directory.
  • Then just include the attached file in the Subs-Db-sqlite.php class

    With the following code


Code: [Select]
require_once('sqlite_wrapper.php');
after the

Code: [Select]
if (!defined('SMF'))
  die('Hacking attempt...');

I would appreciate if anyone can give any feedback on this, I am not sure if it is classed as a modification? If so I can package it up , it also works for a clean install of SMF.

-- FIXED

Quote
There is one bug with regards to database session handling , in that it currently doesn't work , although I am working on a fix .

But if you disable this option , everything so far seems fine.
 

Code: [Select]
UPDATE smf_settings SET value = 0 WHERE variable = 'databaseSession_enable'
-- FIXED


Edit : Modified to add checks to ensure you can run the class, also fixed db session issue
« Last Edit: August 13, 2012, 04:29:06 PM by tinoest »

Offline emanuele

  • SMF Super Hero
  • *******
  • Posts: 14,156
  • Gender: Male
  • THERE'S JUST ME
Re: SMF SQLite3 Wrapper
« Reply #1 on: August 13, 2012, 06:22:06 AM »
Hi tinoest!

Great job! :D

While preparing SMF 2.1 we were talking about the possibility to move from the current 2.8 implementation to a 3.x, the two main problems that stopped us were:
1) it was somehow late (at the time lol),
2) the conversion of the database from 2.8 to 3.x using SMF's upgrade script (the command you reported should be run from the command line, but probably shell_exec could be used now that I think about it, but don't know if it is supported by many hosts...I'm not an expert of SQLite...).

I think it could be in the form of a mod or in the form of instructions on how to install it since I assume if the host upgrades php the forum doesn't work any more and a mod could not be installed.


Take a peek at what I'm doing! ;D



Hai bisogno di supporto in Italiano?

Aiutateci ad aiutarvi: spiegate bene il vostro problema: no, "non funziona" non è una spiegazione!!
1) Cosa fai,
2) cosa ti aspetti,
3) cosa ottieni.

Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 267
Re: SMF SQLite3 Wrapper
« Reply #2 on: August 13, 2012, 09:34:33 AM »
Hi emanuele, 

The upgrading of the SQLite DB I did on the command line and would suggest not doing it to a live copy of your database anyway , due to the remote possibility of losing data , you can do it with a shell_exec or a system command through php if you wished.

Is this the correct forum for this btw? Or should it be in the Modifications sections or somewhere similar?

Thanks,

tino

Offline emanuele

  • SMF Super Hero
  • *******
  • Posts: 14,156
  • Gender: Male
  • THERE'S JUST ME
Re: SMF SQLite3 Wrapper
« Reply #3 on: August 13, 2012, 10:36:50 AM »
The upgrading of the SQLite DB I did on the command line and would suggest not doing it to a live copy of your database anyway , due to the remote possibility of losing data
Yep, I understand.
That's (one of) the problem(s), because I think people would expect to be able to do it from the script...

Is this the correct forum for this btw? Or should it be in the Modifications sections or somewhere similar?
Here or coding discussion I'd say. Probably coding discussion is more active than this, that's all. :)


Take a peek at what I'm doing! ;D



Hai bisogno di supporto in Italiano?

Aiutateci ad aiutarvi: spiegate bene il vostro problema: no, "non funziona" non è una spiegazione!!
1) Cosa fai,
2) cosa ti aspetti,
3) cosa ottieni.

Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 267
Re: SMF SQLite3 Wrapper
« Reply #4 on: August 13, 2012, 01:36:57 PM »
Indeed , although before I go to the effort of packaging and thinking about how to handle the conversion from sqlite 2.8 to sqlite 3.x , I will leave it here to be tested in its current form. As I would prefer to have a stable and working wrapper rather than a 'nice' process to upgrade. I have never seen the point in making something 'nice' over stability.

There is no way that I know off other than the command line to upgrade from sqlite 2.8 to sqlite 3.x .

Although that imo, isn't to hard to do. Its alot easier than many other instances I've seen and only requires a small amount of manual labour.


Offline emanuele

  • SMF Super Hero
  • *******
  • Posts: 14,156
  • Gender: Male
  • THERE'S JUST ME
Re: SMF SQLite3 Wrapper
« Reply #5 on: August 13, 2012, 02:10:34 PM »
As I would prefer to have a stable and working wrapper rather than a 'nice' process to upgrade. I have never seen the point in making something 'nice' over stability.
Yep, indeed!

Although that imo, isn't to hard to do. Its alot easier than many other instances I've seen and only requires a small amount of manual labour.
Indeed it's a rather simple way, if you have access to a command line. ;)
But probably it will just be a matter of document the procedure when the time will come. :)


Take a peek at what I'm doing! ;D



Hai bisogno di supporto in Italiano?

Aiutateci ad aiutarvi: spiegate bene il vostro problema: no, "non funziona" non è una spiegazione!!
1) Cosa fai,
2) cosa ti aspetti,
3) cosa ottieni.

Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 267
Re: SMF SQLite3 Wrapper
« Reply #6 on: August 13, 2012, 04:26:36 PM »
Fixed Session History Error in the first attached file.

Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 267
Re: SMF SQLite3 Wrapper
« Reply #7 on: August 13, 2012, 04:32:43 PM »
Although that imo, isn't to hard to do. Its alot easier than many other instances I've seen and only requires a small amount of manual labour.
Indeed it's a rather simple way, if you have access to a command line. ;)
But probably it will just be a matter of document the procedure when the time will come. :)

If you don't have access to the command line , you are not likely to have access to shell_exec or system either.

Which creates the problem , in this instance I would suggest exporting the database ( if you can ) or using an old backup and converting that across on your local machine or using one of the many available tools online.

Although by default , if you are using sqlite_ then I would hazard a guess at you know something about databases as MySQL if by far the norm in the open source environment , especially with php.

Offline nend

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 1,755
  • 2 deep n2 the code
    • sicommnend on GitHub
    • SIComm.us
Re: SMF SQLite3 Wrapper
« Reply #8 on: August 13, 2012, 05:01:26 PM »
I have 5.3.10 and it includes both. Just looking at the script your provided it would create a SQLite 2 database on my version since the sqlite_* functions still exist on this version.

Only way to use SQLite 3 is on this version is to call the SQLite3 class.  :-\

Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 267
Re: SMF SQLite3 Wrapper
« Reply #9 on: August 13, 2012, 05:09:22 PM »
I have 5.3.10 and it includes both. Just looking at the script your provided it would create a SQLite 2 database on my version since the sqlite_* functions still exist on this version.

Only way to use SQLite 3 is on this version is to call the SQLite3 class.  :-\

That is correct , I only wrote this as I don't have sqlite available in php 5.4.x , to do it in php 5.3.x would be harder but not impossible.

It depends I guess on the individual use case, this was designed to be a wrapper , not a replacement.

Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 267
Re: SMF SQLite3 Wrapper
« Reply #10 on: August 14, 2012, 02:44:14 PM »
Although that imo, isn't to hard to do. Its alot easier than many other instances I've seen and only requires a small amount of manual labour.
Indeed it's a rather simple way, if you have access to a command line. ;)
But probably it will just be a matter of document the procedure when the time will come. :)

If you don't have access to the command line , you are not likely to have access to shell_exec or system either.

Which creates the problem , in this instance I would suggest exporting the database ( if you can ) or using an old backup and converting that across on your local machine or using one of the many available tools online.

Although by default , if you are using sqlite_ then I would hazard a guess at you know something about databases as MySQL if by far the norm in the open source environment , especially with php.

I thought some more about this , and it seems ( in a very limited and simplified test ) that you can export the data from sqlite 2 database and then import it into sqlite 3 database structure.

So if there is a requirement , and SMF would like it done , I can create a SQLite3 version that you can include in your code base as required, rather than the quick and dirty wrapper that I created because 5.4.x doesn't support sqlite_ functions anymore.

Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 267
Re: SMF SQLite3 Wrapper
« Reply #11 on: August 14, 2012, 04:43:21 PM »
Attached is a php file that will work on php 5.3.x it exports the data from the database you specified and then imports it into a sqlite3 db if you set the flag import to TRUE , default is FALSE;

It has limited error checking and will output the SQL to screen , or a file if you pipe it.

It appears to work fine , so its an alternative if you don't have access to the command line.

Although I should add currently it just works on the command line  :P As my skill is not at making a nice gui , more the actual code , so if someone wants to add html and that stuff , it would be appreciated.

Also to note , you have to specify the current database name and your new database name , these should not be the same!

Offline nend

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 1,755
  • 2 deep n2 the code
    • sicommnend on GitHub
    • SIComm.us
Re: SMF SQLite3 Wrapper
« Reply #12 on: August 15, 2012, 11:20:55 PM »
@tinest

I was wondering if I can use some of your wrapper functions in my SQLite3 Cache mod to add compatibility to newer versions of PHP. I don't need all the functions just the ones I am going to use.

Here is the mod if you would like to take a look.
http://custom.simplemachines.org/mods/index.php?mod=3412

Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 267
Re: SMF SQLite3 Wrapper
« Reply #13 on: August 16, 2012, 04:02:45 AM »
@tinest

I was wondering if I can use some of your wrapper functions in my SQLite3 Cache mod to add compatibility to newer versions of PHP. I don't need all the functions just the ones I am going to use.

Here is the mod if you would like to take a look.
http://custom.simplemachines.org/mods/index.php?mod=3412

To be pedantic its tinoest , although tino is also fine  :P

You can utilise what you like in your modification , I am willing to assist if you wish with the modification.

I should of put in the code that I released what I have done under the same license as SMF , which I believe is pretty open source.

Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 267
Re: SMF SQLite3 Wrapper
« Reply #14 on: August 16, 2012, 05:42:01 PM »
If you want to make it a bit more permanent , ie Run it on PHP5.3

Then you can download the attached files and add them to your Sources Directory , an change the db_type in the settings to sqlite3

The db needs to be renamed .db3 instead of .db

Offline nend

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 1,755
  • 2 deep n2 the code
    • sicommnend on GitHub
    • SIComm.us
Re: SMF SQLite3 Wrapper
« Reply #15 on: August 17, 2012, 12:55:39 PM »
I am thinking of making a class so anybody that is on SQLite2 can use it to. I am going to make the SQLite3 class that is found on all newer PHP versions if it is not found, but need some help making the class.

Code: [Select]
if (!class_exists('SQLite3')) {
class SQLite3 {
var $handle;
function SQLite3($data) {
$this->handle = sqlite_open($data);
}
public function exec($data) {
return sqlite_exec($this->handle, $data);
}
public function querySingle($data, $type = false) {
if ($type == true) {$type = false;} else {$type = true;}
return sqlite_single_query($this->handle, $data, $type);
}
public function escapeString($data) {
return sqlite_escape_string($data);
}
public function lastErrorCode() {
return sqlite_last_error();
}
}
}

Some of the problems I am facing is finding the SQLite3 alternative like for sqlite_udf_encode_binary().

Just tested and it sort of works
« Last Edit: August 17, 2012, 02:24:33 PM by nend »

Offline nend

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 1,755
  • 2 deep n2 the code
    • sicommnend on GitHub
    • SIComm.us
Re: SMF SQLite3 Wrapper
« Reply #16 on: August 17, 2012, 02:08:26 PM »
Ok, just got backwards compatibility with SQLite 2, now just need forward compatibility with PHP 5.4 or higher. Only one function keeping me from doing that, sqlite_udf_encode_binary().

Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 267
Re: SMF SQLite3 Wrapper
« Reply #17 on: August 17, 2012, 02:35:29 PM »
Ok, just got backwards compatibility with SQLite 2, now just need forward compatibility with PHP 5.4 or higher. Only one function keeping me from doing that, sqlite_udf_encode_binary().

How do you mean?

I think SQLite3Stmt::bindParam is binary safe if you wanted to utilise that in php5.4  . Unfortunately there is not the equivalent function in SQLite3 yet to match the udf_encode_binary from php sqlite.

Offline nend

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 1,755
  • 2 deep n2 the code
    • sicommnend on GitHub
    • SIComm.us
Re: SMF SQLite3 Wrapper
« Reply #18 on: August 17, 2012, 03:20:39 PM »
It doesn't state it is or isn't.  :-\

Will test later to see if it is, if not I can't use it. I need it to be binary safe because the data getting inserted is compressed.

Offline tinoest

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 267
Re: SMF SQLite3 Wrapper
« Reply #19 on: August 17, 2012, 04:09:49 PM »
Digging into the Source Code of php you find the following function

sqlite3_bind_blob , this is part of the execute command if the type is off SQLITE_BLOB

Code: [Select]
        case SQLITE_BLOB:
        {
          php_stream *stream = NULL;
          int blength;
          char *buffer = NULL;
          if (Z_TYPE_P(param->parameter) == IS_RESOURCE) {
            php_stream_from_zval_no_verify(stream, &param->parameter);
            if (stream == NULL) {
              php_sqlite3_error(stmt_obj->db_obj, "Unable to read stream for parameter %ld", param->param_number);
              RETURN_FALSE;
            }
            blength = php_stream_copy_to_mem(stream, (void *)&buffer, PHP_STREAM_COPY_ALL, 0);
          } else {
            convert_to_string(param->parameter);
            blength =  Z_STRLEN_P(param->parameter);
            buffer = Z_STRVAL_P(param->parameter);
          }

          sqlite3_bind_blob(stmt_obj->stmt, param->param_number, buffer, blength, SQLITE_TRANSIENT);

          if (stream) {
            pefree(buffer, 0);
          }
          break;
        }


So when you bind the param then execute it , if it is of type SQLITE_BLOB , it should make it binary safe.