Re: [Phpgroupware-developers] SQL generator for phpgroupware

[Edgar Luna]

Kai Hofmann <address@hidden> writes:

Hi,
> sounds like a JDO implementation for PHP ;-)
Indeed after reading the code and documentation of
http://db.apache.org/ seems a lot similar. Just for a little(?) thing.

> But how about a bit more code reuse - by using things like PEAR classes:

> DB_DataObject
> DataObject performs 2 tasks:
> 1. Builds SQL statements based on the objects vars and the builder
> methods.
for example in entity i have a $map
that have the structure: 

var $map = array(false_field' => array('select'=>
                                //if something special is needed
                                'function_to_call_in_request',
                                //and so
                                'criteria'      => 'function_to_call_in_insert',
                                'insert'        => 
'function_to_call_in_request',
                                'update'        => 
'function_to_call_in_request',
                                'delete'        => 
'function_to_call_in_request',
                                // db field name
                                'field' => ''
                                'datatype => 'string'),
and soo, foreach field,


so you have the data pre-modeled. Never again set the datatype, or
multiple criterias if needed for this field. or whatever you
need... (in theory :P)

> 2. acts as a datastore for a table row.
> The core class is designed to be extended for each of your tables so that
> you put the
> data logic inside the data classes.
class.sql_entity.inc.php

> included is a Generator to make your configuration files and your base
> classes.


> DB_QueryTool
> This package is an OO-abstraction to the SQL-Query language, it provides
> methods such
> as setWhere, setOrder, setGroup, setJoin, etc. to easily build
> queries.

I believe that we can go one step forward, setJoin could be modeled,
that is class.sql_builder.inc.php so if you have: field1 from table1,
field2 from table1 and field3 from table2

and you know that its a relation 1-n where n is [1-infintiy), why do
you need to set the kind of join each query? I think that this just
could be necessary in one case, where n is [0-infity) and you want for
this especial time set n as [1-infinity) ie. make an INNER JOIN.

with this you could do:

$this->request(array('field1', 'field2', 'field3'));
$sql = $this->get_sql();
then you have the query with join.

> It also provides an easy to learn interface that interacts nicely with
> HTML-forms using
> arrays that contain the column data, that shall be updated/added in a DB.
> This package bases on an SQL-Builder which lets you easily build
> SQL-Statements and execute them.
interesting...
Out of my scope atm.

> MDB
> It provides a common API for all support RDBMS. The main difference to most
> other DB abstraction packages is that MDB goes much further to ensure
> portability. Among other things MDB features:
> * An OO-style query API
I have this class.sql.inc.php, seems I need to check what to add.

> * A DSN (data source name) or array format for specifying database servers
> * Datatype abstraction and on demand datatype conversion
again, class.sql.inc.php, and datatype is pre-modeled.

> * Sequential and non sequential row fetching as well as bulk fetching
> * Ordered array and associative array for the fetched rows
> * Prepare/execute (bind) emulation
> * Sequence emulation
> * Replace emulation
> * Limited Subselect emulation
Jum... if mysql users could accept, that this be achieved via php
(performance hit), could be very interesting. If I use mysql I would not.

> * Table information interface
> * RDBMS management methods (creating, dropping, altering)
> * RDBMS independent xml based schema definition management
> * Altering of a DB from a changed xml schema
> * Reverse engineering of xml schemas from an existing DB (currently only
> MySQL)
This seems an interesting point, lex talk something about this, I
don't know how developers could compare this with scheme proc, that is
actually used to model db, and that I is supposed that I will use in
future for autogenerate sql_entity child classes and sql_builder child
class. Afaik for a complete premodeling I just need a php code
pre-generator that we could create based on xml/xslt, and use scheme
proc. So the db work that lets for application developer is create the
very common queries (without typing a line of sql), this is: the
application API.

> Currently supported RDBMS:
> MySQL
> PostGreSQL
> Oracle
> Frontbase
> Querysim
> Interbase
> Firebird
> MSSQL
If all this have an standard functionality, I think will be great
ported it if the license allow it, if not, we need to ensure that is
valid mantain the code for databases that will not be used.

> Only as inspiration
This have very interesting points, as soon as contacts, be accepted or
not, and after release (in case be accepted I'll be debugging it), I
would like to discuss this a lot.

Best regards and thanks.

-- 
Edgar Antonio Luna Díaz - http://www.sogrp.com
Fingerprint: C008 5EAC 5272 AC8C 7589  4821 8B34 6166 8733 8310