Import Database Schema

Introduction

This channel provides an easy way to create a DBS schema file that includes definitions for all the tables in an existing database. You can use a DBS schema file with the db:merge{} function instead of a VMD file. This removes the dependency on our windows tool Chameleon, which is good news particularly for Linux and Mac users.

This channel also addresses the secondary issue of how to securely store the credentials used to connect to the database to read the table structure. This is resolved by using the encrypt.password module to securely store the database credentials.

This API is also illustrated in the Manipulate Database Schema channel, which uses the module to create schemas in Lua without interrogating a database.

A bit of history…

To use our convenient database db:merge{} function it was necessary to use a VMD “schema” file which is created with the windows tool Chameleon. Chameleon actually has a convenient function “Import tables from database” which makes it easy to import table definitions from an existing database into a VMD file.

In Iguana 5 we introduced a new equivalent schema format – the DBS format. The nice thing about the DBS format is very amenable to being edited and read directly. It’s good for code history with diffs and all the editing can be done within Iguana without requiring Chameleon.

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

Using the Code [top]

This channel is is a tool for creating a DBS schema from a database. So you will probably be using the channel and modifying it to point at your own database (rather than copying parts of the code as you would with most repo channels).

Because of this we will step you through how to run and modify the channel, with a few explanatory comments thrown in:

  1. Import the Import Database Schema channel from the Builtin: Iguana-Tools repository.
  2. Run the channel by clicking on it’s channel URL from the Dashboard:
  3. The first time you run the channel you should see something like this:
    Note: The error message may be different (if you already saved database credentials).
  4. Stop the channel.
  5. Next we need to specify our database credentials and avoid saving them (particularly the password) in the Lua script.
  6. Open the translator instance in the editor and edit the database connection credentials to point at one of your own databases:

    See the commented out lines which set the values? This channel is making use of the encrypt.password module to store the database credentials.
  7. Uncomment the lines and edit them to put in your own credentials. For example let’s say you had a local SQLite database called xero then the code would look like this:
    Note: The user (appuser) and password (apppassword) information are blank because SQLite does not use them.
  8. Click on the annotation for Def on line 60 or run the channel to see a DBS definition of the generated schema for the database:
    Note: This is the xero schema yours will match the database you specified.

    create table [Invoice](
 [InvoiceId] string,
 [InvoiceNumber] string,
 [ContactId] string,
 [InvoiceDate] integer,
 [DueDate] integer,
 [Currency] string,
 [CurrencyRate] double,
 [Total] double,
 [AmountDue] double,
 [Status] string,
 key([InvoiceId])
);

create table [InvoiceLineItem](
 [LineItemId] string,
 [InvoiceId] string,
 [SalesRep] string,
 [AccountCode] string,
 [Description] string,
 [TaxType] string,
 [UnitAmount] double,
 [Quantity] double,
 [Tax] double,
 [Product] string,
 [CommissionCategory] string,
 key([LineItemId])
);

create table [Contact](
 [ContactId] string,
 [Name] string,
 key([ContactId])
);

create table [Account](
 [AccountId] string,
 [AccountCode] string,
 [AccountDescription] string,
 key([AccountId])
);
  9. The best way to use this DBS definition is to either put it into a DBS file in the other folder, at which point you can load it using: 
    local D = dbs.init{filename="myschema.dbs"} -- Load other/myschema.dbs
local S = D:tables() -- Now S has the schema
-- Populate S
-- S.Patient[1].Name = "Fred"
DB:merge{data=S}

This is the github code for the main module:

How it works [top]

For each of the supported database varieties the code makes use of database specific queries to find out:

  1. All the tables defined.
  2. The column names in those tables.
  3. It maps the column types to:
    • string
    • integer
    • double
    • date/time
  4. It figures out which columns are used as primary keys.

If you look at the code you can see how supported is implemented for each database type. As a result if you are dealing with another database type that is not supported you should be able to consult the documentation for that database and add support for it – if you need help please feel free to reach out to our support.

There might be instances where there are database types we have not mapped – it should be straightforward to add support for them.

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

More information [top]

Tagged: