Skip to content

Database Schema and Migration

Jump to bottom
Silvio Moioli edited this page Jul 29, 2021 · 3 revisions

Introduction to the Uyuni DB schema

🤗 This page needs love!

Contribution

Modifications in the Uyuni database involves two distinct tasks:

  1. Editing the schema definition
  2. Adding a migration script from the previous state

Following sections provide guidelines for each of these tasks.

1. Schema definition

The spacewalk schema is located under spacewalk/schema/spacewalk in the source tree.

There are 2 directories:

  • common
  • postgres

Historically, we had plain SQL files that worked on both PostgreSQL and Oracle under under common/, and PosgtreSQL specifics in postgres/. At this point we only support Postgres, so those two will eventually be merged. For the time being please try to stick to standard SQL and put files in common/, when that is not possible use postgres/.

Under each directory you will find 3 subdirectories:

  • tables : table definitions
  • views : views definitions
  • data : initial data

You can alter the order that the tables are created by editing tables.deps in that directory.

2. Schema upgrade

Under spacewalk/schema/spacewalk/upgrade you will find upgrade paths for different versions. This path includes all the migration scripts which are executed in order to alter the schema into desired state for existing databases. Migration scripts can be seen as DML diffs for any schema change. For every change made in the schema, proper migration scripts must be added to the upgrade path under the correct (next) version (e.g. susemanager-schema-4.0.12-to-susemanager-schema-4.0.13).

SUSE Manager schema maintenance process

For SUSE Manager we 'branched' the database schema. This means we need to take care about upstream changes and copy the migration scripts into our migration directory.

This would work best directly after a merge from upstream.

Process how to merge database schema migrations

After we created a branch for the next release, the master is open for merges from upstream again. First we create a migration directory which later needs to be renamed with the correct versions.

   $> cd schema/spacewalk/upgrade/
   $> mkdir susemanager-schema-next

After every merge we need to do the following:

  • look for new upstream migrations in spacewalk-schema-<oldversion>-to-spacewalk-schema-<nextversion>
  • if the migration comes from SUSE Manager, because we 'upstreamed' a feature, nothing needs to be done, otherwise:
  • copy new scripts to susemanager-schema-next
  • change the prefix number to keep the correct order
  • check, if we did changes on this table,package,procedure,function,whatever which need to be kept. If yes:
  • merge our changes into the update script

List of Uyuni specific changes

Here is a list of Uyuni specific changes we need to care about while copying schema migration scripts. Patches are available in git at schema/sm-specific-schema-patches/

  1. available_family_subscriptions feature introduced changes in rhn_channel package. This affects the body and header (.pkb and .pks files).

    • rhn_channel-pks.oracle.dif
    • rhn_channel-pkb.oracle.dif
    • rhn_channel-pkb.postgresql.dif

  2. bare metal - introduce the bootstrap entitelement. This affects:

    • rhn_entitlements.pkb
    • rhn_server.pkb
    • create_first_org
    • create_new_org

  3. speedup inter DB link (postgres only)

    • pg_dblink_exec

helper script scopy.sh

I wrote a helper script to copy the schema migration automatically from upstream to SUMA. This script is named scopy.sh and is part of the git repo in git at schema/sm-specific-schema-patches/ .

How to use scopy.sh

Go to the update directory:

cd schema/spacewalk/update

Call scopy.sh from here:

../../sm-specific-schema-patches/scopy.sh

Check the output. It copies new scripts, and if a script changed it prints a warning with a diff command. Check it and if needed, copy the upstream changes manually.

Call scopy.sh a second time:

../../sm-specific-schema-patches/scopy.sh

For the files above scopy.sh do not copy the upstream migration, but the merged file for the initial setup. This produce a NEED CHECK on the second run. If you have verified, that this is ok, you can create a template to prefent this message.

Store the diff as schema/sm-specific-schema-patches/template/<schema-mig-file>.dif . If the real diff is equal to the stored one, scopy.sh will not print this warning.

Clone this wiki locally