Introducing SQLite

SQLite is a compact relational database management system (rdbms) that requires no installation other than having a single executable file. It creates and stores the schema, tables and data for each database as a single file which is parsed when the database is opened. In general SQLite uses standard sql syntax but with limited alter table support.

SQL statements may extend for several lines and must be terminated with a semicolumn before the statement will be evaluated by the database engine. SQLite dot commands which are specific to this system, serve several special functions. They are entered on only one line and do not require a terminating character. These dot commands duplicate functions found in other databases. For example ".tables" is equivalent to "show tables" function found in many SQL database management systems. More information on the available dot commands can be found in the SQLite notes section.

Starting SQLite in Microsoft Windows

Open a command prompt window by either going through the Start menu and selecting the command prompt listing which can usually be found in the accessories menu, or enter "cmd.exe" in the run dialog box of the Start menu. Using the change directory command (cd), navigate to the folder where the sqlite program is located. Here is one example, although the path shown in red text will likely be different on your computer. "cd C:\Databases\sqlite\ ". At the command line enter "sqlite3 path and database name " If the database file does not currently exist then it will be created. If no path is specified then the database file will be placed in the folder in which the program "sqlite3.exe" resides.

Microsoft Windows XP [Version 5.1.2600] 

(C) Copyright 1985-2001 Microsoft Corp. 

C:\My Documents\>cd C:\Databases\sqlite 

C:\Databases\sqlite>sqlite3 C:\Databases\inventoryctrl.db 

SQLite version 3.7.4 

Enter ".help" for instructions

sqlite> .databases
seq	name	file
---	---------------	----------------------------------------------------------
0	main	C:\Databases\inventoryctrl.db
1	temp	C:\DOCUME~1\COLINR~1\LOCALS~1\Temp\etilqs_i12OPG64gGySrwGX
sqlite >

Creating a Table

CREATE TABLE table_name(fieldname_1 data_type, fieldname_2 data_type, fieldname_3 data_type);

sqlite> CREATE TABLE inventory(StockNumber INTEGER PRIMARY KEY,Descrip VARCHAR(50),OnHandQuan INTEGER,PackQty INTEGER,PackCost FLOAT); 

sqlite>

There is technically no requirement to declare data types when creating a table in SQLite with the possible exception of creating an INTEGER PRIMARY KEY field. That being said, it is still a good idea to do so if you wish to make your database portable to another database management system such as MYSQL or ORACLE. See the section at the end of the document

Putting Data into the Table

String values are surrounded by single quotes, numeric values are not.

INSERT INTO table_name(fieldname_1,fieldname_2,fieldname_3)VALUES ('value a','value b',0.000);

sqlite> INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost)VALUES (51002,'AA Dry Cells 4 Pack',173,12,9.00); 

sqlite> INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost)VALUES (51004,'AA Dry Cells 8 Pack',5,12,16.80); 

sqlite> INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost)VALUES (43512,'10W-30 Motor Oil, Quart',36,12,18.20); 

sqlite> INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost)VALUES (51013,'D Dry Cells 8 Pack',19,12,90.20); 

sqlite> INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost)VALUES (23155,'Shovel Pointed Long Handle',1500,1,9.82); 

sqlite> INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost)VALUES (51001,'AAA Dry Cells 4 Pack ',92,12,9.00); 

sqlite> INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost)VALUES (43111,'White Gas Gallon Can',14,4,14.75);

Let us insert another record for 5W-Motor oil item number 43522

sqlite> INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost)VA 

LUES (43512,'5W-30 Motor Oil, Quart',17,12,18.20); 

SQL error: PRIMARY KEY must be unique 

sqlite>

Oops the wrong stock number was used. In the table definition above, the StockNumber column was specified to be the primary key of the table meaning that each value in that column must be unique. If the statement is reentered with the correct and unique StockNumber then the record will be added to the table. Let's do a simple select query to show that the records have been added to the table properly.

sqlite> select * from inventory; 

StockNumber|Descrip|OnHandQuan|PackQty|PackCost 

Shovel Pointed Long Handle|1500|1|9.82 

White Gas Gallon Can|14|4|14.75 

10W-30 Motor Oil, Quart|36|12|18.2 

5W-30 Motor Oil, Quart|17|12|18.2 

AAA Dry Cells 4 Pack |92|12|9.0 

AA Dry Cells 4 Pack |173|12|9.0 

AA Dry Cells 8 Pack|5|12|16.8 

D Dry Cells 8 Pack|19|12|90.2 

sqlite>

It is possible to use an INSERT statement that does not list column names as shown below.

INSERT INTO table_name VALUES ('value 1',NULL,'value 2 ,'','value 3');

However the values listed in the INSERT statement must be in the same order as they appear in the CREATE TABLE statement and a NULL value or empty quotes must be included for the values that are omitted in the sequence. Use with extreme caution.

Updating Records in a Table

If you look at the first record in the Select Query results you will see that the stated on hand quantity of item number 23155 is 1500 when in fact there are only 15 shovels on hand. This can be corrected by using an UPDATE statement.

UPDATE inventory SET OnHandQuan= 15 WHERE StockNumber = 23155;

sqlite> UPDATE inventory SET OnHandQuan= 15 WHERE StockNumber = 23155; 

sqlite> SELECT * FROM inventory WHERE StockNumber = 23155; 

StockNumber|Descrip|OnHandQuan|PackQty|PackCost 

23155|Shovel Pointed Long Handle|15|1|9.82 

sqlite>

Note that it is very important to include a WHERE clause specifying the correct criteria for the records you wish to update or all the records in the table will be updated.
More complex examples of SQLite UPDATE statements can be found on Page 7 .

Deleting Records From a Table

Here we have a circumstance where the Stock Number was entered incorrectly. While it is certainly possible to use an update statement to fix this, instead we will reenter the record with the correct stock number and delete the incorrect record.

DELETE FROM inventory WHERE StockNumber = 149;

sqlite> select * from inventory; 

StockNumber|Descrip|OnHandQuan|PackQty|PackCost 

149|Ball Point Pens Blue Fine tip, 12 pack|92|20|15.37 

23155|Shovel Pointed Long Handle|15|1|9.82 

43111|White Gas Gallon Can|14|4|14.75 

43512|10W-30 Motor Oil, Quart|36|12|18.2 

43522|5W-30 Motor Oil, Quart|17|12|18.2 

51001|AAA Dry Cells 4 Pack |92|12|9.0 

51002|AA Dry Cells 4 Pack |173|12|9.0 

51004|AA Dry Cells 8 Pack|5|12|16.8 

51013|D Dry Cells 8 Pack|19|12|90.2 

sqlite> sqlite> INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost)VALUES (75149,'Ball Point Pens Blue Fine tip, 12 pack',92,20,15.37); 

sqlite> DELETE FROM inventory WHERE StockNumber = 149; 

sqlite>

Do not use a DELETE command without a "WHERE" clause unless you intend to discard all the records in a table. Also much like the UPDATE command, it is very important to specify the right criteria to be certain that you are in fact deleting the correct records. It is often worthwhile to test the WHERE clause to be used in the DELETE statement by first running a SELECT query using the same criteria.

The .import command

Records from a delimited text file can be added to a SQLite table by using the ".import" command. There are however several limitations and considerations. The table must already exist in the database. The data in each field in the source document must be arranged in the same order as the column names in the SQLite table definition and there must be the same number of fields per line in the text document as there are columns in the table definition. Text values contained in quotes will retain the quotation marks with the text.

.import path/filename.txt tablename

Remember that the default delimiter for SQLite is the pipe "|". If the text file that the data is coming from uses a different delimiter such as a comma then the ".separator" command must to be used to change it. The example below uses the tilde (~) symbol as a delimiter in the source files because commas were present in some of the values in the document as show in the excerpt below.

HARTSHORN, SLATS OF~AMMONIUM CARBONATE
SALT OF HARTSHORN~AMMONIUM CARBONATE
MURIATE OF AMMONIA~AMMONIUM CHLORIDE
SAL AMMONIAC~AMMONIUM CHLORIDE

sqlite> CREATE TABLE chem_name(old_name TEXT,chemical TEXT); 

sqlite> .separator "~" sqlite> .import chempart1.txt chem_name 

sqlite> SELECT * FROM chem_name limit 8; 

old_name	chemical
------------------------------	-----------------------------------
HARTSHORN, SLATS OF	AMMONIUM CARBONATE
SALT OF HARTSHORN	AMMONIUM CARBONATE
MURIATE OF AMMONIA	AMMONIUM CHLORIDE
SAL AMMONIAC	AMMONIUM CHLORIDE
TARTAR EMETIC	ANTIMONY AND POTASIUM TARTRATE
BUTTER OF ANTIMONY	ANTIMONY TRICHLORIDE
ORPIMENT	ARSENIC TRISULFIDE
BARYTA	BARIUM OXIDE

sqlite>
sqlite> .import C:/Databases/chempart2.txt chem_name
sqlite> SELECT COUNT(old_name) FROM chem_name;
COUNT(old_name)
------------------------------
81
sqlite>

The separator for tab delimited files is "\t".

Sqlite Datatypes

Sqlite is a typeless database and with the exception of a field that has been declared as an INTEGER PRIMARY KEY, just about any type of a data can be placed in any column in a SQLite table. SQLite will automatically class each data item in one of the following catagories.

NULL - where there is no value in the field, not even a zero or an empty string.
INTEGER - The value is a positive or negative integer.
REAL - The value is a floating point number
TEXT - Text string
BLOB - Data stored exactly as it was entered.

sqlite> CREATE TABLE datatype(linenum INTEGER PRIMARY KEY,testdata INTEGER); 

sqlite> INSERT INTO datatype(linenum,testdata) VALUES(1,-34); 

sqlite> INSERT INTO datatype(linenum,testdata) VALUES(2,'This a text field'); 

sqlite> INSERT INTO datatype(linenum,testdata) VALUES(3,3.1415); 

sqlite> INSERT INTO datatype(linenum,testdata) VALUES(4,NULL); 

sqlite> /*Demonstrating auto increment by placing null in primary key field.*/ 

sqlite> INSERT INTO datatype(linenum,testdata) VALUES(NULL,'Placing NULL in Primary Key '); 

sqlite> SELECT linenum,testdata,typeof(testdata) FROM datatype;

linenum	testdata	typeof(testdata)
----------	------------------------------	----------------
1	-34	integer
2	This a text field	text
3	3.1415	real
4		null
5	Placing NULL in Primary Key	text

In the select query the expression typeof ( field_value ) uses the typeof function to show how sqlite classes each data item in the column. Note the result on linenumber 5. NULL was entered as the value for the INTEGER PRIMARY KEY on the fifth insert statement and that SQLite automatically added one to the highest existing integer in the column. When a record is successfully added to a SQLite table, SQLite will auto increment an INTEGER PRIMARY KEY column if no value is provided for the column.

sqlite> /*Demonstrating the result of entering a non integer value to the primary key field. */ 

sqlite> INSERT INTO datatype(linenum,testdata) VALUES('This value does','not belong in the primary key'); 

SQL error: datatype mismatch 

sqlite>

The result of this last statement is that the record was not added because an attempt was made to put a text string ("This value does") into an INTEGER PRIMARY KEY column.

Note that SQLite's PRIMARY KEY constraint does not prevent the entry of null values.
In instances where the primary key is an integer then SQLite will autoincrement to the next higher number in the key column. For non integer primary keys however, you must include the words "NOT NULL " after the column datatype in the CREATE TABLE statement for any column that is a primary key or part of Primary Key as is the case with a Composite Primary Key. This will ensure that each record in the table has a distinct identifier and help to avoid the dreaded Cartesian Product (See page 5) when you run queries using multiple tables.

Dates and Times

All dates and times are stored in a SQLite tables as text strings except for the Julian Date format which stores the number of days since November 24, 4714 BC as a floating point number. Dates are most often in the format of "YYYY-MM-DD". SQLite has a number of functions for manipulating and working with date strings

sqlite> SELECT date('now'); 

2011-01-21 

sqlite>SELECT datetime('now'); 

2011-01-21 13:45:51 

sqlite> SELECT datetime('now','localtime'); 

2011-01-21 08:47:22 

sqlite>

Pages

Thursday, April 12, 2012

SQLite