Re: Re: DB_Table: Summary and Pre-Call

Hi, Sergio,

Please allow me a bit of ranting. It is kind of hard to see this discussion taking place all over again, when I already proposed DB_OO back in December. DB_OO back then did most of the stuff now present in DB_Table, with only slight variations in approach (and better documentation, if you allow me some ego polishing).

I understand your frustration. Not to nitpick, but I had proposed DB_Simple (the precursor to the current DB_Table) in October. It was your DB_OO package proposal, as well as the concurrent discussion of Propel/Creole, that convinced me I should advocate DB_Simple more strongly. I re-proposed DB_Simple under new cover as DB_Table in January; thus, it has been open for discussion for more than four months now. Thank you for taking a look at it.

So, to end my rant: I like DB_Table's approach, and I think it fills a need in PEAR. Most of the stuff is written just about the way I'd do it. I just have two showstoppers and one big question: 1) I'd prefer if the datatypes were based on an SQL standard. From the SQL92 standard, these would be: CHARACTER, CHARACTER VARYING, BIT, BIT VARYING, NUMERIC, DECIMAL, INTEGER, SMALLINT, FLOAT, REAL, DOUBLE PRECISION, DATE, TIME, TIMESTAMP, and INTERVAL.

A valid point, and I am willing to reconsider my naming convention. Let me work it out some more.

2) At least these datatypes should be stored as native datatypes.

The only datatypes that are not stored native are date, time, date-time, and unixtime (which is an INT anyway). All the others are stored native (with some semantic differences, e.g., Oracle uses NUMBER and not INT, CLOBs are LONGTEXT in MySQL and TEXT in pgSQL, etc).

When aiming at interoperability, the best is to aim at implementing a standard. I definitely reject the idea of having dates stored as CHAR or VARCHAR. Most databases have the ability to do powerful date arithmetic. There is no point in throwing all those reliable, fast and proven functionallity out of the window in name of interoperability.

Agreed that native date/time types and their related native functions are far more powerful. However, my (admittedly limited) experience is that the powerful date and time arithmetic in one RDBMS is not implemented the same way in another RDBMS. Thus, an SQL query written for one RDBMS with its particular date and time functions will not translate directly to another RDBMS. Additionally, per my earlier conversation with Hans Lellelid, the date and time formats are not the same between RDBMS engines, leading to the need for conversion functions, etc. As always, I am willing to be proven wrong with examples.

3) Having DB_Table tested only with mySQL, I don't think the interoperability has been put under stress.

You are correct, and I agree -- I want very much to test DB_Table under different systems. I only have access to two RDBMS engines, MySQL and MS-SQL, and only have complete control over the MySQL one. I'd be thrilled if you could help test it out, but I understand if you cannot.

Are there any design assumptions that will need to be revised in order to have it work with other DBs? mySQL has a very poor SQL92 implementation, with largely different datatypes. I'd feel more confortable if DB_Table had been approved for use with pgsql or oracle.

So would I; anybody want to help with that?

So, for me, it's a conditional +1.

Thank you, I'll try to implement your suggestions as well as I can. -- Paul M. Jones Savant: the simple alternative to Smarty. http://phpsavant.com/