====== SQLite ======

A little introduction to SQLite with a very simple schema and command line statements.\\
\\
Used executable: sqlite3\\
Used db file: Supermarket.db

==== Diagramm ====

{{:scripting:supermarket.png?nolink&600|}}

==== Create Schema ====
Item:
<code>
sqlite3 Supermarket.db "CREATE TABLE Item(ItemID INTEGER PRIMARY KEY, Description TEXT UNIQUE, Price REAL DEFAULT '0.0');"
</code>

Supermarket:
<code>
sqlite3 Supermarket.db "CREATE TABLE Supermarket(SupermarketID INTEGER PRIMARY KEY, Location TEXT);"
</code>

Inventory:
<code>
sqlite3 Supermarket.db "CREATE TABLE Inventory(SupermarketID INTEGER, ItemID INTEGER, Quantity INTEGER, PRIMARY KEY(SupermarketID, ItemID), FOREIGN KEY(SupermarketID) REFERENCES Supermarket(SupermarketID), FOREIGN KEY(ItemID) REFERENCES Item(ItemID));"
</code>

==== Fill ====

Table "Item":
<code>
sqlite3 Supermarket.db "INSERT INTO Item(Description,Price) VALUES('Fish','8.00');"
sqlite3 Supermarket.db "INSERT INTO Item(Description,Price) VALUES('Chips','5.50');"
sqlite3 Supermarket.db "INSERT INTO Item(Description,Price) VALUES('Cookies','2.70');"
sqlite3 Supermarket.db "INSERT INTO Item(Description,Price) VALUES('Spaghetti','4.90');"
sqlite3 Supermarket.db "INSERT INTO Item(Description,Price) VALUES('Pizza','9.00');"
</code>

Table "Supermarket":
<code>
sqlite3 Supermarket.db "INSERT INTO Supermarket(Location) VALUES('London');"
sqlite3 Supermarket.db "INSERT INTO Supermarket(Location) VALUES('Alpha Centauri');"
sqlite3 Supermarket.db "INSERT INTO Supermarket(Location) VALUES('Golgafrincham');"
sqlite3 Supermarket.db "INSERT INTO Supermarket(Location) VALUES('Rupert');"
sqlite3 Supermarket.db "INSERT INTO Supermarket(Location) VALUES('Ursa Minor Beta');"
</code>

Table "Inventory":
<code>
sqlite3 Supermarket.db "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('1','1','42');"
sqlite3 Supermarket.db "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('1','3','13');"
sqlite3 Supermarket.db "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('5','5','2');"
sqlite3 Supermarket.db "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('2','1','75');"
sqlite3 Supermarket.db "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('3','3','33');"
sqlite3 Supermarket.db "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('1','2','24');"
sqlite3 Supermarket.db "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('4','3','18');"
sqlite3 Supermarket.db "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('3','1','84');"
sqlite3 Supermarket.db "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('5','4','27');"
</code>

==== Update ====

<code>
sqlite3 Supermarket.db "UPDATE Inventory SET Quantity='32' WHERE SupermarketID=3 AND ItemID=3;"
</code>

==== Delete ====

<code>
sqlite3 Supermarket.db "DELETE FROM Inventory WHERE SupermarketID=5 AND ItemID=4;"
</code>

==== Queries ====

Simple query over all tables:
<code>
sqlite3 Supermarket.db "SELECT sm.Location,iv.Quantity,i.Description,i.price FROM Supermarket AS sm, Item AS i, Inventory AS iv WHERE sm.SupermarketID=iv.SupermarketID AND i.ItemID=iv.ItemID;"
</code>

Grouped query:
<code>
sqlite3 Supermarket.db "SELECT sm.Location,COUNT(*) FROM Supermarket AS sm, Item AS i, Inventory AS iv WHERE sm.SupermarketID=iv.SupermarketID AND i.ItemID=iv.ItemID GROUP BY iv.SupermarketID;"
</code>


==== All in one script ====

Bourne shell example:

<code>
#!/bin/sh

EXE=sqlite3
DB=Supermarket.db

###############
# create

# Item
${EXE} ${DB} "DROP TABLE IF EXISTS Item;"
${EXE} ${DB} "CREATE TABLE Item(ItemID INTEGER PRIMARY KEY, Description TEXT UNIQUE, Price REAL DEFAULT '0.0');"

# Supermarket
${EXE} ${DB} "DROP TABLE IF EXISTS Supermarket;"
${EXE} ${DB} "CREATE TABLE Supermarket(SupermarketID INTEGER PRIMARY KEY, Location TEXT);"

# Inventory 
${EXE} ${DB} "DROP TABLE IF EXISTS Inventory;"
${EXE} ${DB} "CREATE TABLE Inventory(SupermarketID INTEGER, ItemID INTEGER, Quantity INTEGER, PRIMARY KEY(SupermarketID, ItemID), FOREIGN KEY(SupermarketID) REFERENCES Supermarket(SupermarketID), FOREIGN KEY(ItemID) REFERENCES Item(ItemID));"

###############
# fill

${EXE} ${DB} "INSERT INTO Item(Description,Price) VALUES('Fish','8.00');"
${EXE} ${DB} "INSERT INTO Item(Description,Price) VALUES('Chips','5.50');"
${EXE} ${DB} "INSERT INTO Item(Description,Price) VALUES('Cookies','2.70');"
${EXE} ${DB} "INSERT INTO Item(Description,Price) VALUES('Spaghetti','4.90');"
${EXE} ${DB} "INSERT INTO Item(Description,Price) VALUES('Pizza','9.00');"

${EXE} ${DB} "INSERT INTO Supermarket(Location) VALUES('London');"
${EXE} ${DB} "INSERT INTO Supermarket(Location) VALUES('Alpha Centauri');"
${EXE} ${DB} "INSERT INTO Supermarket(Location) VALUES('Golgafrincham');"
${EXE} ${DB} "INSERT INTO Supermarket(Location) VALUES('Rupert');"
${EXE} ${DB} "INSERT INTO Supermarket(Location) VALUES('Ursa Minor Beta');"

${EXE} ${DB} "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('1','1','42');"
${EXE} ${DB} "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('1','3','13');"
${EXE} ${DB} "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('5','5','2');"
${EXE} ${DB} "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('2','1','75');"
${EXE} ${DB} "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('3','3','33');"
${EXE} ${DB} "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('1','2','24');"
${EXE} ${DB} "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('4','3','18');"
${EXE} ${DB} "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('3','1','84');"
${EXE} ${DB} "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('5','4','27');"

###############
# update

${EXE} ${DB} "UPDATE Inventory SET Quantity='32' WHERE SupermarketID=3 AND ItemID=3;"

###############
# delete
${EXE} ${DB} "DELETE FROM Inventory WHERE SupermarketID=5 AND ItemID=4;"

###############
# queries

${EXE} ${DB} "SELECT sm.Location,iv.Quantity,i.Description,i.price FROM Supermarket AS sm, Item AS i, Inventory AS iv WHERE sm.SupermarketID=iv.SupermarketID AND i.ItemID=iv.ItemID;"

${EXE} ${DB} "SELECT sm.Location,COUNT(*) FROM Supermarket AS sm, Item AS i, Inventory AS iv WHERE sm.SupermarketID=iv.SupermarketID AND i.ItemID=iv.ItemID GROUP BY iv.SupermarketID;"

</code>