From owner-svn-src-user@FreeBSD.ORG Wed Apr 16 20:56:41 2014 Return-Path: Delivered-To: svn-src-user@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:1900:2254:206a::19:1]) (using TLSv1 with cipher ADH-AES256-SHA (256/256 bits)) (No client certificate requested) by hub.freebsd.org (Postfix) with ESMTPS id D47A2998; Wed, 16 Apr 2014 20:56:41 +0000 (UTC) Received: from svn.freebsd.org (svn.freebsd.org [IPv6:2001:1900:2254:2068::e6a:0]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client did not present a certificate) by mx1.freebsd.org (Postfix) with ESMTPS id A7E981076; Wed, 16 Apr 2014 20:56:41 +0000 (UTC) Received: from svn.freebsd.org ([127.0.1.70]) by svn.freebsd.org (8.14.8/8.14.8) with ESMTP id s3GKufrf034175; Wed, 16 Apr 2014 20:56:41 GMT (envelope-from des@svn.freebsd.org) Received: (from des@localhost) by svn.freebsd.org (8.14.8/8.14.8/Submit) id s3GKufbO034174; Wed, 16 Apr 2014 20:56:41 GMT (envelope-from des@svn.freebsd.org) Message-Id: <201404162056.s3GKufbO034174@svn.freebsd.org> From: Dag-Erling Smørgrav Date: Wed, 16 Apr 2014 20:56:41 +0000 (UTC) To: src-committers@freebsd.org, svn-src-user@freebsd.org Subject: svn commit: r264553 - user/des/fbp/db X-SVN-Group: user MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-BeenThere: svn-src-user@freebsd.org X-Mailman-Version: 2.1.17 Precedence: list List-Id: "SVN commit messages for the experimental " user" src tree" List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 16 Apr 2014 20:56:41 -0000 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 +);