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.
|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.|
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
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.
- 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.
- Backing up and restoring the data can be simplified if it is just a single file rather than part of an existing database.
- This site is a learning exercise and using a combination such as NHibernate and SQLite gives me an opportunity to expand my knowledge
- 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.
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
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.