fix security issue

[selectricity-live] / db / create.sql

diff --git a/db/create.sql b/db/create.sql
old mode 100755 (executable)
new mode 100644 (file)
index ff10db3..2acf948
--- a/db/create.sql
+++ b/db/create.sql
@@ -3,15 +3,19 @@
 
 drop table if exists elections;
 create table elections (
 
 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,
  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,
  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)
 );
  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,
  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)
 );
 
  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,
  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)
 );
  constraint fk_election_voter foreign key (election_id) references election(id),
  primary key (id)
 );


@@ -85,32 +101,19 @@ create table rankings (
  primary key (id)
 );
 
  primary key (id)
 );
 

-# 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;
-
-#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
+# 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)
+);
+

 drop table if exists users;
 create table users (
        id                        int         not null auto_increment,
 drop table if exists users;
 create table users (
        id                        int         not null auto_increment,


@@ -124,4 +127,4 @@ create table users (
        remember_token            text,
        remember_token_expires_at datetime,
        primary key(id)
        remember_token            text,
        remember_token_expires_at datetime,
        primary key(id)

-);
\ No newline at end of file
+);