## 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: todo.sql,v 1.29 2007/03/06 17:58:04 xen0phage Exp $ ## # DROP DATABASE phpTodo; # CREATE DATABASE phpTodo; # USE phpTodo; CREATE TABLE users (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, username CHAR(15) NOT NULL, password CHAR(40) NOT NULL, admin TINYINT UNSIGNED NOT NULL DEFAULT 0, full_name CHAR(40), email CHAR(255) NOT NULL, PRIMARY KEY (id), UNIQUE (username), INDEX (password)); CREATE TABLE priority (id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, name CHAR(20) NOT NULL, priority TINYINT NOT NULL, PRIMARY KEY (id)); CREATE TABLE status (id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, status CHAR(20) NOT NULL, PRIMARY KEY (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 category (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, category CHAR(15) NOT NULL, user_id INTEGER UNSIGNED NOT NULL, PRIMARY KEY (id), INDEX (user_id)); 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, category_filter INTEGER UNSIGNED NOT NULL DEFAULT 0, show_complete TINYINT UNSIGNED NOT NULL DEFAULT 0, sort_stabilizer CHAR(15) NOT NULL DEFAULT 'taskid', sort_order CHAR(3) NOT NULL DEFAULT 'asc', PRIMARY KEY (id), FOREIGN KEY (user_id) REFERENCES users(id), INDEX (user_id)); CREATE TABLE sessions (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, phpsessid CHAR(32) NOT NULL, last INTEGER UNSIGNED NOT NULL, user_id INTEGER UNSIGNED NOT NULL, PRIMARY KEY (id), FOREIGN KEY (user_id) REFERENCES users(id), INDEX (last), INDEX (phpsessid)); CREATE TABLE listentries (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, user_id INTEGER UNSIGNED NOT NULL, subject CHAR(75) NOT NULL, start_date DATETIME, due_date DATETIME, create_date DATETIME, complete_date DATETIME, priority_id TINYINT UNSIGNED NOT NULL, status_id TINYINT UNSIGNED NOT NULL, category_id INTEGER UNSIGNED NOT NULL DEFAULT 1, last_modified TIMESTAMP, PRIMARY KEY (id), INDEX (user_id), INDEX (category_id), INDEX (priority_id), INDEX (status_id), FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (priority_id) REFERENCES priority(id), FOREIGN KEY (status_id) REFERENCES status(id), FOREIGN KEY (category_id) REFERENCES category(id)); CREATE TABLE description (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, list_id INTEGER UNSIGNED NOT NULL, description TEXT, nextaction TEXT, PRIMARY KEY (id), FOREIGN KEY (list_id) REFERENCES listentries(id), INDEX (list_id)); CREATE TABLE feeds (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, user_id INTEGER UNSIGNED NOT NULL, secret CHAR(15) NOT NULL, name CHAR(15) NOT NULL, version CHAR(8) NOT NULL DEFAULT 'RSS0.91', priority TINYINT UNSIGNED NOT NULL, status TINYINT UNSIGNED NOT NULL, category INTEGER 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 (category) REFERENCES category(id), FOREIGN KEY (order_by) REFERENCES order_by(id), INDEX (user_id), INDEX (secret), INDEX (name)); INSERT INTO users VALUES (NULL, 'admin', SHA1('password'), 1, 'Administrative User', 'admin@example.com'); INSERT INTO priority VALUES (NULL, 'High', 1); INSERT INTO priority VALUES (NULL, 'Medium', 5); INSERT INTO priority VALUES (NULL, 'Low', 10); INSERT INTO priority VALUES (NULL, 'None', 99); INSERT INTO status VALUES (1, 'Not Started'); INSERT INTO status VALUES (2, 'Deferred'); INSERT INTO status VALUES (3, 'Waiting on Someone'); INSERT INTO status VALUES (50, 'In Progress'); INSERT INTO status VALUES (98, 'Cancelled'); INSERT INTO status VALUES (99, 'Completed'); 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'); INSERT INTO category VALUES (NULL, 'None', 0); INSERT INTO category VALUES (NULL, 'Personal', 0); INSERT INTO category VALUES (NULL, 'Work', 0); INSERT INTO category VALUES (NULL, 'Other', 0); INSERT INTO user_prefs VALUES (NULL, 1, 0, 0, 0, 0, 0, 0, 'none', 'asc'); # Change the following line to reflect the correct user, host, database name, and password # GRANT insert, update, delete, select ON phpTodo.* TO phpTodo@localhost IDENTIFIED BY 'password';