From Ingres Community Wiki

Contents 1 References 2 Long Identifiers 2.1 Enhancement Requests 2.2 Implementation Details 3 Notes 3.1 DDS Review (Sept 24, 2009) 3.2 Documentation considerations 3.3 Test Considerations 3.4 Needs Research/Work 4 Documentation

References

• The main DDS is available here.
• The GCA (including gcacl) DDS is available here.
• The ODBC DDS is available here.

Long Identifiers

Identifiers are the names of objects, such as table names, column names, replicates, replicate sets, and so on, that Ingres and Replicator use.

An identifier is a character string that must start with a character or an underscore. The remaining characters can be letters, numbers, or underscores. On Ingres, all identifiers, including replicates and replicate sets, can be 32 bytes long. However, if you have any database servers in your replication environment that are an earlier version, you must follow the length restrictions for that version. Any valid character in the II_CHARSETxx is allowed, e.g. "tô" or even Chinese glyphs - clach04

User login IDs can be a maximum of ?? bytes (WE SHOULD USE THE LONGEST OS LIMIT FOR THIS). The owner of a table is derived from a user ID and is thus limited to ?? bytes.

Enhancement Requests

• SIR 121123 - Long Identifier Support
• SD Enhancement Number - 126141
• SIR 127579 - Request for User Names in the format of an e-mail address. If possible, this should be addressed within the scope of this feature. This may effect how special characters are tolerated in object names. NOTE: As this is a larger project, it will be considered as a separate project.

Implementation Details

• Increase the size of the following identifiers to 256:

Table, Partition, Column, Procedure, Parameter, Rule, Constraint, Sequence, and Synonym

• The size or of the following will remain unchanged:

Database, Location, User, Owner, Role, Schema, Group, Node, Cpu, Event, Alarm, Profile

• Remove table and owner names from DML log records.

• Modify with data compression catalogs that store long identifiers.

• Upgrade will be necessary for the config file and catalogs impacted by these changes.

Notes

DDS Review (Sept 24, 2009)

• Use varchar instead of char in back end catalogs.

  We decided not to do this, since standard catalog interface uses char.--Stial01 08:36, 1 October 2010 (CDT)

• New identifier maximum is 256
• Increase size for Table, Partition, Column, Procedure, Parameter, Rule, Constraint, Sequence, Synonym.
• Don't increase size for Database, User/Owner/Role/Schema/Group, Location
• Don't increase size of non-identifier catalog columns that happen to be size 32.
• Use varchar(512) for ima classid names.
• Remove table and owner names from DML log records
• Investigate stack requirements, especially during upgrade
• There are no changes to OpenAPI and hence no changes to DAS

Documentation considerations

• Update length of identifiers in the Standard Catalog Interface (e.g. iitables)
• Migration guide alert for new define for IDs that are 32.

Test Considerations

We need to test this through Set, Star, ESQL, ABF, TM, DBPs, LIBQ, JDBC, ODBC, .NET, OpenAPI. What will be the policy when an ingres26 client tries to pull information from a new server that has the large object name support?

Test new client, old server. execute dbproc (byref, say from esqlc or api) with long name dbproc - hopefully will fail with server too old and not make GCA call (if a short name is used, we use the old invoke method anyway).

Test old client, new server; dynamic select on table with long columns, describe of col name (hopefully will either work by magic, or truncate). Worst case dbms will have to truncate

Needs Research/Work

From July 22nd GCA (including gcacl) DDS review meeting, identified the following for further research/work:

• decided that 256 would be the new max name length for identifiers - this constant needs to be available to internals/tool like GCA, netutil.
• Discussed iidbcapabilities value "SQL_MAX_TABLE_NAME_LEN" - this will be taken to be the database procedure name max length
• Cursor names will NOT be covered in the longer name support
• libq needs some work, e.g. esql/c at the very least should be able to use long names (e.g. procedure call)
• COPY TABLE support will also need to be enhanced as copydb/unloaddb use COPY TABLE and this is our official way of backing up a database. This is both a libq and GCA issue.
• We need a test database/plan for testing. Now we have the max length this can be started.
• Should QA older client (e.g. 9.2) against a server with larger names to understand behaviors so they can be documented (and ensure there are no crashes in Ingres tools).

Documentation

Long Identifiers

The maximum length of names for certain objects has increased from 32 to 256 bytes.

Names for the following objects can now be a maximum of 256 bytes:

• Table
• Column
• Partition
• Procedure
• Procedure parameter
• Rule
• Sequence
• Synonym
• Constraint

The maximum length of names for the following objects remains at 32 bytes:

• Database
• User/Owner/Group/Role/Schema
• Location
• Event/Alarm
• Profile
• Node

The maximum length of a collation sequence name is 64 bytes.

The maximum length for names of objects managed by Ingres tools such as Query-By-Forms, Report-By-Forms, Vision, and Visual Forms Editor remains at 32 bytes. (Examples of these objects are Forms, JoinDefs, QBFnames, Graphs, Reports.)

For ANSI/ISO Entry SQL-92 compliant databases, the maximum length of an object name remains at 18 bytes.

The size of related columns in the standard catalog interface has also increased. For example, iitables.table_name, which is a view on iirelation.relid, has increased from 32 to 256.

Changes made to the catalogs and log records as a result of this feature make Ingres 10 incompatible with previous Ingres versions. These changes will invalidate checkpoints and journals from previous versions of Ingres.

Pre-Ingres-10 databases must be upgraded with upgradedb before they can be accessed. Alternatively, you can unload your database before installing Ingres 10 and then reload the database after installing Ingres 10.

An older version (such as 9.2) client can access an Ingres 10 database if you limit names to a maximum of 32 bytes.

Note: Long identifiers are not yet supported in OpenSQL.