[Coefficient-devel] Re: [Coefficient-users] SQL exceptions + Oracle incompatibility

[Thomas F. <tfo...@us...>]

(Sebastien: this is to the devel list to discuss how and when we fix the
problem. Please contribute to the discussion if necessary, as I don't
think any of the coefficient developers actively use Oracle)

We have an issue with identifier name length in oracle (and maybe other
RDBMS's too) See below:

On Thu, 2005-02-10 at 11:33 +0100, sebastien wrote:
> I also decided to test Coefficient with Oracle in order to find out if
> mysql was the responsible and actually I discovered that it is
> impossible to configure the database with Oracle due to 
> an identifier name issue. 
> 
> See extract of the logs below. 
> 
> 4747 [Thread-5] INFO hbm2ddl.DatabaseMetadata  - table not found:
> COEFFICIENT_PROJECT_ATTRIBUTES_DATA 
> 4757 [Thread-5] INFO hbm2ddl.DatabaseMetadata  - table not found:
> COEFFICIENT_PROJECT 
> 4828 [Thread-5] INFO hbm2ddl.DatabaseMetadata  - table not found:
> hibernate_sequence 
> << key: hibernate_sequence is not sequence or table 
> java.sql.SQLException: ORA-00972: identifier is too long 
> 
>         at
> oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) 
>         at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) 
>         at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573) 
> 

I suggest that we (again) rename the tables to shorten them. I propose
the following convention:

- Core tables (previously prefixed with COEFFICIENT_) are rather
prefixed with COEF_
- Project tables (that previously contained the string PROJECT_ as part
of their names) have PROJECT_ replaced by PROJ_
- Statistics tables (that previously contained the string STATISTICS as
part of their names) have STATISTICS replaced by STATS
- Attribute tables (that previously contained the string ATTRIBUTE_ as
part of their names) have ATTRIBUTE_ replaced by ATTR_
- Module tables (that previously contained the string MODULE_ as part of
their names) have MODULE_ replaced by MOD_
- Configuration tables (that previously contained the string
CONFIGURATION_ as part of their names) have CONFIGURATION_ replaced by
CONFIG_

What do we do about the dithaka and csir.workflow tables?

The list of old table names, with their lengths:
COEFFICIENT_ATTRIBUTE  ::  21
COEFFICIENT_ATTRIBUTE_DATA  ::  26
COEFFICIENT_ATTRIBUTE_ELEMENT  ::  29
COEFFICIENT_ATTRIBUTE_ELEMENTS  ::  30
COEFFICIENT_CATEGORY  ::  20
COEFFICIENT_COMPONENT  ::  21
COEFFICIENT_CONFIGURATION  ::  25
COEFFICIENT_CONFIGURATION_PROPERTIES  ::  36
COEFFICIENT_MODULE_STATISTICS  ::  29
COEFFICIENT_MODULE_STATISTICS_ARCHIVE  ::  37
COEFFICIENT_MODULE_STATISTICS_DATA  ::  34
COEFFICIENT_NEWS  ::  16
COEFFICIENT_PROJECT  ::  19
COEFFICIENT_PROJECT_ATTRIBUTES  ::  30
COEFFICIENT_PROJECT_ATTRIBUTES_DATA  ::  35
COEFFICIENT_PROJECT_CATEGORIES  ::  30
COEFFICIENT_PROJECT_COMPONENTS  ::  30
COEFFICIENT_PROJECT_MEMBER  ::  26
COEFFICIENT_PROJECT_MEMBERS  ::  27
COEFFICIENT_PROJECT_MODULES  ::  27
COEFFICIENT_PROJECT_PENDING_MEMBERS  ::  35
COEFFICIENT_PROJECT_STATISTICS  ::  30
COEFFICIENT_PROJECT_STATISTICS_ARCHIVE  ::  38
COEFFICIENT_PROJECT_STATISTICS_DATA  ::  35
COEFFICIENT_PROJECT_STATISTICS_MODULE_STATISTICS  ::  48
COEFFICIENT_PROJECT_VERSION  ::  27
COEFFICIENT_PROJECT_VERSIONS  ::  28
COEFFICIENT_ROLE  ::  16
COEFFICIENT_USER  ::  16
COEFFICIENT_WORKFLOW_XML  ::  24
CSIR_WORKFLOW_DATA  ::  18
dithaka_conversation  ::  20
dithaka_forum  ::  13
dithaka_message  ::  15
dithaka_resource  ::  16
dithaka_statement  ::  17
dithaka_usermailpreferences  ::  27

The above conventions will change this list to:
COEF_ATTRIBUTE  ::  14
COEF_ATTR_DATA  ::  14
COEF_ATTR_ELEMENT  ::  17
COEF_ATTR_ELEMENTS  ::  18
COEF_CATEGORY  ::  13
COEF_COMPONENT  ::  14
COEF_CONFIGURATION  ::  18
COEF_CONFIG_PROPERTIES  ::  22
COEF_MOD_STATISTICS  ::  19
COEF_MOD_STATS_ARCHIVE  ::  22
COEF_MOD_STATS_DATA  ::  19
COEF_NEWS  ::  9
COEF_PROJECT  ::  12
COEF_PROJ_ATTRIBUTES  ::  20
COEF_PROJ_ATTR_DATA  ::  19
COEF_PROJ_CATEGORIES  ::  20
COEF_PROJ_COMPONENTS  ::  20
COEF_PROJ_MEMBER  ::  16
COEF_PROJ_MEMBERS  ::  17
COEF_PROJ_MODULES  ::  17
COEF_PROJ_PENDING_MEMBERS  ::  25
COEF_PROJ_STATISTICS  ::  20
COEF_PROJ_STATS_ARCHIVE  ::  23
COEF_PROJ_STATS_DATA  ::  20
COEF_PROJ_STATS_MOD_STATS  ::  25
COEF_PROJ_VERSION  ::  17
COEF_PROJ_VERSIONS  ::  18
COEF_ROLE  ::  9
COEF_USER  ::  9
COEF_WORKFLOW_XML  ::  17
CSIR_WORKFLOW_DATA  ::  18
dithaka_conversation  ::  20
dithaka_forum  ::  13
dithaka_message  ::  15
dithaka_resource  ::  16
dithaka_statement  ::  17
dithaka_usermailpreferences  ::  27


These are all < 30 chars (which, I think, is the limit on Oracle).

Any objections to this?

-- 
Thomas Fogwill <tfo...@us...>


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
MailScanner thanks transtec Computers for their support.