Thursday, April 11, 2013

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.

Thursday, April 4, 2013

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.