Database Table Wizard

OpenOffice.org 2.0 Database Access

Document - ID	Specification Owner	Last Change	Status
	Berend Cornelius	06/09/04	Final
Conforms to	
Applies to	Database Access
Task ID(s)	111616
Category	Feature

Abstract

This document describes the whole functionality of the Table Wizard, covers all user interface related aspects and describes the format of the needed scenario files that the wizard is based on. It is only called from databases where the creation of database tables from OpenOffice is possible.

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

	Name	E-mail Address
User Experience	Matthias Müller Prove	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 Müller Prove	07.06.04
Development	Berend Cornelius	07.06.04
Quality Assurance	Marc Neumann	08.06.04
Documentation	Uwe Fischer	09.06.04
String Review		

Document Change History

Rev. Level	Change	Initials	Date
1.0	Berend Cornelius	bc93774	18/12/03
	Screenshots whith labels updated	bc93774	4/5/04
	Issues of MSC,UFI and MMP worked in	bc93774	6/7/04
	Several Issues worked in, new Dialog page introduced	Bc93774	16.01.05
	issue #i49614# worked in	Bc93774	8/8/05
	Issue #i52335# worked in	Bc39774	04/03/06
	string review	MMP	Apr 3, 2006

Contents

Glossary	1

1	Motivation	1

2	User Scenarios	1

3	Goals	1

4	Requirements and Dependencies	1

4.1	Requirements	1

4.2	Technical Dependencies	1

5	Competitive Analyses	1

6	Detailed Specification	1

6.1	G11n	2

6.2	Error Conditions	2

7	Future Tasks	2

8	Notes	2

9	References	2

Glossary

Term	Description
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.
SQLException	an exception that provides information on a database access error

Motivation

In OpenOffice.org 1.1 the whole Base Module is hidden in the office. Working with it requires a solid know-how. Wizards ease the daily work associated with databases.

User Scenario

Typically the first thing after creating a database the user will create tables inside this database. Tables are major main elements in databases. Of course tables can also be created manually in the database beamer but a wizard for this purpose makes it easier especially as it helps to prevent design defects that possible cannot be easily corrected afterwards when data were already applied.Goals

Requirements and Dependencies

Requirements

The unexperienced and average user should be assisted in creating a professional database table that suits his very own needs, by offering numerous table scenarios like “table for sportsclub” or “private wine cellar” etc. This feature is an addon to the existing program architecture and it helps to make the Base component more noticeable.

Technical Dependencies

The dialog page to subsequently modify the fieldtype of the selected columns (see illustration4) is adapted to the “copy table” dialog in the database beamer. On its third page the dialog depicts all relevant aspects of a selected field column. This functionality is also needed for the table wizard and should therefore be wrapped in an API service to avoid redundancy. This service must be specified in detail. In this respect bug #112123# has to be resolved.

Competitive Analyses

The following screenshot give an impression about how the table wizard of MS Access works:

The MS Access wizard offers a rich set of table scenarios. It only creates tables of MS Access Databases whereas on OpenOffice wizard has to consider more database, that also offer the possibility to add more than one primary field.

A negative point of the MS Access wizard is certainly that it does not offer subsequential modification of the type formats of the table fields. Our current table wizard must make this possible.

Therefor the creation of relations to other tables is one thing that the OpenOffice wizard will skip because this feature is not considered so important.

Detailed Specification

The implementation of the wizard is based on a collection of database table scenarios. These scenarios are provided by an XML metafile specified in wd-so-xml-Tablestructure.sxw. Basically the offered tables are divided in two categories “private” and “business”.

Step 1 -Selecting table fields

Illustration 1 Selecting Fields

Item	English	German	Comments
Label	This wizard will help you to create a database table. You can compose it from a set of offered sample tables	Dieser Assistent hilft Ihnen eine Datenbanktabelle zu erzeugen. Sie können sie aus einem Satz von Beispieltabellen zusammenstellen.	
Label	Select fields for your table	Wählen Sie die Felder für Ihre Tabelle aus	
Radio button	B~usiness	~Geschäftlich	
Radio button	P~rivate	~Privat	
Label	Fields in the ne~w table	Felder ~der neuen Tabelle	
Label	Ca~tegories	~Kategorien	
Label	~Sample tables	~Beispieltabellen	
Label	A~vailable fields	~Vorhandene Felder	
Button	>>	>>	
Button	<	<	
Button	<<	<<	
Button	>	>	
Button	∨	∨	
Button	∧	∧	
Roadmap Item	Select fields	Felder wählen	Resource sollte kurz und bündig sein.
Roadmap Item	Set type formatting	Typformat setzen	
Roadmap Item	Set primary key	Primärschlüssel	
Roadmap Item	Create table	Tabelle erzeugen	

Fieldnames in the listboxes are displayed in the format “tablename & “.” & Fieldname” as seen in “Illustration 1 Selecting Fields”. The name of the table will not appear in the right listbox. When necessary the fieldname obtains a numeric suffix to make it unique amonong the other selected fields.

This dialog page gives the user the opportunity to choose all the fields that he wants to include in his table. The offered table samples listbox (“Available fields listbox”) is repopulated as soon as a new category has been selected. The default category is “Business”. Thus the Sample tables listbox is filled up with all available business tables The fields listbox controls and their respective buttons are consequently disabled.

The interaction of the two listboxes is described in the specification FieldSelection Component. Here it is described how the user then has the opportunity to specify exactly which fields to be included in his query by activating the buttons between the listboxes to merge the selected fields into the “Fields in my table” listbox or by doubleclicking the respective items in the “Available fields” listbox.

The dialog component is initialized in appendmode as specified in FieldSelection Component. This mode allows to select fields from various tables. Consequently the items in the “Fields in my table” listbox won't be deleted when a new category has been selected.The structure of the sample tables is stored in the registry. On the Startup the dialog page is initialized with the first buisines sample table being selected and the “Available Fields” listbox being populated accordingly.

Navigation between steps

The wizard's navigation applies to the specification in New Wizard's Concept Specification. The roadmap is interactive which means that by clicking on a roadmap item the dialog's step switches to the selected one. Clicking “Finish” will move to the last step and create a table. The following default values will be applied unless they have been assigned before:

primary key is applied if supported by the database.

The created database table is opened in edit mode (default)

The database tablename is defaulted as specified in Step 4 Finalizing the wizard

The “Finish” button is enabled as soon and as long as the “Fields in my table” Listbox on the first page is populated.

Step 2 Formatting fields

Illustration 2 Modification of the type formats

Item	English	German	Comments
Label Field Name Feldname Label Field Type Feldtyp Label Length Länge Fixed line Field information Feldinformationen Label Characters Buchstaben	String of an API component (already translated)
Button	+	+	
Button	-	-	
Helptext	Add a field	Ein Feld hinzufügen	(+Button)
Helptext	Remove the selected field	Das gewählte Feld entfernen	(-Button)
Label	Set the type formats of your fields	Setzen sie die Formate Ihrer Felder	
Label	~Selected fields	~Gewählte Felder	

Illustration 3 Screenshot of the CopyTable Dialog

On the second page (see Illustration 2) the user can adapt the field formats of his selected scenario to his very own needs (“Illustration 2 Modification of the type formats”). The user interface is consistent to the “copy table” dialog (“Illustration 3 Screenshot of the CopyTable Dialog”) and there should be an API service available to retrieve all the relevant attributes of a datasource field in dependance of this fieldtype (see ). The controls that represent the attributes of a field must be hidden and also made visible during runtime whenever it makes sense for the selected field.

The “Selected fields” listbox does not support multiselection. The first item is initially selected.

On adding a field (“+” button in “Illustration 2 Modification of the type formats”) a new entry is added to the listbox after the current selection. If no selection is made it is appended to the last item. Its defaultname is the localization of “Untitled”. The default fieldtype is “Text”. The attribute “length” that belongs to the fieldtype is defaulted with 50 chars. The focus is thus set to the fieldname textbox. The controls under the “Field Type” textbox only display attributes according to the selected field type. Changes made to the “Fieldname” textbox are copied to the selected listbox entry after the focus has left the textbox.

When defining a fieldtype the relevant textboxes with their respective labels have to appear at the right position. The service referred to in must be able to handle this in the same way as now in the “CopyTable” dialog. The textboxes receiving the input data for the properties should be of a suitable size for the information they contain.

On removing a field (“-” button in “Illustration 2 Modification of the type formats”) a messagebox pops up (Illustration 4). The selected item is removed from the listbox and the entry that now occupies the position is getting the selection. If the selected item has been the last one in the list the selection remains at the last position in the new list. When no selection is made the “Remove” button must be disabled. The controls describing the fields attributes are then hided.

Illustration 4: Query Messagebox before deleting a field

Item	English	German	Comments
Msgbox	Do you really want to remove the selected field?	Möchten Sie wirklich das selektierte Feld entfernen?	

By means of the “Up” and “Down” butons next to the listbox it is possible to change the order of the included fields in the table. The up and down buttons are disabled when the selected item is the top or the bottom element in the list.

Handling of specific field attributes

Entry Required: Basically all primary keyfields have are set to 'Entry Required = false' (if this attribute exists in the database) If fields are selected as primary keyfields on the next dialog page 'Entry Required must be set to true. In any case this is done when the tablewizard has been finished before the actual table creattion. The user is not notified by this action. If databases strictly demand “Entry required' the attribute is set accordingly too.

Step 3: Primary Key

In the fourth page the user has the option to assign a primary key (“Illustration 5 Primary Keys”). If he chooses to set up a primary key automatically the primary key will be assigned internally. This is also the default option.

If he wants to assign a key from existing fields the subsidiary controls get enabled and he can choose from a listbox the field that should represent the primary key. If the selected fieldtype of the database supports Autoincrementation he may also choose the option “Auto value”. Otherwise this control is disabled. The default value of this control is “Selected”.

Illustration 5 Primary Keys In case the user wants to expand the primary key over several fields he can make a selection in the two listboxes. In analogy to the FieldSelection Component the user chooses the primary fields that he wants to expand the primary key over.
Automatic assignment of a primary key: For the automatic assignment of a primary key a fieldtype must be chosen that supports autoincrementation. If such a fieldtype is not available in the database the subordinate checkbox must be disabled.
Fieldname: The maximal allowed textlength of the control relates to the maximal number of allowed characters for a database. If SQL92Check is enabled for the database a check for special characters is done. Eventual special characters are then eliminated directly after the input of the user.

If the database does not support primary keys (like dBase) this page is left out and does not appear on the roadmap.

Item	English	German	Comments
Label	Decide if you want to use a primary key	Entscheiden Sie, ob Sie einen Primärschlussel verwenden möchten	
Label	It is suggested to have a field in every database table that uniquely identifies each record. This field is called a primary key. Primary keys ease the linking of information in separate tables. Without a primary not data entry to this table is possible.	Es wird empfohlen, für jede Tabelle ein Feld zu haben, dass jeden Datensatz eindeutig identifiziert. Dieses Feld heißt Primärschlussel. Primärschlüssel erleichtern die Verknüpfung von Informationen verschiedener Tabellen. Ohne einen Primärschlüssel ist keine Eingabe in diese Tabelle möglich.	
Radio button	~Add a primary key automatically	Primärschlüssel ~wird automatisch hinzugefügt	
Radio button	~Use an existing field as primary key	~Ein bestehendes Feld wird als Primärschlüssel verwendet	
Label	F~ieldname	Fe~ldname	
Radio button	Create a primary ~key	Primärschlüssel erzeugen	
Radio button	Define p~rimary key by several fields	Definition eines P~rimärschlussel durch mehrere Felder	
Radio button	~Primary key fields	~Primärschlüsselfelder	
Checkbox	Auto ~value	Au~towert	

Step 4 Finalizing the wizard

Illustration 6 Finishing

Item	English	German	Comments
Label	Decide how you want to proceed after creating the table	Entscheiden Sie, wie Sie nach dem Erstellen der Tabelle fortfahren möchten	
Label	~Tablename:	~Tabellenname:	
Label	Congratulations. You have assigned all Information you need to create a table.	Herzlichen Glückwunsch! Sie haben alle Informationen zum Erstellen Ihrer Tabelle eingegeben.	
Label	~How do you want to proceed?	~Wie möchten Sie weiter vorgehen	
Radio button	~Modify the table design	Die Tabellen~struktur weiter verändern	
Radio button	~Insert Data immediately	~Daten sofort eingeben	
Radio button	C~reate a form based on this table	Ein Formular auf ~Basis dieser Tabelle erzeugen	

When switching to the last page of the wizard the title textbox gets the focus. Its default value is the concatenation of the prefix “My” + name of the first selected table (see Step 2) and a incrementing suffix to avoid duplicate naming. E.g “FirstSelectedTable_2”. The number of allowed characters is limited by the maximal number of allowed characters for a tablename.If SQL92Check is enabled for the database a check for special characters is done. Eventual special characters are then eliminated directly after the input of the user.

On the fourth page (Illustration 6 Finishing") the user may complete the creation of the table by setting the title and decide if he wants to

modify the table -> The wizard creates the table, closes the dialog and opens the design view of the created table.

work directly with the table-> The wizard creates the table, closes the dialog and opens the edit view of the table.

create form -> the wizard creates the table, opens a writer document and conforming to the specification of the formwizard he creates a form according to the default settings (that may have been modified by the last sessions of the formwizard). The Form Wizard is thus started silently. The wizard dialog is closed then

The created table has to be appended to the tablefilters

Step 4b Finalizing the wizard with catalogs and schemes

In case the database supports catalogs and schemes and also contains such containers, the final page offers one or two listboxes to assign these The catalog preselection is fetched from the API Connection and the preselected schema relates to the user if it exists. Otherwise the first entry is selected. If databases only support catalogs the scheme listbox is not shown just like when catalogs are not supported. In this case the schema listbox resides at the position of the catalog listbox.

Illustration 6 Catalogs and Schemes

Item	English	German	Comments
Listbox Label	Catalog of the table	Katalog der Tabelle	
Listbox Label	Schema of the table	Schema der Tabelle	

Key Board Shortcuts

No Keyboard short cuts are provided.

Error Conditions

Scenario files are not existing or could not be opened, Path is invalid: Stop messagebox: "The files required could not be found.<BR>Please start the %PRODUCTNAME Setup and choose 'Repair'."; wizard is terminated.

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

After finishing the wizard the created table fails to be opened: Error Message with “Stop” icon: "The created table could not be opened."

In case the connection is not be overgiven and cannot be established: Warning messagebox "A Connection to the database could not be established".

An error condition that cannot always be controled by the tablewizard occurs when the creation of a table raises an SQLException. In this case the standard error dialog of the Base application is raised. This error dialog is due to handle the passed over SQL exception in a standardized way. Errors due to duplicately assigned fieldnames are not covered by SQLExceptions. In such a case an error messagebox is raised.

Item	English	German	Comments
Messagebox	The files required could not be found.<BR>Start the %PRODUCTNAME Setup and choose 'Repair'.	Die benötigten Dateien konnten nicht gefunden werden.<BR>Bitte starten Sie %PRODUCTNAME Setup und wählen Sie dort 'Reparieren'	
Messagebox	A table with the title %1 exists already”.	Es existiert bereits eine Tabelle mit dem Namen '%1'	
Messagebox	An error occurred while running the wizard. The wizard will be terminated.	Es ist ein Fehler aufgetreten.	This resource is already existing
Messagebox	The created table could not be opened.	Die erzeugte Tabelle konnte nicht geöffnet werden.	
Messagebox	The field '%FIELDNAME' cannot be inserted because this would exceed the maximum number of %COUNT possible fields in the database table.	Das Feld '%FIELDNAME' kann nicht eingefügt werden, da die maximal mögliche Feldanzahl der Datenbanktabelle von %COUNT damit überschritten würde.	Will be raised on the first or second step when the maximal number of columns is exceeded.
Messagebox	The field '%FIELDNAME' already exists.	Das Feld '%FIELDNAME' existiert bereits.	Appears when the creation of a table fails because of two identical fieldnames

Notes

References

New Wizards' Concept

DialogComponent_FieldSelection.sxw

User Interface Text Style Guides

Database Table Wizard

Page 12

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

Christian Jansen

2003-11-04T09:09:03

Berend Cornelius