Beispiel #1
0
    /**
     * @inheritDoc
     */
    public function parse(QuoteInterface $quoting)
    {
        // get the columns of the inherited table
        $relationLog = $quoting->quoteIdent($this->relationLog->get('fullyQualifiedName'));
        $relationLogParent = $quoting->quoteIdent($this->relationLog->get('parent')->get('fullyQualifiedName'));
        $relationOriginal = $quoting->quoteIdent($this->relationOriginal->get('fullyQualifiedName'));
        $schema = $quoting->quoteIdent($this->relationLog->get('schema'));
        // get a attribute name quoted
        $getNameQuoted = function ($attribute) use($quoting) {
            return $quoting->quoteIdent($attribute->get('name'));
        };
        $output = <<<SQL
CREATE VIEW {$schema}."v{$this->relationLog->get('name')}History" AS
SELECT
    {$this->relationOriginal->getKeySql($quoting)}::key as key,
    array_agg( ROW( {$this->relationOriginal->getAttributes()->implode(', ', $getNameQuoted)} )::{$relationOriginal} ) as datas,
    array_agg( ROW( {$this->relationLog->get('parent')->getAttributes()->implode(', ', $getNameQuoted)} )::{$relationLogParent} ) AS info
FROM
    ( SELECT * FROM {$relationLog} ORDER BY "logId" DESC ) _
GROUP BY
    {$this->relationOriginal->getPrimaryKeys()->implode(', ', $getNameQuoted)}
;

COMMENT ON VIEW {$schema}."v{$this->relationLog->get('name')}History" IS E'
@normality.match: logs
@normality.makeable: MAKEABLE_EXCEPTION
@normality.isReadonly: READONLY_EXCEPTION
@normality.logging: {$this->relationOriginal->get('fullyQualifiedName')}
';

SQL;
        return $output;
    }
Beispiel #2
0
 private function getWhereBlock(QuoteInterface $quoting, $primaryKeys)
 {
     // single column pk
     if (count($primaryKeys) === 10) {
         $pk = $primaryKeys->pop();
         return sprintf("%s = \$1::%s", $quoting->quoteIdent($pk->get('name')), $quoting->quoteIdent($pk->getType()->get('name')));
     }
     // multicolumn pk
     $where = array();
     $c = 0;
     foreach ($primaryKeys as $pk) {
         $where[] = sprintf("%s = key[%d]::%s", $quoting->quoteIdent($pk->get('name')), ++$c, $quoting->quoteIdent($pk->getType()->get('name')));
     }
     return implode(" AND ", $where);
 }
Beispiel #3
0
    /**
     * @inheritDoc
     */
    public function parse(QuoteInterface $quoting)
    {
        // get the columns of the inherited table
        $relationLog = $quoting->quoteIdent($this->relationLog->get('fullyQualifiedName'));
        $relationOriginal = $quoting->quoteIdent($this->relationOriginal->get('fullyQualifiedName'));
        $schema = $quoting->quoteIdent($this->relationLog->get('schema'));
        $name = $this->relationLog->get('name');
        // build output columns
        $columnsLog = $this->relationLog->getAttributes();
        $columnsOriginal = $this->relationOriginal->getAttributes();
        $columns = array();
        foreach ($columnsOriginal as $columnOriginal) {
            $column = $quoting->quoteIdent($columnOriginal->get('name'));
            $typeLog = $columnsLog->findByName($columnOriginal->get('name'))->shift()->getType();
            $typeOriginal = $columnOriginal->getType();
            if ($typeLog !== $typeOriginal) {
                $column .= '::' . $quoting->quoteIdent($typeOriginal->get('fullyQualifiedName'));
            }
            $columns[] = $column;
        }
        $columns = implode(', ', $columns);
        // get a attribute name quoted
        $getNameQuoted = function ($attribute) use($quoting) {
            return $quoting->quoteIdent($attribute->get('name'));
        };
        $output = <<<SQL
CREATE FUNCTION {$schema}."{$name}"( "logId" INT )
RETURNS SETOF {$relationOriginal} AS \$\$
    SELECT
        {$columns}
    FROM
        ( SELECT * FROM {$relationLog} WHERE "logId" <= \$1 AND op <> 'DELETE' ) _
    INNER JOIN
        ( SELECT MAX("logId") AS "logId" FROM {$relationLog} WHERE "logId" <= \$1 GROUP BY {$this->relationOriginal->getPrimaryKeys()->implode(', ', $getNameQuoted)} ) __
    USING
        ("logId")
    ;
\$\$ LANGUAGE SQL IMMUTABLE;
SQL;
        return $output;
    }
Beispiel #4
0
 /**
  * {@inheritDoc}
  */
 public function parse(QuoteInterface $quoting)
 {
     return $quoting->quoteIdent($this->getFullyQualifiedName());
 }
Beispiel #5
0
 /**
  * @inheritDoc
  */
 public function parse(QuoteInterface $quotingInterface)
 {
     return $quotingInterface->quote($this->format(self::POSTGRES_ISO_8601));
 }
Beispiel #6
0
 /**
  * @inheritDoc
  */
 public function parse(QuoteInterface $quoting)
 {
     return $quoting->quoteIdentifier($this->name);
 }
Beispiel #7
0
 /**
  * @inheritDoc
  */
 public function parse(QuoteInterface $quoting)
 {
     if (!isset($this->sqlIdentifier)) {
         throw new \RuntimeException("You can't use a Bond\\Set in a Query object without first setting its \$this->sqlIdentifier property.");
     }
     $numIntervals = count($this->intervals);
     $currentInterval = 0;
     $sqlFragments = array();
     // the full range
     if ($numIntervals === 1 and $this->intervals[0] === array(null, null)) {
         // with null, this matches everything
         if ($this->containsNull) {
             return "TRUE";
         } else {
             // just not null values
             $sqlFragments[] = sprintf("%s IS NOT NULL", $this->sqlIdentifier);
             $currentInterval++;
         }
         // we've potentially got range(s)
     } elseif ($numIntervals) {
         // We'll only ever have one < test and one > test. Lets handle those.
         if ($this->intervals[0][0] === null) {
             $currentInterval++;
             $sqlFragments[] = sprintf("%s <= %s", $this->sqlIdentifier, $quoting->quote($this->intervals[0][1]));
         }
         if ($this->intervals[$numIntervals - 1][1] === null) {
             $sqlFragments[] .= sprintf("%s >= %s", $this->sqlIdentifier, $quoting->quote($this->intervals[$numIntervals - 1][0]));
             $numIntervals--;
         }
     }
     // all these will be BETWEEN statements or IN values
     $sqlValues = array();
     for (; $currentInterval < $numIntervals; $currentInterval++) {
         // in values
         if ($this->intervals[$currentInterval][0] === $this->intervals[$currentInterval][1]) {
             $sqlValues[] = $quoting->quote($this->intervals[$currentInterval][0]);
             // between
         } else {
             $sqlFragments[] = sprintf("%s BETWEEN %s AND %s", $this->sqlIdentifier, $quoting->quote($this->intervals[$currentInterval][0]), $quoting->quote($this->intervals[$currentInterval][1]));
         }
     }
     // NULL
     if ($this->containsNull) {
         $sqlFragments[] = sprintf("%s IS NULL", $this->sqlIdentifier);
     }
     // turn any sql values into a IN statement
     if ($sqlValues) {
         $sqlFragments[] = sprintf("%s IN (%s)", $this->sqlIdentifier, implode(',', $sqlValues));
     }
     return count($sqlFragments) ? "( " . implode(' OR ', $sqlFragments) . " )" : "FALSE";
 }
Beispiel #8
0
 /**
  * @inheritDoc
  */
 public function parse(QuoteInterface $quoteInterface)
 {
     return $quoteInterface->quoteIdent($this->value);
 }
Beispiel #9
0
 /**
  * @inheritDoc
  */
 public function parse(QuoteInterface $quoting)
 {
     return $quoting->quote($this->oid);
 }
Beispiel #10
0
    /**
     * Update query for a data array
     * @param string $table
     * @param array $primaryKeys
     * @param array $data
     * @return string SQL insert statement
     */
    public static function buildQueryUpdate(QuoteInterface $quoting, $table, array $primaryKeys, array $data, array $dataInitial, array $returning = null)
    {
        if (!count($data) === 0) {
            throw new NothingToDoException("Nothing to generate.");
        }
        $columnNames = array_keys($data[0]);
        $columnNamesInitial = array_keys($dataInitial[0]);
        // select statement
        $dataAsSql = "VALUES " . ($columnNamesInitial ? self::dataTuplesInitial($data, $dataInitial) : self::dataTuples($data));
        $columns = array();
        $where = array();
        // core data
        foreach (array_keys($data[0]) as $key => $column) {
            $sqlFragment = sprintf('%s = "uc"."%s"', $quoting->quoteIdent($column), $columnNames[$key]);
            $columns[] = $sqlFragment;
            if (array_key_exists($column, $primaryKeys)) {
                $where[$column] = '"t".' . $sqlFragment;
            }
        }
        // dataInitial
        // all dataInitial columns are primary keys, see self::extractColumnInfoFromDataTypes()
        foreach (array_keys($dataInitial[0]) as $key => $column) {
            // overwrite existing where definition
            $where[$column] = sprintf('"t".%s = "uc"."__initial_%s"', $quoting->quoteIdent($column), $columnNames[$key]);
            $columnNames[] = "__initial_{$column}";
        }
        if (!count($columns)) {
            throw new NothingToDoException("Nothing to update for `{$table}`. Every column passed in the data array is a primary key column.");
        }
        return sprintf(<<<SQL
UPDATE
    %s AS "t"
SET
    %s
FROM
    (
       %s
    ) AS uc ( "%s" )
WHERE
    %s
%s;
SQL
, $quoting->quoteIdent($table), implode(', ', $columns), $dataAsSql, implode('", "', $columnNames), implode(' AND ', $where), '');
    }
Beispiel #11
0
    public function parse(QuoteInterface $quoting)
    {
        $schemaOut = $this->inherits->get('schema');
        $logTable = "{$schemaOut}.{$this->relation->get('name')}";
        $logTableQuoted = $quoting->quoteIdent($logTable);
        $originalTable = $quoting->quoteIdent($this->relation->get('fullyQualifiedName'));
        $schemaOut = $quoting->quoteIdent($schemaOut);
        $inherits = $quoting->quoteIdent($this->inherits->get('fullyQualifiedName'));
        $columnsInherits = $this->inherits->getAttributes()->pluck('name');
        foreach ($this->relation->getAttributes() as $column) {
            $name = $column->get('name');
            if (in_array($name, $columnsInherits)) {
                throw new \LogicException("Can't create a log table from {$originalTable}. The column names {$name} is reserved.");
            }
            // casting
            $type = $column->getType();
            if (isset(self::$recast[$type->get('name')])) {
                $cast = self::$recast[$type->get('name')];
            } elseif ($type->isEnum()) {
                $cast = 'text';
            } else {
                $cast = null;
            }
            $insertColumns[] = sprintf("%s%s", $quoting->quoteIdent($name), $cast ? "::{$cast}" : null);
            $createColumns[] = sprintf("%s %s", $quoting->quoteIdent($name), $quoting->quoteIdent($cast ?: $type->get('name')));
        }
        // callback to get a quoted name
        $quotedNameGet = function ($column) use($quoting) {
            return $quoting->quoteIdent($column->get('name'));
        };
        $columns = $this->relation->getAttributes()->map($quotedNameGet);
        $valuesNew = implode(', ', $this->inheritedValues + array_map($this->stringPrepend('NEW.'), $columns));
        $valuesOld = implode(', ', $this->inheritedValues + array_map($this->stringPrepend('OLD.'), $columns));
        $columns = implode(', ', $this->inherits->getAttributes()->map($quotedNameGet) + $columns);
        $insertColumns = implode(', ', $insertColumns);
        $createColumns = implode(",\n    ", $createColumns);
        $name = $this->relation->get('name');
        $output = <<<SQL
CREATE TABLE {$logTableQuoted} (
    {$createColumns},
    CONSTRAINT "pk_logs_{$name}" PRIMARY KEY ("logId")
) INHERITS ( {$inherits} );

CREATE TRIGGER "trg_log_{$name}_restrict"
    BEFORE UPDATE OR DELETE
    ON {$logTableQuoted} FOR EACH STATEMENT EXECUTE PROCEDURE common.restrictTrigger();

CREATE INDEX "idx_logPk_{$name}" on {$logTableQuoted} USING btree ( {$this->relation->getPrimaryKeys()->implode(',', $quotedNameGet)} );

COMMENT ON TABLE {$logTableQuoted} IS E'
@normality.match: logs
@normality.makeable: MAKEABLE_EXCEPTION
@normality.isReadonly: READONLY_EXCEPTION
@normality.logging: {$this->relation->get('fullyQualifiedName')}
';

CREATE OR REPLACE FUNCTION {$schemaOut}."{$name}_insert_update"() RETURNS TRIGGER LANGUAGE plpgsql AS \$\$
DECLARE
BEGIN

/*
    RAISE INFO E'Operation: % Schema: % Table: %',
        TG_OP,
        TG_TABLE_SCHEMA,
        TG_TABLE_NAME;
 */

    INSERT INTO {$logTableQuoted} ( {$columns} )
    VALUES( {$valuesNew} );

    RETURN NEW;

END;\$\$;

CREATE OR REPLACE FUNCTION {$schemaOut}."{$name}_delete"() RETURNS TRIGGER LANGUAGE plpgsql AS \$\$
DECLARE
BEGIN

/*
    RAISE INFO E'Operation: % Schema: % Table: %',
        TG_OP,
        TG_TABLE_SCHEMA,
        TG_TABLE_NAME;
 */

    INSERT INTO {$logTableQuoted} ( {$columns} )
    VALUES( {$valuesOld} );

    RETURN NEW;

END;\$\$;

CREATE TRIGGER "trg_log_{$name}_insert_update"
    AFTER INSERT OR UPDATE
    ON {$originalTable} FOR EACH ROW EXECUTE PROCEDURE {$schemaOut}."{$name}_insert_update"();

CREATE TRIGGER "trg_log_{$name}_delete"
    AFTER DELETE
    ON {$originalTable} FOR EACH ROW EXECUTE PROCEDURE {$schemaOut}."{$name}_delete"();

SQL;
        return $output;
    }
Beispiel #12
0
 /**
  * @inheritDoc
  */
 public function parse(QuoteInterface $quotingInterface)
 {
     return $quotingInterface->quote($this->__toString());
 }
Beispiel #13
0
 /**
  * Helper function for ->buildQuery(). Where clause.
  * @param array $filterComponents
  * @param array $data
  * @return string
  */
 protected function buildQueryWhereClause(QuoteInterface $db, $filterComponents, array &$data = array())
 {
     // anything to do?
     if (!$filterComponents) {
         return '';
     }
     $output = 'WHERE';
     // build up output
     foreach ($filterComponents as $name => $component) {
         $fragment = '';
         $operation = $component->operation;
         $value = $component->value;
         // AND OR operator keywords
         if ($operation) {
             $fragment .= " {$component->operation}";
         }
         // we going to use a `name` = methodology
         // something more specific?
         if ($value instanceof Set) {
             $value->sqlIdentifierSet($db->quoteIdent($name));
             $comparitor = '';
         } else {
             $fragment .= sprintf(" %s %s", $db->quoteIdent($name), $value instanceof SqlCollectionInterface ? 'IN' : '=');
         }
         // null safe comparison
         if (is_null($component->value)) {
             $fragment = rtrim($fragment, '=');
             $fragment .= " IS NULL";
         } else {
             $fragment .= " {$component->tag}";
             $data[$name] = $component->value;
         }
         $output .= " {$fragment}";
     }
     return $output;
 }
Beispiel #14
0
 /**
  * Produce a sql statement which resolves to a string but is a valid json
  * @return string Sql which evaluates to JSON
  */
 public function donovan(QuoteInterface $quoting, $prefix, $columns)
 {
     $sql = array();
     foreach ($columns as $column => $type) {
         $sql[] = sprintf('donovan(%s%s)', $prefix, $quoting->quoteIdent($column));
     }
     return sprintf("'['||%s||']'", implode("||','||", $sql));
 }