No easy way to create a ClassDB database (M)

[smurthys]

I propose a createClassDB.sql to create a DB with the role ClassDB as owner. This script should be run after running prepareClassServer.sql but before running prepareClassDB.sql.

• [ ] Assert current user has CREATEDB privilege
• [x] Test if app-specific roles are already created (as in prepareClassDB.sql)
• [ ] Create a database with the given name and assign ownership to role ClassDB
• [x] Assign privileges to ClassDB for PUBLIC schema etc. if that is required

Parameterizing the script to accept a database name probably fixes psql as the client, but it can be instructive to consult the docs for the createdb program.

[smurthys]

All tasks except creating a database are now accomplished in initializedDB.sql in v1.0.

A possible solution is to build on the .psql batch script developed in #99.

[wildtayne]

I agree that prepareDB.psql is the right place to do database creation. One possible approach is to have the user supply a desired database name prior to running prepareDB.psql. This can be accomplished using psql's script variables.

The user would set the value of a script variable (in this example, DB_NAME) by either invoking the -v switch when starting psql (-v DB_NAME=<databaseName>), or running the meta-command \set (\set DB_NAME <databaseName>) once inside psql.

Then, the following code added to the beginning of prepareDB.psql will create a new database with the name stored in DB_NAME, and then connect to it prior to running the ClassDB preparation scripts.

CREATE DATABASE :DB_NAME WITH OWNER = classDB;
\connect :DB_NAME

I did a quick test of this code, and it appears to work. If we do use this method, it will be necessary to add the CREATEDB assertion to prepareDB.psql, as mentioned in the first comment.

[smurthys]

I like the approach to using the -v psql switch. However, I highly recommend using the variable name NEW_DB_NAME, because DB_NAME is too close to the psql variable DBNAME.

After much thinking and research, I have identified two alternative approaches: a simple alternative and a rigorous alternative. I recommend taking the simple alternative for M2. Also, because the existing solution does everything except create the database, this issue gets low priority in the interest of time: it should be addressed only if no other issues of higher priority exist.

The simple alternative is to create a createAndPrepareDB.psql which blindly creates the new database, switches connection, and invokes the existing prepareDB.psql. Any error in creating the new DB and connecting to it will just stop the script. The most likely error is that the specified DB already exists.

The rigorous alternative takes the following approach:

• Use the existing prepareDB.psql as the starting point
• Create the new DB (and connect to new DB) only if variable NEW_DB_NAME is non-empty no database with that name already exists. The conditional execution is necessary to accommodate the cases where the current DB is the DB to be initialized (which is actually the current usage)
• Complete DB creation and connection before invoking initializeDB.sql. This way initializeDB.sql will be independent of psql (that script presently works on current_database())

Here are some considerations:

• psql does not support conditional execution: means a OS script is likely needed
  • Executing psql with -l switch prints a list of databases and exits psql. An OS script can grep the result to see if a database with a given name already exists
  • We have stayed away from OS scripting because that adds new dependencies, not to mention a lot of development effort
• CREATE DATABASE cannot be executed in a transaction
  • I assume CREATE DATABASE can be executed inside a PL/pgSQL IF block: this assumption should be tested first before proceeding
• It is possible to test if a database exists in a server using the query

  SELECT COUNT(*) FROM pg_database WHERE datname=':NEW_DB_NAME';

• Once we are sure CREATE DATABASE can be conditionally executed, I propose we put the DB creation and the connection switching in a separate script createDB.psql
  • It should be a psql file because it will contain a psql meta command
  • I assume it is possible to contain a meta command inside a PL/pgSQL code block (but I have my doubts)

[smurthys]

Removing from M2 due to time constraint.

[smurthys]

I am closing this issue because creating a database simply requires executing the CREATE DATABASE and the installation scripts in ClassDB 2.0 make it extremely easy to add ClassDB to any database.