Loading ...
Sorry, an error occurred while loading the content.
 

Re: PHP Extension SQLite Driver latest

Expand Messages
  • jonbouyw
    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
    Message 1 of 10 , Nov 1, 2005
      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_factor);
      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))]=$column;
      }
      $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("");
      }
      };
      }
      ?>
    • Manuel Lemos
      Hello, ... I think an hack of the base manager driver class may be needed. I ll take a look at that. ... I am not sure yet if relying on UDF is a good idea. I
      Message 2 of 10 , Nov 1, 2005
        Hello,

        on 11/01/2005 01:01 AM jonbouyw said the following:
        > OK this latest revision is running 14 out of the 16 tests!!!
        >
        > I had to turn of PRIMARY key support in order to stop it duplicating
        > the PRIMARY key when setting the autoincrement field to type INTEGER
        > PRIMARY KEY.

        I think an hack of the base manager driver class may be needed. I'll
        take a look at that.


        > Because the SQLite API is binary unsafe it's still falling over on
        > LOB's. I should be able to use the PHP sqlite_udf_encode_binary() and
        > sqlite_udf_decode_binary() functions to counter this but I'm not sure
        > how best to include those functions in the driver.
        >
        > Being as using LOB storage for files isn't an issue for me I'm not
        > that bothered, but it would be nice to to have it complete now the
        > autoincrement is working!!!

        I am not sure yet if relying on UDF is a good idea. I need to check what
        is the matter.

        --

        Regards,
        Manuel Lemos

        PHP Classes - Free ready to use OOP components written in PHP
        http://www.phpclasses.org/

        PHP Reviews - Reviews of PHP books and other products
        http://www.phpclasses.org/reviews/

        Metastorage - Data object relational mapping layer generator
        http://www.meta-language.net/metastorage.html
      • Manuel Lemos
        Hello, ... Ok, can you please upload all your files here? http://groups.yahoo.com/group/metabase-dev/files/beta/ ... Great. The survey shows there is a
        Message 3 of 10 , Nov 1, 2005
          Hello,


          on 11/01/2005 05:42 PM jonbouyw said the following:
          > 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.

          Ok, can you please upload all your files here?

          http://groups.yahoo.com/group/metabase-dev/files/beta/


          > I'm off to play with MetaStorage now :)

          Great. The survey shows there is a significant demand from Metastorage
          users to use SQLite.

          BTW, new Metastorage release is out. Check here if you did not know:

          http://www.meta-language.net/news-2005-11-02-metastorage.html

          --

          Regards,
          Manuel Lemos

          PHP Classes - Free ready to use OOP components written in PHP
          http://www.phpclasses.org/

          PHP Reviews - Reviews of PHP books and other products
          http://www.phpclasses.org/reviews/

          Metastorage - Data object relational mapping layer generator
          http://www.meta-language.net/metastorage.html
        • Manuel Lemos
          Hello, ... I am finally integrating your update of the SQLite driver under PHP 4.3.11 using the extension from PECL. I am experiencing a few glitches with the
          Message 4 of 10 , Nov 16, 2005
            Hello,

            on 11/01/2005 05:42 PM jonbouyw said the following:
            > 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 am finally integrating your update of the SQLite driver under PHP
            4.3.11 using the extension from PECL.

            I am experiencing a few glitches with the driver test script. Right
            after trying to create tables for the articles and files tables the
            script attempts to delete all the rows in those tables. However, it is
            failing saying that the table does not exist. If I restart the script it
            succeeds in deleting the rows . Do you have any idea regarding why
            this happens?

            Another matter, why do you touch the database file and test whether it
            is readable and writable before creating the database file? Is it to
            test whether you can write to the database file directory?

            --

            Regards,
            Manuel Lemos

            Metastorage - Data object relational mapping layer generator
            http://www.metastorage.net/

            PHP Classes - Free ready to use OOP components written in PHP
            http://www.phpclasses.org/
          • jonbouyw
            ... script it ... No I have no idea why that happens. I m am aware of it though, strangely it runs right through the test script, first time, when using a
            Message 5 of 10 , Nov 18, 2005
              --- In metabase-dev@yahoogroups.com, Manuel Lemos <mlemos@a...> wrote:
              >
              > Hello,
              >
              > on 11/01/2005 05:42 PM jonbouyw said the following:
              > > 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 am finally integrating your update of the SQLite driver under PHP
              > 4.3.11 using the extension from PECL.
              >
              > I am experiencing a few glitches with the driver test script. Right
              > after trying to create tables for the articles and files tables the
              > script attempts to delete all the rows in those tables. However, it is
              > failing saying that the table does not exist. If I restart the
              script it
              > succeeds in deleting the rows . Do you have any idea regarding why
              > this happens?
              >
              No I have no idea why that happens.

              I'm am aware of it though, strangely it runs right through the test
              script, first time, when using a persistent connection without those
              problems. But it doesn't let go of the persistent connection to the
              resultant database. Which is probably related to the non persistent
              connection problem.

              It's strange though, even with a non-persistent connection, because
              after repeating the script a couple of times it works fine.

              Apologies, I would have replied sooner but I have spent the last few
              days in hospital.

              > Another matter, why do you touch the database file and test whether it
              > is readable and writable before creating the database file? Is it to
              > test whether you can write to the database file directory?
              >

              Yes it's just another layer to assure that file permissions are set
              (or can be set) correctly on the server, thereby giving the user a
              better clue why they are unable to create a database. It's just a
              habit I've got into with SQLite databases.

              > --
              >
              > Regards,
              > Manuel Lemos
              >
              > Metastorage - Data object relational mapping layer generator
              > http://www.metastorage.net/
              >
              > PHP Classes - Free ready to use OOP components written in PHP
              > http://www.phpclasses.org/
              >
            • Manuel Lemos
              Hello, ... I figured that the problem is that when you change the schema of an SQLite database, existing database connections are not made aware. The driver
              Message 6 of 10 , Nov 18, 2005
                Hello,

                on 11/18/2005 03:39 PM jonbouyw said the following:
                >> I am experiencing a few glitches with the driver test script. Right
                >> after trying to create tables for the articles and files tables the
                >> script attempts to delete all the rows in those tables. However, it is
                >> failing saying that the table does not exist. If I restart the
                > script it
                >> succeeds in deleting the rows . Do you have any idea regarding why
                >> this happens?
                >>
                > No I have no idea why that happens.
                >
                > I'm am aware of it though, strangely it runs right through the test
                > script, first time, when using a persistent connection without those
                > problems. But it doesn't let go of the persistent connection to the
                > resultant database. Which is probably related to the non persistent
                > connection problem.
                >
                > It's strange though, even with a non-persistent connection, because
                > after repeating the script a couple of times it works fine.

                I figured that the problem is that when you change the schema of an
                SQLite database, existing database connections are not made aware.

                The driver test script may add tables for testing auto-increment and
                BLOBs. The connection that tests these features is not aware right away.

                The solution was to change the driver test script to close and reopen
                the database connection after a schema change. It works now.


                > Apologies, I would have replied sooner but I have spent the last few
                > days in hospital.

                Oh, I am sorry to hear. I hope you are well now.


                >> Another matter, why do you touch the database file and test whether it
                >> is readable and writable before creating the database file? Is it to
                >> test whether you can write to the database file directory?
                >>
                >
                > Yes it's just another layer to assure that file permissions are set
                > (or can be set) correctly on the server, thereby giving the user a
                > better clue why they are unable to create a database. It's just a
                > habit I've got into with SQLite databases.

                I think a single is_writable call to check whether the database
                directory would be sufficient. Anyway, that is ok too.

                I just added an option to set the file access mode. Setting to 0666 by
                default is dangerous as it may make the database file easy to change or
                steal by other users in an shared environment. I made the default 0640.
                0600 would be safer but then the Web server may not be able to read the
                file.

                I also added support to use composite primary keys. The way you had
                assumed that only the auto-increment field could be a primary key.


                I will be documenting these changes soon. For now you can access the
                latest version the MetaL CVS repository:

                http://www.meta-language.net/download.html#cvs


                --

                Regards,
                Manuel Lemos

                Metastorage - Data object relational mapping layer generator
                http://www.metastorage.net/

                PHP Classes - Free ready to use OOP components written in PHP
                http://www.phpclasses.org/
              • jonbouyw
                ... Yes, the alternative as I understand it would be to insert a VACUUM on the database in between, but I think it is far more efficient to close and
                Message 7 of 10 , Nov 18, 2005
                  --- In metabase-dev@yahoogroups.com, Manuel Lemos <mlemos@a...> wrote:
                  >
                  > Hello,
                  >
                  > on 11/18/2005 03:39 PM jonbouyw said the following:

                  >
                  > I figured that the problem is that when you change the schema of an
                  > SQLite database, existing database connections are not made aware.
                  >
                  > The driver test script may add tables for testing auto-increment and
                  > BLOBs. The connection that tests these features is not aware right away.
                  >
                  > The solution was to change the driver test script to close and reopen
                  > the database connection after a schema change. It works now.

                  Yes, the alternative as I understand it would be to insert a 'VACUUM'
                  on the database in between, but I think it is far more efficient to
                  close and re-open.

                  >
                  >
                  > > Apologies, I would have replied sooner but I have spent the last few
                  > > days in hospital.
                  >
                  > Oh, I am sorry to hear. I hope you are well now.

                  I'm fine thanx :D
                  >
                  >
                  > >> Another matter, why do you touch the database file and test
                  whether it
                  > >> is readable and writable before creating the database file? Is it to
                  > >> test whether you can write to the database file directory?
                  > >>
                  > >
                  > > Yes it's just another layer to assure that file permissions are set
                  > > (or can be set) correctly on the server, thereby giving the user a
                  > > better clue why they are unable to create a database. It's just a
                  > > habit I've got into with SQLite databases.
                  >
                  > I think a single is_writable call to check whether the database
                  > directory would be sufficient. Anyway, that is ok too.
                  >
                  > I just added an option to set the file access mode. Setting to 0666 by
                  > default is dangerous as it may make the database file easy to change or
                  > steal by other users in an shared environment. I made the default 0640.
                  > 0600 would be safer but then the Web server may not be able to read the
                  > file.

                  The implementation is currently set to the default PHP mode, as per
                  documentation. http://www.php.net/manual/en/function.sqlite-open.php
                  of course CHMOD to whatever suits your security needs.

                  Personally I keep my databases below the doc root if they require
                  extra security.
                  >
                  > I also added support to use composite primary keys. The way you had
                  > assumed that only the auto-increment field could be a primary key.
                  >
                  Great stuff, I just implemented a clumsy hack just to get it working,
                  I really appreciate your input on this.

                  >
                  > I will be documenting these changes soon. For now you can access the
                  > latest version the MetaL CVS repository:
                  >
                  > http://www.meta-language.net/download.html#cvs
                  >
                  >
                  > --
                  >
                  > Regards,
                  > Manuel Lemos
                  >
                  > Metastorage - Data object relational mapping layer generator
                  > http://www.metastorage.net/
                  >
                  > PHP Classes - Free ready to use OOP components written in PHP
                  > http://www.phpclasses.org/
                  >
                • Manuel Lemos
                  Hello, ... I am not sure if a VACUUM would notify the other connections. Anyway, that is documented now in the driver notes. ... No problem. I also implemented
                  Message 8 of 10 , Nov 19, 2005
                    Hello,

                    on 11/18/2005 09:12 PM jonbouyw said the following:
                    >> I figured that the problem is that when you change the schema of an
                    >> SQLite database, existing database connections are not made aware.
                    >>
                    >> The driver test script may add tables for testing auto-increment and
                    >> BLOBs. The connection that tests these features is not aware right away.
                    >>
                    >> The solution was to change the driver test script to close and reopen
                    >> the database connection after a schema change. It works now.
                    >
                    > Yes, the alternative as I understand it would be to insert a 'VACUUM'
                    > on the database in between, but I think it is far more efficient to
                    > close and re-open.

                    I am not sure if a VACUUM would notify the other connections. Anyway,
                    that is documented now in the driver notes.


                    >> I also added support to use composite primary keys. The way you had
                    >> assumed that only the auto-increment field could be a primary key.
                    >>
                    > Great stuff, I just implemented a clumsy hack just to get it working,
                    > I really appreciate your input on this.

                    No problem. I also implemented support for table renaming and column
                    adding. That will only work when SQLite 3 library is used with the PHP
                    distribution.

                    Anyway, I just updated the documentation adding your name and e-mail
                    address to the credits. Please take a look and let me know if it is ok
                    for you, as I am about to make a new release:

                    http://www.meta-language.net/metabase.html

                    --

                    Regards,
                    Manuel Lemos

                    Metastorage - Data object relational mapping layer generator
                    http://www.metastorage.net/

                    PHP Classes - Free ready to use OOP components written in PHP
                    http://www.phpclasses.org/
                  • jonbouyw
                    ... Hi Manuel I ve just tested the latest CVS version and it s working great apart from when running for the first time with a persistent connection. PHP
                    Message 9 of 10 , Nov 20, 2005
                      --- In metabase-dev@yahoogroups.com, Manuel Lemos <mlemos@a...> wrote:
                      Hi Manuel

                      I've just tested the latest CVS version and it's working great apart
                      from when running for the first time with a persistent connection.
                      PHP throws up this warning:

                      Warning: sqlite_close(): 244 is not a valid sqlite database resource
                      in c:\Dev\htdocs\public\docs\metabase\metabase_sqlite.php on line 89
                      And still I have to restart Apache to be able to delete the resultant
                      file after using a persistent connection.

                      I'm a bit chained to my development environment, due to other projects
                      I'm working on at the moment, which is PHP 5.0.5 running on Apache
                      1.3.3 as a module, so its hard for me to check this on different
                      setups presently.

                      >
                      > Hello,
                      >
                      > on 11/18/2005 09:12 PM jonbouyw said the following:
                      > >> I figured that the problem is that when you change the schema of an
                      > >> SQLite database, existing database connections are not made aware.
                      > >>
                      > >> The driver test script may add tables for testing auto-increment and
                      > >> BLOBs. The connection that tests these features is not aware
                      right away.
                      > >>
                      > >> The solution was to change the driver test script to close and
                      reopen
                      > >> the database connection after a schema change. It works now.
                      > >
                      > > Yes, the alternative as I understand it would be to insert a 'VACUUM'
                      > > on the database in between, but I think it is far more efficient to
                      > > close and re-open.
                      >
                      > I am not sure if a VACUUM would notify the other connections. Anyway,
                      > that is documented now in the driver notes.
                      >
                      >
                      > >> I also added support to use composite primary keys. The way you had
                      > >> assumed that only the auto-increment field could be a primary key.
                      > >>
                      > > Great stuff, I just implemented a clumsy hack just to get it working,
                      > > I really appreciate your input on this.
                      >
                      > No problem. I also implemented support for table renaming and column
                      > adding. That will only work when SQLite 3 library is used with the PHP
                      > distribution.

                      This is great I've seen the source and it's given me the framework to
                      add the functions to support renaming and column alterations for the
                      2.8.x library, when I get the time to implement them.

                      >
                      > Anyway, I just updated the documentation adding your name and e-mail
                      > address to the credits. Please take a look and let me know if it is ok
                      > for you, as I am about to make a new release:
                      >

                      I feel honoured by that, thank you ;)

                      > http://www.meta-language.net/metabase.html
                      >
                      > --
                      >
                      > Regards,
                      > Manuel Lemos
                      >
                      > Metastorage - Data object relational mapping layer generator
                      > http://www.metastorage.net/
                      >
                      > PHP Classes - Free ready to use OOP components written in PHP
                      > http://www.phpclasses.org/
                      >
                    • Manuel Lemos
                      Hello, ... Maybe that is because closing persistent connections should not make any effect. I changed the driver class to not call sqlite_close when closing a
                      Message 10 of 10 , Nov 20, 2005
                        Hello,

                        on 11/20/2005 12:12 PM jonbouyw said the following:
                        > I've just tested the latest CVS version and it's working great apart
                        > from when running for the first time with a persistent connection.
                        > PHP throws up this warning:
                        >
                        > Warning: sqlite_close(): 244 is not a valid sqlite database resource
                        > in c:\Dev\htdocs\public\docs\metabase\metabase_sqlite.php on line 89

                        Maybe that is because closing persistent connections should not make any
                        effect. I changed the driver class to not call sqlite_close when closing
                        a setup to a database in persistent connection mode.


                        > And still I have to restart Apache to be able to delete the resultant
                        > file after using a persistent connection.

                        I think that is a consequence of keeping persistent connections. The
                        files are kept closed and locked.

                        I do not see much benefit in keeping persistent connections with SQLite
                        because the accesses are not even authenticated, so the reconnection
                        overhead should be neglectable. Anyway, this does not seem to be a
                        Metabase or even a PHP specific problem.

                        --

                        Regards,
                        Manuel Lemos

                        Metastorage - Data object relational mapping layer generator
                        http://www.metastorage.net/

                        PHP Classes - Free ready to use OOP components written in PHP
                        http://www.phpclasses.org/
                      Your message has been successfully submitted and would be delivered to recipients shortly.