Date: Wed, 16 Apr 2014 20:56:41 +0000 (UTC) From: Dag-Erling Smørgrav <des@FreeBSD.org> To: src-committers@freebsd.org, svn-src-user@freebsd.org Subject: svn commit: r264553 - user/des/fbp/db Message-ID: <201404162056.s3GKufbO034174@svn.freebsd.org>
next in thread | raw e-mail | index | archive | help
Author: des Date: Wed Apr 16 20:56:41 2014 New Revision: 264553 URL: http://svnweb.freebsd.org/changeset/base/264553 Log: SQL schema Added: user/des/fbp/db/fbp.sql Added: user/des/fbp/db/fbp.sql ============================================================================== --- /dev/null 00:00:00 1970 (empty, because file is newly added) +++ user/des/fbp/db/fbp.sql Wed Apr 16 20:56:41 2014 (r264553) @@ -0,0 +1,64 @@ +-- +-- $FreeBSD$ +-- + +drop table if exists persons cascade; +create table persons ( + id serial primary key, + login varchar not null, + realname varchar null, + password varchar not null default '*', + admin boolean not null default false, + active boolean not null default false, + unique(login) +); +insert into persons(login, realname, password, active, admin) + values('des', 'Dag-Erling Smørgrav', '*', true, true); +insert into persons(login, realname, password, active, admin) + values('kenneth36', 'Kenneth (36)', '*', true, false); + +drop table if exists polls cascade; +create table polls ( + id serial primary key, + owner integer not null, + title varchar(64) not null, + starts timestamp not null, + ends timestamp not null, + synopsis varchar(256) not null, + long text not null, + unique(title), + foreign key(owner) references persons(id) on delete cascade on update cascade +); + +drop table if exists questions cascade; +create table questions ( + id serial primary key, + poll integer not null, + rank integer not null, + short varchar(256) not null, + long text not null, + min_options integer not null default 1, + max_options integer not null default 1, + unique (poll, rank), + foreign key(poll) references polls(id) on delete cascade on update cascade +); + +drop table if exists options cascade; +create table options ( + id serial primary key, + question integer not null, + label varchar(256) not null, + foreign key(question) references questions(id) on delete cascade on update cascade +); + +drop table if exists votes cascade; +create table votes ( + id serial primary key, + voter integer not null, + question integer not null, + option integer not null, + unique(voter, option), + foreign key(voter) references persons(id) on delete cascade on update cascade, + foreign key(question) references questions(id) on delete cascade on update cascade, + foreign key(option) references options(id) on delete cascade on update cascade +);
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?201404162056.s3GKufbO034174>