Create the Database

In the examples below, we will use the database "documents" (non-normalized), in which we will store, for example, the articles published in this site. This database will contain 2 tables:

• Table: article
  Fields: id, title, author, published, length, updated, notes
• Table: body
  Fields: id, line_num, contents

In mSQL you will create the database and tables using the code:

% msqladmin create documents
% msql documents < schema.sql

in our case "schema.sql" will contain:

create table article (
  id        char(10) NOT NULL,    # unique ID 
  title        char(200) NOT NULL,    # title of the article
  author    char(200),        # one or more, comma separated list
  published    int NOT NULL,        # when was the article published
  length    int NOT NULL,        # how many lines (each of 120 char length)
  updated    int,            # when was it updated
  notes        text(80),        # notes for internal use
)\p\g

create unique index article_idx on article ( id )\p\g

create table body (
  id        char(10) NOT NULL,    # same as the article ID
  line_num    int,            # line number
  contents    char(120)        # a line of text from the article
)\p\g

create index body_idx on article ( id )\p\g

Of course the date fields could have been created using the DATE type, but using an INT type makes it easy for comparison using dates in the form: yyyymmdd, e.g. 19990415 for April 15, 1999. After setting up the database we populate it, and start having fun!

So you want to use a database in your site?
So you want to use a database in your site?
Example 1: a simple SQL query interface