X-Git-Url: https://projects.mako.cc/source/selectricity-live/blobdiff_plain/fc0a6a8d7ea15bcdb27ebdd58721401c7045c6e0..45d48f668c0159ebae18fecb76b3b94f79f87d24:/db/create.sql?ds=inline diff --git a/db/create.sql b/db/create.sql old mode 100755 new mode 100644 index e0c264f..c28dd36 --- a/db/create.sql +++ b/db/create.sql @@ -3,15 +3,19 @@ drop table if exists elections; create table elections ( - id int NOT NULL auto_increment, + id int NOT NULL auto_increment, name varchar(100) NOT NULL, description TEXT NOT NULL, anonymous tinyint NOT NULL DEFAULT 1, startdate datetime, enddate datetime NOT NULL, active tinyint NOT NULL DEFAULT 0, + viewable tinyint NOT NULL DEFAULT 1, + notices tinyint NOT NULL DEFAULT 0, user_id int NULL, - quickvote tinyint NOT NULL DEFAULT 0, + quickuser varchar(255) NULL, #stores session_id for quickvote creators + election_method varchar(100) DEFAULT 'ssd', + `type` varchar(100) NOT NULL, primary key (id), constraint fk_user_election foreign key (user_id) references users(id) ); @@ -25,9 +29,20 @@ create table candidates ( election_id int NOT NULL, name varchar(100) NOT NULL, description text NULL, - picture_filename varchar(200), - picture_data blob, - picture_type varchar(100), + primary key (id) +); + +# CREATE pictures TABLE +##################################### + +drop table if exists pictures; +create table pictures ( + id int NOT NULL auto_increment, + filename varchar(200), + data blob, + filetype varchar(100), + candidate_id int NULL, + constraint fk_candidate_picture foreign key (candidate_id) references candidates(id), primary key (id) ); @@ -43,6 +58,7 @@ create table voters ( election_id int NOT NULL, session_id varchar(32) DEFAULT NULL, ipaddress varchar(32) DEFAULT NULL, + `type` varchar(100) NOT NULL, constraint fk_election_voter foreign key (election_id) references election(id), primary key (id) ); @@ -68,6 +84,7 @@ create table votes ( id int NOT NULL auto_increment, voter_id int DEFAULT NULL, confirmed tinyint NOT NULL DEFAULT 0, + time datetime DEFAULT NULL, constraint fk_vote_voter foreign key (voter_id) references voters(id), primary key (id) ); @@ -84,26 +101,56 @@ create table rankings ( primary key (id) ); +# CREATE sessions TABLE +###################################### + +drop table if exists sessions; +create table sessions ( + id int(11) NOT NULL auto_increment, + sessid varchar(255), + data text, + updated_at datetime DEFAULT NULL, + primary key (id), + index session_index (sessid) +); + # CREATE users TABLE ##################################### -DROP TABLE IF EXISTS `users`; -CREATE TABLE `users` ( - `id` int(11) NOT NULL auto_increment, - `login` varchar(80) NOT NULL default '', - `salted_password` varchar(40) NOT NULL default '', - `email` varchar(60) NOT NULL default '', - `firstname` varchar(40) default NULL, - `lastname` varchar(40) default NULL, - `salt` varchar(40) NOT NULL default '', - `verified` int(11) default '0', - `role` varchar(40) default NULL, - `security_token` varchar(40) default NULL, - `token_expiry` datetime default NULL, - `created_at` datetime default NULL, - `updated_at` datetime default NULL, - `logged_in_at` datetime default NULL, - `deleted` int(11) default '0', - `delete_after` datetime default NULL, - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; +#DROP TABLE IF EXISTS `users`; +#CREATE TABLE `users` ( +# `id` int(11) NOT NULL auto_increment, +# `login` varchar(80) NOT NULL default '', +# `salted_password` varchar(40) NOT NULL default '', +# `email` varchar(60) NOT NULL default '', +# `firstname` varchar(40) default NULL, +# `lastname` varchar(40) default NULL, +# `salt` varchar(40) NOT NULL default '', +# `verified` int(11) default '0', +# `role` varchar(40) default NULL, +# `security_token` varchar(40) default NULL, +# `token_expiry` datetime default NULL, +# `created_at` datetime default NULL, +# `updated_at` datetime default NULL, +# `logged_in_at` datetime default NULL, +# `deleted` int(11) default '0', +# `delete_after` datetime default NULL, +# PRIMARY KEY (`id`) +#) ENGINE=InnoDB DEFAULT CHARSET=latin1; +#Following is the new users table that goes with acts_as_authenticated +#Is simpler for now, saving the old table while in transition between +#the two for ideas on what attributes may be helpful/necessary +drop table if exists users; +create table users ( + id int not null auto_increment, + login text, + ip text not null, + email text, + crypted_password varchar(40), + salt varchar(40), + created_at datetime, + updated_at datetime, + remember_token text, + remember_token_expires_at datetime, + primary key(id) +);