Re: Re: DB_Table: Summary and Pre-Call

Hi, Hans, Thank you for your comments; I'm grateful that you would take time to look at DB_Table at all, much less write such a thoughtful and mannered review. The issue of data types and forcing type storage is an important one, and it brings up a whole host of valid and important arguments. If you care to continue, please let me illuminate my reasoning with the following points. 1. I want to be able so deal with date-time info in any RDBMS, regardless of how that RDBMS stores and presents date-time info, and without knowing in advance what that storage format is. (E.g., for PHP apps and classes that will be distributed and used in various heterogeneous environments.) 2. "Dealing" with date-time info means being able to query against it as well as recognize the date and time pieces. This means I need to know what format the date is in so I can query it, and so I can make sense of that format when I get the information back from the table (year, month, day, etc). 3. All RDBMS engines store and present date-time info differently, and have different limits on the range of dates and times they can store. 4. Thus, if I write a query for one RDBMS, that query, by itself, is not portable to another RDBMS. This is the central point. E.g., look up 1970 Jan 01 (12 am).

    In MySQL:  "SELECT * FROM table WHERE datetimefld = '1970010112000000';"

    In MS-SQL:  "SELECT * FROM table WHERE datetimefld = '01/01/1970 12:00:00;"

    In Oracle it's different from those, and PostgreSQL is different, and SQLite is different...

4a. You get the point; note that none of them are ISO standard. *And* the limits are different. Some databases use a native date field that only stores dates back to 1970 or forward to 2040 (I'd need to review my research to remember which ones); some are accurate to the second, others to one-third of a second. 5. The standard response to this situation is to tell the developer to write queries differently: every time he needs to put date-time info in a query, he should convert that date to the RDBMS format, and use the conversion function in what would otherwise be a literal query string. Similarly, every time he retrieves date-time info from a table, he should convert it into a standard and recognizable format. That standard and recognizable format is usually an ISO format. E.g.:

    "SELECT * FROM table WHERE datetimefld = '" . convertDateToRDBMS($date) . "';"

    -- and then ---

    $result = $db->query($sql_cmd);
    $date = convertDateFromRDBMS($result['datetimefld']);

This is where most people stop, and it is a reasonable place to stop. Metabase, MDB, and MDB2 do this. I, being an argumentative malcontent who has to do things his own way ;-), go one more step. This may or may not be wise on my part. 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.

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.

More generally, I think that doing this makes databases designed for use with DB_Table to be non-standard or even idiosyncratic --- and this seems to be the opposite of what you want.

Completely agreed that it is non-standard and idiosyncratic. I wish it were not. If I stopped at Step 5 (see above) it would not be an issue. Your point is completely valid and well-taken. This is a serious weakness in at least two ways: (1) It becomes difficult to tell, from structure alone, that a VARCHAR(19) field is in fact an ISO DATE+TIME field; however, if there is even one row inserted, it will be obvious what the column is supposed to be. (2) There is a storage efficiency sacrifice. The VARCHAR(19) field might actually boil down to 8 bytes or fewer in the RDBMS native date-time type. Of course, that takes us back to converting date-time formats into and out-of the RDBMS again. These are trade-offs that I am willing to make for my own projects, but others may not be willing to make them.

Now .. I'm unsure whether it's DB_Table's job to deal w/ DB native types or whether it's DB's (or MDB's) job. I tend to think it's the latter, actually, and in my implementation I moved all of that logic down into the Creole level.

Agreed, and I sure wish DB could handle such things. But even if it could, we quickly come back to converting your query dates (and retrieved data) to and from the RDBMS native format. MDB2 handles them nicely, but MDB2 represents a significantly different way of doing things, and frankly its documentation is just not there for me (it might be good enough for others, but I need a lot of hand-holding sometimes ;-).

In addition, because the DB_Table instance has a defined column map,

it knows what to expect from every field. Thus, it can pre-validate all INSERT and UPDATE values to make sure they match the column requirements (data type, size, decimal places, not-null, and so on) before attempting to connect to the database. This also allows

developers to add customized validations for insert and update calls.

This is nice. I actually like the option of doing the table definitions at runtime rather than a build phase (which is how Propel works); it opens up possibilities in the area of dynamic database structures that are interesting.

Am going to add something like this in a future release. It would be great to look at an existing table, figure out what the columns are, and work with them from the extant table. However, my primary audience is developers who distribute their solutions, and in those cases the tables are bound not to exist the first time the code runs.

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.

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?

I assume you could use the native DB/MDB sequence emulation to generate the needed IDs, etc. (I personally don't like how these packages force you to emulate sequences instead of using the RDBMS native id generation, but since you are using DB/MDB you might as well use it, eh?)

Heh. ;-) Yes, DB_Table uses the PEAR DB sequence generation and emulation functions.

Those were my two main comments in looking over the package and briefly at the source. I apologize if I missed something that I should have seen. Obviously I don't think that you should change your code based on my comments, but I wanted to at least raise them for the sake of discussion.

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.

Nice work, Paul.

Thanks, and good going with Propel/Creole! That's a hell of a project, man. Good luck in that and in all your future work. :-) -- Paul M. Jones Savant: the simple alternative to Smarty. http://phpsavant.com/