Difference between revisions of "SQL Builder - Omit Current Schema"

Revision as of 06:58, 13 June 2007

Omitting the current schema name in SQL Builder generated SQL

The SQL Builder will give the user the ability to omit the current schema name in generated SQL. When this option is switched on, tables which belong to the current schema will be unqualified. For example, if the current schema is DBA and a table BOOKS belongs to this schema, then references to this table in SQL generated by the SQL Builder will be either DBA.BOOKS or just BOOKS, depending on whether the user has chosen to omit the current schema name or not.

These options are useful for a number of reasons:

• They can be used to enforce qualification / non-qualification of tables by the current schema.
• Omitting the current schema name may simplify moving SQL statements from one environment to another. If statements are moved to an environment which has a different current schema name, then if tables are unqualified by the current schema name, it may not be necessary to modify the statements.

User Interface

To configure these options, the user will be able to set a number of properties. They will be able switch an Omit current schema name from generated SQL property on or off, and they will be able to select whether the curent Authorization ID should be used as the current schema name or enter the current schema name by hand.

In the UI, the following controls will be used:

Check-box

Omit current schema name in generated SQL

Radio buttons

Use authorization ID as current schema

Specify current schema in SQL format

Text box

Current schema <current schema>

The Current schema text box will be enabled only when the Specify current schema in SQL format radio button is selected. This gives the user the ability to enter the current schema by hand, if required.

Level at which the properties are configured

These properties could be configured at a number of different levels. In this section, the levels will be described and the pros and cons of each will be discussed.

Component level

These settings could be specified as Eclipse Preferences for the SQL Builder component.

Specifying these properties as Eclipse Preferences gives them scope over all statements defined in the SQL Builder. This makes it very easy to set the properties once and for all. However, it does not make sense to specify that the current schema should be the Authorization ID or to specify it by hand at such a high level. The specification of the current schema name really should apply at a lower level - at the level of Connection Profile or individual SQL file / statement.

In addition, whether a particular statement was defined with the Omit current schema... flag set to on/off needs to be recorded for each statement so that when the statement is reloaded in the SQL Builder, it can be parsed.

Connection Profile level

These settings could be specified as extended properties of Connection Profiles.

Stating these properties at the level of Connection Profile may seem appropriate since the current schema name certainly should be defined for a Connection Profile. However, there is a difficult issue of whether to say that these settings apply specifically to the SQL Builder or not. Setting properties that apply specifically to the SQL Builder in a Connection Profile mixes levels in an inappropriate way. On the other hand, if it is not stated that the settings apply specifically to the SQL Builder, then the user would not know what the settings are for and they are likely to assume the settings apply to all DTP SQL tooling.

SQL File / Statement level

These settings could be specified as properties of individual SQL files / statements.

This gives the user maximum control over whether to omit/include the current schema and over the specification of the current schema name. However, it is perhaps cumbersome to force the user to set these properties for each SQL statement defined in the SQL Builder.

For each SQL file / statement, the settings of the properties should be recorded as metadata or through API calls so that when the file / statement is reloaded, the SQL can be parsed and the settings can be set appropriately in the UI.

Combination of levels

A combination of levels could be used. For example, the Omit current schema name from generated SQL flag could be a component level preference and the method of setting the current schema name could be Connection Profile level properties.

Discussion / Comments

This section is for discussion. Please insert your comments here

Jeremy Lindop

I favour having a component level Omit current schema... preference, together with the setting of the current schema name at the level of SQL file / statement. The user should also have the ability to override the current Omit current schema... at the level of SQL file / statement. Although it could be regarded as awkward to force the user to specify these properties for each SQL statement, this method makes it extremely easy to access the settings while editing statements in the SQL Builder.