[Kexi] Help with linked lists

[Jarosław Staniek]

Jeremy Johnson said the following, On 2007-05-09 02:52:

> Does anybody know of a tutorial or example of using linked lists in kexi with 
> postgresql?
> 
> I'm trying to design a database which would be made easier if 
> some fields could be linked lists. A trivial example would be a field for 
> telephones which would be a null-terminated list of phones, e.g.:
> Work->Home->Cell#1->Cell#2->Pager->Email->Voicemail->NULL
> 
> So I'd like a TABLE called Phone_List:
> PK     id     child_id    Phone           TYPE
> 1        1                2     787-1234    (Work)
> 2        2                3      787-4321   (Home)
> 3        3         NULL      787-1111   (Cell)
> 4        4               5       787-2222   (OvalOffice)
> 5        5               6      787-4444    (Cell)
> 6        6         NULL     787-5555    (Pager)
> 
> And I'd also have a table for Person:
> PK     id       Lastname   Firstname   Telephones
> 1        1        Doe              John              1
> 2        2        Bush           George         4
> 
> 
> Of course I don't want to have to edit these Phone_List and Person tables
> by hand. I need to create a form in Kexi which would allow me to to just
> type in the telephone numbers on a CONTACTS form, 
> and kexi/python/ruby/postgresql would take
> care of making sure that every telephone number had a correct child_id or 
> NULL. And I also need a method to remove a phone number (or if I delete a 
> person I need to delete all the telephone numbers for that person).

Why do you need parent-child relation for phone numbers? Maybe you just need 
an ordering?
In any case Kexi does not "know" about such special cases (yet). This may be 
supported after 2.0 on users demand. Unless a special extension (taing care 
about updating hierarchical data) is developed at the GUI level in Kexi, users 
are forced to work only within the tabular view.

That said, recursive queries are indeed useful, e.g. for implementing 
folder-like structures or other hierarchies without additional programming. 
For instance, Oracle offers a "CONNECT BY" (nonstandard, surprise?), while 
SQL99 defines the feature using "WITH" clause. I do not know whether current 
PostgreSQL release features this function, but there were attempts to extend 
it this way [1].

[1] http://gppl.moonbone.ru/

-- 
regards / pozdrawiam, Jaroslaw Staniek
  Sponsored by OpenOffice Polska (http://www.openoffice.com.pl/en) to work on
  Kexi & KOffice: http://www.kexi.pl/en, http://www.koffice.org
  KDE3 & KDE4 Libraries for MS Windows: http://kdelibs.com, http://www.kde.org