From 872f2c9a416ded668f30b30e980696d66b722055 Mon Sep 17 00:00:00 2001
From: Patrick Schmitz <pschmitz@berkeley.edu>
Date: Thu, 9 Jun 2011 20:25:32 +0000
Subject: [PATCH] CSPACE-3546, CSPACE-2903, CSPACE-2421, CSPACE-2170,
 CSPACE-2000, CSPACE-1894. Completed changes to make database and datasource
 configuration use environment vars and build properties, concentrating this
 info and removing passwords from the checked in code. Also separated the
 initial creation of the database from the additional work, which is done by
 scripts created from the gen_ddl target, which in turn depends upon the core
 DB being in place.

---
 build.properties                             |  2 +-
 build.xml                                    | 26 ++++++-
 installer/build.xml                          |  3 -
 services/id/installer/build.xml              | 79 --------------------
 services/security/client/build.xml           | 13 ----
 src/main/resources/db/mysql/init_db.sql      |  6 +-
 src/main/resources/db/postgresql/init_db.sql | 12 +--
 7 files changed, 33 insertions(+), 108 deletions(-)
 delete mode 100644 services/id/installer/build.xml

diff --git a/build.properties b/build.properties
index ddceb388b..7dd8d18a3 100644
--- a/build.properties
+++ b/build.properties
@@ -87,7 +87,7 @@ db.exceptionsorter=org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter
 
 
 #database common settings
-db.script.dir=${basedir}/src/main/resources/db/${db}
+db.script.dir=${jboss.server.cspace}/cspace/services/db/${db}
 db.user=${env.DB_USER}
 db.user.password=${env.DB_PASSWORD}
 db.nuxeo.user=nuxeo
diff --git a/build.xml b/build.xml
index 8d149d977..eb311ae24 100644
--- a/build.xml
+++ b/build.xml
@@ -207,7 +207,12 @@
         <ant antfile="services/build.xml" target="gen_ddl" inheritAll="false"/>
     </target>
 
-    <target name="create_db" 
+    <target name="create_db" depends="create_base_db"
+            description="create database(s), invoke subtasks for indexes, etc.">
+        <ant antfile="services/build.xml" target="create_db" inheritAll="false"/>
+    </target>
+
+    <target name="create_base_db" 
             description="create database(s), grant privileges to test users">
 			<sql driver="${db.jdbc.driver.class}"
              url="${db.jdbc.baseurl}"
@@ -219,7 +224,6 @@
                 <pathelement path="${db.driver.jar}"/>
             </classpath>
         </sql>
-        <ant antfile="services/build.xml" target="create_db" inheritAll="false"/>
     </target>
 
     <target name="import"
@@ -227,7 +231,7 @@
         <ant antfile="services/build.xml" target="import" inheritAll="false"/>
     </target>
 
-    <target name="deploy" depends="install,setup_jbossds.cfg,setup_cspaceds.cfg"
+    <target name="deploy" depends="install,setup_jbossds.cfg,setup_cspaceds.cfg,setup_initdb.sql"
             description="deploy services in ${jboss.server.cspace}">
         <!-- copy db scripts, etc. -->
 				<copy todir="${jboss.server.cspace}/cspace/services/scripts">
@@ -282,6 +286,22 @@
 			</copy>
     </target>
 
+		<target name="setup_initdb.sql" description="copy init_db scripts, replacing keywords">
+			<copy todir="${jboss.server.cspace}/cspace/services/db">
+        <fileset dir="${src}/main/resources/db"/>
+				<filterset>
+					 <!-- Note: double-subs from ${db.xyz.password} fail -->
+					<filter token="DB_CSPACE_USER" value="${db.cspace.user}" />
+					<filter token="DB_NUXEO_USER" value="${db.nuxeo.user}" />
+					<filter token="DB_JBOSS_USER" value="${db.jboss.user}" />
+				  <filter token="DB_CSPACE_PASSWORD" value="${env.DB_PASSWORD_CSPACE}" />
+					<filter token="DB_NUXEO_PASSWORD" value="${env.DB_PASSWORD_NUXEO}" />
+					<filter token="DB_JBOSS_PASSWORD" value="${env.DB_PASSWORD_JBOSS}" />
+				</filterset>
+			</copy>
+    </target>
+ 		
+ 
 
     <target name="undeploy"
             description="undeploy services from ${jboss.server.cspace}">
diff --git a/installer/build.xml b/installer/build.xml
index 73021a717..d02d54705 100644
--- a/installer/build.xml
+++ b/installer/build.xml
@@ -8,9 +8,6 @@
     <property file="build.properties" />
     <property name="src" location="src"/>
 
-		<!-- <property name="db" value="mysql"/> -->
-    <property name="db.script.dir" location="${basedir}/db/${db}"/>
-
     <condition property="osfamily-unix">
         <os family="unix" />
     </condition>
diff --git a/services/id/installer/build.xml b/services/id/installer/build.xml
deleted file mode 100644
index eb9d0e0ad..000000000
--- a/services/id/installer/build.xml
+++ /dev/null
@@ -1,79 +0,0 @@
-
-<project name="id-service-install" default="package" basedir=".">
-    <description>
-        CollectionSpace ID Service Installer Targets
-    </description>
-  <!-- set global properties for this build -->
-    <property file="${basedir}/../../build.properties" />    
-    <property name="src" location="src"/>
-
-    <property name="db" value="mysql"/>
-    <property name="db.script.dir" location="${basedir}/db/${db}"/>
-
-    <condition property="osfamily-unix">
-        <os family="unix" />
-    </condition>
-    <condition property="osfamily-windows">
-        <os family="windows" />
-    </condition>
-
-    <target name="install" description="">
-        <exec executable="echo">
-        		<arg value="Installer: Executing ID Service's &apos;install&apos; target." />
-				</exec>
-    </target>
-
-    <target name="config" description="" depends="install">
-        <exec executable="echo">
-        		<arg value="Installer: Executing ID Service's &apos;config&apos; target." />
-				</exec>
-    </target>
-
-    <target name="predeploy" depends="config">
-        <exec executable="echo">
-        		<arg value="Installer: Executing ID Service's &apos;predeploy&apos; target for the ID Service." />
-				</exec>
-				
-        <exec executable="echo">
-        		<arg value="Installer: Creating necessary database tables for the ID Service." />
-				</exec>				
-				<!-- Create the tables -->
-        <sql driver="com.mysql.jdbc.Driver"
-					url="jdbc:mysql://localhost:3306/cspace"
-					userid="root"
-					password="admin"
-					src="${db.script.dir}/create_id_generators_table.sql">
-					    <classpath>
-					        <pathelement path="${jboss.lib.cspace}/mysql-connector-java-5.1.7-bin.jar"/>
-					    </classpath>
-        </sql>
-        	
-        <exec executable="echo">
-        		<arg value="Installer: Loading tables for the ID Service." />
-				</exec>        	
-				
-				<!-- Load the tables -->
-        <sql driver="com.mysql.jdbc.Driver"
-	        url="jdbc:mysql://localhost:3306/cspace"
-	        userid="root"
-	        password="admin"
-	        src="${db.script.dir}/load_id_generators.sql">
-	            <classpath>
-	                <pathelement path="${jboss.lib.cspace}/mysql-connector-java-5.1.7-bin.jar"/>
-	            </classpath>
-        </sql>        	
-    </target>
-    
-    <target name="deploy" description="">
-        <exec executable="echo">
-        		<arg value="Installer: Executing ID Service's &apos;deploy&apos; target." />
-				</exec>
-    </target>
-    
-    <target name="postdeploy" description="">
-        <exec executable="echo">
-        		<arg value="Installer: Executing ID Service's &apos;init&apos; target." />
-				</exec>
-    </target>
-    
-</project>
diff --git a/services/security/client/build.xml b/services/security/client/build.xml
index 4c3cb31a4..9716c9a6e 100644
--- a/services/security/client/build.xml
+++ b/services/security/client/build.xml
@@ -125,19 +125,6 @@
         </copy>
     </target>
 
-    <target name="create_db"
-    description="create tables(s), indices for security service">
-        <sql driver="${db.jdbc.driver.class}"
-        url="${db.jdbc.cspace.url}"
-        userid="${db.cspace.user}"
-        password="${db.cspace.user.password}"
-        src="${db.script.dir}/test_authn.sql" >
-            <classpath>
-                <pathelement path="${db.driver.jar}"/>
-            </classpath>
-        </sql>
-    </target>
-    
     <target name="deploy" depends="install"
     description="deploy security service in ${jboss.server.cspace}">
         
diff --git a/src/main/resources/db/mysql/init_db.sql b/src/main/resources/db/mysql/init_db.sql
index 18cf584bb..63ccc287d 100644
--- a/src/main/resources/db/mysql/init_db.sql
+++ b/src/main/resources/db/mysql/init_db.sql
@@ -32,11 +32,11 @@ CREATE database nuxeo DEFAULT CHARACTER SET utf8;
 --
 -- grant privileges to test user on nuxeo and jbossdb databases
 --
-GRANT ALL PRIVILEGES ON jbossdb.* TO 'jboss'@'localhost' IDENTIFIED BY 'jbpw' WITH GRANT OPTION;
+GRANT ALL PRIVILEGES ON jbossdb.* TO '@DB_JBOSS_USER@'@'localhost' IDENTIFIED BY '@DB_JBOSS_PASSWORD@' WITH GRANT OPTION;
 FLUSH PRIVILEGES;
-GRANT ALL PRIVILEGES ON cspace.* TO 'cspace'@'localhost' IDENTIFIED BY 'cspw' WITH GRANT OPTION;
+GRANT ALL PRIVILEGES ON cspace.* TO '@DB_CSPACE_USER@'@'localhost' IDENTIFIED BY '@DB_CSPACE_PASSWORD@' WITH GRANT OPTION;
 FLUSH PRIVILEGES;
-GRANT ALL PRIVILEGES ON nuxeo.* TO 'nuxeo'@'localhost' IDENTIFIED BY 'nuxpw' WITH GRANT OPTION;
+GRANT ALL PRIVILEGES ON nuxeo.* TO '@DB_NUXEO_USER@'@'localhost' IDENTIFIED BY '@DB_NUXEO_PASSWORD@' WITH GRANT OPTION;
 --
 -- Grant privileges to read-only user on Nuxeo, for reporting. 
 --
diff --git a/src/main/resources/db/postgresql/init_db.sql b/src/main/resources/db/postgresql/init_db.sql
index 0d44a49f9..25aece532 100644
--- a/src/main/resources/db/postgresql/init_db.sql
+++ b/src/main/resources/db/postgresql/init_db.sql
@@ -7,17 +7,17 @@ DROP USER IF EXISTS nuxeo;
 DROP USER IF EXISTS cspace;
 DROP USER IF EXISTS jboss;
 
-CREATE ROLE nuxeo WITH PASSWORD 'nuxpw' LOGIN;
-CREATE ROLE cspace WITH PASSWORD 'cspw' LOGIN;
-CREATE ROLE jboss WITH PASSWORD 'jbpw' LOGIN;
+CREATE ROLE @DB_NUXEO_USER@ WITH PASSWORD '@DB_NUXEO_PASSWORD@' LOGIN;
+CREATE ROLE @DB_CSPACE_USER@ WITH PASSWORD '@DB_CSPACE_PASSWORD@' LOGIN;
+CREATE ROLE @DB_JBOSS_USER@ WITH PASSWORD '@DB_JBOSS_PASSWORD@' LOGIN;
 CREATE ROLE reader WITH PASSWORD 'read' LOGIN;
 
 --
 -- recreate jbossdb, cspace, and nuxeo databases
 --
-CREATE database jbossdb OWNER jboss;
-CREATE DATABASE cspace ENCODING 'UTF8' OWNER cspace;
-CREATE DATABASE nuxeo ENCODING 'UTF8' OWNER nuxeo;
+CREATE database jbossdb OWNER @DB_JBOSS_USER@;
+CREATE DATABASE cspace ENCODING 'UTF8' OWNER @DB_CSPACE_USER@;
+CREATE DATABASE nuxeo ENCODING 'UTF8' OWNER @DB_NUXEO_USER@;
 
 --
 -- Grant privileges to read-only user on Nuxeo, for reporting. 
-- 
2.47.3