Creating Database

Most Web applications use database to keep data. Our blog system is not an exception. In this section, we will describe how to write database-driven pages for our blog system. We will use techniques including database access object (DAO) and Active Record.

For tutorial purpose, we have simplified the requirements of our blog system so that it only needs to deal with user and post data. We thus create two database tables, users and posts, as shown in the following entity-relationship (ER) diagram.

We use a SQLite 3 database to keep our data. We first convert the ER diagram into the following SQL statements and save them in the file protected/schema.sql.

/* create users table */
CREATE TABLE users (
  username      VARCHAR(128) NOT NULL PRIMARY KEY,
  email         VARCHAR(128) NOT NULL,
  password      VARCHAR(128) NOT NULL,  /* in plain text */
  role          INTEGER NOT NULL,       /* 0: normal user, 1: administrator */
  first_name    VARCHAR(128),
  last_name     VARCHAR(128)
);

/* create posts table */
CREATE TABLE posts (
  post_id       INTEGER NOT NULL PRIMARY KEY,
  author_id     VARCHAR(128) NOT NULL
                CONSTRAINT fk_author REFERENCES users(username),
  create_time   INTEGER NOT NULL,       /* UNIX timestamp */
  title         VARCHAR(256) NOT NULL,  /* title of the post */
  content       TEXT,                   /* post body */
  status        INTEGER NOT NULL        /* 0: published; 1: draft; 2: pending; 2: denied */
);

/* insert some initial data records for testing */
INSERT INTO users VALUES ('admin', 'admin@example.com', 'demo', 1, 'Qiang', 'Xue');
INSERT INTO users VALUES ('demo', 'demo@example.com', 'demo', 0, 'Wei', 'Zhuo');
INSERT INTO posts VALUES (NULL, 'admin', 1175708482, 'first post', 'this is my first post', 0);

Note: The fk_author constraint is ignored by SQLite because SQLite does not support foreign key constraint. Nevertheless, we still keep the constraint there for the capability of porting our blog system to different DBMS. Also, in the above we are exploiting the fact that the posts.post_id field is auto-incremental if we assign NULL to it.

We then use the SQLite command line tool to create the SQLite database. We create a directory protected/data to hold the SQLite database file. We now execute the following command under the directory protected/data:

sqlite3 blog.db < ../schema.sql

The database has been created as protected/data/blog.db and we shall see the following directories and files:

Note: It is required by SQLite that both the directory protected/data and the database file protected/data/blog.db be set writable by the Web server process.