Differences

This shows you the differences between two versions of the page.

--- scripting:sqlite [2013-01-25 19:09] – created root
+++ scripting:sqlite [2014-12-18 20:08] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
 ====== SQLite ======
-== Create Table ==
+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 test.db "CREATE TABLE Testing(Id INTEGER PRIMARY KEY, Name TEXT);"
+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>