I have started to plan the my new online books catalog as part of the new site structure, as I discussed here. I know the basic shape of the way the catalog will work, in that I will be using server based technology rather than compiling an Access database to HTML. I have been assessing which database technology to use bearing in mind that part of the reason this site exists is to provide an opportunity to learn new techniques and I wanted to use either Entity framework or NHibernate as a repository model of the data.

Database options

Database Pros Cons
Access Already in use, no porting needed Cannot really be used for a web site
Non standard SQL means patchy support for ORMs such as EF or NHibernate
SQLite Free, portable (can be run on mobile devices)
Supported by ORMs such as EF and NHibernate
Data can be updated locally and then uploaded by copying a single file		 Incomplete implementation for example foreign key constraints are not enforced and stored procedures are not available.
MySQL Full implementation
Free (ish), I have already paid my $10		 Only available for desktop operating systems.
Porting, updating and backing up the data would be more complex than SQLite
SQLServer Full implementation
Rapid development using LINQ to SQL		 Cost, $100 a year.
Other cons are the same as MySQL

In normal circumstances I would probably select MySQL as it does everything that is needed is supported by the tools that I plan to use. however there are other requirements to be considered.

  1. I am the only person who will be updating the data and it would be a nice option to do this on a mobile device, it is also simpler and quicker to build a UI to enable the data to be updated on a smart client (either desktop or mobile) rather than building a full blown web based UI. This would mean that the web site would be used to report on the data and not update.
  2. Backing up and restoring the data  can be simplified if it is just a single file rather than part of an existing database.
  3. This site is a learning exercise and using a combination such as NHibernate and SQLite gives me an opportunity to expand my knowledge
  4. Stored procedures are less of an issue in an architecture that using an ORM to model the data.

So on balance for this site I am tempted to select SQLite however I am concerned about the lack of support for foreign key constraints. I like my data integrity enforced and would be worried if it was not.

 

 

foreign key constraints

Foreign key constraints are used within the database to ensure that the data is stored correctly. For example consider this relationship from the books database.

data relationship

All this relationship does is ensure that each book has a valid author, and that authors cannot be deletes if it would invalidate any books. In most applications data is accessed and updated in a multiuser environment. The integrity of the data can only be assured if the constraint is applied in a transaction. Consider what would happen if one user changed a book to reference author A and at the same time another user deleted author A. A typical approach is to use foreign key constraints in the database like this

bok_aut_id INTEGER
   CONSTRAINT fk_bok_books_bok_aut_id_aut_author_aut_id
   REFERENCES [aut_author](aut_id)
 

Although the same result could be achieved using other techniques doing it like this in the database is the most robust and transparent solution.

Adding foreign key constraints to SQLite

At first I could not believe such a fundamental part of SQL was missing from SQLite. I had a look around and found this posting describing the problem and a solution using triggers. It looked good and when I tried it with my database it worked. It did however make the SQL to create the constraint in the database a bit lot more verbose.

-- Foreign Key Preventing insert
DROP TRIGGER IF EXISTS fk_bok_books_bok_aut_id_aut_author_aut_id_ins;
CREATE TRIGGER fk_bok_books_bok_aut_id_aut_author_aut_id_ins
BEFORE INSERT ON [bok_books]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on bok_books violates fk constraint')
  WHERE NEW.bok_aut_id IS NOT NULL
        AND (SELECT aut_id
               FROM [aut_author]
               WHERE aut_id = NEW.bok_aut_id
             ) IS NULL;
END;

-- Foreign key preventing update
DROP TRIGGER IF EXISTS fk_bok_books_bok_aut_id_aut_author_aut_id_upd;
CREATE TRIGGER fk_bok_books_bok_aut_id_aut_author_aut_id_upd
BEFORE UPDATE ON [bok_books]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on bok_books violates fk constraint')
    WHERE NEW.bok_aut_id IS NOT NULL
        AND (SELECT aut_id
               FROM [aut_author]
               WHERE aut_id = NEW.bok_aut_id
             ) IS NULL;
END;

DROP TRIGGER IF EXISTS fk_bok_books_bok_aut_id_aut_author_aut_id_del;
-- Foreign key preventing delete
CREATE TRIGGER fk_bok_books_bok_aut_id_aut_author_aut_id_del
BEFORE DELETE ON [aut_author]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'delete on aut_author violates fk constraint')
    WHERE (SELECT bok_aut_id
            FROM [bok_books]
            WHERE bok_aut_id = OLD.aut_id
           ) IS NOT NULL;
END;
 

So the obvious solution would be to generate the SQL rather than typing it in every time. I have used Codesmith template generator in the past so I wrote a Codesmith template to generate the extra SQL given the names of the tables and fields concerned.

The Codesmith template can be used like this

Codesmith 2.6

I have used Codesmith 2.6 – the last free version to test this template in case you do not own a later version however it should work in later versions. I have also added a template parameter to cascade the delete.

You can download the template if you want to use it.