From 814afc69a438412c8b21b2ebd955ea09cc4c5ed2 Mon Sep 17 00:00:00 2001 From: garvinhicking Date: Fri, 18 May 2007 07:59:14 +0000 Subject: [PATCH] Add sqlite3 support, by geekmug --- docs/NEWS | 3 + include/admin/category.inc.php | 2 +- include/db/db.inc.php | 1 + include/db/sqlite3.inc.php | 395 ++++++++++++++++++++++++++++ include/functions_config.inc.php | 3 + include/functions_entries.inc.php | 6 +- include/functions_installer.inc.php | 2 +- include/functions_upgrader.inc.php | 1 + 8 files changed, 408 insertions(+), 5 deletions(-) create mode 100644 include/db/sqlite3.inc.php diff --git a/docs/NEWS b/docs/NEWS index e90bfea..d09ca2a 100644 --- a/docs/NEWS +++ b/docs/NEWS @@ -3,6 +3,9 @@ Version 1.2 () ------------------------------------------------------------------------ + * Add support for sqlite3 (http://php-sqlite3.sourceforge.net/), by + geekmug + * Change database types for IP addresses to varchar(64) to support IPv6 (garvinhicking) diff --git a/include/admin/category.inc.php b/include/admin/category.inc.php index e9c25a5..5ed49b9 100644 --- a/include/admin/category.inc.php +++ b/include/admin/category.inc.php @@ -73,7 +73,7 @@ if ($serendipity['GET']['adminAction'] == 'doDelete' && serendipity_checkFormTok $remaining_cat = (int)$serendipity['POST']['cat']['remaining_catid']; $category_ranges = serendipity_fetchCategoryRange((int)$serendipity['GET']['cid']); $category_range = implode(' AND ', $category_ranges); - if ($serendipity['dbType'] == 'postgres' || $serendipity['dbType'] == 'sqlite') { + if ($serendipity['dbType'] == 'postgres' || $serendipity['dbType'] == 'sqlite' || $serendipity['dbType'] == 'sqlite3') { $query = "UPDATE {$serendipity['dbPrefix']}entrycat SET categoryid={$remaining_cat} WHERE entryid IN ( diff --git a/include/db/db.inc.php b/include/db/db.inc.php index afa8791..91765e3 100644 --- a/include/db/db.inc.php +++ b/include/db/db.inc.php @@ -123,6 +123,7 @@ function serendipity_db_get_interval($val, $ival = 900) { switch($serendipity['dbType']) { case 'sqlite': + case 'sqlite3': $interval = $ival; $ts = time(); break; diff --git a/include/db/sqlite3.inc.php b/include/db/sqlite3.inc.php new file mode 100644 index 0000000..934e1f0 --- /dev/null +++ b/include/db/sqlite3.inc.php @@ -0,0 +1,395 @@ + $v) { + // TODO: If a query of the format 'SELECT a.id, b.text FROM table' is used, + // the sqlite extension will give us key indizes 'a.id' and 'b.text' + // instead of just 'id' and 'text' like in mysql/postgresql extension. + // To fix that, we use a preg-regex; but that is quite performance costy. + // Either we always need to use 'SELECT a.id AS id, b.text AS text' in query, + // or the sqlite extension may get fixed. :-) + $row[preg_replace('@^.+\.(.*)@', '\1', $i)] = str_replace($search, $replace, $v); + } + + if ($type == SQLITE3_NUM) + $frow = array(); + else + $frow = $row; + + if ($type != SQLITE3_ASSOC) { + $i = 0; + foreach($row as $k => $v) { + $frow[$i] = $v; + $i++; + } + } + + return $frow; +} + +/** + * Assemble and return SQL condition for a "IN (...)" clause + * + * @access public + * @param string table column name + * @param array referenced array of values to search for in the "IN (...)" clause + * @param string condition of how to associate the different input values of the $search_ids parameter + * @return string resulting SQL string + */ +function serendipity_db_in_sql($col, &$search_ids, $type = ' OR ') { + $sql = array(); + if (!is_array($search_ids)) { + return false; + } + + foreach($search_ids AS $id) { + $sql[] = $col . ' = ' . $id; + } + + $cond = '(' . implode($type, $sql) . ')'; + return $cond; +} + +/** + * Perform a DB Layer SQL query. + * + * This function returns values dependin on the input parameters and the result of the query. + * It can return: + * false if there was an error, + * true if the query succeeded but did not generate any rows + * array of field values if it returned a single row and $single is true + * array of array of field values if it returned row(s) [stacked array] + * + * @access public + * @param string SQL query to execute + * @param boolean Toggle whether the expected result is a single row (TRUE) or multiple rows (FALSE). This affects whether the returned array is 1 or 2 dimensional! + * @param string Result type of the array indexing. Can be one of "assoc" (associative), "num" (numerical), "both" (numerical and associative, default) + * @param boolean If true, errors will be reported. If false, errors will be ignored. + * @param string A possible array key name, so that you can control the multi-dimensional mapping of an array by the key column + * @param string A possible array field name, so that you can control the multi-dimensional mapping of an array by the key column and the field value. + * @param boolean If true, the executed SQL error is known to fail, and should be disregarded (errors can be ignroed on DUPLICATE INDEX queries and the likes) + * @return mixed Returns the result of the SQL query, depending on the input parameters + */ +function &serendipity_db_query($sql, $single = false, $result_type = "both", $reportErr = true, $assocKey = false, $assocVal = false, $expectError = false) +{ + global $serendipity; + static $type_map = array( + 'assoc' => SQLITE3_ASSOC, + 'num' => SQLITE3_NUM, + 'both' => SQLITE3_BOTH, + 'true' => true, + 'false' => false + ); + + static $debug = false; + + if ($debug) { + // Open file and write directly. In case of crashes, the pointer needs to be killed. + $fp = @fopen('sqlite.log', 'a'); + fwrite($fp, '[' . date('d.m.Y H:i') . '] SQLITE QUERY: ' . $sql . "\n\n"); + fclose($fp); + } + + if ($reportErr && !$expectError) { + $res = sqlite3_query($serendipity['dbConn'], $sql); + } else { + $res = @sqlite3_query($serendipity['dbConn'], $sql); + } + + if (!$res) { + if (!$expectError && !$serendipity['production']) { + var_dump($res); + var_dump($sql); + $msg = "problem with query"; + return $msg; + } + if ($debug) { + $fp = @fopen('sqlite.log', 'a'); + fwrite($fp, '[' . date('d.m.Y H:i') . '] [ERROR] ' . "\n\n"); + fclose($fp); + } + + return $type_map['false']; + } + + if ($res === true) { + return $type_map['true']; + } + + $rows = array(); + + while (($row = serendipity_db_sqlite_fetch_array($res, $type_map[$result_type]))) { + if (!empty($assocKey)) { + // You can fetch a key-associated array via the two function parameters assocKey and assocVal + if (empty($assocVal)) { + $rows[$row[$assocKey]] = $row; + } else { + $rows[$row[$assocKey]] = $row[$assocVal]; + } + } else { + $rows[] = $row; + } + } + + if ($debug) { + $fp = @fopen('sqlite.log', 'a'); + fwrite($fp, '[' . date('d.m.Y H:i') . '] SQLITE RESULT: ' . print_r($rows, true). "\n\n"); + fclose($fp); + } + + if ($single && count($rows) == 1) { + return $rows[0]; + } + + if (count($rows) == 0) { + if ($single) + return $type_map['false']; + return $type_map['true']; + } + + return $rows; +} + +/** + * Try to connect to the configured Database (during installation) + * + * @access public + * @param array input configuration array, holding the connection info + * @param array referenced array which holds the errors that might be encountered + * @return boolean return true on success, false on error + */ +function serendipity_db_probe($hash, &$errs) +{ + global $serendipity; + + $dbName = (isset($hash['sqlitedbName']) ? $hash['sqlitedbName'] : $hash['dbName']); + + if (!function_exists('sqlite3_open')) { + $errs[] = 'SQLite extension not installed. Run "pear install sqlite" on your webserver or contact your systems administrator regarding this problem.'; + return false; + } + + if (defined('S9Y_DATA_PATH')) { + // Shared installations! + $dbfile = S9Y_DATA_PATH . $dbName . '.db'; + } else { + $dbfile = $serendipity['serendipityPath'] . $dbName . '.db'; + } + + $serendipity['dbConn'] = sqlite3_open($dbfile); + + if ($serendipity['dbConn']) { + return true; + } + + $errs[] = "Unable to open \"$dbfile\" - check permissions (directory needs to be writeable for webserver)!"; + return false; +} + +/** + * Prepares a Serendipty query input to fully valid SQL. Replaces certain "template" variables. + * + * @access public + * @param string SQL query with template variables to convert + * @return ressource SQL ressource handle of the executed query + */ +function serendipity_db_schema_import($query) +{ + static $search = array('{AUTOINCREMENT}', '{PRIMARY}', '{UNSIGNED}', '{FULLTEXT}', '{BOOLEAN}', '{UTF_8}'); + static $replace = array('INTEGER', 'PRIMARY KEY', '', '', 'BOOLEAN NOT NULL', ''); + + if (stristr($query, '{FULLTEXT_MYSQL}')) { + return true; + } + + $query = trim(str_replace($search, $replace, $query)); + if ($query{0} == '@') { + // Errors are expected to happen (like duplicate index creation) + return serendipity_db_query(substr($query, 1), false, 'both', false, false, false, true); + } else { + return serendipity_db_query($query); + } +} + +/** + * Returns the option to a LIMIT SQL statement, because it varies accross DB systems + * + * @access public + * @param int Number of the first row to return data from + * @param int Number of rows to return + * @return string SQL string to pass to a LIMIT statement + */ +function serendipity_db_limit($start, $offset) { + return $start . ', ' . $offset; +} + +/** + * Return a LIMIT SQL option to the DB Layer as a full LIMIT statement + * + * @access public + * @param SQL string of a LIMIT option + * @return SQL string containing a full LIMIT statement + */ +function serendipity_db_limit_sql($limitstring) { + return ' LIMIT ' . $limitstring; +} + +/** + * Returns the SQL code used for concatenating strings + * + * @access public + * @param string Input string/column to concatenate + * @return string SQL parameter + */ +function serendipity_db_concat($string) { + return 'concat(' . $string . ')'; +} + +/* vim: set sts=4 ts=4 expandtab : */ diff --git a/include/functions_config.inc.php b/include/functions_config.inc.php index abbd558..0397215 100644 --- a/include/functions_config.inc.php +++ b/include/functions_config.inc.php @@ -784,6 +784,9 @@ function serendipity_probeInstallation($item) { if (extension_loaded('sqlite')) { $res['sqlite'] = 'SQLite'; } + if (extension_loaded('SQLITE3')) { + $res['sqlite3'] = 'SQLite3'; + } break; case 'rewrite' : diff --git a/include/functions_entries.inc.php b/include/functions_entries.inc.php index e1ccb9c..f5d350e 100644 --- a/include/functions_entries.inc.php +++ b/include/functions_entries.inc.php @@ -722,7 +722,7 @@ function &serendipity_searchEntries($term, $limit = '') { $group = ''; $distinct = 'DISTINCT'; $cond['find_part'] = "(title ILIKE '%$term%' OR body ILIKE '%$term%' OR extended ILIKE '%$term%')"; - } elseif ($serendipity['dbType'] == 'sqlite') { + } elseif ($serendipity['dbType'] == 'sqlite' || $serendipity['dbType'] == 'sqlite3') { // Very extensive SQLite search. There currently seems no other way to perform fulltext search in SQLite // But it's better than no search at all :-D $group = 'GROUP BY e.id'; @@ -846,7 +846,7 @@ function serendipity_getTotalEntries() { global $serendipity; // The unique query condition was built previously in serendipity_fetchEntries() - if ($serendipity['dbType'] == 'sqlite') { + if ($serendipity['dbType'] == 'sqlite' || $serendipity['dbType'] == 'sqlite3') { $querystring = "SELECT count(e.id) {$serendipity['fullCountQuery']} GROUP BY e.id"; } else { $querystring = "SELECT count(distinct e.id) {$serendipity['fullCountQuery']}"; @@ -855,7 +855,7 @@ function serendipity_getTotalEntries() { $query =& serendipity_db_query($querystring); if (is_array($query) && isset($query[0])) { - if ($serendipity['dbType'] == 'sqlite') { + if ($serendipity['dbType'] == 'sqlite' || $serendipity['dbType'] == 'sqlite3') { return count($query); } else { return $query[0][0]; diff --git a/include/functions_installer.inc.php b/include/functions_installer.inc.php index 897c345..5844afe 100644 --- a/include/functions_installer.inc.php +++ b/include/functions_installer.inc.php @@ -727,7 +727,7 @@ function serendipity_checkInstallation() { $errs[] = sprintf(CANT_EXECUTE_BINARY, 'convert imagemagick'); } - if ($_POST['dbType'] == 'sqlite') { + if ($_POST['dbType'] == 'sqlite' || $_POST['dbType'] == 'sqlite3') { // We don't want that our SQLite db file can be guessed from other applications on a server // and have access to our's. So we randomize the SQLite dbname. $_POST['sqlitedbName'] = $_POST['dbName'] . '_' . md5(time()); diff --git a/include/functions_upgrader.inc.php b/include/functions_upgrader.inc.php index 34def66..e959261 100644 --- a/include/functions_upgrader.inc.php +++ b/include/functions_upgrader.inc.php @@ -48,6 +48,7 @@ $obsolete_files = array( 'serendipity_db_postgres.inc.php', 'serendipity_db_pdo-postgres.inc.php', 'serendipity_db_sqlite.inc.php', + 'serendipity_db_sqlite3.inc.php', 'htaccess.cgi.errordocs.tpl', 'htaccess.cgi.normal.tpl', 'htaccess.cgi.rewrite.tpl', -- 2.39.5