/** * @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; }
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); }
/** * @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; }
/** * {@inheritDoc} */ public function parse(QuoteInterface $quoting) { return $quoting->quoteIdent($this->getFullyQualifiedName()); }
/** * @inheritDoc */ public function parse(QuoteInterface $quotingInterface) { return $quotingInterface->quote($this->format(self::POSTGRES_ISO_8601)); }
/** * @inheritDoc */ public function parse(QuoteInterface $quoting) { return $quoting->quoteIdentifier($this->name); }
/** * @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"; }
/** * @inheritDoc */ public function parse(QuoteInterface $quoteInterface) { return $quoteInterface->quoteIdent($this->value); }
/** * @inheritDoc */ public function parse(QuoteInterface $quoting) { return $quoting->quote($this->oid); }
/** * 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), ''); }
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; }
/** * @inheritDoc */ public function parse(QuoteInterface $quotingInterface) { return $quotingInterface->quote($this->__toString()); }
/** * 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; }
/** * 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)); }