Categories
Database Development Java

JPA

Bisher habe ich für den Datenbankzugriff mit einem proprietärem Framework gearbeitet, das ich jedoch für das aktuelle Projekt nicht verwenden kann. Bei der Wahl einer frei zugänglichen Alternative entschied ich mich für JPA, die Java/Jakarta Persistence API.

Die Datenbank

Als Datenbank benutze ich einfach das Setup aus meinem letzten Post.

Projekt Setup

Es wird ein neues Maven Projekt angelegt. Java Version 1.8.

Es wird die Javax Persistence API benötigt und eine Implementierung, hier: Hibernate. Als DB wird PostgreSQL verwendet, dazu wird der entsprechende Treiber benötigt.

Die pom.xml des Projekts:

<project xmlns="http://maven.apache.org/POM/4.0.0" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>deringo</groupId>
  <artifactId>jpa</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>JPATest</name>
  <description>JPA Test Project</description>

  <properties>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
  </properties>

  <dependencies>
    <dependency>
      <groupId>javax.persistence</groupId>
      <artifactId>javax.persistence-api</artifactId>
      <version>2.2</version>
    </dependency>
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-core</artifactId>
      <version>5.6.1.Final</version>
    </dependency>
   <!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.2.18</version>
    </dependency>
  </dependencies>

</project>

Verbindungsbeschreibung

Die benötigten Informationen für den Verbindungsaufbau mit der DB werden in der persistence.xml hinterlegt:

<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
  http://xmlns.jcp.org/xml/ns/persistence/persistence_2_2.xsd"
  version="2.2">
    <persistence-unit name="myapp-persistence-unit">
        <properties>
            <!-- Configure a database connection in Java SE -->
            <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver" />
            <property name="javax.persistence.jdbc.url" value="jdbc:postgresql://127.0.0.1:5432/myapp" />
            <property name="javax.persistence.jdbc.user" value="postgres" />
            <property name="javax.persistence.jdbc.password" value="PASSWORD" />
 
            <!-- Configure timeouts -->     
            <property name="javax.persistence.lock.timeout" value="100"/>
            <property name="javax.persistence.query.timeout" value="100"/>
        </properties>
    </persistence-unit>
</persistence>

Java Klassen

Die beiden Tabellen Adresse und Person werden jeweils in eine Java Klasse überführt. Dabei handelt es sich um POJOs mit Default Constructor, (generierter) toString, hashCode und equals Methoden. Annotation als Entity und für die ID, die uA objectID heißen soll und nicht wie in der DB object_id.

package deringo.jpa.entity;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Adresse implements Serializable {
	private static final long serialVersionUID = 1L;

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(name = "object_id")
	private int objectID;
	
	private String strasse;
	private String ort;
	
	public Adresse() {
		// default constructor
	}

	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + objectID;
		result = prime * result + ((ort == null) ? 0 : ort.hashCode());
		result = prime * result + ((strasse == null) ? 0 : strasse.hashCode());
		return result;
	}

	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		Adresse other = (Adresse) obj;
		if (objectID != other.objectID)
			return false;
		if (ort == null) {
			if (other.ort != null)
				return false;
		} else if (!ort.equals(other.ort))
			return false;
		if (strasse == null) {
			if (other.strasse != null)
				return false;
		} else if (!strasse.equals(other.strasse))
			return false;
		return true;
	}

	@Override
	public String toString() {
		return String.format("Adresse [objectID=%s, strasse=%s, ort=%s]", objectID, strasse, ort);
	}

	public int getObjectID() {
		return objectID;
	}

	public void setObjectID(int objectID) {
		this.objectID = objectID;
	}

	public String getStrasse() {
		return strasse;
	}

	public void setStrasse(String strasse) {
		this.strasse = strasse;
	}

	public String getOrt() {
		return ort;
	}

	public void setOrt(String ort) {
		this.ort = ort;
	}
}

Für den Zugriff auf die Tabellen werden die jeweiligen Repository Klassen angelegt.

package deringo.jpa.repository;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

import deringo.jpa.entity.Adresse;

public class AdresseRepository {
	private static EntityManagerFactory emf = Persistence.createEntityManagerFactory("myapp-persistence-unit");

	public static Adresse getAdresseById(int id) {
		EntityManager em = emf.createEntityManager();
		return em.find(Adresse.class, id);
	}

}

“Geschäftslogik” um zu testen, ob es funktioniert:

package deringo.jpa;

import deringo.jpa.entity.Adresse;
import deringo.jpa.repository.AdresseRepository;

public class TestMain {

	public static void main(String[] args) {
		int adresseID = 4;
		Adresse adresse = AdresseRepository.getAdresseById(adresseID);
		System.out.println(adresse);
	}

}

Test Driven

Den Zugriff über die Repositories (und später auch Service Klassen) habe ich Test Driven entwickelt mit JUnit. Zur Entwicklung mit JUnit hatte ich schon mal einen Post verfasst.

Folgende Dependencies wurden der pom.xml hinzugefügt:

    <!-- https://mvnrepository.com/artifact/org.junit.jupiter/junit-jupiter-api -->
    <dependency>
	  <groupId>org.junit.jupiter</groupId>
	  <artifactId>junit-jupiter-api</artifactId>
	  <version>5.8.1</version>
	  <scope>test</scope>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.hamcrest/hamcrest -->
    <dependency>
      <groupId>org.hamcrest</groupId>
      <artifactId>hamcrest</artifactId>
      <version>2.2</version>
      <scope>test</scope>
    </dependency>
package deringo.jpa.repository;

import static org.hamcrest.MatcherAssert.assertThat;
import static org.hamcrest.Matchers.is;
import static org.junit.jupiter.api.Assertions.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertNull;

import org.junit.jupiter.api.Test;

import deringo.jpa.entity.Adresse;

public class AdresseRepositoryTest {

	@Test
	public void getAnmeldungById() {
		int adresseID = 1;
		Adresse adresse = AdresseRepository.getAdresseById(adresseID);
		assertNull(adresse);
		
		adresseID = 4;
		adresse = AdresseRepository.getAdresseById(adresseID);
		assertNotNull(adresse);
		assertThat(adresse.getObjectID(), is(adresseID));
		assertThat(adresse.getStrasse(), is("Beispielstrasse"));
		assertThat(adresse.getOrt(), is("Beispielstadt"));
	}
}

Projektstruktur

Query

Alle Adressen eines Ortes suchen:

	public static List<Adresse> getAdresseByOrt(String ort) {
		EntityManager em = emf.createEntityManager();
		TypedQuery<Adresse> query = em.createQuery("SELECT a FROM Adresse a WHERE a.ort = :ort", Adresse.class);
		query.setParameter("ort", ort);
		return query.getResultList();
	}

Native Query

Um zB herauszufinden, wie die zuletzt vergebene ObjectID lautet, kann ein native Query verwendet werden:

	public static int getLastObjectID() {
		String sequenceName = "public.object_id_seq";
		String sql = "SELECT s.last_value FROM " + sequenceName + " s";
		EntityManager em = emf.createEntityManager();
		BigInteger value = (BigInteger)em.createNativeQuery(sql).getSingleResult();
		return value.intValue();
	}

Kreuztabelle

Nehmen wir mal an, eine Person kann mehrere Adressen haben und an eine Adresse können mehrere Personen gemeldet sein.

Um das abzubilden benötigen wir zunächst eine Kreuztabelle, die wir in der DB anlegen:

DROP TABLE IF EXISTS public.adresse_person;
CREATE TABLE public.adresse_person (
    adresse_object_id integer NOT NULL,
    person_object_id integer NOT NULL
);

Solch eine Relation programmatisch anlegen:

	public static void createAdressePersonRelation(int adresseId, int personId) {
		String sql = "INSERT INTO adresse_person (adresse_object_id, person_object_id) VALUES (?, ?)";//, adresseId, personId);
		EntityManager em = emf.createEntityManager();
		em.getTransaction().begin();
		em.createNativeQuery(sql)
		  .setParameter(1, adresseId)
		  .setParameter(2, personId)
		  .executeUpdate();
		em.getTransaction().commit();
	}

Die Adresse zu einer Person(enID) lässt sich ermitteln:

	public static Adresse getAdresseByPersonID(int personId) {
		String sql = "SELECT adresse_object_id FROM adresse_person WHERE person_object_id = " + personId;
		EntityManager em = emf.createEntityManager();
		Integer adresseId;
		try {
			adresseId = (Integer)em.createNativeQuery(sql).getSingleResult();
		} catch (NoResultException nre) {
			return null;
		}
		return getAdresseById(adresseId.intValue());
	}

Das funktioniert nur, solange die Person nur eine Adresse hat.

Das kann man so machen, schöner ist es aber über entsprechend ausmodellierte ManyToMany Beziehungen in den Entities.
Das Beispiel vervollständige ich hier erstmal nicht, da ich bisher es in meinem Projekt nur so wie oben beschrieben benötigte.

OneToMany

Wandeln wir obiges Beispiel mal ab: An einer Adresse können mehrere Personen gemeldet sein, aber eine Person immer nur an einer Adresse.

Wir fügen also der Person eine zusätzliche Spalte für die Adresse hinzu:

ALTER TABLE person ADD COLUMN adresse_object_id integer;
--
UPDATE person SET adresse_object_id = 4
public class Person implements Serializable {
  [...]
    @ManyToOne
    @JoinColumn(name="adresse_object_id")
	private Adresse adresse;
  [...]
}
public class Adresse implements Serializable {
  [..]
 	@OneToMany
	@JoinColumn(name="adresse_object_id")
	private List<Person> personen = new ArrayList<>();
  [...]
}

Anschließend noch die Getter&Setter, toString, hashCode&equals neu generieren und einen Test ausführen:

	@Test
	public void getAnmeldungById() {
		int adresseID = 4;
		adresse = AdresseRepository.getAdresseById(adresseID);
		assertNotNull(adresse);
		assertThat(adresse.getObjectID(), is(adresseID));
		assertThat(adresse.getStrasse(), is("Beispielstrasse"));
		assertThat(adresse.getOrt(), is("Beispielstadt"));		
		assertThat(adresse.getPersonen().size(), is(3));
	}

Der Test funktioniert.

ABER: Folgende Zeile am Ende bewirkt einen StackOverflow Error:

	public void getAnmeldungById() {
        [...]
		System.out.println(adresse);
    }

Das Problem ist die generierte toString-Methode in Person:

	@Override
	public String toString() {
		return String.format("Person [objectID=%s, vorname=%s, nachname=%s, adresse=%s]", objectID, vorname, nachname, 
                             adresse);
	}

Es soll das Objekt adresse ausgegeben werden, in welchem in der toString-Methode das Objekt person ausgegeben werden soll, in welchem das Objekt adresse ausgegeben werden, in welchem in der toString-Methode das Objekt person ausgegeben werden soll, in welchem das Objekt adresse … usw.

Als Lösung muss die toString-Methode von Person händisch angepasst werden, so dass nicht mehr das Objekt adresse, sondern lediglich dessen ID ausgegeben wird:

	@Override
	public String toString() {
		return String.format("Person [objectID=%s, vorname=%s, nachname=%s, adresse=%s]", objectID, vorname, nachname, 
                             adresse == null ? null : adresse.getObjectID());
	}

siehe auch: https://stackoverflow.com/questions/23973347/jpa-java-lang-stackoverflowerror-on-adding-tostring-method-in-entity-classes

Neuen Eintrag speichern

Adresse speichern:

	public static void saveAdresse(Adresse adresse) {
		EntityManager em = emf.createEntityManager();
		em.getTransaction().begin();
		if (adresse.getObjectID() == 0) {
			em.persist(adresse);
		} else {
			em.merge(adresse);
		}
		em.getTransaction().commit();
	}

Testen:

	@Test
	public void saveNewAdresse() {
		int objectID = AdresseRepository.getLastObjectID();

		Adresse adresse = new Adresse();
		adresse.setStrasse("neue Stasse");
		adresse.setOrt("neuer Ort");
		assertThat(adresse.getObjectID(), is(0));
		AdresseRepository.saveAdresse(adresse);
		assertThat(adresse.getObjectID(), is(objectID + 1));
		assertThat(adresse.getOrt(), is("neuer Ort"));
		
		adresse.setOrt("neuerer Ort");
		AdresseRepository.saveAdresse(adresse);
		assertThat(adresse.getObjectID(), is(objectID + 1));
		assertThat(adresse.getOrt(), is("neuerer Ort"));
	}

Eintrag löschen

Man möchte meinen, dass der Code zum löschen einer Adresse wie folgt lautet:

	public static void deleteAdresse(Adresse adresse) {
		EntityManager em = emf.createEntityManager();
		em.getTransaction().begin();
		em.remove(adresse);
		em.getTransaction().commit();
	}

Testen:

	@Test
	public void deleteAdresse() {
		int adresseID = 8;
		Adresse adresse = AdresseRepository.getAdresseById(adresseID);
		assertNotNull(adresse);
		
		AdresseRepository.deleteAdresse(adresse);
		assertNull(adresse);
	}

Der Test schlägt fehl mit der Nachricht: “Removing a detached instance”.

Das Problem besteht darin, dass die Adresse zuerst über einen EntityManager gezogen wird, aber das Löschen in einem anderen EntityManager, bzw. dessen neuer Transaktion, erfolgen soll. Dadurch ist die Entität detached und muss erst wieder hinzugefügt werden, um sie schließlich löschen zu können:

	public static void deleteAdresse(Adresse adresse) {
		EntityManager em = emf.createEntityManager();
		em.getTransaction().begin();
		em.remove(em.contains(adresse) ? adresse : em.merge(adresse));
		em.getTransaction().commit();
	}
Categories
Database Development

PostgreSQL Docker-Compose Setup

Im vorletzten Post: PostgreSQL hatte ich beschrieben, wie ich zwei Bestandsdatenbanken analysiert und in eine PostgreSQL-DB in einem Docker Container gebracht habe, samt PGAdmin.

Jetzt möchte ich einen Schritt weiter gehen und das komplette Setup über ein Script starten können: DB, PGAdmin & SQL-Scripte. Dazu verwende ich Docker-Compose.

Ausgangslage

In PostgreSQL hatte ich bereits die zugrundeliegenden Docker-Images ermittelt: postgres:13.4-buster für die DB und dpage/pgadmin4 für PGAdmin. Inzwischen gibt es aber ein aktuelleres Image für die DB, das ich verwenden werde: postgres:13.5-bullseye

docker pull postgres:13.5-bullseye
docker pull dpage/pgadmin4

Für die SQL-Daten werde ich auf den Artikel PostgreSQL IDs zurückgreifen und daraus zwei Scripte machen, eines für das Schema der DB und eines mit den “Masterdaten” mit denen das Schema initial befüllt werden soll.

CREATE SEQUENCE object_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

CREATE TABLE person (
    object_id integer NOT NULL DEFAULT nextval('object_id_seq'::regclass),
    vorname varchar(255),
    nachname varchar(255),
    CONSTRAINT person_pkey PRIMARY KEY (object_id)
);

CREATE TABLE adresse (
    object_id integer NOT NULL DEFAULT nextval('object_id_seq'::regclass),
    strasse varchar(255),
    ort varchar(255),
    CONSTRAINT adresse_pkey PRIMARY KEY (object_id)
);
INSERT INTO person (vorname, nachname) VALUES ('Max', 'Mustermann');
INSERT INTO person (vorname, nachname) VALUES ('Peter', 'Person');
INSERT INTO person (vorname, nachname) VALUES ('Donald', 'Demo');

INSERT INTO adresse (strasse, ort) VALUES ('Beispielstrasse', 'Beispielstadt');
INSERT INTO adresse (strasse, ort) VALUES ('Erpelweg', 'Entenhausen');
INSERT INTO adresse (strasse, ort) VALUES ('Bruchstrasse', 'Berlin');

Auf der Seite von Docker Compose bringe ich in Erfahrung, dass die aktuelle Version von Docker Compose 3.9 ist und erstelle schon mal die Datei:

version: "3.9"  # optional since v1.27.0

Ordernstrucktur:

Images starten

Bisher habe ich PostgreSQL und PGAdmin über folgende Kommandos gestartet:

docker run --name myapp-db -p 5432:5432 -e POSTGRES_PASSWORD=PASSWORD -d postgres:13.4-buster
docker run --name myapp-pgadmin -p 80:80 -e PGADMIN_DEFAULT_EMAIL=admin@admin.com -e PGADMIN_DEFAULT_PASSWORD=admin -d dpage/pgadmin4

Diese Kommandos werden in ein Docker-Compose Script transferiert:

version: "3.9"  # optional since v1.27.0
services:
  myapp-db:
    image: postgres:13.5-bullseye
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_PASSWORD=PASSWORD
  myapp-pgadmin:
    image: dpage/pgadmin4
    ports:
      - "80:80"
    environment:
      - PGADMIN_DEFAULT_EMAIL=admin@admin.com
      - PGADMIN_DEFAULT_PASSWORD=admin

Jetzt können beide Images mit einem einfachen Befehl gestartet werden:

\myapp> docker-compose up
Creating network "myapp_default" with the default driver
Creating myapp_myapp-db_1      ... done
Creating myapp_myapp-pgadmin_1 ... done
Attaching to myapp_myapp-db_1, myapp_myapp-pgadmin_1

PGAdmin im Browser starten: http://localhost:80
Login wie bisher mit admin@admin.com / admin

Im nächsten Schritt gibt es bereits eine entscheidende Änderung:
Während bisher beide Container isoliert nebeneinander liefen und nur über den Host-Rechner kommunizieren konnten, wurde durch Docker-Compose automatisch beim Start ein Netzwerk (“myapp_default”) angelegt, in dem beide Container laufen. Außerdem sind beide Container über ihren Servicenamen (“myapp-db” & “myapp-pgadmin”) erreichbar.

Dadurch muss nicht mehr die IP des Host-Rechners ermittelt werden (die sich manchmal ändert), sondern es kann der Name genommen werden:

Datenbank erstellen

In der PostgreSQL Instanz muss jetzt eine Datenbank erzeugt werden, in der die Anwendungsdaten gespeichert werden.

Hierzu gehen wir in den DB Container.
Allerdings ist der Name anders als bisher: Es wurde der Verzeichnisname als Präfix davor und eine 1 (für die 1. und in unserem Fall einzige Instanz) als Postfix dahinter gehangen und so lautet der Name : myapp_myapp-db_1

docker exec -it myapp_myapp-db_1 bash

Im Container erzeugen wir die DB:

su postgres
createdb myappdb
exit

So war es zumindest bisher, einfacher geht es mit Docker-Compose und dem Setzten der Environment-Variablen POSTGRES_DB wodurch die DB automatisch angelegt und verwendet wird.
Sicherlich hätte ich das auch bisher im Docker Kommando so nehmen können, aber im letzten Post hatte ich es zum einen mit zwei DBs zu tun und zum anderen musste ich eh auf die Kommandozeile um die DBs einzuspielen.

version: "3.9"
services:
  myapp-db:
    image: postgres:13.5-bullseye
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_PASSWORD=PASSWORD
      - POSTGRES_DB=myappdb
  myapp-pgadmin:
    image: dpage/pgadmin4
    ports:
      - "80:80"
    environment:
      - PGADMIN_DEFAULT_EMAIL=admin@admin.com
      - PGADMIN_DEFAULT_PASSWORD=admin

Datenbank befüllen

Bisher war der Weg, die SQL-Dateien in den Container zu kopieren und von dort einzuspielen:

# Dateien in Container kopieren
docker cp database/Masterdata.sql myapp_myapp-db_1:/tmp
docker cp database/Schema.sql myapp_myapp-db_1:/tmp
# In Container wechseln
docker exec -it myapp_myapp-db_1 bash

Im Container SQLs einspielen:

su postgres
psql myappdb < /tmp/Schema.sql
psql myappdb < /tmp/Masterdata.sql
exit

Einfacher geht es über Docker-Compose und den Mechanismus, dass PostgreSQL automatisch die Dateien importiert, die im Verzeichnis /docker-entrypoint-initdb.d/ liegen. Und zwar in alphabetischer Reihenfolge.

version: "3.9"
services:
  myapp-db:
    image: postgres:13.5-bullseye
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_PASSWORD=PASSWORD
      - POSTGRES_DB=myappdb
    volumes:
      - ./database/Schema.sql:/docker-entrypoint-initdb.d/1-Schema.sql
      - ./database/Masterdata.sql:/docker-entrypoint-initdb.d/2-Masterdata.sql
  myapp-pgadmin:
    image: dpage/pgadmin4
    ports:
      - "80:80"
    environment:
      - PGADMIN_DEFAULT_EMAIL=admin@admin.com
      - PGADMIN_DEFAULT_PASSWORD=admin

PGAdmin Einstellungen persistieren

Während der Entwicklung öfters mal die DB platt macht und komplett neu aufsetzt: Mit Docker ist das schnell gemacht. Mit Docker-Compose sind es jetzt nur noch zwei Befehle:

# stop and remove stopped containers
docker-compose down
# start containers
docker-compose up

Einen Nachteil gibt es allerdings: Die Einstellungen im PGAdmin gehen ebenfalls flöten und müssen neu eingegeben werden.
Die Lösung: Der PGAdmin Container bekommt ein persistentes Volume, das ein docker-compose down übersteht. Und wenn es doch mal neu aufgesetzt werden muss, ist das einfach über das -v Flag umsetzbar: docker-compose down -v

version: "3.9"
services:
  myapp-db:
    image: postgres:13.5-bullseye
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_PASSWORD=PASSWORD
      - POSTGRES_DB=myappdb
    volumes:
      - ./database/Schema.sql:/docker-entrypoint-initdb.d/1-Schema.sql
      - ./database/Masterdata.sql:/docker-entrypoint-initdb.d/2-Masterdata.sql
  myapp-pgadmin:
    image: dpage/pgadmin4
    ports:
      - "80:80"
    environment:
      - PGADMIN_DEFAULT_EMAIL=admin@admin.com
      - PGADMIN_DEFAULT_PASSWORD=admin
    volumes:
      - pgadminvolume:/var/lib/pgadmin
volumes:
  pgadminvolume: {}

DB Image mit Schema und Masterdata

An Schema und Stammdaten wird sich erstmal nichts ändern. Daher wäre es gut, wenn beim Neubau der Container die DB bereits mit Schema und Stammdaten gestartet wird und nicht diese erst aufbauen muss.

Das wird dadurch erreicht, dass ein Image gebaut wird, dass die PostgreSQL DB sowie Schema und Stammdaten enthält.

Im Verzeichnis database wird eine Datei Dockerfile angelegt. Dieses Dockerfile enthält die Informationen zum Bau des DB Images.

FROM postgres:13.5-bullseye
 ENV POSTGRES_PASSWORD PASSWORD
 ENV POSTGRES_DB myappdb
COPY ./Schema.sql /docker-entrypoint-initdb.d/1-Schema.sql
COPY ./Masterdata.sql /docker-entrypoint-initdb.d/2-Masterdata.sql
version: "3.9"
services:
  myapp-db:
    build: ./database
    ports:
      - "5432:5432"
  myapp-pgadmin:
    image: dpage/pgadmin4
    ports:
      - "80:80"
    environment:
      - PGADMIN_DEFAULT_EMAIL=admin@admin.com
      - PGADMIN_DEFAULT_PASSWORD=admin
    volumes:
      - pgadminvolume:/var/lib/pgadmin
volumes:
  pgadminvolume: {}

Gestartet wird wie gewohnt:

docker-compose up

Sollte es erforderlich sein, das Image neu zu bauen, zB wenn ich das Schema verändert hat:

docker-compose up --build
# or
docker-compose build

UPDATE: Das war leider nix mit dem vorgefüllten Image

Das Dockerfile enthält zwar den Schritt die SQL Dateien in das Image zu kopieren. Es fehlt aber der Schritt, bei dem diese Dateien in die Datenbank hineinmigriert werden. Das geschieht wie zuvor auch erst beim Starten des Containers. Mein Ziel, einen Image zu haben, dass diese Daten bereits enthält, habe ich damit also leider nicht erreicht. Das Dockerfile enthält keine Informationen, die nicht zuvor auch schon im Docker Compose enthalten waren.

Da mich das herumkaspern mit diesem Problem heute den ganzen Tag gekostet und abgesehen vom Erkenntnisgewinn leider nichts gebracht hat, kehre ich zur Docker Compose Variante zurück, bei der die DB beim Starten des Containers gebaut wird. Für das Beispiel auf dieser Seite macht das praktisch gesehen keinen Unterschied, da die Scripte winzig sind. Für mein Projekt leider schon, da benötigt der Aufbau der DB ein paar Minuten. Für die Anwendungsentwicklung, bei der ich die DB alle paar Tage mal neu aufsetze, ist das durchaus OK. Für Tests, die mit einer frischen DB starten sollen, die am Ende weggeschmissen wird, ist das schon ein Problem.

Also Rückkehr zu Docker Compose, diesmal mit einem extra Volume für die DB. Das kann ich dann gezielt löschen.

version: "3.9"
services:
  myapp-db:
    image: postgres:13.5-bullseye
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=PASSWORD
      - POSTGRES_DB=myapp
    volumes:
      - postgresvolume:/var/lib/postgresql/data
      - ./database/Schema.sql:/docker-entrypoint-initdb.d/1-Schema.sql
      - ./database/Masterdata.sql:/docker-entrypoint-initdb.d/2-Masterdata.sql
  myapp-pgadmin:
    image: dpage/pgadmin4
    ports:
      - "80:80"
    environment:
      - PGADMIN_DEFAULT_EMAIL=admin@admin.com
      - PGADMIN_DEFAULT_PASSWORD=admin
    volumes:
      - pgadminvolume:/var/lib/pgadmin
volumes:
  postgresvolume: {}
  pgadminvolume: {}

Docker Befehle

# Create and start containers
docker-compose up
# Image neu bauen
docker-compose up --build
# or
docker-compose build

# Stop containers
docker-compose stop
# Start containers
docker-compose start

# Stop containers and remove them
docker-compose down
# Stop containers, remove them and remove volumes
docker-compose down -v

Categories
Database Development

PostgreSQL IDs

Angenommen, wir haben eine Tabelle mit Personen:

CREATE TABLE person (
	vorname varchar(255),
    nachname varchar(255)
);

Diese Personen sollen alle eine eindeutige ID bekommen und diese soll automatisch beim Einfügen generiert werden.

Sequenz

Dazu kann man eine Sequenz anlegen und aus dieser die ID befüllen:

CREATE SEQUENCE person_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

CREATE TABLE person (
    id integer NOT NULL DEFAULT nextval('person_id_seq'::regclass),
	vorname varchar(255),
    nachname varchar(255),
    CONSTRAINT person_pkey PRIMARY KEY (id)
);

Anschließend ein paar Personen hinzufügen:

INSERT INTO person (vorname, nachname) VALUES ('Max', 'Mustermann');
INSERT INTO person (vorname, nachname) VALUES ('Peter', 'Person');
INSERT INTO person (vorname, nachname) VALUES ('Donald', 'Demo');

Und anzeigen lassen:

SELECT * FROM person;

Identity

Da es etwas lästig ist, immer für jede Tabelle jeweils eine eigene Sequenz anlegen und mit der ID verknüpfen zu müssen, wurde die Frage an mich herangetragen, ob es da nicht soetwas wie autoincrement gäbe, wie man es von MySQL kennen würde.

Nach kurze Recherche fand sich, dass es soetwas natürlich auch für PostgreSQL gibt und zwar seit der Version 10 als “IDENTITY”.

CREATE TABLE adresse (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	strasse varchar(255),
    ort varchar(255)
);

Anschließend ein paar Adressen hinzufügen:

INSERT INTO adresse (strasse, ort) VALUES ('Beispielstrasse', 'Beispielstadt');
INSERT INTO adresse (strasse, ort) VALUES ('Erpelweg', 'Entenhausen');
INSERT INTO adresse (strasse, ort) VALUES ('Bruchstrasse', 'Berlin');

Und anzeigen lassen:

SELECT * FROM adresse;

Der Vorteil der Identity, was man so auf den ersten Blick sieht, ist also, dass man sich etwas stumpfe Tipparbeit spart und keine Sequenz anlegen, mit der ID verknüpfen und die ID als Primary Key definieren muss.

Eine Betrachtung der Unterschiede zwischen SEQUENCE und IDENTITY habe ich leider nicht finden können.

Vermutlich gibt es da keine großen technischen Unterschiede, die IDENTITY scheint mir eine anonyme SEQUENCE zu sein.

Object ID Sequenz

Die IDENTITY kann man nur für einen TABLE nutzen, die SEQUENCE könnte man für mehrere Tabellen nutzen und so eine datenbankweite eindeutige ID verwenden.

Beispielsweise eine eindeutige Object ID Sequenz anlegen und für die Tabellen Person und Adresse verwenden:

CREATE SEQUENCE object_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

CREATE TABLE person (
    object_id integer NOT NULL DEFAULT nextval('object_id_seq'::regclass),
	vorname varchar(255),
    nachname varchar(255),
    CONSTRAINT person_pkey PRIMARY KEY (object_id)
);

CREATE TABLE adresse (
    object_id integer NOT NULL DEFAULT nextval('object_id_seq'::regclass),
	strasse varchar(255),
    ort varchar(255),
    CONSTRAINT adresse_pkey PRIMARY KEY (object_id)
);

Anschließend ein paar Personen und Adressen hinzufügen:

INSERT INTO person (vorname, nachname) VALUES ('Max', 'Mustermann');
INSERT INTO person (vorname, nachname) VALUES ('Peter', 'Person');
INSERT INTO person (vorname, nachname) VALUES ('Donald', 'Demo');

INSERT INTO adresse (strasse, ort) VALUES ('Beispielstrasse', 'Beispielstadt');
INSERT INTO adresse (strasse, ort) VALUES ('Erpelweg', 'Entenhausen');
INSERT INTO adresse (strasse, ort) VALUES ('Bruchstrasse', 'Berlin');

Und anzeigen lassen:

SELECT * FROM person;
SELECT * FROM adresse;

Wie man sehen kann, wurde die ID fortlaufend über beide Tabellen vergeben. Dadurch erhält man eine datenbankweit eindeutige, fortlaufende ID.

UUID

Und weil ich grade schon dabei bin: Seit Version 13 bringt PostgreSQL auch standartmäßig die Möglichkeit einer UUID mit.

Eine UUID ist ein Universally Unique Identifier.
Manchmal, typischerweise in Zusammenhang mit Microsoft, wird auch der Ausdruck GUID Globally Unique Identifier verwendet.

SELECT * FROM gen_random_uuid ();

UUIDs sind ebenfalls datenbankweit (und darüber hinaus) eindeutig. Allerdings sind die IDs nicht mehr fortlaufend.

UUIDs sind etwas langsamer als Sequenzen und verbrauchen etwas mehr Speicher.

Das Beispiel von vorhin:

CREATE TABLE person (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	vorname varchar(255),
    nachname varchar(255)
);

CREATE TABLE adresse (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	strasse varchar(255),
    ort varchar(255)
);

Anschließend ein paar Personen und Adressen hinzufügen:

INSERT INTO person (vorname, nachname) VALUES ('Max', 'Mustermann');
INSERT INTO person (vorname, nachname) VALUES ('Peter', 'Person');
INSERT INTO person (vorname, nachname) VALUES ('Donald', 'Demo');

INSERT INTO adresse (strasse, ort) VALUES ('Beispielstrasse', 'Beispielstadt');
INSERT INTO adresse (strasse, ort) VALUES ('Erpelweg', 'Entenhausen');
INSERT INTO adresse (strasse, ort) VALUES ('Bruchstrasse', 'Berlin');

Und anzeigen lassen:

SELECT * FROM person;
SELECT * FROM adresse;

Categories
Database Development

PostgreSQL

Für die Neu- und Weiterentwicklung einer Anwendung habe ich zur Analyse die Bestandsanwendung samt Datenbanken bekommen.
Für die Analyse musste ich zunächst die Datenbanken zum laufen bekommen und uA mit einem DB-Client einsehen.

Ich habe zum einen eine Datei dump.backup bekommen. Zunächst musste ich herausfinden, um was für eine Datei es sich dabei handelt, dazu nutzte ich das Linux Tool file:

apt update
apt install file
file /tmp/dump.backup
# /tmp/dump.backup: PostgreSQL custom database dump - v1.14-0

Es handelt sich also um einen Dump einer PostgreSQL Datenbank. Und im Dump konnte ich eine Versionsnummer 13.0 finden.

Die andere Datei mydb.sql.gz beinhaltet eine gezippte Version eines SQL Exports einer PostgreSQL DB Version 13.2 von einem Debian 13.2 Server.

Im Laufe der weiteren Analyse der DB Exporte stellte sich heraus, dass der dump.backup die PostGIS Erweiterung der PostgreSQL DB benötigt, welche mit installiert werden muss.

PostgreSQL Datenbank Docker Image

Ich werde beide Datenbanken in einer Docker Version installieren, dazu werde ich eine DB Instanz starten, in der beide DBs installiert werden.

Die Docker Seite für Postgres: Postgres – Official Image | Docker Hub

Da es sich um die Versionsnummer 13.0 und 13.2 handelt, werde ich ein aktuelles Image von Version 13 verwenden, was zum Projektzeitpunkt Version 13.4 war.

Da zumindest eine der beiden DBs auf einem Debian System gehostet ist, werde ich ein Debian Image wählen.

Die Wahl des Images fällt also auf: postgres:13.4-buster

Datenbank installieren

Zuerst das Docker Image ziehen:

docker pull postgres:13.4-buster

Datenbank starten:

docker run --name myapp-db -p 5432:5432 -e POSTGRES_PASSWORD=PASSWORD -d postgres:13.4-buster

DB-Dateien in den laufenden Docker Container kopieren:

docker cp mydb.sql.gz myapp-db:/tmp
docker cp dump.backup myapp-db:/tmp

In den laufenden Container wechseln:

docker exec -it myapp-db bash

PostGIS installieren:

apt update
apt install -y postgresql-13-postgis-3-scripts

DB von Dump erstellen:

su postgres
createdb mydb_dump
pg_restore -d mydb_dump -v /tmp/dump.backup
exit

DB von SQL erstellen:

su postgres
cd /tmp
gunzip mydb.sql.gz
createdb mydb_sql
pg_restore -d mydb_sql -v /tmp/mydb.sql
exit

Die Datenbanken sind installiert und es kann mittels eines letzten exit der Container verlassen werden.

DB Client PGAdmin installieren

Um in die Datenbanken hinein sehen zu können wird ein Client Programm benötigt. Sicherlich es gibt da bereits etwas auf der CommandLine Ebene:

su postgres
psql mydb_sql

Übersichtlich oder komfortabel ist das aber nicht. Daher möchte ich ein Tool mit einer grafischen Oberfläche verwenden. Die Wahl fiel auf pgAdmin, welches sich leicht von einem Docker Image installieren und anschließend über den Browser bedienen lässt.

Zuerst das Docker Image ziehen:

docker pull dpage/pgadmin4

pgAdmin parametrisiert starten:

docker run --name myapp-pgadmin -p 80:80 -e PGADMIN_DEFAULT_EMAIL=admin@admin.com -e PGADMIN_DEFAULT_PASSWORD=admin -d dpage/pgadmin4

Login:
* Email: admin@admin.com
* Passwort: admin

Port: 80

Der pgAdmin ist über den Browser aufrufbar: http://localhost/

Für die Konfiguration für die Verbindung zur zuvor gestarteten PostgreSQL Datenbank benötige ich die IP meines Rechners, die ich mittels ipconfig herausfinden kann.

Categories
Database Development

DB Export & Import

Um die Daten einer MS-SQL Datenbank zu exportieren und anschließend in die geDockerte Version zu kopieren (MS-SQL DB in Docker Container) habe ich die Chains verwendet, eine propritäre Software.

Vorbereitung

Die Entwicklung und Ausführung der Scripte erfolgt auf dem Entwickler Laptop. Später werden die Scripte voraussichtlich auf den Servern ausgeführt, da grade für die produktive Umgebung eine längere Laufzeit erwartet wird.

Auf den Entwickler Laptops läuft Windows, so dass zum Ausführen der Chains das Windows Executable Chain.cmd verwendet wird. In dieser Datei sind Anpassungen vorzunehmen, so ist der JAVA_HOME Pfad inzwischen ein anderer und mMn sollte das File Encoding auf UTF-8 gesetzt werden:

#SET JAVA_HOME=%~d0/jre13
SET JAVA_HOME=%~c0/eclipse/java/java1.8

SET FIXED_PROPS=%STDPROPS% [...] -Dfile.encoding=UTF-8

Die Ausführung der einzelnen Chain muss aus dem Verzeichnis der Chain.cmd erfolgen.
Ausnahme: Auf meinem Laptop muss ich es genau anders herum machen und in das Verzeichnis der Chain gehen und Chain.cmd mit absolutem Pfad aufrufen.

cd D:\Development\workspace\chainproject\bin\
Chain.cmd ../../ChainsProject/ImportChain.chn

Datenbank Verbindungsdaten

Die Verbindungsdaten der Datenbank werden in einer eigenen Konfigurationsdatei hinterlegt:

# connection:
.db=jdbc:jtds:sqlserver://localhost:1433/CCP;TDS=8.0
.user=DonaldDemo
.password=DonaldDemo12345678

# DBObjectSQLServer options:
.objects.autorestore=false
.autorollback=false
.maxconnects=1
.initialconnects=1

# DBObjectSQLServerScript options:
.sqlecho=INFO

Export Chain

Das Chain Command Script ist relativ übersichtlich, da lediglich ein Schritt ausgeführt werden muss. Für diesen Schritt ist das Prefix und die zu verwendende Java Klasse zu definieren. Außerdem sind noch die Verbindungsdaten der Datenbank zu includieren:

#
chainmanager.process-1.prefix=dbscr
dbscr.chain.class=package.name.db.DBObjectSQLServerScript
#
# Scripts:
dbscr.script1=ExportChain.sql

# include configuration:
dbscr.chain.include=MSSQL-RealServer-Connect.inc

Das Chain “SQL” Script ermittelt erst alle Tabellen der angegebenen Schema und speichert diese in der Datei TABLES.csv:

#DEFINE CSVFILE TABLES.csv

#QUERY_CSV (ECHO) [CSVFILE]
  SELECT table_catalog, table_schema, table_name, table_type
    FROM CCP.INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'
     AND TABLE_SCHEMA IN ('DEMO_SCHEMA')
ORDER BY TABLE_SCHEMA, TABLE_NAME
#INFO [SELECTED] rows exported into File [CSVFILE]

Anschließend wird über alle Tabellen iteriert, deren Daten gelesen und in einer CSV-Datei gespeichert:

#FOREACH IDX [CSVFILE]
#INFO Start export of: [IDX:table_schema] [IDX:table_name]
#DEFINE CSVFILE_TABLE [IDX:table_schema]/[IDX:table_name].csv
#QUERY_CSV (ECHO) [CSVFILE_TABLE]
  SELECT *
    FROM [IDX:table_schema].[IDX:table_name]
#INFO [SELECTED] rows exported into File [CSVFILE_TABLE]
#NEXT IDX

Import Chain

Das Chain Command Script entpricht weitgehend dem Export Script:

#
chainmanager.process-1.prefix=dbscr
dbscr.chain.class=package.name.db.DBObjectSQLServerScript
#
# Scripts:
dbscr.script1=ImportChain.sql

# include configuration:
dbscr.chain.include=MSSQL-DockerServer-Connect.inc

Als Vorbereitung muss im Verzeichnis DEMO_SCHEMA eine Datei csvlist angelegt werden, in dieser stehen die zu importierenden Tabellendaten-CSV-Dateinamen. Der reine Import ist ein Einzeiler, dem diese csvlist Datei übergeben wird und das Schema, in das diese Tabellendaten importiert werden sollen:

#IMPORT_CSV_DIR (ECHO) (ENCODING=UTF-8) (ERRORLOG) *DEMO_SCHEMA/csvlist DEMO_SCHEMA

Das ganze Script benötigt noch weitere Befehle, Details dazu siehe MS-SQL DB in Docker Container:

#INFO 
#INFO [PROCESS] start...
#INFO DBUser: [DBUSER]  //  Database-URL: [DBURL]

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

--
SET IDENTITY_INSERT ccp_fdt.performance ON

#IMPORT_CSV_DIR (ECHO) (ENCODING=UTF-8) (ERRORLOG) *DEMO_SCHEMA/csvlist DEMO_SCHEMA

-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Categories
Database Development

MS-SQL DB in Docker Container

Ich möchte eine bestehende MS-SQL Datenbank in einen lokalen Docker Container kopieren.
Dazu werde ich zuerst die Struktur der Datenbank exportieren und in einem Docker Container neu aufbauen. Anschließend werden die Daten in CSV Dateien exportiert und in die containerisierte Datenbank importiert.

Datenbank Script exportieren

Den Export der Datenbankstruktur geschieht über das MS-SQL Management Studio. In der DB einloggen und dann im Objekt-Explorer über Tasks -> Skripts generieren aufrufen:

Datenbankscript generieren lassen(Symbolphoto)

Die Einstellungen wie vorgegeben belassen und die Datei script.sql speichern.

Das Script beginnt ungefähr so:

USE [master]
GO
CREATE DATABASE [DEMO]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'DEMO', FILENAME = N'C:/Pfad/DEMO.mdf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ), 
 FILEGROUP [DEMO_DAT] 
( NAME = N'DEMO_DAT', FILENAME = N'C:/Pfad/DEMO.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ), 
 FILEGROUP [DEMO_IDX] 
( NAME = N'DEMO_IDX', FILENAME = N'C:/Pfad/DEMO_IDX.ndf' , SIZE = 3552960KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
 LOG ON 
( NAME = N'DEMO_log', FILENAME = N'C:/Pfad/DEMO_Log.ldf' , SIZE = 2234880KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB )
GO

ALTER DATABASE [DEMO] ADD FILEGROUP [DEMO_DAT]
GO
ALTER DATABASE [DEMO] ADD FILEGROUP [DEMO_IDX]
GO

USE [DEMO]
GO
CREATE USER [DONALDDEMO] FOR LOGIN [DonaldDemo] WITH DEFAULT_SCHEMA=[DEMO_SCHEMA]
GO
GO
ALTER ROLE [db_owner] ADD MEMBER [DONALDDEMO]
GO

CREATE SCHEMA [DEMO_SCHEMA]
GO

Datenbank Script anpassen

Damit das Script im Container ausgeführt werden kann, müssen ein paar Anpassungen erfolgen.

Der Speicherort auf dem Quell-Server lautet: “C:/Pfad/”. Im Container lautet der Pfad: “/var/opt/mssql/data/”. Dies ist bei den Filenamen anzupassen.

Das Script definiert einen Benutzer für die Datenbank an und weist diesem ein Login zu:

CREATE USER [DONALDDEMO] FOR LOGIN [DonaldDemo]

User und Login sind also zwei verschiedene Sachen. Der User gehört zur Datenbank, der Login zur übergeordneten Datenbankinstanz “master”. Bevor über das Script die DB und der User angelegt werden, wird ein entsprechender Login angelegt:

USE [master]
GO
CREATE LOGIN [DonaldDemo] WITH PASSWORD = 'DonaldDemo12345678'

Eventuell vorhandenen AD-Accounts fliegen raus, die benötige ich nicht für die lokale Entwicklung.

Das angepasste Demo-Script:

USE [master]
GO
CREATE LOGIN [DonaldDemo] WITH PASSWORD = 'DonaldDemo12345678'

USE [master]
GO
CREATE DATABASE [DEMO]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'DEMO', FILENAME = N'/var/opt/mssql/data/DEMO.mdf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ), 
 FILEGROUP [DEMO_DAT] 
( NAME = N'DEMO_DAT', FILENAME = N'/var/opt/mssql/data/DEMO.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ), 
 FILEGROUP [DEMO_IDX] 
( NAME = N'DEMO_IDX', FILENAME = N'/var/opt/mssql/data/DEMO_IDX.ndf' , SIZE = 3552960KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
 LOG ON 
( NAME = N'DEMO_log', FILENAME = N'/var/opt/mssql/data/DEMO_Log.ldf' , SIZE = 2234880KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB )
GO

ALTER DATABASE [DEMO] ADD FILEGROUP [DEMO_DAT]
GO
ALTER DATABASE [DEMO] ADD FILEGROUP [DEMO_IDX]
GO

USE [DEMO]
GO
CREATE USER [DONALDDEMO] FOR LOGIN [DonaldDemo] WITH DEFAULT_SCHEMA=[DEMO_SCHEMA]
GO
GO
ALTER ROLE [db_owner] ADD MEMBER [DONALDDEMO]
GO

CREATE SCHEMA [DEMO_SCHEMA]
GO

Der Docker Container

Die Quell DB ist ein Microsoft SQL Server Version 11, was dem dem Release Namen “SQL Server 2012” entspricht. Das älteste Docker Image ist ein SQL Server 2017, was der Version 14 entspricht. Bei meinen Tests war es aber kein Problem, dass die DB in eine höhere Version migriert wird.
Um die Datenbank zu persistieren wird ein Docker Volume verwendet und das Image über Docker Compose gestartet.

version: "3.8"
services:

  sql-server-db:
    container_name: sql-server-db
    image: microsoft/mssql-server-linux:2017-latest
    ports:
      - "1433:1433"
    environment:
      SA_PASSWORD: "change_This_Password"
      ACCEPT_EULA: "Y"
    volumes:
      - mssql_vol:/var/opt/mssql

volumes:
  mssql_vol:

Start des MS-SQL Servers:

docker-compose up -d

Stoppen des MS-SQL Servers und bei Bedarf anschließendes Löschen des Volumes, um danach wieder frisch anfangen zu können:

docker-compose down
docker volume rm ms-sql_mssql_vol

Sobald der MS-SQL Server gestartet wurde, kann das SQL-Script in den Container kopiert und dort ausgeführt werden:

docker cp demo_script.sql sql-server-db:/var/opt/mssql/demo_script.sql
docker exec -it sql-server-db bash
root@dockerContainer: /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $SA_PASSWORD -i /var/opt/mssql/demo_script.sql # -o /var/opt/mssql/demo_script.out

Mit der Datenbank verbinden

Mit dem Microsoft SQL Server Management Studio kann man sich nun gegen die Datenbank verbinden:

  • Servername: localhost
  • Port: 1433
  • Anmeldename: DonaldDemo
  • Kennwort: DonaldDemo12345678

Daten Export

Für den Daten ex- und anschließenden import verwende ich ein Tool auf das ich hier nicht weiter eingehen werde (vgl. DB Export & Import) und beschreibe lediglich die logischen Schritte und die benötigten SQLs.

In einem ersten Schritt werden die zu exportierenden Tabellen der benötigten Schemata der Datenbank ermittelt und gespeichert:

  SELECT table_catalog, table_schema, table_name, table_type
    FROM DEMO.INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'
     AND TABLE_SCHEMA IN ('DEMO_SCHEMA')--, 'DEMO_SCHEMA_2')
ORDER BY TABLE_SCHEMA, TABLE_NAME

Als nächstes wird über die Tabellen iteriert (IDX), die Daten selektiert und gespeichert:

  SELECT *
    FROM [IDX:table_schema].[IDX:table_name]

Daten Import

Der Datenimport ist nicht ganz so einfach.

Eine Tabelle hat keinen Primärschlüssel und konnte nicht importiert werden. Da diese Tabelle auch keine Daten enthält, war das aber kein Problem und der Import dieser Tabelle konnte einfach ausgelassen werden. Später kann geprüft werden, ob diese Tabelle überhaupt noch verwendet wird oder final gelöscht werden kann.

Eine andere Tabelle hat eine Spalte mit der IDENTITY Eigenschaft und die Daten können nicht einfach so eingefügt werden, dazu muss zuerst das IDENTITY_INSERT für diese Tabelle eingeschaltet werden.

Allgemein besteht das Problem, dass die Tabellen über gewisse Constraints verfügen, die das naive importieren der Daten verhindern. Beispielsweise Foreign Keys, so dass die Daten in einer bestimmten Reihenfolge importiert werden müssten. Oder man deaktiviert für die Dauer des Imports alle Constraints und spart sich so die Sortiererei!

Das Import Script sieht ungefährt so aus:

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

--
SET IDENTITY_INSERT demo_schema.mydemotable ON

#IMPORT_CSV_FILES

-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Ein Problem mit Case Sensitiven Daten

Eine Tabelle bereitet mir noch Probleme:

In dieser Tabelle befinden sich Datensätze, deren Primärschlüssel sich lediglich in der Groß/Kleinschreibung unterscheiden, zB: “EinTollerDatensatz” und “eintollerdatensatz”. In der alten DB waren das zwei unterschiedliche Schlüssel, in der neuen DB leider nicht und so können einige Datensätze nicht importiert werden.

Das Problem könnte mit der Collation, bzw. im Deutschen: Serversortierung, zusammenhängen. In der Servereigenschaften ist diese immer standardmäßig “SQL_LATIN1_General_CP1_CI_AS”, wobei das “CI” für Case Insensitive steht. In den einzelnen Datenbanken des Servers kann man diese anpassen und eine Überprüfung der alten Datenbank ergab, dass diese “Latin1_General_CS_AS” ist. Daher habe ich der neuen Datenbank im Script nach dem CREATE DATABASE Befehl auch diese Eigenschaft zugewiesen:

CREATE DATABASE [DEMO]
# [...]

ALTER DATABASE [DEMO] COLLATE Latin1_General_CS_AS
GO

Leider führte das zu weiteren, multiplen Fehlern. Daher habe ich mich an dieser Stelle erstmal dazu entschlossen, die Collation nicht zu ändern und mit fehlenden Datensätzen weiter zu arbeiten.

Categories
Database

Freier und genutzter Speicherplatz eines ORACLE Tablespaces

Eine Datenbank soll migriert werden und für die Vorbereitungen wird die Größe der Datenbank benötigt, dabei handelt es sich um eine Datenbank des Herstellers Oracle. Um die Größe der Datenbank abschätzen zu können bin ich im Rahmen meiner Recherche auf folgendes SQL Statement gestoßen:

SELECT
FreierspeicherTbl.tablespace_name
,GesamtSpeicherTbl.GesamtSpeicher_In_MByte
,FreierspeicherTbl.FreierSpeicher_In_MByte
,(
GesamtSpeicherTbl.GesamtSpeicher_In_MByte -
FreierspeicherTbl.FreierSpeicher_In_MByte
) VerwendeterSpeicher_In_MByte
FROM
(
SELECT
dfs.tablespace_name
, SUM(dfs.bytes) / (1024 * 1024) FreierSpeicher_In_MByte
FROM dba_free_space dfs
WHERE 1=1
GROUP BY dfs.tablespace_name
) FreierspeicherTbl
,(
SELECT
ddf.tablespace_name
, SUM(ddf.bytes) / (1024 * 1024) GesamtSpeicher_In_MByte
FROM dba_data_files ddf
WHERE 1=1
GROUP BY ddf.tablespace_name)
GesamtSpeicherTbl
WHERE 1=1
AND FreierspeicherTbl.tablespace_name = GesamtSpeicherTbl.tablespace_name;

Dieses nützliche Statement stammt von dieser Seite.

Categories
Database

Search for lost data

A simplified description of a workflow in one of our supported application is:

AnotherServer is sending data through a middleware to MyAppServer. We store the received data in a MS-SQL database an process the data.

Now there is a problem in the AnotherServer system and they did not know, which data they have send to MyAppServer. So they send us a list of data IDs from that day and asked us to check in our application, which data was not received or received but not processed.

The list contains 600 IDs and it would take ~ 1 minute to check a single ID. So this would be work for ~10 hours. And not one single hour would be fun. After a short discussion we decided to do the data analytics directly in the MyApp database.

List of values

First problem is, that they send us an excel file containing the data IDs and we have to transform this in a way, so we can use this IDs in a SQL statement:

SELECT TempTable.Field1
FROM ( VALUES
(10),
(11),
(12)
      ) AS TempTable (Field1)

Connect list to table

LEFT OUTER JOIN [MyAppSchema].[ReceivedDataTable]
ON TempTable.Field1 = ReceivedDataTable.Data_ID

GROUP BY Field1, Data_ID, is_Processed
ORDER BY is_Processed DESC

Filter processed data

WHERE is_Processed != 1 
   OR is_Processed IS NULL