Hi - I'm interested in extending the metabase class so I can add a
wrapper around the Query function. I'd like to record all sql queries
that are run by my application before executing them.

However - since metabase is setup using a function
(MetabaseSetupDatabaseObject) instead of a new declaration I'm not
sure how to extend it without tinkering with the distributed code. Is
this possible?

Thanks,
Jamie

Hello,

On 06/07/2004 10:40 PM, jamiescottmcclelland wrote:
> Hi - I'm interested in extending the metabase class so I can add a
> wrapper around the Query function. I'd like to record all sql queries
> that are run by my application before executing them.
>
> However - since metabase is setup using a function
> (MetabaseSetupDatabaseObject) instead of a new declaration I'm not
> sure how to extend it without tinkering with the distributed code. Is
> this possible?

You can use custom driver classes for that purpose. Just take a look at
these parameters of the MetabaseSetupDatabase function:
IncludedConstant, Include, SubIncludedConstant, SubInclude, ClassName.

OTOH, if what you want to develop is something that could benefit the
developers that use any of the driver classes, you may also consider
discussing the creation a patch to be submitted when you have developed it.

--

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

Hello,

In the latest released Metabase the method CreateDatabase() inside
manager_mysql tests for existance of mysql_create_db function. If that
function exists goes for it, otherwise issues a "CREATE DATABASE" query.

This is fine and fixes the problem of older versions
of metabase with MySQL 4 php api. Unfortunately on a test windows XP
installation (EasyPHP 1.7 packaging Apache 1.3.27 - PHP 4.3.3 - MySQL
4.0.15) despite executes correctly mysql_create_db() gives a warning
about the function being deprecated.

Therefore I suggest to modify the command adding a @ modifier to
suppress the warning:

$success=@mysql_create_db($name,$db->connection);

A second possibility that could be even better would to remove the use
of mysql_create_db and always go with a CREATE DATABASE query.

Regards,
Stefano

Hello,

On 07/17/2004 02:01 PM, Stefano wrote:
> Hello,
>
> In the latest released Metabase the method CreateDatabase() inside
> manager_mysql tests for existance of mysql_create_db function. If that
> function exists goes for it, otherwise issues a "CREATE DATABASE" query.
>
> This is fine and fixes the problem of older versions
> of metabase with MySQL 4 php api. Unfortunately on a test windows XP
> installation (EasyPHP 1.7 packaging Apache 1.3.27 - PHP 4.3.3 - MySQL
> 4.0.15) despite executes correctly mysql_create_db() gives a warning
> about the function being deprecated.
>
> Therefore I suggest to modify the command adding a @ modifier to
> suppress the warning:
>
> $success=@mysql_create_db($name,$db->connection);
>
> A second possibility that could be even better would to remove the use
> of mysql_create_db and always go with a CREATE DATABASE query.

Humm... I have silenced the warning in the latest release. Now, I have
just noticed that I did not make any release after that. I think I will
do it soon when I add a few planned maintenance features. Meanwhile you
may want to get the most upto date version from MetaL CVS repository:

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



--

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

Hello,

It has been a while since I have not made a release of Metabase. Some
people have even thought that Metabase development was halted.

However, I have been making a few changes during all this time. Since
each change was not significant, I ended up not making a a release.

Since many small changes were done and many people can benefit from
them, I intend to make a maintenance release before I add some new
features that I have planned to better support the developments of
Metastorage.

Therefore, I would appreciate that you could get the latest version of
Metabase from a read-only CVS repository that I used to distribute MetaL
based projects and then let me know if there are any issues that I may
have escaped.

You may find all the instructions to download Metabase from MetaL CVS
repository here.

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

Obviously you only need to get metabase module unless you want to build
the documentation, despite it hasn't changed.

Just execute a cvs log command to learn about all the changes done since
the version you are using.

--

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

Hello,

I've recently started playing with Metabase as a solution for my PHP
database abstraction needs.  I'm trying to write a project that needs
to be portable across many different databases, and I'm hoping
Metabase will help me do that, however...

I've read the other comments about MSSQL and dates/times/timestamps
not being translated to the ISO format.  I need to get this
functionalilty working.  Is a solution in the works or already done?
Surely it can be done either with the "convert" SQL functions or
within PHP using regular expression manipulation?  What is the best
way to procede with getting this sorted out?

Thanks

Dave

dcpck2004 wrote:

> Hello,
>
> I've recently started playing with Metabase as a solution for my PHP
> database abstraction needs.  I'm trying to write a project that needs
> to be portable across many different databases, and I'm hoping
> Metabase will help me do that, however...
>
> I've read the other comments about MSSQL and dates/times/timestamps
> not being translated to the ISO format.  I need to get this
> functionalilty working.  Is a solution in the works or already done?
> Surely it can be done either with the "convert" SQL functions or
> within PHP using regular expression manipulation?  What is the best
> way to procede with getting this sorted out?

this may help:

Ini setting for output formatting:
     ini_set('mssql.datetimeconvert', 'Off');

Query on startup to tell the server how input will have date parts
ordered:
     SET DATEFORMAT ymd

regards,
Lukas Smith
smith@...
_______________________________
    BackendMedia
    www.backendmedia.com
    berlin@...

    Linn Zwoch Smith GbR
    Pariser Str. 44
    D-10707 Berlin

    Tel +49 30 83 22 50 00
    Fax +49 30 83 22 50 07

Hello,

On 09/07/2004 11:59 AM, dcpck2004 wrote:
> I've read the other comments about MSSQL and dates/times/timestamps
> not being translated to the ISO format.  I need to get this
> functionalilty working.  Is a solution in the works or already done?
> Surely it can be done either with the "convert" SQL functions or
> within PHP using regular expression manipulation?  What is the best
> way to procede with getting this sorted out?

The Metabase solves that by creating fixed size text fields. Although
text fields may not be suitable for all dates operations they are
sufficient for most (really) portable that you may want to perform
including comparisions and sorting. You may want to double check that by
configuring and running the driver conformance test suite script
driver_test_configuration.php .

This remained that way because the original driver was written to work
with MS SQL server 6, which unlike other databases did not provide a way
to let database clients tell the server to return dates formatted in the
ISO format. I don't know about newer versions.

This solution was preferred to having to guess the current server date
and time format parse it to retrieve dates in ISO format as every other
driver. If newer versions provide new means to assure return correctly
formatted dates without these tricks, it would not be hard to add an
option to the MS SQL driver to use real date and time fields instead of
text fields.

--

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

Hello,

On 11/20/2004 06:13 PM, Jorge Paván wrote:
> Hello!. I want to begin to test the cms component and, after I compile
> it, and want to run "metal\metastorage\install\setup\install.php" and I
> obtain the error: "Error: Could not install database: the database was
> only partially created: Valor por defecto invalido para 'sequence' ".
> I can see the error is in metabase_manager.php, line 345
> Could you help me please?

That sounds like you are using MySQL 4.1 that has a backwards
compatibility problem of not allowing the setting of default values of
auto-increment fields.

That problem was solved almost 2 months ago, but since I have not made a
public release of neither Metastorage or Metabase, the fix is only
available in the CVS version. You may download CVS snapshots from here
and hopefully the problem will be solved:

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


--

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

Before I tried Metabase for the first time (last week) i used:

//create select list options (id,lname,fname)
$options_agents=array(""=>"Select an Agent");
while($row=mysql_fetch_row($result_agents))
$options_agents[$row[0]]=$row[1].', '.$row[2];


As I'm playing with Metabase, I tried with no success:

//create select list options (id,lname,fname)
$options_agents=array(""=>"Select an Agent");
while($row=MetabaseFetchResult($database,$result_agents,0,"fname"))
$options_agents[$row[0]]=$row[1].', '.$row[2];

What is the portable mysql alternative?

Also, is there an alternative for:
while($row=mysql_fetch_array($result)){
$name = $row["name"];
}

BTW, as with your other classes, Great Great work!

Thanks again
James

Hello,

on 02/17/2005 03:42 AM idiroddi said the following:
> Before I tried Metabase for the first time (last week) i used:
>
> //create select list options (id,lname,fname)
> $options_agents=array(""=>"Select an Agent");
> while($row=mysql_fetch_row($result_agents))
> $options_agents[$row[0]]=$row[1].', '.$row[2];
>
>
> As I'm playing with Metabase, I tried with no success:
>
> //create select list options (id,lname,fname)
> $options_agents=array(""=>"Select an Agent");
> while($row=MetabaseFetchResult($database,$result_agents,0,"fname"))
> $options_agents[$row[0]]=$row[1].', '.$row[2];
>
> What is the portable mysql alternative?

for($line=0; !MetabaseEndOfResult($database, $result) &&
MetabaseFetchResultArray($database, $result, $row, $line); $line++)
	 $options_agents[$row[0]]=$row[1].', '.$row[2];



> Also, is there an alternative for:
> while($row=mysql_fetch_array($result)){
> $name = $row["name"];
> }

Sorry no. There is no portable way for fetching column values by name
because each database may change the case or truncate the returned
column names in an unpredictable way.

You can always use defines like this:

define("NAME", 0);

$name=$row[NAME];

Just take care that defines are global.


> BTW, as with your other classes, Great Great work!

Thank you. ;-)


--

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

Here's my situation:

1.  The webserver does not have mysql
2.  I do not have shell access to the web server

Does metabase provide a 'flat-file' style for database access?  I see
that there's SQLite support, but I think SQLite requires shell/telnet
access to the server?

Any advice?

You shouldn't need shell if you have access to web based
management such as phpmysqladmin.  Most of my web hosting
clients who run databases neither have nor want shell.

Perhaps the mysql lives on another machine. you need to
talk to your hoster.

If your host does not give you mysql (LAME LAME LAME),
get another host. (such as me. hehe)

Another alternative is for you to run the MySQL server
on your own machine, but you'd need to have 24/7 on and
a pretty fast pipe.  A third alternative is to find freehosting with
mysql that allows remote connections.  then you could have your
website on the... uhhhh... challenged host and your mysql database on
another.  But honestly I wouldn't even consider a webhost that
doesn't have some RDBMS.  That's like a restaurant that doesn't serve food.

>
>
> Here's my situation:
>
> 1.  The webserver does not have mysql
> 2.  I do not have shell access to the web server
>
> Does metabase provide a 'flat-file' style for database access?  I see
> that there's SQLite support, but I think SQLite requires shell/telnet
> access to the server?
>
> Any advice?
>

Maybe I need to give clarification to my situation.  I do not have a
choice regarding the web server:

My customer for the database app runs their own servers...which are
run by their own IT people.  The IT people have the servers locked
down to the point where it's a bureaucratic nightmare to get
anything 'fancy' like a database.  It's a miracle that they allow me
to run server-side scripts!  It's a VERY simple database app, only
requires 5 tables and minimal access.  I figured there would be
something out there for flat-file SQL...but I'm used to Metabase and
was hoping there would be a flat-file system that I could use so
that I could keep using the Metabase API that I'm already familiar
with.



--- In metabase-dev@yahoogroups.com, Colleen Dick <platypus@p...>
wrote:
>
> You shouldn't need shell if you have access to web based
> management such as phpmysqladmin.  Most of my web hosting
> clients who run databases neither have nor want shell.
>
> Perhaps the mysql lives on another machine. you need to
> talk to your hoster.
>
> If your host does not give you mysql (LAME LAME LAME),
> get another host. (such as me. hehe)
>
> Another alternative is for you to run the MySQL server
> on your own machine, but you'd need to have 24/7 on and
> a pretty fast pipe.  A third alternative is to find freehosting
with
> mysql that allows remote connections.  then you could have your
> website on the... uhhhh... challenged host and your mysql database
on
> another.  But honestly I wouldn't even consider a webhost that
> doesn't have some RDBMS.  That's like a restaurant that doesn't
serve food.
>
> >
> >
> > Here's my situation:
> >
> > 1.  The webserver does not have mysql
> > 2.  I do not have shell access to the web server
> >
> > Does metabase provide a 'flat-file' style for database access?
I see
> > that there's SQLite support, but I think SQLite requires
shell/telnet
> > access to the server?
> >
> > Any advice?
> >

Lese selbst:
http://www.npd.de/npd_info/deutschland/2005/d0405-39.html

Hello,

After a long time without significant enhancements to Metabase, I am
finally adding support to database schema features like auto-increment
fields and primary keys. Future versions of Metastorage will generate
code that takes advantage of these features.

Currently the new Metabase version is in alpha stage as the support for
these features is not completely done. Here is the status:

- autoincrement is a new option of the schema of table fields. It
automatically expands to integer, notnull, default 0 and primary key.

- There are two new functions in the API GetNextKey and GetInsertedKey.
GetNextKey determines what to put in an insert statement in the place of
the value an auto-increment field. GetInsertedKey retrieves the last
inserted value in autoincrement field. These functions are run before
and after an insert query respectively.

- primarykey is a new section of table schema definition. The definition
is similar to indexes but you can only have one primary key per table.

- These features are implemented in the drivers for MySQL, PostgreSQL
and Oracle. If you have access to other databases like Microsoft SQL
server, Interbase, Informix, SQLite, Access/ODBC, mini-SQL, please let
me know because currently I am not able to test the new features in all
these databases.

- Altering tables with primary keys or autoincrement fields is not yet
implemented.

- There is a new database manager API function named
CreateDetailedTable. This is an extension of the CreateTable function to
allow for creation of tables with primary keys and other features in the
future. It supports a check mode that lets the schema manager know if
the described table can be created by the database driver.

- The schema manager now performs a safety check when installing or
altering databases with new tables. If it is not possible to install a
table because the current driver does not support some features, nothing
is changed in the database and the schema manager will return an
explanatory error message.

- Schema reverse engineering of database tables with auto-increment
fields or primary keys is not yet implemented.

- The driver test suite has now a new test named autoincrement that
tries to insert a few records in a new table with an autoincrement field
and verifies if it worked correctly. You may look into the
driver_test.php script for how auto-increment support works.

- There will be a new function to set a value of a prepared query to the
next auto-increment value of a table. This is not yet implemented but of
course will only work with insert queries.

These changes are available in CVS. You may find instructions on how to
obtain access to the CVS server or a download daily snapshots from here:

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

Please test these changes looking and running the driver_test.php script
and provide your feedback.


--

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

Hello,

on 08/02/2005 05:53 PM Alexandre Miguel de Andrade Souza said the following:
> I developed a little script to admin scripts:
>
> import,
> export,
> sincronize from a db to another
> delete
>
> the schemas are created under metabase/schemas
>
> But  I found problems:
>
> 1) When a database already exists in a DBMS it don't put the diferent
> values from the original database.
> 2) When the original (from) database/table has a field to be created in
> second, it does, but at the end of the list of the fields.
> it can cause some problems in applications that use   insert into table
> values (,,,,,) without defined fields, just sequence.
>
> the problems above don't happen when the database (to) doesn't exist and
> is created.
>
> someone can help? or make any sugestions?
>
> the script is the below: admin_schemas.php in directory metabase.

This is interesting. Can you please upload it to the files are of the
metabase-dev mailing list?

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

--

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

Hello,

This email message is a notification to let you know that
a file has been uploaded to the Files area of the metabase-dev
group.

   File        : /admin_schemas.php
   Uploaded by : alexandremasbr <alexandremasbr@...>
   Description : script to admin schemas (import/export/sincronize/delete

You can access this file at the URL:
http://groups.yahoo.com/group/metabase-dev/files/admin_schemas.php

To learn more about file sharing for your group, please visit:
http://help.yahoo.com/help/us/groups/files

Regards,

alexandremasbr <alexandremasbr@...>

Hello,

A new Metabase release is imminent. All new features were implemented,
tested and documented. I am just finishing to update the tutorials and
tomorrow I expect to make a release.

The current development of Metabase features is mostly motivated to
address needs of Metastorage. Since it takes a lot of time to enhance
all the available drivers to implement the new features, for now only
MySQL, PostgreSQL and Oracle drivers were updated.

In case you are not yet aware, I have put up a survey to figure which
other drivers would be worth investing time to update, giving priority
to databases that users that really intend to use Metastorage want to
use. So, if you want to use Metastorage, please go to this survey page
and indicate which database or databases you want to use Metastorage.

After a long time without significant enhancements to Metabase, I am
finally adding support to database schema features like auto-increment
fields and primary keys. Future versions of Metastorage will generate
code that takes advantage of these features.

http://groups.yahoo.com/group/metal-dev/surveys?id=12118553

If you don't know what Metastorage is, it is a code generator tool that
generate classes of objects that perform object-relational mapping based
on a model definition in a XML format.

This is basically meant to provide dramatic improvement in the quality
of PHP database application development, reducing drastically the
development time, especially of medium or large scale applications.

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


As for this release of Metabase it provides:

- Support for auto-increment key fields in a database independent way.
It works very well even on databases like Oracle that do not support
auto-increment fields natively.

There are new API functions to support auto-increment fields:
GetNextKey, GetInsertedKey, and SetQueryKey which is a very innovating
feature that lets you bind a value of a prepared query to an
auto-increment table field.

Using just these functions Metabase figures automatically whether native
auto-increment fields or sequences will be used. There is no need to
verify that at runtime and the code will always be database independent.

- Support for primary keys. Primary keys are implicit with tables using
auto-incremented key fields but Metabase also supports compound keys.

- Migration to alter tables and start using auto-increment fields and
primary keys works flawlessly. I have implemented this in the PHP
Classes site that has tens of tables that were migrated with a simple
change in a XML database schema definition.

If before you had>

<table>

   <name>sometable</name>

   <declaration>

    <field> <name>id</name> <type>integer</type> <notnull>1</notnull>
<default>0</default> </field>
    <field> <name>someotherfield</name> <type>text</type> </field>

    <index>
     <name>sometable_id_index</name>
     <field> <name>id</name> </field>
     <unique>1</unique>
    </index>

   </declaration>

</table>

<sequence>
   <name>sometable_id</name>
   <start>1</start>
   <on> <table>file_accesses_queue</table> <field>id</field> </on>
</sequence>


Now you just need this:

<table>

   <name>sometable</name>

   <declaration>

    <field> <name>id</name> <autoincrement>1</autoincrement> </field>
    <field> <name>someotherfield</name> <type>text</type> </field>

   </declaration>

</table>

Migrating from one kind of schema to the other just takes this change.
Metabase will take care of the field changes and the implicit primary
key association.

As for applications code you just need to change something like this:

$db->GetNextSequenceValue("sometable_id", $id);

$db->Query("INSERT INTO sometable (id, someotherfield) VALUES(".$id,",
'some other value')";

to:

$db->GetNextKey("sometable", $key);

$db->Query("INSERT INTO sometable (id, someotherfield) VALUES(".$key,",
'some other value')";

$db->GetInsertedKey("sometable", $id);


- There are new database manager API functions named
CreateDetailedTable and DropDetailedTable. These are used by the schema
manager class to create and drop tables with auto-increment fields and
primary keys.

- The schema manager now performs a safety check when installing or
altering databases with new tables. If it is not possible to create a
table because the current driver does not support some features, nothing
is changed in the database and the schema manager will return an
explanatory error message.

- Schema reverse engineering of database tables with auto-increment
fields or primary keys is now implemented for now for MySQL only.

- The driver test suite has now a new tests named autoincrement and
preparedautoincrement that try to insert a few records in a new table
with an autoincrement field and verifies if it worked correctly either
using direct queries or prepared queries.


For now this new version is available in CVS. You may find instructions
on how to obtain access to the CVS server or a download daily snapshots
from here:

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

Please test these improvements and let me know if you would like to seem
them also implemented in databases that are not yet supported.

The updated version of the documentation is available here. The tutorial
will be updated  soon.

http://www.meta-language.net/metabase.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

Hello,

In case you don't know, I have just released a new version of Metabase.

This release includes support for inserting rows in tables omitting the
auto-increment field values and make such field get the next
auto-increment value automatically in DBMS that support it.

The Microsoft SQL server version was enhanced to support native
auto-increment fields, primary keys and support several new types of
schema table alterations.

The Oracle driver has also enhanced to use triggers implicitly to
initialize the values of auto-increment fields when these values are
omitted or set to NULL in INSERT queries.

If you pick the MetaL and Metastorage bundle archives generated from the
CVS snapshots, it already includes the latest Metabase version:

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

My short term plan for Metastorage should address these issues:

- Use auto-increment fields omitting their values as it is faster on
some databases (like MS-SQL that requires INSERT query rewriting).

- BLOB class variable support

- Pattern matching support (LIKE operator) to implement new OQL
operatores like startswith, contains and endswith. This will depend a
Metabase feature to be added that will provide database pattern wildcard
escaping.

- Add code generation options file to let developers customize details
of generation to address better their projects: like PHP 5 optimized vs.
PHP 4 compatible, toggle the comments in the generated code, namespace
prefixes for class names, generate setter and getter functions, etc..

- Add the section of report classes of the Metastorage tutorial

- Support more database drivers with native auto-increment support (you
tell me which you want to use)

- Other important features (you tell me about it).

--

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

Hi I've been using metabase now for a few months I use it to develop
small apps using sqlite. I have been working with this driver
converted for the default php5 sqlite extension, and it has been
giving me reliable service and completes the driver_test.php test.

Maybe sqlite's autoincrement feature can be included too.

I wonder if it will be of any use to your users in the absence of
anything else working with PHP5 Sqlite presently.

Thanks for the great work that you do.

Regards John Walton (admin@...)

metabase_sqlite.php

<?php
// metabase_sqlite.php
// for php5 extension

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 $
  * @modified for PHP 5 default sqlite 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->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)
	 {
		 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($clob)));
			 }
			 $this->EscapeText($data);
			 $value.=$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 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["LOBs"]=
		 $this->supported["SelectRowRanges"]=
		 $this->supported["Replace"]=
		 $this->supported["Transactions"]=
			 1;
		 $this->decimal_factor=pow(10.0,$this->decimal_places);
		 return("");
	 }
};

}
?>

Hello,

on 10/28/2005 11:54 PM jonbouyw said the following:
> Manuel
>
> Hi I've been using metabase now for a few months I use it to develop
> small apps using sqlite. I have been working with this driver
> converted for the default php5 sqlite extension, and it has been
> giving me reliable service and completes the driver_test.php test.
>
> Maybe sqlite's autoincrement feature can be included too.
>
> I wonder if it will be of any use to your users in the absence of
> anything else working with PHP5 Sqlite presently.
>
> Thanks for the great work that you do.

Great! I was going to do this soon or later but I am glad you did it as
saves me time.

The original driver was developed for a PHP SQLite extension that is no
longer supported. So it was obsolete.

Anyway, AFAIK the SQLite extension also works in PHP 4, so this driver
has greater interest than you can imagine.

I will look into this probably in two weeks and add whatever may be
missing. Did you try it with the driver_test.php script?

Thank you for you contribution. You will be credited also in the user
documentation.

--

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

--- In metabase-dev@yahoogroups.com, Manuel Lemos <mlemos@a...> wrote:
>
> Hello,
>
> on 10/28/2005 11:54 PM jonbouyw said the following:
> > Manuel
> >
> > Hi I've been using metabase now for a few months I use it to develop
> > small apps using sqlite. I have been working with this driver
> > converted for the default php5 sqlite extension, and it has been
> > giving me reliable service and completes the driver_test.php test.
> >
> > Maybe sqlite's autoincrement feature can be included too.
> >
> > I wonder if it will be of any use to your users in the absence of
> > anything else working with PHP5 Sqlite presently.
> >
> > Thanks for the great work that you do.
>
> Great! I was going to do this soon or later but I am glad you did it as
> saves me time.
>
> The original driver was developed for a PHP SQLite extension that is no
> longer supported. So it was obsolete.
>
> Anyway, AFAIK the SQLite extension also works in PHP 4, so this driver
> has greater interest than you can imagine.
>
> I will look into this probably in two weeks and add whatever may be
> missing. Did you try it with the driver_test.php script?
>
> Thank you for you contribution. You will be credited also in the user
> documentation.
>
> --
>
> 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
>
Yes I did run the conformance tests and it passes all of the tests
including NULLS.

I'm not clear on how the autoincrement works within Metabase at the
moment, but as you know SQLite will treat a primary key field of the
type INTEGER as an autoincrement field, I'm investigating how to
incorporate in the Metabase context.  I would appreciate any
suggestions you have in this regard.

Also I'm looking into the manager class as I have some ALTER TABLE
functions to emulate the ALTER TABLE functions that are lacking in
SQLite which are implemented by recreating a new table with the
alterations included an then re-writing the original database.  This
is slow but does provide full ALTER TABLE functionality.  Again I'm
investigating how to include this within the Metabase context.

As well I expect to be able to produce a SQLite PDO driver which
shouldn't be too dissimilar to the SQLite one already provided.

Thanks for the thumbs up ;)

Hello,

on 10/31/2005 02:38 PM jonbouyw said the following:
> Yes I did run the conformance tests and it passes all of the tests
> including NULLS.

I haven't tried it yet. Does it skip any tests or performs all the 16
tests? I assume that at least auto-increment tests are skipped.


> I'm not clear on how the autoincrement works within Metabase at the
> moment, but as you know SQLite will treat a primary key field of the
> type INTEGER as an autoincrement field, I'm investigating how to
> incorporate in the Metabase context.  I would appreciate any
> suggestions you have in this regard.

That is very similar to MySQL. You may want to take a look at its driver
class.

You need to make the Setup function tell it supports AutoIncrement and
PrimaryKeys. If you can omit the column of an auto-increment field and
the database automatically inserts the next key, also tell that it
supports OmitInsertKey.

You also need to adjust the GetIntegerFieldTypeDeclaration function to
make it declare auto-increment fields when it is the case.

You need to implement GetNextKey and GetInsertedKey to prepare and
retrieve auto-increment key values. See the documentation for more details.


> Also I'm looking into the manager class as I have some ALTER TABLE
> functions to emulate the ALTER TABLE functions that are lacking in
> SQLite which are implemented by recreating a new table with the
> alterations included an then re-writing the original database.  This
> is slow but does provide full ALTER TABLE functionality.  Again I'm
> investigating how to include this within the Metabase context.

MySQL implements table alterations that way but the necessary SQL is simple.

To implement a full blown AlterTable from scratch is tricky but many
people will appreciate your effort. Just try to implement one kind of
table alteration at a time playing with a test schema, and let me know
when you have questions.


> As well I expect to be able to produce a SQLite PDO driver which
> shouldn't be too dissimilar to the SQLite one already provided.

PDO may be more complicated. It is better to start from a generic base
PDO driver class, like there is for ODBC, but not for PDO right now.
Lets deal with one driver at a time.

--

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 wrote:
> Hello,
>
> on 10/31/2005 02:38 PM jonbouyw said the following:
>
>>Yes I did run the conformance tests and it passes all of the tests
>>including NULLS.
>
>
> I haven't tried it yet. Does it skip any tests or performs all the 16
> tests? I assume that at least auto-increment tests are skipped.

Do you have any tests for REPLACE? It should fail those as sqlite does
not return the same affected row values as mysql ..

>>I'm not clear on how the autoincrement works within Metabase at the
>>moment, but as you know SQLite will treat a primary key field of the
>>type INTEGER as an autoincrement field, I'm investigating how to
>>incorporate in the Metabase context.  I would appreciate any
>>suggestions you have in this regard.
>
>
> That is very similar to MySQL. You may want to take a look at its driver
> class.

You can specific autoincrement nonetheless and IIRC it will get stored
by sqlite, which would make reverse engineering clearer.

>>Also I'm looking into the manager class as I have some ALTER TABLE
>>functions to emulate the ALTER TABLE functions that are lacking in
>>SQLite which are implemented by recreating a new table with the
>>alterations included an then re-writing the original database.  This
>>is slow but does provide full ALTER TABLE functionality.  Again I'm
>>investigating how to include this within the Metabase context.
>
>
> MySQL implements table alterations that way but the necessary SQL is simple.
>
> To implement a full blown AlterTable from scratch is tricky but many
> people will appreciate your effort. Just try to implement one kind of
> table alteration at a time playing with a test schema, and let me know
> when you have questions.

Sounds interesting indeed. I will probably steal with for MDB2 :-)

>>As well I expect to be able to produce a SQLite PDO driver which
>>shouldn't be too dissimilar to the SQLite one already provided.
>
>
> PDO may be more complicated. It is better to start from a generic base
> PDO driver class, like there is for ODBC, but not for PDO right now.
> Lets deal with one driver at a time.

I agree that this approach would make the most sense.

regards,
Lukas

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("");
     }
   };
}
?>

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

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

Hello,

This email message is a notification to let you know that
a file has been uploaded to the Files area of the metabase-dev
group.

   File        : /beta/metabase_sqlite.php
   Uploaded by : jonbouyw <jonbouyw@...>
   Description : Metabase SQLite (PHP Extension) Driver

You can access this file at the URL:
http://groups.yahoo.com/group/metabase-dev/files/beta/metabase_sqlite.php

To learn more about file sharing for your group, please visit:
http://help.yahoo.com/help/us/groups/files

Regards,

jonbouyw <jonbouyw@...>