SYNONYM support

[GoogleCodeExporter]

Currently H2 database does not support SYNONYM creation.

Following features are expected as part of this request:

1. User should be able to create or replace a SYNONYM for a TABLE, VIEW, 
SEQUENCE OR ALIAS using syntax
CREATE [ OR REPLACE ] SYNONYM [ schemaName. ] synonymName FOR [ schemaName. ] 
synonymTargetName

2. User should be able to refer a TABLE, VIEW, SEQUENCE OR ALIAS with its 
SYNONYM name.

3. User should be able to create a SYNONYM only if the target OBJECT (TABLE, 
VIEW, SEQUENCE OR ALIAS ) is present in the database. If User tries to create a 
SYNONYM where the target object is not present an error will be thrown as 
Synonym target {object name} not found

4. Meta information of synonym should have following information 
     a. synonym schema, 
     b. synonym name,
     c. target object schema,
     d. target object name,
     e. synonym status (will be set to "INVALID" if the target object is dropped independently)

5. Status of a synonym should be set to "VALID" when its created. If the target 
object for a synonym is dropped then the status of the synonym should change to 
INVALID in the synonym meta data information table.

6. User should be able to drop a SYNONYM using syntax
DROP SYNONYM [ schemaName. ] synonymName

Original issue reported on code.google.com by sudeep.a...@gmail.com on 3 Dec 2013 at 9:56

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Reference:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm

Original comment by sudeep.a...@gmail.com on 3 Dec 2013 at 10:01

[GoogleCodeExporter]

Original comment by noelgrandin on 4 Dec 2013 at 8:56

• Changed title: SYNONYM support

[GoogleCodeExporter]

I am working on this and would be submitting a patch soon.

Original comment by sudeep.a...@gmail.com on 4 Dec 2013 at 10:49

[GoogleCodeExporter]

Attaching patch. I wrote the code, it's mine, and I'm contributing it to H2 for 
distribution multiple-licensed under the H2 License, version 1.0, and under the 
Eclipse Public License, version 1.0 (http://h2database.com/html/license.html).

Some notes:

Following new classes are added which forms the base of the SYNONYM 
implementation.

org.h2.command.ddl.CreateSynonym     - To handle the CREATE OR REPLACE SYNONYM 
ddl.
org.h2.command.ddl.DropSynonym   - To handle the DROP SYNONYM ddl
org.h2.schema.Synonym        - Representation of the SYNONYM Object which will have 
following information
   Synonym Name - name for the synonym this will be unique object name for a given schema.
   Target Object name - name of the target object (Table, view, sequence or a function)
   Target Object schema name - schema name of the target object schema.

org.h2.test.db.TestSynonym - Test class for the synonym.

Changes made in org.h2.schema.Schema

1.  Added new HashMap object synonyms which will hold the org.h2.schema.Synonym 
objects.
2.  Updated methods findTableOrView, getTableOrView, findSequence, getSequence 
and findFunction such that these methods can return the respective objects if a 
corresponding synonym exists.
3.  Added method findUsingSynonym to get a object of type (org.h2.table.Table, 
org.h2.engine.FunctionAlias, or org.h2.schema.Sequence) using the corresponding 
synonym name.

New Error Codes:

1.  Added error code 90011=Synonym {0} already exists for an operation where 
user tries to create a synonym with a duplicate name.
2.  Added error code 90021=Synonym target {0} not found for an operation where 
user tries to create a synonym for a target object which does not exists.

Added table named SYNONYMS to INFORMATION_SCHEMA as meta data representation of 
synonyms
The SYNONYMS meta data table will have following information

1.  SYNONYM_CATALOG -- The catalog where the synonym is present
2.  SYNONYM_SCHEMA -- Schema where the synonym object is created
3.  SYNONYM_NAME -- Name of the synonym object
4.  SYNONYM_STATUS -- The status will be "VALID" when a user creates a synonym. 
If the target object for the synonym is dropped without droping the synonym 
then the synonym_status will be updated to "INVALID"
5.  TARGET_SCHEMA -- The schema where the target object is present
6.  TARGET_OBJECT_NAME -- Name of the target object
7.  SQL_STATEMENT -- Sql statement to recreate the synonym.

Original comment by sudeep.a...@gmail.com on 5 Dec 2013 at 10:39

Attachments:

• 533-Synonym-implementation.patch

[GoogleCodeExporter]

Hi,

Thanks for the patch! However, I'm not quite sure if it makes sense to add this 
feature to H2. I have never heard about the "synonym" feature, and I have never 
heard anybody asking for this feature. I don't know of a database that supports 
it except for Oracle. Not even PostgreSQL supports it I think:

http://www.postgresql.org/message-id/18174.1141742777@sss.pgh.pa.us

It does not make sense to add "the kitchen sink" to H2. Supporting each feature 
binds resources (documentation, support, bugfixes, tests, complexity in the 
source code,...). I'm not convinces this feature is useful.

But of course we can leave this issue open, with "patch available". We 
currently don't have this status, but I can add it.

Original comment by thomas.t...@gmail.com on 13 Dec 2013 at 8:01

• Changed state: PatchesWelcome

[GoogleCodeExporter]

SYNONYM is supported by following databases:

1.       IBM Informix: 
http://pic.dhe.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqls.d
oc%2Fids_sqs_0504.htm

2.       Oracle: 
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm

3.       Microsoft SQL Server: 
http://technet.microsoft.com/en-us/library/ms177544.aspx

4.       IBM DB2: 
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ib
m.db2z9.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_sql_createsynonym.htm

Also, Synonym support is listed on H2 Roadmap as a Priority 2 item.

http://www.h2database.com/html/roadmap.html

Original comment by sudeep.a...@gmail.com on 13 Dec 2013 at 8:55

[GoogleCodeExporter]

Could you please also describe the use case (why do you need this feature) in 
detail?

Original comment by thomas.t...@gmail.com on 13 Dec 2013 at 8:59

[GoogleCodeExporter]

Thanks for the consideration.
I completely understand your concern regarding additional code complexity
and the associated resource expenditure.
I would like to have this feature added to H2, but if it clashes with the
current outlook for H2 then I completely respect your judgement.

Just to give you the context, currently we use Oracle as our database and
are accessing using H2 for some limited features.
Synonyms came up as a pain point during this assessment as our application
uses a Oracle Synonym in many places.

Original comment by sudeep.a...@gmail.com on 13 Dec 2013 at 9:37

[GoogleCodeExporter]

Following use cases can be supported by implementing synonym in H2.

1.  User should be able to give an alternate name to identify an object of type 
TABLE, SEQUENCE or FUNCTION.
2.  User should be able to mask the underlying object name from the synonym user.
3.  User should be able to mask the underlying object schema name from the 
synonym user. 
4.  Provide a feature such that application code should be made transparent to 
any changes to underlying object names. This can be achieved by referring the 
objects using synonym names in the code.
5.  Provide user in schema A ability to refer a table X in schema B. Here schema 
B is not visible to user in schema A. Synonyms are a perfect fit for this 
scenario where we create a synonym in Schema A as “CREATE SYNONYM TEST_SYNM 
for B.X”

Original comment by sudeep.a...@gmail.com on 13 Dec 2013 at 9:52

[GoogleCodeExporter]

Hi guys,

Any idea if/when this will be added?

Take care,

Daniel

Original comment by djgred...@gmail.com on 3 Jan 2014 at 4:18

[GoogleCodeExporter]

Hi,

Requesting to add this feature in upcoming releases.

Regards,
Sudeep

Original comment by sudeep.a...@gmail.com on 2 Jun 2014 at 3:28