DEV Community

Cover image for Creating a User Interface to the Database: Setup
Mia
Mia

Posted on • Originally published at picolisp-explored.com

Creating a User Interface to the Database: Setup

#picolisp #lisp #functional #database

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. 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

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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode
  1. 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) )
Enter fullscreen mode Exit fullscreen mode

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") )
Enter fullscreen mode Exit fullscreen mode

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 )
Enter fullscreen mode Exit fullscreen mode

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))) ) )
Enter fullscreen mode Exit fullscreen mode

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 +
Enter fullscreen mode Exit fullscreen mode

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

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" )
Enter fullscreen mode Exit fullscreen mode

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.

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

Sources

Top comments (0)

Read next

asim_khan_cbe65e41bcbbc65 profile image

Join the Party: A Comprehensive Guide to SQL Joins with Examples

Asim khan -

mrcaption49 profile image

Difference between GROUP BY and DISTINCT in SQL

Pranav Bakare -

amr-saafan profile image

How to Use the SQL Server ANY Keyword for Flexible Querying

Amr Saafan -

kaustubhyerkade profile image

Maximizing Performance: Best Practices for Database Scaling

kaustubh yerkade -