diff options
| author | Meik Sievertsen <acydburn@phpbb.com> | 2005-05-05 17:01:07 +0000 |
|---|---|---|
| committer | Meik Sievertsen <acydburn@phpbb.com> | 2005-05-05 17:01:07 +0000 |
| commit | 81c7144f6847e964057762e94dc197ac77a8f24e (patch) | |
| tree | d84aed9aaf8c78ef4e199b9d20ebf54d86fbc378 /phpBB/includes/db/sqlite.php | |
| parent | b576d6af0a9c0c78f379ca09069648d87364e1a0 (diff) | |
| download | forums-81c7144f6847e964057762e94dc197ac77a8f24e.tar forums-81c7144f6847e964057762e94dc197ac77a8f24e.tar.gz forums-81c7144f6847e964057762e94dc197ac77a8f24e.tar.bz2 forums-81c7144f6847e964057762e94dc197ac77a8f24e.tar.xz forums-81c7144f6847e964057762e94dc197ac77a8f24e.zip | |
- updated oracle schema/db files
- updated sqlite schema/db files
git-svn-id: file:///svn/phpbb/trunk@5141 89ea8834-ac86-4346-8a33-228a782c2dd0
Diffstat (limited to 'phpBB/includes/db/sqlite.php')
| -rw-r--r-- | phpBB/includes/db/sqlite.php | 283 |
1 files changed, 182 insertions, 101 deletions
diff --git a/phpBB/includes/db/sqlite.php b/phpBB/includes/db/sqlite.php index 00d80ac4db..d74a535cea 100644 --- a/phpBB/includes/db/sqlite.php +++ b/phpBB/includes/db/sqlite.php @@ -11,10 +11,10 @@ /** * @ignore */ -if (!defined("SQL_LAYER")) +if (!defined('SQL_LAYER')) { -define("SQL_LAYER","sqlite"); +define('SQL_LAYER', 'sqlite'); /** * @package dbal_sqlite @@ -26,24 +26,30 @@ class sql_db var $query_result; var $return_on_error = false; var $transaction = false; - var $sql_report = ''; var $sql_time = 0; var $num_queries = 0; - var $open_queries = array(); - function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port, $persistency = false) + function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false) { $this->persistency = $persistency; $this->user = $sqluser; $this->server = $sqlserver . (($port) ? ':' . $port : ''); $this->dbname = $database; - $this->db_connect_id = ($this->persistency) ? @sqlite_popen($this->server, 0, $error) : @sqlite_open($this->server, 0, $error); + $error = ''; + $this->db_connect_id = ($this->persistency) ? @sqlite_popen($this->server, 0666, $error) : @sqlite_open($this->server, 0666, $error); - return ($this->db_connect_id) ? true : $error; + if ($this->db_connect_id) + { + @sqlite_query('PRAGMA short_column_names = 1', $this->db_connect_id); + } + + return ($this->db_connect_id) ? true : array('message' => $error); } + // // Other base methods + // function sql_close() { if (!$this->db_connect_id) @@ -69,18 +75,23 @@ class sql_db switch ($status) { case 'begin': - $this->transaction = true; $result = @sqlite_query('BEGIN', $this->db_connect_id); + $this->transaction = true; break; case 'commit': - $this->transaction = false; $result = @sqlite_query('COMMIT', $this->db_connect_id); + $this->transaction = false; + + if (!$result) + { + @sqlite_query('ROLLBACK', $this->db_connect_id); + } break; case 'rollback': - $this->transaction = false; $result = @sqlite_query('ROLLBACK', $this->db_connect_id); + $this->transaction = false; break; default: @@ -91,91 +102,44 @@ class sql_db } // Base query method - function sql_query($query = '', $expire_time = 0) + function sql_query($query = '', $cache_ttl = 0) { if ($query != '') { global $cache; - $query = preg_replace('#FROM \((.*?)\)(,|[\n\t ]+?WHERE) #s', 'FROM \1\2 ', $query); + $query = preg_replace('#FROM \((.*?)\)(,|[\n\r\t ]+?WHERE) #s', 'FROM \1\2 ', $query); - if (!$expire_time || !$cache->sql_load($query, $expire_time)) + // EXPLAIN only in extra debug mode + if (defined('DEBUG_EXTRA')) { - if ($expire_time) - { - $cache_result = true; - } - - $this->query_result = false; - $this->num_queries++; + $this->sql_report('start', $query); + } - if (!empty($_GET['explain'])) - { - global $starttime; + $this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false; - $curtime = explode(' ', microtime()); - $curtime = $curtime[0] + $curtime[1] - $starttime; - } + if (!$this->query_result) + { + $this->num_queries++; - if (!($this->query_result = @sqlite_query($query, $this->db_connect_id))) + if (($this->query_result = @sqlite_query($query, $this->db_connect_id)) === false) { $this->sql_error($query); } - if (!empty($_GET['explain'])) + if (defined('DEBUG_EXTRA')) { - $endtime = explode(' ', microtime()); - $endtime = $endtime[0] + $endtime[1] - $starttime; - - $this->sql_report .= "<pre>Query:\t" . htmlspecialchars(preg_replace('/[\s]*[\n\r\t]+[\n\r\s\t]*/', "\n\t", $query)) . "\n\n"; - - if ($this->query_result) - { - $this->sql_report .= "Time before: $curtime\nTime after: $endtime\nElapsed time: <b>" . ($endtime - $curtime) . "</b>\n</pre>"; - } - else - { - $error = $this->sql_error(); - $this->sql_report .= '<b>FAILED</b> - SQLite ' . $error['code'] . ': ' . htmlspecialchars($error['message']) . '<br><br><pre>'; - } - - $this->sql_time += $endtime - $curtime; - - if (preg_match('#^SELECT#', $query)) - { - $html_table = FALSE; - if ($result = @sqlite_query("EXPLAIN $query", $this->db_connect_id)) - { - while ($row = @sqlite_fetch_array($result, @sqlite_ASSOC)) - { - if (!$html_table && sizeof($row)) - { - $html_table = TRUE; - $this->sql_report .= "<table width=100% border=1 cellpadding=2 cellspacing=1>\n"; - $this->sql_report .= "<tr>\n<td><b>" . implode("</b></td>\n<td><b>", array_keys($row)) . "</b></td>\n</tr>\n"; - } - $this->sql_report .= "<tr>\n<td>" . implode(" </td>\n<td>", array_values($row)) . " </td>\n</tr>\n"; - } - } - - if ($html_table) - { - $this->sql_report .= '</table><br>'; - } - } - - $this->sql_report .= "<hr>\n"; + $this->sql_report('stop', $query); } - if (preg_match('#^SELECT#', $query)) + if ($cache_ttl && method_exists($cache, 'sql_save')) { - $this->open_queries[] = $this->query_result; + $cache->sql_save($query, $this->query_result, $cache_ttl); } } - - if (!empty($cache_result)) + else if (defined('DEBUG_EXTRA')) { - $cache->sql_save($query, $this->query_result); + $this->sql_report('fromcache', $query); } } else @@ -186,20 +150,26 @@ class sql_db return ($this->query_result) ? $this->query_result : false; } - function sql_query_limit($query, $total, $offset = 0, $expire_time = 0) - { - if ($query != '') + function sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0) + { + if ($query != '') { - $this->query_result = false; + $this->query_result = false; + + // if $total is set to 0 we do not want to limit the number of rows + if ($total == 0) + { + $total = -1; + } - $query .= ' LIMIT ' . ((!empty($offset)) ? $total . ' OFFSET ' . $offset : $total); + $query .= "\n LIMIT " . ((!empty($offset)) ? $offset . ', ' . $total : $total); - return $this->sql_query($query, $expire_time); - } - else - { - return false; - } + return $this->sql_query($query, $cache_ttl); + } + else + { + return false; + } } // Idea for this from Ikonboard @@ -234,7 +204,7 @@ class sql_db $query = ' (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $values) . ')'; } - else if ($query == 'UPDATE') + else if ($query == 'UPDATE' || $query == 'SELECT') { $values = array(); foreach ($assoc_ary as $key => $var) @@ -252,7 +222,7 @@ class sql_db $values[] = (is_bool($var)) ? "$key = " . intval($var) : "$key = $var"; } } - $query = implode(', ', $values); + $query = implode(($query == 'UPDATE') ? ', ' : ' AND ', $values); } return $query; @@ -277,7 +247,7 @@ class sql_db return ($this->db_connect_id) ? @sqlite_changes($this->db_connect_id) : false; } - function sql_fetchrow($query_id = 0) + function sql_fetchrow($query_id = false) { global $cache; @@ -286,15 +256,15 @@ class sql_db $query_id = $this->query_result; } - if ($cache->sql_exists($query_id)) + if (isset($cache->sql_rowset[$query_id])) { return $cache->sql_fetchrow($query_id); } - return ($query_id) ? @sqlite_fetch_array($query_id, @sqlite_ASSOC) : false; + return ($query_id) ? @sqlite_fetch_array($query_id, SQLITE_ASSOC) : false; } - function sql_fetchrowset($query_id = 0) + function sql_fetchrowset($query_id = false) { if (!$query_id) { @@ -305,19 +275,19 @@ class sql_db { unset($this->rowset[$query_id]); unset($this->row[$query_id]); - while ($this->rowset[$query_id] = @sqlite_fetch_array($query_id, @sqlite_ASSOC)) + + $result = array(); + while ($this->rowset[$query_id] = $this->sql_fetchrow($query_id)) { $result[] = $this->rowset[$query_id]; } return $result; } - else - { - return false; - } + + return false; } - function sql_fetchfield($field, $rownum = -1, $query_id = 0) + function sql_fetchfield($field, $rownum = -1, $query_id = false) { if (!$query_id) { @@ -326,11 +296,22 @@ class sql_db if ($query_id) { - return ($rownum > -1) ? ((@sqlite_seek($query_id, $rownum)) ? @sqlite_column($query_id, $field) : false) : @sqlite_column($query_id, $field); + if ($rownum > -1) + { + $result = (@sqlite_seek($query_id, $rownum)) ? @sqlite_column($query_id, $field) : false; + } + else + { + $result = @sqlite_column($query_id, $field); + } + + return $result; } + + return false; } - function sql_rowseek($rownum, $query_id = 0) + function sql_rowseek($rownum, $query_id = false) { if (!$query_id) { @@ -359,8 +340,8 @@ class sql_db { if (!$this->return_on_error) { - $this_page = (!empty($_SERVER['PHP_SELF'])) ? $_SERVER['PHP_SELF'] : $_ENV['PHP_SELF']; - $this_page .= '&' . ((!empty($_SERVER['QUERY_STRING'])) ? $_SERVER['QUERY_STRING'] : $_ENV['QUERY_STRING']); + $this_page = (isset($_SERVER['PHP_SELF']) && !empty($_SERVER['PHP_SELF'])) ? $_SERVER['PHP_SELF'] : $_ENV['PHP_SELF']; + $this_page .= '&' . ((isset($_SERVER['QUERY_STRING']) && !empty($_SERVER['QUERY_STRING'])) ? $_SERVER['QUERY_STRING'] : (isset($_ENV['QUERY_STRING']) ? $_ENV['QUERY_STRING'] : '')); $message = '<u>SQL ERROR</u> [ ' . SQL_LAYER . ' ]<br /><br />' . @sqlite_error_string(@sqlite_last_error($this->db_connect_id)) . '<br /><br /><u>CALLING PAGE</u><br /><br />' . htmlspecialchars($this_page) . (($sql != '') ? '<br /><br /><u>SQL</u><br /><br />' . $sql : '') . '<br />'; @@ -380,6 +361,106 @@ class sql_db return $result; } + function sql_report($mode, $query = '') + { + if (empty($_GET['explain'])) + { + return; + } + + global $cache, $starttime, $phpbb_root_path; + static $curtime, $query_hold, $html_hold; + static $sql_report = ''; + static $cache_num_queries = 0; + + if (!$query && !empty($query_hold)) + { + $query = $query_hold; + } + + switch ($mode) + { + case 'display': + if (!empty($cache)) + { + $cache->unload(); + } + $this->sql_close(); + + $mtime = explode(' ', microtime()); + $totaltime = $mtime[0] + $mtime[1] - $starttime; + + echo '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><meta http-equiv="Content-Type" content="text/html; charset=iso-8869-1"><meta http-equiv="Content-Style-Type" content="text/css"><link rel="stylesheet" href="' . $phpbb_root_path . 'adm/subSilver.css" type="text/css"><style type="text/css">' . "\n"; + echo 'th { background-image: url(\'' . $phpbb_root_path . 'adm/images/cellpic3.gif\') }' . "\n"; + echo 'td.cat { background-image: url(\'' . $phpbb_root_path . 'adm/images/cellpic1.gif\') }' . "\n"; + echo '</style><title>' . $msg_title . '</title></head><body>'; + echo '<table width="100%" cellspacing="0" cellpadding="0" border="0"><tr><td><a href="' . htmlspecialchars(preg_replace('/&explain=([^&]*)/', '', $_SERVER['REQUEST_URI'])) . '"><img src="' . $phpbb_root_path . 'adm/images/header_left.jpg" width="200" height="60" alt="phpBB Logo" title="phpBB Logo" border="0"/></a></td><td width="100%" background="' . $phpbb_root_path . 'adm/images/header_bg.jpg" height="60" align="right" nowrap="nowrap"><span class="maintitle">SQL Report</span> </td></tr></table><br clear="all"/><table width="95%" cellspacing="1" cellpadding="4" border="0" align="center"><tr><td height="40" align="center" valign="middle"><b>Page generated in ' . round($totaltime, 4) . " seconds with {$this->num_queries} queries" . (($cache_num_queries) ? " + $cache_num_queries " . (($cache_num_queries == 1) ? 'query' : 'queries') . ' returning data from cache' : '') . '</b></td></tr><tr><td align="center" nowrap="nowrap">Time spent on MySQL queries: <b>' . round($this->sql_time, 5) . 's</b> | Time spent on PHP: <b>' . round($totaltime - $this->sql_time, 5) . 's</b></td></tr></table><table width="95%" cellspacing="1" cellpadding="4" border="0" align="center"><tr><td>'; + echo $sql_report; + echo '</td></tr></table><br /></body></html>'; + exit; + break; + + case 'start': + $query_hold = $query; + $html_hold = ''; + + $curtime = explode(' ', microtime()); + $curtime = $curtime[0] + $curtime[1]; + break; + + case 'fromcache': + $endtime = explode(' ', microtime()); + $endtime = $endtime[0] + $endtime[1]; + + $result = @sqlite_query($query, $this->db_connect_id); + while ($void = @sqlite_fetch_array($result, SQLITE_ASSOC)) + { + // Take the time spent on parsing rows into account + } + $splittime = explode(' ', microtime()); + $splittime = $splittime[0] + $splittime[1]; + + $time_cache = $endtime - $curtime; + $time_db = $splittime - $endtime; + $color = ($time_db > $time_cache) ? 'green' : 'red'; + + $sql_report .= '<hr width="100%"/><br /><table class="bg" width="100%" cellspacing="1" cellpadding="4" border="0"><tr><th>Query results obtained from the cache</th></tr><tr><td class="row1"><textarea style="font-family:\'Courier New\',monospace;width:100%" rows="5">' . preg_replace('/\t(AND|OR)(\W)/', "\$1\$2", htmlspecialchars(preg_replace('/[\s]*[\n\r\t]+[\n\r\s\t]*/', "\n", $query))) . '</textarea></td></tr></table><p align="center">'; + + $sql_report .= 'Before: ' . sprintf('%.5f', $curtime - $starttime) . 's | After: ' . sprintf('%.5f', $endtime - $starttime) . 's | Elapsed [cache]: <b style="color: ' . $color . '">' . sprintf('%.5f', ($time_cache)) . 's</b> | Elapsed [db]: <b>' . sprintf('%.5f', $time_db) . 's</b></p>'; + + // Pad the start time to not interfere with page timing + $starttime += $time_db; + + $cache_num_queries++; + break; + + case 'stop': + $endtime = explode(' ', microtime()); + $endtime = $endtime[0] + $endtime[1]; + + $sql_report .= '<hr width="100%"/><br /><table class="bg" width="100%" cellspacing="1" cellpadding="4" border="0"><tr><th>Query #' . $this->num_queries . '</th></tr><tr><td class="row1"><textarea style="font-family:\'Courier New\',monospace;width:100%" rows="5">' . preg_replace('/\t(AND|OR)(\W)/', "\$1\$2", htmlspecialchars(preg_replace('/[\s]*[\n\r\t]+[\n\r\s\t]*/', "\n", $query))) . '</textarea></td></tr></table> ' . $html_hold . '<p align="center">'; + + if ($this->query_result) + { + if (preg_match('/^(UPDATE|DELETE|REPLACE)/', $query)) + { + $sql_report .= "Affected rows: <b>" . $this->sql_affectedrows($this->query_result) . '</b> | '; + } + $sql_report .= 'Before: ' . sprintf('%.5f', $curtime - $starttime) . 's | After: ' . sprintf('%.5f', $endtime - $starttime) . 's | Elapsed: <b>' . sprintf('%.5f', $endtime - $curtime) . 's</b>'; + } + else + { + $error = $this->sql_error(); + $sql_report .= '<b style="color: red">FAILED</b> - ' . SQL_LAYER . ' Error ' . $error['code'] . ': ' . htmlspecialchars($error['message']); + } + + $sql_report .= '</p>'; + + $this->sql_time += $endtime - $curtime; + break; + } + } + } // class sql_db } // if ... define |