Add generate schemas tool

This is a variant of a tool added to nova during their migration to
alembic. It will be helpful as we continue expanding the repo.

Change-Id: Ib27532005c0f297b2b328cccd8d9528731ec1179
Signed-off-by: Stephen Finucane <stephenfin@redhat.com>

2 files changed, 144 insertions, 1 deletions

diff --git a/keystone/common/sql/expand_repo/versions/001_expand_initial_migration.py b/keystone/common/sql/expand_repo/versions/001_expand_initial_migration.py
index b12cb8556..17315889a 100644
--- a/keystone/common/sql/expand_repo/versions/001_expand_initial_migration.py
+++ b/keystone/common/sql/expand_repo/versions/001_expand_initial_migration.py
@@ -23,6 +23,15 @@ from keystone.identity.mapping_backends import mapping as mapping_backend
 CONF = keystone.conf.CONF
 LOG = log.getLogger(__name__)
 
+# FIXME(stephenfin): Remove this as soon as we're done reworking the
+# migrations. Until then, this is necessary to allow us to use the native
+# sqlalchemy-migrate tooling (which won't register opts). Alternatively, maybe
+# the server default *shouldn't* rely on a (changeable) config option value?
+try:
+    service_provider_relay_state_prefix_default = CONF.saml.relay_state_prefix
+except Exception:
+    service_provider_relay_state_prefix_default = 'ss:mem:'
+
 
 def upgrade(migrate_engine):
     meta = sql.MetaData()
@@ -432,7 +441,7 @@ def upgrade(migrate_engine):
             'relay_state_prefix',
             sql.String(256),
             nullable=False,
-            server_default=CONF.saml.relay_state_prefix,
+            server_default=service_provider_relay_state_prefix_default,
         ),
         mysql_engine='InnoDB',
         mysql_charset='utf8',
diff --git a/tools/generate-schemas b/tools/generate-schemas
new file mode 100755
index 000000000..9d9821c56
--- /dev/null
+++ b/tools/generate-schemas
@@ -0,0 +1,134 @@
+#!/usr/bin/env bash
+#
+# Script to generate schemas for the various versions.
+#
+# Some setup is required, similar to the opportunistic tests.
+#
+# MySQL ->
+#
+# $ mysql -uroot
+# MariaDB [(none)]> CREATE DATABASE keystone
+# MariaDB [(none)]> GRANT ALL PRIVILEGES ON keystone.* TO 'keystone'@'localhost' IDENTIFIED BY 'password';
+# MariaDB [(none)]> quit;
+#
+# Postgres ->
+#
+# $ sudo -u postgres psql
+# postgres=# create user keystone with createdb login password 'password';
+# postgres=# create database keystone with owner keystone;
+# postgres=# quit;
+#
+# Note that you may also have to configure 'pg_hba.conf' to use password-based
+# auth instead of "ident", if you haven't done so already. You can locate this
+# with 'locate pg_hba.conf'. More details at
+# https://ubuntu.com/server/docs/databases-postgresql
+
+set -o xtrace
+set -e
+
+source .tox/py38/bin/activate
+
+INIT_VERSION=$(ls -1 keystone/common/sql/expand_repo/versions/ | head -1 | awk -F_ '{print $1}' | sed 's/^0*//')
+INIT_VERSION=$(($INIT_VERSION-1))
+
+echo "Detected init version of $INIT_VERSION"
+
+mkdir -p /tmp/keystone-schemas
+rm -f "/tmp/keystone-schemas/$INIT_VERSION-*.sql"
+
+#
+# functions
+#
+
+function sync () {
+    DB_URL=$1
+
+    python keystone/common/sql/expand_repo/manage.py version_control \
+      --database "$DB_URL" \
+      --version "$INIT_VERSION" \
+      --repository keystone/common/sql/expand_repo/
+    python keystone/common/sql/data_migration_repo/manage.py version_control \
+      --database "$DB_URL" \
+      --version "$INIT_VERSION" \
+      --repository keystone/common/sql/data_migration_repo/
+    python keystone/common/sql/contract_repo/manage.py version_control \
+      --database "$DB_URL" \
+      --version "$INIT_VERSION" \
+      --repository keystone/common/sql/contract_repo/
+
+    python keystone/common/sql/expand_repo/manage.py upgrade \
+      --database "$DB_URL" \
+      --repository keystone/common/sql/expand_repo/
+    python keystone/common/sql/data_migration_repo/manage.py upgrade \
+      --database "$DB_URL" \
+      --repository keystone/common/sql/data_migration_repo/
+    python keystone/common/sql/contract_repo/manage.py upgrade \
+      --database "$DB_URL" \
+      --repository keystone/common/sql/contract_repo/
+}
+
+#
+# sqlite
+#
+
+# cleanup from previous runs
+
+rm -f /tmp/keystone.db
+
+# sync schema
+
+sync 'sqlite:////tmp/keystone.db'
+
+# dump the schema
+
+sqlite3 /tmp/keystone.db << EOF
+.output "/tmp/keystone-schemas/${INIT_VERSION}-sqlite.sql"
+.schema
+.quit
+EOF
+
+rm -f /tmp/keystone.db
+
+#
+# mysql
+#
+
+# cleanup from previous runs
+
+mysql -u keystone -ppassword << EOF
+DROP DATABASE IF EXISTS keystone;
+CREATE DATABASE keystone;
+EOF
+
+# sync schema
+
+sync 'mysql+pymysql://keystone:password@localhost/keystone'
+
+# dump the schema
+
+mysqldump --no-data --skip-comments -u keystone -ppassword \
+    keystone > "/tmp/keystone-schemas/${INIT_VERSION}-mysql.sql"
+
+mysql -u keystone -ppassword << EOF
+DROP DATABASE IF EXISTS keystone;
+EOF
+
+#
+# postgres
+#
+
+# cleanup from previous runs
+
+sudo -u postgres dropdb --if-exists keystone
+sudo -u postgres createdb --owner=keystone keystone
+
+# sync to initial version
+
+sync 'postgresql://keystone:password@localhost/keystone'
+
+# dump the schema
+
+pg_dump postgresql://keystone:password@localhost/keystone \
+    --schema-only > "/tmp/keystone-schemas/${INIT_VERSION}-postgres.sql"
+
+sudo -u postgres dropdb --if-exists keystone