## phpTodo - Web based todo lists                                             ##
## Copyright (C) 2005-07  Jason Frisvold <friz@godshell.com>                  ##
##                                                                            ##
## This program is free software; you can redistribute it and/or modify       ##
## it under the terms of the GNU General Public License as published by       ##
## the Free Software Foundation; either version 2 of the License, or          ##
## (at your option) any later version.                                        ##
##                                                                            ##
## This program is distributed in the hope that it will be useful,            ##
## but WITHOUT ANY WARRANTY; without even the implied warranty of             ##
## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the              ##
## GNU General Public License for more details.                               ##
##                                                                            ##
## You should have received a copy of the GNU General Public License          ##
## along with this program; if not, write to the Free Software                ##
## Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA ##

## $Id: phpTodo-0.1.1-0.2-update.sql,v 1.3 2007/03/06 17:58:04 xen0phage Exp $ ##

# You *should* be able to pipe this through mysql to update your database
# mysql -uroot -p phpTodo < phpTodo-0.1.1-0.2-update.sql

# If you have altered the tables, this will probably fail.  As always, BACK UP YOUR DATA
# before you try this!

ALTER TABLE users ADD (admin           TINYINT     UNSIGNED    NOT NULL DEFAULT 0,
                       full_name       CHAR(40));

CREATE TABLE user_prefs  (id              INTEGER     UNSIGNED    NOT NULL AUTO_INCREMENT,
                          user_id         INTEGER     UNSIGNED    NOT NULL,
                          priority_sort   TINYINT     UNSIGNED    NOT NULL DEFAULT 0,
                          status_sort     TINYINT     UNSIGNED    NOT NULL DEFAULT 0,
                          duedate_sort    TINYINT     UNSIGNED    NOT NULL DEFAULT 0,
                          subject_sort    TINYINT     UNSIGNED    NOT NULL DEFAULT 0,
                          show_complete   TINYINT     UNSIGNED    NOT NULL DEFAULT 0,
                          PRIMARY KEY     (id),
                          FOREIGN KEY     (user_id) REFERENCES users(id),
                          INDEX           (user_id));

DROP INDEX `primary` ON description;
ALTER TABLE description ADD (id              INTEGER     UNSIGNED    NOT NULL AUTO_INCREMENT,
                             PRIMARY KEY     (id),
                             FOREIGN KEY     (list_id) REFERENCES listentries(id),
                             INDEX           (list_id));

CREATE TABLE order_by    (id              TINYINT     UNSIGNED    NOT NULL AUTO_INCREMENT,
                          field_name      CHAR(30)    NOT NULL,
                          pretty_name     CHAR(20)    NOT NULL,
                          PRIMARY KEY     (id));

CREATE TABLE rss_feeds   (id              INTEGER     UNSIGNED    NOT NULL AUTO_INCREMENT,
                          user_id         INTEGER     UNSIGNED    NOT NULL,
                          secret          CHAR(40)    NOT NULL,
                          name            CHAR(15)    NOT NULL,
                          priority        TINYINT     UNSIGNED    NOT NULL,
                          status          TINYINT     UNSIGNED    NOT NULL,
                          order_by        TINYINT     UNSIGNED    NOT NULL,
                          max_limit       TINYINT     UNSIGNED    NOT NULL,
                          show_completed  TINYINT     UNSIGNED    NOT NULL,
                          PRIMARY KEY     (id),
                          FOREIGN KEY     (order_by)  REFERENCES order_by(id));

UPDATE users SET admin = 1, full_name = 'Administrative User' WHERE username = 'admin';
INSERT INTO user_prefs VALUES (NULL, 1, 0, 0, 0, 0, 0);

INSERT INTO order_by VALUES (NULL, '', 'None');
INSERT INTO order_by VALUES (NULL, 'priority,priority', 'Priority');
INSERT INTO order_by VALUES (NULL, 'status.id', 'Status');
INSERT INTO order_by VALUES (NULL, 'listentries.subject', 'Subject');
INSERT INTO order_by VALUES (NULL, 'listentries.due_date', 'Due Date');
INSERT INTO order_by VALUES (NULL, 'listentries.start_date', 'Start Date');