function alter_col($tablename, $colname, $type)
{
// Ensure we're connected to the database.
if ($this->dbhandle == null) {
if (!($this->dbhandle = $this->sql_database_connect())) {
$this->debug('SEVERE: Unable to connect to database.', 1);
return false;
}
}
switch ($this->db) {
case "mysql":
return $this->sql("ALTER TABLE `{$tablename}` CHANGE `{$colname}` `{$colname}` {$type}");
case "sqlite":
case "sqlite3":
// As per remove_col - SQLite doesn't support ALTER TABLE properly. We have to work
// around it's limitations.
if ($this->db == "sqlite3") {
$res = sqlite3_query($this->dbhandle, "select `tbl_name`,`sql` from `sqlite_master` where `tbl_name`='{$tablename}'");
$sqlarr = sqlite3_fetch_array($res);
sqlite3_query_close($res);
} else {
// We're using the SQLite3 class, which works normally.
$res = $this->sql("select `tbl_name`,`sql` from `sqlite_master` where `tbl_name`='{$tablename}'", "ASSOC", true);
$sqlarr = $res[0];
}
$sqlCreate = $sqlarr['sql'];
// Extract the col types for all the cols in the $sqlCreate string
preg_match_all('/\\n\\s+`(.+)`\\s(.+)[,?$]/', $sqlCreate, $arrNewTableInfo);
// Which loads the col NAMES into $arr[1] and col TYPES into $arr[2]
// For ease of use, we'll just make it assocative.
$i = 0;
foreach ($arrNewTableInfo[1] as $name) {
$arrAssocTypes[$name] = $arrNewTableInfo[2][$i++];
}
print_r($arrAssocTypes);
// and NOW we know what the types are for each name. Lets make sure
// that the col you want to change actually exists.
if (defined($arrAssocTypes[$colname])) {
$this->debug("SQL Error - Tried to change col {$colname}, but it doesn't exist", 2);
print_r($arrAssocTypes);
return false;
}
// Now we need to replace the type of the col in $sqlCreate with the correct one.
$strOld = "`{$colname}` " . $arrAssocTypes[$colname];
$strNew = "`{$colname}` " . $type;
$sqlNewCreate = str_replace($strOld, $strNew, $sqlCreate);
// Right. So we've got the new table definition in $sqlNewCreate, now all we need
// to do is move the old table out of the way, create the new table, and copy
// everything across.
$this->rename_table($tablename, "{$tablename}_temp");
$this->sql($sqlNewCreate, "NONE", true);
// Create the list of cols to use on the import.
$strAllCols = implode(",", $arrNewTableInfo[1]);
// Copy everything from the old table to the new
$sql = "INSERT INTO `{$tablename}` SELECT {$strAllCols} FROM {$tablename}_temp";
if (!$this->sql($sql, "NONE", true)) {
$this->debug("SQL Command Failed: {$sql}\n" . $this->errstr . "\n");
}
break;
default:
$this->debug("SEVERE: Database type '" . $this->db . "' NOT SUPPORTED (escape)", 0);
return false;
}
}
function &DBselect($query, $limit = 'NO')
{
global $DB;
//COpt::savesqlrequest($query);
$result = false;
if (isset($DB['DB']) && !empty($DB['DB'])) {
//SDI('SQL: '.$query);
$DB['SELECT_COUNT']++;
switch ($DB['TYPE']) {
case 'MYSQL':
if (zbx_numeric($limit)) {
$query .= ' limit ' . intval($limit);
}
$result = mysql_query($query, $DB['DB']);
if (!$result) {
error('Error in query [' . $query . '] [' . mysql_error() . ']');
}
break;
case 'POSTGRESQL':
if (zbx_numeric($limit)) {
$query .= ' limit ' . intval($limit);
}
$result = pg_query($DB['DB'], $query);
if (!$result) {
error('Error in query [' . $query . '] [' . pg_last_error() . ']');
}
break;
case 'ORACLE':
if (zbx_numeric($limit)) {
$query = 'select * from (' . $query . ') where rownum<=' . intval($limit);
}
$result = DBexecute($query);
if (!$result) {
$e = ocierror($result);
error('SQL error [' . $e['message'] . '] in [' . $e['sqltext'] . ']');
}
break;
case 'SQLITE3':
if (!$DB['TRANSACTIONS']) {
lock_db_access();
}
if (!($result = sqlite3_query($DB['DB'], $query))) {
error('Error in query [' . $query . '] [' . sqlite3_error($DB['DB']) . ']');
} else {
$data = array();
while ($row = sqlite3_fetch_array($result)) {
foreach ($row as $id => $name) {
if (!zbx_strstr($id, '.')) {
continue;
}
$ids = explode('.', $id);
$row[array_pop($ids)] = $row[$id];
unset($row[$id]);
}
$data[] = $row;
}
sqlite3_query_close($result);
$result =& $data;
}
if (!$DB['TRANSACTIONS']) {
unlock_db_access();
}
break;
}
if ($DB['TRANSACTIONS'] && !$result) {
$DB['TRANSACTION_STATE'] &= $result;
// SDI($query);
// SDI($DB['TRANSACTION_STATE']);
}
}
return $result;
}
print "Creating table:<br>\n";
$ret = sqlite3_exec($db, "CREATE TABLE test (id INTEGER, name TEXT, age INTEGER);");
if (!$ret) {
print "\ttable already exists<br>\n";
} else {
print "\ttable created<br>\n";
}
print "Inserting values:<br>\n";
sqlite3_exec($db, "INSERT INTO test (id,name,age) VALUES (1,'michael',32)");
sqlite3_exec($db, "INSERT INTO test (id,name,age) VALUES (2,'bob',27)");
sqlite3_exec($db, "INSERT INTO test (id,name,age) VALUES (3,'martin',12)");
/*
* Create a query
*/
print "SQL query:<br>\n";
$query = sqlite3_query($db, "SELECT * FROM test ORDER BY age DESC");
if (!$query) {
die(sqlite3_error($db));
}
/*
* sqlite3_fetch_array() returns an associative array
* for each row in the result set. Key indexes are
* the columns names.
*
*/
while ($row = sqlite3_fetch_array($query)) {
printf("\t%-20s %u<br>\n", $row['name'], $row['age']);
}
/*
* do not forget to release all handles !
*
/**
* execute a SQL query.
*
* @param string $query the SQL query
*
* @return mixed + object DB_error object on failure
* + object Result resource for SELECT requests
* + bool TRUE for other sucessful requests
*/
function simpleQuery($query)
{
$isManip = preg_match('/^\\s*"?(SELECT\\s+load_extension\\(|BEGIN[;\\s])/i', $query) || DB::isManip($query);
$retryMicroseconds = 300000;
$retryMore = 1.5;
$retryNb = 10;
for ($i = 0; $i <= $retryNb; $i++) {
if ($isManip) {
$this->result = sqlite3_exec($this->connection, $query);
} else {
$this->result = sqlite3_query($this->connection, $query);
}
if ($this->result) {
return $this->result;
}
$errorNative = $this->errorNative();
if ($errorNative == 'database is locked' && $i < $retryNb) {
//error_log( 'SFN - database is locked, waiting ' . ( $retryMicroseconds / 1000 ) . ' ms and retrying...' );
usleep($retryMicroseconds);
$retryMicroseconds = floor($retryMicroseconds * $retryMore);
} else {
break;
}
}
return $this->RaiseError($errorNative);
}
/**
* perform a query on the database
* @param string $Q_str
* @return result id or bool depend on the query type| FALSE
*/
function query($Q_str)
{
if (!$this->db) {
if (!(db::$autoconnect && $this->open())) {
return FALSE;
}
}
$this->verbose($Q_str, __FUNCTION__, 2);
if ($this->last_qres) {
#- close unclosed previous qres
sqlite3_query_close($this->last_qres);
$this->last_qres = null;
}
if (preg_match('!^\\s*select!i', $Q_str)) {
$this->last_qres = sqlite3_query($this->db, $Q_str);
$res = $this->last_qres;
} else {
$res = sqlite3_exec($this->db, $Q_str);
}
if (!$res) {
$this->set_error(__FUNCTION__);
}
return $res;
}
/**
* 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 or a string if there was an error (depends on $expectError),
* 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;
$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;
}
/**
* execute a SQL query.
*
* @param string $query the SQL query
*
* @return mixed + object DB_error object on failure
* + object Result resource for SELECT requests
* + bool TRUE for other sucessful requests
*/
function simpleQuery($query)
{
$isSelect = preg_match("/^\\s*SELECT/i", $query);
if (!$isSelect) {
$this->result = sqlite3_exec($this->connection, $query);
} else {
$this->result = sqlite3_query($this->connection, $query);
}
if (!$this->result) {
return $this->RaiseError($this->errorNative());
}
return $this->result;
}
function query($query)
{
switch ($this->sql) {
case "mysql":
$result = mysql_query($query, $this->link) or die(mysql_error($this->link) . " - " . $query);
return $result;
break;
case "sqlite":
$result = sqlite_query($this->link, $query) or die("<b>Error on query:</b> - " . $query);
return $result;
break;
case "sqlite3":
$result = sqlite3_query($this->link, $query) or die("<b>Error on query:</b> - " . $query);
return $result;
break;
default:
$this->error($this->sql . " is not supported yet", "query");
return false;
break;
}
}
}
$res = sqlite3_query($db, "insert into test (a,b,c, d) VALUES (?, ?, ?, ?)");
if (!$res) {
die(sqlite3_error($db));
}
if (!sqlite3_bind_int($res, 1, 10)) {
die(sqlite3_error($db));
}
if (!sqlite3_bind_text($res, 2, "bob")) {
die(sqlite3_error($db));
}
if (!sqlite3_bind_double($res, 3, 3.1415)) {
die(sqlite3_error($db));
}
if (!sqlite3_bind_blob($res, 4, file_get_contents("/bin/sh"))) {
die(sqlite3_error($db));
}
if (!sqlite3_query_exec($res, TRUE)) {
/* TRUE: delete the resource after the execution */
die(sqlite3_error($db));
}
$res = sqlite3_query($db, "SELECT * from test");
if (!$res) {
die(sqlite3_error($db));
}
$a_row = sqlite3_fetch_array($res);
for ($n = 0; $n < sqlite3_column_count($res); $n++) {
echo "column {$n}: type " . $col_types[sqlite3_column_type($res, $n)] . "\n";
}
sqlite3_query_close($res);
sqlite3_close($db);
function sqlite3_command($query, $db)
{
$han = sqlite3_open($db);
$aa = sqlite3_query($han, $query);
if (!$aa) {
echo "SQLITE3 QUERY {$query} returned error: " . sqlite3_error($han);
}
return $aa;
}
/**
* execute a SQL query.
*
* @param string $query the SQL query
*
* @return mixed + object DB_error object on failure
* + object Result resource for SELECT requests
* + bool TRUE for other sucessful requests
*/
function simpleQuery($query)
{
$isSelect = preg_match("/^\\s*SELECT/i", $query);
#$handle = fopen("/tmp/sql.txt",'a');
#fwrite($handle, "<strong>sqlite3 simpleQuery: $query</strong><br>\n");
#fclose($handle);
if (!$isSelect) {
$this->result = sqlite3_exec($this->connection, $query);
} else {
$this->result = sqlite3_query($this->connection, $query);
}
if (!$this->result) {
return $this->RaiseError($this->errorNative());
}
return $this->result;
}
* create a SQLite3 handle.
*
* Note: in-memory database are created by the magic keyword ":memory:"
*
*/
$db = sqlite3_open(":memory:");
if (!$db) {
die("Could not create in-memory database..");
}
/*
* Define a new SQL function: sha1, which takes
* only one argument.
* SQLite3 library will call the PHP function get_sha1()
* to get the result.
*
*/
if (!sqlite3_create_function($db, "sha1", 1, "get_sha1")) {
die("sqlite3_create_function() failed.");
}
$res = sqlite3_query($db, "select sha1('my password')");
if (!$res) {
die(sqlite3_error($db));
}
$row = sqlite3_fetch_array($res);
if (!$row) {
echo "error: " . sqlite3_error($db);
} else {
var_dump($row);
}
sqlite3_query_close($res);
sqlite3_close($db);
function &DBselect($query, $limit = 'NO', $offset = 0)
{
global $DB;
$time_start = microtime(true);
$result = false;
if (isset($DB['DB']) && !empty($DB['DB'])) {
$DB['SELECT_COUNT']++;
//SDI('SQL['.$DB['SELECT_COUNT'].']: '.$query);
switch ($DB['TYPE']) {
case 'MYSQL':
if (zbx_ctype_digit($limit)) {
$query .= ' LIMIT ' . intval($limit) . ' OFFSET ' . intval($offset);
}
$result = mysql_query($query, $DB['DB']);
if (!$result) {
error('Error in query [' . $query . '] [' . mysql_error() . ']');
}
break;
case 'POSTGRESQL':
if (zbx_ctype_digit($limit)) {
$query .= ' LIMIT ' . intval($limit) . ' OFFSET ' . intval($offset);
}
$result = pg_query($DB['DB'], $query);
if (!$result) {
error('Error in query [' . $query . '] [' . pg_last_error() . ']');
}
break;
case 'ORACLE':
if (zbx_ctype_digit($limit)) {
$till = $offset + $limit;
$query = 'SELECT * FROM (' . $query . ') WHERE rownum BETWEEN ' . intval($offset) . ' AND ' . intval($till);
}
$result = OCIParse($DB['DB'], $query);
if (!$result) {
$e = @ocierror();
error('SQL error [' . $e['message'] . '] in [' . $e['sqltext'] . ']');
} else {
if (!@OCIExecute($result, $DB['TRANSACTIONS'] ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS)) {
$e = ocierror($result);
error('SQL error [' . $e['message'] . '] in [' . $e['sqltext'] . ']');
}
}
break;
case 'IBM_DB2':
if (zbx_ctype_digit($limit)) {
$till = $offset + $limit;
$query = 'SELECT * FROM (' . $query . ') WHERE rownum BETWEEN ' . intval($offset) . ' AND ' . intval($till);
}
$options = array();
if ($DB['TRANSACTIONS']) {
$options['autocommit'] = DB2_AUTOCOMMIT_OFF;
}
if (!($result = db2_prepare($DB['DB'], $query))) {
$e = @db2_stmt_errormsg($result);
error('SQL error [' . $query . '] in [' . $e . ']');
} else {
if (true !== @db2_execute($result, $options)) {
$e = @db2_stmt_errormsg($result);
error('SQL error [' . $query . '] in [' . $e . ']');
$result = false;
}
}
break;
case 'SQLITE3':
if (!$DB['TRANSACTIONS']) {
lock_db_access();
}
if (zbx_ctype_digit($limit)) {
$query .= ' LIMIT ' . intval($limit) . ' OFFSET ' . intval($offset);
}
if (!($result = sqlite3_query($DB['DB'], $query))) {
error('Error in query [' . $query . '] [' . sqlite3_error($DB['DB']) . ']');
} else {
$data = array();
while ($row = sqlite3_fetch_array($result)) {
foreach ($row as $id => $name) {
if (!zbx_strstr($id, '.')) {
continue;
}
$ids = explode('.', $id);
$row[array_pop($ids)] = $row[$id];
unset($row[$id]);
}
$data[] = $row;
}
sqlite3_query_close($result);
$result =& $data;
}
if (!$DB['TRANSACTIONS']) {
unlock_db_access();
}
break;
}
if ($DB['TRANSACTIONS'] && !$result) {
$DB['TRANSACTION_STATE'] &= $result;
}
}
COpt::savesqlrequest(microtime(true) - $time_start, $query);
return $result;
}
public function query($query)
{
if (strtoupper(substr(trim($query), 0, 6)) != 'SELECT') {
return $this->exec($query);
}
$cursor = sqlite3_query($this->conn, $query);
return new SQLite3Result($query, $cursor);
}
/**
* execute a SQL query.
*
* @param string $query the SQL query
*
* @return mixed + object DB_error object on failure
* + object Result resource for SELECT requests
* + bool TRUE for other sucessful requests
*/
function simpleQuery($query)
{
$isManip = DB::isManip($query);
if ($isManip) {
$this->result = sqlite3_exec($this->connection, $query);
} else {
$this->result = sqlite3_query($this->connection, $query);
}
if (!$this->result) {
return $this->RaiseError($this->errorNative());
}
return $this->result;
}
