# Basel

## Local stack

Note: I'm creating multiple directories, but stacks could be in one single directory.
Use the `-f` option of `docker-compose` to specify the file to use in this case.

**Required tools:**
- `docker`
- `docker-compose`
- `jq`
- a text editor
- a shell
- a web browser

Only tested on a Linux machine.

### Stardog

Make sure you have a valid Stardog license (you can get one at: https://www.stardog.com/get-started/).

Clone the `stardog-docker` repository somewhere on your machine and go in the directory:

git clone [e-mail]:Staatsarchiv-Basel-Stadt/stardog-docker.git \
  && cd stardog-docker

Put your license file at `./volumes/stardog/stardog-license-key.bin`.

Start the stack using:

docker-compose up --build

### PostgreSQL

#### Run the stack

Since Oracle is not open and I don't want to buy a license, I'm using Postgres instead.

Create a directory `postgresql-stack` with the following `docker-compose.yaml` file:

version: "3"

services:
  db:
    image: docker.io/library/postgres:latest
    environment:
      - POSTGRES_PASSWORD=example
    ports:
      - 5432:5432

  adminer:
    image: docker.io/library/adminer:latest
    ports:
      - 8080:8080

Run the stack by running the following command:

docker-compose up

#### Create database and user

Open your browser at http://localhost:8080/?pgsql=db&username=postgres ; it's the Adminer interface that allows you to query the PostgreSQL easily.

And use the following informations:
- System: `PostgreSQL`
- Server: `db`
- User: `postgres`
- Password: `example`

You should now be able to connect.

Click on the `SQL request` link, and run the following request:

CREATE DATABASE test;
CREATE USER test WITH PASSWORD 'test';
GRANT ALL PRIVILEGES ON DATABASE "test" to test;

This will create a `test` user with `test` as password, and create a `test` database where the `test` user will have full access.

#### Create some tables

In the Adminer interface to run SQL queries, make sure you select the `test` database.

Then run the following queries to create all required tables:

DROP TABLE IF EXISTS MVK_OGD_VRZNG_ENHT_1;
CREATE TABLE MVK_OGD_VRZNG_ENHT_1 (
  TITEL text,
  SIGNATUR text,
  STUFE text,
  ENTSTEHUNGSZEITRAUM text,
  DARIN text,
  ARCHIVALIENART text,
  ENTHAELT text,
  ZUGANGS_UND_BENUTZUNGSBEDINGUN text,
  RECHTSSTATUS text,
  ZUGANGSBESTIMMUNGEN text,
  SPRACHE text,
  FORM_UND_INHALT text,
  ID_NR int,
  PARENT_ID_NR int,
  HIERARCHIE_PFAD text,
  SCHUTZFRISTENDE text,
  ZUGAENGLICHKEIT_NM text,
  ARCHIVGESCHICHTE text,
  UMFANG_GESCHAETZT text,
  LAUFMETER text,
  DATENVOLUMEN_MB text,
  ORGANISATIONSZUGEHOERIGKEIT text,
  GESCHICHTE text,
  ORT text,
  FUNKTION_UND_AKTIVITAET text,
  ALLGEMEINER_KONTEXT text,
  ANZEIGEFORMULAR_ID int,
  ANZAHL_DATEIEN int,
  AKTENBILDNER_PROVENIENZ_TEXT text,
  FRUEHERE_STABS_SIGNATUR text,
  ALTE_SIGNATUREN text,
  FRUEHERE_SIGNATUR text,
  VERWALTUNGSGESCHICHTE_BIOGRAFI text,
  ANZAHL text,
  PHYSISCHE_BESCHAFFENHEIT text
);

DROP TABLE IF EXISTS MVK_OGD_VE_ABLFR_BZHNG_1;
CREATE TABLE MVK_OGD_VE_ABLFR_BZHNG_1 (
  ID_NR_VRZNG_ENHT int,
  ID_NR_ABLFR int
);

DROP TABLE IF EXISTS MVK_OGD_VE_PRTNR_BZHNG_1;
CREATE TABLE MVK_OGD_VE_PRTNR_BZHNG_1 (
  ID_NR_PRTNR int,
  ID_NR_VRZNG_ENHT int
);

DROP TABLE IF EXISTS MVK_OGD_VE_DSKRP_BZHNG_1;
CREATE TABLE MVK_OGD_VE_DSKRP_BZHNG_1 (
  ID_NR_DSKRP int,
  ID_NR_VRZNG_ENHT int
);

DROP TABLE IF EXISTS MVK_OGD_DSKRP_1;
CREATE TABLE MVK_OGD_DSKRP_1 (
  ID_NR int,
  "THESAURUS" text,
  BEZEICHNUNG text,
  BESCHREIBUNG text
);

DROP TABLE IF EXISTS MVK_OGD_ABLFR_1;
CREATE TABLE MVK_OGD_ABLFR_1 (
  ID_NR int,
  JAHR_LAUF_NR int,
  TYP text,
  SUBTYP text,
  AKTENBILDENDE_STELLE text,
  ZEITRAUM_PERIODE text
);

DROP TABLE IF EXISTS MVK_OGD_PRTNR_1;
CREATE TABLE MVK_OGD_PRTNR_1 (
  ID_NR int,
  ID_NAME text
);

This was taking a lot of time to deduct this structure from the mapping file.

#### Get the networking IP address

If you are on a Linux machine and created the stack in the directory I mentionned, you should have a docker network called `postgresql-stack_default`.

Get the gateway address using the following command:

docker network inspect postgresql-stack_default \
  | jq -r '.[0].IPAM.Config[0].Gateway'

For this example, it will be `172.27.0.1` for me.

Update the name of the network according the name of the directory you created.

This step is required, because we will run a docker stack with Stardog, and Stardog should be able to query the PostgreSQL database using a specific address which is not `localhost`.

### Mapping

Clone the `staatsarchiv-ld-pipeline` and the `StABS-scope2RDF` repos somewhere, and go in the `staatsarchiv-ld-pipeline` directory:

git clone [e-mail]:stabs/staatsarchiv-ld-pipeline.git \
  && git clone https://github.com/Staatsarchiv-Basel-Stadt/StABS-scope2RDF \
  && sed -i 's/AND WHERE/AND/g' StABS-scope2RDF/src-gen/mapping-stabs.r2rml.ttl \
  && cd staatsarchiv-ld-pipeline

The `sed` command is for tweaking one query from the mapping so that it can work with PostgreSQL.

Install all required dependencies using:

npm ci

Create the following file `./credentials/scope-virtual.properties` with the following content:

jdbc.url=jdbc:postgresql://172.27.0.1:5432/test
jdbc.driver=org.postgresql.Driver
jdbc.username=test
jdbc.password=test

Note: the `jdbc.url` contains the gateway IP address we got in a previous step, update it if needed.

Run the mapping update using the following commands:

export FORCE_UPDATE=true
export GIT_REPO=../StABS-scope2RDF
npm run mappingUpdate

### Check that it worked

Go to Stardog Studio: https://stardog.studio/

Connect to the local Stardog instance:
- username: `admin`
- password: `admin`
- endpoint: `http://localhost:5820`

Go to the virtual graph tab, and you should see the `scope-virtual` graph that we created.

## Production

Conclusion: the script is working as expected.

To fix the one in production, you can try to look the following points:

- Check that the `jdbc.driver` is set to `oracle.jdbc.driver.OracleDriver` and that credentials are correct.
- Test by changing the JDBC driver version, … in the `stardog-docker` repo ; I will not handle this since I cannot test anything.
- Check that all environments variables are OK
- Check proxies and all that complexity ; I don't want to take care of this (and can't)

I will not be able to provide more help on this, since the script is working fine.