OLE Satellite Development Databases - Retire

One-Time Setup

Also see System Requirements 3 for some additional database parameters which should be considered, especially for MySQL.

Impex Tool Installation

The projects listed below are for documentation only. As part of checking out the main ole project, Subversion will automatically check out both the ole-cfg-dbs project and the kul-cfg-dbs projects.

Check out needed projects from SVN. They should all be checked out into the same base directory with the name given in the first column. (/java/projects if using the standard paths)

Project Name	SVN URL
`kul-cfg-dbs`	https://test.kuali.org/svn/kul-cfg-dbs/branches/clover-integration	Database import/export tool
`ole-cfg-dbs`	https://svn.kuali.org/repos/ole-cfg-dbs/trunk	ole base dataset
`ole`	https://svn.kuali.org/repos/ole/trunk	OLE project for workflow and Rice data

Copy one of the ole/impex-build-local-*.properties to your home directory as impex-build.properties
Configure the import.* properties in that file for your local database.

impex-build.properties Reference

Bold properties below are especially important and may require modification.

Property Name	Default	Description
File Locations
torque.schema.dir	../../rice	Specifies the directory which contains all the XML files describing the Rice database tables.
torque.schema.dir.2	../../development	Specifies the directory which contains all the XML files describing the ole database tables.
ole Rice Data Overlay
post.import.liquibase.project	ole	Should be the name of the application project. It just needs to be set to activate the process.
post.import.liquibase.xml.directory	../../../work/db/rice-data	Directory containing liquibase scripts to execute.
ole Workflow Data
post.import.workflow.project	Should be the name of the application project. It is used to activate the process and in other default directory locations.
post.import.workflow.xml.directory	../../../work/workflow	Directory from which to read workflow XML files.
post.import.workflow.ingester.additional.command.line	-Ddatasource.ojb.platform=Oracle9i -Dbase.directory=/java -Dis.local.build= -Ddev.mode=true -Drice.ksb.batch.mode=true -Ddont.filter.project.rice= -Ddont.filter.project.spring.ide=	Extra parameters needed to make the ole build work. The ingester process needs to bring up the server for a few minutes. Make sure you change that first property to "MySQL" if that is your database platform.
Import Destination
import.torque.database	oracle	The type of database you will be importing into. Valid values are `oracle` and `mysql`.
import.torque.database.driver	oracle.jdbc.OracleDriver	The fully-qualified class name of your JDBC driver.
import.torque.database.url	jdbc:oracle:thin:@localhost:1521:XE jdbc:mysql://localhost:3306/oledev	The JDBC URL for your database.
import.torque.database.user	oledev	The user to use when importing the tables into the database.
import.torque.database.password	oledev	Password for the above account.
import.torque.database.schema	OLEDEV	The name of the schema to import the objects into. MUST be the same as the user. For Oracle, the schema name must be in upper case. In MySQL, you probably want this to be in lower case.
Administrative Properties
import.admin.user	kuluser_admin	Account which will be used to create and destroy users and schemas. On Oracle, this account must have execute privilege on the `KULUSER_MAINT_PK` package owned by the account specified in the `oracle.usermaint.user` property. For MySQL, this must be an account with administrative privileges (such as `root`).
import.admin.password	kuluser_admin	Password for the above account.
import.admin.url	jdbc:oracle:thin:@localhost:1521:XE jdbc:mysql://localhost:3306	The JDBC URL used to connect to the database for administrative purposes (creating / recreating users). This will default to the import URL, but must be changed for MySQL databases, as it will fail to connect if the schema at the end of the URL does not exist. The MySQL URL shown here has no default database specified.
oracle.usermaint.user	kulusermaint	Only used for Oracle imports. Account which contains the KULUSER_MAINT_PK package and has permissions to create and drop users and kill sessions.
oracle.usermaint.password	kulusermaint	Password for the above account. Only needed here to run the bootstrap target. (See instructions below.)

Initial Database Setup (Oracle)

Review the contents of the four SQL scripts provided
- Review and adjust user names
- Review and adjust tablespace names
- Set passwords (all default to the same as the user ID)
- In the package, alter the protected users and allowed prefixes as necessary.
Run the following scripts as SYS
- create_kul_developer.sql (creates the KUL_DEVELOPER role)
- system_grants.sql (grants needed permissions to KULUSERMAINT directly)
Complete Setup - Using Ant Scripts
- Configure the oracle.usermaint.* properties appropriately in impex-build.properties
- Configure the import.admin.user and import.admin.password properties appropriately.
- Run ant bootstrap
  
  This will create an administrative user (KULUSER_ADMIN) with execute privilege on the KULUSER_MAINT_PK package in the KULUSERMAINT schema. (as well as create the package) KULUSERMAINT and the KUL_DEVELOPER role must already exist. This process logs in as KULUSERMAINT.
Complete Setup - Manual Process
- Run the following scripts as KULUSERMAINT:
  - create_admin_user.sql (creates the KULUSER_ADMIN user)
  - kuluser_maint_pk.sql (install the user maintenance package)

Initial Database Setup (MySQL)

Configure the import.admin.user and import.admin.password properties appropriately.
Important: Configure import.admin.url to not include the target schema. (I.e., remove the "/oledev" from the end.) Failure to do this will cause the schema/user setup commands to abort.

User Account Setup

set the import.admin.* properties to point to an account which can run the user creation commands
- KULUSER_ADMIN on Oracle
- root or another account on MySQL
If you haven't already, configure the oracle.usermaint.user property to point at the schema where the package was created
Use the impex tool to perform database account setup:
```
ant create-schema
```

Daily Process

Initial Setup
- Perform the above steps to set up the database and create the user.
Daily Run
- In the early AM, run the import process. (The daily export runs from 2 - 3 am. (EST))
  - cd to /java/projects (or your equivalent)
  - perform a SVN update via a shell script to ensure you have the most recent version of the tool and all projects:
    - svn update ole
  - cd to ole/ole-cfg-dbs/db-impex/impex
  - Run the impex tool:
    ant empty-schema import
    This target will completely empty the schema referenced in the impex-build.properties file, and then run the import.