KOALA 0.9.0 released: A database `microsoft access'-4GL-like backen d for PostGreSQL
Paul Sheer
psheer at obsidian.co.za
Sun Dec 26 12:47:42 EST 1999
SOFTWARE RELEASE ANNOUNCEMENT
=============================
KOALA - Version 0.9.0
---------------------
Availability:
-------------
ftp://ftp.obsidian.co.za/pub/koala/
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
database.
Koala is written in Python with gnome-python (thanks James) for the
interface.
Koala is distributed under the GNU General Public License.
----------------------------------
Paul Sheer <psheer at obsidian.co.za>
README.koala>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
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
on.
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:
koala_string
This is the most basic type. It is a Gnome entry widget (%Eg) in
the form and a `text' postgres type.
koala_int
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.
koala_float
Same but for a `float' postgres type.
koala_floatrange
Spin button widget and `float' type.
koala_intrange
Spin button widget and `int' type.
koala_text
Editor widget and `text' type.
koala_largetext
Same but with scrollbars.
koala_bool
Checkbox or radio button as explained, and a `bool' type.
koala_date
Gnome date edit widget and a `date' type.
koala_datetime
Gnome date edit widget with 24 hours and a `date' type.
koala_time
Three spin buttons with 0-23, 0-59 and 0-59, time sql type.
koala_minutes
Two spin buttons with 0-23 and 0-59, time sql type.
koala_money
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.
koala_null
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.
koala_password
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.
koala_mutable
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.
koala_auto
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.
koala_mutablearray
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.
koala_mutablestring
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.
koala_mutableint
This is the same as mutablestring, but with an integer as an
index.
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
using,
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
incorrectly.
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
area:
( (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'
dialog.
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:
#!/usr/bin/python
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 )
Entities:
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.
----------
--
-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: 2.6.3i
mQCNAzdiRpAAAAEEANPUPC/Lrs4OCJOjWaIWaCYTzTIY1p73uPY+8ZOJH5fc4QNp
IAX+EFQ/yZ3RMOLg8yy++HufzBwDoePO4W0MKwyVFCcNIIjsY6JCXWdbpQXsY1LL
OASlGexQnEQ4mfc7ThOAKWSgPyiMv5vDJ6S0EL8rdIFL7fVv56BAxnN042FRAAUR
tCJQYXVsIFNoZWVyIDxwc2hlZXJAb2JzaWRpYW4uY28uemE+iQCVAwUQN2JGkKBA
xnN042FRAQF5CAP/Y0TaguqCpYiTEBHxZPpT/dliInVRBzryi4gdlgX6CCLDRRGH
ATP4ac8aiATegc4ev4+vcdn4fBwc6fQ2AP6hd25ZI93vShxztM/bQlGWy0zp79Uo
+69uGdJhdvgYpIsTCqppM/yjeXAJEqq5TG2Gy4pqHY235rspmeA/fX7kgIo=
=aX4m
-----END PGP PUBLIC KEY BLOCK-----
Cellular . . . +27 83 604 0615 . . . . Land . . . +27 21 761 7224
Obsidian Systems . . . . Linux installations, support, networking
info at 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
More information about the Python-list
mailing list