[ 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: 10.1.0.0 > Environment: Generic > Reporter: Satheesh Bandaram > Assignee: Satheesh Bandaram > Fix For: 10.1.0.0 > > 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: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
