Derby
  1. Derby
  2. DERBY-335

Enhance Derby by adding SYNONYM support. A synonym is an alternate name for a view or a table.

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.1.0
    • Fix Version/s: 10.1.1.0
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Generic

      Description

      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.

      1. synonym.patch.jira
        59 kB
        Satheesh Bandaram

        Issue Links

          Activity

          Hide
          Satheesh Bandaram added a comment -

          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.

          Show
          Satheesh Bandaram added a comment - 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.
          Hide
          Satheesh Bandaram added a comment -

          Second version of the patch. Still to implement include

          • Dependency registration and checking. (will be submitted as another patch)
          • May make minor reorg of code to merge resolveTableToSynonym with existing routines.
          Show
          Satheesh Bandaram added a comment - Second version of the patch. Still to implement include Dependency registration and checking. (will be submitted as another patch) May make minor reorg of code to merge resolveTableToSynonym with existing routines.
          Hide
          Satheesh Bandaram added a comment -

          Code has been present in the trunk for sometime. Code changes were submitted as part of SVN checkins: 180459, 189716 and 190182. Feature should be available in 10.1 release.

          Show
          Satheesh Bandaram added a comment - Code has been present in the trunk for sometime. Code changes were submitted as part of SVN checkins: 180459, 189716 and 190182. Feature should be available in 10.1 release.
          Hide
          Satheesh Bandaram added a comment -

          This new feature has been in Derby since 10.1.

          Show
          Satheesh Bandaram added a comment - This new feature has been in Derby since 10.1.

            People

            • Assignee:
              Satheesh Bandaram
              Reporter:
              Satheesh Bandaram
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development