TDBO - Tcl DataBase Object

TDBO is a Tcl package, named tdbo, that provides an object oriented access to records (or tuples) in a table/view of a Relational Database Management Systems. It provides a framework to abstract schema definition of a table/view in a data-access-object class (e.g., Employee, PurchaseItem etc.). At any given point in time, a data-access-object instance maps to a record in a table. Data-access-objects can make use of add, get, save and delete methods to perform CRUD operations on records of a table/view.

TDBO supports connectivity to SQLite, MySQL / MariaDB and PostgreSQL database systems. However, it is designed to plugin support for other database systems easily. Support for other databases will be added in future.

TDBO package can be obtained from GitHub: https://github.com/nagarajanchinnasamy/tdbo. If you would like to contribute by adding support to a new database, please send in your pull request.

Dependency

TDBO is based on IncrTcl, an object oriented extension of Tcl. TDBO depends on installation of following Tcl packages:

• Tcl 8.5 or later
• logger (part of Tcllib)
• IncrTcl
• SQLite 3 and sqlite3 Tcl package (for SQLite database connectivity)
• MySQL / MariaDB and mysqltcl (for MySQL / MariaDB database connectivity)
• PostgreSQL and Pgtcl package (for PostgreSQL database connectivity)

Getting Started

Simple steps to use this package are:

1. Load tdbo package using: package require tdbo
2. Define your application object by inheriting tdbo::DBObject

   # Employee Definition
   
   itcl::class Employee {
       inherit tdbo::DBObject
   
       # db argument is an instance of tdbo::SQLite class
       constructor {db args} {tdbo::DBObject::constructor $db} {
           configure {*}$args
       }
   
       # Implementing this proc is mandatory to return
       # name of the table/view.
       proc schema_name {} {
           return "employeeTable"
       }
   
       # public variables correspond to fields in the database table
       public variable id
       public variable name
       public variable rollno
   
       # define primary key, unique and sequence/autoincrement fields
       protected method _define_primarykey {} {
           define_primarykey id  
       }
   
       protected method _define_autoincrement {} {
           define_autoincrement id
       }
       protected method _define_unique {} {
           define_unique {rollno}
       }
   }
   

3. Now in your main application, instantiate a database connectivity implementation:

   set db [tdbo::SQLite #auto -location mytest/test.db]
   $db open
   

4. Create instances of Employee and start using it transparently:

   Employee emp $db -name "new employee" -rollno "MK12345" 
   
   # insert the record. After addition the id is automatically populated.
   emp1 add
   
   # modify the record
   emp1 configure -name "new updated employee"
   emp1 save
   
   # delete the record
   emp1 delete 
   
   # query about another employee with rollno "MK67890"
   emp clear
   emp configure -rollno "MK67890"
   emp get
   puts [emp cget]
   

For more details please refer to the documentation in doc folder and examples in demo folder of the package.

The Appenders: Less spoken but powerful aspect of logger package

Logging is one of the fundamental and very important features of an application that helps both developers and maintainers in debugging any issues either during development or in production.

In Tcl, as part of tcllib, we have log and logger packages that support logging facility. A lot of information available on wiki and their reference documentation.

In this article, I would like to highlight one of the less spoken but a powerful aspect of logger package, that is, appender.

An appender is the one that does the actual job of appending the log message to its destination. You can notice that, by default, logger package logs the messages onto standard output / console. This is because, the logger package by default is configured with an appender called "console".

You can change this default behavior of logging messages to  standard output by configuring the logger package with a different appender. To do this configuration you call the proc: 

::logger::utils::applyAppender -appender appenderType  ? options... ?

In addition to "console", there is also an appender provided by logger package is: colorConsole. By invoking applyAppender as:

::logger::utils::applyAppender -appender colorConsole

you can see that your log messages are not just sent to standard output, but they are also formatted with colors.

Now, comes the secret.... an undocumented feature of logger package.... is that there is an another appender available in the package called fileAppend. This appender sends the log messages to a given output channel. You can use this appender to log messages to a file on the file system...something that all applications in the production will need. To configure the logger package with fileAppend appender, you invoke:

set chan [open /log/file/path]
fconfigure $chan -buffering none
::logger::utils::applyAppender -appender fileAppend -outputChannel  $chan

While there are only 3 appenders provided by logger package, you can write your own appenders and make them part of logger package. We will see how to write your own appenders in a different article.

One can use the applyAppender command to configure specific set of services and / or specific set of logger object instances (returned by logger::init command) and / or for specific set of log levels. This makes application's logging facility to be highly configurable with any level of granularity that you want. You can enable logging for only some parts of the application, different parts of the application being able to send messages to different destinations as per the configuration and different parts of the application logging messages at different log levels.

That is the power of logger package in tcllib.

tclIndex files and freeWrap incompatibility...

The auto_mkindex command of Tcl generates an index of all the Tcl command procedures defined in Tcl files, and stores the index information in a file named tclIndex that is suitable for auto_load.

The tclIndex files generated by auto_mkindex command contain relative paths with '.' in them (Tested on of Tcl 8.5.14). An example line from tclIndex: 

set auto_index(::MyNameSpace::mycmd) [list source  [file join $dir . myscript.tcl]]

Applications freeWrap-ed with these tclIndex files fail to load/source files using relative paths. Note the following warning from freeWrap documentation:

You should use the paths to the files as they exist at the time of wrapping. Wrapping takes a "snapshot" of the file path for all wrapped files. Do not use relative paths to refer to wrapped files within the application since relative paths will not be found.

As a workaround, I modify tclIndex (using shell scripts) files to use [file normalize] command around [file join] command. The [file normalize] command  returns a normalized path. A normalized path is an absolute path which has all “../” and “./” removed. Also it is one which is in the “standard” format for the native platform. Hence, the modified tclIndex line looks like: 

set auto_index(::MyNameSpace::mycmd) [list source  [file normalize [file join $dir . myscript.tcl]]]

The shell command I used to automate the modification of tclIndex files is: 

sed -e 's/^set auto_index(.*) \[list source /& \[file normalize /' -e 's/\]$/\]\]/' -i tclIndex

Hope this helps you..... in case if you encounter this problem when using tclIndex files with freeWrap.

Reasons for using tclfcgi

For server side programming of Tcl based web applications, there are many alternatives available.

Initially I used Apache-Rivet. This needs the server side code to be distributed in source form. But, I wanted my code to be protected either as a compiled executable or password protected (at least making it difficult for normal users to see the source code).

Building a Tcl executable that is password protected can be achieved by using freeWrap. Another advantage I get by using freewrap is that it has Tcl interpreter built in. So, there is no need for users to install Tcl on their machine to use my server side code. Freewrap documentation provides instructions on how to build your version of freewrap. Building your own version of freewrap is important as it advised in the freewrap documentation:

Remember, due to freeWrap's ability to mount ZIP files as a subdirectory, a person having the same version of freeWrap (with the same password) can easily read the encrypted files within your application. Therefore, those people interested in securing the files that make up their application should use a copy of freeWrap (built with its own unique password) that no one else has access to.

To run an executable that serves htpp requests from a web server, CGI is an option. Hence, I hosted my application under Apache mod_cgi module. One disadvantage, I found, of this approach was that entire application will get loaded every time to serve every individual request. This is very inefficient and loads the server.

So, I started looking for an option that will facilitate loading the application only once but serve the subsequent requests in a loop without reloading the application again and again. FastCGI provides this option of having a CGI script loaded only once and serve requests in a loop. Using FastCGI also gives an advantage of having your application code being independent of any specific web server platform. So, now my code is ready to be moved to any Web Server / Framework that will support FastCGI.

Hence, for now, I am settled with using tclfcgi for my Tcl FastCGI scripts running under mod_fastcgi module of Apache2 web server.

To summarize, the reasons that lead me to use tclfcgi are:

1. Source code protection
2. No need for installing Tcl at the user site
3. One time application initialization (No reloading of the app for every request)
4. Web server platform independence and portability

Please note that at this point in time, following points are for me to explore futher:

1. I need to explore the mod_fcgid module of Apache2 as an alternative to mod_fastcgi
2. +Donal Fellows and +Steve Landers suggest that SCGI could be an alternative to FCGI.

tclfcgi - Tcl binding to FastCGI - Linux

This creates a Tcl package (Fcgi) that can be used by Tcl based FastCGI scripts. This package is tested on Debian Wheezy (3.2.0-4-686-pae) with Apache Apache/2.2.22 mod_fastcgi and Tcl8.5.14

Download from github:

https://github.com/nagarajanchinnasamy/tclfcgi

Pre-requisite:

sudo apt-get install libfcgi-dev libfcgi0ldbl

Build & Install:

cd c-src
make
sudo make install

Apache Setup:

1. Enable fastcgi module in apache2 using:

 sudo a2enmod fastcgi

2. Place following lines in /etc/apache2/conf.d/httpd.conf

 ScriptAlias /appname/ /my/path/to/fcgi-bin/

 <Directory "/my/path/to/fcgi-bin/">
     SetHandler fastcgi-script
     AllowOverride None
     Options +ExecCGI -MultiViews +SymLinksIfOwnerMatch
     Order allow,deny
     Allow from all
 </Directory>

3. Place your FCGI scripts in /my/path/to/fcgi-bin and set executable permission to the scripts. Example Tcl FCGI script (example.fcg):

do_one_time_app_initialization_here
while {[FCGI_Accept] >= 0 } {

    ::ncgi::parse
    set var1 [::ncgi::value var1]
    set var2 [::ncgi::value var2]

    set result [do_processing $var1 $var2] 
        ::ncgi::header
        puts $result
        ::ncgi::reset
  }

4. Access your FCGI script from browser using:

http://your.com/appname/example.fcg

References:

http://www.fastcgi.com/devkit/doc/fcgi-tcl.htm
http://sourceforge.net/projects/tcl-fastcgi/

reportx Template Format

Overview:

The template defines the structure of a report in tabular format - starting outer most table to rows inside the table to the columns in each row. This definition is a nested/recursive structure that helps you define a cell to contain inner-tables.

Template also facilitates filling dynamic content at run time by having Tcl variable names (e.g. $company_name) embedded into the definition.

Styles (defined using Tcllib's report::defstyle command) that define the border lines, separator lines, header and footer lines can be applied to the outer most table, rows and columns.

Defining Outer Table (-report element):

Grammatically, the template is defined as a Tcl dict. The outermost key must be -report with its value being a dict with -style and -rows elements. The -style element is optional.

E.g.:

-report {
    -style outermost_table_style_optional
    -rows {
        ... Definition of Rows and Columns Go Here...
   }
}

Defining Rows (-rows element):

-rows element contains an optional -style element and individual row elements with their corresponding row number being the key. The -style element is optional and if specified, applies to all the rows defined inside except for the ones that have their own specific -style element.

E.g.:

-report {
    -style outermost_table_style_optional
    -rows {
        -style common_style_for_all_rows_optional
        0 {
            ... Columns Definition of row-0 Goes Here...
        }
       1 {
           -style row_specific_style
           ... Columns Definition of row-1 Goes Here...
        }
        ...
    }
}

Defining Columns (-columns element):

Rows are defined using -columns key. -columns key can have a -style element and columns defined using their corresponding column number as their key. The -style element is optional and if specified, applies to all the columns defined inside except for the ones that have their own specific -style element.

E.g.:


-report {
    -style outermost_table_style_optional
    -rows {
        -style common_style_for_all_rows_optional
        0 {
            -columns {
                -style common_style_for_all_columns_optional
                0 {
                    ... Data Definition of Cell 0,0 Goes Here...
                }
                1 {
                    -style column_specific_style_optional
                    ... Data Definition of Cell 0,1 Goes Here...
                }
                ...
            }
        }
        ...
    }
}

Defining Data (-data element):

Data is defined inside a column using -datastyle (optional) and -data keys. The value of -data key is defined as a Tcl list containing lists (list of lists).

E.g.:


-report {
    -style outermost_table_style_optional
    -rows {
        -style common_style_for_all_rows_optional
        0 {
            -columns {
                -style common_style_for_all_columns_optional
                0 {
                    -style column_specific_style_optional
                    -datastyle style_of_tabular_data_optional
                    -data {

                        {"data_0,0"  "data_0,1"  "data_0,n"}

                        {"data_1,0"  "data_1,1"  "data_1,n"}

                        {"data_m,0"  "data_m,1"  "data_m,n"}

                    }

                }
                ...
            }
        }
        ...
    }
}

Dynamic data can be filled at runtime by having Tcl variable names in the data definition.

E.g.:

-columns {

    1 { -datastyle invoiceHeader

        -data {

            { "${-company_name}" }

            { "${-company_street1}, ${-company_street2}, ${-company_city}-${-company_pin}" }

            { "TIN: ${-company_tin}, Phone: ${-company_phone1} EMail: ${-company_email1}" }

        }

    }

}

For more examples, please look into the demo folder of reportx package.

Encrypting & Accessing SQLite Database From Tcl Scripts

SQLCipher

SQLCipher is an open source extension to SQLite that provides transparent 256-bit AES encryption of database files. It provides a Tcl API interface that enables Tcl SQLite commands to create and access encrypted SQLite database files transparently.

Building SQLCipher

The SQLCipher source code is hosted at github. Issue a 'git clone' command to obtain a local copy of the source code and follow the following instructions. I have tested this on Debian Linux 3.2.0-0.bpo.3-686-pae with Tcl 8.5.8.

The build instructions provided in README file are simple and easy to follow. Just after running configure command using the flags suggested in README, apply the following patch to avoid an undefined symbol error about sqlite3ErrStr (as suggested by one of the posts at sqlcipher users forum):

make sqlite3.h
echo "SQLITE_API const char *sqlite3_sqlite3ErrStr(int);" >> sqlite3.h
echo "const char *sqlite3_sqlite3ErrStr(int err) { return sqlite3ErrStr(err); }" >> src/main.c
sed -i 's/sqlite3ErrStr/sqlite3_sqlite3ErrStr/g' src/tclsqlite.c
make
make install

As I used dynamic linking option. Exported LD_LIBRARY_PATH with the path where new libsqlite3.so is installed.

Creating & Accessing Encrypted DB From Tcl Scripts:

Execute following tcl commands to create encrypted database:

$ tclsh
% package require sqlite3
3.7.14.1
% sqlite3 db /tmp/test2.db
% db eval { PRAGMA key='your key' }
% db eval { create table t1(a,b); insert into t1 values('test1', 'test2'); }
% db eval { select * from t1; }
test1 test2
% db close

To make sure that the database file is encrypted, you can issue the following hexdump command and see that there are no readable strings in the dump:

hexdump -C /tmp/test2.db

Now try to access data without providing the key and confirm that you get an error that file is encrypted or is not a database. Thats what happens when someone without the access to 'you key' tries to access data in the database file.

% sqlite3 db /tmp/test2.db
% db eval { select * from t1; }
file is encrypted or is not a database 

There are other SQLCipher commands like PRAGMA rekey etc. You can read the SQLCipher API documentation for further description.