Creating a User Interface to the Database: Setup
Tags: picolisp, lisp, functional, database
Published: 2022-03-27 21:29:48

A standalone database is not very useful, because editing and viewing from the REPL is quite limited. We need a graphical interface in order to work with it properly. Let's see how we can use the browser to display, search and edit data.

------------------------------

*This post and the following overlap thematically with the [Web Application Tutorial](https://picolisp-blog.hashnode.dev/series/web-app). It is recommended that you have read them, especially regarding the PicoLisp GUI Framework.*

----------------------------

### The Goal

Let's write a single-page script that accesses the database and **displays the family tree** of any record that is specified via the URL (for example ``{A1}``). As a result, we get a recursive print-out of that person's partner and all children and children's children. In the end, it should look like this:

![familytreelinks.gif](https://cdn.hashnode.com/res/hashnode/image/upload/v1634466288147/gt8Hr-qBI.gif)

However, before we can work on that, we first need to have the server and database ready.

-------------------------

# Setting up the script

Let's think about what our little program needs to contain:
1. Some global definitions: namespace, libraries, constants.
2. The entities/relationship model,
3. Opening the database file,
4. Starting the server,
5. A ``html`` function in which we get and display the data.

We go through it step by step.

-----------------

### Namespace, libraries, constants

- First of all, we need to import the libraries related to the PicoLisp GUI framework: ``http.l``, ``xhtml.l``, ``forml.``.

- Secondly, we need to set the *name space* in order to ensure that the objects are unique even if we expand our program and integrate more classes. Let's initialize a new namespace ``family`` and set the standard-namespace ``pico`` as second in the list (when a symbol cannot be found in ``family``, the interpreter will search in ``pico`` next).

- Last, let's also define some global constants, for example ``*Css``.

```
(load "@lib/http.l" "@lib/xhtml.l" "@lib/form.l" )
(setq *Css '("@lib.css" "css/bootstrap.css"))
(symbols 'family 'pico)
```

----------------

### The E/R Model

Next comes the E/R model that we have discussed in the previous post. It needs to include all classes and relations plus the information how the data is spread across the database files.

```
(class +Person +Entity)
(rel nm (+Need +Sn +Idx +String))      # Name
(rel pa (+Joint) kids (+Man))          # Father
...

(dbs
   (0)                                 # @:64
   (2 +Person)                         # A:256
   (3 (+Person nm))                    # B:512
   (3 (+Person job dat fin)) )         # C:512
```

----------------------

### Opening the Database File

Now let's write a short function that opens the database file. We have done this manually from the REPL in the last post. It included two steps:
1. Set the namespace,
2. Open the files (assuming they are all in the folder "family").

   ```
(de main ()
      (symbols '(family pico))
      (pool "family/" *Dbs)
```

3. Exception handling - since we're opening an external file, we should also think about what happens if it fails (for example because it doesn't exist yet).

   If it failed, then our global variable ``*DB`` will be empty. If this happens, let's just create a new database and put new entries inside:

   ```
   (unless (val *DB)
      (put>
         (set *DB (request '(+Man) 'nm "Adam"))
         'mate
         (request '(+Woman) 'nm "Eve") )
      (commit) ) 
```

----------------

### Starting the server

Next we define the function which starts the server. In order to have a clean database, first of all we execute ``(rollback)`` first to clean the RAM from cached objects. 

The ``server`` function accepts an optional URL argument. We can choose between three options:

- If the path starts with an exclamation mark ``!``, the rest (without the '!') is taken as the **name of a Lisp function** to be called. All arguments following the question mark are passed to that function. 

- If a path ends with ".l" (a dot and a lower case 'L'), it is taken as a **Lisp source file name** to be loaded. 

- If the extension of a file name matches an entry in the global mime type table ``*Mime`` , the **file is sent to the client** with ``mime-type`` and ``max-age`` values taken from that table. Otherwise, the file is sent to the client with a ``mime-type`` of "application/octet-stream" and a ``max-age`` of 1 second. 

For our example, let's call the server with the function ``treeReport`` which we will define in the next step.

```
(de go ()
   (rollback)
   (server 8080 "!treeReport") )
```

--------------------------

### The ``treeReport`` function

Now finally we're coming to the interesting part. We said that we want to pass the parameters from the URL. How does that work?

The URL is evaluated as follows: First comes the **desired URI**, then comes a  question mark, and then an arbitrary number of values can be passed. They are encoded as follows:

 - An internal symbol starts with a dollar sign ``$``
 - A number starts with a plus sign ``+``
 - An external (database) symbol starts with dash ``-``
 - A list (one level only) is encoded with underscores ``_``
 - Otherwise, it is a transient symbol (i. e., a plain string) .

In other words, if we want to pass the database record ``{A1}``, we can pass it like this: "[Link to page]/?-A1".

That means our ``treeReport`` function needs to accept one argument which is a database record. We set it as current object:

```
(de treeReport ( This )
```

Then we define our "normal" HTML function. As proof of concept, let's try to print the current record's name property as headline.

```
(de treeReport ( This )
   (html 0 "Family Tree View" *Css NIL
      (<h1> NIL (prinl (: nm))) ) )
```

Now let's start our script from the command line. In general, functions can be started from the command line by a prepending dash ``-``. However, for our ``main`` and ``go`` functions, we need the namespace to ``family`` first, syntax: ``-<namespace>~<function>`` *(note: the namespace delimitor is a tilde ``~``, not a dash ``-``)*. In other words, like this:

```
$ pil family-treeview.l -family~main -go +
```

Since the server is running at port 8080, we can now point or browser to http://localhost:8080/?-A1 and see the name of the ``{A1}`` record expanded:

![treeview1.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1634389825702/ydpkW0SYt.png)

It works!

-------------------------

### Some words about security

If you read this post carefully, you might have noticed that it's possible to execute basically **any PicoLisp function or file** via the command line. Needless to say that this is a huge security hole that needs to be addressed.

The access to resources is controlled via the global variable ``*Allow`` which is checked by the server before delivering any data to the client. We can set this variable via the ``allowed`` function.

In our example, it coud look like this:

```
 (allowed ("css/")
   "@lib.css" "!treeReport" )
```

Access is allowed to everything inside the folder `` css`` as well as the library file ``@lib.css`` and the function ``treeReport``. Everything else is blocked.


------------------------

The file up to this point can be downloaded [here](https://gitlab.com/picolisp-blog/web-applications/-/blob/main/database/family-treeview-step1.l).

In the next post, we will expand our function ``treeReport`` to display the family tree of the current record.

------------------------

### Sources
- https://software-lab.de/doc/app.html  
- https://software-lab.de/doc/index.html