1<?php
2/* Copyright (c) 1998-2009 ILIAS open source, Extended GPL, see docs/LICENSE */
3require_once('./Services/Database/classes/PDO/Manager/class.ilDBPdoManagerPostgres.php');
4require_once('class.ilDBPdo.php');
5require_once('./Services/Database/classes/PDO/FieldDefinition/class.ilDBPdoPostgresFieldDefinition.php');
6require_once('./Services/Database/classes/PDO/Reverse/class.ilDBPdoReversePostgres.php');
7
8/**
9 * Class ilDBPdoPostgreSQL
10 *
11 * @author Fabian Schmid <fs@studer-raimann.ch>
12 */
13class ilDBPdoPostgreSQL extends ilDBPdo implements ilDBInterface
14{
15    const POSTGRE_STD_PORT = 5432;
16    /**
17     * @var int
18     */
19    protected $port = self::POSTGRE_STD_PORT;
20    /**
21     * @var string
22     */
23    protected $storage_engine = null;
24    /**
25     * @var ilDBPdoManagerPostgres
26     */
27    protected $manager;
28
29
30    public function generateDSN()
31    {
32        if (!$this->getPort()) {
33            $this->setPort(self::POSTGRE_STD_PORT);
34        }
35        $this->dsn = 'pgsql:host=' . $this->getHost() . ';port=' . $this->getPort() . ';dbname=' . $this->getDbname() . ';user='
36                     . $this->getUsername() . ';password=' . $this->getPassword() . '';
37    }
38
39
40    /**
41     * @param bool $return_false_for_error
42     * @return bool
43     * @throws \Exception
44     */
45    public function connect($return_false_for_error = false)
46    {
47        $this->generateDSN();
48        try {
49            $this->pdo = new PDO($this->getDSN(), $this->getUsername(), $this->getPassword(), $this->getAttributes());
50            $this->initHelpers();
51        } catch (Exception $e) {
52            $this->error_code = $e->getCode();
53            if ($return_false_for_error) {
54                return false;
55            }
56            throw $e;
57        }
58
59        return ($this->pdo->errorCode() == PDO::ERR_NONE);
60    }
61
62
63    protected function getAdditionalAttributes()
64    {
65        return array(
66            PDO::ATTR_EMULATE_PREPARES => true,
67            PDO::ATTR_STRINGIFY_FETCHES => true,
68        );
69    }
70
71
72    public function initHelpers()
73    {
74        $this->manager = new ilDBPdoManagerPostgres($this->pdo, $this);
75        $this->reverse = new ilDBPdoReversePostgres($this->pdo, $this);
76        $this->field_definition = new ilDBPdoPostgresFieldDefinition($this);
77    }
78
79
80    /**
81     * Primary key identifier
82     */
83    public function getPrimaryKeyIdentifier()
84    {
85        return "pk";
86    }
87
88
89    /**
90     * @return bool
91     */
92    public function supportsFulltext()
93    {
94        return false;
95    }
96
97
98    /**
99     * @return bool
100     */
101    public function supportsTransactions()
102    {
103        return true;
104    }
105
106
107    /**
108     * @param $a_table
109     * @param $a_constraint
110     * @return string
111     */
112    public function constraintName($a_table, $a_constraint)
113    {
114        $a_constraint = str_replace($a_table . '_', '', $a_constraint);
115
116        return $a_table . '_' . $a_constraint;
117    }
118
119
120    /**
121     * @param $index_name_base
122     * @return string
123     */
124    public function getIndexName($index_name_base)
125    {
126        return parent::getIndexName($index_name_base); // TODO: Change the autogenerated stub
127    }
128
129
130    /**
131     * @param $a_table
132     * @param $a_pk_columns
133     * @param $a_other_columns
134     * @return bool
135     * @throws \ilDatabaseException
136     */
137    public function replace($a_table, $a_pk_columns, $a_other_columns)
138    {
139        $a_columns = array_merge($a_pk_columns, $a_other_columns);
140        $fields = array();
141        $field_values = array();
142        $placeholders = array();
143        $types = array();
144        $values = array();
145        $lobs = false;
146        $lob = array();
147        $val_field = array();
148        $a = array();
149        $b = array();
150        foreach ($a_columns as $k => $col) {
151            if ($col[0] == 'clob' or $col[0] == 'blob') {
152                $val_field[] = $this->quote($col[1], 'text') . " " . $k;
153            } else {
154                $val_field[] = $this->quote($col[1], $col[0]) . " " . $k;
155            }
156            $fields[] = $k;
157            $placeholders[] = "%s";
158            $placeholders2[] = ":$k";
159            $types[] = $col[0];
160            $values[] = $col[1];
161            $field_values[$k] = $col[1];
162            if ($col[0] == "blob" || $col[0] == "clob") {
163                $lobs = true;
164                $lob[$k] = $k;
165            }
166            $a[] = "a." . $k;
167            $b[] = "b." . $k;
168        }
169        $abpk = array();
170        $aboc = array();
171        $delwhere = array();
172        foreach ($a_pk_columns as $k => $col) {
173            $abpk[] = "a." . $k . " = b." . $k;
174            $delwhere[] = $k . " = " . $this->quote($col[1], $col[0]);
175        }
176        foreach ($a_other_columns as $k => $col) {
177            $aboc[] = "a." . $k . " = b." . $k;
178        }
179        //		if ($lobs)	// lobs -> use prepare execute (autoexecute broken in PEAR 2.4.1)
180        //		{
181        $this->manipulate("DELETE FROM " . $a_table . " WHERE " . implode(" AND ", $delwhere));
182        $this->insert($a_table, $a_columns);
183
184        return true;
185    }
186
187
188    /**
189     * @param array $a_tables
190     * @deprecated Use ilAtomQuery instead
191     * @return bool
192     */
193    public function lockTables($a_tables)
194    {
195        $locks = array();
196
197        $counter = 0;
198        foreach ($a_tables as $table) {
199            if (!isset($table['sequence']) && $table['sequence']) {
200                $lock = 'LOCK TABLE ' . $table['name'];
201
202                switch ($table['type']) {
203                    case ilDBConstants::LOCK_READ:
204                        $lock .= ' IN SHARE MODE ';
205                        break;
206
207                    case ilDBConstants::LOCK_WRITE:
208                        $lock .= ' IN EXCLUSIVE MODE ';
209                        break;
210                }
211
212                $locks[] = $lock;
213            }
214        }
215
216        // @TODO use and store a unique identifier to allow nested lock/unlocks
217        $this->beginTransaction();
218        foreach ($locks as $lock) {
219            $this->query($lock);
220        }
221
222        return true;
223    }
224
225
226    /**
227     * @throws \ilDatabaseException
228     * @deprecated Use ilAtomQuery instead
229     */
230    public function unlockTables()
231    {
232        $this->commit();
233    }
234
235
236    public function getStorageEngine()
237    {
238        return null;
239    }
240
241
242    public function setStorageEngine($storage_engine)
243    {
244        return false;
245    }
246
247    //
248    //
249    //
250
251    /**
252     * @param string $table_name
253     * @return mixed
254     * @throws \ilDatabaseException
255     */
256    public function nextId($table_name)
257    {
258        $sequence_name = $table_name . '_seq';
259        $query = "SELECT NEXTVAL('$sequence_name')";
260        $result = $this->query($query, 'integer');
261        $data = $result->fetchObject();
262
263        return $data->nextval;
264    }
265
266
267    /**
268     * @param $table_name
269     * @param bool $error_if_not_existing
270     * @return int
271     */
272    public function dropTable($table_name, $error_if_not_existing = false)
273    {
274        try {
275            $this->pdo->exec("DROP TABLE $table_name");
276        } catch (PDOException $PDOException) {
277            if ($error_if_not_existing) {
278                throw $PDOException;
279            }
280
281            return false;
282        }
283
284        return true;
285    }
286
287
288    /**
289     * @param $identifier
290     * @param bool $check_option
291     * @return mixed
292     */
293    public function quoteIdentifier($identifier, $check_option = false)
294    {
295        return '"' . $identifier . '"';
296    }
297
298
299    /**
300     * @param string $table_name
301     * @return bool
302     */
303    public function tableExists($table_name)
304    {
305        $tables = $this->listTables();
306
307        if (is_array($tables)) {
308            if (in_array($table_name, $tables)) {
309                return true;
310            }
311        }
312
313        return false;
314    }
315
316
317    /**
318     * @param $query
319     * @return string
320     */
321    protected function appendLimit($query)
322    {
323        if ($this->limit !== null && $this->offset !== null) {
324            $query .= ' LIMIT ' . (int) $this->limit . ' OFFSET ' . (int) $this->offset;
325            $this->limit = null;
326            $this->offset = null;
327
328            return $query;
329        }
330
331        return $query;
332    }
333
334
335    /**
336     * @param $table_name  string
337     * @param $column_name string
338     *
339     * @return bool
340     */
341    public function tableColumnExists($table_name, $column_name)
342    {
343        return in_array($column_name, $this->manager->listTableFields($table_name));
344    }
345
346
347    /**
348     * @param $a_name
349     * @param $a_new_name
350     * @return bool
351     * @throws \ilDatabaseException
352     */
353    public function renameTable($a_name, $a_new_name)
354    {
355        // check table name
356        try {
357            $this->checkTableName($a_new_name);
358        } catch (ilDatabaseException $e) {
359            return true;
360        }
361
362        if ($this->tableExists($a_new_name)) {
363            return true;
364        }
365        try {
366            $this->manager->alterTable($a_name, array( "name" => $a_new_name ), false);
367        } catch (Exception $e) {
368            return true;
369        }
370
371        return true;
372    }
373
374
375    /**
376     * @param $table_name
377     * @param int $start
378     * @return bool
379     */
380    public function createSequence($table_name, $start = 1)
381    {
382        if (in_array($table_name, $this->manager->listSequences())) {
383            return true;
384        }
385        try {
386            parent::createSequence($table_name, $start); // TODO: Change the autogenerated stub
387        } catch (Exception $e) {
388            return true;
389        }
390    }
391
392
393    /**
394     * @param $table_name
395     * @param $fields
396     * @param bool $drop_table
397     * @param bool $ignore_erros
398     * @return bool|mixed
399     * @throws \ilDatabaseException
400     */
401    public function createTable($table_name, $fields, $drop_table = false, $ignore_erros = false)
402    {
403        if ($this->tableExists($table_name)) {
404            return true;
405        }
406        try {
407            return parent::createTable($table_name, $fields, $drop_table, $ignore_erros); // TODO: Change the autogenerated stub
408        } catch (Exception $e) {
409            return true;
410        }
411    }
412
413
414    /**
415     * @param string $table_name
416     * @param array $primary_keys
417     * @return bool
418     */
419    public function addPrimaryKey($table_name, $primary_keys)
420    {
421        require_once('./Services/Database/classes/class.ilDBAnalyzer.php');
422        $ilDBAnalyzer = new ilDBAnalyzer($this);
423        if ($ilDBAnalyzer->getPrimaryKeyInformation($table_name)) {
424            return true;
425        }
426        try {
427            return parent::addPrimaryKey($table_name, $primary_keys); // TODO: Change the autogenerated stub
428        } catch (Exception $e) {
429            return true;
430        }
431    }
432
433
434    public function addIndex($table_name, $fields, $index_name = '', $fulltext = false)
435    {
436        $indices = $this->manager->listTableIndexes($table_name);
437        if (in_array($this->constraintName($table_name, $index_name), $indices)) {
438            return true;
439        }
440        try {
441            return parent::addIndex($table_name, $fields, $index_name, $fulltext); // TODO: Change the autogenerated stub
442        } catch (Exception $e) {
443            return true;
444        }
445    }
446
447
448    public function addUniqueConstraint($table, $fields, $name = "con")
449    {
450        try {
451            return parent::addUniqueConstraint($table, $fields, $name); // TODO: Change the autogenerated stub
452        } catch (Exception $e) {
453            return true;
454        }
455    }
456
457    /**
458     * @param $table_name
459     * @return bool
460     */
461    public function dropPrimaryKey($table_name)
462    {
463        return $this->manager->dropConstraint($table_name, "pk", true);
464    }
465}
466