RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


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

Not every application needs a full-featured enterprise-scale database. In such cases, you can reduce costs and save resources by using a small-footprint database.

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
Then, add a table to it using the following CREATE TABLE command:

   sqlite> CREATE TABLE items (
      ...> 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
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.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date