KOALA 0.9.0 released: A database `microsoft access'-4GL-like backen

Paul Sheer Paul Sheer <psheer@obsidian.co.za>
Wed, 12 Jan 00 06:54:18 GMT


KOALA - Version 0.9.0



What is this package:

This is an object-database / GUI / database-backend /
data-widget / Microsoft-Access thingie for postgres.

It allows you to create any type of incredibly complicated
interlinked set of database tables without typing a single line of
code. It supports every kind of postgres type corresponding
to every type of gnome/gtk widget.

You all do it from within a GUI. And the GUI form layouts are also
stored within the database in their own table.

Koala gives a form and dialog backend to any postgres

Koala is written in Python with gnome-python (thanks James) for the

Koala is distributed under the GNU General Public License.

Paul Sheer <psheer@obsidian.co.za>


Koala's user reference:
Theory and practical for creating GUI tables.

This documentation is by no means a comprehensive description of
what is going on. It is to be read in combination with running
the demos. Between the two, is all of what you may need to know
to create tables and dialogs from within the GUI.

`Koalii' is the plural of `koala'.

A dialog is a form with lots of widgets inside it that usually need
filling in.

What is a koala: A koala is a widget in a dialog that corresponds
to a column in a database table. It is hence a DATA WIDGET
(term used in some circles). The dialog itself corresponds to the
whole table. A particular instance of the dialog corresponds to a
particular row of the table. A koala is also an python object that
knows how to create that widget as well as format that data for
the particular column in SQL.

Koalii usually hold strings, integers, dates etc - like a persons
name or his shoe size. A `compound' koala is a koala that holds
only an integer where the integer is a reference to a row id of
another table. For instance, a dialog of personal details may
contain a string koala that holds the persons preferred brand of
cologne. It would be better to rather have a table of all brands of
cologne and then just store the particular row id of the brand
instead of storing a string of the full name of the brand (This
allows all brands to be accounted for and makes it impossible to
input a non-existing, but similar sounding brand name into the
widget). The table of all known colognes would have its own
koalii: such as a string containing the name, another for
manufacturer etc. These strings are called sub-koalii of the
integer koala for this cologne. In postgres (the SQL server), row
id's are actually called oid's (Object ID's) and are unique across
the entire database.

So we may have an oid referring to a person. The person has a
row containing a lot of strings and integers describing them.
One these integers is an oid, and is a reference to a row in
another table of cologne brands. The cologne brand row will
contain strings, integers etc describing it, and possibly some
oid's linked to other tables of manufacturers, ingredients... and
so on. In fact, the manufacturer may contain a list of employees
which link all the way back to the persons table: in this way, the
database can circular.

A compound koala can hence be displayed in two ways: as a form
with all its sub-koala represented as widgets (checkboxes,
entry widgets etc.); or, when it itself is a sub-koala, as
a widget inside the form of its parent-koala.

The form has to be laid out neatly so that many sub-koala can be
represented. This is actually quite tricky when you are dealing
with a table of fifty fields. To do this neatly we group
associated koala, displaying each group on a separate page of a
gtk notebook. So for example, a `person' koala would have one
page for his identity (first name, last name, title), and
another page for his address (street, city, country, zip) and so

In the code, these are called `pages', and there might be only one
of them if there are only three or four fields.

The form of each page is described by a formatting language, so
that a form of a checkbox with an entry widget above it may look
like this:
	( %Cf / %Exf )f

The details of these forms are as per the gtk_dialog_cauldron.3
man page, or the gnome sgml documentation of the
gtk_dialog_cauldron() C function. There is one change however:
You may mostly use %X instead of an actual format specifier,
because the code will test the type of the koala being passed to
that format specifier and then substitute the %X for an
appropriate widget type. An exception is the checkbox (%C) and
radiobutton (%R) widgets. There is no way to tell them apart
because the are both `boolean' koalii. Instead of %X you should
explicitly specify %R or %C as the case may be.

The complete list of user widgets is as follows:


    This is the most basic type. It is a Gnome entry widget (%Eg) in
    the form and a `text' postgres type.


    A number entry widget, and an `int' postgres type. This has
    a calculator button to bring up a calculator even if it is
    totally inapplicable to the purpose of the data. The
    calculator also doesn't work, because it fails to add a grab
    or something - Gnome bug.


    Same but for a `float' postgres type.


    Spin button widget and `float' type.


    Spin button widget and `int' type.


    Editor widget and `text' type.


    Same but with scrollbars.


    Checkbox or radio button as explained, and a `bool' type.


    Gnome date edit widget and a `date' type.


    Gnome date edit widget with 24 hours and a `date' type.


    Three spin buttons with 0-23, 0-59 and 0-59, time sql type.


    Two spin buttons with 0-23 and 0-59, time sql type.


    Gtk entry widget with format of 999999.99 and `float' type.
    Note: One of the libpg (postgres library) or _pg (python
    postgres module) libraries has a bug that causes a segfault
    when using the `money' sql type. Hence the money koala now
    uses the float type in tables.


    If you read the gtk_dialog_cauldron.3 man page, you will see
    that some tokens in the format string require an argument
    merely for display purposes (such as a title to a frame).
    You can use the null koala for this, which does not
    represent a column in the table.


    An entry widget. This is NOT a password entry of the
    tradition type where asterisks replace characters. It is for
    storing passwords so that they can only be read by trusted
    users. The widget has a check button to the right that, when
    depressed, decrypts the text and then encrypts it again
    when raised. The encryption is done with triple-DES. This
    koala might be used, for instance, in a service IT business
    that has a database of customers machines that they
    administer. Each machine has a root password which you
    can safely store using this koala, only the encrypted text is
    transfered to the database. The key used to do the
    encryption is a hash of a password entered with the
    do_passwd() dialog. A few characters of the intermediate
    hash can be passed as the second argument to verify that
    the typed password is correct.


    This is the compound entity explained above. The widget is a
    Gnome entry widget that contains text that refers to the
    first sub-koala on the first page (i.e. the first column in
    the table). For instance a cologne koala would have the
    brand name as the first sub-koala, so the brand name would
    appear in an entry widget in the parent dialog. Next to the
    entry widget are two buttons, [?] and [^]. The first brings
    up a list box of possible values that may appear in the
    entry widget - it does a search through the table. The
    second displays the full dialog of the koala - the notebook
    with all its pages.

    Two extra pages appear in the notebook besides the ones
    containing sub-koala widgets. The are the `Edit' and
    `Access' pages. The Access page allows the user `postgres'
    to restrict or allow access to the particular koala in
    various ways.


    This is just like the mutable koala, but the dialog is
    automatically created from a pre-existing table. This can be
    used in intermediate stages where you quickly want to create
    some kind of access to a table, where it doesn't have to
    look pretty. Type is `oid' like mutable.


    This widget is gtk clist - i.e. a table with column headings
    for each field. The table has `Add', `New', `Edit' and
    `Delete' buttons. The `Edit' button brings up a dialog just
    like the mutable koala, from where pages can be added and
    edited. New entities that are created within the form editor
    will appear as new columns in the table. The sql type is
    `text'. This is the one koala that uses more than one row.
    The table itself has an extra column `_key'. All `_key' with
    the same value belong together.


    Above we said that compound koala's have an oid value that
    indexed a row. Mutablestring is a mutable koalii that has a
    string as the index instead of an oid of the row. The string
    represents the first koala on the first page - which must be
    of sql type `text' - and would be the first column of the
    table. Note that there is not yet protection of duplicate
    entries. The user should make sure that two rows do not have
    the same index.


    This is the same as mutablestring, but with an integer as an

Note on editing pages:

When you edit a page from within the GUI, it will not appear
immediately in the notebook. You have to first test the page by
clicking the `Test' button and then `Cancel' the dialog and then
`Cancel' the parent dialog. Only then are the previous form
layouts cleared from memory.

The method append_mutable_page appends koala that do not appear
in the page table. If koala already exist, no action is taken,
even if the column types or form layout differs. This means that
once you have created a page with append_mutable_page you can
only alter it from within the GUI. Alternatively you can do a

    DELETE FROM table_pagearray WHERE _key = 'my_table';

Which will clear out the form and reference to any koalii. Stale
(unlinked) koalii from this page will remain in
table_entityarray, but these may be ignored.

Note on date style:

Postgress outputs dates as 4/28/1984 or 28/4/1984 depending on
the locale. I force my date to european style (28/4/1984) by

    postmaster -o -e ...

in my init script (see postmaster(1) and postgres(1)). If you
use non-european style, set,

    european_date_style = 0

in object_bases.py. This has nothing to do with the way that the
date will display in the widget, which is has to do with Gnome
and not with me.

Be absolutely sure about what date style postgres is outputting,
by actually creating a table with dates in it and doing a SELECT
on them. Koala cannot autodetect this and will read the date

Note on quotation marks:

At the moment, the python code replaces all ' with ` before
committing any string value to a table.

How to jump in and create a database:

There are two ways: one is to just run extended_bases.py as the main
program (i.e. 

              su postgres
              python extended_bases.py

) and then begin creating tables with the `New' button. Follow this
example as a tutorial. Here we create a table of motor vehicles.

    0. Postgres should be set up so that a user `postgres' can access
	a database called postgres and create and delete tables.
	See the postgres documentation about installing and
	configuring postgres.

    1. run
	su postgres
	python extended_bases.py

    2. Click on `New'

    3. Enter `table_vehicles'

    4. A notepad will appear with two pages: `Edit' and `Access'
	Raise the `Edit' page and click on `New'.

    5. Enter `Identity' on the Title page, `100' in the `Order'
	field on the `Form' page. Now enter your form in the text
	    ( (Registration Number:) %Xxf )f /
	    ( (Make:) %Xxf )f /
	    ( (Model:) %Xxf )f /
	    ( (Year:) %Xxf )f
	It would be wise to read the full gtk_dialog_cauldron(3)
	man page. Note that newlines are completely ignored.

    6. Now raise the `Koalii' page and read the message their.

    7. Click on `Ok'

    8. Click on `Edit' and go back to the `Koalii' page.

    9. Click on `New' in the `Koalii' page.

    10. A Koalii dialog will appear, enter:
	`Registration Number' in the `Title' field,
	`10' in the `Order' field,
	`string' in the `Type' field,
	`ident_registration' in the `Table column' field,
	and leave the rest blank.

    11. Now repeat steps 9 and 10 for each of make, model and
	year. In each case, the `Table column' MUST be different
	and the `Title' field must be something descriptive.
	The `Order' field should indicate the order that the
	koalii appear in the form, preferably with large gaps
	in case you need to add new ones later, eg 20 for make,
	30 for model, and 40 for year.

    12. In the `Koalii' page of the `Pages' dialog, check
	A: that you have four entries
	B: that each entry has a different Column field
	C: that each entry has a properly descriptive Title field
	D: that each entry has a type of `string'
	E: that the order of the entries matches the order within
	    the form

    13. Hit `Ok' to go back to the `Edit' page of the `table_car'

    14. You will notice that under `Enabled' is an `f'. Click on
	`Test' to test the page and then `Close' the test dialog.
	If there was something wrong with the form string, an
	appropriate error message may be printed (see
	README.patch_first). You may also get error
	messages that are very cryptic. Don't scratch your
	head trying to understand them, just double check all
	your koalii and your form string and try to make the
	page simpler.

    15. `Cancel' the `table_car' dialog.

    16. You will now notice a new button on the main menu:
	`table_car'. click on this button.

    17. You can continue to add other pages to the notebook.
	Though do not ever delete pages and then reuse column
	names. Postgres lacks the `DROP COLUMN' feature (its
	also dangerous to use in this case) so stale columns
	will hang around. You can drop them by recreating
	the table, but this must be done with psql on the
	command line.

Other way to create a table:

The other way to create a table is to program it in python.
Here is a python script that does the same:


from extended_bases import *

class koala_car (koala_mutable):
    def widget_create (self, *args):
	self.append_mutable_page ("Identity",
		"( (Registration Number:) %Xxf )f /"
		"( (Make:) %Xxf )f /"
		"( (Model:) %Xxf )f /"
		"( (Year:) %Xxf )f",
		(("string", "ident_registration", "Registration Number", ""),
		 ("string", "ident_make", "Make", ""),
		 ("string", "ident_model", "Model", ""),
		 ("string", "ident_year", "Year", "")))
	return koala_mutable.widget_create (self)

def do_table (heading, table):
    a = koala_mutable ("", heading, "", db, table)
    a.widget_create ()
    s = search_dialog ("Search: " + heading)
    if s:
	a.list (heading, s[0])

db = initialise_bases ("postgres", "localhost", 5432)
init_mutable_table (koala_car, db, "table_car")
do_table ("Cars", "table_car")


Above, the last arguments were always left blank (i.e. "").
These are the default values for the field. In the above cases
there is no entry that is more likely than any other, so we
left them blank.

Now that the form has been created, goto the `Access' page and add
some groups so that others besides the postgres login can use
the table. You must first create users and groups as explained in
README.groups. You need only create one group.

Then edit the `Identity' page again and goto `Access'. Here again
you must add a group to the list.

That concludes a trivial database. To create a complex database
with links requires very little more effort...

Here we are going to add an owner to the car. First we need to
create a `table_person' table. (There may already be one as
an example).

Add a new page to your car notepad like this:
    Title:	Owner
    Form:	( (Owner:) %Xxf )f / ( (Date of purchase:) %Xxf )

Title		Order	Type		Column		Table
---------------	-------	---------------	---------------	------------
Owner		10	mutable		owner_owner	table_person
Date Purchased	20	date		owner_date

The entry widget next to `Owner' will have two buttons to select
from a list of people and to add a new person to the list,
respectively. You should beware of adding duplicate entries
because these are perfectly allowable with a mutable koala, but
may create confusion in the database. If there would never be a
duplicate entry for the table you have created, then you can use
mutablestring instead of mutable which indexes the table by the
first column and does not allow duplicate entries. For
table_person, the first column contains only the last name of
the person. Since there can be many people with the same last
name, mutable is used instead of mutablestring.

Note that the mutable koala does not automatically insert a
default value for a new instance of the dialog. Mutablestring
does insert such a value if defined.


Version: 2.6.3i


Cellular . . . +27 83 604 0615 . . . .  Land . . . +27 21 761 7224
Obsidian Systems . . . .  Linux installations, support, networking
info@obsidian.co.za . . . . . . . . . .  Tel  . .  +27 21 448 9265
http://www.obsidian.co.za  . . . . . . . Jhb . . . +27 11 792 6500
L I N U X . . . . . . . . . . . . . The Choice of a GNU Generation

<P><A HREF="ftp://ftp.obsidian.co.za/pub/koala/">KOALA 0.9.0</A> - 
a database/GUI forms package for PostgreSQL and
Gnome (uses Python 1.5.1).  (26-Dec-99)

----------- comp.lang.python.announce (moderated) ----------
Article Submission Address:  python-announce@python.org
Python Language Home Page:   http://www.python.org/
Python Quick Help Index:     http://www.python.org/Help.html