Re: The future of database packages in PEAR

After a look at the Doctrine documentation, I'm impressed. It would be great to have this become a PEAR package, if the author (Konsta Vesterinen) is interested. Like Mark, I had been thinking about how PHP 5 versions of MDB2 and DB_Table could provide something similar in capability to Doctrine. As Mark mentioned, I'm now close to finishing a first release of a _Database subpackage of DB_Table, which builds a relational database layer above the DB_Table table abstraction layer. (A somewhat outdated discussion, with links to an early version of the phps source, is one the web here: http://www.cems.umn.edu/~morse/DB_Table_Database/docs/about.html) This relational database abstraction layer knows about foreign key and joining table relationships, and so can do things like automating construction of join conditions. DB_DataObjects also has some abiity to do such things. Equivalent features for automating joins already exist in Doctrine. (The most notable difference is the lack of support for multi-column foreign keys in Doctrine, as already noted). Regarding Lukas' suggestion that PEAR developers focus on contributing to Doctrine rather than developing new generations of MDB2 and related classes: The more I look at the documentation for Doctrine, the more I'm inclined to agree. Most of what we might hope to achieve with a PHP 5 rewrite of MDB2 and DB_Table(+_Database) is already there, and apparently well tested in Doctrine. There is a separate advantage to basing further work on PEAR PHP 5 database packages on PDO, because it has been bundled with PHP 5, and because it is coded as a C extension and is purported to be faster than other portable DBALs. There is a problem of complexity: The entire package looks well designed, but it takes some time to get your head around. It would be difficult to learn the entire public API. The size of the API is a potential barrier for more casual or impatient users. This barrier could be reduced if an influx of new developers and users helped write tutorials with relatively simple usage examples. It is also true, moreover, that most of the basic functionality provided by MDB2 alone is provided by PDO, which has a relatively simple API. I don't know if I will be of much help with any of this after I get DB_Table_Database written, because of limited time. The opinions that really matter will be those of people who have more time to contribute. Nonetheless, here are some comments and questions I have after reading the user documentation and trying to absorb some of the API documention (http://doctrine.pengus.net/API/index.html). Some of these are really questions for Konsta. Lukas, if you have a current email address for Konsta, could you forward him this message, and encourage him to sign up for pear-dev and to participate in a discussion? I assume other people will also have questions. Database Management: -------------------- It's not yet clear to me how Doctrine manages the relationship between the php objects that represent tables and the actual RDBMs tables to which they bind. Does instantiation of a table or record objects automatically trigger creation or verification of the existence of a real table (as occurs during instantiation of a DB_Table object)? I found it hard to figure this out. I don't see Doctrine methods for creation, alteration, and dropping of tables and sequences, similar to those provided by the MDB2 Manager classes and the constructor and some methods of DB_Table. There are some reasons to have database management (data definition language) methods integrated with data manipulation and query methods, because it allows table creation and mofification to be based on abstract data types. The MDB2 Manager and MDB2_Schema classes also provide reverse-engineering of table schemas. (I should mention that table modification is inherently more complicated in a database with foreign key relationships, since you have to make sure that the PHP database model remains sane and accurate when tables are modified. This is something that I haven't fully grappled with in DB_Table_Database.) If Doctrine does not contain as rich a set of table and sequence management methods as those of MDB2, a new set of Manager classes might be needed, based on PDO and the Doctrine abstract datatypes. If so, I wonder if this could one of the biggest costs of a refocusing of developer effort. Data Type Abstraction: ---------------------- Doctrine has some nice abstract data types that I don't think exist in MDB2, namely, enumerations, arrays, and objects (arrays and objects are stored in serialized form), as well as those supported by MDB2. One weakness in the current PHP 4 MDB2/DB_Table stack of classes is that the MDB2 and DB_Table abstract data types were defined independently, and are not entirely consistent in either choice of what types to support or how they are stored in different databases. (MDB2 makes more use of native data types, while DB_Table tries to store some things like dates and times in the same way in different RDBMS, using more primivite types). While this has been worked around in the current version of DB_Table, it is something that IMO would be important to fix during any simultaneous PHP 5 rewrites of both packages. More generally, if development of MDB* and MDB*_Table continues, it will be important to try to make the two packages more coherent. One signficant advantage of Doctrine is that it was designed as a single coherent application. A similar level of coherence could be achieved by a simultaneous PHP 5 update of MDB2 and DB_Table, but I don't think it exists yet. One nice feature of MDB2 is the ability to specify the PHP types of the values returned by a query. In contrast, PEAR::DB seems to always return non-null values as strings. It is not clear to me from the user documentation how Doctrine decides the php types of values in the rows returned by queries, or if the user can dictate these. It appears to be possible that the Doctrine could actually have enough information to figure out the appropriate data types automatically (see discussion of DQL below), but I'd like to get this clarified. If Doctrine doesn't include a way to specify returned data types from queries, it is something that should be considered for addition. I'm interested in this partly because I've been trying to figure out how to get DB_Table_Database to choose appropriate data types for columns in the return sets by itself. At a minimum, this requires the ability to specify the types of the columns in the return set and 'knowledge' of the database schema. It is not that hard to implement this for some simple cases, as when the query 'SELECT' clause involves only a series of column names. If the select clause involves expressions or SQL operators, however, it becomes impossible to do this without parsing and analyzing the SQL. SQL Abstraction: ---------------- Doctrine provides a level of SQL query abstraction that does not exist in DB/MDB2/DB_Table (or anywhere else that I know of). In Doctrine, queries can be submitted in a Doctrine Query Language (DQL) that appears to be, essentially, a dialect of SQL, with a very concise way of specifying when known relationships should be used to construct joins. This is silently parsed and internally converted to the appropriate SQL dialect for the backend RDBMS. The use of some sort of RDBMS-indepedendent SQL dialect that can be parsed by the php layer seems to me to be a important advance, both for SQL portability, and because it will enable automation of some things that would otherwise be impossible. In particular, it provides a way of solving my problem of how to teach the php layer to choose php types for the values returned by a query: If the php application parses and analyzes a query, and knows the database schema, it has all the information it needs to do this. I can't tell if Doctrine is already using this information to set return types. Data Type Validation --------------------- DB_Table checks that data is of the correct type for each column before inserting it, and checks that, e.g., date and time variables have the correct formats. The documentation for Doctrine shows how specific validators can be added to columns, including many that are not simply based on the column types (e.g., the email validator). Is there any kind of automatic minimal checking or recasting of data types, based on the declared column types? Referential Integrity --------------------- About the ownsOne and ownsMany methods of declaring relations, the Doctrine documentation says that: "Basically, using the owns* methods is like adding a database level ON CASCADE DELETE constraint on a related component, with the exception that doctrine handles the deletion in application level." Aside from this, how does Doctrine deal with enforcing foreign key integrity? Are foreign key constraints declared in the create table statements, so that they can be enforced by the RDBMS (if supported?) Are foreign keys checked at the application layer upon insertion and updating? If a row that "has" (rather than "owns") another is deleted, is anything done to the referencing columns? What (if anything) is done to referencing columns upon updates of referenced keys? In DB_Table_Database, I've tried provide optional php emulation of all the referential integrity checks and actions that are provided by a fully relational database such as postgreSQL, but not by, e.g., SQLite. This is not a feature that everyone will care about, but it made me curious how Doctrine deals with the issue of referential integrity. XML ---- The documentation for Doctrine emphasizes that configuration of a Doctrine application is not based parsing XML configuration files. Aside from initial configuration, however, XML or some other text format has an important role as a way of communicating a database schema between applications or packages. If, for example, we have form-generation packages separate from Doctrine or MDB2/DB_Table or DB_DataObject, the form generation package will need a format from which it can extract a table or database schema. Similarly, if someone wants to port an application from MDB2/DB_Table to Doctrine, it would be nice if there were a common format for the database schema that one application could output and another could import. If MDB2_schema has the ability to reverse engineer a schema, and Doctrine does not, it would be nice if they could talk to one another. In writing DB_Table_Database, I tried to adopted the MDB2 XML syntax. Igor Feghali and I have now agreed on a syntax for foreign key constraints, which weren't supported by the MDB2 DTD. It might be worth thinking about adopting this or another XML dialect that supports foreign keys as a sort of standard for communication between packages, or at least adding methods to Doctrine to export and import database schemas in some existing XML dialect. Transactions ------------- It appears from the documentation that Doctrine wraps all SQL commands in transactions. If an application issues a series of several related commands, and the programmer wants to wrap the whole sequence in a transaction, will this interfere with the automatic use of transactions, i.e., is it easy to wrap user defined transactions around ones that Doctrine is using internally? How is this done? -- !-------------------------------------------------------------------! ! David Morse email: morse@cems.umn.edu ! ! Dept of Chem Eng & Mat Sci phone: (612)625-0167 ! ! University of Minnesota ! ! Minneapolis, MN 55455 ! !-------------------------------------------------------------------!