db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Satheesh Bandaram (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-335) Enhance Derby by adding SYNONYM support. A synonym is an alternate name for a view or a table.
Date Sun, 05 Jun 2005 00:07:40 GMT
    [ http://issues.apache.org/jira/browse/DERBY-335?page=comments#action_12312666 ] 

Satheesh Bandaram commented on DERBY-335:

Here is some Implementation notes and my patch tries to implement the proposed behavior.

There are two primary parts to the implementation. First, implement the DDL support and second
implement runtime mapping of a synonym to its base table/view.

Create synonym DDL

Derby already supports creating functions/procedures using CreateAliasNode and CreateAliasConstantAction.
In trying to avoid creating more nodes, I have extended these to also handle synonyms. After
parsing create synonym DDL, the bind phase performs some checks on the statement, like disabling
a synonym on a temporary table (these don't exists in catalogs) etc.

Most of the work is performed in the CreateAliasConstantAction. This tries to map schema information
to system catalogs. Some of the constraints are:

   1. TargetSchema needs to be stored as a name, rather than a schemaID. This ensures that
a synonym stays valid even if the targetSchema is dropped and recreated. Similarly a TargetName
needs to be stored as a string, instead of a tableID. TargetName need not be present at the
DDL time as a database object.
   2. While I am providing implementation that allows creating synonyms for tables and views,
it is possible to extend this mechanism to other database objects as well, like procedures
or functions. Some of the database vendors already support this.

There seem to be several options to map this info to existing catalogs

   1. Use the SYSALIASES catalog to store all synonym info. This could be achieved either
by adding more columns to store TargetSchema and TargetTable or by using AliasInfo to store
them as a java object. We currently store function/procedure info by creating a RoutineAliasInfo.
I am proposing we follow the same approach. Since synonyms share same namespace with views
and tables, we need to check if a table/view is already present before allowing a synonym
to be created.
   2. It is also possible to add extra columns to SYSTABLES to hold TargetSchema/TargetTable
info, if the object refers to a synonym. This approach makes it easy to ensure same namespace
is used for synonyms, tables and views. If synonyms are also allowed be created for other
database objects, then we would have to check for any namespace conflicts. Database upgrade
needs to ensure creating these extra columns following a hard upgrade.

CreateAliasConstantAction also needs to catch some error conditions. Attempts to create a
cycling synonym reference should result in an error. This can be achieved by traversing a
synonym chain. Also attempts to create a synonym to a table/view that doesn't already exists
should raise a warning and succeed.

Synonym resolution

When a DML statement refers to a synonym, it needs to be resolved to its base table or base
view. This can be achieved by traversing a synonym chain by reading AliasDescriptors.

Other changes

I will also be providing some other related changes to Derby.

   1. Enhance dblook schema dumping tool to emit synonym info. Changes are required to the
tool and these depend on how the synonym info is stored in the catalogs.
   2. Add required dependency registering and checking. These ensure that when a synonym is
dropped, for example, all plans that depend on the schema are invalidated.

> Enhance Derby by adding SYNONYM support. A synonym is an alternate name for a view or
a table.
> ----------------------------------------------------------------------------------------------
>          Key: DERBY-335
>          URL: http://issues.apache.org/jira/browse/DERBY-335
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions:
>  Environment: Generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>      Fix For:

> Synonym provides an alternate name for a table or a view that is present in the same
schema or another schema. A synonym can also be created for another synonym, causing nesting
of synonyms. A synonym can be used in SELECT, INSERT, UPDATE, DELETE or LOCK TABLE statements
instead of the original qualified table or view name. Note that a synonym can be created for
a table or a view that doesn't yet exists. But the target table/view must be present before
the synonym can be used.
> Synonyms are supported by all major database vendors, including Oracle, DB2 and mySQL.
DB2 also allows CREATE ALIAS statement, which does exactly same as CREATE SYNONYM. Creating
aliases instead of synonyms is not supported by Oracle or mySQL, so I propose that Derby not
support creating aliases. Synonyms are not part of SQL-2003 spec, but is a common-SQL statement
among major database vendors. SQL standard doesn't pay attention to DDLs as much, so I suspect
they skipped synonyms. 
> I will be adding two new DDL statements to Derby:
> CREATE SYNONYM <SynonymSchema>.<SynonymName> FOR <TargetSchema>.<TargetName>
> DROP SYNONYM <SynonymSchema>.<SynonymName>
> Synonyms share the same namespace as tables or views. It is not possible to create a
synonym with same name as a table that already exists in the same schema. Similarly, a table/view
can't be created that matches a synonym already present.

This message is automatically generated by JIRA.
If you think it was sent incorrectly contact one of the administrators:
For more information on JIRA, see:

View raw message