URL to Database Mapping Tutorial

In this tutorial, we show you how to create a basic wiki engine and how to map a URL or the path component of a URL to a database entry. This tutorial builds on what we learned in the Lua SQLite Database Tutorial.

The purpose with this tutorial is to show you how to do URL to database mapping, and the wiki engine we design in this tutorial will be fairly basic. You can use what you learn in this tutorial as a base if you want to build a full-fledged wiki engine. The SQL table that will be used in the wiki engine for storing wiki pages will be created as follows:

CREATE TABLE wiki (relpath TEXT, data TEXT)

The 'relpath' is the URL's relative path element. The relpath is used as a key and allows us to search for pages by using the URL's relative path element. We will get more into relative paths soon. The 'data' is the text displayed on the wiki page.

Virtual File System and URL Mapping

The Mako Server is a spin-off from the Barracuda Embedded Web Server Library and this server supports the concept of a virtual file system, where the path component of a URL is mapped to directory nodes installed in this file system. Each installed directory node can optionally have a callback function called the directory service function. A directory service function allows us to map the URL to SQL queries.

Directory Service Function

The following code snippet shows the directory service function in the wiki engine's .preload script.

   1   local function wikifunc(_ENV,relpath)
   2      local sql=string.format("data FROM wiki WHERE relpath=%s",
   3                              luasql.quotestr(relpath))
   4      local data=su.find(function() return su.open"wiki" end, sql)
   5      if data then
   6         if request:data"edit" then
   7            _ENV.relpath=relpath
   8            _ENV.wikidata = data
   9            response:forward(wikidir:baseuri()..".edit/modify.lsp")
  10         else
  11            response:write("<html><body>",
  12                           text2html(data),
  13                           "<p><a href='?edit='>edit</a> <a href='",
  14                           wikidir:baseuri(),
  15                           "'>index</a></p>",
  16                           "</body></html>")
  17         end
  18      else
  19         _ENV.relpath=relpath
  20         response:forward(wikidir:baseuri()..".edit/create.lsp")
  21      end
  22   end
  • The wiki function on line 1 above takes two arguments, the Lua environment (_ENV) and the directory function's relative path. The relative path is what we will use when translating the URL to database queries.
  • We create the SQL query on line 2 and 3. Recall (from the Lua SQLite Database Tutorial) that we do not include the SQL 'SELECT' keyword when using the sqlutil library.
  • We use the sqlutil function 'find' on line 4. This function returns the search result, if found. The complete SQL query will look something like the following:
    SELECT data FROM wiki WHERE \ relpath='the/relative/path'
  • Line 5 to 17 deals with the server response if the page was found in the 'wiki' table. If the page was found, we either delegate the response to the modify.lsp page if the edit parameter is set or we create the response page. Line 11 to 16 creates a complete HTML page by using data from the SQL query.
  • We end up at line 20 if the page is not found in the SQL 'wiki' table. The LSP page 'create.lsp' is invoked if a page does not exist. This is a standard LSP page that emits a HTML form where the user can create a new page.

Directory Objects

The server provides a number of directory object types that can be installed in the virtual file system. When you load the wiki engine using the Mako Server, the server creates one directory node of type resource reader for the wiki application and inserts this directory node into the virtual file system. The resource reader is your LSP application and the resource reader's directory node is made available as 'dir' in the preload script. The following example inserts the above directory service function ' wikifunc' into this directory node.


The directory service function takes precedence over LSP pages in your application, and the 'wikifunc' as we have designed it above will make it impossible to run any of our LSP pages in the application since all pages will be assumed to be part of the wiki. What we want to do is to be able to access the LSP pages in our example such as 'index.lsp' and the two pages we use for editing 'create.lsp' and 'modify.lsp'. Any resource not found in our LSP application will be assumed to be part of the wiki. One way to solve this is to create a new directory object and then insert this as a child object of the application's directory object. The following code snippet from the .preload script illustrates how this is done.

Lua environment (_ENV)

Lua has the concept of environments. Environments provide scoping for collections of variables. The wiki function's Lua environment (_ENV) argument provides access to the server's command (request/response) environment. For example, the two predefined objects 'request' and 'response' used in the wiki function (wikifunc) are available via this environment. Lua automatically looks up the referenced data in this table when accessed. For example, the code on line 11 can be rewritten to explicitly access the response object by writing _ENV.response.... We can also store variables in this environment, such as the relpath that is stored on line 7 and 19 and the SQL query response data that is stored on line 8. We must explicitly reference the _ENV table on line 7 and 19 since relpath is a local variable. Data stored in the environment can then be used by the two Lua scripts 'modify.lsp' and 'create.lsp' since these two scripts receive the same request/response environment when we delegate the response on line 9 and 20.

Path component and 'relpath'

The URL's path component is base-path + relpath. The base path is the location where the directory node is installed in the virtual file system. The relpath is whatever is left of the path component after stripping off the base path.

URL mapping
   1   local wikidir=ba.create.dir() -- No 'name' makes dir a sibling when inserted
   2   wikidir:setfunc(wikifunc)
   3   dir:insert(wikidir,true)

Child objects with no name behave as if it is a sibling to the parent directory. This is a feature that works great with our wiki engine since it allows resources to be initially searched for in the application's directory and if not found, searched for in the wiki directory. The 'wikifunc' directory service function is called as soon as the virtual file system delegates the response to the wiki directory object.

Support Pages

Hidden Resources

Files and directories that start with a dot such as .preload and .edit/ are hidden and cannot be visited by entering the URL to these resources. Resources that start with a dot are internal resources and can only be accessed internally by using methods such as response:forward() and response:include().

The wiki engine in the .preload script includes a few supporting LSP pages:

  • index.lsp creates an index by listing all pages in the SQL wiki table. The dynamically created index includes links to all pages in the wiki.
  • .edit/create.lsp is used by the wiki directory service function when a page does not exist. The wiki directory service function forwards the request to this page when a user enters the URL to a non existing wiki page. The LSP file enables the user to create a new wiki page for the provided URL.
  • .edit/modify.lsp is used by the wiki directory service function when the user wants to modify an existing wiki page. The LSP file enables the user to edit any of the existing wiki pages.

Download Wiki Engine

Content Management System

We provide ready to use dashboard app templates. These templates store the content as files and do not use a database, but you can use what you have learned in this tutorial and change the backend for any of the templates. You will in effect create a compact and efficient Content Management System (CMS) if you change the server side from using a file system to using a database.

Posted in Tutorials