Remote DB Access

NOTE: This article builds off the family example in the Database Programming chapter of the PicoLisp tutorial.


PicoLisp offers three simple extensions for remote DB access.

The extensions are a Pilog (PicoLisp Prolog) rule for remote queries, as well as a global variable and a new function in the interpreter core to transparently handle foreign database objects.

'remote' rule (Pilog)
The 'remote' rule (in "lib/pilog.l") was developed first. It takes an arbitrary Pilog expression, arranges everything so that this expression is evaluated in parallel on remote machines, and collects the results locally.

Typically, the results of queries contain not only primitives like numbers or strings, but most notably references to other external symbols on remote machines. To make it possible to handle these symbols locally (access their attributes, display in the GUI etc.), a dedicated mechanism was built into the core.

'*Ext'
This mechanism must avoid conflicts with symbols in the local database, as well as with symbols residing on other remote machines. It is implemented by maintaining a database file offset for each consulted database. The "normal" PicoLisp database resides in a well-defined number of files (determined by the list in the second argument to 'pool'). For each external symbol (e.g. {3-7}) the interpreter knows how to access its contents (here the 7th object in the 3rd database file). An error occurs if the file number is larger than the number of files in the database.

A new global variable '*Ext' can be used now to define additional number spaces for dynamic database extensions. It should hold a list of cons pairs, where the CAR of each pair defines an offset, and the CDR a function to produce the contents of a symbol having that offset. The individual offsets should be chosen with care, so that they don't overlap.

'ext' function
An auxiliary function 'ext' is provided, that takes such an offset and causes certain I/O functions to add and subtract that offset to all file numbers of external symbols during input and output.

With that mechanism, both local and external databases see only symbols they can handle in their own space.

The DB Server

Let's look at a simple but complete example. We will use the family demo as described in the "Database Programming" chapter of "doc/tut.html".

The setup
Please edit "doc/family.l", and extend the 'go' function so that it looks like

   (de go ()
      (rollback)
      (task (port 4000)  # Set up the object server in the background
         (let? Sock (accept @)
            (unless (fork)  # Child process
               (in Sock
                  (while (rd)
                     (sync)
                     (tell)
                     (out Sock
                        (pr (eval @))) ) )
               (bye) )
            (close Sock) ) )
      (forked)
      (server 8080 "!person") )

The background task will listen on port 4000, and handle any requests. For this simple demo, we avoid issues like access permissions and authentication.

Then start it on a separate console as

   $ pil doc/family.l -main -go +

It could be used now with a browser just like before. In addition, it listens on port 4000 for remote queries.

The Test Client

Start a PicoLisp process on another console. It could be another application, or just a plain PicoLisp process without a local database:

   $ pil +
   :

The setup
Then set '*Ext':

   : (setq *Ext           # Set up external offsets
      (mapcar
         '((@Host @Ext)
            (cons @Ext
               (curry (@Host @Ext (Sock)) (Obj)
                  (when (or Sock (setq Sock (connect @Host 4000)))
                     (ext @Ext
                        (out Sock (pr (cons 'qsym Obj)))
                        (prog1
                           (in Sock (rd))
                           (unless @
                              (close Sock)
                              (off Sock) ) ) ) ) ) ) )
         '("localhost")
         '(20) ) )

Normally, the 'quote' expressions at the end would specify several offsets,ports and hosts. For our purpose, a single process on "localhost", listening on port 4000 (i.e. our family demo), will suffice. If we look at the first (and only) entry,

   : (pretty (car *Ext))
   (20
      (Obj)
      (job '((Sock))
         (when (or Sock (setq Sock (connect "localhost" 4000)))
            (ext
               20
               (out Sock (pr (cons 'qsym Obj)))
               (prog1
                  (in Sock (rd))
                  (unless @ (close Sock) (off Sock)) ) ) ) )

we see an offset of 20 (this would leave plenty space for a local database), and a function taking a single 'Obj' argument. This function will be called internally by the PicoLisp interpreter whenever an external symbol is accessed that doesn't belong to the local database. It connects to the given host and port (or re-uses an already open connection in the local variable 'Sock'), uses the 'ext' function to translate output (with 'pr') and input (with 'rd'), and closes the connection if end of file is encountered.

Finally, just for convenience, define a list of database resources, matching the offset(s) in '*Ext':

   : (de rsrc ()  # Simple resource handler, ignoring errors or EOFs
      (extract
         '((@Ext Host)
            (let? @Sock (connect Host 4000)
               (cons
                  (curry (@Ext @Sock) (X)  # out
                     (ext @Ext (out @Sock (pr X))) )
                  (curry (@Ext @Sock) ()  # in
                     (ext @Ext (in @Sock (rd))) ) ) ) )
         '(20)
         '("localhost") ) )



The CARs again are the offsets, and the CDR are executable expressions so that the 'remote' Pilog rule knows where and how to connect to remote servers.

Remote queries
Now we are ready to start remote queries and symbol accesses in the test client. The simplest example along the line of "doc/tut.html" would be a query to get all "Edward"s

   : (? (db nm +Person "Edward" @P))
   -> NIL

Naturally, it won't work, because the test client has no local database!

However, if we use 'remote', it works:

   : (? @Rsrc (rsrc) (remote (@P . @Rsrc) (db nm +Person "Edward" @P)))
    @Rsrc=((((X) (ext 20 (out 3 (pr X)))) NIL (ext 20 (in 3 (rd))))) @P={K-;}
    @Rsrc=((((X) (ext 20 (out 3 (pr X)))) NIL (ext 20 (in 3 (rd))))) @P={K-1B}
    @Rsrc=((((X) (ext 20 (out 3 (pr X)))) NIL (ext 20 (in 3 (rd))))) @P={K-R}
    @Rsrc=((((X) (ext 20 (out 3 (pr X)))) NIL (ext 20 (in 3 (rd))))) @P={K-1K}
    @Rsrc=((((X) (ext 20 (out 3 (pr X)))) NIL (ext 20 (in 3 (rd))))) @P={K-a}.
    # hit enter to step through results
    # type a dot '.' (or any char) followed by enter to return to the REPL

The original query, executed natively on the server, would return the symbols {2-;}, {2-1B}, {2-R}, ... (as opposed to {K-;}, {K-1B}, ...), and so on. We can see that the offset of 20 transformed the database file number '2' to 'K'. Please note that your external symbol names will almost certainly be different.

Another query:

   : (? @Rsrc (rsrc)
      (remote (@P . @Rsrc)
         (db nm +Person "Edward" @P)
         (val "Queen" @P mate job) ) )
    @Rsrc=((((X) (ext 20 (out 4 (pr X)))) NIL (ext 20 (in 4 (rd))))) @P={K-1B}
   -> NIL

We see that any query that would be legal on a local database, can be evaluated remotely simply by extending the query with

   (remote (@Var . @Resources) ..)

'@Var' is the variable which 'remote' is to return from the remote machine.

Using symbols locally
The returned external symbols can be used locally, just like normal external symbols:

   : (show '{K-1B})
      {K-1B} (+Man)
      kids ({K-1C} {K-1D} {K-1E} {K-1F} {K-1G} {K-1H} {K-1I} {K-g} {K-a})
      nm "Albert Edward"
      job "Prince"
      mate {K-f}
      fin 680370
      dat 664554
   -> {K-1B}

   : (show '{K-1B} 'kids 1 'nm)
   "Beatrice Mary Victoria" "Beatrice Mary Victoria"
   -> "Beatrice Mary Victoria"

   : (show '{K-1B} 'mate 'nm)
   "Victoria I" "Victoria I"
   -> "Victoria I"

   : (show '{K-1B} 'mate 'kids)
   -> ({K-a} {K-g} {K-1I} {K-1H} {K-1G} {K-1F} {K-1E} {K-1D} {K-1C})

A '+QueryChart' in a local GUI can have 'remote' applied to any Pilog expression (typically with 'select'), and will work just like with a local database.

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

19mar16    erik