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

Revision as of 07:26, 13 June 2007

Omitting the current schema in SQL Builder generated SQL

The SQL Builder will give the user the ability to omit the current schema 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 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 may simplify moving SQL statements from one environment to another. If statements are moved to an environment which has a different current schema, then if tables are unqualified by the current schema, it may not be necessary to modify the statements.

User Interface

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

In the UI, the following controls will be used:

Check-box

Omit current schema in generated SQL. This toggles the setting to omit/include the current schema in SQL generated by the SQL Builder.

Radio buttons

Use authorization ID as current schema. This says that the current authorization ID should be used as the current schema.

Specify current schema in SQL format. This says that the current schema will be entered by hand in the text box below. If the schema name requires quotation marks in SQL format, then these should be included.

Text box

Current schema <current schema>. This allows the user to enter the current schema name by hand.

The Current schema text box will be enabled only when the Specify current schema in SQL format radio button is selected.

Level at which the properties are configured

These properties could be configured at the component level, Connection Profile level or SQL File / Statement level. 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 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 because it makes sense to define the current schema 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. 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 from generated SQL flag could be a component level preference and the method of setting the current schema 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 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.