<?php
if (!extension_loaded("sqlite")) {
dl("sqlite.so");
if (!extension_loaded("sqlite")) {
exit("Please enable SQLite support\n");
}
}
debug_zval_dump(sqlite_libversion());
debug_zval_dump(sqlite_libencoding());
$s = sqlite_open("weztest.sqlite", 0666, $err);
debug_zval_dump($err);
debug_zval_dump($s);
$r = sqlite_query("create table foo (a INTEGER PRIMARY KEY, b INTEGER )", $s);
debug_zval_dump(sqlite_last_error($s));
debug_zval_dump(sqlite_error_string(sqlite_last_error($s)));
$r = sqlite_query("select *, php('md5', sql) as o from sqlite_master", $s);
debug_zval_dump($r);
debug_zval_dump(sqlite_num_rows($r));
debug_zval_dump(sqlite_num_fields($r));
for ($j = 0; $j < sqlite_num_fields($r); $j++) {
echo "Field {$j} is " . sqlite_field_name($r, $j) . "\n";
}
while ($row = sqlite_fetch_array($r, SQLITE_ASSOC)) {
print_r($row);
}
sqlite_close($s);
/**
* Internal method to get a list of field names returned
*
* @return Integer
*/
public function getFields()
{
$fields = sqlite_num_fields($this->result);
$result = array();
for ($i = 0; $i < $fields; $i++) {
$result[$i] = sqlite_field_name($this->result, $i);
}
return $result;
}
/**
* Constructor
*
* @param resource handle
*/
public function __construct($result)
{
$fields = array();
if (is_resource($result)) {
for ($i = 0, $num = sqlite_num_fields($result); $i < $num; $i++) {
$fields[sqlite_field_name($result, $i)] = FALSE;
// Types are unknown
}
}
parent::__construct($result, $fields);
}
/**
* Field data
*
* Generates an array of objects containing field meta-data
*
* @access public
* @return array
*/
function field_data()
{
$retval = array();
for ($i = 0; $i < $this->num_fields(); $i++) {
$F = new stdClass();
$F->name = sqlite_field_name($this->result_id, $i);
$F->type = 'varchar';
$F->max_length = 0;
$F->primary_key = 0;
$F->default = '';
$retval[] = $F;
}
return $retval;
}
/**
* Constructor method for the adapter. This constructor implements the setting of the
* 3 required properties for the object.
*
* @param resource $d The datasource resource
*/
function sqliteAdapter($d)
{
parent::RecordSetAdapter($d);
// grab all of the rows
$fieldcount = sqlite_num_fields($d);
// loop over all of the fields
for ($i = 0; $i < $fieldcount; $i++) {
// decode each field name ready for encoding when it goes through serialization
// and save each field name into the array
$this->columns[] = sqlite_field_name($d, $i);
}
if (sqlite_num_rows($d) > 0) {
$this->rows = sqlite_fetch_all($d, SQLITE_NUM);
}
}
/**
* Constructor method for the adapter. This constructor implements the setting of the
* 3 required properties for the object.
*
* @param resource $d The datasource resource
*/
function sqliteAdapter($d)
{
parent::RecordSetAdapter($d);
// grab all of the rows
$fieldcount = sqlite_num_fields($d);
$ob = "";
$fc = pack('N', $fieldcount);
if (sqlite_num_rows($d) > 0) {
sqlite_seek($d, 0);
while ($line = sqlite_fetch_array($d, SQLITE_NUM)) {
//Write array flag + length
$ob .= "\n" . $fc;
$to = count($line);
for ($i = 0; $i < $to; $i++) {
//Type everything as a string since this is sqlite
$value = $line[$i];
$os = $this->_directCharsetHandler->transliterate($value);
//string flag, string length, and string
$len = strlen($os);
if ($len < 65536) {
$ob .= "" . pack('n', $len) . $os;
} else {
$ob .= "\f" . pack('N', $len) . $os;
}
}
}
}
// grab the number of fields
// loop over all of the fields
for ($i = 0; $i < $fieldcount; $i++) {
// decode each field name ready for encoding when it goes through serialization
// and save each field name into the array
$this->columnNames[$i] = $this->_directCharsetHandler->transliterate(sqlite_field_name($d, $i));
}
$this->numRows = sqlite_num_rows($d);
$this->serializedData = $ob;
}
/**
* Returns metadata for all columns in a result set.
* @return array
*/
public function getResultColumns()
{
$count = sqlite_num_fields($this->resultSet);
$columns = array();
for ($i = 0; $i < $count; $i++) {
$name = str_replace(array('[', ']'), '', sqlite_field_name($this->resultSet, $i));
$pair = explode('.', $name);
$columns[] = array('name' => isset($pair[1]) ? $pair[1] : $pair[0], 'table' => isset($pair[1]) ? $pair[0] : NULL, 'fullname' => $name, 'nativetype' => NULL);
}
return $columns;
}
/**
* Retourne les données d'une table de la base de données sous forme de requètes SQL de type DML
*
* @param string $tablename Nom de la table à considérer
*
* @access public
* @return string
*/
function get_table_data($tablename)
{
global $db;
$contents = '';
$sql = 'SELECT * FROM ' . $tablename;
if (!($result = $db->query($sql))) {
trigger_error('Impossible d\'obtenir le contenu de la table ' . $tablename, ERROR);
}
$result->setFetchMode(SQL_FETCH_ASSOC);
if ($row = $result->fetch()) {
$contents = $this->eol;
$contents .= '-- ' . $this->eol;
$contents .= '-- Contenu de la table ' . $tablename . ' ' . $this->eol;
$contents .= '-- ' . $this->eol;
$fields = array();
for ($j = 0, $n = sqlite_num_fields($result->result); $j < $n; $j++) {
$fields[] = sqlite_field_name($result->result, $j);
}
$fields = implode(', ', $fields);
do {
$contents .= "INSERT INTO {$tablename} ({$fields}) VALUES";
foreach ($row as $key => $value) {
if (is_null($value)) {
$row[$key] = 'NULL';
} else {
$row[$key] = '\'' . $db->escape($value) . '\'';
}
}
$contents .= '(' . implode(', ', $row) . ');' . $this->eol;
} while ($row = $result->fetch());
}
return $contents;
}
/**
* Returns metadata for all columns in a result set.
*
* @return array
*/
public function getColumnsMeta()
{
$count = sqlite_num_fields($this->resultSet);
$meta = array();
for ($i = 0; $i < $count; $i++) {
// items 'name' and 'table' are required
$meta[] = array('name' => sqlite_field_name($this->resultSet, $i), 'table' => NULL);
}
return $meta;
}
protected function _field_name($field)
{
return sqlite_field_name($this->_result, $field);
}
function sql_fieldname($offset, $query_id = 0)
{
if (!$query_id) {
$query_id = $this->query_result;
}
return $query_id ? @sqlite_field_name($query_id, $offset) : false;
}
function getFieldInfo($stack = 0)
{
$fields = array();
$i = 0;
if (($table = Session::get('select', 'table')) != '') {
// query from a table, so we can find keys related information using pragma
$this->result['_tinfo'] = @sqlite_query('PRAGMA table_info(' . $this->quote($table) . ')', $this->conn);
while ($row = $this->fetchRow('_tinfo')) {
$f = new StdClass();
$f->name = $row['name'];
$f->table = $table;
$f->not_null = $row['notnull'];
$f->blob = $row['type'] == 'BLOB' ? 1 : 0;
$f->pkey = $row['pk'];
$f->ukey = 0;
$f->mkey = 0;
$f->zerofill = 0;
$f->unsigned = 0;
$f->autoinc = 0;
$f->numeric = $row['type'] == 'INTEGER' ? 1 : 0;
$f->type = $row['type'] == 'INTEGER' ? 'numeric' : ($row['type'] == 'BLOB' ? 'binary' : 'text');
$fields[] = $f;
$i++;
}
} else {
while ($i < sqlite_num_fields($this->result[$stack])) {
$f = new StdClass();
$f->name = sqlite_field_name($this->result[$stack], $i);
$f->table = '';
$f->not_null = 0;
$f->blob = 0;
$f->pkey = 0;
$f->ukey = 0;
$f->mkey = 0;
$f->zerofill = 0;
$f->unsigned = 0;
$f->autoinc = 0;
$f->numeric = 0;
$f->type = 'string';
$fields[] = $f;
$i++;
}
}
return $fields;
}
function &FetchField($fieldOffset = -1)
{
$fld = new ADOFieldObject();
$fld->name = sqlite_field_name($this->_queryID, $fieldOffset);
$fld->type = 'VARCHAR';
$fld->max_length = -1;
return $fld;
}
function field_name($resId = null, $index)
{
if ($resId == null) {
$resId = $this->resId;
}
if (DEBUG) {
$out = sqlite_field_name($resId, $index);
} else {
$out = @sqlite_field_name($resId, $index);
}
return $out;
}
function sqlitem_field_name($result, $index)
{
return sqlite_field_name($result, $index);
}
function field_name($query_id = 0, $field_offset)
{
return $query_id ? @sqlite_field_name($query_id, $field_offset) : false;
}
/**
* Method to fetch fields information of resultset
*
* @return array
*/
public function fetch_fields()
{
$_fields_info = array();
$_f = 0;
$_field_num = sqlite_num_fields($this->res_result);
while ($_f < $_field_num) {
$field_obj = new simbio_sqlite_field_info();
$field_obj->name = sqlite_field_name($this->res_result, $_f);
$field_obj->type = 'VARCHAR';
$_fields_info[] = $field_obj;
$_f++;
}
return $_fields_info;
}
function sql_field_name($sqltype, $hasil, $i)
{
if ($sqltype == 'mysql') {
if (function_exists('mysql_field_name')) {
return mysql_field_name($hasil, $i);
}
} elseif ($sqltype == 'mssql') {
if (function_exists('mssql_field_name')) {
return mssql_field_name($hasil, $i);
} elseif (function_exists('sqlsrv_field_metadata')) {
$metadata = sqlsrv_field_metadata($hasil);
if (is_array($metadata)) {
$metadata = $metadata[$i];
}
if (is_array($metadata)) {
return $metadata['Name'];
}
}
} elseif ($sqltype == 'pgsql') {
if (function_exists('pg_field_name')) {
return pg_field_name($hasil, $i);
}
} elseif ($sqltype == 'oracle') {
if (function_exists('oci_field_name')) {
return oci_field_name($hasil, $i + 1);
}
} elseif ($sqltype == 'sqlite3') {
if (class_exists('SQLite3')) {
return $hasil->columnName($i);
}
} elseif ($sqltype == 'sqlite') {
if (function_exists('sqlite_field_name')) {
return sqlite_field_name($hasil, $i);
}
} elseif ($sqltype == 'odbc') {
if (function_exists('odbc_field_name')) {
return odbc_field_name($hasil, $i + 1);
}
} elseif ($sqltype == 'pdo') {
if (class_exists('PDO')) {
$res = $hasil->getColumnMeta($i);
return $res['name'];
}
}
}
$s_result .= str_replace("__sqltype__", "pgsql", str_replace("__sqlcode__", $sqls, $sqlform_tpl));
$s_result .= "<div>" . $q_result . "</div>";
if ($con) {
pg_close($con);
}
} elseif (isset($_REQUEST['sqlite']) && ($con = sqlite_open($sqlite_file))) {
if (isset($_REQUEST['sqlcode'])) {
$sqls = ss($_REQUEST['sqlcode']);
$querys = explode(";", $sqls);
foreach ($querys as $query) {
if (trim($query) != "") {
$hasil = sqlite_query($con, $query);
if ($hasil) {
$q_result .= "<p style=\"padding:0;margin:20px 6px 0 6px;\">" . $query . "; \r\n\t\t\t\t\t\t<span class=\"gaya\">[</span> ok <span class=\"gaya\">]</span></p>\r\n\t\t\t\t\t\t<table class=\"explore\" style=\"width:99%;\"><tr>";
for ($i = 0; $i < sqlite_num_fields($hasil); $i++) {
$q_result .= "<th>" . htmlspecialchars(sqlite_field_name($hasil, $i)) . "</th>";
}
$q_result .= "</tr>";
while ($rows = sqlite_fetch_array($hasil)) {
$q_result .= "<tr>";
for ($j = 0; $j < sqlite_num_fields($hasil); $j++) {
if ($rows[$j] == "") {
$dataz = " ";
} else {
$dataz = $rows[$j];
}
$q_result .= "<td>" . htmlspecialchars($dataz) . "</td>";
}
$q_result .= "</tr>";
}
$q_result .= "</table>";
///////
sqlite_query("CREATE TABLE strings(foo VARCHAR, bar VARCHAR, baz VARCHAR)", $db);
echo "Buffered\n";
$r = sqlite_query("SELECT * from strings", $db);
echo "num fields: " . sqlite_num_fields($r) . "\n";
for ($i = 0; $i < sqlite_num_fields($r); $i++) {
var_dump(sqlite_field_name($r, $i));
}
echo "Unbuffered\n";
$r = sqlite_unbuffered_query("SELECT * from strings", $db, SQLITE_NUM, $errmsg);
if (!$r) {
var_dump($errmsg);
}
echo "num fields: " . sqlite_num_fields($r) . "\n";
for ($i = 0; $i < sqlite_num_fields($r); $i++) {
var_dump(sqlite_field_name($r, $i));
}
sqlite_exec("DROP TABLE strings", $db);
/////
$data = array(array(0 => 'one', 1 => 'two'), array(0 => 'three', 1 => 'four'));
sqlite_query("CREATE TABLE strings(a VARCHAR, b VARCHAR)", $db);
foreach ($data as $str) {
sqlite_query("INSERT INTO strings VALUES('{$str[0]}','{$str[1]}')", $db);
}
echo "====BUFFERED====\n";
$r = sqlite_query("SELECT a, b from strings", $db);
while (sqlite_valid($r)) {
var_dump(sqlite_current($r, SQLITE_NUM));
var_dump(sqlite_column($r, 0));
var_dump(sqlite_column($r, 1));
var_dump(sqlite_column($r, 'a'));
/**
* Field data
*
* Generates an array of objects containing field meta-data
*
* @return array
*/
public function field_data()
{
$retval = array();
for ($i = 0, $c = $this->num_fields(); $i < $c; $i++) {
$retval[$i] = new stdClass();
$retval[$i]->name = sqlite_field_name($this->result_id, $i);
$retval[$i]->type = NULL;
$retval[$i]->max_length = NULL;
}
return $retval;
}
public function columns()
{
if (empty($this->query)) {
return false;
}
$columns = array();
$num_fields = $this->num_fields();
for ($i = 0; $i < $num_fields; $i++) {
$columns[] = sqlite_field_name($this->query, $i);
}
return $columns;
}
function sql_field_name($sqltype, $result, $i)
{
if ($sqltype == 'mysql') {
if (class_exists('mysqli_result')) {
$z = $result->fetch_field();
return $z->name;
} elseif (function_exists('mysql_field_name')) {
return mysql_field_name($result, $i);
}
} elseif ($sqltype == 'mssql') {
if (function_exists('sqlsrv_field_metadata')) {
$metadata = sqlsrv_field_metadata($result);
if (is_array($metadata)) {
$metadata = $metadata[$i];
}
if (is_array($metadata)) {
return $metadata['Name'];
}
} elseif (function_exists('mssql_field_name')) {
return mssql_field_name($result, $i);
}
} elseif ($sqltype == 'pgsql') {
return pg_field_name($result, $i);
} elseif ($sqltype == 'oracle') {
return oci_field_name($result, $i + 1);
} elseif ($sqltype == 'sqlite3') {
return $result->columnName($i);
} elseif ($sqltype == 'sqlite') {
return sqlite_field_name($result, $i);
} elseif ($sqltype == 'odbc') {
return odbc_field_name($result, $i + 1);
} elseif ($sqltype == 'pdo') {
$res = $result->getColumnMeta($i);
return $res['name'];
}
}
function sql_field_name($s_sqltype, $s_hasil, $s_i)
{
if ($s_sqltype == 'mysql') {
return mysql_field_name($s_hasil, $s_i);
} elseif ($s_sqltype == 'mssql') {
if (function_exists('mssql_field_name')) {
return mssql_field_name($s_hasil, $s_i);
} elseif (function_exists('sqlsrv_field_metadata')) {
$s_metadata = sqlsrv_field_metadata($s_hasil);
if (is_array($s_metadata)) {
$s_metadata = $s_metadata[$s_i];
}
if (is_array($s_metadata)) {
return $s_metadata['Name'];
}
}
} elseif ($s_sqltype == 'pgsql') {
return pg_field_name($s_hasil, $s_i);
} elseif ($s_sqltype == 'oracle') {
return oci_field_name($s_hasil, $s_i + 1);
} elseif ($s_sqltype == 'sqlite3') {
return $s_hasil->columnName($s_i);
} elseif ($s_sqltype == 'sqlite') {
return sqlite_field_name($s_hasil, $s_i);
} elseif ($s_sqltype == 'odbc') {
return odbc_field_name($s_hasil, $s_i + 1);
} elseif ($s_sqltype == 'pdo') {
$s_res = $s_hasil->getColumnMeta($s_i);
return $s_res['name'];
}
}
function query($query)
{
// For reg expressions
$query = str_replace("/[\n\r]/", '', trim($query));
// initialise return
$return_val = 0;
// Flush cached values..
$this->flush();
// Log how the function was called
$this->func_call = "\$db->query(\"{$query}\")";
// Keep track of the last query for debug..
$this->last_query = $query;
// Perform the query via std mysql_query function..
$this->result = @sqlite_query($this->dbh, $query);
$this->count(true, true);
// If there is an error then take note of it..
if (@sqlite_last_error($this->dbh)) {
$err_str = sqlite_error_string(sqlite_last_error($this->dbh));
$this->register_error($err_str);
$this->show_errors ? trigger_error($err_str, E_USER_WARNING) : null;
return false;
}
// Query was an insert, delete, update, replace
if (preg_match("/^(insert|delete|update|replace)\\s+/i", $query)) {
$this->rows_affected = @sqlite_changes($this->dbh);
// Take note of the insert_id
if (preg_match("/^(insert|replace)\\s+/i", $query)) {
$this->insert_id = @sqlite_last_insert_rowid($this->dbh);
}
// Return number fo rows affected
$return_val = $this->rows_affected;
} else {
// Take note of column info
$i = 0;
while ($i < @sqlite_num_fields($this->result)) {
$this->col_info[$i]->name = sqlite_field_name($this->result, $i);
$this->col_info[$i]->type = null;
$this->col_info[$i]->max_length = null;
$i++;
}
// Store Query Results
$num_rows = 0;
while ($row = @sqlite_fetch_array($this->result, SQLITE_ASSOC)) {
// Store relults as an objects within main array
$obj = (object) $row;
//convert to object
$this->last_result[$num_rows] = $obj;
$num_rows++;
}
// Log number of rows the query returned
$this->num_rows = $num_rows;
// Return number of rows selected
$return_val = $this->num_rows;
}
// If debug ALL queries
$this->trace || $this->debug_all ? $this->debug() : null;
return $return_val;
}
function field_info($query, $count)
{
if ($this->debug) {
echo "<pre style=\"color : green\">Getting column information {$this->dbpath} <p style=\"color:purple;\"> {$query} </p></pre>";
}
$nooffields = 0;
//Validate the sql statement and make adjustments
switch ($this->dbtype) {
/* Firebird Functionality */
case "firebird":
//write some things here
$col_info = ibase_field_info($query, $count);
break;
/* SQLite Functionality */
/* SQLite Functionality */
case "sqlite":
putenv("TMP=" . $this->tmppath);
$name = sqlite_field_name($query, $count);
//echo $name;
$col_info["alias"] = $name;
$col_info["name"] = $name;
break;
/* Oracle Functionality */
/* Oracle Functionality */
case "oracle":
$column_name = oci_field_name($query, $count);
$column_type = oci_field_type($query, $count);
$column_size = oci_field_size($query, $count);
$column_prec = oci_field_precision($query, $count);
$column_scale = oci_field_scale($query, $count);
$col_info["name"] = $column_name;
$col_info["alias"] = $column_name;
$col_info["length"] = $column_size;
$col_info["prec"] = $column_prec;
$col_info["type"] = $column_type;
$col_info["scale"] = $column_scale;
break;
/* PGSQL Functionality */
/* PGSQL Functionality */
case "pgsql":
$col_info["name"] = pg_field_name($query, $count);
$col_info["alias"] = NULL;
// always set to NULL
$col_info["relation"] = NULL;
// always set to NULL
$col_info["length"] = pg_field_size($query, $count);
$col_info["type"] = pg_field_type($query, $count);
break;
}
if ($this->debug) {
echo "<pre style=\"color : blue\">Column Info fetched for Column {$count} \n </pre>";
}
return $col_info;
}
/**
* Retrieve the names of columns returned by the DBMS in a query result.
*
* @return mixed Array variable that holds the names of columns as keys
* or an MDB2 error on failure.
* Some DBMS may not return any columns when the result set
* does not contain any rows.
* @access private
*/
function _getColumnNames()
{
$columns = array();
$numcols = $this->numCols();
if (PEAR::isError($numcols)) {
return $numcols;
}
for ($column = 0; $column < $numcols; $column++) {
$column_name = @sqlite_field_name($this->result, $column);
$columns[$column_name] = $column;
}
if ($this->db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
$columns = array_change_key_case($columns, $this->db->options['field_case']);
}
return $columns;
}
/**
* Enter description here...
*
* @param unknown_type $results
*/
function resultSet(&$results)
{
$this->results =& $results;
$this->map = array();
$num_fields = sqlite_num_fields($results);
$index = 0;
$j = 0;
while ($j < $num_fields) {
$columnName = str_replace('"', '', sqlite_field_name($results, $j));
if (strpos($columnName, '.')) {
$parts = explode('.', $columnName);
$this->map[$index++] = array($parts[0], $parts[1]);
} else {
$this->map[$index++] = array(0, $columnName);
}
$j++;
}
}
/**
* Devuelve el nombre de un campo en el resultado de un select
*
* @param int $number
* @param resource $resultQuery
* @return string
*/
function field_name($number, $resultQuery = '')
{
if (!$resultQuery) {
$resultQuery = $this->last_result_query;
if (!$resultQuery) {
return false;
}
}
if (($fieldName = sqlite_field_name($resultQuery, $number)) !== false) {
return $fieldName;
} else {
throw new KumbiaException($this->error());
}
}
function getFieldInfo($stack = 0)
{
$fields = array();
$i = 0;
while ($i < sqlite_num_fields($this->result[$stack])) {
$meta = false;
//sqlite_fetch_field($this->result[$stack], $i);
//@todo: properly fill structure here like dbex class
if ($meta) {
$f = new StdClass();
$type = sqlite_field_type($this->result[$stack], $i);
$f->name = $meta->name;
$f->table = $meta->table;
$f->not_null = $meta->not_null;
$f->blob = $meta->blob;
$f->pkey = $meta->primary_key;
$f->ukey = $meta->unique_key;
$f->mkey = $meta->multiple_key;
$f->zerofill = $meta->zerofill;
$f->unsigned = $meta->unsigned;
$f->autoinc = 0;
//($meta->flags & AUTO_INCREMENT_FLAG) ? 1 : 0;
$f->numeric = $meta->numeric;
$f->type = $type == 'string' ? 'text' : 'binary';
$fields[] = $f;
} else {
$f = new StdClass();
$type = 'string';
$f->name = sqlite_field_name($this->result[$stack], $i);
$f->table = '';
$f->not_null = 0;
$f->blob = 0;
$f->pkey = 0;
$f->ukey = 0;
$f->mkey = 0;
$f->zerofill = 0;
$f->unsigned = 0;
$f->autoinc = 0;
$f->numeric = 0;
$f->type = $type == 'string' ? 'text' : 'binary';
$fields[] = $f;
}
$i++;
}
return $fields;
}
