SQL Builder - Omit Current Schema

← Back to SQL Development Tools page

Introduction

The SQL Query 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 the table BOOKS belongs to this schema, then references to this table in SQL generated by the SQL Query Builder will be either BOOKS or DBA.BOOKS, depending on whether the user has chosen to omit the current schema or not.

This option is useful for a number of reasons:

• It 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.

This page describes how this option might be configured in the UI through either preferences or properties and raises the question of what level is appropriate for setting these preferences or properties.

User Interface

To configure this option, the user will be able to set some preferences or properties. They will be able toggle an Omit current schema from generated SQL setting, 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 Query 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 settings are configured

These settings could be configured at the component level, Connection Profile level or SQL File / Statement level. In this section, the various options 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 Query Builder component.

Specifying these properties as Eclipse Preferences gives them scope over all statements defined in the SQL Query Builder. This makes it very easy to configure the settings 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 Query Builder, it can be parsed.

Connection Profile level

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

Specifying these settings as Connection Profile properties 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 Query Builder or not. Setting properties that apply specifically to the SQL Query 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 Query 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 Query 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 from generated SQL 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... preference at the level of SQL file / statement. When the user opens an existing SQL statement in the SQL Query Builder, the SQL Query Builder should show the settings of these properties that were used when the statement was last edited. In addition, changing the settings of the properties should have immediate impact on the generated SQL. Although it could be regarded as awkward to force the user to specify these properties for each SQL statement, this method gives maximum flexibility and it makes it extremely easy to access the settings while editing statements in the SQL Query Builder.

Roman Rokytskyy (Firebird Project)

I like the idea of Omit current schema from generated SQL too, but please also respect the value of the supportsSchema() call from the database definition. In other words for those databases that do not support schemas (I speak about Firebird in the first place) the setting should be not available at all (disabled and in unchecked state).