Using SQLite for Simple Database Storage - SQLite in Action
(Page 2 of 6 )
To demonstrate SQLite in action, I'll create a simple user registration system. While my application is targeted at the family Christmas Wish list application that we use for our gift exchange, this tool could be easily adapted to any application. I've written the class in VB.NET, using the ADO.NET SQLite provider at http://sourceforge.net/projects/adodotnetsqlite/. This provider can be downloaded in binary or source form.
The ADO.NET provider returns results just like any other ADO.NET provider. The native interfaces to SQLite return data in more unique ways which are covered in the SQLite documentation.
The application needs to collect a unique user name, an email address for confirmation, and a password. We'll protect the password by md5 encoding it in our database. To keep the length of the article down,
I'll only show the parts relevant to database interaction. At the end
I'll provide a link that will let you download a working user-adding application, so you can see the code in action.
create table user (
id integer primary key,
name varchar(40) not null,
password char(32) not null,
email varchar(60) not null
);
create index auth on user (name, password);
Although SQLite does not honor the data type declarations, I have left them in place because they are good reminders of what the fields should represent. This SQL is also highly portable to other database engines, such as MySQL. Keeping the portability options open is important in case my application grows.
SQLite provides a feature similar to MySQL's auto_increment fields on any field declared as an integer primary key. In SQL this can be retrieved with "SELECT LAST_INSERT_ROWID()". An earlier incarnation of this application used PHP and PEAR::DB to interact with the database. PEAR::DB doesn't deal well with auto_increment fields, preferring to use a sequence instead. To support that feature PEAR::DB created the following schema entries:
CREATE TABLE user_seq (id INTEGER UNSIGNED PRIMARY KEY);
CREATE TRIGGER user_seq_cleanup AFTER INSERT ON user_seq
BEGIN
DELETE FROM user_seq WHERE id<LAST_INSERT_ROWID();
END;
Trigger support in a lightweight, embedded database like SQLite is an impressive feature. Features like that have lead to the popularity of this engine. We don't need that feature in our application because we're using a different provider, but I showed it here to demonstrate SQLite's versatility and power.
Next: An Example User Object >>
More Database Articles
More By Clay Dowling
/ 24 
