summaryrefslogtreecommitdiff
path: root/schemas
diff options
context:
space:
mode:
Diffstat (limited to 'schemas')
-rw-r--r--schemas/.htaccess1
-rw-r--r--schemas/mysql.php165
-rw-r--r--schemas/sqlite.php167
3 files changed, 333 insertions, 0 deletions
diff --git a/schemas/.htaccess b/schemas/.htaccess
new file mode 100644
index 00000000..14249c50
--- /dev/null
+++ b/schemas/.htaccess
@@ -0,0 +1 @@
+Deny from all \ No newline at end of file
diff --git a/schemas/mysql.php b/schemas/mysql.php
new file mode 100644
index 00000000..3b9cc014
--- /dev/null
+++ b/schemas/mysql.php
@@ -0,0 +1,165 @@
+<?php
+
+namespace Schema;
+
+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 tasks(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/schemas/sqlite.php b/schemas/sqlite.php
new file mode 100644
index 00000000..8e5698f5
--- /dev/null
+++ b/schemas/sqlite.php
@@ -0,0 +1,167 @@
+<?php
+
+namespace Schema;
+
+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()."')
+ ");
+}
generated by cgit v1.2.3 (git 2.46.0) at 2024-11-24 00:35:06 +0000