PageRenderTime 445ms CodeModel.GetById 221ms app.highlight 3ms RepoModel.GetById 219ms app.codeStats 0ms

/wheels/model/adapters/Oracle.cfc

http://cfwheels.googlecode.com/
ColdFusion CFScript | 238 lines | 220 code | 12 blank | 6 comment | 21 complexity | eae4a8e28e17b09c4a68ad198d92965a MD5 | raw file
  1<cfcomponent extends="Base" output="false">
  2
  3	<cffunction name="$generatedKey" returntype="string" access="public" output="false">
  4		<cfreturn "rowid">
  5	</cffunction>
  6
  7	<cffunction name="$randomOrder" returntype="string" access="public" output="false">
  8		<cfreturn "dbms_random.value()">
  9	</cffunction>
 10
 11	<cffunction name="$getType" returntype="string" access="public" output="false">
 12		<cfargument name="type" type="string" required="true">
 13		<cfargument name="scale" type="string" required="true">
 14		<cfscript>
 15			var loc = {};
 16			switch(arguments.type)
 17			{
 18				case "blob": case "bfile": {loc.returnValue = "cf_sql_blob"; break;}
 19				case "char": case "nchar": {loc.returnValue = "cf_sql_char"; break;}
 20				case "clob": case "nclob": {loc.returnValue = "cf_sql_clob"; break;}
 21				case "date": case "timestamp": {loc.returnValue = "cf_sql_timestamp"; break;}
 22				case "binary_double": {loc.returnValue = "cf_sql_double"; break;}
 23				case "number": case "float": case "binary_float":
 24				{
 25					// integer datatypes are represented by number(38,0)
 26					if (val(arguments.scale) == 0)
 27					{
 28						loc.returnValue = "cf_sql_integer";
 29					}
 30					else
 31					{
 32						loc.returnValue = "cf_sql_float";
 33					}
 34					break;
 35				}
 36				case "long": {loc.returnValue = "cf_sql_longvarchar"; break;}
 37				case "raw": {loc.returnValue = "cf_sql_varbinary"; break;}
 38				case "varchar2": case "nvarchar2": {loc.returnValue = "cf_sql_varchar"; break;}
 39			}
 40		</cfscript>
 41		<cfreturn loc.returnValue>
 42	</cffunction>
 43
 44	<cffunction name="$query" returntype="struct" access="public" output="false">
 45		<cfargument name="sql" type="array" required="true">
 46		<cfargument name="limit" type="numeric" required="false" default=0>
 47		<cfargument name="offset" type="numeric" required="false" default=0>
 48		<cfargument name="parameterize" type="boolean" required="true">
 49		<cfargument name="$primaryKey" type="string" required="false" default="">
 50		<cfscript>
 51			var loc = {};
 52
 53			arguments.sql = $removeColumnAliasesInOrderClause(arguments.sql);
 54			arguments.sql = $addColumnsToSelectAndGroupBy(arguments.sql);
 55			if (arguments.limit > 0)
 56			{
 57				loc.beforeWhere = "SELECT #arguments.$primaryKey# FROM (SELECT tmp.#arguments.$primaryKey#, rownum rnum FROM (";
 58				loc.afterWhere = ") tmp WHERE rownum <=" & arguments.limit+arguments.offset & ")" & " WHERE rnum >" & arguments.offset;
 59				ArrayPrepend(arguments.sql, loc.beforeWhere);
 60				ArrayAppend(arguments.sql, loc.afterWhere);
 61			}
 62
 63			// oracle doesn't support limit and offset in sql
 64			StructDelete(arguments, "limit", false);
 65			StructDelete(arguments, "offset", false);
 66			loc.returnValue = $performQuery(argumentCollection=arguments);
 67			loc.returnValue = $handleTimestampObject(loc.returnValue);
 68		</cfscript>
 69		<cfreturn loc.returnValue>
 70	</cffunction>
 71
 72	<cffunction name="$identitySelect" returntype="any" access="public" output="false">
 73		<cfargument name="queryAttributes" type="struct" required="true">
 74		<cfargument name="result" type="struct" required="true">
 75		<cfargument name="primaryKey" type="string" required="true">
 76		<cfset var loc = {}>
 77		<cfset var query = {}>
 78		<cfset loc.sql = Trim(arguments.result.sql)>
 79		<cfif Left(loc.sql, 11) IS "INSERT INTO">
 80			<cfset loc.startPar = Find("(", loc.sql) + 1>
 81			<cfset loc.endPar = Find(")", loc.sql)>
 82			<cfset loc.columnList = ReplaceList(Mid(loc.sql, loc.startPar, (loc.endPar-loc.startPar)), "#Chr(10)#,#Chr(13)#, ", ",,")>
 83			<cfif NOT ListFindNoCase(loc.columnList, ListFirst(arguments.primaryKey))>
 84				<cfset loc.returnValue = {}>
 85				<cfset loc.tbl = SpanExcluding(Right(loc.sql, Len(loc.sql)-12), " ")>
 86				<cfif !StructKeyExists(arguments.result, $generatedKey()) || application.wheels.serverName IS NOT "Adobe ColdFusion">
 87					<!---
 88					there isn't a way in oracle to tell what (if any) sequences exists
 89					on a table. hence we'll just have to perform a guess for now.
 90					TODO: in 1.2 we need to look at letting the developer specify the sequence
 91					name through a setting in the model
 92					--->
 93					<cftry>
 94						<cfquery attributeCollection="#arguments.queryAttributes#">SELECT #loc.tbl#_seq.currval AS lastId FROM dual</cfquery>
 95						<cfcatch type="any">
 96							<!--- in case the sequence doesn't exists return a blank string for the expected value --->
 97							<cfset query.name.lastId = "">
 98						</cfcatch>
 99					</cftry>
100				<cfelse>
101					<cfquery attributeCollection="#arguments.queryAttributes#">SELECT #arguments.primaryKey# AS lastId FROM #loc.tbl# WHERE ROWID = '#arguments.result[$generatedKey()]#'</cfquery>
102				</cfif>
103				<cfset loc.lastId = Trim(query.name.lastId)>
104				<cfif len(query.name.lastId)>
105					<cfset loc.returnValue[$generatedKey()] = Trim(loc.lastid)>
106					<cfreturn loc.returnValue>
107				</cfif>
108			<cfelse>
109				<!--- since Oracle always returns rowid we need to delete it in those cases where we have manually inserted the primary key, if we don't do this we'll end up setting the rowid value to the object --->
110				<cfif StructKeyExists(arguments.result, "rowid")>
111					<cfset StructDelete(arguments.result, "rowid")>
112				</cfif>
113				<cfif StructKeyExists(arguments.result, "generatedkey")>
114					<cfset StructDelete(arguments.result, "generatedkey")>
115				</cfif>
116			</cfif>
117		</cfif>
118	</cffunction>
119
120	<cffunction name="$getColumnInfo" returntype="query" access="public" output="false">
121		<cfargument name="table" type="string" required="true">
122		<cfargument name="datasource" type="string" required="true">
123		<cfargument name="username" type="string" required="true">
124		<cfargument name="password" type="string" required="true">
125		<cfscript>
126		var loc = {};
127		loc.args = duplicate(arguments);
128		StructDelete(loc.args, "table");
129		if (!Len(loc.args.username))
130		{
131			StructDelete(loc.args, "username");
132		}
133		if (!Len(loc.args.password))
134		{
135			StructDelete(loc.args, "password");
136		}
137		loc.args.name = "loc.returnValue";
138		</cfscript>
139		<cfquery attributeCollection="#loc.args#">
140		SELECT
141			TC.COLUMN_NAME
142			,TC.DATA_TYPE AS TYPE_NAME
143			,TC.NULLABLE AS IS_NULLABLE
144			,CASE WHEN PKC.COLUMN_NAME IS NULL THEN 0 ELSE 1 END AS IS_PRIMARYKEY
145			,0 AS IS_FOREIGNKEY
146			,'' AS REFERENCED_PRIMARYKEY
147			,'' AS REFERENCED_PRIMARYKEY_TABLE
148			,NVL(TC.DATA_PRECISION, TC.DATA_LENGTH) AS COLUMN_SIZE
149			,TC.DATA_SCALE AS DECIMAL_DIGITS
150			,TC.DATA_DEFAULT AS COLUMN_DEFAULT_VALUE
151			,TC.DATA_LENGTH AS CHAR_OCTET_LENGTH
152			,TC.COLUMN_ID AS ORDINAL_POSITION
153			,'' AS REMARKS
154		FROM
155			ALL_TAB_COLUMNS TC
156			LEFT JOIN ALL_CONSTRAINTS PK
157				ON (PK.CONSTRAINT_TYPE = 'P'
158				AND PK.TABLE_NAME = TC.TABLE_NAME
159				AND TC.OWNER = PK.OWNER)
160			LEFT JOIN ALL_CONS_COLUMNS PKC
161				ON (PK.CONSTRAINT_NAME = PKC.CONSTRAINT_NAME
162				AND TC.COLUMN_NAME = PKC.COLUMN_NAME
163				AND TC.OWNER = PKC.OWNER)
164		WHERE
165			TC.TABLE_NAME = '#UCase(arguments.table)#'
166		ORDER BY
167			TC.COLUMN_ID
168		</cfquery>
169		<!---
170		wheels catches the error and raises a Wheels.TableNotFound error
171		to mimic this we will throw an error if the query result is empty
172		 --->
173		<cfif !loc.returnValue.RecordCount>
174			<cfthrow/>
175		</cfif>
176		<cfreturn loc.returnValue>
177	</cffunction>
178
179	<cffunction name="$handleTimestampObject" hint="Oracle will return timestamp as an object. you need to call timestampValue() to get the string representation">
180		<cfargument name="results" type="struct" required="true">
181		<cfscript>
182		var loc = {};
183		// depending on the driver and engine used with oracle, timestamps can be returned as
184		// objects instead of strings.
185		if (StructKeyExists(arguments.results, "query"))
186		{
187			// look for all timestamp columns
188			loc.query = arguments.results.query;
189			loc.rows = loc.query.RecordCount;
190			if (loc.rows gt 0)
191			{
192				loc.metadata = GetMetaData(loc.query);
193				loc.columns = [];
194				loc.iEnd = ArrayLen(loc.metadata);
195				for (loc.i = 1; loc.i lte loc.iEnd; loc.i++)
196				{
197					loc.column = loc.metadata[loc.i];
198					if (loc.column.typename eq "timestamp")
199					{
200						ArrayAppend(loc.columns, loc.column.name);
201					}
202				}
203				// if we have any timestamp columns
204				if (!ArrayIsEmpty(loc.columns))
205				{
206					loc.iEnd = ArrayLen(loc.columns);
207					for (loc.i = 1; loc.i lte loc.iEnd; loc.i++)
208					{
209						loc.column = loc.columns[loc.i];
210						for (loc.row = 1; loc.row lte loc.rows; loc.row++)
211						{
212							if (IsObject(loc.query[loc.column][loc.row]))
213							{// call timestampValue() on objects to convert to string
214								loc.query[loc.column][loc.row] = loc.query[loc.column][loc.row].timestampValue();
215							}
216							else if (IsSimpleValue(loc.query[loc.column][loc.row]) && Len(loc.query[loc.column][loc.row]))
217							{// if the driver does the conversion automatically, there is no need to continue
218								break;
219							}
220						}
221					}
222				}
223				arguments.results.query = loc.query;
224			}
225		}
226		return arguments.results;
227		</cfscript>
228	</cffunction>
229
230	<cffunction name="$tableAliasForJoin" returntype="string" access="public" output="false">
231		<cfargument name="table" type="string" required="true">
232		<cfargument name="alias" type="string" required="true">
233		<cfreturn "#arguments.table# #arguments.alias#">
234	</cffunction>
235
236	<cfinclude template="../../plugins/injection.cfm">
237
238</cfcomponent>