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.

The corresponding SQL statements for creating the tables are as follows,

CREATE TABLE users ( username VARCHAR(128) NOT NULL PRIMARY KEY, email VARCHAR(128) NOT NULL UNIQUE, password VARCHAR(128) NOT NULL, first_name VARCHAR(128), last_name VARCHAR(128) ); CREATE TABLE posts ( post_id INTEGER NOT NULL PRIMARY KEY, author VARCHAR(128) NOT NULL, create_time INTEGER NOT NULL, title VARCHAR(256) NOT NULL, content TEXT NOT NULL, CONSTRAINT "posts_fk" FOREIGN KEY ("author") REFERENCES users ("username") ON DELETE CASCADE ON UPDATE CASCADE );

We use a SQLite 3 database to keep our data.