diff options
Diffstat (limited to 'demos/blog-tutorial/protected/pages/Day2/CreateDB.page')
| -rw-r--r-- | demos/blog-tutorial/protected/pages/Day2/CreateDB.page | 41 |
1 files changed, 29 insertions, 12 deletions
diff --git a/demos/blog-tutorial/protected/pages/Day2/CreateDB.page b/demos/blog-tutorial/protected/pages/Day2/CreateDB.page index 04dd0d90..0d45ba50 100644 --- a/demos/blog-tutorial/protected/pages/Day2/CreateDB.page +++ b/demos/blog-tutorial/protected/pages/Day2/CreateDB.page @@ -3,7 +3,7 @@ <h1>Creating Database</h1>
<p>
-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 <a href="http://www.pradosoft.com/demos/quickstart/?page=Database.DAO">database access object (DAO)</a> and <a href="http://www.pradosoft.com/demos/quickstart/?page=Database.ActiveRecord">active record</a>.
+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 <a href="http://www.pradosoft.com/demos/quickstart/?page=Database.DAO">database access object (DAO)</a> and <a href="http://www.pradosoft.com/demos/quickstart/?page=Database.ActiveRecord">Active Record</a>.
</p>
<p>
@@ -13,34 +13,51 @@ For tutorial purpose, we have simplified the requirements of our blog system so <img src="<%~ ER.gif %>" />
<p>
-The corresponding SQL statements for creating the tables are as follows,
+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 <tt>protected/schema.sql</tt>.
</p>
<com:TTextHighlighter CssClass="source">
+/* create users table */
CREATE TABLE users (
username VARCHAR(128) NOT NULL PRIMARY KEY,
email VARCHAR(128) NOT NULL UNIQUE,
- password VARCHAR(128) NOT NULL,
+ password VARCHAR(128) NOT NULL, /* plain text password */
+ 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 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
+ author VARCHAR(128) NOT NULL, /* references users.username */
+ create_time INTEGER NOT NULL, /* UNIX timestamp */
+ title VARCHAR(256) NOT NULL, /* title of the post */
+ content TEXT NOT NULL /* content of the post */
);
+
+/* 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');
+</com:TTextHighlighter>
+
+<com:NoteBox>
+SQLite does not support <a href="http://www.sqlite.org/omitted.html">foreign key constraint</a>. Therefore, we will write PHP code to ensure that the <tt>posts.author</tt> field contains valid data. Also, we are exploiting the fact that the <tt>posts.post_id</tt> field is <a href="http://www.sqlite.org/autoinc.html">auto-incremental</a> if we assign NULL to it.
+</com:NoteBox>
+
+<p>
+We use the <a href="http://www.sqlite.org/download.html">SQLite command line tool</a> to create the SQLite database. We first create a directory <tt>protected/data</tt> to hold the SQLite database file. Per SQLite's requirement, this directory and the database file to be created must be set writable by the Web server process. We now execute the following command under the directory <tt>protected/data</tt>:
+</p>
+
+<com:TTextHighlighter CssClass="source">
+sqlite3 blog.db < ../schema.sql
</com:TTextHighlighter>
<p>
-We use a SQLite 3 database to keep our data.
+The database has been created as <tt>protected/data/blog.db</tt> and we shall see the following directories and files:
</p>
+<img src="<%~ directories.gif %>" />
</com:TContent>
\ No newline at end of file |