Should Solar_Sql_Entity Support Oracle?

Solar is a simple object library and application repository for PHP5. It is currently in development. This post refers to the object-relation management tool, Solar_Sql_Entity, which follows closely the table data gateway pattern described by Martin Fowler.

Oracle only allows 30 characters for an object name (table, field, index, sequence). While that by itself may be OK, it seriously restricts the operation of Solar_Sql_Entity. (This class is the natural successor to DB_Table.)

One of the points of Solar_Sql_Entity is to make it possible to create a table that will work on *all* of the supported RDBMS backends. This means that it has to submit to a lowest common denominator in some ways.

For example, in PostgreSQL, your index names must be unique across all tables in the database. For example, if you have a “table1” and “table2”, each with an “id” field, it’s not enough to specify an index called “id” on table1 and annother index also named “id” on table2; PgSQL sees that as a conflict. You have to make the index names unique even though they refer to different tables.

Thus, to support PgSQL, the index names would have to be “table1_id” and “table2_id”. This is why DB_Table and Solar_Sql_Entity prefix the table name automatically to your index name (i.e., to make sure there are no index name conflicts if the schema is created in PgSQL). As an additional identifier, the index name gets “_idx” suffixed to it.

You can see where this is going: because of the 30-char limit in Oracle, this means you’re pretty limited in how long your table and field names can be when using Solar_Sql_Entity. Right now, Solar_Sql_Entity limits you to 15 characters for table names and 10 characters for field names, all to make sure we stay under 30 chars for Oracle. This is pretty short, almost too short to be universally useful.

If Solar_Sql_Entity were not to support Oracle, the next highest character limit is 64 (MySQL and MS-SQL). That effectively doubles the available length (~30 chars for table, ~25 chars for field), but at the cost of dropping Oracle support within Solar_Sql_Entity.

Frankly, I’m not sure Oracle is in the “target market” for Solar anyway; the point of Solar is to be provide a toolset to help build apps-for-distribution (although it’s fine for in-place apps, too).

Anybody have any feelings about (or experiences with) this kind of issue, one way or another?