Setup OpenCoral Database Users and Schema
This section describes how to setup the OpenCoral database users and schema.
Initial Setup of Database Users
Now we need to create the database tables, insert bootstrap data, and some initial fab data. As postgres, set the database and its password [db.password].
$ psql template1 postgres Welcome to psql 9.xx.xx, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit template1=# alter user postgres with password '[db.password]'; ALTER USER template1=# \q
Add the language plpgsql to the template1 database. This langauge will be used for any stored procedures that are used by Coral. If it is added at this time, it should be availabe to other databases that are subsequently created.
$ createlang plpgsql template1 CREATE LANGUAGE
Now add user coraldba to postgres
$ createuser -d -a -P coraldba Enter password for new user: [coraldba.password] Enter it again: [coraldba.password] Password: [db.password] CREATE USER
Now add database coral
$ createdb -e -O coraldba coral 'OpenCoral Database' Password: [db.password] CREATE DATABASE coral; CREATE DATABASE Password: [db.password] COMMENT ON DATABASE coral IS 'OpenCoral Database'; COMMENT
Make sure that the plpgsql prodecural languange is available to the coral database:
$ createlang -l coral Procedural Languages Name | Trusted? ---------+---------- plpgsql | yes
If plpgsql is not associated with the coral database, it may be added at any time with the command:
$ createlang plpgsql -U coraldba coral CREATE LANGUAGE
Setting Up Server-specific Database Users and Database Tables
At this point we've created an empty database named coral that is owned by the main database user coraldba. Now we are about to create a series of secondary database users that will correspond to each of the Coral servers and will each own the tables that they need to control. In other words, the Coral equipment server will have tables that are owned by the database user eqmgr and that database user will own and manage the database tables that are used by the equipment server.
Edit /path_to/opencoral/src/sql/Postgres/initialSetup/lab_db.sql replace all 'password' to your coraldba passwords.
Once you have changed the passwords in the file lab_db.sql to something that you want, you can run all of the sql script files by typing the command:
Make will prompt you for the coraldba password and then, as it runs some of the subsequent SQL files, it will prompt you for the password of the other database users.
Once these commands have completed, you should edit lab_db.sql to chage the password that you previously entered back to 'password'. This is important to avoid your database password from being easily visible to anyone looking through your filesystem.
The results of each *.sql script will be stored in a log file of the same name. In other words, the result of running lab_db.sql are stored in lab_db.log.
Implicit casting in Postgresql 8.3 and Newer
Versions of Postgresql newer than 8.2 have removed implicit casts based on the contention that implicit casts create problems. While this may be true in certain instances, it is also true that different databases .... Oracle and Postgresql in particular in the case of Coral ... have different syntax for explicit casting from one type to another. While a future version of Coral may include only explicit casts, Coral currently supports and makes use of implicit casts.
If you are running Postgresql 8.3 or newer you need to follow the following instructions to add an appropriate number of implicit casts to make Postgresql 8.3 or newer handle the same explicit casts that are supported in Postgresql 8.2 or older, and all versions of Oracle.
Go to https://opencoral.mit.edu/files/Pg83-implicit-casts.sql.gz and download that file to your local machine.
Uncompress it with the command 'gunzip Pg83-implicit-casts.sql.gz'. Next, create those implicit casts with the command:
psql coral coraldba -f Pg83-implicit-casts.sql