From Ingres Community Wiki

Overview

Ingres presently supports the DROPing of various entities such as tables and views but does not provide a clean method to conditionally drop such without producgin an error. Without this ability, creating such an entity regardless of whether it existed before, needs to be preceded by a DROP of that entity name that will usually then generate an error message.

This change defines a means of deleting an entity if it does exist and doing nothing if such an entity does not exists.

Ingres Enhancement Number

SIR 125040

Design Documents

This project does not require a detailed design document or a functional spec.

The Problem

Ingres presently supports the DROPing of various entities such as tables and views but does not provide a clean method to conditionally drop such without producgin an error. Without this ability, creating such an entity regardless of whether it existed before, needs to be preceded by a DROP of that entity name that will usually then generate an error message.

This change defines a means of deleting an entity if it does exist and doing nothing if such an entity does not exists.

Proposal

• The IF EXISTS syntax is to be implemented for the following database objects:
  • TABLE
  • INDEX
  • VIEW
  • PROCEDURE
  • SYNONYM
  • LINK

• The IF EXISTS syntax will not be implemented for the following database objects:
  • DBEVENT
  • GROUP
  • INTEGRITY
  • LOCATION
  • PERMIT
  • PROFILE
  • ROLE
  • RULE
  • SECURITY_ALARM
  • SEQUENCE
  • TRIGGER
  • USER
  • COLUMN

Scope

PSF Changes

• The parser will need extending to handle the extra syntax and to use this to mute the error message that would otherwise be raised on non-existence.
• The scanner already supports IF and EXISTS so no changes there.

Testing Requirements

Full current HOQA has been passed. New sep tests to test this IF EXISTS change specifically in the server-side parser and front-end parsers have been written. These are: am103 in be/access for the server-side, and glc55, glc56 and glc57 in embed/esqlc for the front-end.

Documentation

1. The new IF EXISTS syntax slots into the existing DROP and REMOVE syntax as shown below:

 DROP [IF EXISTS] name {, name} 
 DROP TABLE [IF EXISTS] name {, name}
 DROP VIEW [IF EXISTS] name {, name} 
 DROP INDEX [IF EXISTS] name  {, name}
 DROP SYNONYM [IF EXISTS] name  {, name}
 DROP PROCEDURE [IF EXISTS] name  {, name} 
 REMOVE [TABLE] [IF EXISTS] name  {, name} 

 Distributed: 

 DROP LINK [IF EXISTS] name  {, name} 
 REMOVE [IF EXISTS] name  {, name}
 REMOVE TABLE [IF EXISTS] name  {, name}
 REMOVE VIEW [IF EXISTS] name  {, name}

2. The effect of IF EXISTS in a DROP or REMOVE statement is to suppress error
reporting for any object specified in the DROP or REMOVE statement that does not exist or exists but is not owned by the user. It is used typically prior to a CREATE or REGISTER statement in order to ensure that the CREATE or REGISTER statement does not fail due to an existing object.

3. The IF EXISTS syntax is implemented for the following database objects:

 TABLE
 INDEX
 VIEW
 PROCEDURE
 SYNONYM
 LINK

4. The IF EXISTS syntax is not implemented for the following database objects: 

 COLUMN
 DBEVENT
 GROUP
 INTEGRITY
 LOCATION
 PERMIT
 PROFILE
 ROLE
 RULE
 SECURITY_ALARM
 SEQUENCE
 TRIGGER
 USER

 If any of these database object keywords are used in a DROP or REMOVE statement with IF EXISTS, the statement will be rejected with an error message indicating a syntax error. For example, the statement:

 DROP GROUP IF EXISTS mygroup;

will return the following error message:

 E_US0EE8 line 1, Syntax error on 'exists'.  The correct syntax is:
     DROP GROUP groupname {,groupname}

5. If DROP is used without specifying an object keyword, Ingres assumes you mean
a table, an index or a view. So for the statement ‘DROP IF EXISTS name’ Ingres
will look for a table, index  or  view called ‘name’. If the table, index or
view exists, Ingres will attempt to drop it; if a table, index or view of that
name does not exist, or exists but is not owned by you, Ingres will not return an error message.

6. Similarly, if REMOVE is used without specifying an object keyword, Ingres
assumes you mean an Ingres Star table. So for the statement ‘REMOVE IF EXISTS
name’ Ingres will look for a table called ‘name’. If the table exists Ingres
will attempt to de-register it; if a table of that name does not exist, or exists but is not owned by you, Ingres will not return an error message.