Re: [PROPOSAL] DB_Simple

On Nov 1, 2003, at 2:32 PM, Alexey Borzov wrote:

My problem with this package is "simple", too: I don't see the problem it is trying to solve.

It is true that the individual functions combined into DB_Simple exist in other packages. However (and this is the problem) they do not all exist in a single package, and those packages are not really compatible with each other (MDB and DB_DataObjects in particular) or are not well-documented enough to be useful to the new developer.

The only *new* functionality is checking the field types, but: 1) Database does this anyway and does this better. 2) There can be other constraints defined inside database that can raise an error, even if the types match. 3) The package requires describing the table structure *twice*: first when you create it and then when you are using "DB_Simple". "DB_Simple" does not provide a tool to extract the metadata from the DB (DB_DataObject does). This can lead to inconsistencies. Of course, with MySQL that considers 2003-02-31 a valid date and does not understand CHECK constraints another application level check can come in handy [1]. That's why I will not oppose the package only if it is moved to MySQL namespace ghetto. And, last but not least, let me quote The Holy PEAR Manual[2]: ------------------------------------------------------------------------ First of all, do a reality check: Why do I want to commit a new package? Really bad answers are "To see my name in PEAR" or "I didn't understand the API of the existing class".

"To see my name" certainly isn't the case, as I already have two packages in there. ;-) "I don't understand the API" is not exactly the case either, as I have gone to some lengths to test two major packages and a few minor ones that would appear to have solved my problem (but did not). Again, the problem is that the combination of functionality I require does not exist in a single released package, and the packages that provide parts of the needed functionality do not combine well. As such, DB_Simple exists to close the gaps between those packages by combining the functionality into a single, well-documented, easy to understand package; it is not for super-powered complex database access, and does not attempt to be everything to everyone, but will do very well for simple database-enabled applications or modules.

A good reason for a new class is often, that you are missing a function, behaviour or speed in an existing implementation. In this case, you should take a look at this class, if it possible to extend this class. If not, then you have a good reason to commit a new class. "If not" means, it isn't possible to add the required functionality without changing the basics of this class.

I think that's the case with DB_DataObject (i.e., can't add my stuff without changing the "philosophy" of the class), although an extension or wrapping of the new MDB 2.0 is certainly possible. But again, MDB 2.0 is not out, and I have a solution available now (not pretty but workable ;-).

MySQLism?

No, each supported database uses its own internal datatypes for numbers and strings; I call the MySQL example is "typical" only because it's typical for me, although MS-SQL is looming in my future.

I meant 'unsigned' modifier itself is a MySQLism. I doubt that it is supported by any other database.

A-ha! OK, I have removed it from my own code base. Thanks for the pointer.

Quoting the comment buried *deep* inside createTable(): // add the field definition ... // mysql only for now, include others later If the package supports only MySQL, then it has to have MySQL in its name.

I should have removed the comment; it is not MySQL only, although I have not tested it on others. Please note the class property $_declare; it has field declaration keywords for a number of different databases.

Ugh. Looked more closely at the method: is it intentional that it uses DB_SIMPLE_STRING for all date and time related columns???

Yes, for the reason I stated in the proposal. (Incidentally, it looks like DB_DataObject does that, too -- it only has INT and STR type identifiers, unless I am missing something.) This is one of the primary conceits of DB_Simple: that you can force the database to store things the way you want them to be stored, thus avoiding the need to convert back-and-forth between native database types when moving from one RDBMS to another. (C.f. the comments from the SQLite guys on their implementation as well.) This "forcing of type storage" allows very easy portability between database servers; you do not need to convert your query terms to the database native format, you can just write them the way you know the database will store your data (because DB_Simple is forcing it to be stored that way). Yes, you lose a lot of power that individual database back-ends may provide vis-a-vis their own datatypes; but then, if you are custom-writing your app for a specific RDBMS, you're probably not going to be able to port those database-specific abilities anyway.

Is this any easier than to directly write SELECT id, username, email FROM tablename WHERE division = 'Information Technology' ORDERB BY username The manually written query has less chars in it, BTW.

One might ask the same thing about DB_DataObjects, and the answer is "yes and no." Maybe I am not a good SQL guy; sometimes I want a baseline list, and sometimes I want to filter that baseline list. Having a basic query as a property, and then adding a different filter to it in different methods, has been useful to me in the past.

OK, you may have a point here. But I think we already have some query builders in PEAR?

Yes, but none that I saw allow ad-hoc filters and orders from a baseline query, none recognize a customized "column map" a la DB_Simple, none support multiple embedded queries based on that column map, and none use the DB::modifyLimitQuery() method to tailor limit requests to a specific database. It is possible that I failed to note the ones that do; have you any suggestions for a single well-documented package that provides those functions?

=========================================== Automated INSERT and UPDATE With Validation -------------------------------------------

...

Will it also check for column constraints, foreign keys and the like? Besides, this info is *not* read from DB, but provided by the user -> inconsistencies.

No, at least not yet -- do all the supported databases use all those features? I have tried to find the common denominators between the supported databases and abstract them, but it's possible I missed some.

I think all major databases except MySQL support column constraints. And even MySQL sorta supports foreign keys. My real point being, of course, that this stuff should be checked in DB, not in application.

Fair enough; I'll do more research on foreign key support and column constraints, and add them in as I can. However, some level of validation has to happen at the application level, and I'd rather check as much as I can at the app level before it so as to avoid making connections that will only produce errors. (I see DB_Simple as the beginning of the application tier, so perhaps this point is one of conflicting perspectives.) Also, this automated validation allows user to create their own custom validations at the data-access level (instead of at the UI level), the results of which can be passed back to the UI. I like the idea of the table-interface class validating its own data, because different UIs may talk to the same table for different reasons; thus, each UI has to have its own validation routines. Encapsulating validation of data at the data-access level makes more sense to me for that reason.

And my all-time favourite error message, from delete() method: return $this->raiseError("The WHERE clause in your DELETE " .

    "statement has no operator (=, <, <=, >, >=, LIKE) in it; " .
    "this might delete all records.  Request denied.");

Seriously, I've stupidly sent "DELETE FROM table" commands before, and that only little bit of code would haved save my but at least once.

A 'ROLLBACK' command seems a better choice in this case. ;]

... if your RDBMS supports rollback (MySQL 3.x sucking again).

What about DELETE FROM tablename WHERE id IN (1, 2, 3) DELETE FROM tablename WHERE username ~ '^Paul'; -- PostgreSQL regex check

Also fair: I have removed the dummy-check in my code base, and will let the user implement their own in extensions of DB_Simple. _______________________________________________________________________ Paul M. Jones Savant: the simple alternative to Smarty. http://phpsavant.com/