README.md

# stacomi_db


[![pipeline status](https://forgemia.inra.fr/stacomi/stacomi_db/badges/develop0.6.1/pipeline.svg)](https://forgemia.inra.fr/stacomi/stacomi_db/-/commits/develop0.6.1) 

Scripts related with database development in stacomi.

## Docker

This repository also serves to build [docker](https://www.docker.com/) images with an example database. 
These images are on [this gitlab repository](https://forgemia.inra.fr/stacomi/stacomi_db/container_registry/368).
Tags are given as follows:
- a database version ;
- a postgresql version ;
- a postgis version ;
- an optional `dev` tag. Any image with this tag is aimed to be used in a development context and **should not be used** in a production environment.

This the tag `0.6.0-pg9.6-ps3.4` means that the database version is `0.6.0`, the postgresql version is `9.6` and the
  postgis version is `3.4`.
  
  The image is build on a [postgis docker](https://registry.hub.docker.com/r/postgis/postgis/).

The arguments (`ARG`or when building the image `--build-arg`) `postgresql_version` and `postgis_version` allow to use the right [version of docker postgis image](https://hub.docker.com/r/postgis/postgis/tags).

### How to use the image

- You should have [docker installed](https://docs.docker.com/get-docker/) in your machine (computer or server).
- You should pull the desired image using one of the following line depending if you want the latest version or [a
   given version](https://forgemia.inra.fr/stacomi/stacomi_db/container_registry/368), eventually after [being authenticate to the registry](https://docs.gitlab.com/user/packages/container_registry/authenticate_with_container_registry/)

~~~ shell
docker pull registry.forgemia.inra.fr/stacomi/stacomi_db/stacomi_db:latest
docker pull registry.forgemia.inra.fr/stacomi/stacomi_db/stacomi_db:0.6.0-pg9.6-ps3.2
~~~

- Run a docker (example with latest version)
~~~ shell
docker run --name postgres_stacomi --rm  -e POSTGRES_PASSWORD=yourpwd -d -p 5435:5432 registry.forgemia.inra.fr/stacomi/stacomi_db/stacomi_db:latest
~~~

`postgres_stacomi` can be replaced by any name (it's the docker container name on your machine) ; `yourpwd` **should be
changed** to your custom password for the postgres user (refer to the [docker
documentation](https://registry.hub.docker.com/_/postgres/) if you need more options but this one is mandatory) ; `5435`
can be replaced by your custom port on your machine ; `latest` can be replaced by [any valid
version](https://forgemia.inra.fr/stacomi/stacomi_db/container_registry/368) of our docker.

- your docker is now up. That means that you have a postgresql server running with postgis enabled and
  our sample database installed. You can use any [postgresql
  client](https://wiki.postgresql.org/wiki/PostgreSQL_Clients) to access to it. **Note that it can take few minutes to have the database fully setup (and thus the postgresql server really running and accepting connection).**

For example you can use psql to connect to the database server :
~~~ shell
psql -h localhost -p 5435 -U postgres
~~~

you can even use the psql from the docker (replace `postgres_stacomi` by your container name) :
~~~ shell
docker exec postgres_stacomi psql --version
~~~

- it can also be used inside a [gitlab CI/CD pipeline](https://docs.gitlab.com/ee/ci/pipelines/). We recommend to use
  the image as a service (note that `POSTGRES_PASSWORD` is required and any other postgres options can be defined under
  the `variables`). The hostname is defined by the `alias` that can be changed to what  you want.

~~~ yaml
variables:
  POSTGRES_PASSWORD: yourpwd
  
services:
  - name:  registry.forgemia.inra.fr/stacomi/stacomi_db/stacomi_db:latest
    alias: db
~~~

The database can then be called inside your script.

~~~ yaml
test_psql:
  image: ubuntu
  stage: test_psql
  script:
    - apt update
    - apt install -y postgresql-client
    - psql --version
    - export PGPASSWORD=$POSTGRES_PASSWORD
    - psql -U postgres -h db  -c "SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';"
    - psql -U postgres -h db -c "select * from iav.t_dispositifcomptage_dic" bd_contmig_nat
~~~