View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0019350 | mantisbt | db schema | public | 2015-02-07 03:27 | 2025-03-10 12:46 |
Reporter | seclan | Assigned To | dregad | ||
Priority | normal | Severity | minor | Reproducibility | have not tried |
Status | resolved | Resolution | duplicate | ||
Summary | 0019350: SQLite support patches for mantis 1.2.19 and 1.3.0b1 | ||||
Description | Hi All, How to install:
About SQLite aware SQL schemas: If you will use a PRIMARY AUTOINCREMENT word, don't use PRIMARY words in other places for the schema, Instead, consider to use UNIQUE words. SourceIntegration plugin: SourceIntegration plugin (2015/01/03) has a same problem. In function schema(), there are "repository" and "changeset" schema definitions. Good luck ! | ||||
Tags | patch | ||||
Attached Files | mantis.sqlite12.diff (11,963 bytes)
diff -urN mantisbt-1.2.19/admin/install.php mantisbt/admin/install.php --- mantisbt-1.2.19/admin/install.php Sun Jan 25 15:00:30 2015 +++ mantisbt/admin/install.php Sat Feb 07 09:00:00 2015 @@ -253,7 +253,9 @@ print_test( 'Setting Database Username', '' !== $f_db_username, true, 'database username is blank' ); print_test( 'Setting Database Password', '' !== $f_db_password, false, 'database password is blank' ); - print_test( 'Setting Database Name', '' !== $f_database_name, true, 'database name is blank' ); + if( $f_db_type != 'sqlitepdo' ) { + print_test( 'Setting Database Name', '' !== $f_database_name, true, 'database name is blank' ); + } if( $f_db_type == 'db2' ) { print_test( 'Setting Database Schema', !is_blank( $f_db_schema ), true, 'must have a schema name for AS400 in the form of DBNAME/SCHEMA' ); @@ -425,6 +427,7 @@ 'pgsql' => 'PostgreSQL', 'oci8' => 'Oracle', 'db2' => 'IBM DB2', + 'sqlitepdo' => 'SQLite', ); // mssql is not supported with PHP >= 5.3 @@ -673,6 +676,13 @@ while(( $i <= $lastid ) && !$g_failed ) { if( !$f_log_queries ) { echo '<tr><td bgcolor="#ffffff">'; + } + + if($f_db_type == 'sqlitepdo'){ + //If the schema has an AUTOINCREMENT field, avoid to use PRIMARY fields in other places. + if($upgrade[$i][0]=='CreateTableSQL' && $upgrade[$i][1][0]==db_get_table( 'mantis_tag_table' )){ + $upgrade[$i][1][1] = preg_replace('!NOTNULL PRIMARY DEFAULT!', 'NOTNULL UNIQUE DEFAULT', $upgrade[$i][1][1]); + } } $dict = NewDataDictionary( $g_db ); diff -urN mantisbt-1.2.19/core/database_api.php mantisbt/core/database_api.php --- mantisbt-1.2.19/core/database_api.php Sun Jan 25 15:00:30 2015 +++ mantisbt/core/database_api.php Sat Feb 07 09:00:00 2015 @@ -164,6 +164,9 @@ case 'odbc_mssql': $t_support = function_exists( 'odbc_connect' ); break; + case 'sqlitepdo': + $t_support = class_exists( 'PDO' ); + break; default: $t_support = false; } @@ -459,6 +462,10 @@ $t_array = $p_result->fields; $p_result->MoveNext(); return $t_array; + } else if($p_result->databaseType=='array'){ + $t_array = each($p_result->_array); + if($t_array===false)return false; + return $t_array[1]; } else { $t_row = $p_result->GetRowAssoc( false ); static $t_array_result; @@ -726,6 +733,9 @@ case 'postgres7': case 'pgsql': return pg_escape_string( $p_string ); + case 'sqlitepdo': + $t_escaped = $g_db->_connectionID->quote($p_string); + return utf8_substr( $t_escaped, 1, utf8_strlen( $t_escaped ) - 2 ); default: error_parameters( 'db_type', $t_db_type ); trigger_error( ERROR_CONFIG_OPT_INVALID, ERROR ); diff -urN mantisbt-1.2.19/core/profile_api.php mantisbt/core/profile_api.php --- mantisbt-1.2.19/core/profile_api.php Sun Jan 25 15:00:30 2015 +++ mantisbt/core/profile_api.php Sat Feb 07 09:00:00 2015 @@ -289,7 +289,7 @@ FROM $t_user_profile_table up, $t_bug_table b WHERE $t_project_where AND up.id = b.profile_id - ORDER BY platform, os, os_build"; + ORDER BY up.platform, up.os, up.os_build"; $result = db_query_bound( $query ); $t_rows = array(); diff -urN mantisbt-1.2.19/library/adodb/adodb.inc.php mantisbt/library/adodb/adodb.inc.php --- mantisbt-1.2.19/library/adodb/adodb.inc.php Sun Jan 25 15:00:34 2015 +++ mantisbt/library/adodb/adodb.inc.php Sat Feb 07 09:00:00 2015 @@ -4294,6 +4294,7 @@ case 'odbtp': if (strncmp('odbtp_',$drivername,6)==0) return substr($drivername,6); case 'odbc' : if (strncmp('odbc_',$drivername,5)==0) return substr($drivername,5); case 'ado' : if (strncmp('ado_',$drivername,4)==0) return substr($drivername,4); + case 'pdo': return 'sqlitepdo'; case 'native': break; default: return $provider; diff -urN mantisbt-1.2.19/library/adodb/datadict/datadict-sqlitepdo.inc.php mantisbt/library/adodb/datadict/datadict-sqlitepdo.inc.php --- mantisbt-1.2.19/library/adodb/datadict/datadict-sqlitepdo.inc.php Thu Jan 01 09:00:00 1970 +++ mantisbt/library/adodb/datadict/datadict-sqlitepdo.inc.php Sat Feb 07 09:00:00 2015 @@ -0,0 +1,141 @@ +<?php +/** + V5.10 10 Nov 2009 (c) 2000-2009 John Lim (jlim#natsoft.com). All rights reserved. + (c)2015 seclan. All rights reserved. + + Released under both BSD license and Lesser GPL library license. + Whenever there is any discrepancy between the two licenses, + the BSD license will take precedence. + + Set tabs to 4 for best viewing. + +*/ + +// security - hide paths +if (!defined('ADODB_DIR')) die(); + +class ADODB2_sqlitepdo extends ADODB_DataDict { + + var $databaseType = 'sqlitepdo'; + var $seqField = false; + + + function ActualType($meta) + { + switch($meta) { + case 'C': + case 'XL': + case 'X': return 'TEXT'; + + case 'C2': return 'TEXT'; + case 'X2': return 'TEXT'; + + case 'B': return 'BLOB'; + + case 'TS': + case 'D': + case 'T': return 'TEXT'; + + case 'L': + case 'I': + case 'I1': + case 'I2': + case 'I4': + case 'I8': return 'INTEGER'; + + case 'F': return 'REAL'; + case 'N': return 'NUMERIC'; + default: + return $meta; + } + } + + function _regen_table($tabname, $dropnames = null, $chgtype = null, $renames=null) + { + if($dropnames===null) $dropnames=array(); + if($chgtype===null) $chgtype=array(); + if($renames===null) $renames=array(); + + $mcs = $this->MetaColumns($tabname); + $newcols = array(); + $cpycols = array(); + $newpkeys = array(); + + foreach($mcs as $fo) { + if(isset($dropnames[$fo->name]))continue; + // + if($chgtype[$fo->name]){ + $sqln = $chgtype[$fo->name]; + } else { + $sqln = $fo->type; + if($fo->max_length) $sqln .= "({$fo->max_length})"; + if($fo->default_value!==NULL) $sqln .= " DEFAULT {$fo->default_value}"; + if($fo->not_null) $sqln .= ' NOT NULL'; + //ignore scale(sqlite:always 0) $fo->scale + } + if($renames[$fo->name]){ + $rname = $renames[$fo->name]; + $newcols[] = $rname.' '.$sqln; + $cpycols[] = "{$fo->name} AS {$rname}"; + if($fo->primary_key) $newpkeys[] = $rname; + } else { + $newcols[] = $fo->name.' '.$sqln; + $cpycols[] = $fo->name; + if($fo->primary_key) $newpkeys[] = $fo->name; + } + } + + $newcolstr = implode(', ',$newcols); + if(count($newpkeys)) $newcolstr.=', PRIMARY KEY('.implode(', ', $newpkeys).')'; + $cpycolstr = implode(', ',$cpycols); + + $tempname = $tabname.'_tmp'; + $aSql[] = 'BEGIN TRANSACTION'; // we use a transaction, to make sure not to loose the content of the table + $aSql[] = "CREATE TABLE {$tempname}({$newcolstr})"; + $aSql[] = "INSERT INTO {$tempname} SELECT {$cpycolstr} FROM {$tabname}"; + $aSql[] = "DROP TABLE {$tabname}"; + $aSql[] = "ALTER TABLE {$tempname} RENAME TO {$tabname}"; + // recreate the indexes, if they not contain one of the droped columns + $dropflds = array_flip($dropnames); + $midx = $this->MetaIndexes($tabname); + if(is_array($midx) && count($midx))foreach($midx as $idx_name => $idx_data) { + if(!count($dropflds) || !count(array_intersect($dropflds,$idx_data['columns']))){ + $aSql = array_merge($aSql,$this->CreateIndexSQL($idx_name,$tabname,$idx_data['columns'], + $idx_data['unique'] ? array('UNIQUE') : False)); + } + } + $aSql[] = 'COMMIT'; + return $aSql; + } + + function AlterColumnSQL($tabname, $flds) + { + $t_newflds = $this->_GenFields($flds,true); + $t_flds = preg_split('![ \t]+!', current($t_newflds[0]), 2); + $chgtype = array($t_flds[0]=>$t_flds[1]); + + $sqls = $this->_regen_table($tabname,null,$chgtype); + return $sqls; + } + + function DropColumnSQL($tabname, $flds) + { + $dropnames = array_flip(explode(',',$flds)); + $sqls = $this->_regen_table($tabname,$dropnames); + return $sqls; + } + + function RenameColumnSQL($tabname,$oldcolumn,$newcolumn,$flds='') + { + if($flds!=''){ + $t_newflds = $this->_GenFields($flds,true); + $t_flds = preg_split('![ \t]+!', current($t_newflds[0]), 2); + $chgtype = array($t_flds[0]=>$t_flds[1]); + } else { + $chgtype = null; + } + $renames=array($oldcolumn=>$newcolumn); + $sqls = $this->_regen_table($tabname,null,$chgtype,$renames); + return $sqls; + } +} diff -urN mantisbt-1.2.19/library/adodb/drivers/adodb-pdo.inc.php mantisbt/library/adodb/drivers/adodb-pdo.inc.php --- mantisbt-1.2.19/library/adodb/drivers/adodb-pdo.inc.php Sun Jan 25 15:00:34 2015 +++ mantisbt/library/adodb/drivers/adodb-pdo.inc.php Sat Feb 07 09:00:00 2015 @@ -218,6 +218,13 @@ { return $this->_driver->MetaColumns($table,$normalize); } + function MetaIndexes($table, $primary = false, $owner = false) + { + if(method_exists($this->_driver,'MetaIndexes')) + return $this->_driver->MetaIndexes($table, $primary, $owner); + else + return parent::MetaIndexes($table, $primary, $owner); + } function InParameter(&$stmt,&$var,$name,$maxLen=4000,$type=false) { diff -urN mantisbt-1.2.19/library/adodb/drivers/adodb-pdo_sqlite.inc.php mantisbt/library/adodb/drivers/adodb-pdo_sqlite.inc.php --- mantisbt-1.2.19/library/adodb/drivers/adodb-pdo_sqlite.inc.php Sun Jan 25 15:00:34 2015 +++ mantisbt/library/adodb/drivers/adodb-pdo_sqlite.inc.php Sat Feb 07 09:00:00 2015 @@ -199,5 +199,61 @@ } return $ret; } + + + function __adodb_save_fetchmode() + { + global $ADODB_FETCH_MODE; + + $r = array('g_fm'=>$ADODB_FETCH_MODE); + $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC; + if($this->pdoDriver->fetchMode !== false) $r['fm'] = $this->pdoDriver->SetFetchMode(false); + + return $r; + } + function __adodb_restore_fetchmode($p_fm) + { + global $ADODB_FETCH_MODE; + + if(isset($p_fm['fm'])) $this->pdoDriver->SetFetchMode($p_fm['fm']); + $ADODB_FETCH_MODE = $p_fm['g_fm']; + } + + function MetaIndexes($table, $primary = false, $owner = false) + { + if($primary !== false) return false; + if($owner !== false) return false; + + $fm = $this->__adodb_save_fetchmode(); + + $r = false; + $res = $this->pdoDriver->Execute("PRAGMA index_list('{$table}')"); + if(is_object($res)) { + $excpfix='sqlite_autoindex_'; + $excpfix_len=strlen($excpfix); + + $r = array(); + while($rr = $res->FetchRow()){ + if(substr($rr['name'],0,$excpfix_len)==$excpfix)continue; + $r[$rr['name']]['unique'] = $rr['unique']-0 ? true: false; + $r[$rr['name']]['columns'] = array(); + } + $res->Close(); + + foreach($r as $idxname=>$idxinfo){ + $res = $this->pdoDriver->Execute("PRAGMA index_info('{$idxname}')"); + if(!is_object($res))continue; + + while($rr = $res->FetchRow()){ + $r[$idxname]['columns'][$rr['seqno']] = $rr['name']; + } + $res->Close(); + } + } + + $this->__adodb_restore_fetchmode($fm); + + return $r; + } } -?> \ No newline at end of file + diff -urN mantisbt-1.2.19/library/adodb/drivers/adodb-sqlitepdo.inc.php mantisbt/library/adodb/drivers/adodb-sqlitepdo.inc.php --- mantisbt-1.2.19/library/adodb/drivers/adodb-sqlitepdo.inc.php Thu Jan 01 09:00:00 1970 +++ mantisbt/library/adodb/drivers/adodb-sqlitepdo.inc.php Sat Feb 07 09:00:00 2015 @@ -0,0 +1,19 @@ +<?php +/** + @file adodb-sqlitepdo.inc.php + @author (c)2015 seclan. All rights reserved. + + Released under both BSD license and Lesser GPL library license. + Whenever there is any discrepancy between the two licenses, + the BSD license will take precedence. +*/ + +require_once('adodb-pdo.inc.php'); + +class ADODB_sqlitepdo extends ADODB_pdo { + function _connect($argDSN, $argUsername, $argPassword, $argDatabasename, $persist=false) + { + return parent::_connect('sqlite:'.$argDSN, $argUsername, $argPassword, null, $persist); + } +}; + mantis.sqlite13.diff (12,019 bytes)
diff -urN mantisbt-1.3.0-beta.1/admin/install.php mantisbt-1.3.0-beta.1.modified/admin/install.php --- mantisbt-1.3.0-beta.1/admin/install.php Sun Dec 07 14:58:14 2014 +++ mantisbt-1.3.0-beta.1.modified/admin/install.php Sat Feb 07 09:00:00 2015 @@ -325,7 +325,9 @@ print_test( 'Setting Database Hostname', '' !== $f_hostname, true, 'host name is blank' ); print_test( 'Setting Database Username', '' !== $f_db_username, true, 'database username is blank' ); print_test( 'Setting Database Password', '' !== $f_db_password, false, 'database password is blank' ); - print_test( 'Setting Database Name', '' !== $f_database_name || $f_db_type == 'oci8', true, 'database name is blank' ); + if( $f_db_type != 'sqlitepdo' ) { + print_test( 'Setting Database Name', '' !== $f_database_name || $f_db_type == 'oci8', true, 'database name is blank' ); + } if( $f_db_type == 'db2' ) { print_test( 'Setting Database Schema', !is_blank( $f_db_schema ), true, 'must have a schema name for AS400 in the form of DBNAME/SCHEMA' ); @@ -526,6 +528,7 @@ 'pgsql' => 'PostgreSQL', 'oci8' => 'Oracle', 'db2' => 'IBM DB2', + 'sqlitepdo' => 'SQLite', ); # mysql is deprecated as of PHP 5.5.0 if( version_compare( phpversion(), '5.5.0' ) >= 0 ) { @@ -875,6 +878,13 @@ while( ( $i <= $t_last_id ) && !$g_failed ) { if( !$f_log_queries ) { echo '<tr><td bgcolor="#ffffff">'; + } + + if($f_db_type == 'sqlitepdo'){ + # If the schema has an AUTOINCREMENT field, avoid to use PRIMARY fields in other places. + if($g_upgrade[$i][0]=='CreateTableSQL' && $g_upgrade[$i][1][0]==db_get_table( 'tag' )){ + $g_upgrade[$i][1][1] = preg_replace('!NOTNULL PRIMARY DEFAULT!', 'NOTNULL UNIQUE DEFAULT', $g_upgrade[$i][1][1]); + } } $t_sql = true; diff -urN mantisbt-1.3.0-beta.1/core/database_api.php mantisbt-1.3.0-beta.1.modified/core/database_api.php --- mantisbt-1.3.0-beta.1/core/database_api.php Sun Dec 07 14:58:14 2014 +++ mantisbt-1.3.0-beta.1.modified/core/database_api.php Sat Feb 07 09:00:00 2015 @@ -213,6 +213,9 @@ case 'odbc_mssql': $t_support = function_exists( 'odbc_connect' ); break; + case 'sqlitepdo': + $t_support = class_exists( 'PDO' ); + break; default: $t_support = false; } @@ -506,6 +509,10 @@ $t_array = $p_result->fields; $p_result->MoveNext(); return $t_array; + } else if($p_result->databaseType=='array'){ + $t_array = each($p_result->_array); + if($t_array===false)return false; + return $t_array[1]; } else { $t_row = $p_result->GetRowAssoc( ADODB_ASSOC_CASE_LOWER ); static $s_array_result; @@ -775,6 +782,9 @@ return pg_escape_string( $p_string ); case 'oci8': return $p_string; + case 'sqlitepdo': + $t_escaped = $g_db->_connectionID->quote($p_string); + return utf8_substr( $t_escaped, 1, utf8_strlen( $t_escaped ) - 2 ); default: error_parameters( 'db_type', $t_db_type ); trigger_error( ERROR_CONFIG_OPT_INVALID, ERROR ); diff -urN mantisbt-1.3.0-beta.1/library/adodb/adodb.inc.php mantisbt-1.3.0-beta.1.modified/library/adodb/adodb.inc.php --- mantisbt-1.3.0-beta.1/library/adodb/adodb.inc.php Sun Dec 07 14:58:16 2014 +++ mantisbt-1.3.0-beta.1.modified/library/adodb/adodb.inc.php Sat Feb 07 09:00:00 2015 @@ -4396,6 +4396,7 @@ case 'odbtp': if (strncmp('odbtp_',$drivername,6)==0) return substr($drivername,6); case 'odbc' : if (strncmp('odbc_',$drivername,5)==0) return substr($drivername,5); case 'ado' : if (strncmp('ado_',$drivername,4)==0) return substr($drivername,4); + case 'pdo': return 'sqlitepdo'; case 'native': break; default: return $provider; diff -urN mantisbt-1.3.0-beta.1/library/adodb/datadict/datadict-sqlitepdo.inc.php mantisbt-1.3.0-beta.1.modified/library/adodb/datadict/datadict-sqlitepdo.inc.php --- mantisbt-1.3.0-beta.1/library/adodb/datadict/datadict-sqlitepdo.inc.php Thu Jan 01 09:00:00 1970 +++ mantisbt-1.3.0-beta.1.modified/library/adodb/datadict/datadict-sqlitepdo.inc.php Sat Feb 07 09:00:00 2015 @@ -0,0 +1,141 @@ +<?php +/** + V5.10 10 Nov 2009 (c) 2000-2009 John Lim (jlim#natsoft.com). All rights reserved. + (c)2015 seclan. All rights reserved. + + Released under both BSD license and Lesser GPL library license. + Whenever there is any discrepancy between the two licenses, + the BSD license will take precedence. + + Set tabs to 4 for best viewing. + +*/ + +// security - hide paths +if (!defined('ADODB_DIR')) die(); + +class ADODB2_sqlitepdo extends ADODB_DataDict { + + var $databaseType = 'sqlitepdo'; + var $seqField = false; + + + function ActualType($meta) + { + switch($meta) { + case 'C': + case 'XL': + case 'X': return 'TEXT'; + + case 'C2': return 'TEXT'; + case 'X2': return 'TEXT'; + + case 'B': return 'BLOB'; + + case 'TS': + case 'D': + case 'T': return 'TEXT'; + + case 'L': + case 'I': + case 'I1': + case 'I2': + case 'I4': + case 'I8': return 'INTEGER'; + + case 'F': return 'REAL'; + case 'N': return 'NUMERIC'; + default: + return $meta; + } + } + + function _regen_table($tabname, $dropnames = null, $chgtype = null, $renames=null) + { + if($dropnames===null) $dropnames=array(); + if($chgtype===null) $chgtype=array(); + if($renames===null) $renames=array(); + + $mcs = $this->MetaColumns($tabname); + $newcols = array(); + $cpycols = array(); + $newpkeys = array(); + + foreach($mcs as $fo) { + if(isset($dropnames[$fo->name]))continue; + // + if($chgtype[$fo->name]){ + $sqln = $chgtype[$fo->name]; + } else { + $sqln = $fo->type; + if($fo->max_length) $sqln .= "({$fo->max_length})"; + if($fo->default_value!==NULL) $sqln .= " DEFAULT {$fo->default_value}"; + if($fo->not_null) $sqln .= ' NOT NULL'; + //ignore scale(sqlite:always 0) $fo->scale + } + if($renames[$fo->name]){ + $rname = $renames[$fo->name]; + $newcols[] = $rname.' '.$sqln; + $cpycols[] = "{$fo->name} AS {$rname}"; + if($fo->primary_key) $newpkeys[] = $rname; + } else { + $newcols[] = $fo->name.' '.$sqln; + $cpycols[] = $fo->name; + if($fo->primary_key) $newpkeys[] = $fo->name; + } + } + + $newcolstr = implode(', ',$newcols); + if(count($newpkeys)) $newcolstr.=', PRIMARY KEY('.implode(', ', $newpkeys).')'; + $cpycolstr = implode(', ',$cpycols); + + $tempname = $tabname.'_tmp'; + $aSql[] = 'BEGIN TRANSACTION'; // we use a transaction, to make sure not to loose the content of the table + $aSql[] = "CREATE TABLE {$tempname}({$newcolstr})"; + $aSql[] = "INSERT INTO {$tempname} SELECT {$cpycolstr} FROM {$tabname}"; + $aSql[] = "DROP TABLE {$tabname}"; + $aSql[] = "ALTER TABLE {$tempname} RENAME TO {$tabname}"; + // recreate the indexes, if they not contain one of the droped columns + $dropflds = array_flip($dropnames); + $midx = $this->MetaIndexes($tabname); + if(is_array($midx) && count($midx))foreach($midx as $idx_name => $idx_data) { + if(!count($dropflds) || !count(array_intersect($dropflds,$idx_data['columns']))){ + $aSql = array_merge($aSql,$this->CreateIndexSQL($idx_name,$tabname,$idx_data['columns'], + $idx_data['unique'] ? array('UNIQUE') : False)); + } + } + $aSql[] = 'COMMIT'; + return $aSql; + } + + function AlterColumnSQL($tabname, $flds) + { + $t_newflds = $this->_GenFields($flds,true); + $t_flds = preg_split('![ \t]+!', current($t_newflds[0]), 2); + $chgtype = array($t_flds[0]=>$t_flds[1]); + + $sqls = $this->_regen_table($tabname,null,$chgtype); + return $sqls; + } + + function DropColumnSQL($tabname, $flds) + { + $dropnames = array_flip(explode(',',$flds)); + $sqls = $this->_regen_table($tabname,$dropnames); + return $sqls; + } + + function RenameColumnSQL($tabname,$oldcolumn,$newcolumn,$flds='') + { + if($flds!=''){ + $t_newflds = $this->_GenFields($flds,true); + $t_flds = preg_split('![ \t]+!', current($t_newflds[0]), 2); + $chgtype = array($t_flds[0]=>$t_flds[1]); + } else { + $chgtype = null; + } + $renames=array($oldcolumn=>$newcolumn); + $sqls = $this->_regen_table($tabname,null,$chgtype,$renames); + return $sqls; + } +} diff -urN mantisbt-1.3.0-beta.1/library/adodb/drivers/adodb-pdo.inc.php mantisbt-1.3.0-beta.1.modified/library/adodb/drivers/adodb-pdo.inc.php --- mantisbt-1.3.0-beta.1/library/adodb/drivers/adodb-pdo.inc.php Sun Dec 07 14:58:16 2014 +++ mantisbt-1.3.0-beta.1.modified/library/adodb/drivers/adodb-pdo.inc.php Sat Feb 07 09:00:00 2015 @@ -220,6 +220,14 @@ return $this->_driver->MetaColumns($table,$normalize); } + function MetaIndexes($table, $primary = false, $owner = false) + { + if(method_exists($this->_driver,'MetaIndexes')) + return $this->_driver->MetaIndexes($table, $primary, $owner); + else + return parent::MetaIndexes($table, $primary, $owner); + } + function InParameter(&$stmt,&$var,$name,$maxLen=4000,$type=false) { $obj = $stmt[1]; diff -urN mantisbt-1.3.0-beta.1/library/adodb/drivers/adodb-pdo_sqlite.inc.php mantisbt-1.3.0-beta.1.modified/library/adodb/drivers/adodb-pdo_sqlite.inc.php --- mantisbt-1.3.0-beta.1/library/adodb/drivers/adodb-pdo_sqlite.inc.php Sun Dec 07 14:58:16 2014 +++ mantisbt-1.3.0-beta.1.modified/library/adodb/drivers/adodb-pdo_sqlite.inc.php Sat Feb 07 09:00:00 2015 @@ -199,4 +199,60 @@ } return $ret; } + + function __adodb_save_fetchmode() + { + global $ADODB_FETCH_MODE; + + $r = array('g_fm'=>$ADODB_FETCH_MODE); + $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC; + if($this->pdoDriver->fetchMode !== false) $r['fm'] = $this->pdoDriver->SetFetchMode(false); + + return $r; + } + function __adodb_restore_fetchmode($p_fm) + { + global $ADODB_FETCH_MODE; + + if(isset($p_fm['fm'])) $this->pdoDriver->SetFetchMode($p_fm['fm']); + $ADODB_FETCH_MODE = $p_fm['g_fm']; + } + + function MetaIndexes($table, $primary = false, $owner = false) + { + if($primary !== false) return false; + if($owner !== false) return false; + + $fm = $this->__adodb_save_fetchmode(); + + $r = false; + $res = $this->pdoDriver->Execute("PRAGMA index_list('{$table}')"); + if(is_object($res)) { + $excpfix='sqlite_autoindex_'; + $excpfix_len=strlen($excpfix); + + $r = array(); + while($rr = $res->FetchRow()){ + if(substr($rr['name'],0,$excpfix_len)==$excpfix)continue; + $r[$rr['name']]['unique'] = $rr['unique']-0 ? true: false; + $r[$rr['name']]['columns'] = array(); + } + $res->Close(); + + foreach($r as $idxname=>$idxinfo){ + $res = $this->pdoDriver->Execute("PRAGMA index_info('{$idxname}')"); + if(!is_object($res))continue; + + while($rr = $res->FetchRow()){ + $r[$idxname]['columns'][$rr['seqno']] = $rr['name']; + } + $res->Close(); + } + } + + $this->__adodb_restore_fetchmode($fm); + + return $r; + } + } diff -urN mantisbt-1.3.0-beta.1/library/adodb/drivers/adodb-sqlitepdo.inc.php mantisbt-1.3.0-beta.1.modified/library/adodb/drivers/adodb-sqlitepdo.inc.php --- mantisbt-1.3.0-beta.1/library/adodb/drivers/adodb-sqlitepdo.inc.php Thu Jan 01 09:00:00 1970 +++ mantisbt-1.3.0-beta.1.modified/library/adodb/drivers/adodb-sqlitepdo.inc.php Sat Feb 07 09:00:00 2015 @@ -0,0 +1,19 @@ +<?php +/** + @file adodb-sqlitepdo.inc.php + @author (c)2015 seclan. All rights reserved. + + Released under both BSD license and Lesser GPL library license. + Whenever there is any discrepancy between the two licenses, + the BSD license will take precedence. +*/ + +require_once('adodb-pdo.inc.php'); + +class ADODB_sqlitepdo extends ADODB_pdo { + function _connect($argDSN, $argUsername, $argPassword, $argDatabasename, $persist=false) + { + return parent::_connect('sqlite:'.$argDSN, $argUsername, $argPassword, null, $persist); + } +}; + | ||||
duplicate of | 0010573 | acknowledged | support SQLite |
@seclan, Thanks for your contribution. Why do we need changes in adodb? Is this because we are not running latest version or these are gaps in ADODB itself? I would rather if such changes go through ADODB upstream. |
|
@seclan / vboctor - Thank you for this patch! I've successfully applied the patch on 1.2.19, however I'm not sure how to proceed with creating the SQLite schema. After selecting 'sqlite' in the dropdown, and entering a path/to/sqlitefile in 'hostname' in the install.php, and pressing 'install/upgrade database', I get the following errors: Setting Database Password POSSIBLE PROBLEM Attempting to connect to database as admin BAD Do I need to create the Schema ahead of time, and can you share the SQL code to do so? Thanks for your help! |
|
Based on the work done before I have tried to apply the patch to the current 1.3.16 version. mantis-1.3.16-sqlite.diff (11,940 bytes)
diff -ruN --strip-trailing-cr mantisbt-1.3.16/admin/install.php mantisbt-1.3.16-patched/admin/install.php --- mantisbt-1.3.16/admin/install.php 2018-09-04 07:09:55.000000000 +0200 +++ mantisbt-1.3.16-patched/admin/install.php 2018-10-24 16:52:28.000000000 +0200 @@ -378,7 +378,9 @@ print_test( 'Setting Database Hostname', '' !== $f_hostname, true, 'host name is blank' ); print_test( 'Setting Database Username', '' !== $f_db_username, true, 'database username is blank' ); print_test( 'Setting Database Password', '' !== $f_db_password, false, 'database password is blank' ); - print_test( 'Setting Database Name', '' !== $f_database_name || $f_db_type == 'oci8', true, 'database name is blank' ); + if( $f_db_type != 'sqlitepdo' ) { + print_test( 'Setting Database Name', '' !== $f_database_name || $f_db_type == 'oci8', true, 'database name is blank' ); + } if( $f_db_type == 'db2' ) { print_test( 'Setting Database Schema', !is_blank( $f_db_schema ), true, 'must have a schema name for AS400 in the form of DBNAME/SCHEMA' ); @@ -598,6 +600,7 @@ 'mssqlnative' => 'Microsoft SQL Server Native Driver', 'pgsql' => 'PostgreSQL', 'oci8' => 'Oracle', + 'sqlitepdo' => 'SQLite', # Hidden for new installs per #17336 # 'db2' => 'IBM DB2 (unsupported)', ); @@ -972,6 +975,13 @@ echo '<tr><td bgcolor="#ffffff">'; } + if($f_db_type == 'sqlitepdo'){ + # If the schema has an AUTOINCREMENT field, avoid to use PRIMARY fields in other places. + if($g_upgrade[$i][0]=='CreateTableSQL' && $g_upgrade[$i][1][0]==db_get_table( 'tag' )){ + $g_upgrade[$i][1][1] = preg_replace('!NOTNULL PRIMARY DEFAULT!', 'NOTNULL UNIQUE DEFAULT', $g_upgrade[$i][1][1]); + } + } + $t_sql = true; $t_target = $g_upgrade[$i][1][0]; diff -ruN --strip-trailing-cr mantisbt-1.3.16/core/database_api.php mantisbt-1.3.16-patched/core/database_api.php --- mantisbt-1.3.16/core/database_api.php 2018-09-04 07:09:55.000000000 +0200 +++ mantisbt-1.3.16-patched/core/database_api.php 2018-10-24 23:28:24.000000000 +0200 @@ -215,6 +215,9 @@ case 'odbc_mssql': $t_support = function_exists( 'odbc_connect' ); break; + case 'sqlitepdo': + $t_support = class_exists( 'PDO' ); + break; default: $t_support = false; } @@ -344,7 +347,7 @@ static $s_check_params; if( $s_check_params === null ) { - $s_check_params = ( db_is_pgsql() || $t_db_type == 'odbc_mssql' || $t_db_type == 'mssqlnative' ); + $s_check_params = ( db_is_pgsql() || $t_db_type == 'odbc_mssql' || $t_db_type == 'mssqlnative' || $t_db_type == 'sqlitepdo' ); } $t_start = microtime( true ); @@ -361,7 +364,7 @@ for( $i = 0;$i < $t_params;$i++ ) { if( $p_arr_parms[$i] === false ) { $p_arr_parms[$i] = 0; - } elseif( $p_arr_parms[$i] === true && $t_db_type == 'mssqlnative' ) { + } elseif( $p_arr_parms[$i] === true && ($t_db_type == 'mssqlnative' || $t_db_type == 'sqlitepdo') ) { $p_arr_parms[$i] = 1; } } @@ -753,6 +756,9 @@ return pg_escape_string( $p_string ); case 'oci8': return $p_string; + case 'sqlitepdo': + $t_escaped = $g_db->_connectionID->quote($p_string); + return utf8_substr( $t_escaped, 1, utf8_strlen( $t_escaped ) - 2 ); default: error_parameters( 'db_type', $t_db_type ); trigger_error( ERROR_CONFIG_OPT_INVALID, ERROR ); diff -ruN --strip-trailing-cr mantisbt-1.3.16/library/adodb/adodb.inc.php mantisbt-1.3.16-patched/library/adodb/adodb.inc.php --- mantisbt-1.3.16/library/adodb/adodb.inc.php 2018-09-04 07:09:57.000000000 +0200 +++ mantisbt-1.3.16-patched/library/adodb/adodb.inc.php 2018-10-24 16:51:42.000000000 +0200 @@ -4856,6 +4856,8 @@ if (strncmp('ado_',$drivername,4)==0) { return substr($drivername,4); } + case 'pdo': + return 'sqlitepdo'; case 'native': break; default: diff -ruN --strip-trailing-cr mantisbt-1.3.16/library/adodb/datadict/datadict-sqlitepdo.inc.php mantisbt-1.3.16-patched/library/adodb/datadict/datadict-sqlitepdo.inc.php --- mantisbt-1.3.16/library/adodb/datadict/datadict-sqlitepdo.inc.php 1970-01-01 01:00:00.000000000 +0100 +++ mantisbt-1.3.16-patched/library/adodb/datadict/datadict-sqlitepdo.inc.php 2018-10-24 16:50:50.000000000 +0200 @@ -0,0 +1,141 @@ +<?php +/** + V5.10 10 Nov 2009 (c) 2000-2009 John Lim (jlim#natsoft.com). All rights reserved. + (c)2015 seclan. All rights reserved. + + Released under both BSD license and Lesser GPL library license. + Whenever there is any discrepancy between the two licenses, + the BSD license will take precedence. + + Set tabs to 4 for best viewing. + +*/ + +// security - hide paths +if (!defined('ADODB_DIR')) die(); + +class ADODB2_sqlitepdo extends ADODB_DataDict { + + var $databaseType = 'sqlitepdo'; + var $seqField = false; + + + function ActualType($meta) + { + switch($meta) { + case 'C': + case 'XL': + case 'X': return 'TEXT'; + + case 'C2': return 'TEXT'; + case 'X2': return 'TEXT'; + + case 'B': return 'BLOB'; + + case 'TS': + case 'D': + case 'T': return 'TEXT'; + + case 'L': + case 'I': + case 'I1': + case 'I2': + case 'I4': + case 'I8': return 'INTEGER'; + + case 'F': return 'REAL'; + case 'N': return 'NUMERIC'; + default: + return $meta; + } + } + + function _regen_table($tabname, $dropnames = null, $chgtype = null, $renames=null) + { + if($dropnames===null) $dropnames=array(); + if($chgtype===null) $chgtype=array(); + if($renames===null) $renames=array(); + + $mcs = $this->MetaColumns($tabname); + $newcols = array(); + $cpycols = array(); + $newpkeys = array(); + + foreach($mcs as $fo) { + if(isset($dropnames[$fo->name]))continue; + // + if($chgtype[$fo->name]){ + $sqln = $chgtype[$fo->name]; + } else { + $sqln = $fo->type; + if($fo->max_length) $sqln .= "({$fo->max_length})"; + if($fo->default_value!==NULL) $sqln .= " DEFAULT {$fo->default_value}"; + if($fo->not_null) $sqln .= ' NOT NULL'; + //ignore scale(sqlite:always 0) $fo->scale + } + if($renames[$fo->name]){ + $rname = $renames[$fo->name]; + $newcols[] = $rname.' '.$sqln; + $cpycols[] = "{$fo->name} AS {$rname}"; + if($fo->primary_key) $newpkeys[] = $rname; + } else { + $newcols[] = $fo->name.' '.$sqln; + $cpycols[] = $fo->name; + if($fo->primary_key) $newpkeys[] = $fo->name; + } + } + + $newcolstr = implode(', ',$newcols); + if(count($newpkeys)) $newcolstr.=', PRIMARY KEY('.implode(', ', $newpkeys).')'; + $cpycolstr = implode(', ',$cpycols); + + $tempname = $tabname.'_tmp'; + $aSql[] = 'BEGIN TRANSACTION'; // we use a transaction, to make sure not to loose the content of the table + $aSql[] = "CREATE TABLE {$tempname}({$newcolstr})"; + $aSql[] = "INSERT INTO {$tempname} SELECT {$cpycolstr} FROM {$tabname}"; + $aSql[] = "DROP TABLE {$tabname}"; + $aSql[] = "ALTER TABLE {$tempname} RENAME TO {$tabname}"; + // recreate the indexes, if they not contain one of the droped columns + $dropflds = array_flip($dropnames); + $midx = $this->MetaIndexes($tabname); + if(is_array($midx) && count($midx))foreach($midx as $idx_name => $idx_data) { + if(!count($dropflds) || !count(array_intersect($dropflds,$idx_data['columns']))){ + $aSql = array_merge($aSql,$this->CreateIndexSQL($idx_name,$tabname,$idx_data['columns'], + $idx_data['unique'] ? array('UNIQUE') : False)); + } + } + $aSql[] = 'COMMIT'; + return $aSql; + } + + function AlterColumnSQL($tabname, $flds, $tableflds='', $tableoptions='') + { + $t_newflds = $this->_GenFields($flds,true); + $t_flds = preg_split('![ \t]+!', current($t_newflds[0]), 2); + $chgtype = array($t_flds[0]=>$t_flds[1]); + + $sqls = $this->_regen_table($tabname,null,$chgtype); + return $sqls; + } + + function DropColumnSQL($tabname, $flds, $tableflds='', $tableoptions='') + { + $dropnames = array_flip(explode(',',$flds)); + $sqls = $this->_regen_table($tabname,$dropnames); + return $sqls; + } + + function RenameColumnSQL($tabname,$oldcolumn,$newcolumn,$flds='') + { + if($flds!=''){ + $t_newflds = $this->_GenFields($flds,true); + $t_flds = preg_split('![ \t]+!', current($t_newflds[0]), 2); + $chgtype = array($t_flds[0]=>$t_flds[1]); + } else { + $chgtype = null; + } + $renames=array($oldcolumn=>$newcolumn); + $sqls = $this->_regen_table($tabname,null,$chgtype,$renames); + return $sqls; + } +} diff -ruN --strip-trailing-cr mantisbt-1.3.16/library/adodb/drivers/adodb-pdo.inc.php mantisbt-1.3.16-patched/library/adodb/drivers/adodb-pdo.inc.php --- mantisbt-1.3.16/library/adodb/drivers/adodb-pdo.inc.php 2018-09-04 07:09:57.000000000 +0200 +++ mantisbt-1.3.16-patched/library/adodb/drivers/adodb-pdo.inc.php 2018-10-24 16:51:24.000000000 +0200 @@ -251,6 +251,14 @@ return $this->_driver->MetaColumns($table,$normalize); } + function MetaIndexes($table, $primary = false, $owner = false) + { + if(method_exists($this->_driver,'MetaIndexes')) + return $this->_driver->MetaIndexes($table, $primary, $owner); + else + return parent::MetaIndexes($table, $primary, $owner); + } + function InParameter(&$stmt,&$var,$name,$maxLen=4000,$type=false) { $obj = $stmt[1]; diff -ruN --strip-trailing-cr mantisbt-1.3.16/library/adodb/drivers/adodb-pdo_sqlite.inc.php mantisbt-1.3.16-patched/library/adodb/drivers/adodb-pdo_sqlite.inc.php --- mantisbt-1.3.16/library/adodb/drivers/adodb-pdo_sqlite.inc.php 2018-09-04 07:09:57.000000000 +0200 +++ mantisbt-1.3.16-patched/library/adodb/drivers/adodb-pdo_sqlite.inc.php 2018-10-24 16:51:10.000000000 +0200 @@ -201,4 +201,60 @@ } return $ret; } + + function __adodb_save_fetchmode() + { + global $ADODB_FETCH_MODE; + + $r = array('g_fm'=>$ADODB_FETCH_MODE); + $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC; + if($this->pdoDriver->fetchMode !== false) $r['fm'] = $this->pdoDriver->SetFetchMode(false); + + return $r; + } + + function __adodb_restore_fetchmode($p_fm) + { + global $ADODB_FETCH_MODE; + + if(isset($p_fm['fm'])) $this->pdoDriver->SetFetchMode($p_fm['fm']); + $ADODB_FETCH_MODE = $p_fm['g_fm']; + } + + function MetaIndexes($table, $primary = false, $owner = false) + { + if($primary !== false) return false; + if($owner !== false) return false; + + $fm = $this->__adodb_save_fetchmode(); + + $r = false; + $res = $this->pdoDriver->Execute("PRAGMA index_list('{$table}')"); + if(is_object($res)) { + $excpfix='sqlite_autoindex_'; + $excpfix_len=strlen($excpfix); + + $r = array(); + while($rr = $res->FetchRow()){ + if(substr($rr['name'],0,$excpfix_len)==$excpfix)continue; + $r[$rr['name']]['unique'] = $rr['unique']-0 ? true: false; + $r[$rr['name']]['columns'] = array(); + } + $res->Close(); + + foreach($r as $idxname=>$idxinfo){ + $res = $this->pdoDriver->Execute("PRAGMA index_info('{$idxname}')"); + if(!is_object($res))continue; + + while($rr = $res->FetchRow()){ + $r[$idxname]['columns'][$rr['seqno']] = $rr['name']; + } + $res->Close(); + } + } + + $this->__adodb_restore_fetchmode($fm); + + return $r; + } } diff -ruN --strip-trailing-cr mantisbt-1.3.16/library/adodb/drivers/adodb-sqlitepdo.inc.php mantisbt-1.3.16-patched/library/adodb/drivers/adodb-sqlitepdo.inc.php --- mantisbt-1.3.16/library/adodb/drivers/adodb-sqlitepdo.inc.php 1970-01-01 01:00:00.000000000 +0100 +++ mantisbt-1.3.16-patched/library/adodb/drivers/adodb-sqlitepdo.inc.php 2018-10-24 16:50:54.000000000 +0200 @@ -0,0 +1,19 @@ +<?php +/** + @file adodb-sqlitepdo.inc.php + @author (c)2015 seclan. All rights reserved. + + Released under both BSD license and Lesser GPL library license. + Whenever there is any discrepancy between the two licenses, + the BSD license will take precedence. +*/ + +require_once('adodb-pdo.inc.php'); + +class ADODB_sqlitepdo extends ADODB_pdo { + function _connect($argDSN, $argUsername, $argPassword, $argDatabasename, $persist=false) + { + return parent::_connect('sqlite:'.$argDSN, $argUsername, $argPassword, null, $persist); + } +}; + |
|
To those having trouble, note that the process accessing the database (php-fpm or similar) does not only need write-access to the database file itself, but also requires permission to create and write files (journal or wal file) in the directory containing the database. |
|
Closing this as duplicate of 0010573. |
|