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.
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:
This example code below has been tested with Sqlite version 3.6.16 on Ubuntu Linux x86.
Create the DatabaseLets 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 DataAfter 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 InterfaceLet's say, you need to do the following operations with the birthday data:
-- 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.
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.
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:
First download the package header file to the 'inc' directory which stores the include files for your app.
Next download the shared library to the 'lib' directory which stores the library files for 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 <<" " < |
