fun with sqlite3

We already have sqlite3 installed. So creating a new database is easy:

[code]
..mypath/db$ sqlite3 test.sqlite3
SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
[/code]

We add some tables:
[code]
sqlite> create table categories (name varchar(10), id smallint);
sqlite> create table products(name varchar(20), id smallint, category_id smallint);
[/code]

List our new tables:
[code]
sqlite> .schema
CREATE TABLE categories (name varchar(10), id smallint);
CREATE TABLE products(name varchar(20), id smallint, category_id smallint);
sqlite>
[/code]

Now insert some data:
[code]
sqlite> insert into categories values (‘catholicism’, 1);
sqlite> insert into categories values (‘baptist’, 2);
sqlite> insert into categories values (‘chino orthodox’, 3);
sqlite> insert into categories values (‘redneck’, 4);
sqlite> insert into products values (‘bible’, 1, 2);
sqlite> insert into products values (‘candle’, 2, 1);
sqlite> insert into products values (‘gun’, 3, 4);
sqlite> insert into products values (‘chopsticks’, 4, 3);
[/code]

List content of tables:
[code]
sqlite> select * from categories;
catholicism|1
baptist|2
chino orthodox|3
redneck|4
sqlite>
[/code]
[code]
sqlite> select * from products;
bible|1|2
candle|2|1
gun|3|4
chopsticks|4|3
sqlite>
[/code]

Okay. In this simple design there is a one-to-many relationship between tables. A category has many products. A category is just a category. It has no foreign keys. The products tables has a foreign key called “category_id.”

Now we can join the data:

[code]
sqlite> select p.name, c.name from products p inner join categories c on p.category_id = c.id;
bible|baptist
candle|catholicism
gun|redneck
chopsticks|chino orthodox
sqlite>
[/code]

Why did I use the “inner join” construct? Honestly, I don’t even think about this. I always think first in terms of inner join. If I need more complicated logic then perhaps I will use an “left join” construct.

[code]
sqlite> select p.name, c.name from products p left join categories c on p.category_id = c.id;
bible|baptist
candle|catholicism
gun|redneck
chopsticks|chino orthodox
[/code]

Hey! The results are the same! Why? Only because of the state of the data.

Let us change the data:
[code]
sqlite> update products set category_id = null where id = 3;
sqlite> select * from products;
bible|1|2
candle|2|1
gun|3|
chopsticks|4|3
sqlite>
[/code]

Now what category does the “gun” item belong to? We don’t know. Maybe it belongs to the Baptists or the Catholics?

Run the query again with both queries:

[code]
sqlite> select p.name, c.name from products p inner join categories c on p.category_id = c.id;
bible|baptist
candle|catholicism
chopsticks|chino orthodox
sqlite>
[/code]
[code]
sqlite> select p.name, c.name from products p left join categories c on p.category_id = c.id;
bible|baptist
candle|catholicism
gun|
chopsticks|chino orthodox
sqlite>
[/code]

Wow. The first query only shows products that have valid categories. The second query allows the invalid categories. So it returns the intersection and layers it on top of the invalid data.

Now we can ask which products need a category:
[code]
sqlite> select p.name, c.name from products p left join categories c on p.category_id = c.id where p.category_id is null;
gun|
sqlite>
[/code]
Ooooh! Someone in the Church Bookstore forgot to update the category of the gun item!