Re: Re: DB_Table: Summary and Pre-Call

Hi Paul, Great -- and very extensive -- response. I think your explanation is good. One thing that was made more clear while reading is that there are some constraints that are just going to be there given the tools (DB/MDB) that you are working with. Also, though, things like the text storage for date/time is just a different philosophy. I think you were right to point to SQLite as a justification of this philosophy; indeed -- SQLite works well (for many applications, anyway) and doesn't have any types other than varchar (w/ the slight exception of the autoincrement INT, I guess).

6. If you're converting back to ISO format anyway, why not store it in the table like that in the first place? Then you get the added benefit of not having to convert your query strings when you write queries, and voila! Instant query string and data type portability; a poor-man's portability to be sure, but guaranteed to work on any RDBMS that supports strings.

Yes, this is the way I opted to go w/ Propel / Creole. Creole basically requires you to write "prepared-statement" style SQL. There will obviously be a slight performance cost as this SQL will usually have to be emulated (for MySQL, Postgres, SQL Server, and SQLite). It does get around the issue of dates, though. Timestamps are converted to unix timestamps. The db drivers know the quirks of their databases; usually strtotime() is going to work, but not always. Of course this is a simplistic interpretation of what a timestamp is in a database, but in my experience it is how people use timestamps in databases (when someone wants parsing/formatting support for a timestamp w/ timezone information, they can help write the patch :). Of course forcing users (Propel is the biggest direct user, I think) to use the prepared-statment notation is not ideal, but it's the cleanest way I know of to get around the constant need to format values for inclusion in SQL. I think the performance difference between doing this & inserting the formatted values directly is negligible in a complex db system. Forcing the use of stored procedures also acts as a measure to prevent SQL injection -- since no values are inserted raw (setInt() will cast as integer, setString() will be escaped, etc.). Not perfect, but feels ok :)

I believe that the native DB types should be used so that you can take advantage of (e.g.) date-time function in the database. Of course using such functions would probably tie your class to a particular RDBMS, but I think it should be possible if you really want to do this.

I agree that the native RDBMS data types for date and time are much more powerful. But you bring up the obvious corollary point: if you want to use the native functions of a specific RDBMS, then strictly speaking, database abstraction is not for you: not DB, not MDB, not DB_DataObject, and not DB_Table. The functions in Oracle are not the functions in MySQL, or MS-SQL, or PostgreSQL, etc. If you want to do RDBMS-native transformations of date-time information in your SQL queries, your queries are no longer portable to other RDBMSes, which is part of the point of using a database abstraction layer in the first place.

Yes, true, although I would say that there's more to a db abstraction layer (or layers in this case) than simply being able to switch databases. Certainly you don't want to change the API if you do switch databases, but it seems pretty inevitable that you're going to write RDBMS-specific SQL when dealing w/ a really advanced system. I felt in Propel that it was important to make it easy to use custom SQL, but it's also highly encouraged to encapsulate that in entity class methods. That way if you do change RDBMS you'll have to change SQL in a few places, but the API is intact. Of course, realistically moving an app from one RDBMS to another is rare (although recently I did have to move an app from MySQL -> PostgreSQL and was very happy that I'd been using Propel: didn't have to change a line of code, but the app was also not doing anything *too* fancy in the db).

I wonder if there's any reason, though, why DB_Table doesn't support primary key information?

No reason, other than primary key flags are not always the same in every RDBMS (but my research there is quite limited, I'm willing to be proven wrong and add the functionality). The interim workaround for this is to define a unique index on your primary key column. In fact, I want to add a number of other pieces to the table definition array, but I can only do so much at one time; adding at least "hints" for primary and foreign keys is fully within my intent.

Yeah ... I was thinking you would explicitly state which column/columns comprised your pk. I might be over-simplifying it also. I did something similar for Jargon, which is a simple convenience kit for Creole that includes DAO. You have to declare the key at runtime in order to enable smart inserting / updating. Of course Creole supports fewer databases than DB_Table, so it could be that I've just been lucky.

I'd think it would be fairly simple to have a save() method rather than an update() and insert() method.

At that point it starts looking like a data object class rather than a table interface; DB_Table is the latter, not the former. It may be a subtle difference, but it is an important one. The DB_Table properties are properties of the table, not of the row, which means that save() and those kinds of functions are not the proper metaphor to use. Does that make sense?

Yes, ok -- perhaps that's a misconception on my part. I indeed to do think of DB_Table as a DAO class, but I guess you've been pretty clear about that point. Especially as PEAR already has DB_DataObject, etc. Given that reminder, I'd say that your insert() and update() methods make more sense than a save() method. Seems to me that DB_DataObject could benefit from using DB_Table -- and not the other way around. Is that right?

Not at all, I enjoy talking about it, and you have made well-reasoned arguments. Certainly there are tradeoffs in DB_Table that are not for everyone, but for me they have worked well, and I like to share when I can. Yes, it's great work. I think your point that it's not a DAO is a good one. Seems this would be a good addition to PEAR since it provides a solid & re-usable low-level tool.

Cool work & good luck :) Hans