Oracle's 30 character identifier limit

[Kevin McGrath]

Running into a problem on a new project I'm working on.  There is a pre-existing DB with column names that are between 28 - 30 characters.  The issue is that when using a Manager's get_objects method that requires table joins the identifier that Rose creates by default ends up being over 30 characters with the t1_ or t2_ prefix.  Check out ORA-00972 for details.

I thought to myself, no big deal I'll just make an alias on the column of 27 characters or less, but that doesn't seem to work since QueryBuilder uses the full column name.

I made the following changes to QueryBuilder.  Things seem to be working since the rest of the Rose code appears to use the column_mutator_method_name, which will default to the alias if there is one.

310d309
<
313d311
<       my $column_alias  = $args{'meta'}->{$classes->{$table}}->column_rw_method_name($column);
316c314
<       my $unique_column = "${table_alias}_" . $column_alias; # . ($column_alias ? $column_alias : $column);
---
>       my $unique_column = "${table_alias}_$column";
341c339
<               ($unique_aliases ? (' AS ' . $db->auto_quote_column_name("${unique_column}")) : '')
---
>               ($unique_aliases ? (' AS ' . $db->auto_quote_column_name("${table_alias}_$column")) : '')
346c344
<               ($unique_aliases ? (' AS ' . $db->auto_quote_column_name("${unique_column}")) : '')
---
>               ($unique_aliases ? (' AS ' . $db->auto_quote_column_name("${table_alias}_$column")) : '')
348c346
<             ($unique_aliases ? "$short_column AS ${unique_column}" : $short_column));
---
>             ($unique_aliases ? "$short_column AS ${table_alias}_$column" : $short_column));



Is this the right way to go about fixing this? Is there another better way?  the only other way I thought of would be to make unique aliases specifically for the DB (not related to the column alias) for every column that are 30 characters or less.  But then those aliases would need to be mapped correctly on the object creation side... that seemed like more of a nightmare.

Thanks,
Kevin

[Kevin McGrath]

Also, when using an alias on a primary or unique key Rose::DB::Object::Cached breaks.  Cached does not use the mutator methods when getting the primary or unique keys and an error is thrown because the correct functions are not being called.

Changes I've made in the Cached module:

29c29,31
<   my $pk = join(PK_SEP, grep { defined } map { $self->$_() } $self->meta->primary_key_column_names);
---
> #  my $pk = join(PK_SEP, grep { defined } map { $self->$_() } $self->meta->primary_key_column_names);
>    my $pk = join(PK_SEP, grep { defined } map { $self->{$self->meta->column_mutator_method_name($_)} } $self->meta->primary_key_column_names);
>
48c50
<                          map { $self->$_() } @$cols);
---
>                          map { $self->{$self->meta->column_mutator_method_name($_)} } @$cols);
122c124
<     my $pk = join(PK_SEP, grep { defined } map { $_[0]->$_() } $_[0]->meta->primary_key_column_names);
---
>     my $pk = join(PK_SEP, grep { defined } map { $_[0]->{$_[0]->meta->column_mutator_method_name($_)} } $_[0]->meta->primary_key_column_names);
139c141
<                              map { $_[0]->$_() } @$cols);
---
>                              map { $_[0]->{$_[0]->meta->column_mutator_method_name($_)} } @$cols);

[John Siracusa]

On Sat, Nov 14, 2009 at 1:32 AM, Kevin McGrath <kmcg...@baknet.com> wrote:

> Running into a problem on a new project I'm working on.  There is a
> pre-existing DB with column names that are between 28 - 30 characters.  The
> issue is that when using a Manager's get_objects method that requires table
> joins the identifier that Rose creates by default ends up being over 30
> characters with the t1_ or t2_ prefix.  Check out ORA-00972 for details.
>
> I thought to myself, no big deal I'll just make an alias on the column of 27
> characters or less, but that doesn't seem to work since QueryBuilder uses
> the full column name.

That still seems like a lot of work to me, to have to alias all your
columns that are over 27 characters (or 26 if you ever do a join where
you end up with a t10 or higher! :) How about if the QueryBuilder
just truncates the unique aliases to a maximum of 30 total characters
when querying Oracle?

-John

[Kevin McGrath]

If the objects all get created properly on the Manager side, I'm down for anything.  I didn't dive to far into things on the Manager side but I thought the column name had to be named either the actual column name or it's alias to initialize the object.

The only problem I see with the simple truncate is if two columns have the same first 26 or 27 characters.  I don't believe I have anything like this right now, but I guess it could happen.

-Kevin

[John Siracusa]

On Sat, Nov 14, 2009 at 11:18 AM, Kevin McGrath <kmcg...@baknet.com> wrote:
> If the objects all get created properly on the Manager side, I'm down for
> anything. I didn't dive to far into things on the Manager side but I
> thought the column name had to be named either the actual column name or
> it's alias to initialize the object.

We're just talking about the column aliases used in the generated SQL.
They shouldn't affect the objects or data returned.

> The only problem I see with the simple truncate is if two columns have the
> same first 26 or 27 characters. I don't believe I have anything like this
> right now, but I guess it could happen.

Hm, maybe I should check for a column alias first, and only truncate
if one is not found?

-John

[Kevin McGrath]

I think that should suffice for just about all cases.  The only other way I thought of is if you used the same type of t1 table alias for the actual column name so the query would end up looking like:

t1.very_long_oracle_column_name AS t1_col1
t1.very_long_oracle_column_name_2 AS t1_col2

-Kevin