/**
* Method to send SQL query
*
* @param resource $res_conn
* @return void
*/
private function sendQuery($res_conn)
{
// checking query type
// if the query return recordset or not
if (preg_match("/^(SELECT)\\s/i", $this->sql_string)) {
$this->res_result = @sqlite_query($res_conn, $this->sql_string);
// error checking
if (!$this->res_result) {
$this->errno = sqlite_last_error($res_conn);
$this->error = "Query failed to executed. Please check your query again. \n" . sqlite_error_string($this->errno);
} else {
// count number of rows
$this->num_rows = @sqlite_num_rows($this->res_result);
}
} else {
$_query = @sqlite_unbuffered_query($res_conn, $this->sql_string);
$this->insert_id = sqlite_last_insert_rowid($res_conn);
// error checking
if (!$_query) {
$this->errno = sqlite_last_error($res_conn);
$this->error = "Query failed to executed. Please check your query again. \n" . sqlite_error_string($this->errno);
} else {
// get number of affected row
$this->affected_rows = @sqlite_changes($res_conn);
}
// nullify query
$_query = null;
}
}
function get_masters()
{
$db = sqlite_open("database.db") or die("failed to open/create the database");
$sql = "SELECT * FROM master";
$res = sqlite_query($db, $sql);
return $res;
}
public function prepareDB($bWithData = true)
{
if (file_exists(dirname(__FILE__) . '/unittest.db')) {
unlink(dirname(__FILE__) . '/unittest.db');
}
$db = sqlite_open(dirname(__FILE__) . '/unittest.db');
$res = sqlite_query($db, 'CREATE TABLE people (id INTEGER PRIMARY KEY, firstName TEXT, lastName TEXT)', $sError);
if ($res === false) {
throw new Exception($sError);
}
if ($bWithData) {
$res = sqlite_query($db, 'INSERT INTO people (id,firstName,lastName) VALUES (1, \'Jerome\', \'Piochet\')', $sError);
if ($res === false) {
throw new Exception($sError);
}
$res = sqlite_query($db, 'INSERT INTO people (id,firstName,lastName) VALUES (2, \'Tadao\', \'Poichet\')', $sError);
if ($res === false) {
throw new Exception($sError);
}
$res = sqlite_query($db, 'INSERT INTO people (id,firstName,lastName) VALUES (3, \'A\', \'B\')', $sError);
if ($res === false) {
throw new Exception($sError);
}
$res = sqlite_query($db, 'INSERT INTO people (id,firstName,lastName) VALUES (4, \'C\', \'D\')', $sError);
if ($res === false) {
throw new Exception($sError);
}
}
sqlite_close($db);
}
public function executeNoresSQL($a_query)
{
if (!$this->db) {
return NULL;
}
sqlite_query($this->db, $a_query);
}
public function __construct()
{
if (!defined("WebOrbServicesPath")) {
$dom = new DomDocument();
$dom->load(WebOrb . "weborb-config.xml");
$servicePath = $dom->documentElement->getAttribute('servicePath');
define("WebOrbServicesPath", realpath(WebOrb . $servicePath) . DIRECTORY_SEPARATOR);
}
if (file_exists(WebOrbServicesPath . "Weborb/Examples/Security/datbase.db")) {
if (!($this->link = sqlite_open(WebOrbServicesPath . "Weborb/Examples/Security/datbase.db", 0666, $sqliteerror))) {
throw new Exception($sqliteerror);
}
} else {
if (!($this->link = sqlite_open(WebOrbServicesPath . "Weborb/Examples/Security/datbase.db", 0666, $sqliteerror))) {
throw new Exception($sqliteerror);
}
sqlite_query($this->link, "CREATE TABLE Security ( Id INT, UserName VARCHAR(20), Password VARCHAR(20), Role VARCHAR(20) )");
sqlite_query($this->link, "INSERT INTO Security (Id, UserName, Password, Role) VALUES ( 1, 'joe', 'flexrocks', 'examplesuser' )");
sqlite_query($this->link, "INSERT INTO Security (Id, UserName, Password, Role) VALUES ( 2, 'bob', 'weborb', 'administrator' )");
sqlite_query($this->link, "CREATE TABLE Products ( Id INT, Name VARCHAR(20), Price NUMERIC )");
sqlite_query($this->link, "INSERT INTO Products (Id, Name, Price) VALUES ( 1, 'Laptop', 499 )");
sqlite_query($this->link, "INSERT INTO Products (Id, Name, Price) VALUES ( 2, 'Mouse', 20 )");
sqlite_query($this->link, "INSERT INTO Products (Id, Name, Price) VALUES ( 3, 'Keyboard', 10 )");
}
}
function populate_sqlite_db($database, $db)
{
sqlite_query($db, "CREATE TABLE webcal_user (cal_login VARCHAR(25) NOT NULL, cal_passwd VARCHAR(32), cal_lastname VARCHAR(25), cal_firstname VARCHAR(25), cal_is_admin CHAR(1) DEFAULT 'N',cal_email VARCHAR(75) NULL,cal_enabled CHAR(1) DEFAULT 'Y',cal_telephone VARCHAR(50) NULL,cal_address VARCHAR(75) NULL,cal_title VARCHAR(75) NULL,cal_birthday INT,cal_last_login INT, PRIMARY KEY ( cal_login ))");
sqlite_query($db, "INSERT INTO webcal_user ( cal_login, cal_passwd, cal_lastname, cal_firstname, cal_is_admin ) VALUES ( 'admin', '21232f297a57a5a743894a0e4a801fc3', 'Administrator', 'Default', 'Y' );");
sqlite_query($db, "CREATE TABLE webcal_entry ( cal_id INT NOT NULL, cal_group_id INT NULL, cal_ext_for_id INT NULL, cal_create_by VARCHAR(25) NOT NULL, cal_date INT NOT NULL, cal_time INT NULL, cal_mod_date INT, cal_mod_time INT, cal_duration INT NOT NULL, cal_due_date INT default NULL, cal_due_time INT default NULL, cal_location varchar(100) default NULL, cal_url varchar(100) default NULL, cal_completed INT default NULL, cal_priority INT DEFAULT 5, cal_type CHAR(1) DEFAULT 'E', cal_access CHAR(1) DEFAULT 'P', cal_name VARCHAR(80) NOT NULL, cal_description TEXT, PRIMARY KEY ( cal_id ))");
sqlite_query($db, "CREATE TABLE webcal_entry_repeats ( cal_id INT DEFAULT 0 NOT NULL, cal_type VARCHAR(20), cal_end INT, cal_frequency INT DEFAULT 1, cal_days CHAR(7), cal_endtime int(11) default NULL, cal_bymonth varchar(50) default NULL, cal_bymonthday varchar(100) default NULL, cal_byday varchar(100) default NULL, cal_bysetpos varchar(50) default NULL, cal_byweekno varchar(50) default NULL, cal_byyearday varchar(50) default NULL, cal_wkst char(2) default 'MO', cal_count int(11) default NULL, PRIMARY KEY (cal_id))");
sqlite_query($db, "CREATE TABLE webcal_entry_repeats_not ( cal_id INT NOT NULL, cal_date INT NOT NULL, cal_exdate INT NOT NULL default '1', PRIMARY KEY ( cal_id, cal_date ))");
sqlite_query($db, "CREATE TABLE webcal_entry_user ( cal_id INT DEFAULT 0 NOT NULL, cal_login VARCHAR(25) NOT NULL, cal_status CHAR(1) DEFAULT 'A', cal_category INT DEFAULT NULL, cal_percent INT NOT NULL default '0', PRIMARY KEY ( cal_id, cal_login ))");
sqlite_query($db, "CREATE TABLE webcal_entry_ext_user ( cal_id INT DEFAULT 0 NOT NULL, cal_fullname VARCHAR(50) NOT NULL, cal_email VARCHAR(75) NULL, PRIMARY KEY ( cal_id, cal_fullname ))");
sqlite_query($db, "CREATE TABLE webcal_user_pref ( cal_login VARCHAR(25) NOT NULL, cal_setting VARCHAR(25) NOT NULL, cal_value VARCHAR(100) NULL, PRIMARY KEY ( cal_login, cal_setting ))");
sqlite_query($db, "CREATE TABLE webcal_user_layers ( cal_layerid INT DEFAULT 0 NOT NULL, cal_login VARCHAR(25) NOT NULL, cal_layeruser VARCHAR(25) NOT NULL, cal_color VARCHAR(25) NULL, cal_dups CHAR(1) DEFAULT 'N', PRIMARY KEY ( cal_login, cal_layeruser ))");
sqlite_query($db, "CREATE TABLE webcal_site_extras ( cal_id INT DEFAULT 0 NOT NULL, cal_name VARCHAR(25) NOT NULL, cal_type INT NOT NULL, cal_date INT DEFAULT 0, cal_remind INT DEFAULT 0, cal_data TEXT)");
sqlite_query($db, "CREATE TABLE webcal_reminders (cal_id INT DEFAULT 0 NOT NULL,cal_date INT DEFAULT 0 NOT NULL,cal_offset INT DEFAULT 0 NOT NULL,cal_related CHAR(1) DEFAULT 'S' NOT NULL,cal_before CHAR(1) DEFAULT 'Y' NOT NULL,cal_last_sent INT DEFAULT NULL,cal_repeats INT DEFAULT 0 NOT NULL,cal_duration INT DEFAULT 0 NOT NULL,cal_times_sent INT DEFAULT 0 NOT NULL,cal_action VARCHAR(12) DEFAULT 'EMAIL' NOT NULL,PRIMARY KEY ( cal_id ))");
sqlite_query($db, "CREATE TABLE webcal_group ( cal_group_id INT NOT NULL, cal_owner VARCHAR(25) NULL, cal_name VARCHAR(50) NOT NULL, cal_last_update INT NOT NULL, PRIMARY KEY ( cal_group_id ))");
sqlite_query($db, "CREATE TABLE webcal_group_user ( cal_group_id INT NOT NULL, cal_login VARCHAR(25) NOT NULL, PRIMARY KEY ( cal_group_id, cal_login ))");
sqlite_query($db, "CREATE TABLE webcal_view ( cal_view_id INT NOT NULL, cal_owner VARCHAR(25) NOT NULL, cal_name VARCHAR(50) NOT NULL, cal_view_type CHAR(1), cal_is_global CHAR(1) DEFAULT 'N' NOT NULL, PRIMARY KEY ( cal_view_id ))");
sqlite_query($db, "CREATE TABLE webcal_view_user ( cal_view_id INT NOT NULL, cal_login VARCHAR(25) NOT NULL, PRIMARY KEY ( cal_view_id, cal_login ))");
sqlite_query($db, "CREATE TABLE webcal_config ( cal_setting VARCHAR(50) NOT NULL, cal_value VARCHAR(100) NULL, PRIMARY KEY ( cal_setting ))");
sqlite_query($db, "CREATE TABLE webcal_entry_log ( cal_log_id INT NOT NULL, cal_entry_id INT NOT NULL, cal_login VARCHAR(25) NOT NULL, cal_user_cal VARCHAR(25) NULL, cal_type CHAR(1) NOT NULL, cal_date INT NOT NULL, cal_time INT NULL, cal_text TEXT, PRIMARY KEY ( cal_log_id ))");
sqlite_query($db, "CREATE TABLE webcal_categories ( cat_id INT NOT NULL, cat_owner VARCHAR(25) NULL, cat_name VARCHAR(80) NOT NULL,cat_color VARCHAR(8) NULL, PRIMARY KEY ( cat_id ))");
sqlite_query($db, "CREATE TABLE webcal_asst ( cal_boss VARCHAR(25) NOT NULL, cal_assistant VARCHAR(25) NOT NULL, PRIMARY KEY ( cal_boss, cal_assistant ))");
sqlite_query($db, "CREATE TABLE webcal_nonuser_cals ( cal_login VARCHAR(25) NOT NULL, cal_lastname VARCHAR(25) NULL, cal_firstname VARCHAR(25) NULL, cal_admin VARCHAR(25) NOT NULL, cal_is_public CHAR(1) DEFAULT 'N' NOT NULL, cal_url VARCHAR(255) DEFAULT NULL, PRIMARY KEY ( cal_login ))");
sqlite_query($db, "CREATE TABLE webcal_import ( cal_import_id INT NOT NULL, cal_name VARCHAR(50) NULL, cal_date INT NOT NULL, cal_type VARCHAR(10) NOT NULL, cal_login VARCHAR(25) NULL, PRIMARY KEY ( cal_import_id ))");
sqlite_query($db, "CREATE TABLE webcal_import_data ( cal_import_id INT NOT NULL, cal_id INT NOT NULL, cal_login VARCHAR(25) NOT NULL, cal_import_type VARCHAR(15) NOT NULL, cal_external_id VARCHAR(200) NULL, PRIMARY KEY ( cal_id, cal_login ))");
sqlite_query($db, "CREATE TABLE webcal_report ( cal_login VARCHAR(25) NOT NULL, cal_report_id INT NOT NULL, cal_is_global CHAR(1) DEFAULT 'N' NOT NULL, cal_report_type VARCHAR(20) NOT NULL, cal_include_header CHAR(1) DEFAULT 'Y' NOT NULL, cal_report_name VARCHAR(50) NOT NULL, cal_time_range INT NOT NULL, cal_user VARCHAR(25) NULL, cal_allow_nav CHAR(1) DEFAULT 'Y', cal_cat_id INT NULL, cal_include_empty CHAR(1) DEFAULT 'N', cal_show_in_trailer CHAR(1) DEFAULT 'N', cal_update_date INT NOT NULL, PRIMARY KEY ( cal_report_id ))");
sqlite_query($db, "CREATE TABLE webcal_report_template ( cal_report_id INT NOT NULL, cal_template_type CHAR(1) NOT NULL, cal_template_text TEXT, PRIMARY KEY ( cal_report_id, cal_template_type ))");
sqlite_query($db, "CREATE TABLE webcal_access_user ( cal_login VARCHAR(25) NOT NULL, cal_other_user VARCHAR(25) NOT NULL, cal_can_view INT NOT NULL DEFAULT '0', cal_can_edit INT NOT NULL DEFAULT '0', cal_can_approve INT NOT NULL DEFAULT '0', cal_can_invite CHAR(1) NOT NULL DEFAULT 'Y', cal_can_email CHAR(1) NOT NULL DEFAULT 'Y', cal_see_time_only CHAR(1) NOT NULL DEFAULT 'N', PRIMARY KEY ( cal_login, cal_other_user ))");
sqlite_query($db, "CREATE TABLE webcal_access_function ( cal_login VARCHAR(25) NOT NULL, cal_permissions VARCHAR(64) NOT NULL, PRIMARY KEY ( cal_login ))");
sqlite_query($db, "CREATE TABLE webcal_user_template ( cal_login VARCHAR(25) NOT NULL default '', cal_type CHAR(1) NOT NULL default '', cal_template_text text, PRIMARY KEY (cal_login,cal_type))");
sqlite_query($db, "CREATE TABLE webcal_entry_categories (cal_id INT NOT NULL default '0', cat_id INT NOT NULL default '0', cat_order INT NOT NULL default '0', cat_owner VARCHAR(25) default NULL)");
sqlite_query($db, "CREATE TABLE webcal_blob ( cal_blob_id INT NOT NULL, cal_id INT NULL, cal_login VARCHAR(25) NULL, cal_name VARCHAR(30) NULL, cal_description VARCHAR(128) NULL, cal_size INT NULL, cal_mime_type VARCHAR(50) NULL, cal_type CHAR(1) NOT NULL, cal_mod_date INT NOT NULL, cal_mod_time INT NOT NULL, cal_blob BLOB, PRIMARY KEY ( cal_blob_id ))");
sqlite_query($db, "CREATE TABLE webcal_timezones (tzid varchar(100) NOT NULL default '', dtstart varchar(25) default NULL, dtend varchar(25) default NULL, vtimezone text, PRIMARY KEY ( tzid ))");
}
function run_query($sql)
{
if (!$this->conn) {
$this->connect();
}
return sqlite_query($sql, $this->conn);
}
function query_start($query)
{
// For reg expressions
$query = trim($query);
// Query was an insert, delete, update, replace
if (preg_match("/^(insert|delete|update|replace)\\s+/i", $query)) {
return false;
}
$this->savedqueries[] = $query;
// Flush cached values..
$this->flush();
// Log how the function was called
$this->func_call = "\$db->query_start(\"{$query}\")";
// Keep track of the last query for debug..
$this->last_query = $query;
$this->result = @sqlite_query($query, $this->dbh);
$this->num_queries++;
// If there is an error then take note of it..
if (!$this->result) {
$this->print_error();
return false;
}
// =======================================================
// Take note of column info
#$i=0;
#foreach(@sqlite_fetch_field_array($handle) as $name)
#{
# $this->col_info[$i++]->name = $name;
#}
$this->last_result = array();
$this->num_rows = 0;
// If debug ALL queries
$this->trace || $this->debug_all ? $this->debug() : null;
return true;
}
/**
* 执行mysql query()操作
* @param string $sql
* @return mixed
*/
public function query($sql)
{
// 是否记录 SQL log
if (true == C('sql_log')) {
wlog('SQL-Log', $sql);
}
$error_msg = '';
$_key = strtolower(substr($sql, 0, 6));
if ($_key == 'select') {
$rs = sqlite_query($this->db, $sql, SQLITE_BOTH, $error_msg);
} else {
$rs = sqlite_exec($this->db, $sql, $error_msg);
}
if (!empty($rs)) {
$GLOBALS['run_dbquery_count']++;
return $rs;
} else {
if (C('show_errors')) {
show_error('执行sqlite_query()出现错误: ' . $error_msg . '<br />原SQL: ' . $sql);
} else {
exit('db_sqlite2::query() error.');
}
}
//return false;
}
function increase_nb_play($id)
{
global $db;
$id = (int) $id;
$query = "UPDATE fuu SET nb_play=nb_play + 1 WHERE id='{$id}'";
return sqlite_query($db, $query);
}
protected function _query($query = '')
{
if ($this->_conexion == null) {
$this->_connect();
}
return sqlite_query($query, $this->_conexion, SQLITE_ASSOC);
}
public function set($key, $val, $immutable = false)
{
if ($immutable) {
$this->immutableSet($key, $val);
} else {
if (empty($val)) {
sqlite_query($this->db, "DELETE FROM is4c_local WHERE keystr='{$key}'");
} else {
if (is_array($val)) {
$temp = "";
foreach ($val as $v) {
$temp .= $v . chr(255);
}
$val = substr($temp, 0, strlen($temp) - 1);
} elseif ($val === false) {
$val = 'FALSE';
} elseif ($val === true) {
$val = 'TRUE';
}
$check = sqlite_query($this->db, "SELECT valstr FROM is4c_local WHERE keystr='{$key}'");
if (sqlite_num_rows($check) == 0) {
sqlite_query($this->db, "INSERT INTO is4c_local VALUES ('{$key}','{$val}')");
} else {
sqlite_query($this->db, "UPDATE is4c_local SET valstr='{$val}' WHERE keystr='{$key}'");
}
}
}
$this->debug();
}
public function request($s)
{
$h = sqlite_query($this->c, $s, SQLITE_BOTH, $this->e);
if ($h === false) {
throw new HException("Error while executing " . $s . " (" . $this->e . ")");
}
return new php_db__Sqlite_SqliteResultSet($h);
}
protected function _doExec($query)
{
if ($qI = sqlite_query($query, $this->_connection)) {
return sqlite_changes($this->_connection);
} else {
throw new jException('jelix~db.error.query.bad', sqlite_error_string($this->_connection) . '(' . $query . ')');
}
}
public function query($query)
{
$this->enhanceQuery($query);
if ($resource = sqlite_query($this->handle, $query)) {
return new SQLResult_sqlite2($resource);
}
return false;
}
/**
* Constructs a new SqliteResultSet
*
* \param $sql
* The sql query to execute.
*
* \param $backend
* A reference to the used backend.
*/
function SqliteResultSet($sql, &$backend)
{
assert('is_string($sql)');
$this->sql = $sql;
$this->backend =& $backend;
$this->rs = sqlite_query($this->backend->handle, $sql) or trigger_error(sprintf('Query failed (%s)', sqlite_error_string(sqlite_last_error($this->backend->handle))), E_USER_ERROR);
$this->rows_affected = sqlite_changes($this->backend->handle);
}
public function query($sql)
{
if ($sql) {
return sqlite_query($this->openObj, $sql, 'SQLITE_ASSOC');
} else {
return false;
}
}
function execute($sql)
{
$result = sqlite_query($this->getConnectionId(), $sql);
if ($result === false) {
$this->_raiseError($sql);
}
return $result;
}
public function real_execute_sql($sql)
{
$result_set_handle = sqlite_query($this->connection_handle, $sql);
if (!$result_set_handle) {
throw new AnewtDatabaseQueryException('SQLite error: %s', sqlite_error_string(sqlite_last_error($this->connection_handle)));
}
return new AnewtDatabaseResultSetSQLite($sql, $this->connection_handle, $result_set_handle);
}
public function query($sql)
{
LogMaster::log($sql);
$res = sqlite_query($this->connection, $sql);
if ($res === false) {
throw new Exception("SQLLite - sql execution failed\n" . sqlite_error_string(sqlite_last_error($this->connection)));
}
return $res;
}
/**
* Method which runs queries. Returns a class on success and false on error
* @param string $sql The SQL query to run
* @return class
* @uses lastfmApiDatabase_result
*/
function &query($sql)
{
if (!($queryResource = sqlite_query($this->dbConn, $sql, SQLITE_BOTH, $this->error))) {
return false;
} else {
$result = new SqliteResult($this, $queryResource);
return $result;
}
}
/**
* 执行一个SQL语句
* @param sql 需要执行的SQL语句
*/
public function exec($sql)
{
$this->arrSql[] = $sql;
if ($result = sqlite_query($this->conn, $sql, SQLITE_ASSOC, $sqliteerror)) {
return $result;
} else {
spError("{$sql}<br />执行错误: " . $sqliteerror);
}
}
function query($query)
{
$this->error = "";
if ($this->versi == 3) {
return $this->db->query($query);
} else {
return sqlite_query($this->{$db}, $query, SQLITE_ASSOC, $this->error);
}
}
/**
* 执行数据库查询
*
* @param string $query
* @param mixed $handle
* @param int $op
* @param null $action
* @return resource|SQLiteResult
* @throws Typecho_Db_Query_Exception
*/
public function query($query, $handle, $op = Typecho_Db::READ, $action = NULL)
{
if ($resource = @sqlite_query($query instanceof Typecho_Db_Query ? $query->__toString() : $query, $handle)) {
return $resource;
}
/** 数据库异常 */
$errorCode = sqlite_last_error($this->_dbHandle);
throw new Typecho_Db_Query_Exception(sqlite_error_string($errorCode), $errorCode);
}
/**
* query
*
* @param mixed $query
* @access public
* @return mixed
* @throws AdapterException
*/
public function query($query)
{
$handle = @sqlite_query($query, $this->_dbHandle);
if (!$handle) {
$errorCode = sqlite_last_error($this->_dbHandle);
throw new AdapterException(sqlite_error_string($errorCode), $errorCode);
}
return $handle;
}
public function query($sql)
{
if ($this->lnk) {
$query_result = sqlite_query($this->lnk, $sql);
return $query_result;
} else {
return false;
}
}
function vdict_get($db, $word)
{
if ($db instanceof PDO) {
$res = $db->query('SELECT * FROM dict WHERE word = ' . $db->quote($word));
return $res->fetch();
}
$res = sqlite_query($db, "SELECT * FROM dict WHERE word = '{$word}'");
return sqlite_fetch_array($res);
}
function query($sql)
{
//DBへクエリを発信する
if ($result = sqlite_query($this->link, $sql)) {
return true;
} else {
return false;
}
}
/**
* Executes the given query and returns the resource. If an error has occured, passes the error data to $Sql->_error()
* Argument : $query - SQL query
* Return : The SQL Resource of the given query
*/
function getSql($query)
{
$this->_resource = sqlite_query($this->_db_connection, $query);
if (!$this->_resource) {
$this->_error($query);
return false;
}
return $this->_resource;
}
/**
* Method which runs queries. Returns a class on success and false on error
* @param string $sql The SQL query to run
* @return class
* @uses lastfmApiDatabase_result
*/
function &query($sql)
{
if (!($queryResource = sqlite_query($this->dbConn, $sql, SQLITE_BOTH, $this->error))) {
return false;
} else {
$return = new lastfmApiDatabase_result($this, $queryResource);
return $return;
}
}
