OpenOffice.org Base

Data Source Properties Dialog

Task ID(s)

111090, i12497, i21957

Category	Specification Affects

Abstract

Base, OpenOffice.org's database application, needs new dialogs where the user can edit the properties of an already created data source. To achieve this, three dialogs were designed.

i-Team

Specification Owner	Ocke Janssen	oj@openoffice.org
Marketing (MA)		
User Experience (UX)	Christian Jansen	cj@openoffice.org
Development(DEV)	Ocke Janssen	oj@openoffice.org
Quality Assurance (QA)	Marc Neumann	msc@openoffice.org
Documentation (DOC)	Uwe Fischer	ufi@openoffice.org

Specification Reviews

Version	Reviewer	Comment
Reviewed by UX 08/12/03	Christian Jansen	
UI String Review 04/08/04	Elizabeth Matthis	
Reviewed by QA 08/26/03	Marc Neumann	
Reviewed by DOC 08/18/03	Uwe Fischer	

Document History

Version	Author	Comment
30.07.03	Ocke Janssen	First draft 0.1
04.08.03	Ocke Janssen	
08/12/03	Ocke Janssen	Added some mock-ups
03.03.04	Ocke Janssen	Change of the properties dialog handling.
Final 03/30/04	Ocke Janssen	
04/21/04	Ocke Jansen	2 new strings and a new mock-up inserted at The Advanced dialog with the "Special Settings" page visible
04/21/04	Elizabeth Matthis	Reviewed the 2 new strings
05.05.04	Ocke Janssen	One new string inserted
05.05.04	Elizabeth Matthis	Reviewed one new string and added 3 more related strings to change as discussed with OJ.
05.05.04	Ocke Jansen	One new string for the advanced dialog
1.0 1. February 2005	Ocke Jansen	Some changes for special connection types (e.g. Mozilla)
1.1 6. December 2006	Frank Schönheit	added option “Use keyword AS before table alias names”
1.2 29. August 2007	Frank Schönheit	added option "Form data input checks for required fields"
1.3 12. November 2007	Ocke Janssen	added option "Ignore currency field information"
1.4 12. November 2007	Ocke Janssen	Microsoft Access 2007 added

Contents

1	Category Specification Affects	1

Glossary	1

References	1

2	Motivation	1

3	Goals	1

3.1	How does this feature meet the requirements	1

3.2	How does this feature improve the program architecture dependencies	1

3.3	Technical Dependencies / Requirements	1

4	Detailed Specification	1

4.1	Database Types of a data source	1

4.2	The properties dialog	3

4.2.1	The “General” page	4

4.2.2	The “Additional Settings” page	16

Defaulted properties	24

4.3	The advanced properties dialog	25

“Generated Values”	25

"Special Settings"	25

4.4	The Connection Type wizard	28

Glossary

Term	Description
<Term 1>	

References

http://dba.openoffice.org/specifications/character_sets.html

http://dba.openoffice.org/specifications/AutoIncrementValues.html

http://dba.openoffice.org/howto/param_subst.html

http://dba.openoffice.org/howto/IgnoreDriverPrivileges.html

http://dba.openoffice.org/howto/AppendTableAlias.html

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

Motivation

The user needs the possibility to change the settings of an existing data source. For instance to change the user name if needed, or to enable the substitution of parameter names.

Goals

The goal is to define a dialog where the user can easily change all data source properties, which depends on the database type, can be changed.

How does this feature meet the requirements

This feature is a requirement from the new database application. The database application contains a menu entry from where this dialog can be opened.

How does this feature improve the program architecture dependencies

Technical Dependencies / Requirements

The database drivers must return the properties, which they support, when calling the the com.sun.star.sdbc.XDriver method getProperties().

Detailed Specification

Database Types of a data source

In the previous releases of OpenOffice 1.1+, there exists different database types which confuses the user most of the time. For this reason the database type will be more database specific and ease the connection to existing database formats. In particular these database types are:

MySQL (ODBC)

MySQL (JDBC)

Adabas D

Oracle JDBC

JDBC

ODBC

dBASE

MS Access

ADO

Text

Spreadsheet

PostgreSQL

Mozilla Address Book

MS Outlook Address Book

Windows Address Book

LDAP Address Book

Evolution Address Book

Microsoft Access 2007

The order of the types above is mandatory. In the further paragraphs, it will be described which properties the user can modify for each single driver, which are set by default and further on the platforms limitations some drivers have.

String list

Item	English	German	Comments
Title	Connection Type	Verbindungstyp	Title of the dialog
ListBox entry	MySQL (ODBC)	MySQL (ODBC)	Database type; Product name---do not translate!
ListBox entry	MySQL (JDBC)	MySQL (JDBC)	Product name---do not translate!
ListBox entry	Adabas D	Adabas D	Product name---do not translate!
ListBox entry	Oracle JDBC	Oracle JDBC	Product name---do not translate!
ListBox entry	JDBC	JDBC	Product name---do not translate!
ListBox entry	ODBC	ODBC	Product name---do not translate!
ListBox entry	dBASE	dBASE	Product name---do not translate!
ListBox entry	Microsoft Access	Microsoft Access	Product name---do not translate!
ListBox entry	ADO	ADO	Product name---do not translate! Stands for: “ActiveX Data Object”
ListBox entry	Text	Text	
ListBox entry	Spreadsheet	Tabellendokument	
ListBox entry	PostgreSQL	PostgreSQL	Product name---do not translate!
ListBox entry	Mozilla Address Book	Mozilla Adressbuch	“Mozilla” is a product name---do not translate!
ListBox entry	Microsoft Outlook Address Book	Microsoft Outlook Adressbuch	Product name---do not translate!
ListBox entry	Microsoft Windows Address Book	Microsoft Windows Adressbuch	Product name---do not translate!
ListBox entry	LDAP Address Book	LDAP Adressbuch	“LDAP” is not to translate!
ListBox entry	Evolution Address Book	Evolution Adressbuch	“Evolution” is a product name---do not translate!
ListBox entry	Microsoft Access 2007		

The properties dialog

With the help of the properties dialog an user can modify the settings for the data source. The dialog contains at least one tab-page. Some types make it necessary to append an extra page which contains settings like the character set to convert the strings from the database. These pages are:

the “General” page

the “Additional Settings” page

The pages will be described later on.

What follows is a list of all available properties and which database type supports which property. The default values of these properties will be explained in the paragraph “Defaulted properties”.

Property	MySQL ODBC	MySQL JDBC	Adabas	MS Access (2007)	PostgreSQL	JDBC	Oracle JDBC	ADO	ODBC	dBASE	Spreadsheet	Mozilla	Outlook	Outlook Express	Evolution	LDAP address book	Text
name of the data source	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
database type	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
name of the ODBC data source	X								X								
user name and the need for a password	X	X	X	X	X	X	X	X	X		X					X	
character set	X	X	X		X			X	X	X							X
MySQL Java driver class name		X															
Oracle Java driver class name							X										
Java driver class name						X											
database (name or path to file)		X	X	X	X		X			X	X						X
host		X	X		X		X									X	
port		X			X		X									X	
SQL92 naming constraints			X	X		X	X	X	X	X							X
retrieve auto generated values auto increment statement to create an auto increment query to fetch the auto increment after insert						X	X	X	X								
use catalog for file-based databases									X								
driver settings									X								
show inactive records										X							
text contains headers																	X
field separator																	X
text separator																	X
decimal separator																	X
thousand separator																	X
extension																	X
parameter name substitution	X	X			X	X	X	X	X								
ignore driver privileges	X	X			X	X	X	X	X								
append table alias name	X	X	X	X	X	X	X	X	X								
use AS before table alias name	X	X	X	X	X	X	X	X	X								
Boolean comparison mode	X	X	X	X	X	X	X	X	X								
Suppress version column	X	X	X		X	X	X	X	X								
Indexes										X							
Base DN																X	
Records max.																X	
Enable Outer Join Escape Sequence	X	X		X	X	X	X	X	X								
Use Catalog in Select statement						X		X	X								
Use Schema in Select statement						X		X	X								
Add ASC or DESC at index statement						X		X	X								
Line End format (Unix/DOS)	X	X	X	X	X	X		X	X	X							
Check for required fields	X	X	X	X	X	X	X	X	X								
Ignore currency field information							X										

Table 1Database type properties

The “General” page

The “General” page contains the information which is needed at least to establish a connection to a database. The properties which will be displayed here depends on the database type of the database file. It can not happen that a data source doesn't have a database type. When a Java driver class name is necessary, a button allows the user to test if this driver is loadable, e.g. to check if the name is typed correctly and that the Java class is available in the systems class path. Additionally this page has a button which the user can use to test his connection, to verify that it is working. The handling of all database type dependent properties are described in Database type properties. See Illustration 1 The "General" page

The properties which should appear on the “General” page are (dependent on Database type properties):

user name and the need for a password

name of the ODBC data source

database (name or path to file)

Java driver class name

Host name for LDAP Address Book

If the database type allows to browse for a path (which should be shown as system path) or file or for example an ODBC data source name, an Browse button will also be available.

A special handling must be implemented for the database type JDBC. There the URL prefix jdbc: must be visible, because most users just copy the URL string from the database documentation.

For database types which do not need additional information to connect to the database, the “General” page doesn't exist. These types are:

Mozilla Address Book

Windows Address Book

MS Outlook Address Book

Evolution Address Book

Illustration 1 The "General" page

Illustration 2 Connection Test dialog

String list

Item	English	German	Comments
Dialog Title	Database Properties	Datenbankeigenschaften	
Title	General	Allgemein	The title of the tab page
FixedLine	General	Allgemein	Occurs in every following dialog
FixedText	Path to dBASE files	Pfad zu den dBASE Dateien	
PushButton	Browse...	Suchen...	Occurs also in ODBC, MS Access
PushButton	Test Connection	Verbindung testen	Occurs in every following dialog; “%test” is replaced at runtime by this string.
String	Connection Test	Verbindungstest	Title of the connection test dialog
String	The connection was established successfully.	Die Verbindung wurde erfolgreich hergestellt.	Appears when the connection test was successful

Illustration 3 Text Mock-up

String list

Item	English	German	Comments
FixedText	Path to the text files	Pfad zu den Textdateien	

Illustration 4 Spreadsheet Mock-up

String list

Item	English	German	Comments
FixedText	Path to the spreadsheet document	Pfad zum Tabellendokument	

String list

Illustration 5 The "General " page for a MySQL (ODBC) database type.

Illustration 6 The "General" page for a ODBC database type.

Item	English	German	Comments
FixedText	Name of the ODBC data source on your system	Name der ODBC Datenquelle auf Ihrem System	
FixedLine	User authentication	Benutzer Authentifizierung	
FixedText	~User name	~Benutzername	
CheckBox	Password required	Kennwort erforderlich	

Illustration 7 MySQL mock-up

String list

Item	English	German	Comments
FixedText	Name of the MySQL database	Name der MySQL Datenbank	

Illustration 8 The "General " page for a Oracle database type.

String list

Item	English	German	Comments
FixedText	Name of the Oracle database	Name der Oracle Datenbank	

Illustration 9 The "General " page for a Adabas D database type.

String list

Item	English	German	Comments
FixedText	Name of the Adabas D database	Name der Adabas D Datenbank	

Illustration 10This dialog appears when opening it for an Adabas D connection type

String list

Item	English	German	Comments
Button	Create...	Erstellen...	In SO only: Opens the dialog “Create New Adabas D Database”
Dialog Title	Create New Adabas D Database	Neue Adabas D Datenbank erstellen	
Dialog Title	Local Databases	Lokale Datenbanken	
Fixed text	Choose a database	Wählen Sie eine Datenbank	

Illustration 11 The "General " page for a MS Access database type.

String list

Item	English	German	Comments
FixedText	Microsoft Access database file	Microsoft Access Datenbankdatei	

Illustration 12 The "General " page for the Mozilla Address book

String list

Item	English	German	Comments
FixedText	No more settings are necessary. To verify that the connection is working, click the '%test' button.	Es werden keine weiteren Einstellungen benötigt. Um die Verbindung zu prüfen, klicken Sie auf die Schaltfläche '%test'.	“%test” is replaced by the string “Connection Test”

Illustration 13 The "General " page for a LDAP address book database type.

String list

Item	English	German	Comments
FixedText	~Host name	~Rechnername	

Illustration 14 The "General " page for a JDBC database type.

Illustration 15 Dialog appears when a JDBC driver was loaded successfully

String list

Item	English	German	Comments
FixedText	~JDBC driver class	~JDBC-Treiberklasse	
FixedText	~Data source URL	D~atenquellen-URL	
FixedLine	JDBC properties	JDBC-Eigenschaften	
PushButton	Test Class	Klasse testen	Occurs also in MySQL Additional settings
String	JDBC Driver Test	Test des JDBC-Treibers	Noun phrase; Title of the Java class test dialog
String	The JDBC driver was loaded successfully.	Der JDBC-Treiber wurde erfolgreich geladen.	On success Dialog appears when a JDBC driver was loaded successfully
String	The JDBC driver could not be loaded.	Der JDBC-Treiber konnte nicht geladen werden.	On error

String list

Item	English	German	Comments
String	No Adabas D data sources were found on your system.	Auf Ihrem System wurden keine Adabas D Datenquellen gefunden.	

The “Additional Settings” page

On this page the advanced user can change settings which he doesn't want to be defaulted. See Illustration 21 Mock-up for Adabas D settings. These properties are:

character set

MySQL Java driver class name

host

port

use catalog for file-based databases

driver settings

inactive records

text contains headers

field separator

decimal separator

thousand separator

extension

Indexes

Base DN

Records max.

The next string list will only contain the whole strings. Which string should be visible when a database type was selected, can be looked up in the Database type properties.

Illustration 16 MySQL Additional settings mock-up

String list

Item	English	German	Comments
Tab page title	Additional Settings	Zusätzliche Einstellungen	

Item	English	German	Comments
FixedLine	Connection settings	Verbindungsangaben	Occurs often
FixedText	Data conversion	Datenkonvertierung	Occurs often
FixedText	~Character set	~Zeichensatz	Occurs often

Item	English	German	Comments
FixedText	MySQL JDBC d~river class	MySQL JDBC-~Treiberklasse	
FixedText	~Host name	~Rechnername	Occurs also Oracle
FixedText	~Port number	~Portnummer	Occurs also in LDAP and Oracle

Illustration 17 Oracle settings mock-up

String list

Item	English	German	Comments
FixedText	Oracle JDBC d~river class	Oracle JDBC-~Treiberklasse	

Illustration 18 ODBC settings mock-up

String list

Item	English	German	Comments
Checkbox	Use catalog for file-based databases	Katalog verwenden bei dateibasierten Datenbanken	
FixedText	~Driver settings	~Treibereinstellungen	

Illustration 19 Mock-up for LDAP.

String list

Item	English	German	Comments
FixedText	Base DN	Basis-DN	LDAP
FixedText	Maximum number of records	Maximale Anzahl der Datensätze	LDAP

Illustration 20 Mock-up for dBASE.

String list

Item	English	German	Comments
FixedLine	Optional settings	Optionale Einstellungen	ODBC database type
CheckBox	Display deleted records as well	Auch gelöschte Datensätze anzeigen	
FixedText	Note: When deleted, and thus inactive, records are displayed, you will not be able to delete records from the data source.	Achtung: Wenn gelöschte und daher inaktive Datensätze angezeigt werden, können Sie keine Datensätze aus der Datenquelle löschen.	Will be visible when CheckBox before is checked.
Button	Indexes...	Indizies...	Allows the user to set indexes-tables relation.

Illustration 21 Mock-up for Adabas D settings.

String list

Item	English	German	Comments
FixedText	Data ~buffer size (MB)	Daten~puffergröße (MB)	Adabas D
FixedText	~Data increment (MB)	Zu~wachsgröße (MB)	Adabas D
FixedLine	Administration	Administration	Adabas D
FixedText	Control ~user name	Control B~enutzername	Adabas D
FixedText	Control ~password	~Control Kennwort	Adabas D
CheckBox	~Shut down service when closing %PRODUCTNAME	~Service beim Beenden von %PRODUCTNAME herunterfahren	Adabas D
PushButton	Extended...	E~rweitert...	

Illustration 22 Mock-up for the database type Text

String list

Item	English	German	Comments
FixedLine	Row format	Zeilenformat	
Checkbox	~Text contains headers	~Text enthält Kopfzeile	
FixedText	~Field separator	~Feldtrenner	
FixedText	Text ~separator	T~exttrenner	
FixedText	~Decimal separator	~Dezimaltrennzeichen	
FixedText	Tho~usands separator	T~ausendertrennzeichen	
FixedText	File ~extension	Dateie~rweiterung	
String	Text = "\"\t34\t'\t39" ;	Text = "\"\t34\t'\t39" ;	Do not Translate anything except “Text”
String	Text[ english_us ] = "{None}";	Text = "{Keiner}" ;	
String	#1 must be set.	#1 muss angegeben sein.	Error text when a separator wasn't set.
String	#1 and #2 must be different.	#1 und #2 müssen unterschiedlich sein.	Error text when two separators are identical.
String	Wildcards such as ?,* are not allowed in #1.	Platzhalter wie *,? sind in der #1 nicht möglich.	Error text when no wildcards are allowed.

Defaulted properties

Defaulted properties are the properties which the user can use without change. They are set when the user selects a database type like MS Access. These properties can be looked at the “Additional Settings” page.

Property	MySQL ODBC	MySQL JDBC	Adabas	PostgreSQL	JDBC	Oracle JDBC	ADO	ODBC	dBASE	LDAP address book	Text
character set	System			System		System
Java driver class name		X				X					
host		localhost	localhost							
port		3306				1521				389	
use catalog for file-based databases								FALSCH	
driver settings											
inactive records									FALSCH		
text contains headers											WAHR
field separator											;
text separator											"
decimal separator											.
thousand separator											,
extension											txt
Records max.										100	

Table 2Default properties

The advanced properties dialog

This dialog opens when the user selects under the menu Edit->Database the entry “Advanced Properties”, which allows the user to enable/disable special properties. These properties are grouped on two pages. The first page is named “Generated Values”, the second one "Special Settings". The properties have to be divided like stated below. See Illustration 23 The Advanced dialog with the "Generated Values" page visible and Illustration 24 The Advanced dialog with the "Special Settings" page visible

“Generated Values”

retrieve auto generated values

auto increment statement to create an auto increment

query to fetch the auto increment after insert

"Special Settings"

SQL92 naming constraints

append table alias name

use AS before table alias name

ignore driver privileges

parameter name substitution

Suppress version column

Boolean comparison mode

Ignore currency field information

when enabled it specifies that all columns should not be treated as currency fields nonetheless what the used database driver returns.

Enable Outer Join Escape Sequence

specifies that the select statement for joins uses the {OJ ...} syntax to express the join.

Use catalog name in SELECT statement

Use schema name in SELECT statement

Add ASC or DESC at index statement

Line End format (Unix/DOS)
specifies whether multi-line text written into the database (when entered in forms or the table/query data view) should contain DOS or Unix line ends

Check for required fields
specifies whether OOo Base should, when the user enters data in a form, check for required fields. If this option is enabled, Base will not submit the entered data to the database if a control a) is bound to a field for which is data is required and b) no data has been entered so far. This option might be disabled for backends where OOo is not reliably able to determine whether the server will fill in own default data for required fields.

Illustration 23 The Advanced dialog with the "Generated Values" page visible String list

Item	English	German	Comments
Tab page	Generated Values	Generierte Werte	http://dba.openoffice.org//specifications/AutoIncrementValues.html
Tab page	Data Handling	Daten Handhabung	

Item	English	German	Comments
Dialog Title	Advanced Properties	Erweiterte Eigenschaften	
FixedText			
FixedText			
FixedLine	Settings	Einstellungen	See The Advanced dialog with the "Generated Values" page visible
CheckBox	Re~trieve generated values	Generierte Werte ~berücksichtigen	See The Advanced dialog with the "Generated Values" page visible
FixedText	~Auto-increment statement	~Auto-Increment-Befehl	See The Advanced dialog with the "Generated Values" page visible

Illustration 24 The Advanced dialog with the "Special Settings" page visible String list

Item	English	German	Comments
FixedText	~Query of generated values	Abfrage der generierten ~Werte	See The Advanced dialog with the "Generated Values" page visible
FixedLine	Options	Optionen	See The Advanced dialog with the "Special Settings" page visible
CheckBox	Use SQL92 naming constraints	SQL92 Namensbeschränkung verwenden	See The Advanced dialog with the "Special Settings" page visible
CheckBox	Append the table alias name in SELECT statements	Den Aliasnamen von Tabellen im SELECT-Befehl anhängen	See The Advanced dialog with the "Special Settings" page visible
CheckBox	Use keyword AS before table alias names	Schlüsselwort AS vor Tabellen-Alias-Namen verwenden	
CheckBox	Ignore the privileges from the database driver	Die Rechte vom Datenbank Treiber ignorieren	See The Advanced dialog with the "Special Settings" page visible
CheckBox	Replace named parameters with '?'	Benannte Parameter durch '?' ersetzen	See The Advanced dialog with the "Special Settings" page visible; http://dba.openoffice.org//howto/param_subst.html
CheckBox	Display version columns (when available)	Versionsspalten anzeigen (wenn verfuegbar)	See The Advanced dialog with the "Special Settings" page visible
FixedText	Comparison of Boolean values	Vergleich von Wahrheitswerten	See The Advanced dialog with the "Special Settings" page visible
CheckBox	Use Outer Join syntax '{OJ }'	Für Outer Joins die Syntax '{OJ }' verwenden	See The Advanced dialog with the "Special Settings" page visible
Stringlist	< "Default" ; Default ; > ; < "SQL" ; Default ; > ; < "Mixed" ; Default ; > ; < "MS Access" ; Default ; > ;	< "Normal" ; Standard ; > ; < "SQL" ; Standard ; > ; < "Gemischt" ; Standard ; > ; < "MS Access" ; Standard ; > ;\	Will be shown in the list box for Boolean comparison; DO NOT translate “MS Access”
CheckBox	Use the catalog name in SELECT statements	Den Katalognamen in SELECT-Befehlen verwenden	
CheckBox	Use the schema name in SELECT statements	Den Schemanamen in SELECT-Befehlen verwenden	
CheckBox	Create index with ASC or DESC statement	Index mit ASC- oder DESC-Befehl erstellen	
CheckBox	End text lines with CR+LF	Textzeilen mit CR+LF beenden	
CheckBox	Form data input checks for required fields		
CheckBox	Ignore currency field information		

The Connection Type wizard

When the user wants to change the connection type of the data source, he has to select under the menu Edit->Database the entry “Connection type”. The appearing dialog shows a new page called “Connection Type” followed by the “General” page and the “Additional Settings” page. All pages must be shown in wizard mode. See Illustration 25 Mock-up of the dialog which behaves like a wizard.

database type

For database types which do not need additional information to connect to the database, the “Next” button must be disabled and the “Finish” button must be enabled. These types are:

Mozilla Address Book

Windows Address Book

MS Outlook Address Book

Evolution Address Book

Illustration 25 Mock-up of the dialog which behaves like a wizard.

String list

Item	English	German	Comments
FixedText	Select the type of database to which you want to establish a connection.	Bitte wählen Sie den Type der Datenbank aus, zu der eine Verbindung aufgebaut werden soll.	
FixedText	Database ~type	Datenbank~typ	The database type
FixedText	On the following pages, you can make detailed settings for the connection.\n\nThe new settings you make will overwrite your existing settings.	Auf den nächsten Seiten können Sie dann detaillierte Verbindungs-Einstellungen vornehmen.\n\nBitte beachten Sie dabei,dass diese neuen Einstellungen Ihre existierenden überschreiben.	More space between sentences

It is assumed that the PostgreSQL driver will reach final state to OpenOffice 2.0 release.

com.mysql.jdbc.Driver

oracle.jdbc.driver.OracleDriver

OpenOffice.org Base

Page 2

<Required - Path to specification>

OpenOffice.org Base

Ocke Janssen

2003-07-03T07:38:55