Embedded databases in ESP projects

[remi_astier]

In software development, the easier it is to test, the better the code!

And the ESP studio is nice development framework for developing and testing projects with the local cluster. Projects often use input / output database adapters, however, sometimes there are constraints around the use of those databases which can make testing more complicated.

This post explains how in less than two minutes you can create a lightweight database that will be automatically started and stopped along your ESP projects. The database engine is SQL Anywhere, which is already installed :smile: If your SQL code is ANSI, using SQL Anywhere instead of a bigger footprint database should probably be transparent.

This tutorial is made with the ESP Studio SPS10 but it should work with SPS9, maybe even SPS8.

It consists of 5 steps:

1. Customize the attributes of your testing database
2. Initialize it and create the ODBC alias
3. Optionally, configure Sybase Central for an even easier database development experience
4. Create a table and add rows
5. Use the new database table in an ESP project

Open a DOS console and adjust the values based on your preferences :

set dbname=miniDB

set dbpassword=Training1

set dbpath=%STREAMING_HOME%\miniDB

Then you can simply execute the following commands in the same window to create the database, register an ODBC alias, and connect your newly running SQL Anywhere db :

set pathutils=%STREAMING_HOME%\sqla\Bin64

set dbfile=%dbpath%\%dbname%.db

mkdir "%dbpath%"

cd /D "%dbpath%"

%pathutils%\dbinit -z "UTF8BIN" -zn "UCA" -dba DBA,"%dbpassword%" -t "%dbname%.dblog" "%dbname%.db"

%pathutils%\dbdsn -pe -y -cw -w sqla_%dbname% -c "DBF=%dbfile%;UID=DBA;PWD=%dbpassword%;DBN=%dbname%;ASTART=TRUE;ASTOP=TRUE;StartLine=%pathutils%\dbeng16 -c 12M -xd -zr sql -zo %dbpath%\queries.txt -zn 3 -zs 1M"

%pathutils%\dbisql -datasource sqla_%dbname%

That's it, you should have an open connection to the new db. Press F9 to execute a query

This new db is located in the path you've specified  :

The miniDB is configured to log all queries in queries.TXT with 3 rotating log files of 1 MB each.

A user ODBC alias should have been created :

Optionally, you can use Sybase Central if you're not familiar with SQL Anywhere.

Launch it:    %STREAMING_HOME%\sqla\Bin64\scjview

Open tools / Plug-ins

The Plug in Menu should appear, Click on "Register"

Then select the second option, "Register a pluging by specifying a JAR file" and browse.

Paste this to navigate to the appropriate directory : %STREAMING_HOME%\sqla\Java and select the file saplugin.jar

Then click next and finish and the plugin should be loaded :

Then make a right click on SQL Anywhere 16 and select connect

Then choose the ODBC option

Click on "browse" to find your ODBC alias

And then click connect,  you can leave the fields "User ID" and "Password" blanks, the values are defined inside the alias.

You can now use Sybase Central to administer the database and create objects :

For the rest of this tutorial, we'll use the table called reservations to store the bookings of the hotel franchise.

You can get the DDL of any object by selecting it and hitting "ctrl-c"

CREATE TABLE "DBA"."reservations" (

  "hotelid" INTEGER NOT NULL,

  "bookingid" INTEGER NOT NULL DEFAULT AUTOINCREMENT,

  "bookingdt" TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,

  "amount" FLOAT NOT NULL,

  "bookingdtstart" DATE NOT NULL,

  "bookingdtend" DATE NOT NULL,

  PRIMARY KEY ( "bookingid" ASC )

) ;

Execute the following SQL to insert 2000 bookings in 12 hotels, of a length between 1 and 7 nights

delete "DBA"."reservations";

BEGIN

   declare @i int;

   declare @j int;

   set @i=1;

   while @i<2000 LOOP 

      set @j=round(RAND()*60,0);

      insert "DBA"."reservations" (hotelid, amount, bookingdtstart, bookingdtend)

           values (round(RAND()*12,0),round(RAND()*1000, 0), dateadd(dd,@j,current date),dateadd(dd,@j+1+round(RAND()*6, 0),current date) ) ;

      set @i=@i+1;

   END LOOP;

   COMMIT;

END;

The following ESP Project is designed to maintain aggregates, for instance the number of arrivals and departures per hotel and per day.

The CCL code is :

CREATE SCHEMA RESERVATIONS_RCD (

  hotelid INTEGER,

  bookingid INTEGER ,

  bookingdt MsDate,

  amount FLOAT,

  bookingdtstart Bigdatetime,

  bookingdtend Bigdatetime

);

CREATE INPUT STREAM INPUT_RESERVATIONS SCHEMA RESERVATIONS_RCD;

CREATE OUTPUT WINDOW OUT_DAILYARRIVALS

PRIMARY KEY DEDUCED

KEEP ALL as

select res.hotelid,

       res.bookingdtstart,

       count(*) nb_arrivals

from  INPUT_RESERVATIONS res

//remove reservations that ended before yesterday

where res.bookingdtstart > datefloor( 'DAY',now() ) - 1 DAY

group by res.hotelid, res.bookingdtstart ;

ATTACH INPUT ADAPTER DB_RESERVATIONS TYPE db_in

TO INPUT_RESERVATIONS

GROUP RunGroup1

PROPERTIES

  query = 'SELECT hotelid, bookingid, bookingdt, amount, bookingdtstart, bookingdtend FROM reservations where bookingid > ?ESP_COUNTER_VALUE AND bookingdtend >= dateadd(dd,-1,current DATE)' ,

  recordCounterColumn='bookingid',

  recordCounterInitValue='0',

  service = 'sqla_minidb',

  pollperiod = 20 ;

ADAPTER START GROUPS RunGroup1;

Then add the connection to the new database by selecting the Data Services tab and making a right click "Add ODBC service"

Set the following connection properties :

Then finally you can run the project, open the stream view of OUT_DAILYARRIVALS.

If you re execute the loop to insert data, ESP will issue a query to poll only this incremental data and you should see new reservations in the window.

Please let me know if you run into any issue while trying to follow this tutorial.

Remi.