Proposed change with code: Specify Owner of Table in reactor.xml

[nowen@…]

I have projects in Oracle that use multiple schemas, so it useful for me to specify which owner(Schema) to use. I showed what it took for me to achieve this. Also the one other issue to be able to read from Oracle with no problems("OWNER"."TABLENAME" didn't work for me in Oracle? Without quotes owner.tablename works fine)

Anyways, Here's what an object looks like in reactor.xml:

<object name="ADDRESS" alias="ADDRESS" owner="WASTE">
  <hasOne name="ZIPCODE">
    <relate from="ZIPCODE_ID" to="ID" />
  </hasOne>
</object>

Here's the code I used to achieve it:

core/object.cfc function init:

	<cffunction name="init" access="public" hint="I configure the object." returntype="reactor.core.object">
		<cfargument name="alias" hint="I am the alias of the obeject being represented." required="yes" type="string" />
		<cfargument name="Config" hint="I am a reactor config object" required="yes" type="reactor.config.config" />
		
		<cfset setAlias(arguments.alias) />
		<cfset setConfig(arguments.Config) />
		<cfset setObjectConfig(getConfig().getObjectConfig(getAlias())) />
		<cfset setName(getObjectConfig().object.XmlAttributes.name) />
		<cfset variables.flag = "off">
		<cftry>
			<cfset setOwner(getObjectConfig().object.XmlAttributes.owner)>
			<cfcatch>
			</cfcatch>
		</cftry>
		<cfreturn this />
	</cffunction>

in data/oracle/Convention.cfc function formatObjectName: I had to remove double quotes:

	<cffunction name="formatObjectName" access="public" hint="I format the object/table name" output="false" returntype="string">
		<cfargument name="ObjectMetadata" hint="I am the metadata to use." required="yes" type="reactor.base.abstractMetadata" />

		<cfreturn '#arguments.ObjectMetadata.getOwner()#.#arguments.ObjectMetadata.getName()#' />
	</cffunction>

and in reactor/data/oracle/ObjectDao.cfc function readObject - To not overwrite the owner I added an if:

<cfif arguments.Object.getOwner() eq "">
				<cfset arguments.Object.setOwner( qObject.TABLE_OWNER ) />
			</cfif>

in the whole function:

	<cffunction name="readObject" access="private" hint="I confirm that this object exists at all.  If not, I throw an error." output="false" returntype="void">
		<cfargument name="Object" hint="I am the object to check on." required="yes" type="reactor.core.object" />
      <!--- @@Note: added "var" --->
      <cfset var qObject = 0 />

		<cfquery name="qObject"   datasource="#getDsn()#" username="#getUsername()#" password="#getPassword()#">
			SELECT  object_type  as TABLE_TYPE,
					owner        as table_owner
			FROM all_objects
			where object_type in ('TABLE','VIEW')
			and	object_name = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="64" value="#arguments.Object.getName()#" />

		</cfquery>

		<cfif qObject.recordCount>
			<!--- set the owner --->
			<cfif arguments.Object.getOwner() eq "">
				<cfset arguments.Object.setOwner( qObject.TABLE_OWNER ) />
			</cfif>
			<cfset arguments.Object.setType( lcase(qObject.table_type) ) />
		<cfelse>
			<cfthrow type="reactor.NoSuchObject" />
		</cfif>
	</cffunction>

and in the query qFields in readFields of /data/racle/ObjectDao.cfc I needed to filter by owner because I was getting the wrong table otherwise:

Snippet of change:

<cfif arguments.Object.getOwner() neq "">
						AND	col.owner = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="64" value="#arguments.Object.getOwner()#" />
					</cfif>

in

<cfquery name="qFields"  datasource="#getDsn()#" username="#getUsername()#" password="#getPassword()#">
			 SELECT DISTINCT
               	    col.COLUMN_NAME       as name,
                    CASE
                          WHEN primaryConstraints.column_name IS NULL THEN 0
                          ELSE 1
                    END                   as primaryKey,
                    /* Oracle has no equivalent to autoincrement or  identity */
                    'false'                     AS "IDENTITY",
                    col.NULLABLE,
                    col.DATA_TYPE         as dbDataType,
                    col.DATA_LENGTH       as length,
                    ''      as "DEFAULT"
              FROM  all_tab_columns   col,
                    ( select  colCon.column_name,
                   			  colcon.table_name
                    from    all_cons_columns  colCon,
                           all_constraints   tabCon
                    where tabCon.table_name = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="128" value="#arguments.Object.getName()#" />
                         AND colCon.CONSTRAINT_NAME = tabCon.CONSTRAINT_NAME
                         AND colCon.TABLE_NAME      = tabCon.TABLE_NAME
                         AND 'P'                    = tabCon.CONSTRAINT_TYPE
                   ) primaryConstraints
              where col.table_name = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="128" value="#arguments.Object.getName()#" />
              		and col.COLUMN_NAME        = primaryConstraints.COLUMN_NAME (+)
                    AND col.TABLE_NAME         = primaryConstraints.TABLE_NAME (+)
					<cfif arguments.Object.getOwner() neq "">
						AND	col.owner = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="64" value="#arguments.Object.getOwner()#" />
					</cfif>

		</cfquery>

I thought this might be handy for other people. I needed all these change to populate a page with my Oracle data. I still have the issues of saving in Ticket #41 though.

[mgw4jc]

I attached the two files I modified. If the solution is an owner attribute, my changes would not work. As I mentioned on the list, I approached the dot-notated name as a list with a '.' delimiter. Not super pretty. My changes can easily be found along with the original code by searching for "MW Hack".

My object definition looks like this: <object name="mySchema.Owner.fooTable" alias="fooTable" />

-Matt Williams

[dhughes]

True support for schemas/owners will be added soon.