13. Data Retrieval
For data retrieval MUIbase offers two ways:
the programming feature and the query editor.
The programming feature allows you to install
buttons in table masks which, when pressed, call program functions.
The usage of this feature is described in the structure editor chapter
(see Structure editor) and in the chapter about programming MUIbase
(see Programming MUIbase).
This chapter describes the usage of the query editor,
a requester where you can enter queries and view the output in a scrolling list-view.
13.1 Select-From-Where Queries
MUIbase offers a select-from-where query similar to the one in SQL database systems.
The query allows you to list the record contents from one or more tables.
Only records matching certain criteria are included in the output.
The (incomplete) syntax of an select-from-where query is
| SELECT exprlist FROM tablelist [WHERE test-expr]
[ORDER BY orderlist]
|
where exprlist is a comma separated list of expressions to be printed
(usually the attribute names) or a simple star * matching all
attributes of the specified tables, tablelist is a comma
separated list of tables whose records are examined, test-expr
is the expression that is tested for each set of records to be included
in the output, and orderlist is a comma separated list of
attributes that defines the order for listing the output.
Please note that the WHERE and ORDER BY fields are optional,
denoted by the brackets [].
For example, the query
lists the attribute contents of all records in the given table.
| SELECT attr1 FROM table WHERE (LIKE attr2 "*Madonna*")
|
lists the value of the attr1 field in all records of table
where the contents of field attr2 contain the word `Madonna'.
For more information about the select-from-where query including its full syntax,
see Programming MUIbase, for more example see Query examples.
13.2 Query Editor
For entering and running queries, open the query editor by choosing menu item
`Program - Queries'. The query editor is able to manage several queries,
however only one query is displayed at a time. The query editor window contains
the following items:
- a string input field with an attached pop-up button. The edit-able string field
displays the name of the current query.
By pressing the pop-up button, a list with further query names together with several buttons appear.
You can select one of the listed queries to make it the current one, press the `New'
button to create a new query, press the `Duplicate' button to get a copy of the
activated query, click on the `Sort' button to sort the list of queries,
or press the `Delete' button to delete the selected query.
For leaving the pop-up window without changing anything, click on the pop-up button again.
- a `Run' button that compiles and runs the query program and displays the output in
the output list-view.
- a `Print' button that opens a requester (see Printing queries)
for printing the results.
- an editor field for entering the query program. Here you usually enter a
select-from-where query. However, it is also possible to enter any
expression of MUIbase' programming language. This can be useful if you want
to do simple computations or update some fields of a table by using a simple program.
Please note that MUIbase automatically surrounds your program expression with
a pair of parenthesis, thus you can omit the outermost ones.
- a list-view that displays the output after running the current query.
The output is formatted into rows and columns.
The title row holds the field names of the select-from-where query
(usually the attribute names).
The other rows hold the contents of the query result,
one set of records per row.
Each field is displayed in its own column.
By clicking on a column title you can sort the list with respect to this column.
A second click onto the same column title reverses the order.
On the Amiga you can set a secondary sort column by holding down the
Shift key when clicking on a title.
If you double click on entry in the list
and the entry was generated from a record
then this record is displayed in the corresponding table mask.
This offers an easy way to jump to a certain record in its table mask.
The query editor is a non-modal requester. This means that you can leave the query editor
open and still work with the rest of the application. You can close the query
editor at any time by clicking on the close button in the window title bar.
13.3 Printing Queries
After you have run a query you can print the result to a file or printer
by clicking on the `Print' button in the query editor.
This opens a print requester containing the following items:
- a field `Delimiter' where you specify how the columns should be separated.
`Spaces' pads the fields with space characters. Padding is done
on the left or on the right side depending on the type
of the field (numbers are padded on the left, text on the right side).
`Tabs' inserts exactly one tab character between the columns.
This can be useful if you want to use the print requester for
exporting records (see below).
`Custom' allows to specify a custom delimiter string to be
printed between fields.
- a field `Font' where you specify which font should be used for
printing the output. `NLQ' stands for near letter quality which
should print the output in better quality than `Draft'.
- a field `Size' where you define the character size.
`Pica' prints in a large font (10 CPI), `Elite' in a medium
font (12 CPI) and `Condensed' in a small font (17 CPI).
- a string field `Init sequence' where you can enter a string
for initializing your printer. The contents of this field are written directly
after opening the printer. For example you can use `\33c' as init sequence
which resets your printer.
- a field `Indent' where you can enter a number of spaces that
are used for indenting each output line.
- a field `Headline' that, if checked, prints the field names
in the first line.
- a field `Escape codes'. If not checked the output of all escape codes
is suppressed which means that the settings of the fields `Font'
and `Size' are ignored and the contents of `Init sequence'
are not printed. Suppressing the output of all escape codes is
useful if you want to generate an ASCII file, e.g. for exporting records.
- a field `Quotes' that, if checked, surrounds all
fields with double quotes.
- a field `After printing' where you can specify how the
output should be finished. `Form feed' prints a form feed
character \f. `Line feeds' prints a number of line feed
characters \n. The number of line feeds can be entered
in the string field to the right of the `Line feeds' button.
`Nothing' doesn't write anything to the printer.
- a string field `Output' with an attached pop-up button.
You can use the pop-up button to open a file requester for
choosing a filename or directly enter the filename into the string field.
For writing the output to your printer enter `|lpr' (Linux)
respectively `PRT:' (Amiga).
For other special filenames, see Program output file.
- two buttons `Ok' and `Cancel' for leaving the print requester.
After you are done with all settings, click on the `Ok' button
to start the print job.
The print requester can also be used for exporting records to an ASCII file.
To do this, specify `Tabs' (or `Custom') in the `Delimiter'
field, set the number of spaces in the `Indent' field to 0,
check `Headline', un-check `Escape codes' to suppress the font,
size and init sequence settings, optionally check `Quotes' if you want
the field contents to be surrounded by double quotes, check `Nothing'
in the `After printing' field, and enter the output filename in the
`Output' field.
Using the query editor together with the print requester for exporting records
can be more powerful than when using MUIbase' import/export feature
(see Import and Export) since in the query editor any query can be entered
whereas the export requester only uses a fixed query.
13.4 Query Examples
To give you an impression of the power of the select-from-where queries
this section gives you some sample queries.
Suppose we have two tables `Person' and `Dog'.
`Person' has a string attribute `Name',
an integer attribute `Age', and two reference
attributes `Father' and `Mother'
that refer to the father and mother records in table `Person'.
The table contains the following records:
| Name Age Father Mother
--------------------------------
p1: Steffen 26 p2 p3
p2: Dieter 58 NIL NIL
p3: Marlies 56 NIL NIL
p4: Henning 57 NIL NIL
|
`Dog' has a string attribute `Name', a choice attribute
`Color' and a reference attribute `Owner' that
refers to the owner in the `Person' table.
The table contains the following records:
| Name Color Owner
-----------------------
d1: Boy white p3
d2: Streuner grey NIL
|
Given these data the following sample select-from-where queries can be run:
results to:
| Name Age Father Mother
--------------------------
Steffen 26 Dieter Marlies
Dieter 58
Marlies 56
Henning 57
|
(For the reference attributes the `Name' field of the referenced record is printed.)
| SELECT Name "Child", Age,
Father.Name "Father", Father.Age "Age",
Mother.Name "Mother", Mother.Age "Age"
FROM Person WHERE (AND Father Mother)
|
results to:
| Child Age Father Age Mother Age
----------------------------------
Steffen 26 Dieter 58 Marlies 56
|
| SELECT Name, Color,
(IF Owner Owner.Name "No owner") "Owner"
FROM Dogs
|
results to:
| Name Color Owner
------------------------
Boy white Marlies
Streuner grey No owner
|
| SELECT a.Name, a.Age, b.Name, b.Age FROM Person a, Person b
WHERE (> a.Age b.Age)
|
results to:
| a.Name a.Age b.Name b.Age
---------------------------
Dieter 58 Steffen 26
Marlies 56 Steffen 26
Henning 57 Steffen 26
Dieter 58 Marlies 56
Henning 57 Marlies 56
Dieter 58 Henning 57
|
This document was generated
on May, 27 2010
using texi2html