Create sql script that creates the FCREPO and GEMINI DBs upon Mariadb init

[g7morris]

• add a .sql script that runs upon the first mariadb init which will create the following:
  • drupal db along with drupal_db_user and password. (Default already)
  • fcrepo db along with fcrepo_db_user and password.
  • gemini db along with gemini db user and password.

[g7morris]

Putting my comment from #6 (comment)

"Changes in direction prior to merge

@niakathone I hate to be a pain but can we revert the current Docker-compose service name changes of drupal_db back to using only mariadb please? I regret wasting your time here, apologies. I'm concerned with a big resource change like having three mariadbs and not having enough group architectural input as of yet. We can certainly discuss further the possibility of using multiple mariadbs as the opportunity arises post merge.

For now, I've discussed with @noahwsmith as well and we think the easiest thing we can do without having to manage 3 separate mariadbs, is to continue to use one and to add a .sql script that runs upon the first mariadb init which will create the following:

drupal db along with drupal_db_user and password. (Default already)
fcrepo db along with fcrepo_db_user and password.
gemini db along with gemini db user and password.

This way everything is created from the start even if users don't use fcrepo or gemini later on at least they exist and are ready to use from the first start.

I will work on this script and adapt from what we already have in MVP3. Per the https://hub.docker.com/_/mariadb section called Initializing a fresh instance, It would then be placed in the mariadb docker-entrypoint-initdb.d as a sql file which can first check upon init if all of the users and dbs exist and then creates them.

I am not sure how we would make that SQL file dynamic so I'm guessing we'll hardcode user values for now and iterate after the merge."

[g7morris]

From testing on 4/30 and reporting on the isle8-architecture Slack channel:

So it turns out that when I was testing the .sql files / docker-entrypoint.db trick using the wodby image everything worked great upon the first init but the official mariadb image just craters, fails and exits as a container.

The official mariadb dockerhub page encourages folks to use this same method of placing sql files but it simply doesn't work period due to permissions errors. So I'm scratching my head here as to why they would set users up this way and failing to find any recent working docker-compose examples that work.

From the https://hub.docker.com/_/mariadb Initializing a fresh instance section:

When a container is started for the first time, a new database with the specified name will be created and initialized with the provided configuration variables. Furthermore, it will execute files with extensions .sh, .sql and .sql.gz that are found in /docker-entrypoint-initdb.d. Files will be executed in alphabetical order. You can easily populate your mariadb services by mounting a SQL dump into that directory and provide custom images with contributed data. SQL files will be imported by default to the database specified by the MYSQL_DATABASE variable."

The wodby mariadb service in the docker-compose.yml did this: (truncated for display)

 mariadb:
    image: wodby/mariadb:$MARIADB_TAG
    container_name: "${PROJECT_NAME}_mariadb"
    ...
    volumes:
      - mariadb-data:/var/lib/mysql
      - ./scripts/fcrepo/sqlfiles:/docker-entrypoint-initdb.d

In any event, I think given this challenge, isle-fcrepo would be a good first candidate for S6 and confd.
@nigelgbanks ^^ 👍

For next week, I'll update the isle-fcrepo image to use those tools to be able to do something similar as to what we do in ISLE7. Upon init, the ISLE7 fedora container checks if there is a database server with a healthcheck once that db server / container is up and ready, the ISLE7 fedora container is able to install a new db and user. This would be a more eloquent solution in that, if a user doesn't want fcrepo they don't have to manage or deal with the extra dbs.

Here are a couple of links from ISLE 7 land for reference:

• https://github.com/Born-Digital-US/isle-fedora/blob/master/rootfs/etc/confd/conf.d/sql.toml

• https://github.com/Born-Digital-US/isle-fedora/blob/master/rootfs/etc/confd/templates/sql/fedora.sql.tpl

• https://github.com/Born-Digital-US/isle-fedora/blob/master/rootfs/etc/cont-init.d/01-fedora-config

Both confd and S6 are a fairly easy install and @dbernstein we can talk about how this would impact the code.

Or if that sounds too hacky / gorey, I can switch to building our own mariadb image which would handle this but we'd be back to non-fcrepo users having to contend with extra dbs

Either way sadly the current official mariadb image doesn't seem to support using its own prescribed docker-entrypoint method of creating dbs which in turn is causing the current build's fcrepo and gemini services to crash.

@nigelgbanks @dannylamb @nikathone and @dbernstein Curious your thoughts on this new direction?

[g7morris]

From a discussion with @dannylamb we might be able to fold the gemini db creation into the gemini image using the symfony console and confd. Consider splitting this ticket into two (fcrepo & gemini)

[g7morris]

update islandora-deprecated/isle-fcrepo#4

Working fcrepo and tomcat. @dannylamb once accepted, I think you should move onto the SYN part. #40 I've pushed to the islandora repo using latest

[g7morris]

Closing this ticket in favor of #17