From e2c38e0ff43f4f719126bf03ff89487e37ec797d Mon Sep 17 00:00:00 2001 From: Tony Mack Date: Tue, 21 Oct 2008 19:13:26 +0000 Subject: [PATCH] migration scrips for conf_files enhancements --- migrations/010-down-slice-conf_file.sql | 20 +++++++++++++++ migrations/010-up-slice-conf_file.sql | 34 +++++++++++++++++++++++++ 2 files changed, 54 insertions(+) create mode 100644 migrations/010-down-slice-conf_file.sql create mode 100644 migrations/010-up-slice-conf_file.sql diff --git a/migrations/010-down-slice-conf_file.sql b/migrations/010-down-slice-conf_file.sql new file mode 100644 index 00000000..47954c73 --- /dev/null +++ b/migrations/010-down-slice-conf_file.sql @@ -0,0 +1,20 @@ +DROP VIEW view_conf_files; + +ALTER TABLE conf_files DROP site_id; +ALTER TABLE conf_files DROP creator_id; +ALTER TABLE conf_files DROP last_modified_by_id; +ALTER TABLE conf_files DROP date_created; +ALTER TABLE conf_files DROP last_modified; + + +CREATE OR REPLACE VIEW view_conf_files AS +SELECT +conf_files.*, +COALESCE((SELECT node_ids FROM conf_file_nodes WHERE conf_file_nodes.conf_file_id = conf_files.conf_file_id), '{}') AS node_ids, +COALESCE((SELECT nodegroup_ids FROM conf_file_nodegroups WHERE conf_file_nodegroups.conf_file_id = conf_files.conf_file_id), '{}') AS nodegroup_ids +FROM conf_files; + + +UPDATE plc_db_version SET subversion = 9; +SELECT subversion from plc_db_version; + diff --git a/migrations/010-up-slice-conf_file.sql b/migrations/010-up-slice-conf_file.sql new file mode 100644 index 00000000..70a66808 --- /dev/null +++ b/migrations/010-up-slice-conf_file.sql @@ -0,0 +1,34 @@ +ALTER TABLE conf_files ADD site_id integer NOT NULL REFERENCES sites; +ALTER TABLE conf_files ADD creator_id integer NOT NULL REFERENCES persons; +ALTER TABLE conf_files ADD last_modified_by_id integer NOT NULL REFERENCES persons; +ALTER TABLE conf_files ADD date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP; +ALTER TABLE conf_files ADD last_modified timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP; + +DROP VIEW view_conf_files; + +CREATE OR REPLACE VIEW view_conf_files AS +SELECT +conf_files.conf_file_id, +conf_files.enabled, +conf_files.source, +conf_files.dest, +conf_files.file_permissions, +conf_files.file_owner, +conf_files.file_group, +conf_files.preinstall_cmd, +conf_files.postinstall_cmd, +conf_files.error_cmd, +conf_files.ignore_cmd_errors, +conf_files.always_update, +conf_files.site_id, +conf_files.creator_id, +conf_files.last_modified_by_id, +CAST(date_part('epoch', conf_files.date_created) AS bigint) AS date_created, +CAST(date_part('epoch', conf_files.last_modified) AS bigint) AS last_modified, +COALESCE((SELECT node_ids FROM conf_file_nodes WHERE conf_file_nodes.conf_file_id = conf_files.conf_file_id), '{}') AS node_ids, +COALESCE((SELECT nodegroup_ids FROM conf_file_nodegroups WHERE conf_file_nodegroups.conf_file_id = conf_files.conf_file_id), '{}') AS nodegroup_ids +FROM conf_files; + + +UPDATE plc_db_version SET subversion = 10; +SELECT subversion from plc_db_version; -- 2.47.0