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

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

dir:setfunc(wikifunc)

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.

   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.

Discover More:

Whether you are a maker, a startup, or a large business, we've got you covered. Please send us an email if you have any questions or if you are unsure on what product to select. We are here to help you find the best solution, and we'd really like to help you with your hardware/software project challenges.


OPC-UA

OPC-UA Client & Server

An easy to use OPC UA stack that enables bridging of OPC-UA enabled industrial products with cloud services, IT, and HTML5 user interfaces.

Edge Controller

Edge Controller

Use our user programmable Edge-Controller as a tool to accelerate development of the next generation industrial edge products and to facilitate rapid IoT and IIoT development.

On-Premises IoT

On-Premises IoT Platform

Learn how to use the Barracuda App Server as your On-Premises IoT Foundation.

Embedded Web Server

Barracuda Embedded Web Server

The compact Web Server C library is included in the Barracuda App Server protocol suite but can also be used standalone.

WebSocket Server

Microcontroller Friendly

The tiny Minnow Server enables modern web server user interfaces to be used as the graphical front end for tiny microcontrollers. Make sure to check out the reference design and the Minnow Server design guide.

WebDAV Server

Network File System

Why use FTP when you can use your device as a secure network drive.

HTTP Client

Secure HTTP Client Library

PikeHTTP is a compact and secure HTTP client C library that greatly simplifies the design of HTTP/REST style apps in C or C++.

WebSocket Client

Microcontroller Friendly

The embedded WebSocket C library lets developers design tiny and secure IoT applications based on the WebSocket protocol.

SMTP Client

Secure Embedded SMTP Library

Send alarms and other notifications from any microcontroller powered product.

Crypto Library

RayCrypto C Library

The RayCrypto engine is an extremely small and fast embedded crypto library designed specifically for embedded resource-constrained devices.

Embedded PKI Service

Automatic SSL Certificate Management for Devices

Real Time Logic's SharkTrust™ service is an automatic Public Key Infrastructure (PKI) solution for products containing an Embedded Web Server.

Modbus

Modbus TCP client

The Modbus client enables bridging of Modbus enabled industrial products with modern IoT devices and HTML5 powered HMIs.

Posted in Tutorials