Rapid Application Development with Open Source Software

Next: Open Source Software Stack: FreeBSD, CakePHP, Nginx, Bootstrap UP: TOC

The following SQL-DDL define the relevant database object in a PostgreSQL 9.x database.

create table bo_user (
   id character(36) primary key,           -- Id of the user: system genereated string
   nick character varying(24) not null,    -- the nickname
   email character varying (255) not null,
   pw character varying (64),              -- the password
   lang character(2) default 'en',         -- the language
   last_login timestamp with time zone,
   created  timestamp with time zone DEFAULT ('now'::text)::timestamp with time zone,
   modified timestamp with time zone DEFAULT ('now'::text)::timestamp with time zone
) with (oids = false);

-- ensure that users' email is used only once in this application:
create unique index bo_user_email on bo_user(email);

-- ensure that the nick name in unique within this application:
create unique index bo_user_nick on bo_user(nick);


create sequence bo_book_id_seq;
create table bo_book (
   id integer primary key default nextval('bo_book_id_seq'::regclass),
   name character varying (80) not null,
   user_id character(36) not null,    -- link to author
   created  timestamp with time zone default ('now'::text)::timestamp with time zone,
   modified timestamp with time zone default ('now'::text)::timestamp with time zone
) with (oids = false);


-- the name of the book needs to be unique within all books in the database:
create unique index bo_book_name on bo_book(name); 

-- ensure that every book has one author:
ALTER TABLE ONLY bo_book
    ADD CONSTRAINT book_author_fk FOREIGN KEY (user_id) REFERENCES bo_user(id);

-- data structure for a chapter:
create sequence bo_chapter_id_seq;
create table bo_chapter (
   id integer primary Key default nextval('bo_chapter_id_seq'::regclass),
   user_id character(36) not null,
   book_id integer not null,  
   level integer default 1,
   sequence_in_book integer default 100,
   title character varying(80) not null,
   content text
   CONSTRAINT levelvalues CHECK ( level IN ( 1,2,3 ) )
) with (oids = false);

-- every chapter must be assigned to a book
ALTER TABLE ONLY bo_chapter
    ADD CONSTRAINT chapter_book_fk FOREIGN KEY (book_id) REFERENCES bo_book(id);  

-- every chapter must be have an author
ALTER TABLE ONLY bo_chapter
    ADD CONSTRAINT chapter_author_fk FOREIGN KEY (user_id) REFERENCES bo_user(id);  



create sequence bo_comment_id_seq;

create table bo_comment (
   id integer primary Key default nextval('bo_comment_id_seq'::regclass),
   chapter_id integer not null,
   content character varying(2000) not null,
   email character varying(255) not null
) with (oids = false);

Next: Open Source Software Stack: FreeBSD, CakePHP, Nginx, Bootstrap UP: TOC