Skip to content

Database Schema and Migration

Jump to bottom
Michael Calmer edited this page May 22, 2024 · 3 revisions

Introduction to the Uyuni DB schema

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).

The schema upgrade must be idempodent! This means the upgrade can be executed multiple times and must not fail and produce always the same result. Check the existing migrations how to achive this.

Deploy a schema migration to a remote Container

Uyuni will be shipped as container. When developing a schema migration there is an easy way to copy the schema files into the remote container and apply them.

Pre-requisite

Before you can deploy to a remote machine into a podman Container, you need to setup a podman remote connection.

$> ssh-copy-id [email protected]

$> podman system connection add dev ssh://[email protected]

This create a connection named dev which can be used for mgrctl commands

You also need to install mgrctl from Uyuni Container Utils:

$> zypper ar -f https://download.opensuse.org/repositories/systemsmanagement:/Uyuni:/Stable:/ContainerUtils/openSUSE_Leap_15.5/ uyuni-container-utils
$> zypper in mgrctl

Check https://download.opensuse.org/repositories/systemsmanagement:/Uyuni:/Stable:/ContainerUtils/ for other OSes

Copy files into the container and apply the schema upgrade

To copy any file from your local workstation into the container you can call:

$> CONTAINER_CONNECTION=dev mgrctl cp /path/to/local.file server:/path/to/place/in.container

You can open a remote terminal session and apply the schema upgrade with:

$> CONTAINER_CONNECTION=dev mgrctl term
CONTAINER> spacewalk-sql --select-mode /path/to/schema/file.sql
Clone this wiki locally