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
sqlite OLD.DB .dump | sqlite3 NEW.DBMore information is found here http://www.sqlite.org/version3.html (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
require_once('sqlite_wrapper.php');after the
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.
-- FIXEDQuoteThere 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.
UPDATE smf_settings SET value = 0 WHERE variable = 'databaseSession_enable'-- FIXEDEdit : Modified to add checks to ensure you can run the class, also fixed db session issue
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.
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
Quote from: tinoest on August 13, 2012, 09:34:33 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...
Quote from: tinoest on August 13, 2012, 09:34:33 AM
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. :)
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.
Quote from: tinoest on August 13, 2012, 01:36:57 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!
Quote from: tinoest on August 13, 2012, 01:36:57 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. :)
Fixed Session History Error in the first attached file.
Quote from: emanuele on August 13, 2012, 02:10:34 PM
Quote from: tinoest on August 13, 2012, 01:36:57 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 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. :-\
Quote from: nend 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. :-\
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.
Quote from: tinoest on August 13, 2012, 04:32:43 PM
Quote from: emanuele on August 13, 2012, 02:10:34 PM
Quote from: tinoest on August 13, 2012, 01:36:57 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.
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!
@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
Quote from: nend 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
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.
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
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.
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
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().
Quote from: nend 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().
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.
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.
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
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, ¶m->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.
Quote from: nend 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.
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
Do you know that you can invoke the SQLite as an object like you can in SQLite3 ( well you don't have a choice in that function )
So you can SQLite->queryExec for example.
Methods are at the top , although they are all mentioned in the procedural counterparts.
http://www.php.net/manual/en/ref.sqlite.php
Yeah, already knew that. When I first started missing with SQLite I used to use those statements. Then I moved to SQLite3. I don't think I will use PDO though, don't like how it is set up for SQLite.
Its not actually the PDO , that's something entirely different.
Although I have to admit , I prefer the procedural way for DB connections over the objects.
You probably need to add a check to ensure $this->handle still exists in your quoted code btw , atleast that was a problem I had with the SQLite3 and SMF 2.0
Quote from: tinoest on August 13, 2012, 04:32:43 PM
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.
There are two kind of people that could use SQLite:
1) those who know what they are doing,
2) those that don't even know what a database is.
There is also a third category: those that did a mistake while installing and didn't notice until later, but that is rather small group.
Considering the timing (well...yeah...I know), I think that for 2.1 we will be still stuck with SQLite 2.8. The idea of a mod or seems the way to for now, that way there will be enough time to test different cases and include the support in 2.2. :)
I feel support the two versions in parallel could be a nightmare the moment we decide to drop 2.8 in favour of 3.x...
FWIW: today did a fresh install of latest SMF 2.0.4 on PHP 5.3.22 using SQLite 3.7.3 and above wrapper with edit to Subs-Db-sqlite.php as in first post before running install.php.
As far as I can tell it all works flawlessly - may fall over it host upgrades PHP of course. Just for feedback and thanks very much for the wrapper (only SQLite available for free hosting).
Original code for non registrered users: <?php
if (!defined('SMF'))
die('Hacking attempt...');
if(!defined('SQLITE_NUM')) define ( 'SQLITE_NUM' , SQLITE3_NUM);
// Included by default from php 5.3.x
if(phpversion() < '5.3.0') return;
if(!class_exists("SQLite3")) return;
if(!function_exists('sqlite_open')) {
function sqlite_open($location,$mode,$error) {
// Error and Mode not used
$dB = new SQLite3($location);
return $dB;
}
}
if(!function_exists('sqlite_popen')) {
function sqlite_popen($location,$mode,$error) {
// Error and Mode not used
$dB = new SQLite3($location);
return $dB;
}
}
if(!function_exists('sqlite_query')) {
function sqlite_query($query, $dB , $result = FALSE , &$errmsg = FALSE ) {
global $db_name;
$created = FALSE;
if(!is_object($dB)) {
if (substr($db_name, -3) != '.db')
$db_name .= '.db';
$dB = new SQLite3($db_name);
$created = TRUE;
}
if(stristr($query,'SELECT') /*|| stristr($query,'INSERT') || stristr($query,'UPDATE') */) {
$result = $dB->query($query);
} else {
$result = $dB->exec($query);
}
if($created === TRUE)
$dB->close();
if($result === FALSE)
return FALSE;
return $result;
}
}
if(!function_exists('sqlite_fetch_array')) {
function sqlite_fetch_array(&$result,$type = SQLITE3_BOTH ) {
if(!is_object($result)) {
return $result;
}
$resx = $result->fetchArray( $type );
return $resx;
}
}
if(!function_exists('sqlite_create_function')) {
function sqlite_create_function ( $dB , $funcname , $callback , $args = '-1' ) {
$ret = $dB->createFunction ( $funcname , $callback , $args);
return $ret;
}
}
if(!function_exists('sqlite_escape_string')) {
function sqlite_escape_string ( $query ) {
global $db_connection , $db_name;
$created = FALSE;
if(!is_object($db_connection)) {
if (substr($db_name, -3) != '.db')
$db_name .= '.db';
$dB = new SQLite3($db_name);
$created = TRUE;
} else {
$dB = $db_connection;
}
$esql = $dB->escapeString($query);
if($created === TRUE)
$dB->close();
return $esql;
}
}
if(!function_exists('sqlite_changes')) {
function sqlite_changes () {
global $db_connection , $db_name;
$created = FALSE;
if(!is_object($db_connection)) {
if (substr($db_name, -3) != '.db')
$db_name .= '.db';
$dB = new SQLite3($db_name);
$created = TRUE;
} else {
$dB = $db_connection;
}
$changes = $dB->changes();
if($created === TRUE)
$dB->close();
return $changes;
}
}
if(!function_exists('sqlite_num_rows')) {
function sqlite_num_rows ( $result ) {
$numRows = 0;
while ($result->fetchArray())
$numRows ++;
return ($numRows);
}
}
if(!function_exists('sqlite_libversion')) {
function sqlite_libversion () {
global $db_connection , $db_name;
$created = FALSE;
if(!is_object($db_connection)) {
if (substr($db_name, -3) != '.db')
$db_name .= '.db';
$dB = new SQLite3($db_name);
$created = TRUE;
} else {
$dB = $db_connection;
}
$version = $dB->version();
if($created === TRUE)
$dB->close();
return $version;
}
}
if(!function_exists('sqlite_last_insert_rowid' )) {
function sqlite_last_insert_rowid ( $dB ) {
return $dB->lastInsertRowID();
}
}
if(!function_exists('sqlite_last_error')) {
function sqlite_last_error ( $dB ) {
global $db_name;
$created = FALSE;
if(!is_object($dB)) {
if (substr($db_name, -3) != '.db')
$db_name .= '.db';
$dB = new SQLite3($db_name);
$created = TRUE;
}
$errCode = $dB->lastErrorCode();
if($created === TRUE)
$dB->close();
return $errCode;
}
}
if(!function_exists('sqlite_error_string')) {
function sqlite_error_string ($errno) {
global $db_connection , $db_name;
$created = FALSE;
if(!is_object($db_connection)) {
if (substr($db_name, -3) != '.db')
$db_name .= '.db';
$dB = new SQLite3($db_name);
$created = TRUE;
} else {
$dB = $db_connection;
}
$lastError = $dB->lastErrorMsg();
if($created === TRUE)
$dB->close();
return $lastError;
}
}
?>