SQLite Manager Project

written 13 July 2004 being revised May 2006

Google site search

ContentsIntroductionBasicIntermediateAdvancedFuturePolicyInfrastructure

1   Introduction

Rationale, project structure, resources required, prerequisite knowledge

1.1   Rationale

SQLite is an excellent small database, but is basically a raw physical implementaion. It lacks much of the logical information which supports applications which use the data, in particular the way thyat the data should be displayed in various contexts and media, formatting and presentation of the value of each field and other variables and methods which we recognise as object methods and values. The SQLite manager addresses these issues.
  1. The embedding of logical data structure in the database via three tables (dbdomain, dbtable, dbfield) with option fields which can integrate with Tk (and HTML, XML) options databases. In particular the dbdomain is used to define objects in the loosest, most flexible way possible.
  2. Dataentry, access and display frontends which are intuitive and user friendly.
  3. Automated management of data types that require special handling such as dates/times, images and encypted data.
  4. Overall database management, creation, backup, access to logical structure.

1.2   Project Structure

The database manager, creation and backup functions are dealt with first. In particular creation, because the rest of the package depends on this. This includes the logical definintion. Then the applications are written to use this structure.

1.3   Resources Required

This project uses SQLite and Tcl/Tk.

1.4   Prerequisite Knowledge

A solid grounding in basic SQL and Tcl/Tk is required. The iwidgets Tk megawidget package is also used.

2   The Logical Database

There are three tables in the logical definition. All opf the names are prefixed with "db" partly because the words used tend to be reserved within SQL in any case, and also to clearly identify this core subset of the database definition. All the tables and fields mentioned above are used and displayed by the manager and any other applications and themselves have attributes associated with them. Therefore there are domains for dbtable, dbkey, dbfield, dbposition, dbdomain, dbtype and dbattrs.

This design requires some review.

It can be seen that all the detail working bits have been pushed into the dbattr fields in the dbtable and the dbdomain tables. These act as sparse arrays rather than as dense database records. There are several reasons for this approach.

  1. It is the cleanest. It avoids the complexity of any hybrid approach of having some fixed fields such as 'title' and 'length' for all fields but not for 'format'.

    The attributes fields are a kind of sparse database. Not knowing what fields are going to be required, and that different fields have different field attributes, a strict relational database approach to holding these attribute values cannot be maintained. At some point the attribute field is required. But what should be held as attribute options and what should have its own field ?

    Consider that we make a decision in this regard and create lots of databases, then see that we wish to change this adding adding a field or removing a field from the dbfield record. Such a change is impossible to effect. For this reason everything has been pushed into the dbattrs field.

  2. It allows for the one attr tag to be overloaded amongst different data types. The width attribute typically means different things for different domains. If it were a field in the dbfield table it would imply that the same kind of thing was being referred to in every instance of the width field. An image width is different to a number width, typically using different units to start with.
  3. The handling of the attr is conveniently managed with Tcl by the use of array lists.
  4. This meshes nicely with the Tk options database which is also a sparse array list format. Particularly with the handling of default values.
  5. In the above design only the type attribute has been given its own field in the domain table.

    What is the difference between domain and type ? How are they used differently ?

    The reason for this is that when it comes to using these attributes in an application, these attributes get applied along with their sibling attributes in the creation of a widget. for instance: entry $w.myfield -text "My Field" -width 20 -validate focusout -validatecommand editmyfield -variable myfield This example shows the -text and -width options being invoked to create a widget. But at the same time some other attributes are also invoked which may not be present for other field types. By keeping all attributes together in the one place creating the above statement is simplified simply by matching the attributes in the domain record against the attributes which are valid for the entry widget (which can be got from the Tk options database). Having seperate label and width fields in the domain definition would only hinder this process.

    So why is type not also in the attribute field?

    Type is an odd attribute. It does not get invoked in the context of other attributes. It is used mainly to invoke special processes for that object type. Such as comparison, instead of the normal character comparison, procedures for displaying objects such as images, and for interpreting the data held in the field. In the case of HTML type the data is used in an HTML application context. In the case of 'command' type the field contents are evaluated. In other words, type is a functional attribute, whereas most other attributes are descriptive. It is its usage which makes sense for it to have a special field in the dbfield and dbdomain tables.

    The default type is char.

    In this implementation, domains do not inherit attributes from other domains. This kind of association needs to be managed by hand at this stage. This is not a great management problem as a typical database may have 20 to 40 fields and probably somewhat less domain types as the char domain gets used as a default for many fields.

    The following SQL code gives us the primitive structure from which to progress.

     -- database system tables BEGIN
     -- table and key are reserved words :-(
    create table dbtable (
        dbtable char,
        dbkey integer,
        primary key ( dbtable)
    );
    insert into dbtable ( dbtable, dbkey ) values ( 'dbtable', 1 );
    insert into dbtable ( dbtable, dbkey ) values ( 'dbfield', 1 );
    insert into dbtable ( dbtable, dbkey ) values ( 'dbdomain', 1 );
     
    
     -- dbfield is really "table field"
    create table dbfield (
        dbfield char,
        dbposition integer,
        dbdomain char,
        dbattrs char,
      primary key ( dbfield )
    );
    
    delete from dbfield where dbfield glob 'dbtable *';
    insert into dbfield values ('dbtable dbtable', 0, 'char', '-width 20 -text Table -widget entry' );
    insert into dbfield values ('dbtable dbkey',   1, 'integer', '-width 2 -text Keys -widget entry' );
    
    delete from dbfield where dbfield glob 'dbfield *';
    insert into dbfield values ('dbfield dbfield',       0, 'char', '-width 20 -text Field -widget entry' );
    insert into dbfield values ('dbfield dbposition', 1, 'integer', '-width 2 -text Position -widget entry' );
    insert into dbfield values ('dbfield dbdomain',  2, 'char', '-width 20 -text Domain -widget entry' );
    insert into dbfield values ('dbfield dbattrs',      3, 'char', '-width 30 -text Attrs -widget entry' );
    
    
    create table dbdomain (
        dbdomain char(20),
        dbtype char(20),
        dbattrs char(40),
      primary key (dbdomain)
    );
    
    delete from dbfield where dbfield glob 'dbdomain *';
    insert into dbfield values ('dbdomain dbdomain', 0, 'dbdomain', '-width 20 -label Domain -labeltext Domain' );
    insert into dbfield values ('dbdomain dbtype',     1, 'char', '-width 20 -label Type -labeltext Type' );
    insert into dbfield values ('dbdomain dbattrs',     2, 'char', '-width 30 -label Attrs -labeltext Attrs' );
    
     -- base dbdomain types are 'char', 'num'
    insert into dbdomain values ( '', 'char', '-widget entry -width 12' );
    insert into dbdomain values ( 'char', 'char',  '-widget entry -width 30' );
    insert into dbdomain values ( 'num', 'num',  '-widget entry -width 8' );
    
     -- system types are dbdomain, dbattrs, dbtable, dbkey, dbfield, dbposition, dbtype
    insert into dbdomain values ( 'dbdomain', 'char',  '-widget entry -width 20 -text Domain -labeltext Domain' );
    insert into dbdomain values ( 'dbtable',     'char',  '-widget entry -width 20 -text Table -labeltext Table' );
    insert into dbdomain values ( 'dbkey',       'num',  '-widget entry -width 3' );
    insert into dbdomain values ( 'dbfield',      'char',  '-widget entry -width 20 -text Field -labeltext Field' );
    insert into dbdomain values ( 'dbposition', 'num',  '-widget entry -width 3 -text Position -labeltext Position' );
    insert into dbdomain values ( 'dbtype',      'char',  '-widget entry -width 10' );
    
     -- END of base table definitions
     -- in theory the dbdomain table can be constructed from dbtable and dbfield
     -- the contents of these 3 tables can now be used to create any database tables
    
     -- the code to do this has not been written yet
    
    This is pretty much pared to the bone. char and integer domains are reused. As this is a meta-definition (a definition of a definition) a lot of selfreferencing can be seen. This can be confusing at first and some care needs to be taken to understand the structure that is described.

    3   Creating a Database

    SQLite databases are known simply by their filename. The Manager maintains a list of filenames which it manages.

    4   Encryption

    You do not use SQL databases for long before you want to use encryption to hide sensitive data such as creditcard details. What is the best way of handling this?

    Take the case of an order, with delivery details, creditcard payments details and a list of items ordered. Only the creditcard payment details need to be encrypted. If the customer is regular and has an account then the credit card details may already be on file. Delivery details may change from one order to the next. But if the customer is always casual then it is tempting to place the delivery details and the creditcard details together in the one record. Th typically go in a seperate table because there is a one-to-many relatyionship with the order.

    Encryption can occur at the database level, the whole database can be encrypted and a readable copy made only for the time required to access the database for a particular query which presumably requires a password signon.

    The next useful level of encryption for SQLite is at the field level, as tables are not kept as seperate physical entities in SQLite. Encryption occurs before an INSERT or UPDATE and decryptuion occurs immediately on the result of the SELECT. Encryption cannot usefully be done at the record level because you need to be able to see the indexes to retreive records. If the whole record is encrypted it cannot be indexed.

    The the case of creditcard details there are several fields to be encrypted. We have a choice of encrypting each field seperately or of lumping them altogether. Encrypting each field seperately is somewhat clumsy, as these feilds tend to be used all together as an object. So in a sense it is the creditcard object which is being encrypted. Since the credit card is seen as a seperate object it makes sense for it to be placed in its own table indexed by a visable field, the orderid. Everything else in the record is held in an encrypted array list which is unpacked to be read.

    ©2000 - 2006 WEBSCOOL This page last updated 11 May 2006. All rights reserved - including copying or distribution of any portion of this document in any form or on any medium without authorisation. For more regarding the copyright.