QueryBuilder = new SQLiteQueryBuilder();
}
function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port, $persistency = false)
{
$this->persistency = $persistency;
$this->user = $sqluser;
$this->server = $sqlserver . (($port) ? ':' . $port : '');
$this->dbname = $database;
$this->db_connection = ($this->persistency) ? @sqlite_popen($this->server, 0, $error) : new SQLite3($this->server);
return ($this->db_connection) ? true : $error;
}
// Other base methods
function sql_close()
{
if (!$this->db_connection)
{
return false;
}
return $this->db_connection->close();
}
function sql_return_on_error($fail = false)
{
$this->return_on_error = $fail;
}
function sql_num_queries()
{
return $this->num_queries;
}
function sql_transaction($status = 'begin')
{
switch ($status)
{
case 'begin':
$this->transaction = true;
$result = $this->db_connection->exec('BEGIN');
break;
case 'commit':
$this->transaction = false;
$result = $this->db_connection->exec('COMMIT');
break;
case 'rollback':
$this->transaction = false;
$result = $this->db_connection->exec('ROLLBACK');
break;
default:
$result = true;
}
return $result;
}
// Base query method
function sql_query($query = '', $cache_ttl = 0)
{
if ($query != '')
{
global $cache;
$query = preg_replace('#FROM \((.*?)\)(,|[\n\t ]+?WHERE) #s', 'FROM \1\2 ', $query);
if (!$expire_time || !$cache->sql_load($query, $expire_time))
{
if ($expire_time)
{
$cache_result = true;
}
$this->query_result = false;
$this->num_queries++;
if (!empty($_GET['explain']))
{
global $starttime;
$curtime = explode(' ', microtime());
$curtime = $curtime[0] + $curtime[1] - $starttime;
}
if (!($this->query_result = $this->db_connection->query($query)))
{
$this->sql_error($query);
}
if (!empty($_GET['explain']))
{
$endtime = explode(' ', microtime());
$endtime = $endtime[0] + $endtime[1] - $starttime;
$this->sql_report .= "
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: " . ($endtime - $curtime) . "\n
";
}
else
{
$error = $this->sql_error();
$this->sql_report .= 'FAILED - SQLite ' . $error['code'] . ': ' . htmlspecialchars($error['message']) . '
';
}
$this->sql_time += $endtime - $curtime;
if (preg_match('#^SELECT#', $query))
{
$html_table = FALSE;
if ($result = $this->db_connection->query("EXPLAIN $query"))
{
while ($row = $result->fetchArray(SQLITE_ASSOC))
{
if (!$html_table && sizeof($row))
{
$html_table = TRUE;
$this->sql_report .= "
\n";
$this->sql_report .= "\n" . implode(" | \n", array_keys($row)) . " | \n
\n";
}
$this->sql_report .= "\n" . implode(" | \n", array_values($row)) . " | \n
\n";
}
}
if ($html_table)
{
$this->sql_report .= '
';
}
}
$this->sql_report .= "
\n";
}
if (preg_match('#^SELECT#', $query))
{
$this->open_queries[] = $this->query_result;
}
}
if (!empty($cache_result))
{
$cache->sql_save($query, $this->query_result);
}
}
else
{
return false;
}
return ($this->query_result) ? $this->query_result : false;
}
function sql_query_limit($query, $total, $offset = 0, $expire_time = 0)
{
if ($query != '')
{
$this->query_result = false;
$query .= ' LIMIT ' . ((!empty($offset)) ? $total . ' OFFSET ' . $offset : $total);
return $this->sql_query($query, $expire_time);
}
else
{
return false;
}
}
// Idea for this from Ikonboard
function sql_build_array($query, $assoc_ary = false)
{
if (!is_array($assoc_ary))
{
return false;
}
$fields = array();
$values = array();
if ($query == 'INSERT')
{
foreach ($assoc_ary as $key => $var)
{
$fields[] = $key;
if (is_null($var))
{
$values[] = 'NULL';
}
elseif (is_string($var))
{
$values[] = "'" . $this->sql_escape($var) . "'";
}
else
{
$values[] = (is_bool($var)) ? intval($var) : $var;
}
}
$query = ' (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $values) . ')';
}
else if ($query == 'UPDATE')
{
$values = array();
foreach ($assoc_ary as $key => $var)
{
if (is_null($var))
{
$values[] = "$key = NULL";
}
elseif (is_string($var))
{
$values[] = "$key = '" . $this->sql_escape($var) . "'";
}
else
{
$values[] = (is_bool($var)) ? "$key = " . intval($var) : "$key = $var";
}
}
$query = implode(', ', $values);
}
else if ($query == 'SELECT')
{
foreach ($assoc_ary as $key => $var)
{
if (is_null($var))
{
$values[] = "$key = NULL";
}
elseif (is_string($var))
{
$values[] = "$key = '" . $this->sql_escape($var) . "'";
}
else
{
$values[] = (is_bool($var)) ? "$key = " . intval($var) : "$key = $var";
}
}
$query = implode(' AND ', $values);
}
return $query;
}
// Other query methods
//
// NOTE :: Want to remove _ALL_ reliance on sql_numrows from core code ...
// don't want this here by a middle Milestone
function sql_numrows($query_id = false)
{
// The new SQLite3 module in PHP doesn't provide a way of determining
// the number of rows returned before iterating through them.
throw new Exception('Not Implemented');
}
function sql_affectedrows()
{
return ($this->db_connection) ? $this->db_connection->changes() : false;
}
function sql_fetchrow($query_result = NULL)
{
global $cache;
if (!$query_result)
{
$query_result = $this->query_result;
}
if (isset($cache->sql_rowset[$query_result]))
{
return $cache->sql_fetchrow($query_result);
}
return ($query_result) ? $query_result->fetchArray(SQLITE3_ASSOC) : false;
}
function sql_fetchrowset($query_result = false)
{
if (!$query_result)
{
$query_result = $this->query_result;
}
if ($query_result)
{
$result = array();
while ($row = $query_result->fetchArray(SQLITE3_ASSOC))
{
$result[] = $row;
}
return $result;
}
else
{
return false;
}
}
function sql_fetchfield($field, $rownum = -1, $query_id = 0)
{
if (!$query_id)
{
$query_id = $this->query_result;
}
$row = [];
while ($rownum > 0) {
$row = $query_id->fetchArray(SQLITE3_NUM);
}
if ($row)
{
return $row[$field];
}
}
function sql_rowseek($rownum, $query_id = 0)
{
if (!$query_id)
{
$query_id = $this->query_result;
}
return ($query_id) ? @sqlite_seek($query_id, $rownum) : false;
}
function sql_nextid()
{
return ($this->db_connection) ? $this->db_connection->lastInsertRowID() : false;
}
function sql_freeresult($query_id = false)
{
return true;
}
function sql_escape($msg)
{
return SQLite3::escapeString(stripslashes($msg));
}
function sql_error($sql = '')
{
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']);
$message = 'SQL ERROR [ ' . SQL_LAYER . ' ]
' . $this->db_connection->lastErrorMsg() . '
CALLING PAGE
' . htmlspecialchars($this_page) . (($sql != '') ? '
SQL
' . $sql : '') . '
';
if ($this->transaction)
{
$this->sql_transaction('rollback');
}
trigger_error($message, E_USER_ERROR);
}
$result = array(
'message' => $this->db_connection->lastErrorMsg(),
'code' => $this->db_connection->lastErrorCode(),
);
return $result;
}
} // class sql_db
} // if ... define
?>