Code refactoring (add autoloader and change files organization)

2 files changed, 495 insertions, 0 deletions

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 @@
+<?php
+
+namespace Schema;
+
+const VERSION = 16;
+
+function version_16($pdo)
+{
+    $pdo->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 @@
+<?php
+
+namespace Schema;
+
+const VERSION = 16;
+
+function version_16($pdo)
+{
+    $pdo->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()."')
+    ");
+}