Talk:Documentation/OOoAuthors User Manual/Base Guide/Planning

From Apache OpenOffice Wiki
Jump to: navigation, search

I wrote a document called AndrewBase.odt (available online). This is a basic outline used there:

Information Page 2 Copyright 2 Authors 2 Feedback 2 Acknowledgments 2 Publication date and software version 2

1. Introduction    1
1.1. Introductory comments    1
1.2. Document organization and introduction    1
2. Storing images (binary data) in Base    3
2.1. Create the initial Base document    3
2.1.1. Using the GUI    3
2.1.2. Using a macro    3
2.1.3. Using a macro to open the wizard    4
2.2. Create the table    4
2.2.1. Using the GUI    4
2.2.2. Using a macro    5
2.2.3. Using SQL statements to modify tables    7
2.2.4. Refresh the tables    7
2.2.5. Creating and deleting tables using SQL    8
2.2.6. Increase a field's length    9
2.3. Create a form    10
2.3.1. Using the GUI    10
2.3.2. Using a macro    12
2.4. Open a form using a macro    15
2.5. Accessing the binary data    18
2.5.1. Adding binary data    18
2.5.2. Extracting binary data    19
3. One-To-Many relationships    22
3.1. Create the tables    22
3.1.1. Create the DEALER table    22
3.1.2. Create the ITEM table    23
3.2. Define the data relationships    25
3.3. Add data to the DEALER and ITEM tables    26
4. Forms    29
4.1. The internal object model    29
4.1.1. A control's shape is in the draw page    29
4.1.2. A draw page contains forms    30
4.1.3. A control's data model is in a form    31
4.1.4. A control's view model is in the controller    32
4.1.5. Enabling and setting controls visible – an example    32
4.1.6. Finding a control from an event – an example    33
4.1.7. Control model summary    34
4.2. Database Forms act like a result set    34
4.2.1. Duplicate record macro    35
4.3. Show one item and the corresponding dealer    38
4.4. Use a combo box with the dealer id    40
4.5. Use a list box with the dealer name    41
4.6. Relations in a single table    42
4.6.1. Solution    43
4.6.2. Solution characteristics    44
4.7. Use a “help and fill” button    45
5. Many-to-many relationships    46
6. Database fields    47
6.1. Storing numbers    50
6.1.1. Integer numbers    50
6.1.2. Floating point numbers    51
6.1.3. NUMERIC and DECIMAL types    52
6.2. Bit and Boolean Types    52
6.3. Date and time    53
6.4. Text data    53
6.5. Binary data    54
6.6. Other data type    54
6.7. Database sequences and auto-value fields    54
7. A few easy database definitions    56
7.1. Schema    57
8. Database connections    58
8.1. Obtain a database context    58
8.1.1. Registered data sources    59
8.1.2. Unregistering a data source    59
8.1.3. Registering a data source    59
8.2. Connect to a database    60
8.3. Connect using an interaction handler    61
8.4. Connections    61
8.4.1. Extended SDB connections    63
8.4.2. Meta-data    63
8.4.3. Inspecting the meta-data    70
8.4.4. GetBestRowIdentifier    76
8.4.5. GetColumnPrivileges    77
8.4.6. GetColumns    78
8.4.7. GetExportedKeys    79
8.4.8. GetIndexInfo    81
8.4.9. GetPrimaryKeys    82
8.4.10. GetTablePrivileges    82
8.4.11. GetTables    83
8.4.12. GetTypeInfo()    83
8.4.13. GetUDTS    85
8.4.14. GetVersionColumns    85
8.5. Connections     86
8.6. Connections without a data source    87
8.6.1. Delimited text files    91
8.6.2. Fixed width text files    93
8.6.3. Help, I still can not import my CSV file    98
8.6.4. Address books    99
8.6.5. MySQL using JDBC    100
8.6.6. Paradox using ODBC    100
8.6.7. Conclusion    102
9. Connecting to MySQL using JDBC    103
10. Mailmerge    105
11. Copying an entire database    106
12. General utility macros    107
12.1. Choose a directory    108
12.2. Get a document's directory    109
12.3. Choose a file    109
12.4. Finding a (loaded) OOo document    111
12.5. Append to an array    112
12.6. Compare data in an array    113
12.7. Create a property    113
12.8. Create a Point and a Size    114
12.9. Append a data array to a Calc document    114
12.10. Dynamically call object methods    115
12.11. Display numeric constants as meaningful text    119
12.12. Select from a list in a list box    120
13. Database utility macros    122
13.1. Quoting table and field names    122
13.2. Convert between an UNO Date and a Basic Date    122
13.3. Convert a result set to an array of data    124
13.4. Create and populate a dialog from a result set    127
14. Tips and tricks    128
14.1. Limit the number of returned records    128
15. Connect to a Base document using JDBC    130

The content started as a book that I intended to write, but then it was dropped, so I created this instead. It is likely too concentrated on macros for the average person. That said, any content can be removed and used. Something of interest with AndrewBase.odt, is that all included macros are included in the document itself and my be run by clicking on buttons in the document.

Note that Drew Jensen probably has much of interest that he could add.

Areas of interest that should likely be covered, in my opinion, include:

What is a database "key"?

Database design deals with what is known as "normal" forms. This is perhaps very advanced, but I have been sent database documents created by users that had no idea how to deal with redundant data. For example, if I have a doll that can have a "body type". The usual method is to create a table that enumerates body types. Each body type has a primary key acting as a unique identifier. I do not repeat all information related to the body type for each doll. Instead, I use the primary key and reference the body type table.

Dealing with one-to-many and many-to-many relationships. Areas of interest include table design, form design, and reports.

There is an entirely new Report writing system in the works, it is very exciting. This should be covered, perhaps as its own chapter.

Using an internal database. Using an external database such as MySQL. Converting from MS Access.

I am tired, and these are without much thought.

06/30/07 Here is my first thought on an outline

Contents

Introduction

How are the sections laid out

         o Each section for a Wizard will have a description page for each of the dialog pages. Named the same as the page on screen.
         o Each section for a Designer will have a description of the tool bars used, dialogs used and embedded wizards used.
         o Who are you?  [ Help them with where to start reading and suggest a different route through the sections ]
             + I have a Base database ( *.odb file ) and I need to work with it. 
             + I need to create a database, like I have done before with ( XXXX )[Access, FileMaker..]
             + I need to create a database, like I have with Excel or Calc before. 
             + I think I want a database, but have never used a database manager before
         o A brief history of OO.o databases and Base
           I think it will help show why some things are done they way they are in Base.

A lot of this will most likely be covered in the Getting started guide. I am not sure if much of that document shouldn't become this chapter. There would be additions of course.

Base window layout

         o Menus
         o Tool bars
         o Sections 
         o SQL Window

How to open a Base file

The Base module creates files with the extension odb. You can open a Base file just as you would any other file type within OpenOffice.org (OOo). Namely with the File>Open dialog box, double clicking on an Base odb file from your operating system GUI, opening the file via a command line or via a script.

Additionally the File>New>Database wizard maintains a Most Recent Used (MRU) list of the last 5 database files opened.

A new main Base document window is opened for each database opened, just as are the other document types in OOo.

Main Base Document Window

This main Base document window is composed of three main screen areas:

The Icon Choice Controls. This is found on the left side of the window and has the title 'Database'.

The task section. This is the top right section of the window and is titled 'Tasks'.

The lower right section of the window is the detail section. The title in this section changes as you select different items in the Database section. Namely, Tables, Queries, Forms or Reports.

The main toolbar for the Base window contains the normal default items: File, Edit, Insert, View, Tools, Window and Help.

The menu items File, Window and Help are not changed from their default actions.

The remaining menus are set to:

Edit - The menu item 'Database' is added to the end of the menu. The meny has three sub_menu items: Properties Connection Type Advanced Settings

These menu selections are disabled with the embedded database format. For more information on these menu items see the section on Customizing Your Database.

Database objects

Tables

What is a table?

A table has a name, which must be unique within the Base database. ( This uniqueness includes names for Tables, Views and Queries since version 2.2.1 ??? [ I might be wrong on the version number ] )

It contains a collection of records, where each record consists of a specific sequence of fields.

A field can hold a value for a single piece of information, or no information at all. The latter is referred to as a NULL ( or EMPTY ) value.

A field has a name. It also consists of a collection of rules regarding exactly what information may be contained in this field. Every field has one such rule, the data type.

Additional rules can be added to a field and when this is done each is referred to as a constraint.

Base only allows constraints to be added to fields by using SQL Standard Data Definition Language (DDL) commands in the SQL Window, or within scripts. For more information see the section on Advanced Database Reading.

How to create a table.

There are three ways to create a Table within Base.

Using the New Table Wizard. For more information see the section on Table Wizard.

Using the Table Design window. For more information see the section on Table Designer.

Using SQL Standard DDL commands in the SQL Window, or within scripts. For more information see the section on Advanced Database Reading.

Creating Admin Forms - 1 Click Away

If you use the New Table Wizard to create your tables then Base can automatically start the New Form Wizard for you.

[The option is found on page ? of the wizard]

[The ExerciseLog example database uses this feature. ]

A form with a grid control is easier to work with then is the data view window from a Table. See the section on Table Grid Form Controls.

Views

Base supports the use of and creation of views.

Base does not have support for editing a view after it has been defined.

Views are SQL select statements stored by the database engine, in the database.

Views are most commonly used for reporting purposes.

One advantage of a view over a simple query is that many database engines will optimize indexes for use by the View.

Another advantage in large database schema is that the engine will not allow the database to be changed in such a way as to invalidate the actual SQL statement used to produce the View data. ( Actually, in some this is allowed, but the user is warned that this is a side effect of whatever change they are making, when they make it. )

Most Base users will benefit more by using the Query-in-Query feature of Base instead of a View.

For more information on Views see the section on Table Designer.

[ NOTE - the first example database on OOoForum ( employees.odb ) uses a View and it was a mistake to have done so. The majority of problems people have with that file involve the View - some because of issues within Base and others within the HSQL code. Most, but not all have been resolved. ]

[ ExerciseLog does not make use of a View ] [ Contacts.odb uses a view to generate a report ]

Queries

Base Query vs SQL Select

A Base query definition contains an SQL Select statement and more.

Can a Base query also use SQL Insert / Update / Delete statements?

No. Performing the same actions is covered in the section Adding and Removing data.

Using a Query in A Query

New with 2.1

Forms

Wizards every where

Reports Wizard / Designer Based

Wizard for quick lists
I don't have the designer?

New with version 2.3

keep track of it all

Using folders to keep things together.

Here again a lot of this most likely is covered pretty much as needed in the help files. I don't know, exactly, where in the source files are the help files.

How to find your information ( Searches and Filters )

         o What are the differences between a search and a filter
         o Searching multiple fields ( And vs Or and stuff )
         o Don't forget sorting

Adding and Removing data

Once a table has been defined data may be added to it in any of he following:
The data view window. This is the window opened when a table name is double clicked, or the context menu item Open is used.
The data grid area of a query designer window. 
Controls on a Base form.
Data controls in any other type of OpenOffice.org document window.
By using SQL standard INSERT / UPDATE / DELETE command statements from within the SQL window.
By using SQL standard INSERT / UPDATE / DELETE command statements from within a script.

Working in a table data view window

Using Queries for insert / Update / Delete

Working in a form window

Mixing Base with the rest of OO.o

         o Overview of data exchange within OO.o
         o F4 - Has always been there, and is still there - Use it
             + Add a table to Calc
             + Add a table to Writer
             + F4 Even works in Base forms???
                 # Update a lookup table on the fly

The examples used in the book

         o A flat database ( Exercise Log? ) 
         o A relational model ( Contacts??  )
         o Advanced features (  [Invoices or  Inventory or ??? ] )

This would be just a thumbnail of each database and what from each is shown where. I would strongly recommend that each database should be based on supplied table structures. With a modicum of changes or additions. With the possible exception of the advanced entry. There are a couple of database files on the internet hat would be good candidates for this. There is a good French language database that might be right for here for example. Or the accounting demo built by a company in Australia ( sorry, I am not going to go dig up the urls at this minute..)

I would bet if someone asked they any of these authors would allow their OS offering to have certain pieces dissected for the manual. There is also a POS database for running for a small specialty gift retailer that is fully OS, it is less polished or advanced then the others, but with an update for the new report designer would work quite well.

Also the fact that it is not so advanced might be a benefit, it is advanced enough to demonstrate I think most of what needs to be covered - but not so much so that the reader needs to be a seasoned database developer to follow the structures and methods.

Wizards ( Let OO.o do most of the work. )

Open database wizard

         o Why have a wizard for Open?
         o Where is my data anyway?
         o The default database type.
         o Connections to external databases
               + A connection is not the same as an import!
         o Your address book ( Not really a marriage made in Heaven. )

New Table Wzard

Data Types 101 ( Default or HSQL datatypes )=

Numerics
	INTEGER | INT	Java type int | java.lang.Integer
	DOUBLE [PRECISION] | FLOAT	Java type double | java.lang.Double
	DECIMALNo limit	Java type java.math.BigDecimal
	NUMERICNo limit	Java type java.math.BigDecimal
	TINYINT	Java type byte | java.lang.Byte
	SMALLINT	Java type short | java.lang.Short
	BIGINT	Java type long | java.lang.Long
	REAL	Java type double | java.lang.Double[1]


Char / Varchar / Memo
	VARCHAR as Integer. MAXVALUE	Java type java.lang.String
	VARCHAR_IGNORECASE as Integer.MAXVALUE	Java type java.lang.String
	CHAR | CHARACTER as Integer.MAXVALUE	Java type java.lang.String
	LONGVARCHARas Integer.MAXVALUE	Java type java.lang.String
Dates / Time
	DATEas Java type	Java type java.sql.Date
	TIMEas Java type	Java type java.sql.Time

TIMESTAMP | DATETIME Java type java.sql.Timestamp

Binary ( Graphics / Documents )
	BINARY as Integer.MAXVALUE	Java type byte[]
	VARBINARY as Integer.MAXVALUE 	Java type byte[]
	LONGVARBINARYas Integer.MAXVALUE	Java type byte[] 
Boolean
	BOOLEAN | BIT	Java type boolean | java.lang.Boolean
Object
	OTHER | OBJECTas Integer.MAXVALUE	Java type  java.lang.Object

Key please!

Working with the table templates

Standard_Distributed_Schema

Create Exercise Log table

Query Wizard

         o Getting only a few of many ( Simple selects statements )
         o Create Exercise Log queries
       NOTE - this may be the weakest wizard and I would make this the shortest section - the focus on queries is the designer

Form Wizard

         o Tables and Queries and SQL - Oh my! ( Why the are the same, and why they aren't )
         o Which layout template?
               + Build Exercise Log Main Form
         o Seating for two please. ( Adding a sub-form )
               + Relations prompt suggestions
               + Build Contacts Main Form

Report Wizard

When you need a simple list it's hard to beat, if you can make a query!

Tables and Queries and SQL - Oh My! en duex!

         o It's all in the query.

Chose a layout

Can I make this little change?

 ( Sure but know the lay of the land )
  + Using Styles is THE safest way.
  + Create Exercise Log report
      # The report takes one input from the user, the name of a month. It then prints the report for that month of the current year.

Exchanging Data

Working with Calc

   o Using linked data ranges

There must be some information in Calc Guide about working with Base - need to see how this reference can augment that.

Working with Writer

   o MailMerge

There must be some information in Writer about working with Base - need to see how this reference can augment that.

Working with Text files

   o Multiple Choices 
       + The TEXT connection type
       + A TEXT table in an embedded database

There are plenty of sources for this section. The HSQL documentation, a fine how to written in French, a few of my postings on the forum are fairly good.


Customizing your database

HSQL database properties

Designers ( When you need to do it your way.)

Table Designer

Data Types
   o More engines equals more data types ( MySQL, PostgreSQL, MS Access and a host of others
GUI default values
Indexes
Create View

Relation Designer

       + Set up Contacts database

Query Designer

Design View / SQl View / SQL Direct ( Which one? )
Working with Queries in Queries
Using Replacement Parameters
Using Aliases
Adding data form multiple tables together ( Joins )
Why Can't I update the data?
Creating Calculated fields
Using aggregate functions
Average
Sum
Maximum
Minimum
Count
Group
Default built-in functions
Numerical
			Designer	Parser	Direct
	ABS(d)		YES	YES	YES
	ACOS(d)		YES	YES	YES
	ASIN(d)		YES	YES	YES
	ATAN(d)		YES	YES	YES
	ATAN2(a,b)	YES	YES	YES
 	BITAND(a,b)	YES	YES	YES
	BITOR(a,b)	YES	YES	YES
	CEILING(d)	YES	YES	YES
	COS(d)		YES	YES	YES
	COT(d)		YES	YES	YES
	DEGREES(d)	YES	YES	YES
	EXP(d)		YES	YES	YES
	FLOOR(d)	YES	YES	YES
	LOG(d)		YES	YES	YES
	LOG10(d)	YES	YES	YES
	MOD(a,b)	YES	YES	YES
	PI()		YES	YES	YES
	POWER(a,b)	YES	YES	YES
	RADIANS(d)	YES	YES	YES
	RAND()		YES	YES	YES
	ROUND(a,b)	YES	YES	YES
	ROUNDMAGIC(d)	YES	YES	YES
	SIGN(d)		YES	YES	YES
	SIN(d)		YES	YES	YES
	SQRT(d)		YES	YES	YES
	TAN(A)		YES	YES	YES
	TRUNCATE(a,b)	YES	YES	YES
String
			Designer	Parser	Direct
	ASCII(s)	YES	YES	YES
	BIT_LENGTH(str)	YES	YES	YES
	CHAR(c)		YES	YES	YES
	CHAR_LENGTH(str)YES	YES	YES
	CONCAT(str1,str2)YES	YES	YES
	DIFFERENCE(s1,s2)YES	YES	YES
	HEXTORAW(s1)	YES	YES	YES
	INSERT(s,start,len,s2)	YES	YES	YES
	LCASE(s)	YES	YES	YES
	LEFT(s,count)	YES	YES	YES
	LENGTH(s)	YES	YES	YES
	LOCATE(search,s,[start])YES	YES	YES
	LTRIM(s)	YES	YES	YES
	OCTET_LENGTH(str)YES	YES	YES
	RAWTOHEX(s1)	YES	YES	YES
	REPEAT(s,count)	YES	YES	YES
	REPLACE(s,replace,s2)	YES	YES	YES
	RIGHT(s,count)	YES	YES	YES
	RTRIM(s)	YES	YES	YES
	SOUNDEX(s)	YES	YES	YES
	SPACE(count)	YES	YES	YES
	SUBSTR(s,start[,len])	YES	YES	YES
	SUBSTRING(s,start[,len])YES	YES	YES
	UCASE(s)	YES	YES	YES
	LOWER(s)	YES	YES	YES
	UPPER(s)	YES	YES	YES
Date/Time
			Designer	Parser	Direct
	CURDATE()	YES	YES	YES
	CURTIME()	YES	YES	YES
	DATEDIFF(string, datetime1, datetime2)		YES	YES	YES
	DAYNAME(date)	YES	YES	YES
	DAYOFMONTH(date)YES	YES	YES
	DAYOFWEEK(date)	YES	YES	YES
	DAYOFYEAR(date)	YES	YES	YES
	HOUR(time)	YES	YES	YES
	MINUTE(time)	YES	YES	YES
	MONTH(date)	YES	YES	YES
	MONTHNAME(date)	YES	YES	YES
	NOW()		YES	YES	YES
	QUARTER(date)	YES	YES	YES
	SECOND(time)	YES	YES	YES
	WEEK(date)	YES	YES	YES
	YEAR(date)	YES	YES	YES
	CURRENT_DATE	YES	YES	YES
	CURRENT_TIME	YES	YES	YES
	CURRENT_TIMESTAMP	YES	YES	YES
System/Connection
			Designer	Parser	Direct
	DATABASE()	YES	YES	YES
	USER()		NO	NO	YES
	CURRENT_USER	YES	YES	YES
	IDENTITY()	YES	YES	YES
Stored Procedures
			Designer	Parser	Direct
	IFNULL(exp,value)	YES	YES	YES
	CASEWHEN(exp,v1,v2)	YES	YES	YES
	CONVERT(term,type)	NO	NO	YES
	CAST(term AS type)	YES	YES	YES
	COALESCE(expr1,expr2,expr3,...)		YES	YES	YES
	NULLIF(v1,v2)		YES	YES	YES
	CASE v1 WHEN...		NO	NO	YES
	CASE WHEN...		NO	NO	YES
	EXTRACT		NO	NO	YES
	POSITION (... IN ..)	YES	YES	YES
	SUBSTRING(... FROM ... FOR ...)	YES	YES	YES
	TRIM( LEDING ... FROM ...)	YES	YES	YES
         o Advanced SQL
             + Unions / Intersections / Exclusions
             + Case When
             + Calling Stored Procedures
                 #  As column
                 #  In Criteria

Form Designer

Tool bars
   + Form Design ( Overview )
   + Form Controls ( Overview )
   + More Controls ( Overview )
Form Navigator
   + Dataforms 
        # Content Types
        # Table
        # Query
        # SQL
   + Control Lists
        # Moving controls around
Add Field Window
Form Designer Wizards
   + New Table Grid Control
   + New Combo Box
   + New List Box

Report Designer

Using Macros

Validating data input

Generating calculated data

Working with multiple forms

Using other OO.o documents

Using Base at work

Moving from an embedded single user Base file to a Multi-User HSQL server

   o Extract the HSQL database defintion
   o Setup the HSQL server
   o Create the new Base file
Calling User Defined Functions

HSQl is the default, Base supports it in all three deployment models: In Memory ( default ), File, Server. Moving between the three is very easy and the engine is immediately available on every computer OS OO.o runs on.

Using Other Servers

MySQL

Data Type Mapping
Working with stored procedures
Calling User Defined Functions

PostgreSQL

Data Type Mapping
Working with stored procedures
Calling User Defined Functions

MS Access

MS Jet ( MDB )
ACCDB ( MS 2000,2007 )
Data Type Mapping
Working with stored procedures
Calling User Defined Functions

Understanding Connections

Auto Commit

Handling multiple table transactions

Restricting Access to Forms and Reports

Appendix I Build the example database

   * Flat ( ? )
   * Relational 1 ( ?? )
   * Relational 2 ( ???

This should be a page or 2 of the steps needed to create any of the example files needed. This would not necessarily be the same as creating a duplicate of the files that would be available for download of the example databases.

These pages would be here for the person that might get a printed copy of the document and not have access to the odb files. For this guy then he has to type it in and limit this to only the items needed for what is described.

Appendix II Overview of A Database

Appendix III Further reading on database design

Personal tools