How to Add Records to the PicoLisp Database
Tags: picolisp, lisp, database, webdev
Published: 2022-03-18 11:55:20

In the last post, we have created a simple entity/relation-model using the PicoLisp E/R framework. You can find the file with the definitions [here](https://gitlab.com/picolisp-blog/web-applications/-/blob/main/database/family-dbmodel.l).

Now let's create a new database and fill it with some values.

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

### Create a new database

First of all, let's start the file from the REPL and open a new database file. 

```
$ pil family-dbmodel.l +

: (pool "myFamily.db")
-> T 
```

The database is empty:

```
: (show *DB)
{1} NIL
-> {1}
```

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

### Inserting some data using the ``request`` function

From the previous posts you might remember that we can insert new items with the ``new`` function. However, for many cases there is a better alternative: the ``request`` function. What's the difference? ``request`` returns an object from the database if it exists, otherwise it creates a new one. This avoids that duplicates are inserted by mistake.

Syntax and example from the [documentation](https://software-lab.de/doc/index.html):

> ``(request 'typ 'sym ['hook] 'val ..) -> obj ``

Example: 
> : (request '(+Item) 'nr 2)
> -> {B2}

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


Let's create an object of the class ``+Man`` called ``Adam``.

```
: (request '(+Man) 'name "Adam")
-> {2}
```

This seems to have worked. Let's insert Eve as well.

```
: (request '(+Woman) 'name "Eve")
-> {6}
```

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

### Querying the data

Let's see what we have in our database now:

```
: (show *DB)
{1} NIL
   +Person {3}
   +Woman {7}
   +Man {4}
-> {1}
```

Well, it's not empty anymore, but we don't see anything like "Adam" or "Eve", only the object class names. In order to find our entries, we need to **query** the database. One of the most useful functions for that is ``collect``.

``collect`` has several options, for now let's only consider the most easy one: return all objects of a certain class. Again from the documentation:

> ``(collect 'sym 'cls  (...) )``

> Returns a list of all database objects of class ``cls``, where the values for the ``sym`` arguments correspond to the ``any`` arguments (...)

> ```
: (collect 'nr '+Item)
-> ({B1} {B2} {B3} {B4} {B5} {B6} {B8})
```

So, applied to our example: We know we have inserted one ``+Woman`` and one ``+Man``, both of them are ``+Person``. We can query it like this:

```
: (collect 'name '+Person)
-> ({2} {6})
```

This looks better! We can confirm that we have the right return values with ``show``:

```
: (show '{2})
{2} (+Man)
   name "Adam"
-> {2}
```

However it would be nice to receive this directly in one query. For this we can use the ``more`` function, which accepts a list and a function as arguments.

> ``(more 'lst ['fun]) -> flg`` 

> Displays the elements of ``lst`` (first form). After each step, ``more`` waits for console input, and terminates when a non-empty line is entered. In that case, ``T`` is returned, otherwise (when end of data is reached) ``NIL``.

Applied to our example:

```
: (more (collect 'name '+Person) show)
{2} (+Man)
   name "Adam"

{6} (+Woman)
   name "Eve"
```

Let's commit the content up to now to the database by ``commit``:

```
: (commit)
T
```

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

### Defining the relationships

What other fields do we have? About Adam and Eve we don't have much information about birthday, death day and job. But we can define the relationship between them.

Adam and Eve are partners. Since the partner attribute is a ``+Joint`` connection, it's enough to define it on one side to automatically set it on the other side as well. We can set the relation using the function ``put>`` and ``(commit)``. As a shortcut, we could also do ``put!>`` in one step.

*Note: ``put>`` is a method of the ``+Entity`` class and should not be confused with ``put`` for "normal" objects.*

Since we know that Adam is ``{2}`` and Eve is ``{6}``, we can address them directly. This is OK for debugging mode - in a real application, it should't be addressed directly because it could corrupt the debugger. 

So, we set Eve as partner of Adam:

```
: (put!> '{2} 'partner '{6})
-> {6}
```

Let's check both of them: They should now be partners of each other.

```
: (show '{2})
{2} (+Man)
   partner {6}
   name "Adam"
-> {2}

: (show '{6})
{6} (+Woman)
   partner {2}
   name "Eve"
-> {6}
```

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

### Some more entries

Let's also add the children: Kain, Abel and Set. We can add their jobs and parents as well.

```
: (request '(+Man) 'name "Kain" 'job "farmer" 'father '{2} 'mother '{6})
-> {12}
: (request '(+Man) 'name "Abel" 'job "shepherd" 'father '{2} 'mother '{6})
-> {14}
: (request '(+Man) 'name "Seth" 'father '{2} 'mother '{6})
-> {15}

: (commit)
-> T
```

Now both Adam and Eve have another attribute "kids" which contains three items:

```
: (show '{2})
{2} (+Man)
   kids ({15} {14} {12})
   partner {6}
   name "Adam"
-> {2}
```

Again, we could step through Adam's kids using a combination of ``more`` and ``show``. Instead of ``show`` we could also use ``get``:

```
: (more (get '{2} 'kids) show)
{15} (+Man)
   mother {6}
   father {2}
   name "Seth"

{14} (+Man)
   mother {6}
   father {2}
   job "shepherd"
   name "Abel"

{12} (+Man)
   mother {6}
   father {2}
   job "farmer"
   name "Kain"
```

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

### Validation checks 

As you might remember, for some values we have defined the types. For example, the birthday needs to be of class ``+Date``:

```
(rel birthday (+Ref +Date))
```

Let's see what happens if we define a string instead.

```
: (put> '{2} 'birthday "Genesis")
-> "Genesis"
```

Well, it seems it was accepted although "Genesis" is clearly not a date type.

```
: (show '{2})
{2} (+Man)
   birthday "Genesis"
   kids ({15} {14} {12})
   partner {6}
   name "Adam"
-> {2}
```

However, we can check if the data is correct using the ``mis>`` function.

```
: (mis> '{2} 'birthday "Genesis")
-> "Numeric input expected"
```

Therefore you should always run ``mis>`` before you commit any input into a database, for consistency and also for security reasons. 

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

In the next post we will expand this little example to a larger database which already has some values inside.


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

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