Oracle schema holder

Our starting point is always a final progress df and the incremental progress df to get from previous version to current version.

We then migrate the final complete progress df to oracle / sql using data administration tool (manually).

We then generate the incremental sql scripts with a custom tool that uses information from the incremental progress df combined with the new schema holder. There is an option in data administration to do this now, but I'm not sure how well if works.

Fresh new schema holders are deployed.

From Ant / PCT / Jenkins the tables are recreated per test run:

<target name="oracle">
		<pct:db_create dbName="exactcssh" destDir="${test}/db" schemaFile="${other}/db/oracle/exactcssh.df" />
		<antcall target="create-tables.oracle">
			<param name="oracle_user" value="${db.oracle.user.report.exact}" />
			<param name="db.script" value="exactcs" />
		</antcall>

		<echo file="${test}/db.exactcs.pf">-db db/exactcssh -1 -db exactcs -dt ORACLE -ld exactcs -U ${db.oracle.user.report.exact}/secret@${db.oracle.tnsname}</echo>
</target>

	<!-- oracle: create tables -->
	<target name="create-tables.oracle">

		<property environment="env" />
		<property name="oracle.jdbc" value="${env.ORACLE_HOME}/jdbc/lib/ojdbc6.jar" />

		<!-- sql script attempts to drop everything before creating it, which errors when it does not yet exist, so onerror="continue" -->
		<sql driver="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:oci:@${db.oracle.tnsname}" userid="${oracle_user}" password="secret" classpath="${oracle.jdbc}" onerror="continue">
			<transaction src="${other}/db/oracle/${db.script}.sql" />
		</sql>

	</target>

Thanks Stefan. I'm trying to deal with a different case where the schema holder is not created from a source Progress schema ; the Oracle database is the main DB of another Java application, so the only option I have is to pull the schema from Oracle, and entirely refreshing the schema holder seems to be the best solution.

Guess I'll push this code to PCT if it's stable enough...

In my opinion the easiest way by far is to replace the schema holder database during the deployment.

It just really simplifies the entire process. You deploy exactly what you compiled and tested against and the fall back is as simple as putting the old copy out there again.

In this context, anything going to deployment has to be built from source, there's no way to tell deployment guys to just copy a few files from the QA server... So, at some stage, I have to create the schema holder by code (but the delete part may be optional).

How do they deploy images and other binary files? Why are you recompiling between DEV,QA and Prod?

I have always generated the proper schema holder in DEV and then promoted it to QA, PreProd and Prod.  Just like R-Code and binary files.

Posting again, my answer didn't show up.

Images and some other binary files are stored in the code repository, and included in the build artifact. However, I can't imagine storing a schema holder in the code repository, that's just something that should be generated during the build process.

We store both (for ease) - the .df and the binary.

Automated builds use the df of the oracle schema holder to create the schema holder (see Ant snippet above).