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

[GoogleCodeExporter]

I vote for this as well. 

We use synonyms for all production system, as a mechanism to limit the database 
privileges granted to the applications. Under development, each developer works 
with his own database account, running directly against the tables. In 
production, tables are accessed from a different schema using grants and 
synonyms (to make them appear as objects in the app users' schema). This 
prevents malicious or accidental deletion of tables and data under audit-trail 
regulations, as none of the accounts used to access the database have the 
necessary privileges. 

Also, refactoring of databases used by several applications is simplified if 
the tables are accessed using synoyms: A changed table structure can 
accompanied by  backwards-compatible views and a redirect of the legacy 
application's synonyms to the views. 

Synonyms in h2 would make testing all kinds of sql stuff with h2 a lot simpler, 
and even make h2 a candate for being used in real production. 

Actually, synonyms are quite similar to symbolic links in unix: there is 
nothing you can do with them that cannot be done without. However, they are a 
magnificent  utility for making simple single-user and complex multi-user 
setups appear identical to the user. 

Original comment by eirik.m...@gmail.com on 28 May 2015 at 12:54

[GoogleCodeExporter]

Also, there seems to be some common-sense "requirements" (clarifications) 
missing from the above wish-lists:
- synonyms are ordinary database objects, members of a schema and catalog.
- It should not be possible to create a synonym if the name is already used by 
some other object in the user's schema (same rule as any other 
table/view/sequence). 
- synonyms can not refer to a synonym. 
- it is (usually) not possible to grant access to a synonym, they belong to the 
schema of the logged-in user. 
- all privileges check / access control is performed against the object 
referred to by the synonym: select from a table referred to by a synonym is 
only sucessfull if the user is granted 'select' on the table. 

Original comment by eirik.m...@gmail.com on 28 May 2015 at 1:15

[GoogleCodeExporter]

I'm sorry but I will not have time to work on this. Patches are welcome!

Original comment by thomas.t...@gmail.com on 28 May 2015 at 7:58

[GoogleCodeExporter]

There was a patch provided above... not sure if you've had a chance to look at 
it?

Original comment by djgred...@gmail.com on 29 May 2015 at 12:13

[GoogleCodeExporter]

I see, I will have a look at it. Thanks!

Original comment by thomas.t...@gmail.com on 29 May 2015 at 4:24

• Changed state: Accepted

[GoogleCodeExporter]

Attaching the patch updated for current trunk, please take a look.

Original comment by sudeep.a...@gmail.com on 30 Jul 2015 at 11:20

Attachments:

• 533-Synonym-implementation.patch

[GoogleCodeExporter]

Patch 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 90051=Synonym {0} already exists for an operation where 
user tries to create a synonym with a duplicate name.
2.  Added error code 90056=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 30 Jul 2015 at 11:27