Metatranz StepSqlite alpha Metatranz StepSqlite Home Metatranz StepSqlite Home

StepSqlite Tutorial

StepSqlite is a unique multi-target PL/SQL compiler for Sqlite. It can be used to compile your database interfaces written in PL/SQL for use on several target operating systems, architectures and language APIs.

Currently StepSqlite supports Linux and iPhone on x86, ARM using Sqlite native C++ API - this list is being continuously expanded.

StepSqlite is being developed to support compiling Pl/SQL code to several target platforms (Linux, iPhone, Mac, Windows), architectures (x86, ARM) and languages/APIs (Sqlite native C++ API, Python PySqlite, Android SDK/NDK, JDBC, etc).

The example below shows the steps required to write, compile and use the DB interface written in PL/SQL for use in a C++ application running on Ubuntu x86 Linux. The steps described are as follows:

  1. Prepare the Database
  2. Write the Database Interface
  3. Compile the Database Interface Using StepSqlite
  4. Use the Database Interface in Your App


This example code below has been tested with Sqlite version 3.6.16 on Ubuntu Linux x86.

Prepare the Database

Create the Database

Lets create a DB interface for an application that tracks and displays alerts for the birthdays of your friends. To store the birthday data, you create a Sqlite database called friends.db with following tables:


 $ sqlite3 friends.db "CREATE TABLE friends(fname varchar, lname varchar, bday date, attending BOOLEAN);"

Insert Data

After table friends has been created, insert some data as follows:

     $ sqlite3 friends.db  "insert into friends (fname,lname,bday,attending) values ('Mark', 'Z', '1984-05-14',0);"
     $ sqlite3 friends.db  "insert into friends (fname,lname,bday,attending) values ('Larry', 'P', '1973-03-26',0);"
     $ sqlite3 friends.db  "insert into friends (fname,lname,bday,attending) values ('Sergey', 'B', '1973-08-21',0);"
     $ sqlite3 friends.db  "insert into friends (fname,lname,bday,attending) values ('Bill', 'G', '1955-10-28',0);"
     $ sqlite3 friends.db  "insert into friends (fname,lname,bday,attending) values ('Steve', 'J', '1955-02-24',0);"

Write the Database Interface

Let's say, you need to do the following operations with the birthday data:

  • Display birthday falling in next 'x' days
  • Mark a birthday for attending
  • Get a count of upcoming birthdays that you plan to attend this year
To do the above, you write the following DB interface:


  -- The 'create table' below tells the compiler about all the tables you are 
  -- referring to in the package body code below.
  -- It does not actually create a table for you. 
  -- You must do that separately as we have done above in this example.
  CREATE TABLE friends(fname varchar, lname varchar, bday date, attending BOOLEAN);

  PACKAGE BODY MyDBinterface IS
  TYPE RECTYPE IS RECORD (fname varchar, lname varchar, bday date, attending BOOLEAN, age integer);
  TYPE bdaycurtype IS REF CURSOR RETURN RECTYPE;

  -- This procedure lists birthdays falling in next several days
  PROCEDURE displayUpcomingBirthdays(daysToBirthday integer) IS
  BEGIN
   dbms_output.put_line('List of friends with birthdays in next ' || daysToBirthday || ' days.');
   dbms_output.put_line('=====================================================================');
   FOR friend IN (SELECT fname, lname, bday
                  FROM friends
                  WHERE (
                          to_date(
                                   to_char(sysdate(),'YYYY')||'-'
                                   ||to_char(bday,'MM')||'-'
                                   ||to_char(bday,'DD')
                                  ) 
                          > sysdate()
                         AND to_date(
                                   to_char(sysdate(),'YYYY')||'-'
                                   ||to_char(bday,'MM')||'-'
                                   ||to_char(bday,'DD')
                                  ) 
                          < sysdate() + daysToBirthday
                        )
                  )
   LOOP
     dbms_output.put_line(friend.fname || ' '|| friend.lname ||' has birthday on:' || to_char(sysdate(),'YYYY')||'-'
                                   ||to_char(friend.bday,'MM')||'-'
                                   ||to_char(friend.bday,'DD'));
   END LOOP;
   dbms_output.put_line('');
  END;


  -- This function marks a specific friends birthday in current calendar year for attending the celebration
  -- It returns true if the operation was successful ,false otherwise.
  FUNCTION attendBirthday(first_name varchar, last_name varchar, birthday date) RETURN BOOLEAN IS
  count INTEGER := 0;
  BEGIN
       SELECT count(*) 
       INTO count 
       FROM Friends 
       WHERE (
               to_date(
                        to_char(sysdate(),'YYYY')||'-'
                        ||to_char(bday,'MM')||'-'
                        ||to_char(bday,'DD')
                      ) 
               = to_date(
                         to_char(sysdate(),'YYYY')||'-'
                         ||to_char(birthday,'MM')||'-'
                         ||to_char(birthday,'DD')
                        )
               AND to_date(
                        to_char(sysdate(),'YYYY')||'-'
                        ||to_char(bday,'MM')||'-'
                        ||to_char(bday,'DD')
                      ) > sysdate() 
               AND lower(fname) = lower(first_name) 
               AND lower(lname) = lower(last_name)
             );
       IF count = 1 THEN
           UPDATE friends 
           SET attending= true 
           WHERE (
                   to_date(
                           to_char(sysdate(),'YYYY')||'-'
                           ||to_char(bday,'MM')||'-'
                           ||to_char(bday,'DD')
                          ) 
                   = to_date(
                             to_char(sysdate(),'YYYY')||'-'
                             ||to_char(birthday,'MM')||'-'
                             ||to_char(birthday,'DD')
                            ) 
               AND lower(fname) = lower(first_name) 
               AND lower(lname) = lower(last_name)
                 );
           dbms_output.put_line('Make arrangements to attend birthday celebrations for: '|| first_name || ' '|| last_name || ' on ' || to_char(sysdate(),'YYYY')||'-'
                             ||to_char(birthday,'MM')||'-'
                             ||to_char(birthday,'DD'));
           RETURN true;
       ELSE
           dbms_output.put_line('Error: Birthday already past or Not found for given input: '|| first_name || ' '|| last_name || ' ' || to_char(sysdate(),'YYYY')||'-'
                             ||to_char(birthday,'MM')||'-'
                             ||to_char(birthday,'DD'));
           RETURN false;
       END IF;
  END;

  -- This function gets a count of birthdays before end of the year that are marked for attending
  FUNCTION countAttendingBirthdays() RETURN INTEGER IS
  count INTEGER := 0;
  BEGIN
       SELECT count(*) 
       INTO count 
       FROM friends 
       WHERE (
               to_date(
                        to_char(sysdate(),'YYYY')||'-'
                        ||to_char(bday,'MM')||'-'
                        ||to_char(bday,'DD')
                      ) 
               >= sysdate()
               AND attending = TRUE
             );
       RETURN count;
  END;

  --This procedure has a CURSOR OUT parameter which is used in the app to fetch records.
  PROCEDURE getDetails(typeOfDetails NUMBER, bdays OUT bdaycurtype) IS
  REC RECTYPE;
  BEGIN
    IF typeOfDetails = 1 THEN
        -- Return all attending birthdays
        OPEN bdays FOR select fname, lname, to_char(sysdate(),'YYYY')||'-' ||to_char(bday,'MM')||'-' ||to_char(bday,'DD') birthday , attending, (sysdate()-bday)/365 age from friends where attending = TRUE;

    ELSE

        -- Return all non-attending birthdays
        OPEN bdays FOR select fname, lname, to_char(sysdate(),'YYYY')||'-' ||to_char(bday,'MM')||'-' ||to_char(bday,'DD') birthday , attending, (sysdate()-bday)/365 age from friends where attending is null OR attending = FALSE;
    END IF;
  END;

 END;

The above code shows how easy it is to write complex database logic using StepSqlite. It has stored procedures, date calculations, for-loop over select cursor (implicit cursor), local variables used directly in SQL, strongly typed cursor being returned to your app as a parameter in a stored procedure and moreover all these done in a readable PL/SQL syntax. There are several more benefits of using StepSqlite 'compiler' as compared to other 'wrapper' interfaces to Sqlite. You can check out these unique benefits of StepSqlite here.

Compile the Database Interface Using StepSqlite

Now using StepSqlite, compile the above PL/SQL package body to a C++ library for Sqlite. First open any web browser and access the StepSqlite web interface at www.metatranz.com/stepsqlite

This is the home page of StepSqlite. You will need an StepSqlite alpha access code to compile the database package. If you have the alpha code, click on Sign in (Alpha Trial) link on the home page and enter the access code.

Upon successful validation of alpha trial access code the StepSqlite compiler page displays as shown below:

Enter the Pl/SQL code for your database interface package body here. Only enter the package body code. Compiling Pl/SQL package headers is not currently supported. This affects no functionality as all the package functions, procedures and variables are made accessible to the users of the package.

Note that the 'Create table' statements before the 'Create Package' statement serve to describe your table structure to compiler. They are not executed as part of the compiled package, so you must create tables in the Sqlite database yourself separately.

Enter your Pl/SQL package body here

Choose the target platform for your app. Currently, StepSqlite has support for Linux x86 and iPhone. Choose Linux x86 target for this example. Once the package code is entered and target chosen, hit Compile.

If the package is compiled successfully, the download page for your generated files is displayed:

Download the generated files

First download the package header file to the 'inc' directory which stores the include files for your app.

Download package header file to include path

Next download the shared library to the 'lib' directory which stores the library files for your app.

Download the compiled shared library

Use the Database Interface in Your App

Here we write a simple command line C++ program which uses the StepSqlite generated header and library as its DB interface:

Write Your App


#include "package.hpp"
int main(int argc, char **argv)
{
    sqlite3 *pDb; 
    int rc;
    MYDBINTERFACE * pMyDbInterface = NULL;

    if( argc!=2 )
    {
        cerr << "Usage: " << argv[0] << " DATABASE " << endl;
        cerr << "ArgC Count is "<<  argc << endl;
        exit(1);
    }
    
    try
    {
        //Init library
        ss_world l_ss_world;
        l_ss_world.getInstance(argv[1], ERROR);

        // Init Package
        pMyDbInterface = MYDBINTERFACE::getInstance();
        if(! pMyDbInterface )
        {
            cerr <<  "Error creating DB Interfcae. Exiting..."<< endl;
            exit(1);
        }

        // Call the procedure in your DB interface to show birthdays falling in next 30 days. 
        pMyDbInterface->DISPLAYUPCOMINGBIRTHDAYS(30);


        // Call the function in your DB interface to mark a birthday for attending the celebration

        string lname = "B";
        string fname = "Sergey";
        string bday = "1973-08-21";
        if (pMyDbInterface->ATTENDBIRTHDAY(fname, lname, bday))
            cout << "Scheduled to attend birthday for " << fname << " "<< lname << endl ;
        else
            cout << "Not scheduled to attend birthday for " << fname << " "<< lname  << endl;

        // Call the function in your DB interface to get a count of birthdays  
        // before end of the year that are marked for attending
        int count = pMyDbInterface->COUNTATTENDINGBIRTHDAYS();
        cout << endl;
        cout << "Total Birthdays to attend before year End" << endl;
        cout << "-----------------------------------------" << endl;
        cout << "There are " << count << " more  birthdays this year to attend." << endl;
        cout << endl;

        MYDBINTERFACE::BDAYCURTYPE details;
        int ATTENDING_TYPE=1;
        int NON_ATTENDING_TYPE=2;
        
        //Get the birthdays you are scheduled to attend.
        pMyDbInterface->GETDETAILS(ATTENDING_TYPE, details);
        cout << "Details of Birthdays to attend before year End" << endl;
        cout << "----------------------------------------------" << endl;
        details.fetch();
        while(details.isFound())
        {
            cout << details.FNAME <<" " <GETDETAILS(NON_ATTENDING_TYPE, details);
        cout << endl;
        cout << "Details of Birthdays Missed " << endl;
        cout << "--------------------------------------------" << endl;
        details.fetch();
        while(details.isFound())
        {
            cout << details.FNAME <<" " <

This app shows the essentials of using the DB interface in your application.

To use the DB Interface, first initialize the StepSqlite library by creating an object of class ss_world and calling its getInstance() method.


  ss_world l_ss_world;
  l_ss_world.getInstance(argv[1], TRACE);

There are four getInstance() methods available in class ss_world. The first argument to getInstance() takes either the path to your sqlite database file or a pointer to your database connection. If you use a getInstance() method that takes a path to the database file, StepSqlite library will take care of opening and closing the database for you.

 

           getInstance(sqlite3* pDb);
           getInstance(sqlite3* pDb, LOG_LEVEL logLevel);

           getInstance(string dbname);
           getInstance(string dbname, LOG_LEVEL logLevel);

    

The optional second argument to getInstance() sets the logging level for the code in DB interface. In the code above it is set to ERROR. You can set it to any of the levels listed below. Also, you can change it at runtime by calling pMyDbInterface->setLogLevel(logLevel)

 

            NONE  //Min setting -disables all logs
            FATAL
            ERROR
            WARN
            INFO
            DEBUG 
            TRACE //Max setting - enables all logs

    

To use the DB Interface, first create an instance of the package class in your application using getInstance() method. If you have more than one PL/SQL packages in the DB interface, each package will have its own class; in order to call methods in a particular package from your app, you should first getInstance() for its package class. The getInstance() method ensures that only one instance of a package is ever created in your application. Use the following getInstance() method to get a package class instance:

 

           getInstance();

    

Compile and Link Your App

Make sure that the StepSqlite generated libpackage.so and libsqlite3.so is in your library path. Also make sure that the StepSqlite generated header file package.hpp is in your include path. Then compile and link your app as follows.


     g++ -g -L/path/to/mylibs  -I/path/to/myincludes -I. -o myapp.out myapp.cpp -lpackage -lsqlite3 

Run Your App


    export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/path/to/mylibs
    ./myapp.out /path/to/databases/friends.db  

You should get output similar to below:


List of friends with birthdays in next 30 days.
=====================================================================
Sergey B has birthday on:2009-08-21

Make arrangements to attend birthday celebrations for: Sergey B on 2009-08-21
Scheduled to attend birthday for Sergey B

Total Birthdays to attend before year End
-----------------------------------------
There are 1 more  birthdays this year to attend.

Details of Birthdays to attend before year End
----------------------------------------------
Sergey B is getting 36 years old on 2009-08-21

Details of Birthdays Missed 
--------------------------------------------
Mark Z on 2009-05-14
Larry P on 2009-03-26