Language

A gentil introduction to using Pliant databases

Defining the database

module "/pliant/storage/database.pli"

public

type Article
  field Str ref
  field Int count
  field Float ppu

type Order
  field Str customer
  field DateTime created
  field DateTime ordered
  field DateTime delivered
  field Set:Article article

type Customer
  field Str name
  field Str address

type Shop
  field Set:Order order
  field Set:Customer customer

(gvar Database:Shop shop_database) load "data:/my_corp/shop/shop.pdb" mount "/my_corp/shop"

In this first sample,  we define the structure of our initial sample database.
Each table or subtable is defined using a 'type' instruction.
Fields use standard Pliant data types.
The only special data type used by the database engine is 'Set:xxx' that says that the variable (field) will contain a table or subtable. Each element (record) in a set (table or subtable) is associated with a unique string key, and can be access either directly through providing the key, or through scanning all elements of the set one by one.

The 'public' statement is intended to make all data types (Article, Order, Custoomer and Shop) and variables (shop_database) available to the external. This sample listing is assume to be the listing of /pliant/sample/shop1/database.pli module, and we'll see the importance of public a bit later when introducting the user interface sample module for handling the database..

Database model digression

What might look strange to you at first if you are familiar with the now standard database relational model, is that 'Set' is used both for declaring tables in the 'Shop' list of tables, and to declare articles subtable in orders records. This is because Pliant database model is tree. Just like in any tree data structure, we have nodes and leafes. 'Shop' is defining the root node of the tree. Nodes are either types (records) or set field (table or subtable). Leafes are non set fields such as 'ref' or 'count'.

If we want to model the database according to the relational model, we would write:

type Article
  field Str order
  field Str ref
  field Int count
  field Float ppu

type Order
  field Str customer
  field DateTime created
  field DateTime ordered
  field DateTime delivered

type Customer
  field Str name
  field Str address

type Shop
  field Set:Order order
  field Set:Customer customer

The difference is that, under the database relational model, a database is a set of tables, each table is a set of records and each record contains a well defined set of fields. So, the 1 to n relations, in our sample the fact that one order contains n articles is coded

   •   

in the Pliant tree model, as an 'article' field with type 'Set:Article' in 'Order' table

   •   

in the relational model, as a 'order' field in the 'Article' table.

Honesty makes me say that the reliational model, from the design point of view, is a better model than the tree model used by Pliant database engine, because it's more flexible. On the partical side, it's another issue. Please refer to the 'An introduction to Pliant storage' for extra details.

Back to defining the database

Back to our initial sample, the effective database is declared on the line:

(gvar Database:Shop shop_database) load "data:/my_corp/shop/shop.pdb" mount "/my_org/shop"

'Database:xxx' says that we want a Pliant database stored in XML like file (ASCII), where xxx is a data type, here 'Shop', that provides the layout for the database content.

'load' is defining the file where database content will be stored on disk.

'mount' is defining where the database tree will be in the Pliant global databases tree.
This used to be very important to have each database properly mapped in a global tree a fiew years ago when the standard user interface was the old Pliant HTTP server, but it's not that usefull with the new UI (user interface), so you could just get rid of the 'mount' option.

On most applications, an extra 'log' option will be used:

(gvar Database:Shop shop_database) load "data:/my_corp/shop/shop.pdb" log "data:/my_corp/shop/shop.log" mount "/my_org/shop"

The 'log' option defines where the database infinit log will be strored.
The database infinit log is a great value for today cheap monster hard disks since it enables you to know who (and when) entered that blody wrong data in your database, or selectively rewind what a hill behaved intruder has modified in your database using a valid (password stolen) account.
In very fiew words: this is highly recommanded.

The infinit log of your database might become huge over time, so that you might want to compress it at some point. This can be done automatically through:

shop_database configure "encoding zlib"
shop_database configure "growth "+(string 64*2^20)

The last line means: no more than 64 MB of uncompressed datas at the end of the shop.log file.
If you compress the database, you lose the ability to edit it using a standard text editor, but keep the other possibilities provided by an infinit log.

One last point about the database files. If the file:/pliant_data/my_corp/shop/ directory does not exist, the changes applied to the database will not be logged, so lost in the end. The directory can be created either using a file browser or through a Pliant instruction:

module "/pliant/admin/file.pli"
file_tree_create "data:/my_corp/shop/"

A first use of the database

The following sample might be executed within Pliant interpreter:

module "/pliant/sample/shop1/database.pli"
var (Data Set:Customer) shop_customer :> shop_database:data customer
shop_customer create "c1"
var Data:Customer c :> shop_customer "c1"
c name := "My first customer"
c address := "12 rue de Nante[lf]48235 Milou[lf]France"

The result, in file file:/pliant_data/my_corp/shop/shop.pdb looks like:

<plogin timestamp="Sat, 31 May 2008 12:23:25 GMT" />
<pcreate path="/customer/c1" />
<plogin timestamp="Sat, 31 May 2008 12:23:25 GMT" />
<pdata path="/customer/c1/name">My first customer</pdata>
<plogin timestamp="Sat, 31 May 2008 12:23:25 GMT" />
<pdata path="/customer/c1/address">12 rue de Nante&#10;48235 Milou&#10;France</pdata>

In this first sample, we see that Pliant databases are mostly handled through pointers, and that database pointers (as opposed to standard pointers that have type Pointer:xxx or Link:xxx) have type Data:xxx

In our sample, 'shop_database' is the database object and 'data' method is used to get a pointer to the root of the database tree. So, the first line

var (Data Set:Order) shop_order :> shop_database:data order

is the same as:

var Data:Shop shop_root :> shop_database data
var (Data Set:Order) shop_order :> shop_root order

The first line get's the pointer to the root of the database,
and the second one get's a pointer to a subtree though following edge with label 'order'.

The two next lines are easy to understand:

shop_order create "101"
var Data:Order o :> shop_order "101"

The first line creates a new record associated with key "101" in the 'Order'' table,
and the second one get's a database pointer on th newly created record.

On the last lines:

a ref := "r2735"

we see that changing some database content is fairly simple at application level: we can use the standard ':=' operator just like for ordinary variables, and the reason is that the database machinery is concentrated in the  'Data:xxx' special pointers.

A first user interface for accessing the database

Here is the /pliant/sample/shop1/index.ui sample module that provides a first, incomplete, user interface for the sample shopping database:

module "/pliant/graphic/ui/server/api.pli"
module "/pliant/storage/database.pli"
module "database.pli"

ui_path "/pliant/sample/shop1/order/"
  var Data:Order o :> shop_database:data:order subpath
  if not exists:o
    url_return
    return
  window left
    button "Exit" key "alt x" stretch
      url_return
  window main
    title "Order '"+keyof:o+"'"
    header "Status"
    table noborder
      row
        cell
          text "Customer:"
        cell
          hook
            input "" o:customer
            section "customer" dynamic
              text " "+(shop_database:data:customer o:customer):name
          change
            section_replay "customer"
      row
        cell
          text "Created on:"
        cell
          input "" o:created
      row
        cell
          text "Ordered on:"
        cell
          input "" o:ordered
      row
        cell
          text "Delivered on:"
        cell
          input "" o:delivered
    header "Order content"
    section "content" dynamic
      table
        row
          cell header
            text "Article"
          cell header
            text "Unit price"
          cell header
            text "Count"
          cell header
            text "Price"
          cell header
            void
        var Float total := 0
        each a o:article
          row
            cell
              input "" a:ref
            hook
              cell
                input "" a:ppu
              cell
                input "" a:count
            change
              section_replay "content"
            cell
              text (string a:count*a:ppu)
              total += a:count*a:ppu
            cell
              button "remove"
                o:article delete keyof:a
                section_replay "content"
        row
          cell
            input "" (ovar Str ref)
          cell
            ovar Float ppu := undefined
            input "" ppu
          cell
            ovar Int count := 1
            input "" count
          cell
            void
          cell
            button "add"
              var Str id := generate_id
              o:article create id
              var Data:Article a :> o:article id
              a ref := ref
              a ppu := ppu
              a count := count
              section_replay "content"
        row
          cell span 3 1
            text "Total"
          cell
            text string:total
          cell
            void

ui_path "/pliant/sample/shop1/menu"
  window left
    button "New" key "alt n" stretch help "Create a new order"
      window top
        input "New order ID: " (ovar Str nid) focus true
        button "Create" key "alt c"
          if (exists shop_database:data:order:nid)
            section_overwrite "comment"
              eol
              text "Order '"+nid+"' already exists !"
          else
            shop_database:data:order create nid
            var Data:Order o :> shop_database:data:order nid
            o created := datetime
            url_call "order/"+nid
        button "Cancel" key "escape"
          window top
            void
        section "comment"
          void
    eol
    button "Edit" key "alt e" stretch help "Edit an existing order"
      window top
        input "Order ID: " (ovar Str eid) focus true
        section "comment"
          void
        button "Edit" key "alt e"
          if not (exists shop_database:data:order:eid)
            section_overwrite "comment"
              eol
              text "There is no '"+eid+"' order !"
          else
            url_call "order/"+eid
        button "Cancel" key "escape"
          window top
            void
        section "comment"
          void
    eol
    button "Exit" key "alt x" stretch
      url_return
  window main
    title "Shopping sample database"
  window bottom
    section "help"
      void

When reading the following explainations, you are assumed to be mastering the Pliant UI (user interface) basic concepts introduced in other articles about the UI.

The homepage of the sample database user interface is defined by:

ui_path "/pliant/sample/shop1/menu"
   ...

If you are using a single Pliant process for the server and the client, you should be abble to connect the sample database using loopback:/pliant/sample/shop1/menu URL. How to properly map URLs to modules when using a different process, or a different computer, for the client and the server, and maybe even using a standard web browser for the client, is outside the scope of this article. Only the configuration files and final URL would change, not the code.

Then, each order is mapped to an URL so that it could be accessed directly from another application through a link. In the single process configuration, the URL for order 'o1' would be:
loopback:/pliant/sample/shop1/order/o1
The mapping, and code for editing an order is provided by:

ui_path "/pliant/sample/shop1/order/"
  ...

More on records keys and scanning

'each' instruction is used to scan all the records in a table or subtable. In the first sample, we wrote:

each a o:article
   ...

The first parameter (here 'a') provided to each is an identifier that will automatically declare a local variable which is a data pointer to a record of the scanned table or subtable (here Data:Order).
All records are associated with a unique key (the key data type is always Str), and 'each' will scan records in the ascending order of key.

When we have a pointer to a record, we can get the associated key with 'keyof':

each a o:article
  console keyof:a eol

The key associated with each record in a table or subtable cannot be changed. This is a serious limitation of Pliant database engine over the relational model. There would be no difficulty to implement the ability to change the key associated with a record, but it would not be a good idea. Changing the key works great on a closed database single server configuration, but is a nightmare in a multi servers configuration. So, Pliant code does not provide the feature so that people are forced to cope with the contrain at database planification time (early in the process) rather than having to cope with it when the project get's large and a lot of code is already written and needing the feature.
In facts, if we allow the key associated to a record to be changed, then in the end, it will lead to creating a second key, fixed, that will be hidden but used internally to keep consistency. The result would be the need for a large dictionary of all keys of currently loaded database records, so that the application level key that can be changed would be not much more that a field in the record in facts.
So, if your application makes it mandatory to have a key that can be changed, just store it as a field in the record. Also it might have serious impact on performances since Pliant database engine is too naive to silently create secondary indexes like SQL engines do, with a bit of extra code, it should be possible to cope with it, also this belongs to the advanced article about using Pliant databases, so I stop the explaination here at the moment.

'each' can have two extra options, 'filter' and 'sort':

each o shop_database:data:order filter o:delivered=undefined sort o:ordered
  ...

here, 'filter' is used to skip all orders that have already been delived (the delivery timestamp is defined) and 'sort' is used to scan records according to the order confirmation date as opposed to scanning according to order key.