Contents

Introduction

Basic

Intermediate

Advanced

Future

Policy

Infrastructure

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.

• dbtable - this has fields
  1. dbtable - the name of the table. This is the key field of the dbtable table.
  2. dbkey - the length of the logical key for this table. Note that this is not a physical key and so it can be changed as convenient for runtime purposes. It applies to the first ${dbkey}s fields of the table definition. This does not determine the physical database access method but is used rather to define what key fields must be provided in order to access a unique recording the database. Only this one primary key is catered for in this definition. This is because effective use of secondary keys requires such complex programming that it is not worth the effort.
• dbfield - this has fields
  1. dbfield - This is the key field of the dbfield table. It consists of a pair of words { dbtable fieldname }
  2. dbposition - This is the position of the field in the physical SQL record. This matches the order of the SQL CREATE TABLE declaration. The first position is 0.
  3. dbdomain - This is a domain name in the domain table which is used as the default for type and attribute values not specified in the type and attribute fields in this record. 'char' and 'num' are the basic domains, but 'seconds', 'image' and 'command' are also important special domains. Times and dates are typically held as [clock seconds]. Images are held by filename and require special display and upload functions in the programs that use these fields. 'command' is used for storing tcl commands which can include encryption specifiactions, in particular the decode command that provides the value.
  4. dbtype - This specifies an object type for this field. The simplest type is char which is the default. The other common type is num for numeric types. these are the only types intrinsic to the Tcl manager in its first implementation. Other more complex types can be added. Examples are image for image types, html for text with HTML markup, and tcl for tcl code fragments.
  5. dbattrs - This field holds a list of option pairs in the Tcl array list form. This is used to provide widget and html display options including formatting options for both input adn output (e.g. - format "%d %b %y" for dates). Virtually anything can be put in here including references to Tcl commands to be executed by appropriate applications, including validation and help.
• dbdomain - The domain table is a simple reference store for attribute sets which are commonly used by fields in an application. Any object in an application such as date, product, supplier, which is used across the database can usefully be described in the domain table generically. Particualr instances of the object in a particular table may then have special attrs applied to it which override the defaults on the domain table. The domain table provides a mechanism for managing field attributes across the database. Basically, ALL field attrs should be encoded in the dbdomain table rather than with a specific field in order to minimise maintenance and management. Only specific attribute overrides should be encoded in the dbfield table. This means that if a field has any attributes, then it should have an entry in the dbdomain table with the base attributes held there. In practice some attributes are always held at the field level because they tend to be unique to the field in that table such as the -text attribute which is typically used to provide the heading.
  1. dbdomain - This is the keyfield of the dbdomain table. There are two intrinsic dbdomains char and .
  2. dbtype - There are two intrinsic types char and . SQLite stores only character type but uses numeric type in expressions. How is numeric type used by the SQLite manager which is different to char type ?
  3. dbattrs - The aray list of option value pairs for this domain, which serve as the default attributes throughout all tables in the database.

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.