[Tutorial] Structured Query Language in Base

Forum rules
No question in this forum please
For any question related to a topic, create a new thread in the relevant section.
Post Reply
User avatar
Posts: 30805
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[Tutorial] Structured Query Language in Base

Post by Villeroy »

This was intended as follow-up in How to add up Table Fields?. Well, I think it is a tutorial although it appears a little bit biased against the way how Base is suggested to be used.
wickerman wrote:Thank you for answering. I will give this a try. I have the handbook "Learning SQL" and need to delve deeper into it.
:D Handbook is a very good idea! SQL is fairly simple to learn, whereas the operations you can do with it may become very complex, certainly more complex than a reasonable GUI will ever be able to master. SQL will help you to understand and operate any relational database on the market.
As for the Base-specific part, allow me to hijack this thread for my monthly rant on Base. Most of it will be on topic, dealing with SQL in the tool set of OOo's database component and why it is an excellent idea to rely upon something you can learn and actually use. In the following I assume that you are familiar with SQL and need to know the implementation details and how to apply your SQL knowledge.
If the status bar of your database window shows something with "HSQL" then you presumably created a new Base-database from scratch. Keep in mind that you are working with a "OOo-embedded edition" of a database which is fully documented as stand-alone "server edition" here: http://hsqldb.org/doc/guide/ch09.html. You could also install that one and connect Base to it like you could connect to many other db-servers. The server version of HSQL would store the raw data outside your Base document. The server could serve data to any database tool able to connect with the server. The entire documentation on how to control a running HSQLDB is written by means of SQL commands since most databases run without graphical point-and-click interface.
However, your native type of Base document embeds the whole database in one file. OOo comes with a special HSQL server, adjusted to serve the databases that are embedded in the Base documents. They are zip archives with .odb-suffix, BTW. No other application can deal with Base documents nor does OOo's integrated HSQL server serve other applications. But the SQL you can use with OOo's integrated HSQL server is the same SQL as documented for the "adult version".

Data definition
In the database window, menu Tools>SQL... pops up the "command line" for data definition commands to manipulate the structure of tables, fields, relations and indices (commands starting with ALTER, CREATE, DROP, SET). Usually those commands are used to create the database skeleton before any data are stored in it. It can be quite challenging to modify the definition once there are data in it.
A table's design view and the relation manager (Tools>Relations...) provide a graphical user interface for data definition. Those graphical tools are neither reliable nor feature-complete. Knowing some SQL you can easily use the "command line" which works as reliably as the underlying database engine does. Knowing it's your fault if something goes wrong gives you a better feeling than stumbling upon a faulty implementation you can not repair by your own. Things get even worse when you try to construct the skeleton of a foreign database engine by means of the graphical tools in Base. Better use any of the recommended development tools for your particular database. Once you have the fundamental structure up and running, you can connect a Base document to it, create queries, forms and reports as with any other database front end.

Data manipulation
The same "command line" (Tools>SQL...) can dispatch data manipulation commands to manipulate heaps of data (SQL:INSERT, UPDATE, DELETE). There are no graphical tools for data manipulation other than the grid you see when you open a table or query. Customized input forms may be added by yourself in another development phase.

Data extraction and recombination (SQL SELECT)
I assume "views" in the tables' container being basically the same as read-only queries. The following refers to SELECT-queries stored in the queries container of a Base document.
Base offers 4 ways to create SELECT queries.
1. The wizard composes a simple SELECT query by asking you questions in a step-by step process.
2. The query designer provides a drag&drop interface to create and edit a simple SELECT query.
3. The SQL editor in “parsed mode”, with "direct SQL mode" turned off, lets you type a simple SELECT query.
4. The SQL editor with "direct SQL mode" turned on lets you type any SELECT query supported by the underlying database. [sql-view:menu:Edit>[X]Run SQL directly... or the push the "SQL" button while in SQL view]

Options 1 and 2 create a query that can be displayed as SQL string in 3. You can toggle between designer(2) and "command line"(3) but you can not recall the wizard(1) to edit an existing query.
"Simple SELECT queries" as created in 1, 2 or 3 (also referred to as "parsed queries") are interpreted by Base. They are supposed to be supported within Base regardless of the type of database. However, the available functions and clauses are limited by the underlying data source.

SELECT queries and file based data sources
Base does not provide much database functionality for a linked spreadsheet or csv-table. All the file based database drivers support SELECT queries on single tables with COUNT(*) as the only aggregation (count of all records in table). The functions that are supposed to work with whole arrays of field values are documented in [Specification] SQL Functions for file based database drivers. Except for dBase all of OOo's file based database drivers return read-only record sets.

SELECT queries and real databases
If the source is a "real database", such as HSQL, Base's development status is the limiting factor when using parsed SQL. Base chokes on many SELECT queries which are perfectly valid when dispatched directly to the underlying database. When using direct SQL, the connected database driver is the limiting factor.

Do yourself a favour and use direct SQL mode if possible. With direct SQL mode you can rely upon the features documented for the particular database while circumventing the whole broken Base thing. HSQL is very close to the features and syntax used in any handbook for learning SQL.
Personally, I use the graphical query designer for drafts or when I do not recall all the names of tables and fields. Quite often I have to turn on "direct SQL mode" in order to select from JOINed tables, filter by slightly advanced criteria or simply answer the question if I am wrong or Base's SQL parser is wrong. Too often Base's parsed mode does not accept valid queries.
One thing that requires parsed mode: Parameter queries like SELECT * FROM "Table" WHERE "Name"=:Ask_user_for_name. Asking the user for a complement to the query is the obligation of the database front end Base pops up a dialogue to ask you for the parameter substitution. Additionally you can substitute the parameter by means of forms substituting parameters in sub-forms.
 Edit: 2009-01-03 
Another thing that requires parsed mode: You can not successfully link two forms (form-subform) if one of them is based on a direct SQL query. [Issue] Form-subform relation fails with direct SQL
 Edit: 2009-02-03: Today I learned that even the simpliest queries return read-only result sets in direct SQL. 

Editing SQL
There are many "advanced text editors" made for coders. If such a text editor offers syntax highlighting, if it indicates pairs of braces as well as pairs of quotes, then it may be even easier to use than graphical tools. Copy and paste your commands and comments between the editor of your choice and Base's SQL window. This way you can also store drafts or queries with "interesting" results as plain text. At the time of writing someone is writing an advanced SQL editor for Base, which hopefully will be integrated in version 3.1.

SQL in forms and reports
Forms, list boxes and combo boxes are bound to record sets. The record set can be a table, one of your stored queries or an individual "SQL command" stored as a property of the form or box respectively.
The [...]-button right of the SQL command in the screen shot pops up the same query designer as when you create a query to be stored in the queries container. Of course you can type the SQL directly into the property-box as well.
Option "Analyse SQL command" (Yes/No) makes the difference between "parsed mode"(Yes) and "direct SQL mode"(No).
Writer-form opened in edit mode. 2nd button on tool bar "Form Design" toggles between design mode and working mode. While in design mode, the 5th button on tool bar "Form Design" loads the "Form Navigator" showing the Writer document's hierarchy of forms and controls. Dialogue "Form Properties" shows the data-properties of the currently selected "MainForm".
Last edited by Villeroy on Tue Feb 03, 2009 12:56 pm, edited 5 times in total.
Reason: Functionality in parsed mode, spelling and structure.
Post Reply