From 2230dd4e6b148346c0ec596b9e3e12996a762ed8 Mon Sep 17 00:00:00 2001 From: Frédéric Guillot Date: Thu, 22 May 2014 12:28:28 -0400 Subject: Code refactoring (add autoloader and change files organization) --- app/Schema/Mysql.php | 236 +++++++++++++++++++++++++++++++++++++++++++++ app/Schema/Sqlite.php | 259 ++++++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 495 insertions(+) create mode 100644 app/Schema/Mysql.php create mode 100644 app/Schema/Sqlite.php (limited to 'app/Schema') diff --git a/app/Schema/Mysql.php b/app/Schema/Mysql.php new file mode 100644 index 00000000..6764ad5d --- /dev/null +++ b/app/Schema/Mysql.php @@ -0,0 +1,236 @@ +exec(" + CREATE TABLE project_has_categories ( + id INT NOT NULL AUTO_INCREMENT, + name VARCHAR(255), + project_id INT, + PRIMARY KEY (id), + UNIQUE KEY `idx_project_category` (project_id, name), + FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE + ) ENGINE=InnoDB CHARSET=utf8" + ); + + $pdo->exec("ALTER TABLE tasks ADD COLUMN category_id INT DEFAULT 0"); +} + +function version_15($pdo) +{ + $pdo->exec("ALTER TABLE projects ADD COLUMN last_modified INT DEFAULT 0"); +} + +function version_14($pdo) +{ + $pdo->exec("ALTER TABLE users ADD COLUMN name VARCHAR(255)"); + $pdo->exec("ALTER TABLE users ADD COLUMN email VARCHAR(255)"); + $pdo->exec("ALTER TABLE users ADD COLUMN google_id VARCHAR(30)"); +} + +function version_13($pdo) +{ + $pdo->exec("ALTER TABLE users ADD COLUMN is_ldap_user TINYINT(1) DEFAULT 0"); +} + +function version_12($pdo) +{ + $pdo->exec(" + CREATE TABLE remember_me ( + id INT NOT NULL AUTO_INCREMENT, + user_id INT, + ip VARCHAR(40), + user_agent VARCHAR(255), + token VARCHAR(255), + sequence VARCHAR(255), + expiration INT, + date_creation INT, + FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, + PRIMARY KEY (id) + ) ENGINE=InnoDB CHARSET=utf8" + ); + + $pdo->exec(" + CREATE TABLE last_logins ( + id INT NOT NULL AUTO_INCREMENT, + auth_type VARCHAR(25), + user_id INT, + ip VARCHAR(40), + user_agent VARCHAR(255), + date_creation INT, + FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, + PRIMARY KEY (id), + INDEX (user_id) + ) ENGINE=InnoDB CHARSET=utf8" + ); +} + +function version_11($pdo) +{ +} + +function version_10($pdo) +{ +} + +function version_9($pdo) +{ +} + +function version_8($pdo) +{ +} + +function version_7($pdo) +{ +} + +function version_6($pdo) +{ +} + +function version_5($pdo) +{ +} + +function version_4($pdo) +{ +} + +function version_3($pdo) +{ +} + +function version_2($pdo) +{ +} + +function version_1($pdo) +{ + $pdo->exec(" + CREATE TABLE config ( + language CHAR(5) DEFAULT 'en_US', + webhooks_token VARCHAR(255), + timezone VARCHAR(50) DEFAULT 'UTC' + ) ENGINE=InnoDB CHARSET=utf8 + "); + + $pdo->exec(" + CREATE TABLE users ( + id INT NOT NULL AUTO_INCREMENT, + username VARCHAR(50), + password VARCHAR(255), + is_admin TINYINT DEFAULT 0, + default_project_id INT DEFAULT 0, + PRIMARY KEY (id) + ) ENGINE=InnoDB CHARSET=utf8 + "); + + $pdo->exec(" + CREATE TABLE projects ( + id INT NOT NULL AUTO_INCREMENT, + name VARCHAR(50) UNIQUE, + is_active TINYINT DEFAULT 1, + token VARCHAR(255), + PRIMARY KEY (id) + ) ENGINE=InnoDB CHARSET=utf8 + "); + + $pdo->exec(" + CREATE TABLE project_has_users ( + id INT NOT NULL AUTO_INCREMENT, + project_id INT, + user_id INT, + PRIMARY KEY (id), + UNIQUE KEY `idx_project_user` (project_id, user_id), + FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE, + FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE + ) ENGINE=InnoDB CHARSET=utf8 + "); + + $pdo->exec(" + CREATE TABLE columns ( + id INT NOT NULL AUTO_INCREMENT, + title VARCHAR(255), + position INT NOT NULL, + project_id INT NOT NULL, + task_limit INT DEFAULT '0', + UNIQUE KEY `idx_title_project` (title, project_id), + PRIMARY KEY (id), + FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE + ) ENGINE=InnoDB CHARSET=utf8 + "); + + $pdo->exec(" + CREATE TABLE tasks ( + id INT NOT NULL AUTO_INCREMENT, + title VARCHAR(255), + description TEXT, + date_creation INT, + date_completed INT, + date_due INT, + color_id VARCHAR(50), + project_id INT, + column_id INT, + owner_id INT DEFAULT '0', + position INT, + score INT, + is_active TINYINT DEFAULT 1, + PRIMARY KEY (id), + INDEX `idx_task_active` (is_active), + FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE, + FOREIGN KEY(column_id) REFERENCES columns(id) ON DELETE CASCADE + ) ENGINE=InnoDB CHARSET=utf8 + "); + + $pdo->exec(" + CREATE TABLE comments ( + id INT NOT NULL AUTO_INCREMENT, + task_id INT, + user_id INT, + date INT, + comment TEXT, + PRIMARY KEY (id), + FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE, + FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE + ) ENGINE=InnoDB CHARSET=utf8 + "); + + $pdo->exec(" + CREATE TABLE actions ( + id INT NOT NULL AUTO_INCREMENT, + project_id INT, + event_name VARCHAR(50), + action_name VARCHAR(50), + PRIMARY KEY (id), + FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE + ) ENGINE=InnoDB CHARSET=utf8 + "); + + $pdo->exec(" + CREATE TABLE action_has_params ( + id INT NOT NULL AUTO_INCREMENT, + action_id INT, + name VARCHAR(50), + value VARCHAR(50), + PRIMARY KEY (id), + FOREIGN KEY(action_id) REFERENCES actions(id) ON DELETE CASCADE + ) ENGINE=InnoDB CHARSET=utf8 + "); + + $pdo->exec(" + INSERT INTO users + (username, password, is_admin) + VALUES ('admin', '".\password_hash('admin', PASSWORD_BCRYPT)."', '1') + "); + + $pdo->exec(" + INSERT INTO config + (webhooks_token) + VALUES ('".\Model\Base::generateToken()."') + "); +} diff --git a/app/Schema/Sqlite.php b/app/Schema/Sqlite.php new file mode 100644 index 00000000..0bb4de8d --- /dev/null +++ b/app/Schema/Sqlite.php @@ -0,0 +1,259 @@ +exec(" + CREATE TABLE project_has_categories ( + id INTEGER PRIMARY KEY, + name TEXT COLLATE NOCASE, + project_id INT, + UNIQUE (project_id, name), + FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE + )" + ); + + $pdo->exec("ALTER TABLE tasks ADD COLUMN category_id INTEGER DEFAULT 0"); +} + +function version_15($pdo) +{ + $pdo->exec("ALTER TABLE projects ADD COLUMN last_modified INTEGER DEFAULT 0"); +} + +function version_14($pdo) +{ + $pdo->exec("ALTER TABLE users ADD COLUMN name TEXT"); + $pdo->exec("ALTER TABLE users ADD COLUMN email TEXT"); + $pdo->exec("ALTER TABLE users ADD COLUMN google_id TEXT"); +} + +function version_13($pdo) +{ + $pdo->exec("ALTER TABLE users ADD COLUMN is_ldap_user INTEGER DEFAULT 0"); +} + +function version_12($pdo) +{ + $pdo->exec( + 'CREATE TABLE remember_me ( + id INTEGER PRIMARY KEY, + user_id INTEGER, + ip TEXT, + user_agent TEXT, + token TEXT, + sequence TEXT, + expiration INTEGER, + date_creation INTEGER, + FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE + )' + ); + + $pdo->exec( + 'CREATE TABLE last_logins ( + id INTEGER PRIMARY KEY, + auth_type TEXT, + user_id INTEGER, + ip TEXT, + user_agent TEXT, + date_creation INTEGER, + FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE + )' + ); + + $pdo->exec('CREATE INDEX last_logins_user_idx ON last_logins(user_id)'); +} + +function version_11($pdo) +{ + $pdo->exec( + 'ALTER TABLE comments RENAME TO comments_bak' + ); + + $pdo->exec( + 'CREATE TABLE comments ( + id INTEGER PRIMARY KEY, + task_id INTEGER, + user_id INTEGER, + date INTEGER, + comment TEXT, + FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE, + FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE + )' + ); + + $pdo->exec( + 'INSERT INTO comments SELECT * FROM comments_bak' + ); + + $pdo->exec( + 'DROP TABLE comments_bak' + ); +} + +function version_10($pdo) +{ + $pdo->exec( + 'CREATE TABLE actions ( + id INTEGER PRIMARY KEY, + project_id INTEGER, + event_name TEXT, + action_name TEXT, + FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE + )' + ); + + $pdo->exec( + 'CREATE TABLE action_has_params ( + id INTEGER PRIMARY KEY, + action_id INTEGER, + name TEXT, + value TEXT, + FOREIGN KEY(action_id) REFERENCES actions(id) ON DELETE CASCADE + )' + ); +} + +function version_9($pdo) +{ + $pdo->exec("ALTER TABLE tasks ADD COLUMN date_due INTEGER"); +} + +function version_8($pdo) +{ + $pdo->exec( + 'CREATE TABLE comments ( + id INTEGER PRIMARY KEY, + task_id INTEGER, + user_id INTEGER, + date INTEGER, + comment TEXT, + FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE, + FOREIGN KEY(user_id) REFERENCES tasks(id) ON DELETE CASCADE + )' + ); +} + +function version_7($pdo) +{ + $pdo->exec(" + CREATE TABLE project_has_users ( + id INTEGER PRIMARY KEY, + project_id INTEGER, + user_id INTEGER, + FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE, + FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, + UNIQUE(project_id, user_id) + ) + "); +} + +function version_6($pdo) +{ + $pdo->exec("ALTER TABLE columns ADD COLUMN task_limit INTEGER DEFAULT '0'"); +} + +function version_5($pdo) +{ + $pdo->exec("ALTER TABLE tasks ADD COLUMN score INTEGER"); +} + +function version_4($pdo) +{ + $pdo->exec("ALTER TABLE config ADD COLUMN timezone TEXT DEFAULT 'UTC'"); +} + +function version_3($pdo) +{ + $pdo->exec('ALTER TABLE projects ADD COLUMN token TEXT'); + + // For each existing project, assign a different token + $rq = $pdo->prepare("SELECT id FROM projects WHERE token IS NULL"); + $rq->execute(); + $results = $rq->fetchAll(\PDO::FETCH_ASSOC); + + if ($results !== false) { + + foreach ($results as &$result) { + $rq = $pdo->prepare('UPDATE projects SET token=? WHERE id=?'); + $rq->execute(array(\Model\Base::generateToken(), $result['id'])); + } + } +} + +function version_2($pdo) +{ + $pdo->exec('ALTER TABLE tasks ADD COLUMN date_completed INTEGER'); + $pdo->exec('UPDATE tasks SET date_completed=date_creation WHERE is_active=0'); +} + +function version_1($pdo) +{ + $pdo->exec(" + CREATE TABLE config ( + language TEXT, + webhooks_token TEXT + ) + "); + + $pdo->exec(" + CREATE TABLE users ( + id INTEGER PRIMARY KEY, + username TEXT, + password TEXT, + is_admin INTEGER DEFAULT 0, + default_project_id INTEGER DEFAULT 0 + ) + "); + + $pdo->exec(" + CREATE TABLE projects ( + id INTEGER PRIMARY KEY, + name TEXT NOCASE UNIQUE, + is_active INTEGER DEFAULT 1 + ) + "); + + $pdo->exec(" + CREATE TABLE columns ( + id INTEGER PRIMARY KEY, + title TEXT, + position INTEGER, + project_id INTEGER, + FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE, + UNIQUE (title, project_id) + ) + "); + + $pdo->exec(" + CREATE TABLE tasks ( + id INTEGER PRIMARY KEY, + title TEXT, + description TEXT, + date_creation INTEGER, + color_id TEXT, + project_id INTEGER, + column_id INTEGER, + owner_id INTEGER DEFAULT '0', + position INTEGER, + is_active INTEGER DEFAULT 1, + FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE, + FOREIGN KEY(column_id) REFERENCES columns(id) ON DELETE CASCADE + ) + "); + + $pdo->exec(" + INSERT INTO users + (username, password, is_admin) + VALUES ('admin', '".\password_hash('admin', PASSWORD_BCRYPT)."', '1') + "); + + $pdo->exec(" + INSERT INTO config + (language, webhooks_token) + VALUES ('en_US', '".\Model\Base::generateToken()."') + "); +} -- cgit v1.2.3