|
|
Line 1: |
Line 1: |
− | (Please scroll down to see additions from Drew and comments by Jean.)
| + | #REDIRECT [[User:Foral/JA/Documentation/2.3]] |
− | | |
− | Andrew Pitonyak wrote: 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.
| |
− | | |
− | =Drew Jensen's comments=
| |
− | 06/30/07 Here is my first thought on an outline
| |
− | | |
− | ==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.
| |
− | | |
− | ======Main Menu======
| |
− | | |
− | The main menu 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 have the following default changes made to them:
| |
− | | |
− | ======Edit======
| |
− | The menu item 'Database' is added to the end of the menu. The menu has three sub_menu items:
| |
− | | |
− | Properties
| |
− | | |
− | Connection Type
| |
− | | |
− | Advanced Settings
| |
− | | |
− | NOTE - These menu selections are disabled with the embedded
| |
− | database format. For more information on these menu items
| |
− | see the section on '''''Customizing Your Database'''''.
| |
− | | |
− | ======View======
| |
− | The menu item 'Database Objects' is added to the top with the fullowing selections:
| |
− | | |
− | Tables
| |
− | | |
− | Queries
| |
− | | |
− | Forms
| |
− | | |
− | Reports
| |
− | | |
− | Selecting one of these items is the same as selecting the same item in the 'Database' section on the left of the window.
| |
− | | |
− | ======Insert======
| |
− | The menu has the following selections:
| |
− | | |
− | Form
| |
− | This creates a blank Form.
| |
− | | |
− | Report Wizard
| |
− | This launches the Report Wizard. If a particular table is highlighted at the time the wizard will start with that table preselected.
| |
− | | |
− | Query( Design View )
| |
− | Opens new Query window, in design view mode.
| |
− | The Add Table / Query dialog box is automatically opened.
| |
− | | |
− | Query( SQL View )
| |
− | Open new Query window, in SQL view mode.
| |
− | | |
− | Table Design
| |
− | Opens a blank table design window.
| |
− | | |
− | View ( Design )
| |
− | Opens a new View definition window, in designer view mode.
| |
− | | |
− | View ( Simple )
| |
− | Opens a new View definition window, in SQL view mode.
| |
− | | |
− | Folder
| |
− | When Forms or Reports are selected for display in the detail section this item opens the 'insert as' dialog box.
| |
− | | |
− | ===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 and HSQL datatypes )====
| |
− | | |
− | Base, as with all of OpenOffice.org is designed to conform to standard document definitions put forth by the OASIS OpenDocument Specification. This specification includes rules for database front end applications, and as part of this is a set of default data types.
| |
− | | |
− | bit
| |
− | boolean
| |
− | tinyint
| |
− | smallint
| |
− | integer
| |
− | bigint
| |
− | float
| |
− | real
| |
− | double
| |
− | numeric
| |
− | decimal
| |
− | char
| |
− | varchar
| |
− | longvarchar
| |
− | date
| |
− | time
| |
− | timestmp
| |
− | binary
| |
− | varbinary
| |
− | longvarbinary
| |
− | sqlnull
| |
− | other
| |
− | object
| |
− | distinct
| |
− | struct
| |
− | array
| |
− | blob
| |
− | clob
| |
− | ref
| |
− | | |
− | When you are working with Base of course you are not working with some abstract specification, you are working with real data stored in some database. The Base module must therefore map these default data types to the real data types used by the database egnine.
| |
− | | |
− | Here is the mapping of data types to the HSQLdb database engine, supplied with OpenOffice.org.
| |
− | | |
− | =====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==
| |
− | | |
− | = Jean Weber's comment's on Drew's outline =
| |
− | | |
− | I think Drew's outline is a good start to an outline for our book.
| |
− | | |
− | One problem with a book such as this is: how much to teach about relational databases themselves, versus just explaining how to use the tools provided in OOo Base. Obviously our main requirement is to cover OOo's tools, but I do like the idea of including some instruction on database design as well. Dan Lewis's "Getting Started with Base" chapter is great in that respect.
| |
− | | |
− | A model I like is the GnuCash Tutorial and Concepts Guide, http://svn.gnucash.org/docs/guide/
| |
− | Most of the chapters are divided into three parts: basic concepts, instructions for using the program, and examples of use. It's a good way to combine background and tutorial information with reference info in one book, without jumbling it all up together. I think something like this might work really well for a book on OOo Base. What do others think?
| |
− | | |
− | I'll try to find time to work on another iteration of the outline (EDIT: It is now on the Article tab of this page).
| |
− | | |
− | =Original outline from the Article tab=
| |
− | | |
− | Chapter 1: Learning OObase - building your first application
| |
− | This uses the built-in data source and should be restricted to following the development of the members table only in the data model. Within this authors can get as creative as they like. This will be expanded on in section 3. It is anticipated that the majority of techniques can be covered with this example, apart from joins and relationships.
| |
− | | |
− | Comment by Jean: I'm not sure what is meant above by the "built-in data source" -- does OOoBase have a sample database? Perhaps we could reuse the material in the Getting Started book's chapter on Base, instead of writing a new chapter for this book? It's an excellent introduction, very thorough. Or do people think a different approach is more suitable for this book?
| |
− | | |
− | Tables
| |
− | | |
− | Queries
| |
− | | |
− | Forms
| |
− | | |
− | Reports
| |
− | | |
− | Chapter 2: Accessing other data sources
| |
− | This is where porting from other applications could go.
| |
− | | |
− | Comment by Jean: Is this supposed to include connecting to dbs in other apps, eg MySQL?
| |
− | | |
− | Chapter 3: A multi-table application using OpenOffice base
| |
− | This is where advanced features could go while building a real application.
| |
− | | |
− | Introduction to the application
| |
− | Includes data design etc.
| |
− | | |
− | Setting up the tables
| |
− | | |
− | Queries
| |
− | | |
− | Forms
| |
− | | |
− | Reports
| |
− | | |
− | Chapter 4: Integration with other OpenOffice components
| |
− | | |
− | Calc
| |
− | | |
− | Writer
| |
− | | |
− | etc
| |
− | | |
− | Chapter 5: Special facilities
| |
− | eg. using address books etc.
| |
− | | |
− | Appendices
| |
− | | |
− | Database theory including databases/data sources etc.
| |
− | To refer to in the main text
| |