I've got a database & website I want to move from using MySQL to using SQLite3. Well, I think I want to use SQLite3. Their document saying what sorts of uses make sense for SQLite3 are directly in line with my website, and I do want to remove some of the load off of my MySQL server so that it can have cycles free for more important purposes.
However I've run into a couple troubles converting the schema so that it fits within SQLite3's limited SQL support. Turns out that it doesn't support some column types and indexes. And that the SQL produced by mysqldump contains some MySQLisms which SQLite3 just doesn't understand.
One of the details is that SQLite3 doesn't support enum's. Sigh. Here's one of my table definitions only slightly cleaned up from mysqldump:
DROP TABLE IF EXISTS geog;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE geog (
id int(11) NOT NULL ,
continent enum('North America','South America','Central America','Carribean','Atlantic','Europe (West)','Europe (East)','Africa','Middle East','South Asia','East Asia','Asia','Pacific','Australia') NOT NULL ,
country char(255) NOT NULL default '',
state char(255) NOT NULL default '',
city char(255) NOT NULL default '',
url char(255) default NULL,
PRIMARY KEY (`id`) AUTOINCREMENT,
KEY `byContinent` (`continent`),
KEY `byCountry` (`country`),
KEY `byState` (`state`),
KEY `byCity` (`city`),
KEY `geogurl` (`url`)
) ENGINE=MyISAM AUTO_INCREMENT=9643 DEFAULT CHARSET=latin1 PACK_KEYS=1;
SET character_set_client = @saved_cs_client;
This table is meant to contain a list of geographic locations and I chose to use an enum to store the continent names. There are several things in this which SQLite3 barfs on when you run it like so:
$ sqlite3 -init geog.sql geog.db
-- Loading resources from geog.sql
SQL error near line 3: near "SET": syntax error
SQL error near line 4: near "SET": syntax error
SQL error near line 5: near "'North America'": syntax error
SQL error near line 19: near "SET": syntax error
SQLite version 3.5.9
Enter ".help" for instructions
sqlite>
Most of the SET statements simply don't work so we'll comment them out in a minute. The thing which stuck out was the "syntax error" on North America. Took awhile to work out what that meant, had to dig through the SQLite3 documentation to learn that their type system is somewhat, um, interesting.
They have this concept of "type affinity" which means that the stored value is only loosely associated with the type name in the SQL of the table definition. One particular thing is that enum is not one of the recognized data types. Which leaves you with the task of mimicing the enum column type in some other way.
Basically an enum column is a text column constrained to a specific set of values. I like the enum syntax because it's nice and concise and declarative. But .. pragmatically .. there are several ways to implement constraints. For example your code surrounding the database can enforce the constraint, but then you have to make sure all accesses to the database is through that code because that's the only way to enforce the constraint.
Fortunately I found a discussion of using triggers to enforce constraints and came up with this:-
DROP TABLE IF EXISTS geog;
-- SET @saved_cs_client = @@character_set_client;
-- SET character_set_client = utf8;
CREATE TABLE geog (
id int(11) NOT NULL ,
continent text, -- enum('North America','South America','Central America','Carribean','Atlantic','Europe (West)','Europe (East)','Africa','Middle East','South Asia','East Asia','Asia','Pacific','Australia') NOT NULL ,
country char(255) NOT NULL default '',
state char(255) NOT NULL default '',
city char(255) NOT NULL default '',
url char(255) default NULL,
PRIMARY KEY (`id`) -- AUTOINCREMENT,
-- KEY `byContinent` (`continent`),
-- KEY `byCountry` (`country`),
-- KEY `byState` (`state`),
-- KEY `byCity` (`city`),
-- KEY `geogurl` (`url`)
); -- ENGINE=MyISAM AUTO_INCREMENT=9643 DEFAULT CHARSET=latin1 PACK_KEYS=1;
--SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS geogContinents;
CREATE TABLE geogContinents (
continentName text
);
CREATE TRIGGER ContinentTrigger BEFORE INSERT ON geog FOR EACH ROW
WHEN (SELECT COUNT(*) FROM geogContinents WHERE continentName = new.continent) = 0 BEGIN
SELECT RAISE(rollback, 'foreign-key violation: geog.continent');
END;
insert into geogContinents values ('North America');
insert into geogContinents values ('South America');
insert into geogContinents values ('Central America');
insert into geogContinents values ('Carribean');
insert into geogContinents values ('Atlantic');
insert into geogContinents values ('Europe (West)');
insert into geogContinents values ('Europe (East)');
insert into geogContinents values ('Africa');
insert into geogContinents values ('Middle East');
insert into geogContinents values ('South Asia');
insert into geogContinents values ('East Asia');
insert into geogContinents values ('Asia');
insert into geogContinents values ('Pacific');
insert into geogContinents values ('Australia');
First off, notice that I commented out a bunch of stuff because SQLite3 doesn't recognize SET or KEY statements, or AUTOINCREMENT, or some other stuff.
The other thing is to change the definition of continent to text, add a new table geogContinents to hold the continent names, and a trigger to enforce the constraint that continent names can only be those named in the geogContinents table.
Now with this table definition you can initialize the database:-
$ sqlite3 -init geog2.sql geog2.db
-- Loading resources from geog2.sql
SQLite version 3.5.9
Enter ".help" for instructions
sqlite>
Then with an initialized database see that the constraints are enforced:-
$ sqlite3 geog2.db SQLite version 3.5.9 Enter ".help" for instructions sqlite> .tables geog geogContinents sqlite> insert into geog values (1, 'Georgia', '', '', '', ''); SQL error: foreign-key violation: geog.continent sqlite> insert into geog values (1, 'Asia', '', '', '', ''); sqlite>
