1: <?php
  2: /**
  3:  * EXPERIMENTAL Oracle database adapter.
  4:  *
  5:  * @package DatabaseAdapters
  6:  * @author Riccardo Tacconi, Ago Luberg, enzo - Eduardo Garcia
  7:  
  8: */
  9: 
 10: class QOracleDatabase extends QDatabaseBase {
 11:     const Adapter = 'Oracle Database Adapter';
 12:     protected $objOracle;
 13: 
 14:     protected $EscapeIdentifierBegin = '';
 15:     protected $EscapeIdentifierEnd = '';
 16:     protected $blnOnlyFullGroupBy = true;
 17:     protected $debug;
 18:     protected $commitMode;
 19: 
 20:     public function SqlLimitVariablePrefix($strLimitInfo) {
 21:         // MySQL uses Limit by Suffixes (via a LIMIT clause)
 22:         // Prefix is not used, therefore, return null
 23:         return null;
 24:     }
 25: 
 26:     public function SqlLimitVariableSuffix($strLimitInfo) {
 27:         // Setup limit suffix (if applicable) via a LIMIT clause
 28:         // the parameter receives 10 or 60,10 from instance
 29:         // then from Mysql style must be converted to the Oracle way
 30:         if (strlen($strLimitInfo)) {
 31:             if (strpos($strLimitInfo, ';') !== false)
 32:             throw new Exception('Invalid Semicolon in LIMIT Info');
 33:             if (strpos($strLimitInfo, '`') !== false)
 34:             throw new Exception('Invalid Backtick in LIMIT Info');
 35: 
 36:             //if there is a ',' we change it to ORACLE ROWNUM
 37: 
 38:             if(strpos($strLimitInfo,',') !== false){
 39:                 // we have to add a lot of code to make a sql statement with
 40:                 //pagination FROM x to y
 41:                 // Oracle doesnt support LIMT, or OFFSET or TOP
 42: 
 43:                 $array_limit = explode(',',$strLimitInfo);
 44:                 //array_limit[0] is the MIN row to fetch
 45:                 //max_row is MAX row to fetch
 46: 
 47:                 $max_row = $array_limit[0] + $array_limit[1];
 48: 
 49:                 return "_LIMIT2_) a where ROWNUM <= $max_row ) where rnum  > $array_limit[0]";
 50:             }
 51:             else{
 52:                 //The string LIMIT will be parsed in Query Method
 53:                 return "_LIMIT1_) where rownum <= $strLimitInfo";
 54:             }
 55:         }
 56: 
 57:         return null;
 58:     }
 59: 
 60:     public function SqlSortByVariable($strSortByInfo) {
 61:         // Setup sorting information (if applicable) via a ORDER BY clause
 62:         if (strlen($strSortByInfo)) {
 63:             if (strpos($strSortByInfo, ';') !== false)
 64:             throw new Exception('Invalid Semicolon in ORDER BY Info');
 65:             if (strpos($strSortByInfo, '`') !== false)
 66:             throw new Exception('Invalid Backtick in ORDER BY Info');
 67: 
 68:             return "ORDER BY $strSortByInfo";
 69:         }
 70:             
 71:         return null;
 72:     }
 73: 
 74:     public function Connect() {
 75:         // Lookup Adapter-Specific Connection Properties
 76:         $strServer = $this->Server;
 77:         $strName = $this->Database;
 78:         $strUsername = $this->Username;
 79:         $strPassword = $this->Password;
 80:         $strPort = $this->Port;
 81:         //echo "DB vars: ".$this->__get('Username');
 82: 
 83:         // Connect to the Database Server
 84:         $this->objOracle = ociplogon($strUsername, $strPassword, "$strServer/$strName",'UTF8');
 85: 
 86:         if (!$this->objOracle)
 87:             throw new QOracleDatabaseException("Unable to connect to Database", -1, null);
 88: 
 89:         if ($objOracle_error=oci_error())
 90:             throw new QOracleDatabaseException($objOracle_error['message'],$objOracle_error['code'] , null);
 91: 
 92:         // Set to AutoCommit
 93:         //$this->NonQuery('SET AUTOCOMMIT=1;');
 94:         //Set SORT with special characteres
 95:         //$this->NonQuery("ALTER SESSION SET NLS_SORT=SPANISH");
 96:         // Update "Connected" Flag
 97:         $this->blnConnectedFlag = true;
 98:         
 99:         $this->NonQuery("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD hh24:mi:ss'");
100:         //$this->NonQuery("ALTER SESSION SET NLS_LANGUAGE='AMERICAN'");
101:         // to use . instead of , for floating point
102:         //$this->NonQuery("ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,'");
103:     }
104: 
105:     public function __get($strName) {
106:         switch ($strName) {
107:             case 'AffectedRows':
108:                 return $this->objOracle->affected_rows;
109:             case 'EscapeIdentifierBegin':
110:                 return $this->EscapeIdentifierBegin;
111:             case 'EscapeIdentifierEnd':
112:                 return $this->EscapeIdentifierEnd;
113:             default:
114:                 try {
115:                     return parent::__get($strName);
116:                 } catch (QCallerException $objExc) {
117:                     $objExc->IncrementOffset();
118:                     throw $objExc;
119:                 }
120:         }
121:     }
122: 
123:   /**
124:    * Prepares Oracle statement for execution
125:    *
126:    * @param string $strQuery
127:    * @return resource
128:    */
129:   public function Parse ($strQuery) {
130:     // Connect if Applicable
131:     if (!$this->blnConnectedFlag) $this->Connect();
132:     return oci_parse($this->objOracle,$strQuery);
133:   }
134: 
135: 
136:   /**
137:    * Executes prepared statement and returns usable object
138:    *
139:    * @param resource $objResult
140:    * @return QOracleDatabaseResult
141:    */
142:   protected function Execute ($objResult) {
143:     $blnReturn = false;
144:     if($objResult)
145:     {
146:       if($this->commitMode) {
147:         $blnReturn = oci_execute($objResult,OCI_DEFAULT);
148:       }
149:       else {
150:           $blnReturn = @oci_execute($objResult);
151:           if (!$blnReturn) {
152:             $objOracle_error=oci_error($objResult);
153:           }
154:       }
155:     }
156: 
157:     if ($objOracle_error=oci_error())
158:     {
159:       throw new QOracleDatabaseException($objOracle_error['message'],$objOracle_error['code'] , null);
160:     }
161: 
162:     return $blnReturn;
163:   }
164: 
165: 
166:     protected function ExecuteQuery($strQuery) {
167:         
168:         // Connect if Applicable
169:         if (!$this->blnConnectedFlag) $this->Connect();
170: 
171:         //looks for escaped characters like \' and replace them for two single
172:         //quotes. this is needed because, Oracle, diferently from other SGDB
173:         // do not understand \ do parse quotes and double quotes
174: 
175:         $strQuery = str_replace("\'","''",$strQuery);
176: 
177:         //remove backslash from escaped characters like \" and \\
178:         //Oracle has a problem only with single quotes escaped
179:         $strQuery = stripslashes($strQuery);
180: 
181:         //oracle does not accept the word AS to alias a table
182:         $strQuery = str_replace(' AS ',' ',$strQuery);
183: 
184:         //oracle does not accept formated SQL statement,
185:         // so strip off /n/t/r
186:         $strQuery=trim($strQuery);
187: 
188:         // called for displaying the pages except first page... see the else clause
189:         if(!strpos($strQuery,'_LIMIT2_')===false){
190:             //check if SqlLimitVariableSuffix was called
191:             // and if it as a "composed limit" ( from x to y)
192:             //if so, add the rest of the query, and it will look like this
193:             // http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
194: 
195:             $strQuery ="SELECT * FROM ( SELECT /*+ FIRST_ROWS(n) */ a.*, ROWNUM 
196:                             rnum from (". str_replace('_LIMIT2_','',$strQuery);
197: 
198:         }
199:         // called for displaying the first page
200:         else if(!strpos($strQuery,'_LIMIT1_')===false){
201:             //check if SqlLimitVariableSuffix was called
202:             // and if it as a "simple limit" (TOP N)
203:             //if so, add the rest of the query, and it will look like this
204:             // http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
205: 
206:             $strQuery ="SELECT * FROM(". str_replace('_LIMIT1_','',$strQuery);
207:         }
208:             
209:         // Perform the Query
210:         //print $strQuery; die();
211:         $objResult = oci_parse($this->objOracle,$strQuery);
212: 
213:         if($objResult)
214:         {
215:             if($this->commitMode) {
216:                 oci_execute($objResult,OCI_DEFAULT);
217:             }
218:             else {
219:                 //die($strQuery);
220:                 oci_execute($objResult);
221:             }
222:         }
223: 
224:         if ($objOracle_error=oci_error())
225:         {
226:             throw new QOracleDatabaseException($objOracle_error['message'],$objOracle_error['code'] , null);
227:         }
228: 
229:         // Return the Result
230: 
231:         $objOracleDatabaseResult = new QOracleDatabaseResult($objResult, $this);
232:         return $objOracleDatabaseResult;
233:     }
234: 
235: 
236: 
237:     protected function ExecuteNonQuery($strNonQuery) {
238: 
239:         // Connect if Applicable
240:         if (!$this->blnConnectedFlag) $this->Connect();
241:         
242:         //looks for escaped characters like \' and replace them for two single
243:         //quotes. this is needed because, Oracle, diferently from other SGDB
244:         // do not understand \ do parse quotes and double quotes
245: 
246:         $strNonQuery = str_replace("\'","''",$strNonQuery);
247: 
248:         //remove backslash from escaped characters like \" and \\
249:         //Oracle has a problem only with single quotes escaped
250:         $strNonQuery = stripslashes($strNonQuery);
251: 
252:         // Perform the Query
253:         $objResult = oci_parse($this->objOracle,$strNonQuery);
254: 
255:         if($objResult)
256:         {
257:             if($this->commitMode) {
258:                 oci_execute($objResult,OCI_DEFAULT);
259:             }
260:             else {
261:                 oci_execute($objResult);
262:             }
263:         }
264: 
265:         if ($objOracle_error=oci_error())
266:         {
267:             throw new QOracleDatabaseException($objOracle_error['message'],$objOracle_error['code'] , null);
268:         }
269:     }
270: 
271:     public function GetTables() {
272:         $objResult = $this->Query("select table_name from tabs order by table_name");
273: 
274:         $strToReturn = array();
275:         while ($strRowArray = $objResult->FetchRow())
276:         {
277:             array_push($strToReturn, $strRowArray[0]);
278:         }
279:         return $strToReturn;
280:     }
281: 
282:     public function GetFieldsForTable($strTableName) {
283:         $objResult = $this->Query(sprintf("select * from user_tab_columns where table_name = '%s'",strtoupper($strTableName)));
284: 
285:         $objFields = array();
286:         while ($objRow = $objResult->FetchRow())
287:         {
288:             array_push($objFields, new QOracleDatabaseField($objRow, $this));
289:         }
290:         return $objFields;
291:     }
292: 
293:     //it returns the last inserted row for the current session
294:     public function InsertId($strTableName = null, $strColumnName = null) {
295:         $seqName = substr($strTableName,0, 25) . "_SEQ";
296:         $objResult = $this->Query("select "."$seqName".".currval from dual");
297:         $strDbRow = $objResult->FetchRow();
298:         return QType::Cast($strDbRow[0], QType::Integer);       
299:     }
300: 
301:     public function Close() {
302:         oci_close($this->objOracle);
303: 
304:         // Update Connected Flag
305:         $this->blnConnectedFlag = false;
306:     }
307: 
308:     protected function ExecuteTransactionBegin() {
309:         // Set to AutoCommit
310:         //$this->NonQuery('SET AUTOCOMMIT OFF;');
311:         $this->commitMode = true;
312:     }
313: 
314:     protected function ExecuteTransactionCommit() {
315:         /*$this->NonQuery('COMMIT;');
316:         // Set to AutoCommit
317:         $this->NonQuery('SET AUTOCOMMIT OFF;');*/
318:         oci_commit($this->objOracle);
319:     }
320: 
321:     protected function ExecuteTransactionRollBack() {
322:         /*$this->NonQuery('ROLLBACK;');
323:         // Set to AutoCommit
324:         $this->NonQuery('SET AUTOCOMMIT OFF;');*/
325:         oci_rollback($this->objOracle);
326:     }
327: 
328:     public function GetIndexesForTable($strTableName) {
329:         $objIndexArray = array();
330:         $objResult = $this->Query(sprintf("select ui.index_name,ui.index_type,ui.uniqueness,uc.constraint_type FROM user_indexes ui left join user_constraints uc on (ui.index_name=uc.index_name) WHERE ui.table_name= '%s'",strtoupper($strTableName)));
331:         while ($objRow = $objResult->FetchRow())
332:         {
333:             $ColumnNameArray = array();
334:             $objResult2 = $this->Query(sprintf("select * from user_ind_columns where index_name='%s' order by  column_position",$objRow[0]));
335:             while ($objRow2 = $objResult2->FetchRow())
336:             {
337:                 array_push($ColumnNameArray, $objRow2[2]);
338:             }
339:             $blnUnique = strcmp($objRow[2],"UNIQUE")?false:true;
340:             $blnPrimaryKey = strcmp($objRow[3],"P")?false:true;
341:             $objIndex = new QDatabaseIndex($objRow[0], $blnPrimaryKey, $blnUnique, $ColumnNameArray);
342:             array_push($objIndexArray, $objIndex);
343:         }
344:         //SHOW The Obj Index
345:         // echo "<pre>";
346:         // print_r($objIndexArray);
347:         // echo "</pre>";
348:         //*/
349:             
350:         return $objIndexArray;
351:     }
352: 
353:     public function GetForeignKeysForTable($strTableName) {
354: 
355:         $objForeignKeysArray = array();
356:         $objResult = $this->Query(sprintf("select uc1.constraint_name,uc2.table_name,uc2.constraint_name from user_constraints uc1,user_constraints uc2 where uc1.r_constraint_name=uc2.constraint_name and uc1.constraint_type='R' and uc1.table_name='%s'",strtoupper($strTableName)));
357:         while ($objRow = $objResult->FetchRow())
358:         {
359:             $ColumnNameArray = array();
360:             $objResult2 = $this->Query(sprintf("select * from user_cons_columns where constraint_name = '%s' order by  position",$objRow[0]));
361:             while ($objRow2 = $objResult2->FetchRow())
362:             {
363:                 array_push($ColumnNameArray, $objRow2[3]);
364:             }
365: 
366:             $ColumnNameArray2 = array();
367:             $objResult3 = $this->Query(sprintf("select * from user_cons_columns where constraint_name = '%s' order by  position",strtoupper($objRow[2])));
368:             while ($objRow3 = $objResult3->FetchRow())
369:             {
370:                 array_push($ColumnNameArray2, $objRow3[3]);
371:             }
372: 
373:             $objIndex = new QDatabaseForeignKey($objRow[0], $ColumnNameArray,$objRow[1],$ColumnNameArray2);
374:             array_push($objForeignKeysArray, $objIndex);
375:         }
376:             
377:   /*    SHOW The Obj ForeingKey
378:    echo "<pre>";
379:    print_r($objForeignKeysArray);
380:    echo "</pre>";
381:    */
382:             
383:             
384: 
385:   return $objForeignKeysArray;
386:     }
387: 
388:     // MySql defines KeyDefinition to be [OPTIONAL_NAME] ([COL], ...)
389:     // If the key name exists, this will parse it out and return it
390:     private function ParseNameFromKeyDefinition($strKeyDefinition) {
391:         $strKeyDefinition = trim($strKeyDefinition);
392: 
393:         $intPosition = strpos($strKeyDefinition, '(');
394: 
395:         if ($intPosition === false)
396:         throw new Exception("Invalid Key Definition: $strKeyDefinition");
397:         else if ($intPosition == 0)
398:         // No Key Name Defined
399:         return null;
400: 
401:         // If we're here, then we have a key name defined
402:         $strName = trim(substr($strKeyDefinition, 0, $intPosition));
403: 
404:         // Rip Out leading and trailing "`" character (if applicable)
405:         if (substr($strName, 0, 1) == '`')
406:         return substr($strName, 1, strlen($strName) - 2);
407:         else
408:         return $strName;
409:     }
410: 
411:     // MySql defines KeyDefinition to be [OPTIONAL_NAME] ([COL], ...)
412:     // This will return an array of strings that are the names [COL], etc.
413:     private function ParseColumnNameArrayFromKeyDefinition($strKeyDefinition) {
414:         $strKeyDefinition = trim($strKeyDefinition);
415: 
416:         // Get rid of the opening "(" and the closing ")"
417:         $intPosition = strpos($strKeyDefinition, '(');
418:         if ($intPosition === false)
419:         throw new Exception("Invalid Key Definition: $strKeyDefinition");
420:         $strKeyDefinition = trim(substr($strKeyDefinition, $intPosition + 1));
421: 
422:         $intPosition = strpos($strKeyDefinition, ')');
423:         if ($intPosition === false)
424:         throw new Exception("Invalid Key Definition: $strKeyDefinition");
425:         $strKeyDefinition = trim(substr($strKeyDefinition, 0, $intPosition));
426: 
427:         // Create the Array
428:         // TODO: Current method doesn't support key names with commas or parenthesis in them!
429:         $strToReturn = explode(',', $strKeyDefinition);
430: 
431:         // Take out trailing and leading "`" character in each name (if applicable)
432:         for ($intIndex = 0; $intIndex < count($strToReturn); $intIndex++) {
433:             $strColumn = $strToReturn[$intIndex];
434: 
435:             if (substr($strColumn, 0, 1) == '`')
436:             $strColumn = substr($strColumn, 1, strpos($strColumn, '`', 1) - 1);
437: 
438:             $strToReturn[$intIndex] = $strColumn;
439:         }
440: 
441:         return $strToReturn;
442:     }
443: 
444:     private function ParseForIndexes($strCreateStatement) {
445:         // MySql nicely splits each object in a table into it's own line
446:         // Split the create statement into lines, and then pull out anything
447:         // that says "PRIMARY KEY", "UNIQUE KEY", or just plain ol' "KEY"
448:         $strLineArray = explode("\n", $strCreateStatement);
449:         $objIndexArray = array();
450:         // We don't care about the first line or the last line
451:         for ($intIndex = 1; $intIndex < (count($strLineArray) - 1); $intIndex++) {
452:             $strLine = $strLineArray[$intIndex];
453: 
454:             // Each object has a two-space indent
455:             // So this is a key object if any of those key-related words exist at position 2
456:             switch (2) {
457:                 case (strpos($strLine, 'PRIMARY KEY')):
458:                     $strKeyDefinition = substr($strLine, strlen('  PRIMARY KEY '));
459: 
460:                     $strKeyName = $this->ParseNameFromKeyDefinition($strKeyDefinition);
461:                     $strColumnNameArray = $this->ParseColumnNameArrayFromKeyDefinition($strKeyDefinition);
462: 
463:                     $objIndex = new QDatabaseIndex($strKeyName, $blnPrimaryKey = true, $blnUnique = true, $strColumnNameArray);
464:                     array_push($objIndexArray, $objIndex);
465:                     break;
466: 
467:                 case (strpos($strLine, 'UNIQUE KEY')):
468:                     $strKeyDefinition = substr($strLine, strlen('  UNIQUE KEY '));
469: 
470:                     $strKeyName = $this->ParseNameFromKeyDefinition($strKeyDefinition);
471:                     $strColumnNameArray = $this->ParseColumnNameArrayFromKeyDefinition($strKeyDefinition);
472: 
473:                     $objIndex = new QDatabaseIndex($strKeyName, $blnPrimaryKey = false, $blnUnique = true, $strColumnNameArray);
474:                     array_push($objIndexArray, $objIndex);
475:                     break;
476: 
477:                 case (strpos($strLine, 'KEY')):
478:                     $strKeyDefinition = substr($strLine, strlen('  KEY '));
479: 
480:                     $strKeyName = $this->ParseNameFromKeyDefinition($strKeyDefinition);
481:                     $strColumnNameArray = $this->ParseColumnNameArrayFromKeyDefinition($strKeyDefinition);
482: 
483:                     $objIndex = new QDatabaseIndex($strKeyName, $blnPrimaryKey = false, $blnUnique = false, $strColumnNameArray);
484:                     array_push($objIndexArray, $objIndex);
485:                     break;
486:             }
487:         }
488: 
489:         return $objIndexArray;
490:     }
491: 
492:     private function ParseForInnoDbForeignKeys($strCreateStatement) {
493:         // MySql nicely splits each object in a table into it's own line
494:         // Split the create statement into lines, and then pull out anything
495:         // that starts with "CONSTRAINT" and contains "FOREIGN KEY"
496:         $strLineArray = explode("\n", $strCreateStatement);
497: 
498:         $objForeignKeyArray = array();
499: 
500:         // We don't care about the first line or the last line
501:         for ($intIndex = 1; $intIndex < (count($strLineArray) - 1); $intIndex++) {
502:             $strLine = $strLineArray[$intIndex];
503: 
504:             // Check to see if the line:
505:             // * Starts with "CONSTRAINT" at position 2 AND
506:             // * contains "FOREIGN KEY"
507:             if ((strpos($strLine, "CONSTRAINT") == 2) &&
508:             (strpos($strLine, "FOREIGN KEY") !== false)) {
509:                 $strLine = substr($strLine, strlen('  CONSTRAINT '));
510:                     
511:                 // By the end of the following lines, we will end up with a strTokenArray
512:                 // Index 0: the FK name
513:                 // Index 1: the list of columns that are the foreign key
514:                 // Index 2: the table which this FK references
515:                 // Index 3: the list of columns which this FK references
516:                 $strTokenArray = split(' FOREIGN KEY ', $strLine);
517:                 $strTokenArray[1] = split(' REFERENCES ', $strTokenArray[1]);
518:                 $strTokenArray[2] = $strTokenArray[1][1];
519:                 $strTokenArray[1] = $strTokenArray[1][0];
520:                 $strTokenArray[2] = split(' ', $strTokenArray[2]);
521:                 $strTokenArray[3] = $strTokenArray[2][1];
522:                 $strTokenArray[2] = $strTokenArray[2][0];
523:                     
524:                 // Cleanup, and change Index 1 and Index 3 to be an array based on the
525:                 // parsed column name list
526:                 if (substr($strTokenArray[0], 0, 1) == '`')
527:                 $strTokenArray[0] = substr($strTokenArray[0], 1, strlen($strTokenArray[0]) - 2);
528:                 $strTokenArray[1] = $this->ParseColumnNameArrayFromKeyDefinition($strTokenArray[1]);
529:                 if (substr($strTokenArray[2], 0, 1) == '`')
530:                 $strTokenArray[2] = substr($strTokenArray[2], 1, strlen($strTokenArray[2]) - 2);
531:                 $strTokenArray[3] = $this->ParseColumnNameArrayFromKeyDefinition($strTokenArray[3]);
532:                     
533:                 // Create the FK object and add it to the return array
534:                 $objForeignKey = new QDatabaseForeignKey($strTokenArray[0], $strTokenArray[1], $strTokenArray[2], $strTokenArray[3]);
535:                 array_push($objForeignKeyArray, $objForeignKey);
536:                     
537:                 // Ensure the FK object has matching column numbers (or else, throw)
538:                 if ((count($objForeignKey->ColumnNameArray) == 0) ||
539:                 (count($objForeignKey->ColumnNameArray) != count($objForeignKey->ReferenceColumnNameArray)))
540:                 throw new Exception("Invalid Foreign Key definition: $strLine");
541:             }
542:         }
543:         return $objForeignKeyArray;
544:     }
545: 
546:     private function GetCreateStatementForTable($strTableName) {
547:         // Use the MySQL "SHOW CREATE TABLE" functionality to get the table's Create statement
548:         $objResult = $this->Query(sprintf('SHOW CREATE TABLE `%s`', $strTableName));
549:         $objRow = $objResult->FetchRow();
550:         $strCreateTable = $objRow[1];
551:         $strCreateTable = str_replace("\r", "", $strCreateTable);
552:         return $strCreateTable;
553:     }
554: 
555:     private function GetTableTypeForCreateStatement($strCreateStatement) {
556:         // Table Type is in the last line of the Create Statement, "TYPE=DbTableType"
557:         $strLineArray = explode("\n", $strCreateStatement);
558:         $strFinalLine = strtoupper($strLineArray[count($strLineArray) - 1]);
559: 
560:         if (substr($strFinalLine, 0, 7) == ') TYPE=') {
561:             return trim(substr($strFinalLine, 7));
562:         } else if (substr($strFinalLine, 0, 9) == ') ENGINE=') {
563:             return trim(substr($strFinalLine, 9));
564:         } else
565:         throw new Exception("Invalid Table Description");
566:     }
567: 
568:     public function ExplainStatement($sql) {
569:         return $this->Query("EXPLAIN PLAN FOR " . $sql);
570:     }
571: }
572: 
573: class QOracleDatabaseException extends QDatabaseExceptionBase {
574:     public function __construct($strMessage, $intNumber, $strQuery) {
575:         parent::__construct(sprintf("Oracle Error: %s", $strMessage), 2);
576:         $this->intErrorNumber = $intNumber;
577:         $this->strQuery = $strQuery;
578:     }
579: }
580: 
581: /**
582:  * Class to handle results sent by database upon querying
583:  */
584: class QOracleDatabaseResult extends QDatabaseResultBase {
585:     protected $objOracleResult;
586:     protected $objDb;
587: 
588:     public function __construct($objResult, QOracleDatabase $objDb) {
589:         $this->objOracleResult = $objResult;
590:         $this->objDb = $objDb;
591:     }
592: 
593:     public function FetchArray() {
594:         return oci_fetch_array($this->objOracleResult);
595:     }
596: 
597:     public function FetchObject() {
598:         return oci_fetch_object($this->objOracleResult);
599:     }
600: 
601:     public function FetchFields() {
602:         return null; // not implemented
603:     }
604: 
605:     public function FetchField() {
606:         return null; // not implemented
607:     }
608: 
609:     public function FetchRow() {
610:         return oci_fetch_row($this->objOracleResult);
611:     }
612: 
613:     public function CountRows() {
614:         $nr_rows =sizeof(oci_fetch_array($this->objOracleResult,OCI_NUM));
615:         if($nr_rows >1)
616:         return ($nr_rows/2);//divide by 2 because it makes OCI_BOTH by default, so ir repeats the results twice
617:         else
618:         return 0;
619:     }
620: 
621:     public function CountFields() {
622:         return oci_num_fields($this->objOracleResult);
623:     }
624: 
625:     public function Close() {
626:         $this->objOracleResult->free();
627:     }
628: 
629:     public function GetNextRow() {
630:         $strColumnArray = $this->FetchArray();
631: 
632:         if ($strColumnArray)
633:         return new QOracleDatabaseRow($strColumnArray);
634:         else
635:         return null;
636:     }
637: 
638:     public function GetRows() {
639:         $objDbRowArray = array();
640:         while ($objDbRow = $this->GetNextRow())
641:         array_push($objDbRowArray, $objDbRow);
642:         return $objDbRowArray;
643:     }
644: }
645: 
646: class QOracleDatabaseRow extends QDatabaseRowBase {
647:     protected $strColumnArray;
648: 
649:     public function __construct($strColumnArray) {
650:         $this->strColumnArray = $strColumnArray;
651:     }
652: 
653:     /**
654:      * Gets the value of a column from a result row returned by the database
655:      *
656:      * @param string                  $strColumnName Name of te column
657:      * @param null|QDatabaseFieldType $strColumnType Data type
658:      *
659:      * @return mixed
660:      */
661:     public function GetColumn($strColumnName, $strColumnType = null) {
662:         if (!isset($this->strColumnArray[$strColumnName])) {
663:             return null;
664:         }
665:         $strColumnValue = $this->strColumnArray[$strColumnName];
666: 
667:         switch ($strColumnType) {
668:             case QDatabaseFieldType::Bit:
669:                 // Account for single bit value
670:                 $chrBit = $strColumnValue;
671:                 if ((strlen($chrBit) == 1) && (ord($chrBit) == 0))
672:                     return false;
673: 
674:                 // Otherwise, use PHP conditional to determine true or false
675:                 return ($strColumnValue) ? true : false;
676: 
677:             case QDatabaseFieldType::Blob:
678:                 return QType::Cast($strColumnValue->load(), QType::String);
679:             case QDatabaseFieldType::Char:
680:             case QDatabaseFieldType::VarChar:
681:                 return QType::Cast($strColumnValue, QType::String);
682: 
683:             case QDatabaseFieldType::Date:
684:             case QDatabaseFieldType::DateTime:
685:             case QDatabaseFieldType::Time:
686:                 return new QDateTime($strColumnValue);
687: 
688:             case QDatabaseFieldType::Float:
689:                 return QType::Cast($strColumnValue, QType::Float);
690: 
691:             case QDatabaseFieldType::Integer:
692:                 return QType::Cast($strColumnValue, QType::Integer);
693: 
694:             default:
695:                 return $strColumnValue;
696:         }
697:     }
698: 
699:     /**
700:      * Tells whether a particular column exists in a returned database row
701:      *
702:      * @param string $strColumnName Name of te column
703:      *
704:      * @return bool
705:      */
706:     public function ColumnExists($strColumnName) {
707:         return array_key_exists($strColumnName, $this->strColumnArray);
708:     }
709:     
710:     public function GetColumnNameArray() {
711:         return $this->strColumnArray;
712:     }
713: }
714: 
715: class QOracleDatabaseField extends QDatabaseFieldBase {
716:     public function __construct($mixFieldData, $objDb = null) {
717:             
718:             
719:         $this->strTable =  $mixFieldData[0];
720:         $this->strName = $mixFieldData[1];
721:         $this->strType =$mixFieldData[2];
722:         $this->intMaxLength = $mixFieldData[5];
723:         $this->blnNotNull = ($mixFieldData[8]=="N")?true:false;
724:         $this->strDefault = $mixFieldData[11];
725:         $this->strOriginalTable = $this->strTable;
726:         $this->strOriginalName = $this->strName;
727:         $this->SetFieldType($mixFieldData[2]);
728: 
729: 
730:         /**
731:          *
732:             Implementation just like PostGreSQL Adapter
733:          */
734: 
735:         $objResult=$objDb->Query(sprintf("select position, constraint_type as type from
736:             user_cons_columns ucc,user_constraints uc where ucc.column_name='%s' 
737:             AND ucc.TABLE_NAME='%s' and ucc.TABLE_NAME=uc.TABLE_NAME and 
738:             uc.CONSTRAINT_NAME=ucc.CONSTRAINT_NAME and constraint_type IN ('P', 'U') order by position",$this->strOriginalName,$this->strTable));
739:         // TODO: unique and primary?
740:         // TODO: auto number (sequence)
741:         // TODO: not null?
742:         while ($mixRow = $objResult->FetchArray()) {
743:           if (isset($mixRow['TYPE'])) {
744:             if ($mixRow['TYPE'] == 'P') {
745:               $this->blnPrimaryKey = true;
746:               // auto-number
747:               $this->blnIdentity = true;
748:               break;
749:             }
750:             if ($mixRow['TYPE'] == 'U') {
751:               $this->blnUnique = true;
752:               $this->blnIdentity = false;
753:               $this->blnPrimaryKey = false;
754:             }
755:           }     
756:         }
757:     }
758: 
759:     protected function SetFieldType($OracleFieldType) {
760: 
761:         //Note: Info from http://download-east.oracle.com/docs/html/B10255_01/ch3.htm#1026123
762:         switch ($OracleFieldType) {
763:             case "FLOAT":
764:                 $this->strType = QDatabaseFieldType::Float;
765:                 break;              
766:             case "NUMBER":
767:                 $this->strType = QDatabaseFieldType::Float;
768:                 break;
769:             case "CHAR":
770:                 if ($this->intMaxLength == 1)
771:                     $this->strType = QDatabaseFieldType::Bit;
772:                 else
773:                     $this->strType = QDatabaseFieldType::Char;
774:                 break;
775:             case "VARCHAR":
776:             case "VARCHAR2":
777:             case "NVARCHAR2":
778:             case "NCHAR":
779:                 $this->strType = QDatabaseFieldType::VarChar;
780:                 break;
781:             case "DATE":
782:                 $this->strType = QDatabaseFieldType::DateTime;
783:                 break;
784:             case "BLOB":
785:             case "CLOB":
786:             case "LONG":
787:             case "NCLOB":
788:             case "RAW":
789:             case "LONG RAW":
790:                 $this->strType = QDatabaseFieldType::Blob;
791:                 break;
792:             default:
793:                 throw new Exception("Unable to determine Oracle Database Field Type: $OracleFieldType");
794:                 break;
795:         }
796:     }
797: }