Assembling the Application
Now that the implementation roadmap is clearer, you can begin assembling the application. First, create a new SQLite database by calling the SQLite program with the name of the database file:
root@thor:/usr/local/apache/data# sqlite3 todo.db3
SQLite version 3.3.17
Enter ".help" for instructions
sqlite>
Then, add a table to it using the following
CREATE TABLE command:
sqlite> CREATE TABLE items (
...> id INTEGER NOT NULL PRIMARY KEY,
...> name TEXT NOT NULL,
...> due INTEGER NOT NULL,
...> complete INTEGER NULL,
...> priority INTEGER NOT NULL,
...> status INTEGER NOT NULL
...> );
Table 1 shows a brief explanation for each field in the preceding table definition.
Table 1: The table shows the database fields in the "items" table in the sample application, along with a brief explanation of each.
Field |
Description |
Id |
Record identifier |
Name |
Description of to-do item |
Due |
Date item becomes due |
Complete |
Date item was actually completed |
Priority |
Priority of to-do item, ranging from 1 (low) to 5 (high) |
Status |
Status of item: 0 (complete) or 1 (pending) |
You can verify that the table has been created with the
.tables command, which lists all the tables in the current database; if the table was created correctly, the
.tables command will return one table named
items:
sqlite> .tables
items
Next, insert some dummy records to get things rolling:
sqlite> INSERT INTO "items" VALUES(1,
'Buy anniversary present',1184437800,NULL,4,1);
sqlite> INSERT INTO "items" VALUES(2,
'Send invoices',1180549800,NULL,5,1);
sqlite> INSERT INTO "items" VALUES(3,
'Finish homework',1180549800, 1180290600,1,0);
sqlite> INSERT INTO "items" VALUES(4,
'Visit Jane',1180290600,NULL,2,1);
sqlite> INSERT INTO "items" VALUES(5,
'Finalize hotel reservations',1181737000,NULL,5,1);
sqlite> INSERT INTO "items" VALUES(6,
"Angie's birthday",1182537000,NULL,4,1);
sqlite> INSERT INTO "items" VALUES(7,
'Weed the lawn',1182237000,NULL,1,1);
In case you're wondering about the value inserted into the
due fieldwhich doesn't look like a dateit's a UNIX timestamp representation of a date. You can generate such a timestamp easily with PHP's
mktime() function; you'll see how to do that a little later.
Author's Note: A quick word here about SQLite's datatypes. SQLite supports five basic datatypes: NULL, INTEGER, REAL, TEXT, and BLOB. However, when inserting data into an SQLite database, the datatype of each value being inserted is given higher priority than the datatype of the field into which it is being inserted. This feature, referred to as "manifest typing," is unique to SQLite and it allows, for example, INTEGER values to be inserted into a TEXT field, or vice-versa. The only exception is a field of type INTEGER PRIMARY KEY, which creates an automatically-incrementing numeric fieldyou can see that field type in the preceding schema. |
If you find manifest typing distressing, relax; you can configure SQLite to run in "strict affinity mode," which returns an error if you attempt to insert an incorrectly typed value into a field. You can read more about manifest typing
here, and you can find a complete list of SQL-92 features
omitted from or
unsupported in SQLite.