Process of adding ClassDB to a database is not automated (M)

[smurthys]

Multiple scripts files must be run to add ClassDB to a database. Users can run these files individually, but it will be helpful to have a Windows batch file and a Linux shell script to run all scripts on behalf of the user. Other alternatives should be considered as well.

See the ClassDB introduction document for an idea of how the proposed batch/script file will be used:

• [ ] Windows: perhaps prepareDatabase.bat
• [ ] Linux: perhaps prepareDatabase.sh

I don't know much about Linux shell scripts, but would we need to target a particular shell?

[wildtayne]

I'm not super well versed in the different Linux shells, but I think most of them have maintained compatibility with one of the older UNIX shell programs. Many of them have extensions that are incompatible with each other, but for our purposes we shouldn't need any of those features.

[smurthys]

It looks like psql might have meta commands that make it easy to provide an OS-independent "batch solution". The important requirement is to make it really easy to invoke the batch solution and stop execution if any of the batch steps causes an error.

[wildtayne]

One useful psql feature is the option ON_ERROR_STOP, which causes psql to immediately exit with error code 3 when a script encounters an error.

The simplest solution would be to create a batch file that takes user connection info, similar to the gradebook ones, and executes psql with -f switches for each file. Combined with ON_ERROR_STOP, this meets all the basic requirements.

This solution doesn't capture choosing to install optional components or not. Should the script only install the required components by default, and require the user to manually install the others?

Whatever we decide, I'll start by making prepareDatabase.bat, which will install the required database components to a single database.

[smurthys]

Yes, the availability of ON_ERROR_STOP is exactly what prompted me to imagine an OS-independent solution.

Instead of a batch file, couldn't we just have a .sql file which contains meta commands to invoke other .sql files? Please consider that alternative before proceeding with a batch file, because that would be OS-independent.

If it has to be a batch file: The Gradebook approach works, but we can keep it really simple (I'd like to change it even in Gradebook, but that is for another day).

Please make the file name prepareDB.bat (shorter and easier to speak of).

Here is my vision for how the batch file is invoked: prepareDB.bat <pathForDirWithSqlFiles> <other params>

<pathForDirWithSqlFiles> is the path to the directory containing the .sql files. This could be just . or a relative/absolute path. In any case, not ending with a \, unless you wish to add the ending \ as needed.

User will supply -h, -p, -d, etc. as necessary in any order in <other params> so that we don't fix an order or require they provide every detail.

Sketch of the content of batch file (with appropriate syntax adjustment):

SET sqlFilePath = %1
SHIFT \1
psql %1 %2 %3 %4 %5 %6 %7 %8 %9 -f %sqlFilePath%\initializeDB.sql ....

[wildtayne]

Having though more about it, I think a sql script is a better solution for what we want to do here. I think the following script is sufficient:

\set ON_ERROR_STOP on
\i initializeDB.sql
\i addHelpers.sql
\i addConnectionMgmt.sql
\i addUserMgmt.sql

This works by either executing psql from the command line with the -f switch pointing to this file, or in an interactive psql prompt using the \i command. In my local repo, I have tentatively called this file prepareDatabase.psql to distinguish it from normal sql files.

[smurthys]

Perfect and great idea to use the .psql extension. Please name the file prepareDB.psql.

I assume the usage will be as follows: psql <connection params> prepareDB.sql

I have added this usage to the Quick Start guide. Please review.

[wildtayne]

You have to use the -f switch to have it execute the file, but otherwise that is correct: psql <connection params> -f prepareDB.sql

[wildtayne]

7e1a2a7313895eb51e97bcd396f3d2e31b9737d2 has the installer script, however it uses the file names from #42. I'm holding off on doing a pull request until that issue is done/merged.

[smurthys]

Am I correct this issue is fixed by #103? Please verify and close the issue.

[wildtayne]

Yes, this was fixed. I'll close it now.