Home » Database » DB_Table » Bug #8767

[2006-09-22 20:00 UTC] dweingart at pobox dot com

Description:
------------
Only PostgreSQL (of the supported DBMS) has the requirement of globally unique index names. Currently DB_Table_Manager expands index names (prepending the table name and appending "_idx") for all supported DBMS. It then limits the index names to 30 characters for Oracle compatibility.

By enforcing both constraints you enforce a lowest common denominator that no database requires, and makes the allowable table and index combinations very limited. Index names in PostgreSQL can be up to <a href="http://www.postgresql.org/docs/7.3/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS">63 characters</a> in length.

My suggestion would be expand index names only for PostgreSQL (the same way you avoid creating primary keys for SQLite). Index names are not referred to directly in SQL statements so it would still preserve portability if the index names differ between DBMS for the same DB_Table object.

[2006-10-24 02:33 UTC] dweingart at pobox dot com

> I see your point that PostgreSQL would allow longer names (up to
> 63 characters). But if we think about portability: What happens
> when you develop your application on PostgreSQL and you're
> required later to switch to (e.g.) Oracle? Now you have the 30
> char limit, but you need to move everything from the PostgreSQL
> database to the Oracle database.
>
> One could argue now that there are not many things that depend on
> index names, but you would at least need to change the index name
> to able to import the SQL dump into Oracle.

I really don't think this is an issue. To migrate from one database
to another in that scenario you would have two choices:

1. Modify the index name(s) in the SQL dump file

2. Let DB_Table automatically create the tables for you in Oracle,
and import a SQL dump of just the data.

In my opinion, neither choice sacrifices portability. But the
restriction on index name length does severely limit the developer's
choices of table and column names.

[2006-10-27 03:19 UTC] dweingart at pobox dot com

> But how to do it? Your initial request sounds like "allow 63
> instead of 30 characters for the index names when PostgreSQL is
> used", right?

Pretty much. :-)

Here's my proposed patch to DB_Table_Manager:

Replace line 1471:

$newIdxName = $table . '_' . $idxname . '_idx';

with this:

if ($phptype == 'pgsql') {
$newIdxName = $table . '_' . $idxname . '_idx';
}

and starting line 1483:

if (strlen($newIdxName) > 30) {
return DB_Table::throwError(
DB_TABLE_ERR_IDX_STRLEN,
"'$idxname' ('$newIdxName')"
);
}

change to:

$limit = ($phptype != 'pgsql') ? 30 : 63;
if (strlen($newIdxName) > $limit) {
return DB_Table::throwError(
DB_TABLE_ERR_IDX_STRLEN,
"'$idxname' ('$newIdxName')"
);
}

I have not researched the question exhaustively, but I think only Oracle is that limited in the number of characters it supports. I could be wrong.