Query Wizard

OpenOffice.org 2.0 Database Access

Document - ID	Specification Owner	Last Change	Status
	Berend Cornelius	01/26/04	Approval pending
Conforms to	
Applies to	Base
Task ID(s)	Bug #111603
Category	Feature

Abstract

This document describes in detail how the new Query wizard will help the user to create queries. Within up to eight steps the wizard will guide the user through the creation of a query of an arbitrary registered SQL database. As various databases support different extents of features the wizard recognizes the potential of the addressed database in such a way that also users who are unexperienced in database technology succeed in creating a query. The wizard covers the following main features (if supported by the respective database: column aliasing, filter conditions, sorting, grouping, grouping conditions, and ggregate functions.

i-Team Members (The specification Owner is part of the i-Team)

	Name	E-mail Address
User Experience	Matthias Müller Prove /MMP	mprove@sun.com
Development	Berend Cornelius	Berend.Cornelius@sun.com
Quality Assurance	Marc Neumann	Marc.Neumann@sun.com
Documentation	Uwe Fischer	Uwe.Fischer@sun.com

Approved for Implementation

	Approved by	Date
User Experience	Matthias Mueller-Prove	26.01.04
Development	Berend Cornelius	
Quality Assurance	Marc Neumann	
Documentation	Uwe Fischer	01/22/04
String Review	Elizabeth Matthis	15.03.04

Document Change History

Rev. Level	Change	Initials	Date
1.0			<DD/MM/YY>
	some comments inline. Please see my issues at 6Notes as MMP-1 to MMP-8. Another review is required	MMP	08.09.03
	review done	MMP	02.12.03
	review done, approval pending	MMP	26.01.04
	Addressed issues worked in	BC	26.01.04
	Some final changes	BC	08.03.04

Contents

Glossary	1

1	Motivation	2

2	Goals	2

2.1	How does this feature meet the requirements	2

2.2	How does this feature improve the program architecture	2

3	Dependancies	2

3.1	Technical Dependancies/Requirements	2

4	Competitive Analyses	2

5	Detailed Specification	2

5.1	Step 1 Choose fields	3

5.2	Step 2	5

5.3	Step 3	5

5.4	Step 4	7

5.5	Step 5	8

5.6	Step 6	9

5.7	Step 7	11

5.8	Step 8	12

5.9	Error Conditions	14

6	Future Tasks	15

7	Notes	15

8	References	15

Glossary

Term	Description
Select query	A select query is the most common type of query. It retrieves data from one or more tables and displays the results in a datasheet where you can update the records (with some restrictions). A select query can be used to group records and calculate sums, counts, averages, and other types of totals.
Filter conditions	A set of conditions applied to data in order to display a subset of the data or to sort the data.
Aggregate function	A function, such as Sum, Count, Avg, or Var, that can be used to calculate totals
Group	Query data groups contain data according to the field name selected. Functions are executed according to the specified groups. In SQL, this option corresponds to the GROUP BY clause. If a criterion is added, this entry appears in the SQL HAVING.
SQL	Structured Query Language (SQL) is a language used for database queries. In OpenOffice.org you can formulate queries either in SQL or interactively with the mouse.
SQL Statement	An expression that defines an SQL command, such as SELECT, UPDATE, or DELETE, and includes clauses such as WHERE, ORDER BY, GROUP BY and HAVING.SQL strings/statements are typically used in queries and in aggregate functions. This query wizard produces a “SELECT” statement.
Sort order	A way to arrange data based on value or data type. Data may be sorted alphabetically, numerically, or by date. Sort orders use an ascending (1 to 9, A to Z) or descending (9 to 1, Z to A) order.)
Expression	A combination of operators, field names, functions, literals, and constants that evaluates to a single value. Expressions can specify conditions (such as Order Amount>10000) or perform calculations on field values (such as Price*Quantity).)
Alias	The “Alias” property specifies a custom name for a field in a query or a source table or query. It is used for example when the name of a field occurs several times in different commands.
Interaction Handler	If the execution of a command cannot proceed because of an error condition, an OpenOffice.org API InteractionHandler is used to resolve the problem in a consistent way.
OpenOffice.org API	The OpenOffice.org API is the comprehensive specification that describes the programmable features of OpenOffice.org. ##apply Text body style

Motivation

We used to have a Query Wizard in the StarOffice 5.2. That Wizard was able to create the most common queries by offering field selection, sorting and filter conditions. It was not adopted for OpenOffice.org 1.0. The motivation to reintegrate a Query Wizard is to ease the handling of datasources, as the creation of queries is a major topic for the daily work with databases.

Goals

How does this feature meet the requirements

The feature meets the customers needs because it takes him by the hand through a “not quite so easy to handle” task. A wizard has a big advantage in opposition to the manual query creation in the database beamer's design view, because it does not require the user to take the initiative to fulfill all the necessary subtasks to solve his problem. Especially the unexperienced users will be grateful for this assistance. The wizard will be designed to help the user to create queries with the most common features. A supplementary adaption to his very own needs is still possible by accessing the query design view of the database beamer.

How does this feature improve the program architecture

The “Base” Component will be enriched with a top level functionality to subsequently perform several tasks.

Dependancies

Technical Dependancies/Requirements

It is planned to improve the current UNO service SQL QueryComposer. This task would be relevant to the implementation of the query Wizard. (Contact is Frank Schönheit.)

Competitive Analyses

In comparison to a major competitor the Query Wizard offers a lot more features and therefor makes it possible to create “more sophisticated” queries. The following screenshots give information about the extent of the feature set of the major competitor. The following set of screenshots show the “Simple Query Wizard” of Microsoft Office Access XP.

Detailed Specification

The result of this wizard is a created SQL Select statement that represents the select query to be created.

The Wizard includes several steps. With each step the user performs a certain subtask needed to create a complete query.

Step 1 Choose fields

Choose fields

Item	English	German	Comments
Label	Fields ~in the query	Felder ~in der Abfrage	
Dialog Title	Query Wizard	Abfrage-Assistent	
Label	Tables	Tabellen	
Label	Available fields	Verfügbare Felder	
Right pane Title	Select the fields (columns) for your query.	Wählen Sie die Felder (Spalten) für Ihre Abfrage aus.	
Roadmap Item 1	Field selection	Felderauswahl	
Roadmap Item 2	Sorting order	Sortierreihenfolge	
Roadmap Item 3	Search conditions	Suchbedingungen	
Roadmap Item 4	Detail or summary	Detail oder Zusammenfassung	
Roadmap Item 5	Grouping	Gruppierung	
Roadmap Item 6	Grouping conditions	Bedingungen der Gruppierung	
Roadmap Item 7	Aliases	Aliasnamen	
Roadmap Item 8	Overview	Übersicht	

The wizard is started with a connection to a database passed over. The listbox “Tables” (“Choose fields”) contains all tables of the database and is filled on start up. The order of the tables is the same as when you view them in the database beamer in OOo1.1.

The first wizard page is about the extraction of all the fields of the datasource tables that are to be included in the query. The handling of this page is specified in FieldSelection Specification.

As soon as a database table has been selected immediately all available fields of this table are put into the “Available Fields” listbox (Initially no command has been selected). The user then has the option to specify exactly which fields to be included in his query by activating the buttons between the listboxes to populate the “Fields im my Query” listbox.

The dialog component is initialized in appendmode as specified in FieldSelection Specification. This mode allows to select fields from various tables in one database. As the number of tables to be included in a select statement is limited for certain databases the “Tables” listbox is disabled as soon as this limit is reached. No further feedback is given.

Fieldnames in the listboxes are displayed in the format “TABLENAME.FIELDNAME” as seen in the illustration “Choose Fields”

As soon as the “Fields in my Query” listbox contains items the “Next” button and the “Finish” button gets enabled. Contrarily the “Next” button gets disabled when the “Fields in my Query” listbox is empty.

Clicking “Finish” will create an SQL query resulting from a simple Select Statement with only a “SELECT” clause and a “FROM” clause. The name of the query is defaulted according to the following pattern:

“Query_” + FirstincludedTablename + + Index”.

With “FirstincludedTablename” being the table that the first field belongs to and

Index being a suffix that is incremented if a query with that name exists already. The default of Index is “”.

Generally the Finish button will add all applied settings to the SQL statement on each step.

Roadmap Handling

The Roadmap is initialized in “Complete” mode. On start up it has to be checked which wizard features may be applied to the datasource. E.g “Grouping” and aggregate functions are not supported by all databases and must be left out then. Only available feature appear in the roadmap.

The roadmap must be implemented as an active roadmap. On Startup all Roadmap items are disabled as long as the “Fields in my Query” listbox is empty.

This means that according to the Common Wizards Specification clicking on a Roadmap item will switch to the appropriate step and the roadmap items behave like hyperlinks. Only the Roadmap item “Assign Grouping conditions” must be disabled as long as its previous page (“Assign Groupings”), that is a prerequisite to this dialog page, has not been filled out.

The “Calculated functions” item is disabled when the selected datasource supports aggregate functions but when in the session no numeric fields have been selected.

Resolving Roadmap conflicts

Roadmap conflicts might occur when the user switches back to the first page, removes a once included field that is already used as a field criterion or Group field etc. If such a conflict occurs the respective field is also removed from the respective page in the dialog automatically without any further notification for the user. If the removed field has been the only Grouping field assigned the “Assigned Grouping conditions” item is disabled.

If it has been the only numerical field in the query the “Summary functions” item must be immediately disabled too.

If all fields have been removed from the “Fields in my Query” listbox the roadmap immediately returns to the initial state where only the first Roadmap item is specfied and all further items are disabled.

On the Startup the dialog page is initialized with the first table being selected and the “Available Fields” listbox being populated accordingly – unless specific selection has been passed over on the start up of the wizard.

Step 2

Sorting order

Item	English	German	Comments
Right pane Title	Select the sorting order	Legen Sie die Sortiereihenfolge fest	
	The remaining resources are determined inhttp://dba.openoffice.org/specifications/DialogComponent_Sorting.sxw

On the second page (Sorting order) the user has to set the sorting orders according to the specification in Sorting Component Specification.

Searching for duplicately assigned conditions will take place on clicking the “Next” button and have to be handled by the user in the same way as described in Sorting Component Specification.

Step 3

(Filter conditions): An often used feature in queries are filter conditions to reduce the number of included data. The exact handling of this page is described in the Filterconditions Specification.

As described there, only combinations of “AND” and “OR” are possible.

The number of possible filter conditions is restricted to 3.

Filter conditions

Item	English	German	Comments
Right pane Title	Select the search conditions	Wählen Sie die Suchbedingungen	
Radio Button Label	Match all of the following	Entspricht allen folgenden Bedingungen	
Radio Button Label	Match any of the following	Entspricht einigen der folgenden Bedingungen	
Label	Field	Feld	
Label	Condition	Bedingung	
Label	Value	Wert	
Listbox Item	is smaller than	ist kleiner als	
Listbox Item	is greater than	ist größer als	
Listbox Item	is equal or less than	ist kleiner oder gleich	
Listbox Item	is equal or greater than	ist größer oder gleich	
Listbox Item	is not equal to	ist ungleich	
Listbox Item	is equal to	ist gleich	
Listbox Item	like	ähnlich	

Step 4

Fourth page (Calculated fields): This page is only displayed when there are numerical fields within the query that allow the appliance of aggregate functions. See also Resolving Roadmap conflicts.

The frame with the depicted listboxes is subsidiary to the option button “Summary query” and disabled if this option button is not selected. In this case also the following two pages where Groups are assigned are disabled because Groupings generally only make sense along with numeric functions.

The button “-” removes the last row of controls consisting of two listboxes filled up with all numerical fields and all supported aggregate functions.

The button “+” appends a row of controls with no appended listbox being preselected.

There is no limitation of applied aggregate functions. The scrollbar becomes active as soon as the last aggregate function does not fit into the frame anymore. In this case the aggregate functions move up one item.

If an aggregate function has been duplicately assigned an exclamation Messagebox pops up on clicking the “Next” button (Illustration 5). In this case the dialog won't switch to the next step. It would not make sense to prevent the user from selecting certain combinations twice because the wizard would then also make it much harder for the user to change the orders of the aggregate functions subsequently (but before moving to a next step.

The items describing the respective aggregate functions should be verbalized as “non technical” as possible. If the word order of the respective Office localization does not match the English word order it has to be kept technical. This must be mentioned as a comment in the respective resource files.

Calculated fields

Calculated Functions-MessageBox (Dialog in Background is deprecated)

Item	English	German	Comments
Right Pane Title	Select the type of query	Wählen Sie die Art der Abfrage	
Radio Button Label	Detailed query (Shows all records of the query)	Detaillierte Abfrage (zeigt alle Datensätze der Abfrage)	
Radio Button Label	Summary query (Shows only results of aggregate functions)	Zusammenfassende Abfrage (zeigt nur Ergebnisse von Aggregatfunktionen)	
Label	Aggregate function	Aggregatfunktion	
Listbox Item	get the sum of	Nehme die Summe aus	
Listbox Item	get the average of	Nehme den Durchschnitt von	
Listbox Item	get the minimum of	Nehme das Minimum von	
Listbox Item	get the maximum of	Nehme das Maximum von	
Messagebox appendix	Please remove one of the assigned functions.	Bitte entfernen Sie eine der zugewiesenen Funktionen	

Step 5

Groups of the query are added to the SQL statement as “Order by clauses” (Calculated Functions-MessageBox). They are applied by means of the above mentioned FieldSelection Component

If the datasource does not support group by clauses this page and the following page are disabled in the roadmap and will be left out. The page is initialized as follows: All fields that no aggregate function is applied to are populated into the right listbox meaning that they have to be Grouping fields. Moving a field from the right hand listbox to the left hand listbox will be prohibited with an exclamation message box

Grouping Selection

Item	English	German	Comments
Right pane Title	Select the groups	Wählen Sie die Gruppierungen	
Label	Group by	Gruppieren nach	

Step 6

(see Grouping Selection) If the datasource supports the creation of conditions on groups these are applied in the same way as the general filter conditions on page 3. Otherwise this page is left out and the respective entry in the roadmap is disable.

Grouping conditions can only be applied to group fields.

Grouping Conditions

Item	English	German	Comments
Right pane Title	Select the grouping conditions	Wählen Sie die Bedingungen der Gruppierung	

Along with the “Group by” clause it is possible to append a “Having” clause to the SQL statement. This “Having clause” additionally defines conditions for the grouping fields (See Illustration 7). It works identically to the filter conditions dialog (Illustration 3).

Step 7

The assignment of aliases is before the last step. Its functionality is described in Assign Alias Specification. Basically it is about the assignment of aliasnames to fieldnames in order to provide a more intuitive “User Interface” name or to avoid confusion among fieldnames with the same name in different tables (Assign Aliases).

Assign Aliases

Item	English	German	Comments
Right pane Title	Assign aliases if desired	Vergeben Sie Aliasnamen falls erwünscht	
Label	Field	Feld	
Label	Alias	Aliasname	

Step 8

Overview

Item	English	German	Comments
Right pane Title	Check the overview and decide how to proceed	Überprüfen Sie die Übersicht und entscheiden Sie wie Sie fortfahren möchten	
Label	How do you want to proceed after creating the query?	Wie möchten Sie nach dem Erzeugen der Abfrage fortfahren?	
Label	Name of the query	Name der Abfrage	
TextBox Content	Query	Abfrage	
Radio Button Label	Display Query	Abfrage anzeigen	
Radio Button Label	Modify Query	Abfrage verändern	
Headline	Overview	Überblick	
	Fields in my query:	Felder in meiner Abfrage:	
Textbox clause	Sorting order:	Sortierreihenfolge:	
Textbox clause	No sorting fields were assigned.	Es wurde keine Sortierreihenfolge festgelegt.	
Textbox clause	Search conditions:	Suchbedingungen:	
Textbox clause	No conditions were assigned.	Es wurden keine Bedingungen festgelegt.	
Textbox clause	Aggregate functions:	Aggregatfunktionen:	
Textbox clause	No aggregate functions were assigned.	Es wurden keine Aggregatfunktionen festgelegt.	
Textbox clause	Grouped by:	Gruppiert nach:	
Textbox clause	No Groups were assigned.	Es wurden keine Gruppierungen festgelegt.	
Textbox clause	Grouping conditions:	Gruppierungsbedingungen:	
Textbox clause	No grouping conditions were assigned.	Es wurden keine Gruppierungsbedingungen festgelegt.	
Separator in Summary Textbox	", "	", "	
Structure clause in Summary Textbox	"<FIELDTITLE> (<FIELDNAME>)"	"<FIELDTITLE> (<FIELDNAME>)"	These tags shall be emedded in a clause like the following: “Fields in my Query: Fieldtitle1 (Table1.FieldName2), FieldTitle2 (Table2.Fieldname3)...”
Structure clause in Summary Textbox	"<FIELDNAME> (<SORTMODE>)"	"<FIELDNAME> (<SORTMODE>)"	These tags shall be emedded in a clause like the following: “Sorting order: FieldName1 (Ascending), Fieldname2 (Descending)...”
Structure clause in Summary Textbox	"<FIELDNAME> <LOGICOPERATOR> <VALUE>"	"<FIELDNAME> <LOGICOPERATOR> <VALUE>"	These tags shall be emedded in a clause like the following: Filter conditions: “Fieldname1 is greater than 0, FieldName2 is like “blabla”

On the last page (Overview) the user has the option to assign a query title and decide whether he wants to further edit the query or display the results immediately after query creation. The dialog remains open untill the query has been created successfully. Errors will be caught with an API interaction handler that delivers a message that gives information about the cause of the failure. This dialog will handle all exception derived from a com.sun.star.sdbc.SQLException. In this case the dialog will remain open.

If the user chooses to display the query the datasource view with the new query is opened. If “Modify Query” is selected the design view of the query is opened.

The “Next” button is disabled.

The name of the query is defaulted to FirstincludedTablename + “_Query” + Index. The incremental index suffix is only appended to avoid duplicate naming as described in ”4.1 Step 1”

The readonly Summary textbox contains a textual summary of all applied configurations. Each line in the text represents the results of the settings applied in one dialog page of the wizard. The text is a concatenation of the following strings. The tagged placeholders substitute lists of fields to be inserted into the string.

“Fields in query: “ + <FIELDNAMES> + <RETURN>

(If applied:) “Sorting order: “ <SORT ORDERS> + <RETURN>

(If applied:) “Filter conditions: ” <FILTER conditions> + <RETURN>

(If applied:) “Calculated functions: <AGGEGATE FUNCTIONS> + <RETURN>

(If applied:) “Grouped by: “ + <GROUPS>

(If applied:) “Grouping conditions: “ <GROUP conditions> + <RETURN>

The following table describes the pattern of each placeholder by depicting one item of the lists represented by each item:

Placeholder	String pattern with assigned Aliases
<FIELDNAMES>	Aliasname1 + “(“ + Tablename1 + “.” + FieldName1 +“), “ + Aliasname2 + “(“ + Tablename2 + “.” + FieldName2 +“), “ ...
<SORT ORDERS>	UIFieldname1 + “ (“ +”Ascending/Descending), “ + UIFieldname2 + “ (“ +”Ascending/Descending), “...
<FILTER conditions>	UIFieldname1 + logical operator1 + “ “ + Value1 + “, ” + UIFieldname2 + logical operator2 + “ “ + Value2 + “, ” ...
<AGGREGATE FUNCTIONS>	Function1 + “ ” UIFieldname1 + “, “ + Function2 + “ ” UIFieldname2...
<GROUPS>	UIFieldname1 + “, “ UIFieldname2 + “, “...
<GROUP conditions>	UIFieldname1 + “ “ + logical operator1 + “ “ + Value1 + “, “ + UIFieldname2 + “ “ + logical operator2 + “ “ + Value2 + “, “...

UI Fieldname: The UI fieldname is either the Alias name of a field if specified on the page before the last dialog page or it is the fully qualified Fieldname that includes the tablename after the pattern: Tablename + “.” + FieldName

The logical operator of a criterion is defined in Filterconditions Specification and it could be for example “is greater than” or “is like”

The value is where a criterion evaluates to like “Fieldname5 is like “xyz””.

To this extend an exemplary string could look like this:

Fields in my query: MyfirstAliasName (Tablename1.FieldName1.), MySecondAliasname (Tablename2. Fieldname1).

Sorting order: Aliasname3 (Descending), Aliasname5 (Ascending).

Filter conditions: Tablename3.Fieldname is like “blablabla”.

Calculated fields: Get the sum of Tablename1.NumericFieldname2.

Grouped by: Aliasfieldname3, Aliasfieldname1.

Grouping conditions: Aliasfieldname3 > ”

String Lists

This dialog will provide no key board shortcuts.

Error Conditions

An error occurred during the creation of the query: These error conditions will be passed over to an API interaction handler that automatically displays the appropriate error message related to the occurred exception. In such a case the Wizard should not be terminated.

An error occurred during execution of the query: An error messagebox with “Stop” symbol pops up; “An error occurred. The query could not be executed".

Unexpected error occurs: Error message with Stop icon: "An error occurred while running the Wizard. The Wizard will be terminated." Wizard is terminated.

Item	English	German	Comments
Error Message	An error occurred.<BR> The query could not be executed	Es ist ein Fehler aufgetreten.<BR> Die Abfrage konnte nicht ausgeführt werden.	These Strings are already existing (BC)
Error Message	An error occurred while running the wizard. The wizard will be terminated.	Während der Ausführung des Assistenten ist ein Fehler aufgetreten. Der Assistent wird beendet.	These Strings are already existing (BC)
Error Message	The condition '<FIELDNAME> <LOGICOPERATOR> <VALUE>' was chosen twice. Each condition can only be chosen once.	Die Bedingung '<FIELDNAME> <LOGICOPERATOR> <VALUE>' wurde zweimal ausgewählt. Jede Bedingung kann nur einmal ausgewählt werden.	Same style as message in http://dba.openoffice.org/specifications/DialogComponent_Sorting.sxw
Error Message	The aggregate function <FUNCTION> has been assigned twice to the fieldname '<NUMERICFIELD>'.	Die Aggregatfunktion '<FUNCTION>' wurde dem Feldnamen '<NUMERICEFIELD>' zweimal zugeordnet.	
Error Message	A field that has not been assigned an aggregate function must be used in a group.	Sie müssen ein Feld, dem keine Aggregatfunktion zugewiesen ist, als Gruppierung verwenden.	

Future Tasks

For a future version it would be an improvement for the user interface if the fields listboxes on the first step would support drag and drop. See “Technical Dependancies/Requirements”.

Notes

References

The dialog component to select the fields from a preselected datasource command is derived from:

http://dba.openoffice.org/specifications/DialogComponent_FieldSelection.sxw

Filterconditions Specification

Common Wizards Concept Specification

DialogComponent_AssignAlias.sxw

DialogComponent_Sorting.sxw

Query Wizard

Page 18

http://dba.openoffice.org/specifications/Wizard_Query.sxw

Christian Jansen

2003-11-04T09:09:03

Berend Cornelius

Elizabeth Matthis