diff options
Diffstat (limited to 'app/Schema')
| -rw-r--r-- | app/Schema/Mysql.php | 33 | ||||
| -rw-r--r-- | app/Schema/Postgres.php | 33 | ||||
| -rw-r--r-- | app/Schema/Sqlite.php | 33 |
3 files changed, 96 insertions, 3 deletions
diff --git a/app/Schema/Mysql.php b/app/Schema/Mysql.php index 9c066625..e4be05b7 100644 --- a/app/Schema/Mysql.php +++ b/app/Schema/Mysql.php @@ -5,7 +5,38 @@ namespace Schema; use PDO; use Core\Security; -const VERSION = 44; +const VERSION = 45; + +function version_45($pdo) +{ + $pdo->exec('ALTER TABLE tasks ADD COLUMN date_moved INT DEFAULT 0'); + + /* Update tasks.date_moved from project_activities table if tasks.date_moved = null or 0. + * We take max project_activities.date_creation where event_name in task.create','task.move.column + * since creation date is always less than task moves + */ + $pdo->exec("UPDATE tasks + SET date_moved = ( + SELECT md + FROM ( + SELECT task_id, max(date_creation) md + FROM project_activities + WHERE event_name IN ('task.create', 'task.move.column') + GROUP BY task_id + ) src + WHERE id = src.task_id + ) + WHERE (date_moved IS NULL OR date_moved = 0) AND id IN ( + SELECT task_id + FROM ( + SELECT task_id, max(date_creation) md + FROM project_activities + WHERE event_name IN ('task.create', 'task.move.column') + GROUP BY task_id + ) src + ) + "); +} function version_44($pdo) { diff --git a/app/Schema/Postgres.php b/app/Schema/Postgres.php index 52130c96..92587273 100644 --- a/app/Schema/Postgres.php +++ b/app/Schema/Postgres.php @@ -5,7 +5,38 @@ namespace Schema; use PDO; use Core\Security; -const VERSION = 25; +const VERSION = 26; + +function version_26($pdo) +{ + $pdo->exec('ALTER TABLE tasks ADD COLUMN date_moved INT DEFAULT 0'); + + /* Update tasks.date_moved from project_activities table if tasks.date_moved = null or 0. + * We take max project_activities.date_creation where event_name in task.create','task.move.column + * since creation date is always less than task moves + */ + $pdo->exec("UPDATE tasks + SET date_moved = ( + SELECT md + FROM ( + SELECT task_id, max(date_creation) md + FROM project_activities + WHERE event_name IN ('task.create', 'task.move.column') + GROUP BY task_id + ) src + WHERE id = src.task_id + ) + WHERE (date_moved IS NULL OR date_moved = 0) AND id IN ( + SELECT task_id + FROM ( + SELECT task_id, max(date_creation) md + FROM project_activities + WHERE event_name IN ('task.create', 'task.move.column') + GROUP BY task_id + ) src + ) + "); +} function version_25($pdo) { diff --git a/app/Schema/Sqlite.php b/app/Schema/Sqlite.php index 37483945..7e0668e3 100644 --- a/app/Schema/Sqlite.php +++ b/app/Schema/Sqlite.php @@ -5,7 +5,38 @@ namespace Schema; use Core\Security; use PDO; -const VERSION = 43; +const VERSION = 44; + +function version_44($pdo) +{ + $pdo->exec('ALTER TABLE tasks ADD COLUMN date_moved INTEGER DEFAULT 0'); + + /* Update tasks.date_moved from project_activities table if tasks.date_moved = null or 0. + * We take max project_activities.date_creation where event_name in task.create','task.move.column + * since creation date is always less than task moves + */ + $pdo->exec("UPDATE tasks + SET date_moved = ( + SELECT md + FROM ( + SELECT task_id, max(date_creation) md + FROM project_activities + WHERE event_name IN ('task.create', 'task.move.column') + GROUP BY task_id + ) src + WHERE id = src.task_id + ) + WHERE (date_moved IS NULL OR date_moved = 0) AND id IN ( + SELECT task_id + FROM ( + SELECT task_id, max(date_creation) md + FROM project_activities + WHERE event_name IN ('task.create', 'task.move.column') + GROUP BY task_id + ) src + ) + "); +} function version_43($pdo) { |