class.ilPostgresQueryUtils.php

Go to the documentation of this file.

<?php
require_once('./Services/Database/classes/QueryUtils/class.ilQueryUtils.php');

class ilPostgresQueryUtils extends ilQueryUtils {

        public function createTable($name, $fields, $options = array()) {
                if (!$name) {
                        throw new ilDatabaseException('no valid table name specified');
                }
                if (empty($fields)) {
                        throw new ilDatabaseException('no fields specified for table "' . $name . '"');
                }
                $query_fields_array = array();
                foreach ($fields as $field_name => $field) {
                        $query_fields_array[] = $this->db_instance->getFieldDefinition()->getDeclaration($field['type'], $field_name, $field);
                }

                $query_fields = implode(', ', $query_fields_array);

                if (!empty($options['primary'])) {
                        $query_fields .= ', PRIMARY KEY (' . implode(', ', array_keys($options['primary'])) . ')';
                }

                $query = "CREATE  TABLE $name ($query_fields)";

                $options_strings = array();

                if (!empty($options['comment'])) {
                        $options_strings['comment'] = 'COMMENT = ' . $this->quote($options['comment'], 'text');
                }

                if (!empty($options['charset'])) {
                        $options_strings['charset'] = 'DEFAULT CHARACTER SET ' . $options['charset'];
                        if (!empty($options['collate'])) {
                                $options_strings['charset'] .= ' COLLATE ' . $options['collate'];
                        }
                }

                $type = false;
                if (!empty($options['type'])) {
                        $type = $options['type'];
                }
                if ($type) {
                        $options_strings[] = "ENGINE = $type";
                }

                if (!empty($options_strings)) {
                        $query .= ' ' . implode(' ', $options_strings);
                }

                return $query;
        }


        public function in($field, $values, $negate = false, $type = "") {
                if (count($values) == 0) {
                        // BEGIN fixed mantis #0014191:
                        //return " 1=2 ";               // return a false statement on empty array
                        return $negate ? ' 1=1 ' : ' 1=2 ';
                        // END fixed mantis #0014191:
                }
                if ($type == "")        // untyped: used ? for prepare/execute
                {
                        $str = $field . (($negate) ? " NOT" : "") . " IN (?" . str_repeat(",?", count($values) - 1) . ")";
                } else                    // typed, use values for query/manipulate
                {
                        $str = $field . (($negate) ? " NOT" : "") . " IN (";
                        $sep = "";
                        foreach ($values as $v) {
                                $str .= $sep . $this->quote($v, $type);
                                $sep = ",";
                        }
                        $str .= ")";
                }

                return $str;
        }


        public function quote($value, $type = null) {
                return $this->db_instance->quote($value, $type);
        }


        public function concat(array $values, $allow_null = true) {
                if (!count($values)) {
                        return ' ';
                }

                $concat = ' CONCAT(';
                $first = true;
                foreach ($values as $field_info) {
                        $val = $field_info[0];

                        if (!$first) {
                                $concat .= ',';
                        }

                        if ($allow_null) {
                                $concat .= 'COALESCE(';
                        }
                        $concat .= $val;

                        if ($allow_null) {
                                $concat .= ",''";
                                $concat .= ')';
                        }

                        $first = false;
                }
                $concat .= ') ';

                return $concat;
        }


        public function locate($a_needle, $a_string, $a_start_pos = 1) {
                $locate = ' LOCATE( ';
                $locate .= $a_needle;
                $locate .= ',';
                $locate .= $a_string;
                $locate .= ',';
                $locate .= $a_start_pos;
                $locate .= ') ';

                return $locate;
        }


        public function free(ilPDOStatement $statement) {
                $statement->closeCursor();

                return true;
        }


        public function quoteIdentifier($identifier) {
                return $this->db_instance->quoteIdentifier($identifier);
        }


        public function like($column, $type, $value = "?", $case_insensitive = true) {
                if (!in_array($type, array(
                        ilDBConstants::T_TEXT,
                        ilDBConstants::T_CLOB,
                        "blob",
                ))
                ) {
                        throw new ilDatabaseException("Like: Invalid column type '" . $type . "'.");
                }
                if ($value == "?") {
                        if ($case_insensitive) {
                                return "UPPER(" . $column . ") LIKE(UPPER(?))";
                        } else {
                                return $column . " LIKE(?)";
                        }
                } else {
                        if ($case_insensitive) {
                                // Always quote as text
                                return " UPPER(" . $column . ") LIKE(UPPER(" . $this->quote($value, 'text') . "))";
                        } else {
                                // Always quote as text
                                return " " . $column . " LIKE(" . $this->quote($value, 'text') . ")";
                        }
                }
        }


        public function now() {
                return "now()";
        }


        public function lock(array $tables) {
                $lock = 'LOCK TABLES ';

                $counter = 0;
                foreach ($tables as $table) {
                        if ($counter ++) {
                                $lock .= ', ';
                        }

                        if (isset($table['sequence']) && $table['sequence']) {
                                $table_name = $this->db_instance->getSequenceName($table['name']);
                        } else {
                                $table_name = $table['name'];
                        }

                        $lock .= ($table_name . ' ');

                        if ($table['alias']) {
                                $lock .= ($table['alias'] . ' ');
                        }

                        switch ($table['type']) {
                                case ilDBConstants::LOCK_READ:
                                        $lock .= ' READ ';
                                        break;

                                case ilDBConstants::LOCK_WRITE:
                                        $lock .= ' WRITE ';
                                        break;
                        }
                }

                return $lock;
        }


        public function unlock() {
                return 'UNLOCK TABLES';
        }


        public function createDatabase($a_name, $a_charset = "utf8", $a_collation = "") {
                if ($a_collation != "") {
                        $sql = "CREATE DATABASE " . $a_name . " CHARACTER SET " . $a_charset . " COLLATE " . $a_collation;
                } else {
                        $sql = "CREATE DATABASE " . $a_name . " CHARACTER SET " . $a_charset;
                }

                return $sql;
        }


        public function groupConcat($a_field_name, $a_seperator = ",", $a_order = NULL) {
                if ($a_order === NULL) {
                        $sql = "STRING_AGG(" . $a_field_name . ", " . $this->quote($a_seperator, "text") . ")";
                } else {
                        $sql = "STRING_AGG(" . $a_field_name . ", " . $this->quote($a_seperator, "text") . " ORDER BY " . $a_order . ")";
                        
                }
                return $sql;
        }
        

        public function cast($a_field_name, $a_dest_type) {
                return "CAST({$a_field_name} AS " . $this->db_instance->getFieldDefinition()->getTypeDeclaration(array("type" => $a_dest_type)) . ")";
        }

}