Getting Small: Building Lightweight Web Applications with Small-Footprint Databases : Page 3

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.

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 field—which doesn't look like a date—it'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 field—you 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.

Previous Page

Next Page