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.
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.
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.