A Minimal PicoLisp DB/GUI Application

A few weeks ago, my wife wanted a small online database application, for common address and contact data of family members, relatives, friends and so on.

Normally, a PicoLisp database contains objects of various classes. To handle them in the GUI, you must be able to search and/or create objects, edit their properties, and delete unwanted objects.

A typical application implements the following features: But in case of a simple address database, this is overkill. There is just a single class of objects.

Fortunately, there is a way to handle all this in a single swoop. First of all, there is no need for a menu. We can jump directly to the addresses. And all the rest can be handled with a single GUI component, the +QueryChart.

Refer to the end of this article for a complete source listing.

The Address DB Data Model

Let's define the "Person" class (for the purpose of this article in a slightly reduced form) as:

   (class +Prs +Entity)
   (rel nm (+Sn +IdxFold +String))        # Name
   (rel adr (+IdxFold +String))           # Address
   (rel em (+String))                     # E-Mail
   (rel tel (+String))                    # Telephone
   (rel dob (+Date))                      # Date of birth

It can be easily extended as needed.

Instead of separate properties for street, zip, city etc., we have a single free-format property for the whole address. We define two non-unique indexes, one for the person's name and one for the address. The name index supports a tolerant search (using the Soundex algorithm for "similar" names).

The GUI Function

We have only a single GUI function called work. It starts with the standard app (= establish a session), action (= handle form events) and html (= generate the HTML page) functions, as is typical for each PicoLisp application. The <ping> function is optional, it uses JavaScript to generate keep-alive events.

   (de work ()
      (app)
      (action
         (html 0 Ttl "@lib.css" NIL
            (<ping> 2)

Then - as an elementary security measure - it shows a password field in the first form, with a hard-coded password "mypass". You may change this to something more elaborated.

   (ifn *Login
      (form NIL
         (gui 'pw '(+PwField) 20 ,"Password")
         (gui '(+Button) ,"login"
            '(ifn (= "mypass" (val> (: home pw)))
               (error ,"Permission denied")
               (on *Login)
               (url "!work") ) ) )

(The real application has a full user/password authentication (from "@lib/adm.l"). We omitted it here just for brevity)

In any case, it uses the global variable *Login, which is set by the "login" button when the password matches. If so, the second - main - form is displayed.

   (form NIL
      (<grid> "--."
         "Name" (gui 'nm '(+DbHint +TextField) '(nm +Prs) 20)
         (searchButton '(init> (: home query)))
         "Address" (gui 'adr '(+DbHint +TextField) '(adr +Prs) 20)
         (resetButton '(nm adr query)) )

It shows two search text fields, "Name" and "Address", and two buttons "Search" and "Reset". The search fields use the +DbHint prefix class, to pop up search proposals according to which names and addresses are already in the database. Pressing the "Search" button will start the search, and "Reset" clears all fields.

Now follows the heart of this application's GUI. We use a +QueryChart to both search for matching entries, as well as to edit or create them.

A +QueryChart is used in all search dialogs. It uses a Pilog query to search for a set of given criteria, and displays a possibly unlimited number of results as long as there are more matching entries, and the user continues to press the scroll buttons.

   (gui 'query '(+QueryChart) 12
      '(goal
         (quote
            @Nm (val> (: home nm))
            @Adr (val> (: home adr))
            (select (@@)
               ((nm +Prs @Nm) (adr +Prs @Adr))
               (tolr @Nm @@ nm)
               (part @Adr @@ adr) ) ) )

The first argument (here 12) gives the initial number of hits to populate the chart. The second argument is a Pilog query, which uses the values of the "Name" and "Address" search fields to do a tolerant and partial text search. Check https://software-lab.de/doc/select.html for details.

Then follow the three standard +Chart arguments

   6
   '((This) (list (: nm) (: adr) (: em) (: tel) (: dob)))
   '((L D)
      (cond
         (D
            (mapc
               '((K V) (put!> D K V))
               '(nm adr em tel dob)
               L )
            D )
         ((car L)
            (new! '(+Prs) 'nm (car L)) ) ) ) )

which are the number of columns (here 6), a put and a get function.

A PicoLisp +Chart calls these functions whenever something happens in the GUI. The put function translates the logical contents of a chart's row (here an address object) to the physical display of name, address, email etc.:

   '((This) (list (: nm) (: adr) (: em) (: tel) (: dob)))

The This argument to put is the object, and it expands to a list of the values for the row.

The get function does the opposite, mapping values in a row to an object. It takes in L a list of GUI values (the texts, numbers, dates etc. entered by the user in the chart's row), and in D the logical data (here: the object).

   '((L D)

Then it checks, in a cond expression, whether D - the object - is already existing. If so, it stores the values from L in the corresponding object properties, thus updating the database as necessary:

   (D
      (mapc
         '((K V) (put!> D K V))
         '(nm adr em tel dob)
         L )
      D )

If the object does not exist, but the first column of the chart contains a name (that is, the user has just entered a new value), then a new entry is created with that name:

   ((car L)
      (new! '(+Prs) 'nm (car L)) ) ) ) )

That's all! This is the logic necessary to create or modify address entries.

The +Chart or +QueryChart is the internal object handling this GUI logic. Now we need the physical components for user interaction. We put them into a table

   (<table> NIL (choTtl "Entries" '+Prs)

with proper headers

   (quote
      (NIL "Name")
      (NIL "Address")
      (NIL "E-Mail")
      (NIL "Telephone")
      (NIL "Date of birth") )

followed by 12 rows of text-, mail-, telephone- and date-fields

   (do 12
      (<row> NIL
         (gui 1 '(+TextField) 30)
         (gui 2 '(+TextField) 40)
         (gui 3 '(+MailField) 20)
         (gui 4 '(+TelField) 15)
         (gui 5 '(+DateField) 10)
         (gui 6 '(+DelRowButton)
            '(lose!> (curr))
            '(text "Delete Entry @1?" (curr 'nm)) ) ) ) )

Note the +DelRowButton in the last column. It can be used to delete an entry from the database. This button will pop up a dialog, asking whether the user really wants to delete this entry. When deleting multiple rows, though, it won't ask a second time.

Finally, the four standard scroll buttons

   (scroll 12) ) ) ) ) )

are displayed. They allow to scroll through the chart line-by-line or by whole pages.

Initialization and Start

By convention, a PicoLisp application supplies two functions, main and go. main should initialize the runtime environment, and go should start the GUI event handler.

   (de main ()
      (locale "UK")
      (pool "adr.db") )

   (de go ()
      (server 8080 "!work") )

locale is needed mainly for the +TelField to properly handle telephone numbers. You might want to supply your own, e.g. from the "@loc/" directory.

If you copy/paste the source code below to a file "minDbGui.l", or download it from https://software-lab.de/minDbGui.l, you can start it as

   $ pil minDbGui.l -main -go -wait

or - in debug mode - as

   $ pil minDbGui.l -main -go +



Complete Source Code


   # 11jan15abu
   # (c) Software Lab. Alexander Burger

   (allowed ()
      "!work" "@lib.css" )

   (load "@lib/http.l" "@lib/xhtml.l" "@lib/form.l")

   (class +Prs +Entity)
   (rel nm (+Sn +IdxFold +String))        # Name
   (rel adr (+IdxFold +String))           # Address
   (rel em (+String))                     # E-Mail
   (rel tel (+String))                    # Telephone
   (rel dob (+Date))                      # Date of birth

   (de work ()
      (app)
      (action
         (html 0 Ttl "@lib.css" NIL
            (<ping> 2)
            (ifn *Login
               (form NIL
                  (gui 'pw '(+PwField) 20 ,"Password")
                  (gui '(+Button) ,"login"
                     '(ifn (= "mypass" (val> (: home pw)))
                        (error ,"Permission denied")
                        (on *Login)
                        (url "!work") ) ) )
               (form NIL
                  (<grid> "--."
                     "Name" (gui 'nm '(+DbHint +TextField) '(nm +Prs) 20)
                     (searchButton '(init> (: home query)))
                     "Address" (gui 'adr '(+DbHint +TextField) '(adr +Prs) 20)
                     (resetButton '(nm adr query)) )
                  (gui 'query '(+QueryChart) 12
                     '(goal
                        (quote
                           @Nm (val> (: home nm))
                           @Adr (val> (: home adr))
                           (select (@@)
                              ((nm +Prs @Nm) (adr +Prs @Adr))
                              (tolr @Nm @@ nm)
                              (part @Adr @@ adr) ) ) )
                     6
                     '((This) (list (: nm) (: adr) (: em) (: tel) (: dob)))
                     '((L D)
                        (cond
                           (D
                              (mapc
                                 '((K V) (put!> D K V))
                                 '(nm adr em tel dob)
                                 L )
                              D )
                           ((car L)
                              (new! '(+Prs) 'nm (car L)) ) ) ) )
                  (<table> NIL (choTtl "Entries" '+Prs)
                     (quote
                        (NIL "Name")
                        (NIL "Address")
                        (NIL "E-Mail")
                        (NIL "Telephone")
                        (NIL "Date of birth") )
                     (do 12
                        (<row> NIL
                           (gui 1 '(+TextField) 30)
                           (gui 2 '(+TextField) 40)
                           (gui 3 '(+MailField) 20)
                           (gui 4 '(+TelField) 15)
                           (gui 5 '(+DateField) 10)
                           (gui 6 '(+DelRowButton)
                              '(lose!> (curr))
                              '(text "Delete Entry @1?" (curr 'nm)) ) ) ) )
                  (scroll 12) ) ) ) ) )

   (de main ()
      (locale "UK")
      (pool "adr.db") )

   (de go ()
      (server 8080 "!work") )

   # vi:et:ts=3:sw=3

https://picolisp.com/wiki/?mindbgui

09apr17    abu