1: <?php
  2: 
  3: namespace Yajra\Oci8;
  4: 
  5: use Doctrine\DBAL\Connection as DoctrineConnection;
  6: use Doctrine\DBAL\Driver\OCI8\Driver as DoctrineDriver;
  7: use Illuminate\Database\Connection;
  8: use Illuminate\Database\Grammar;
  9: use PDO;
 10: use Yajra\Oci8\Query\Grammars\OracleGrammar as QueryGrammar;
 11: use Yajra\Oci8\Query\OracleBuilder as QueryBuilder;
 12: use Yajra\Oci8\Query\Processors\OracleProcessor as Processor;
 13: use Yajra\Oci8\Schema\Grammars\OracleGrammar as SchemaGrammar;
 14: use Yajra\Oci8\Schema\OracleBuilder as SchemaBuilder;
 15: use Yajra\Oci8\Schema\Sequence;
 16: use Yajra\Oci8\Schema\Trigger;
 17: use Yajra\Pdo\Oci8\Statement;
 18: 
 19: class Oci8Connection extends Connection
 20: {
 21:     /**
 22:      * @var string
 23:      */
 24:     protected $schema;
 25: 
 26:     /**
 27:      * @var \Yajra\Oci8\Schema\Sequence
 28:      */
 29:     protected $sequence;
 30: 
 31:     /**
 32:      * @var \Yajra\Oci8\Schema\Trigger
 33:      */
 34:     protected $trigger;
 35: 
 36:     /**
 37:      * @param PDO|\Closure $pdo
 38:      * @param string $database
 39:      * @param string $tablePrefix
 40:      * @param array $config
 41:      */
 42:     public function __construct($pdo, $database = '', $tablePrefix = '', array $config = [])
 43:     {
 44:         parent::__construct($pdo, $database, $tablePrefix, $config);
 45:         $this->sequence = new Sequence($this);
 46:         $this->trigger  = new Trigger($this);
 47:     }
 48: 
 49:     /**
 50:      * Get current schema.
 51:      *
 52:      * @return string
 53:      */
 54:     public function getSchema()
 55:     {
 56:         return $this->schema;
 57:     }
 58: 
 59:     /**
 60:      * Set current schema.
 61:      *
 62:      * @param string $schema
 63:      * @return $this
 64:      */
 65:     public function setSchema($schema)
 66:     {
 67:         $this->schema = $schema;
 68:         $sessionVars  = [
 69:             'CURRENT_SCHEMA' => $schema,
 70:         ];
 71: 
 72:         return $this->setSessionVars($sessionVars);
 73:     }
 74: 
 75:     /**
 76:      * Update oracle session variables.
 77:      *
 78:      * @param array $sessionVars
 79:      * @return $this
 80:      */
 81:     public function setSessionVars(array $sessionVars)
 82:     {
 83:         $vars = [];
 84:         foreach ($sessionVars as $option => $value) {
 85:             if (strtoupper($option) == 'CURRENT_SCHEMA') {
 86:                 $vars[] = "$option  = $value";
 87:             } else {
 88:                 $vars[] = "$option  = '$value'";
 89:             }
 90:         }
 91:         if ($vars) {
 92:             $sql = 'ALTER SESSION SET ' . implode(' ', $vars);
 93:             $this->statement($sql);
 94:         }
 95: 
 96:         return $this;
 97:     }
 98: 
 99:     /**
100:      * Get sequence class.
101:      *
102:      * @return \Yajra\Oci8\Schema\Sequence
103:      */
104:     public function getSequence()
105:     {
106:         return $this->sequence;
107:     }
108: 
109:     /**
110:      * Set sequence class.
111:      *
112:      * @param \Yajra\Oci8\Schema\Sequence $sequence
113:      * @return \Yajra\Oci8\Schema\Sequence
114:      */
115:     public function setSequence(Sequence $sequence)
116:     {
117:         return $this->sequence = $sequence;
118:     }
119: 
120:     /**
121:      * Get oracle trigger class.
122:      *
123:      * @return \Yajra\Oci8\Schema\Trigger
124:      */
125:     public function getTrigger()
126:     {
127:         return $this->trigger;
128:     }
129: 
130:     /**
131:      * Set oracle trigger class.
132:      *
133:      * @param \Yajra\Oci8\Schema\Trigger $trigger
134:      * @return \Yajra\Oci8\Schema\Trigger
135:      */
136:     public function setTrigger(Trigger $trigger)
137:     {
138:         return $this->trigger = $trigger;
139:     }
140: 
141:     /**
142:      * Get a schema builder instance for the connection.
143:      *
144:      * @return \Yajra\Oci8\Schema\OracleBuilder
145:      */
146:     public function getSchemaBuilder()
147:     {
148:         if (is_null($this->schemaGrammar)) {
149:             $this->useDefaultSchemaGrammar();
150:         }
151: 
152:         return new SchemaBuilder($this);
153:     }
154: 
155:     /**
156:      * Begin a fluent query against a database table.
157:      *
158:      * @param string $table
159:      * @return \Yajra\Oci8\Query\OracleBuilder
160:      */
161:     public function table($table)
162:     {
163:         $processor = $this->getPostProcessor();
164: 
165:         $query = new QueryBuilder($this, $this->getQueryGrammar(), $processor);
166: 
167:         return $query->from($table);
168:     }
169: 
170:     /**
171:      * Set oracle session date format.
172:      *
173:      * @param string $format
174:      * @return $this
175:      */
176:     public function setDateFormat($format = 'YYYY-MM-DD HH24:MI:SS')
177:     {
178:         $sessionVars = [
179:             'NLS_DATE_FORMAT'      => $format,
180:             'NLS_TIMESTAMP_FORMAT' => $format,
181:         ];
182: 
183:         return $this->setSessionVars($sessionVars);
184:     }
185: 
186:     /**
187:      * Get doctrine connection.
188:      *
189:      * @return \Doctrine\DBAL\Connection
190:      */
191:     public function getDoctrineConnection()
192:     {
193:         $driver = $this->getDoctrineDriver();
194: 
195:         $data = ['pdo' => $this->getPdo(), 'user' => $this->getConfig('username')];
196: 
197:         return new DoctrineConnection($data, $driver);
198:     }
199: 
200:     /**
201:      * Get doctrine driver.
202:      *
203:      * @return \Doctrine\DBAL\Driver\OCI8\Driver
204:      */
205:     protected function getDoctrineDriver()
206:     {
207:         return new DoctrineDriver();
208:     }
209: 
210:     /**
211:      * Execute a PL/SQL Function and return its value.
212:      * Usage: DB::executeFunction('function_name(:binding_1,:binding_n)', [':binding_1' => 'hi', ':binding_n' =>
213:      * 'bye'], PDO::PARAM_LOB).
214:      *
215:      * @author Tylerian - jairo.eog@outlook.com
216:      * @param string $sql (mixed)
217:      * @param array $bindings (kvp array)
218:      * @param int $returnType (PDO::PARAM_*)
219:      * @return mixed $returnType
220:      */
221:     public function executeFunction($sql, array $bindings = [], $returnType = PDO::PARAM_STR)
222:     {
223:         $query = $this->getPdo()->prepare('begin :result := ' . $sql . '; end;');
224: 
225:         foreach ($bindings as $key => &$value) {
226:             if (! preg_match('/^:(.*)$/i', $key)) {
227:                 $key = ':' . $key;
228:             }
229: 
230:             $query->bindParam($key, $value);
231:         }
232: 
233:         $query->bindParam(':result', $result, $returnType);
234:         $query->execute();
235: 
236:         return $result;
237:     }
238: 
239:     /**
240:      * Execute a PL/SQL Procedure and return its result.
241:      * Usage: DB::executeProcedure($procedureName, $bindings).
242:      * $bindings looks like:
243:      *         $bindings = [
244:      *                  'p_userid'  => $id
245:      *         ];
246:      *
247:      * @param string $procedureName
248:      * @param array $bindings
249:      * @param mixed $returnType
250:      * @return array
251:      */
252:     public function executeProcedure($procedureName, $bindings, $returnType = PDO::PARAM_STMT)
253:     {
254:         $command = sprintf('begin %s(:%s, :cursor); end;', $procedureName, implode(', :', array_keys($bindings)));
255: 
256:         $stmt = $this->getPdo()->prepare($command);
257: 
258:         foreach ($bindings as $bindingName => &$bindingValue) {
259:             $stmt->bindParam(':' . $bindingName, $bindingValue);
260:         }
261: 
262:         $cursor = null;
263: 
264:         $stmt->bindParam(':cursor', $cursor, $returnType);
265:         $stmt->execute();
266: 
267:         if ($returnType === PDO::PARAM_STMT) {
268:             $statement = new Statement($cursor, $this->getPdo(), $this->getPdo()->getOptions());
269:             $statement->execute();
270:             $results = $statement->fetchAll(PDO::FETCH_ASSOC);
271:             $statement->closeCursor();
272: 
273:             return $results;
274:         }
275: 
276:         return $cursor;
277:     }
278: 
279:     /**
280:      * Bind values to their parameters in the given statement.
281:      *
282:      * @param \PDOStatement $statement
283:      * @param array $bindings
284:      */
285:     public function bindValues($statement, $bindings)
286:     {
287:         foreach ($bindings as $key => $value) {
288:             $statement->bindParam($key, $bindings[$key]);
289:         }
290:     }
291: 
292:     /**
293:      * Get the default query grammar instance.
294:      *
295:      * @return \Illuminate\Database\Grammar|\Yajra\Oci8\Query\Grammars\OracleGrammar
296:      */
297:     protected function getDefaultQueryGrammar()
298:     {
299:         return $this->withTablePrefix(new QueryGrammar());
300:     }
301: 
302:     /**
303:      * Set the table prefix and return the grammar.
304:      *
305:      * @param \Illuminate\Database\Grammar|\Yajra\Oci8\Query\Grammars\OracleGrammar|\Yajra\Oci8\Schema\Grammars\OracleGrammar $grammar
306:      * @return \Illuminate\Database\Grammar
307:      */
308:     public function withTablePrefix(Grammar $grammar)
309:     {
310:         return $this->withSchemaPrefix(parent::withTablePrefix($grammar));
311:     }
312: 
313:     /**
314:      * Set the schema prefix and return the grammar.
315:      *
316:      * @param \Illuminate\Database\Grammar|\Yajra\Oci8\Query\Grammars\OracleGrammar|\Yajra\Oci8\Schema\Grammars\OracleGrammar $grammar
317:      * @return \Illuminate\Database\Grammar
318:      */
319:     public function withSchemaPrefix(Grammar $grammar)
320:     {
321:         $grammar->setSchemaPrefix($this->getConfigSchemaPrefix());
322: 
323:         return $grammar;
324:     }
325: 
326:     /**
327:      * Get config schema prefix.
328:      *
329:      * @return string
330:      */
331:     protected function getConfigSchemaPrefix()
332:     {
333:         return isset($this->config['prefix_schema']) ? $this->config['prefix_schema'] : '';
334:     }
335: 
336:     /**
337:      * Get the default schema grammar instance.
338:      *
339:      * @return \Illuminate\Database\Grammar|\Yajra\Oci8\Schema\Grammars\OracleGrammar
340:      */
341:     protected function getDefaultSchemaGrammar()
342:     {
343:         return $this->withTablePrefix(new SchemaGrammar());
344:     }
345: 
346:     /**
347:      * Get the default post processor instance.
348:      *
349:      * @return \Yajra\Oci8\Query\Processors\OracleProcessor
350:      */
351:     protected function getDefaultPostProcessor()
352:     {
353:         return new Processor();
354:     }
355: }
356: