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