Added doc for smoke testing db-migrator

Issue-ID: POLICY-3724
Change-Id: I7069e52cf7eb6c503b0c2d7cdf13bcfccef35c8e
Signed-off-by: ktimoney <kevin.timoney@est.tech>

1 files changed, 413 insertions, 0 deletions

diff --git a/docs/development/devtools/db-migrator-smoke.rst b/docs/development/devtools/db-migrator-smoke.rst
new file mode 100644
index 00000000..4aa41e46
--- /dev/null
+++ b/docs/development/devtools/db-migrator-smoke.rst
@@ -0,0 +1,413 @@
+.. This work is licensed under a  Creative Commons Attribution
+.. 4.0 International License.
+.. http://creativecommons.org/licenses/by/4.0
+
+Policy DB Migrator Smoke Tests
+##############################
+
+Prerequisites
+*************
+
+Check number of files in each release
+
+.. code::
+  :number-lines:
+
+    ls 0800/upgrade/*.sql | wc -l = 96
+    ls 0900/upgrade/*.sql | wc -l = 13
+    ls 0800/downgrade/*.sql | wc -l = 96
+    ls 0900/downgrade/*.sql | wc -l = 13
+
+Upgrade scripts
+===============
+
+.. code::
+  :number-lines:
+
+    /opt/app/policy/bin/prepare_upgrade.sh policyadmin
+    /opt/app/policy/bin/db-migrator -s policyadmin -o upgrade
+
+.. note::
+   You can also run db-migrator upgrade with the -t and -f options
+
+Downgrade scripts
+=================
+
+.. code::
+  :number-lines:
+
+    /opt/app/policy/bin/prepare_downgrade.sh policyadmin
+    /opt/app/policy/bin/db-migrator -s policyadmin -o downgrade -f 0900 -t 0800
+
+Db migrator initialization script
+=================================
+
+Update /oom/kubernetes/policy/resources/config/db_migrator_policy_init.sh with the appropriate upgrade/downgrade calls.
+
+The policy version you are deploying should either be an upgrade or downgrade from the current db migrator schema version.
+
+Every time you modify db_migrator_policy_init.sh you will have to undeploy, make and redeploy before updates are applied.
+
+1. Fresh Install
+****************
+
+.. list-table::
+   :widths: 60 20
+   :header-rows: 0
+
+   * - Number of files run
+     - 109
+   * - Tables in policyadmin
+     - 75
+   * - Records Added
+     - 109
+   * - schema_version
+     - 0900
+
+2. Downgrade to Honolulu (0800)
+*******************************
+
+Modify db_migrator_policy_init.sh - remove any lines referencing upgrade and add the 2 lines under "Downgrade scripts"
+
+Make/Redeploy to run downgrade.
+
+.. list-table::
+   :widths: 60 20
+   :header-rows: 0
+
+   * - Number of files run
+     - 13
+   * - Tables in policyadmin
+     - 73
+   * - Records Added
+     - 13
+   * - schema_version
+     - 0800
+
+3. Upgrade to Istanbul (0900)
+*****************************
+
+Modify db_migrator_policy_init.sh - remove any lines referencing downgrade and add the 2 lines under "Upgrade scripts".
+
+Make/Redeploy to run upgrade.
+
+.. list-table::
+   :widths: 60 20
+   :header-rows: 0
+
+   * - Number of files run
+     - 13
+   * - Tables in policyadmin
+     - 75
+   * - Records Added
+     - 13
+   * - schema_version
+     - 0900
+
+4. Upgrade to Istanbul (0900) without any information in the migration schema
+*****************************************************************************
+
+Ensure you are on release 0800. (This may require running a downgrade before starting the test)
+
+Drop db-migrator tables in migration schema:
+
+.. code::
+  :number-lines:
+
+    DROP TABLE schema_versions;
+    DROP TABLE policyadmin_schema_changelog;
+
+Modify db_migrator_policy_init.sh - remove any lines referencing downgrade and add the 2 lines under "Upgrade scripts".
+
+Make/Redeploy to run upgrade.
+
+.. list-table::
+   :widths: 60 20
+   :header-rows: 0
+
+   * - Number of files run
+     - 13
+   * - Tables in policyadmin
+     - 75
+   * - Records Added
+     - 13
+   * - schema_version
+     - 0900
+
+5. Upgrade to Istanbul (0900) after failed downgrade
+****************************************************
+
+Ensure you are on release 0900.
+
+Rename pdpstatistics table in policyadmin schema:
+
+.. code::
+
+    RENAME TABLE pdpstatistics TO backup_pdpstatistics;
+
+Modify db_migrator_policy_init.sh - remove any lines referencing upgrade and add the 2 lines under "Downgrade scripts"
+
+Make/Redeploy to run downgrade
+
+This should result in an error (last row in policyadmin_schema_changelog will have a success value of 0)
+
+Rename backup_pdpstatistic table in policyadmin schema:
+
+.. code::
+
+    RENAME TABLE backup_pdpstatistics TO pdpstatistics;
+
+Modify db_migrator_policy_init.sh - Remove any lines referencing downgrade and add the 2 lines under "Upgrade scripts"
+
+Make/Redeploy to run upgrade
+
+.. list-table::
+   :widths: 60 20
+   :header-rows: 0
+
+   * - Number of files run
+     - 11
+   * - Tables in policyadmin
+     - 75
+   * - Records Added
+     - 11
+   * - schema_version
+     - 0900
+
+6. Downgrade to Honolulu (0800) after failed downgrade
+******************************************************
+
+Ensure you are on release 0900.
+
+Add timeStamp column to papdpstatistics_enginestats:
+
+.. code::
+
+    ALTER TABLE jpapdpstatistics_enginestats ADD COLUMN timeStamp datetime DEFAULT NULL NULL AFTER UPTIME;
+
+Modify db_migrator_policy_init.sh - remove any lines referencing upgrade and add the 2 lines under "Downgrade scripts"
+
+Make/Redeploy to run downgrade
+
+This should result in an error (last row in policyadmin_schema_changelog will have a success value of 0)
+
+Remove timeStamp column from jpapdpstatistics_enginestats:
+
+.. code::
+
+    ALTER TABLE jpapdpstatistics_enginestats DROP COLUMN timeStamp;
+
+The config job will retry 5 times. If you make your fix before this limit is reached you won't need to redeploy.
+
+Redeploy to run downgrade
+
+.. list-table::
+   :widths: 60 20
+   :header-rows: 0
+
+   * - Number of files run
+     - 14
+   * - Tables in policyadmin
+     - 73
+   * - Records Added
+     - 14
+   * - schema_version
+     - 0800
+
+7. Downgrade to Honolulu (0800) after failed upgrade
+****************************************************
+
+Ensure you are on release 0800.
+
+Modify db_migrator_policy_init.sh - remove any lines referencing downgrade and add the 2 lines under "Upgrade scripts"
+
+Update pdpstatistics:
+
+.. code::
+
+    ALTER TABLE pdpstatistics ADD COLUMN POLICYUNDEPLOYCOUNT BIGINT DEFAULT NULL NULL AFTER POLICYEXECUTEDSUCCESSCOUNT;
+
+Make/Redeploy to run upgrade
+
+This should result in an error (last row in policyadmin_schema_changelog will have a success value of 0)
+
+Once the retry count has been reached, update pdpstatistics:
+
+.. code::
+
+    ALTER TABLE pdpstatistics DROP COLUMN POLICYUNDEPLOYCOUNT;
+
+Modify db_migrator_policy_init.sh - Remove any lines referencing upgrade and add the 2 lines under "Downgrade scripts"
+
+Make/Redeploy to run downgrade
+
+.. list-table::
+   :widths: 60 20
+   :header-rows: 0
+
+   * - Number of files run
+     - 7
+   * - Tables in policyadmin
+     - 73
+   * - Records Added
+     - 7
+   * - schema_version
+     - 0800
+
+8. Upgrade to Istanbul (0900) after failed upgrade
+**************************************************
+
+Ensure you are on release 0800.
+
+Modify db_migrator_policy_init.sh - remove any lines referencing downgrade and add the 2 lines under "Upgrade scripts"
+
+Update PDP table:
+
+.. code::
+
+    ALTER TABLE pdp ADD COLUMN LASTUPDATE datetime NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER HEALTHY;
+
+Make/Redeploy to run upgrade
+
+This should result in an error (last row in policyadmin_schema_changelog will have a success value of 0)
+
+Update PDP table:
+
+.. code::
+
+    ALTER TABLE pdp DROP COLUMN LASTUPDATE;
+
+The config job will retry 5 times. If you make your fix before this limit is reached you won't need to redeploy.
+
+Redeploy to run upgrade
+
+.. list-table::
+   :widths: 60 20
+   :header-rows: 0
+
+   * - Number of files run
+     - 14
+   * - Tables in policyadmin
+     - 75
+   * - Records Added
+     - 14
+   * - schema_version
+     - 0900
+
+9. Downgrade to Honolulu (0800) with data in pdpstatistics and jpapdpstatistics_enginestats
+*******************************************************************************************
+
+Ensure you are on release 0900.
+
+Check pdpstatistics and jpapdpstatistics_enginestats are populated with data.
+
+.. code::
+  :number-lines:
+
+    SELECT count(*) FROM pdpstatistics;
+    SELECT count(*) FROM jpapdpstatistics_enginestats;
+
+Modify db_migrator_policy_init.sh - remove any lines referencing upgrade and add the 2 lines under "Downgrade scripts"
+
+Make/Redeploy to run downgrade
+
+Check the tables to ensure the number  records is the same.
+
+.. code::
+  :number-lines:
+
+    SELECT count(*) FROM pdpstatistics;
+    SELECT count(*) FROM jpapdpstatistics_enginestats;
+
+Check pdpstatistics to ensure the primary key has changed:
+
+.. code::
+
+    SELECT column_name, constraint_name FROM information_schema.key_column_usage WHERE table_name='pdpstatistics';
+
+Check jpapdpstatistics_enginestats to ensure id column has been dropped and timestamp column added.
+
+.. code::
+
+    SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_name = 'jpapdpstatistics_enginestats';
+
+Check the pdp table to ensure the LASTUPDATE column has been dropped.
+
+.. code::
+
+    SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_name = 'pdp';
+
+
+.. list-table::
+   :widths: 60 20
+   :header-rows: 0
+
+   * - Number of files run
+     - 13
+   * - Tables in policyadmin
+     - 73
+   * - Records Added
+     - 13
+   * - schema_version
+     - 0800
+
+10. Upgrade to Istanbul (0900) with data in pdpstatistics and jpapdpstatistics_enginestats
+******************************************************************************************
+
+Ensure you are on release 0800.
+
+Check pdpstatistics and jpapdpstatistics_enginestats are populated with data.
+
+.. code::
+  :number-lines:
+
+    SELECT count(*) FROM pdpstatistics;
+    SELECT count(*) FROM jpapdpstatistics_enginestats;
+
+Modify db_migrator_policy_init.sh - remove any lines referencing downgrade and add the 2 lines under "Upgrade scripts"
+
+Make/Redeploy to run upgrade
+
+Check the tables to ensure the number  records is the same.
+
+.. code::
+  :number-lines:
+
+    SELECT count(*) FROM pdpstatistics;
+    SELECT count(*) FROM jpapdpstatistics_enginestats;
+
+Check pdpstatistics to ensure the primary key has changed:
+
+.. code::
+
+    SELECT column_name, constraint_name FROM information_schema.key_column_usage WHERE table_name='pdpstatistics';
+
+Check jpapdpstatistics_enginestats to ensure timestamp column has been dropped and id column added.
+
+.. code::
+
+    SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_name = 'jpapdpstatistics_enginestats';
+
+Check the pdp table to ensure the LASTUPDATE column has been added and the value has defaulted to the CURRENT_TIMESTAMP.
+
+.. code::
+
+    SELECT table_name, column_name, data_type, column_default FROM information_schema.columns WHERE table_name = 'pdp';
+
+.. list-table::
+   :widths: 60 20
+   :header-rows: 0
+
+   * - Number of files run
+     - 13
+   * - Tables in policyadmin
+     - 75
+   * - Records Added
+     - 13
+   * - schema_version
+     - 0900
+
+.. note::
+   The number of records added may vary depnding on the number of retries.
+
+End of Document