• Introduction
• Using the Code
• How it works
• More Information

Introduction

This channel illustrates the use of the dbs.api to programmatically manipulate DBS schema, it shows how to create schemas in Lua without referring to any database directly.

This API is also illustrated in the import database schema channel, which uses the module to interrogate an existing database and create DBS schema objects from the existing database tables.

Why is it useful to manipulate DBS schemas programatically?

My requirement was to make a better web adapter for talking to a salesforce.com instance we have (see Salesforce.com Mk II adapter). Salesforce.com has a convenient web API call which describes all the fields of objects that one accesses in the salesforce.com application. By iterating through these field definitions it was possible to programmatically create database schema objects based off those field definitions.

Database objects in translator offer a nicer experience with the quality of the annotations and intellisense given than just pure JSON objects. This is where this module comes into it’s own.

If you have any questions please contact us at support@interfaceware.com.

Using the Code [top]

• Import the Manipulate Table Schema channel from the Builtin: Iguana Tools repository
• Experiment with the code to find out how it works
• Then add the module(s) to your Translator project
• Copy the require statement from the channel and add it at the top of your script
  Note: This module uses require to return a single function and introduces some global symbols into the dbs table

• Adapt the code to your requirements.
• Interactive scripting help is included for this module

This is the github code for the main module:

How it works [top]

The module works by building up a Lua table representation of the DBS schema which is then converted into a DBS schema when you invoke the :dbs() method. Like so:

local Schema = NewSchema()
-- Create a 'Contact' table.
local T = Schema:table{name="Contact"}
T:addColumn{name="id",        type=dbs.integer,key=true}
T:addColumn{name="FirstName", type=dbs.string}
T:addColumn{name="LastName" , type=dbs.string}
T:addColumn{name="Title"    , type=dbs.string}
local Dbs = Schema:dbs()

This gives us a DBS schema in the Dbs variable like this:

create table [Contact](
   [id] integer,
   [FirstName] string,
   [LastName] string,
   [Title] string,
   key([id])
);

From here we can take this DBS schema and compile it and make an DBS cache object that we can instantiate a group of tables – in this case containing the Contact table, here is a screenshot showing the calls we make:

If we were to browse the D object returned from the tables() method we would see something like this:

The objects which are generated from the DBS schema give us really nice annotations, which is the value of using these objects versus just using plain Lua tables:

To show applicability of the code to working with web services, the channel shows some example code which converts the tables into JSON data which could be passed by HTTP to a web service API.  You can see the code in github here.The format of the JSON is extremely arbitrary – in this case I separated the column names from the row data:

One could change this to fit the requirements of talking to a real API.

More information [top]

• Source code for the main module on github
• Source code for the dbs.api.lua module on github
• API documentation for dbs.init, db.connect and db.merge
• The import database schema channel uses the module to create a DBS schema from a database.