Here is more info about the functionality and how the patch tries to
address it.
Synonym functionality
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. For example, the standard doesn't discuss CREATE
INDEX statement.
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.
Implementation
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:
- 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.
- 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
- 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.
- 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.
- 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.
- 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.
I could provide more info on how these can be done, if needed, once I
actually implement them.
Daniel John Debrunner wrote:
Satheesh Bandaram wrote:
Could this possibly lead to dead-locks? If someone is trying to create a
table and another trying to create a synonym could possibly block each
other. I have a feeling you have a better way to do this, so what is it?
No i don't have a better way to do this, I'm trying to understand how
you implemented SYNONYNs, your e-mail has the functionality descibed but
not the implementation. To quote an extract from:
http://incubator.apache.org/derby/derby_comm.html#Contribute+Code+or+Documentation
[quote]
Post the patch to the derby-dev mail list or attach it to the Jira
issue. Include the following information with your post:
[snip]
- A description of the changes, e.g. how a feature is implemented or
why the change fixes a bug. Hopefully this will be in comments in the
code but sometimes an overview in the e-mail is useful, or have the
e-mail point to an overview in the code comments.
[snip]
[end-quote]
I imagine this functionality could possibly be implemented using
SYSTABLES and TableDescriptor or SYSALIASES and AliasDescritpor.
I'm sure there would be different problems in each approach, it would be
interesting to see your approach explained.
Dan.
|
