summaryrefslogtreecommitdiffstats
path: root/docs/db-migrator
diff options
context:
space:
mode:
authorLiam Fallon <liam.fallon@est.tech>2021-10-07 10:28:03 +0000
committerGerrit Code Review <gerrit@onap.org>2021-10-07 10:28:03 +0000
commitd1bae74bae23486e3233c7c14db4a09b7fa10696 (patch)
treeced3810bb451d7c6b6797e46c7fde59c2898c603 /docs/db-migrator
parentdd48a50c9e8d4736f2e64c1763dcafb1f4efdde0 (diff)
parentc90b1ed0de40349ff116437fe2d16e67a0c4d5a4 (diff)
Merge "Added documentation for policy db-migrator"
Diffstat (limited to 'docs/db-migrator')
-rw-r--r--docs/db-migrator/policy-db-migrator.rst297
1 files changed, 297 insertions, 0 deletions
diff --git a/docs/db-migrator/policy-db-migrator.rst b/docs/db-migrator/policy-db-migrator.rst
new file mode 100644
index 00000000..64e7c6d8
--- /dev/null
+++ b/docs/db-migrator/policy-db-migrator.rst
@@ -0,0 +1,297 @@
+.. This work is licensed under a Creative Commons Attribution
+.. 4.0 International License.
+.. http://creativecommons.org/licenses/by/4.0
+
+Using Policy DB Migrator
+########################
+
+Policy DB Migrator is a set of shell scripts used to
+install the database tables required to run ONAP Policy Framework.
+
+.. note::
+ Currently the Istanbul versions of the PAP and API components require
+ ``db-migrator`` to run prior to initialization.
+
+Package contents
+================
+
+Policy DB Migrator is run as a docker container and consists of the following scripts:
+
+.. code::
+ :number-lines:
+
+ prepare_upgrade.sh
+ prepare_downgrade.sh
+ db-migrator
+
+
+``prepare_upgrade.sh`` is included as part of the docker image and is used
+to copy the upgrade sql files to the run directory.
+This script takes one parameter: <SCHEMA NAME>.
+
+``prepare_downgrade.sh`` is included as part of the docker image and is used
+to copy the downgrade sql files to the run directory.
+This script takes one parameter: <SCHEMA NAME>.
+
+``db-migrator`` is included as part of the docker image and is used
+to run either the upgrade or downgrade operation depending on user requirements.
+This script can take up to four parameters:
+
+.. list-table::
+ :widths: 20 20 20
+ :header-rows: 1
+
+ * - Parameter Name
+ - Parameter flag
+ - Value (example)
+ * - operation
+ - -o
+ - upgrade/downgrade/report
+ * - schema
+ - -s
+ - policyadmin
+ * - to
+ - -t
+ - 0800/0900
+ * - from
+ - -f
+ - 0800/0900
+
+The container also consists of several sql files which are used to upgrade/downgrade
+the policy database.
+
+The following environment variables need to be set to enable ``db-migrator``
+to run and connect to the database.
+
+.. list-table::
+ :widths: 20 20
+ :header-rows: 1
+
+ * - Name
+ - Value (example)
+ * - SQL_HOST
+ - mariadb
+ * - SQL_DB
+ - policyadmin
+ * - SQL_USER
+ - policy_user
+ * - SQL_PASSWORD
+ - policy_user
+ * - POLICY_HOME
+ - /opt/app/policy
+
+Prepare Upgrade
+===============
+
+Prior to upgrading the following script is run:
+
+.. code::
+
+ /opt/app/policy/bin/prepare_upgrade.sh <SCHEMA NAME>
+
+This will copy the upgrade files from ``/home/policy/sql`` to ``$POLICY_HOME/etc/db/migration/<SCHEMA NAME>/sql/``
+
+Each individual sql file that makes up that release will be run as part of the upgrade.
+
+
+Prepare Downgrade
+=================
+
+Prior to downgrading the following script is run:
+.. code::
+
+ /opt/app/policy/bin/prepare_downgrade.sh <SCHEMA NAME>
+
+This will copy the downgrade files from ``/home/policy/sql`` to ``$POLICY_HOME/etc/db/migration/<SCHEMA NAME>/sql/``
+
+Each individual sql file that makes up that release will be run as part of the downgrade.
+
+Upgrade
+=======
+
+.. code::
+
+ /opt/app/policy/bin/db-migrator -s <SCHEMA NAME> -o upgrade -f 0800 -t 0900
+
+If the ``-f`` and ``-t`` flags are not specified, the script will attempt to run all available
+sql files greater than the current version.
+
+The script will return either 1 or 0 depending on successful completion.
+
+Downgrade
+=========
+
+.. code::
+
+ /opt/app/policy/bin/db-migrator -s <SCHEMA NAME> -o downgrade -f 0900 -t 0800
+
+If the ``-f`` and ``-t`` flags are not specified, the script will attempt to run all available
+sql files less than the current version.
+
+The script will return either 1 or 0 depending on successful completion.
+
+Logging
+=======
+
+ .. container:: paragraph
+
+After every upgrade/downgrade ``db-migrator`` runs the report operation to show the
+contents of the db-migrator log table.
+
+.. code::
+
+ /opt/app/policy/bin/db-migrator -s <SCHEMA NAME> -o report
+
+Console output will also show the sql script command as in the example below:
+
+.. code::
+
+ upgrade 0100-jpapdpgroup_properties.sql
+ --------------
+ CREATE TABLE IF NOT EXISTS jpapdpgroup_properties (name VARCHAR(120) NULL, version VARCHAR(20) NULL,
+ PROPERTIES VARCHAR(255) NULL, PROPERTIES_KEY VARCHAR(255) NULL)
+
+
+migration schema
+================
+
+The migration schema contains two tables which belong to ``db-migrator``.
+
+* schema_versions - table to store the schema version currently installed by ``db-migrator``
+
+.. list-table::
+ :widths: 20 20
+ :header-rows: 1
+
+ * - name
+ - version
+ * - policyadmin
+ - 0900
+
+* policyadmin_schema_changelog - table which stores a record of each sql file that has been run
+
+.. list-table::
+ :widths: 10 40 10 10 10 20 10 20
+ :header-rows: 1
+
+ * - ID
+ - script
+ - operation
+ - from_version
+ - to_version
+ - tag
+ - success
+ - atTime
+ * - 1
+ - 0100-jpapdpgroup_properties.sql
+ - upgrade
+ - 0
+ - 0800
+ - 1309210909250800u
+ - 1
+ - 2021-09-13 09:09:26
+
+* ID: Sequence number of the operation
+* script: name of the sql script which was run
+* operation: operation type - upgrade/downgrade
+* from_version: starting version
+* to_version: target version
+* tag: tag to identify operation batch
+* success: 1 if script succeeded and 0 if it failed
+* atTime: time script was run
+
+
+Partial Upgrade/Downgrade
+=========================
+
+If an upgrade or downgrade ends with a failure status (success=0) the next time an upgrade
+or downgrade is run it will start from the point of failure rather than re-run scripts
+that succeeded. This allows the user to perform a partial upgrade or downgrade depending
+on their requirements.
+
+Running db-migrator
+===================
+
+The script that runs ``db-migrator`` is part of the database configuration and is in the following directory:
+
+.. code::
+
+ oom/kubernetes/policy/resources/config/db_migrator_policy_init.sh
+
+This script is mounted from the host file system to the policy-db-migrator container.
+It is setup to run an upgrade by default.
+
+.. code::
+
+ /opt/app/policy/bin/prepare_upgrade.sh ${SQL_DB}
+ /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o upgrade
+ rc=$?
+ /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o report
+ exit $rc
+
+The following table describes what each line does:
+
+.. list-table::
+ :widths: 30 30
+ :header-rows: 1
+
+ * - code
+ - description
+ * - /opt/app/policy/bin/prepare_upgrade.sh ${SQL_DB}
+ - prepare the upgrade scripts for the <SQL_DB> schema
+ * - /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o upgrade
+ - run the upgrade
+ * - rc=$?
+ - assign the return code from db-migrator to a variable
+ * - /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o report
+ - run the db-migrator report for the <SQL_DB> schema
+ * - exit $rc
+ - exit with the return code from db-migrator
+
+To alter how ``db-migrator`` is run the first two lines need to be modified.
+The first line can be changed to call either ``prepare_upgrade.sh`` or ``prepare_downgrade.sh``.
+The second line can be changed to use different input parameters for ``db-migrator`` :
+
+.. list-table::
+ :widths: 10 20 10
+ :header-rows: 1
+
+ * - flag
+ - value
+ - required
+ * - ``-o``
+ - upgrade/downgrade
+ - ``Y``
+ * - ``-s``
+ - ${SQL_DB}
+ - ``Y``
+ * - ``-f``
+ - current version (e.g. 0800)
+ - ``N``
+ * - ``-t``
+ - target version (e.g. 0900)
+ - ``N``
+
+This is an example of how a downgrade from version 0900 to version 0800 could be run:
+
+.. code::
+
+ /opt/app/policy/bin/prepare_downgrade.sh ${SQL_DB}
+ /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o downgrade -f 0900 -t 0800
+ rc=$?
+ /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o report
+ exit $rc
+
+Additional Information
+======================
+If the target version of your upgrade or downgrade is the same as the current version,
+no sql files are run.
+
+If an upgrade is run on a database where tables already exist in the policy schema, the
+current schema version is set to 0800 and only sql scripts from later versions are run.
+
+.. note::
+ It is advisable to take a backup of your database prior to running this utility.
+ Please refer to the mariadb documentation on how to do this.
+
+End of Document