KOALA 0.9.0 released: A database `microsoft access'-4GL-like backend for PostGreSQL
Lee Hughes
lhughes at lakenet.com
Tue Dec 28 10:13:25 EST 1999
Is there an HTML-based interface to this system?
Paul Sheer <psheer at obsidian.co.za> wrote in message
news:E122Hlq-00031d-00 at cericon.obsidian.co.za...
>
> 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