Editing SchemasEvery query in SQL Designer is originally derived from a Schema. A schema is basically a collection of meta-data that describes the database objects and the relationships that exist between those objects. The schema effectively describes what tables and fields may be used in a query and holds the rules and methods that determine how queries may be constructed when using that schema.
Editing SchemasThere are two ways to edit a schema using the sample application. You may load a query from the left hand panel and then click on the edit link shown here, or, you may choose an option from the Schema menu at the top of the application.
Once a schema is loaded, or, a new schema created, the Schema Editor form is displayed, allowing you to construct and/or edit the schema's objects and properties.
SQL Designer provides a simple mechanism for presenting the user with a fully assembled schema editor form that can be invoked simply by instantiating the fSchema form, assigning the cSchema object property and displaying the form. This method is demonstrated in the sample application. It is also worth mentioning here that all of the individual components of SQL Designer's Schema Editor (Schema Panel Design Surface, Field Editor, and Property Panel) are also exposed and documented. If your application demands substantially more flexibility than with the pre-built editor allows, you may easily construct your own schema editor environment within your host application.
Once the schema editor is invoked as shown here, you are provided with access to the following:
Tables & fields may be added, removed and edited. Table objects may be imported directly from a database connection, Imported from a serialized 'xsd' file of a dataset, captured from the host application via an interface provided for that purpose, and lastly, generated via either an SQL statement, or, from an existing SQL Designer query definition!
Properties for fields are extensive and allow the designer to specify the behavior of the field down to the smallest detail.
Fields, Tables and the Schema its-self contain the ability to persist meta-data through the ExtendedProperties property to allow the developer to implement extensibility for integration with host applications.
The Fields tab provides extensive UI presentation and grouping functionality that allows you to completely control the manner in which fields are grouped and presented in the query designer.
Tables may have inner, outer, left, right and full joins specified together with rules that apply to when the joins will be used or enforced.
Comparison types may be defined - either at the global level (applies to all schemas), or, at the schema level. This allows you to support schema specific criteria operators, very powerful.
Value Functions may be defined in Global Settings. A value function performs a context dependent calculation of criteria values. It enables you to enter query criteria such as "Order Date is in This Calendar Quarter" and it also allows you to supplement internally supplied logic with business rules defined by your host application. for an example of this at work, see the example application that supplies the special business logic for the criteria "Is In the 1990's"
Regular Expression based validation rules may be defined in global Settings. These rules can then be used to validate user input criteria values against any pattern that you wish to implement.
Every schema maintains a GUID property that uniquely identifies the schema. When SQL Designer loads a Query, it makes a request to the host application for the schema on which the query was based. It does this by raising the SchemaNeeded event which requests a reference to the schema by using its GUID.
By using GUID identifiers, the host application has the option to save schemas on the file system, using the GUID as the file name, or, by using the GUID as a key for storing the definition in the database or other persistent repository.
It is also worth noting here that Query definitions also have a GUID property and schemas can include references to query definitions as part of their table meta data. for additional information on this approach, see the topic on Queries.