OK the last update, for now, until someone who knows what they are
doing can verify what I've done.

This one is completing all 16 conformance tests. And now supports
persistent connections which I should have done first being as I'm
running PHP as an apache module :(

It requires the addition of this function to 'manager_sqlite.php'
which is meant to stop the duplication of the sqlite native PRIMARY
KEY definition for autoincrement.

I'm off to play with MetaStorage now :)

Regards John

// snip

Function

GetTableFieldsAndOptions(&$db,&$table,&$sql,&$options)
{
$options="";
if(!$this->GetFieldList($db,$table["FIELDS"],$sql))
return(0);
if(IsSet($table["PRIMARYKEY"]))
{
if(!$this->GetPrimaryKeyDeclaration($db,$table["PRIMARYKEY"],$key))
return(0);
$sql.=" ";
}
return(1);
}

// snip


'metabase_sqlite.php'


<?php
if(!defined("METABASE_SQLITE_INCLUDED"))
{
define("METABASE_SQLITE_INCLUDED",1);

/*
* metabase_sqlite.php
*
* @(#) $Header: /home/mlemos/cvsroot/metabase/metabase_sqlite.php,v
1.3 2004/07/27 06:26:03 mlemos Exp $
* @author Jeroen Derks <jeroen@...>
* @adapted for PHP extension by John Walton <admin@...>
*/

class metabase_sqlite_class extends metabase_database_class
{
var $connection=0;
var $connected_database_file;
var $decimal_factor=1.0;
var $results=array();
var $highest_fetched_row=array();
var $columns=array();
var $escape_quotes="'";
var $sequence_prefix="_sequence_";
var $manager_class_name="metabase_manager_sqlite_class";
var $manager_include="manager_sqlite.php";
var $manager_included_constant="METABASE_MANAGER_SQLITE_INCLUDED";
var $base_transaction_name="___php_metabase_sqlite_auto_commit_off";
var $fixed_float=0;
var $select_queries=array(
"select"=>"",
"show"=>"",
"explain"=>""
);

Function GetDatabaseFile($database_name)
{
$database_path=(IsSet($this->options["DatabasePath"]) ?
$this->options["DatabasePath"] : "");
$database_extension=(IsSet($this->options["DatabaseExtension"])
? $this->options["DatabaseExtension"] : ".db");
return($database_path.$database_name.$database_extension);
}

Function Connect()
{
$database_file=$this->GetDatabaseFile($this->database_name);
if($this->connection!=0)
{
if (!strcmp($this->connected_database_file,$database_file))
return(1);
sqlite_close($this->connection);
$this->connection=0;
$this->affected_rows=-1;
}
if(!function_exists('sqlite_open'))
return($this->SetError("Connect","SQLite support is not
available in this PHP configuration"));
if(!@file_exists($database_file))
return($this->SetError("Connect","database does not exist"));
if($this->persistent==1){
if(($this->connection=@sqlite_popen($database_file))==0)
return($this->SetError("Connect",IsSet($php_errormsg) ?
$php_errormsg : "Could not open SQLite database"));
}else{
if(($this->connection=@sqlite_open($database_file))==0)
return($this->SetError("Connect",IsSet($php_errormsg) ?
$php_errormsg : "Could not open SQLite database"));
}
if(IsSet($this->supported["Transactions"])
&& !$this->auto_commit)
{
$this->Debug("Query: BEGIN TRANSACTION
$this->base_transaction_name");
if(!@sqlite_query("BEGIN TRANSACTION
$this->base_transaction_name;",$this->connection))
{
sqlite_close($this->connection);
$this->connection=0;
$this->affected_rows=-1;
return($this->SetError("Connect",IsSet($php_errormsg) ?
$php_errormsg : "Could not start transaction"));
}
$this->RegisterTransactionShutdown(0);
}
$this->connected_database_file=$database_file;
return(1);
}

Function Close()
{
if($this->connection!=0)
{
if(IsSet($this->supported["Transactions"])
&& !$this->auto_commit)
$this->AutoCommitTransactions(1);
sqlite_close($this->connection);
$this->connection=0;
$this->affected_rows=-1;
}
}

Function Query($query)
{
$this->Debug("Query: $query");
$first=$this->first_selected_row;
$limit=$this->selected_row_limit;
$this->first_selected_row=$this->selected_row_limit=0;
if(!strcmp($this->database_name,""))
return($this->SetError("Query","it was not specified a valid
database name to select"));
if(!$this->Connect())
return(0);
$query_string=strtolower(strtok(ltrim($query)," \t\n\r"));
if(($select=IsSet($this->select_queries[$query_string]))
&& $limit>0)
$query.=" LIMIT $limit OFFSET $first";
if(($result=@sqlite_query($query.';',$this->connection)))
{
if($select){
switch(GetType($result))
{
case "resource":
case "integer":
$this->highest_fetched_row[$result]=-1;
break;
default:

$error=sqlite_error_string(sqlite_last_error($this->connection));
return($this->SetError("Query","this select query did not
return valid result set value: ".$query.(strlen($error) ? "
(".$error.")" : "")));
}
}
else
$this->affected_rows=sqlite_changes($this->connection);
UnSet($this->columns[$result]);
}
else
return($this->SetError("Query",$php_errormsg));
return($result);
}

Function Replace($table,&$fields)
{
$count=count($fields);

for($keys=0,$query=$values="",Reset($fields),$field=0;$field<$count;Next($fields\
),$field++)
{
$name=Key($fields);
if($field>0)
{
$query.=",";
$values.=",";
}
$query.=$name;
if(IsSet($fields[$name]["Null"])
&& $fields[$name]["Null"])
$value="NULL";
else
{
if(!IsSet($fields[$name]["Value"]))
return($this->SetError("Replace","it was not specified a value
for the $name field"));
switch(IsSet($fields[$name]["Type"]) ? $fields[$name]["Type"]
: "text")
{
case "text":
$value=$this->GetTextFieldValue($fields[$name]["Value"]);
break;
case "boolean":
$value=$this->GetBooleanFieldValue($fields[$name]["Value"]);
break;
case "integer":
$value=strval($fields[$name]["Value"]);
break;
case "decimal":
$value=$this->GetDecimalFieldValue($fields[$name]["Value"]);
break;
case "float":
$value=$this->GetFloatFieldValue($fields[$name]["Value"]);
break;
case "date":
$value=$this->GetDateFieldValue($fields[$name]["Value"]);
break;
case "time":
$value=$this->GetTimeFieldValue($fields[$name]["Value"]);
break;
case "timestamp":
$value=$this->GetTimestampFieldValue($fields[$name]["Value"]);
break;
default:
return($this->SetError("Replace","it was not specified a
supported type for the $name field"));
}
}
$values.=$value;
if(IsSet($fields[$name]["Key"])
&& $fields[$name]["Key"])
{
if($value=="NULL")
return($this->SetError("Replace","key values may not be NULL"));
$keys++;
}
}
if($keys==0)
return($this->SetError("Replace","it were not specified which
fields are keys"));
return($this->Query("REPLACE INTO $table ($query) VALUES($values)"));
}

Function EndOfResult($result)
{
if(!IsSet($this->highest_fetched_row[$result]))
{
$this->SetError("End of result","attempted to check the end of
an unknown result");
return(-1);
}

return($this->highest_fetched_row[$result]>=$this->NumberOfRows($result)-1);
}
Function Fetch($result)
{
if(GetType($result)=="boolean")
{
if(!$result)
return($this->SetError("Fetch result array","invalid result set"));
$this->results[$result]=array();
return($result);
}
else
{
if(!IsSet($this->results[$result]))
$this->results[$result]=@sqlite_fetch_all($result);
if(GetType($this->results[$result])!="array")
return($this->SetError("Fetch result array",IsSet($php_errormsg)
? $php_errormsg : "could not fetch the query results"));
}
return(1);
}

//
Function FetchResult($result,$row,$field)
{
if(($column=$this->GetColumn($result,$field))==-1 |
!$this->Fetch($result))
return("");

$this->highest_fetched_row[$result]=max($this->highest_fetched_row[$result],$row\
);
return($this->results[$result][$row][$column]);
}

Function FetchResultArray($result,&$array,$row)
{
if(!sqlite_seek($result,$row)
|| !($array=sqlite_fetch_array($result)))
return($this->SetError("Fetch result
array",sql_last_error($this->connection)));

$this->highest_fetched_row[$result]=max($this->highest_fetched_row[$result],$row\
);
return($this->ConvertResultRow($result,$array));
}

Function FetchCLOBResult($result,$row,$field)
{
return($this->FetchLOBResult($result,$row,$field));
}

Function FetchBLOBResult($result,$row,$field)
{
return($this->FetchLOBResult($result,$row,$field));
}

Function ConvertResult(&$value,$type)
{
switch($type)
{
case METABASE_TYPE_BOOLEAN:
$value=(strcmp($value,"Y") ? 0 : 1);
return(1);
case METABASE_TYPE_DECIMAL:

$value=sprintf("%.".$this->decimal_places."f",doubleval($value)/$this->decimal_f\
actor);
return(1);
case METABASE_TYPE_FLOAT:
$value=doubleval($value);
return(1);
case METABASE_TYPE_DATE:
case METABASE_TYPE_TIME:
case METABASE_TYPE_TIMESTAMP:
return(1);
default:
return($this->BaseConvertResult($value,$type));
}
}

Function NumberOfRows($result)
{
return sqlite_num_rows($result);
}

Function FreeResult(&$result)
{
UnSet($this->highest_fetched_row[$result]);
UnSet($this->columns[$result]);
UnSet($this->result_types[$result]);
UnSet($this->results[$result]);
return(1);
}

Function GetCLOBFieldTypeDeclaration($name,&$field)
{
if(IsSet($field["length"]))
{
$length=$field["length"];
if($length<=255)
$type="TINYTEXT";
else
{
if($length<=65535)
$type="TEXT";
else
{
if($length<=16777215)
$type="MEDIUMTEXT";
else
$type="LONGTEXT";
}
}
}
else
$type="LONGTEXT";
return("$name $type".(IsSet($field["notnull"]) ? " NOT NULL" : ""));
}

Function GetBLOBFieldTypeDeclaration($name,&$field)
{
if(IsSet($field["length"]))
{
$length=$field["length"];
if($length<=255)
$type="TINYBLOB";
else
{
if($length<=65535)
$type="BLOB";
else
{
if($length<=16777215)
$type="MEDIUMBLOB";
else
$type="LONGBLOB";
}
}
}
else
$type="LONGBLOB";
return("$name $type".(IsSet($field["notnull"]) ? " NOT NULL" : ""));
}

Function GetIntegerFieldTypeDeclaration($name,&$field)
{
if($field["autoincrement"]){
$fld=1;
return("$name "." INTEGER PRIMARY
KEY".(IsSet($field["default"]) ? " DEFAULT ".$fld :
"").(IsSet($field["notnull"]) ? " NOT NULL" : ""));
}
else
{
return("$name ".(IsSet($field["unsigned"]) ? "INT UNSIGNED" :
"INT").(IsSet($field["default"]) ? " DEFAULT ".$field["default"] :
"").(IsSet($field["notnull"]) ? " NOT NULL" : ""));
}
}
Function GetDateFieldTypeDeclaration($name,&$field)
{
return($name." DATE".(IsSet($field["default"]) ? " DEFAULT
'".$field["default"]."'" : "").(IsSet($field["notnull"]) ? " NOT NULL"
: ""));
}

Function GetTimestampFieldTypeDeclaration($name,&$field)
{
return($name." DATETIME".(IsSet($field["default"]) ? " DEFAULT
'".$field["default"]."'" : "").(IsSet($field["notnull"]) ? " NOT NULL"
: ""));
}

Function GetTimeFieldTypeDeclaration($name,&$field)
{
return($name." TIME".(IsSet($field["default"]) ? " DEFAULT
'".$field["default"]."'" : "").(IsSet($field["notnull"]) ? " NOT NULL"
: ""));
}

Function GetFloatFieldTypeDeclaration($name,&$field)
{
if(IsSet($this->options["FixedFloat"]))
$this->fixed_float=$this->options["FixedFloat"];
else
{
if($this->connection==0)
$this->Connect();
}
return("$name DOUBLE".($this->fixed_float ?
"(".($this->fixed_float+2).",".$this->fixed_float.")" :
"").(IsSet($field["default"]) ? " DEFAULT
".$this->GetFloatFieldValue($field["default"]) :
"").(IsSet($field["notnull"]) ? " NOT NULL" : ""));
}

Function GetDecimalFieldTypeDeclaration($name,&$field)
{
return("$name BIGINT".(IsSet($field["default"]) ? " DEFAULT
".$this->GetDecimalFieldValue($field["default"]) :
"").(IsSet($field["notnull"]) ? " NOT NULL" : ""));
}

Function GetCLOBFieldValue($prepared_query,$parameter,$clob,&$value)
{
for($value="'";!MetabaseEndOfLOB($clob);)
{
if(MetabaseReadLOB($clob,$data,$this->lob_buffer_length)<0)
{
$value="";
return($this->SetError("Get CLOB field
value",MetabaseLOBError($clob)));
}
$this->EscapeText($data);
$value.=$data;
}
$value.="'";
return(1);
}

Function FreeCLOBValue($prepared_query,$clob,&$value,$success)
{
Unset($value);
}

Function GetBLOBFieldValue($prepared_query,$parameter,$blob,&$value)
{
for($value="'";!MetabaseEndOfLOB($blob);)
{
if(!MetabaseReadLOB($blob,$data,$this->lob_buffer_length))
{
$value="";
return($this->SetError("Get BLOB field
value",MetabaseLOBError($blob)));
}
$value.=sqlite_udf_encode_binary($data);
}
$value.="'";
return(1);
}

Function FreeBLOBValue($prepared_query,$blob,&$value,$success)
{
Unset($value);
}

Function GetFloatFieldValue($value)
{
return(!strcmp($value,"NULL") ? "NULL" : "$value");
}

Function GetDecimalFieldValue($value)
{
return(!strcmp($value,"NULL") ? "NULL" :
strval(round(doubleval($value)*$this->decimal_factor)));
}

Function GetColumnNames($result,&$column_names)
{
$result_value=intval($result);
if(!IsSet($this->highest_fetched_row[$result_value]))
return($this->SetError("Get column names","it was specified an
inexisting result set"));
if(!IsSet($this->columns[$result_value]))
{
$this->columns[$result_value]=array();
$columns=sqlite_num_fields($result);
for($column=0;$column<$columns;$column++)

$this->columns[$result_value][strtolower(sqlite_field_name($result,$column))]=$c\
olumn;
}
$column_names=$this->columns[$result_value];
return(1);
}

Function GetColumn($result,$field)
{
if(!$this->GetColumnNames($result,$column_names))
return(-1);
if(GetType($field)=="integer")
{
if(($column=$field)<0
|| $column>=count($this->columns[$result]))
{
$this->SetError("Get column","attempted to fetch an query
result column out of range");
return(-1);
}
}
else
{
$name=strtolower($field);
if(!IsSet($this->columns[$result][$name]))
{
$this->SetError("Get column","attempted to fetch an unknown
query result column");
return(-1);
}
$column=$this->columns[$result][$name];
}
return($column);
}

Function NumberOfColumns($result)
{
if(!IsSet($this->highest_fetched_row[intval($result)]))
{
$this->SetError("Get number of columns","it was specified an
inexisting result set");
return(-1);
}
return(sqlite_num_fields($result));
}

Function GetSequenceNextValue($name,&$value)
{
$sequence_name=$this->sequence_prefix.$name;
if(!($result=$this->Query("INSERT INTO $sequence_name (sequence)
VALUES (NULL)")))
return(0);
$value=intval(sqlite_last_insert_rowid($this->connection));
if(!$this->Query("UPDATE $sequence_name SET sequence=$value
WHERE ROWID=$value"))
return(0);
if(!$this->Query("DELETE FROM $sequence_name WHERE
sequence<$value"))
$this->warning="could delete previous sequence table values";
return(1);
}

Function GetNextKey($table,&$key)
{
$key="NULL";
return(1);
}

Function GetInsertedKey($table,&$value)
{
$value=intval(sqlite_last_insert_rowid($this->connection));
return(1);
}


Function AutoCommitTransactions($auto_commit)
{
$this->Debug("AutoCommit: ".($auto_commit ? "On" : "Off"));
if(!IsSet($this->supported["Transactions"]))
return($this->SetError("Auto-commit transactions","transactions
are not in use"));
if(((!$this->auto_commit)==(!$auto_commit)))
return(1);
if($this->connection)
{
if($auto_commit)
{
if(!$this->Query("END TRANSACTION
$this->base_transaction_name"))
return(0);
}
else
{
if(!$this->Query("BEGIN TRANSACTION
$this->base_transaction_name"))
return(0);
}
}
$this->auto_commit=$auto_commit;
return($this->RegisterTransactionShutdown($auto_commit));
}

Function CommitTransaction()
{
$this->Debug("Commit Transaction");
if(!IsSet($this->supported["Transactions"]))
return($this->SetError("Commit transaction","transactions are
not in use"));
if($this->auto_commit)
return($this->SetError("Commit transaction","transaction changes
are being auto commited"));
if(!$this->Query("COMMIT TRANSACTION $this->base_transaction_name"))
return(0);
return($this->Query("BEGIN TRANSACTION
$this->base_transaction_name"));
}

Function RollbackTransaction()
{
$this->Debug("Rollback Transaction");
if(!IsSet($this->supported["Transactions"]))
return($this->SetError("Rollback transaction","transactions are
not in use"));
if($this->auto_commit)
return($this->SetError("Rollback transaction","transactions can
not be rolled back when changes are auto commited"));
if(!$this->Query("ROLLBACK TRANSACTION
$this->base_transaction_name"))
return(0);
return($this->Query("BEGIN TRANSACTION
$this->base_transaction_name"));
}

Function Setup()
{
$this->supported["Sequences"]=
$this->supported["Indexes"]=
$this->supported["AffectedRows"]=
$this->supported["SummaryFunctions"]=
$this->supported["OrderByText"]=
$this->supported["GetSequenceCurrentValue"]=
$this->supported["SelectRowRanges"]=
$this->supported["Transactions"]=
$this->supported["LOBs"]=
$this->supported["Replace"]=
$this->supported["AutoIncrement"]=
$this->supported["PrimaryKey"]=
$this->supported["OmitInsertKey"]=
1;
$this->decimal_factor=pow(10.0,$this->decimal_places);
return("");
}
};
}
?>