Build a generic insert/ignore/replace query

@param array $statements
@param array $data
@param string $type

@return array
@throws Exception
353911
private function doInsert(array $statements, array $data, $type): array
    {
        $table = end($statements['tables']);

        $bindings = $keys = $values = [];

        foreach ($data as $key => $value) {
            $keys[] = $key;
            if ($value instanceof Raw) {
                $values[] = (string)$value;
            } else {
                $values[] = '?';
                $bindings[] = $value;
            }
        }

        $sqlArray = [
            $type . ' INTO',
            $this->wrapSanitizer($table),
            '(' . $this->arrayStr($keys) . ')',
            'VALUES',
            '(' . $this->arrayStr($values, ',', false) . ')',
        ];

        if (isset($statements['onduplicate']) === true) {

            if (\count($statements['onduplicate']) < 1) {
                throw new Exception('No data given.', 4);
            }

            [$updateStatement, $updateBindings] = $this->getUpdateStatement($statements['onduplicate']);
            $sqlArray[] = 'ON DUPLICATE KEY UPDATE ' . $updateStatement;
            $bindings = array_merge($bindings, $updateBindings);

        }

        $sql = $this->concatenateQuery($sqlArray);

        return compact('sql', 'bindings');
    }
156963
public function getQuery(string $type = 'select', ...$arguments): QueryObject
    {
        $allowedTypes = [
            'select',
            'insert',
            'insertignore',
            'replace',
            'delete',
            'update',
            'criteriaonly',
        ];

        if (\in_array(strtolower($type), $allowedTypes, true) === false) {
            throw new Exception($type . ' is not a known type.', 1);
        }

        $queryArr = $this->adapterInstance->$type($this->statements, ...$arguments);

        return new QueryObject($queryArr['sql'], $queryArr['bindings'], $this->getConnection());
    }
874321
public function insert($tableName, array $data, array $types = []): int
    {
        $modified = $this->shallModifyIdentityInsert($data)
            && $this->modifyIdentityInsert($tableName, true);

        $result = parent::insert($tableName, $data, $types);

        if ($modified) {
            $this->modifyIdentityInsert($tableName, false);
        }

        return $result;
    }
770056
public function delete($statements)
    {
        if (!isset($statements['tables'])) {
            throw new Exception('No table specified', 3);
        }

        $table = end($statements['tables']);

        // Wheres
        list($whereCriteria, $whereBindings) = $this->buildCriteriaWithType($statements, 'wheres', 'WHERE');

        // Limit
        $limit = isset($statements['limit']) ? 'LIMIT ' . $statements['limit'] : '';

        $sqlArray = array('DELETE FROM', $this->wrapSanitizer($table), $whereCriteria);
        $sql = $this->concatenateQuery($sqlArray);
        $bindings = $whereBindings;

        return compact('sql', 'bindings');
    }
815791
public static function insertBySql($sql,$data=[],$db_name=''){
		$db = self::getDb($db_name);
		$result = $db->createCommand($sql,$data)
				->execute();
		return $result;
	
	}
1260791
protected function insert(array $data = array())
    {
        $fields = array();
        $values = array();
        
        foreach ($data as $field => $value) {
            $fields[] = $field;
            $values[] = '?';
        }
        
        $sql = "INSERT INTO $this->_table " 
                . "(" . implode(', ', $fields) . ")"
                . "VALUES (" . implode(', ', $values) . ")";
                
        $pdos = $this->_pdo->prepare($sql);
        
        $pdos->execute(array_values($data));
        
        return $pdos->statement()->rowCount();
    }
1015671
public function insert(DataAbstract $data, $forceUpdate = false)
    {
        if ($data->exists() && !$data->isUpdated()) {
            return false;
        }

        //~ Reset binded fields
        $this->binds = array();

        $querySet = $this->getQueryFieldsSet($data, false);
        if (empty($querySet)) {
            throw new \LogicException(__METHOD__ . '|Set clause cannot be empty !');
        } else {
            $querySet = ' ' . $querySet;
        }

        $queryDuplicateUpdate = '';

        if ($forceUpdate || $data->exists()) {
            $queryDuplicateUpdate = $this->getQueryFieldsOnDuplicateUpdate($data);

            if (empty($queryDuplicateUpdate)) {
                throw new \LogicException(__METHOD__ . '|ON DUPLICATE UPDATE clause cannot be empty !');
            }

            $queryDuplicateUpdate = ' ' . $queryDuplicateUpdate;
        }

        $query     = 'INSERT INTO ' . $this->getTable() . $querySet . $queryDuplicateUpdate;
        $statement = $this->db->prepare($query);
        $statement->execute($this->binds);

        //~ If has auto increment key (generaly, is a primary key), set value
        $lastInsertId = (int) $this->db->lastInsertId();
        if ($lastInsertId > 0) {
            $this->lastId = $lastInsertId;

            $data->setAutoIncrementId($this->getLastId());
        }

        //~ Reset some data
        $data->setExists(true);
        $data->resetUpdated();

        //~ Clear
        $this->clear();
        $this->deleteCache($data);

        return true;
    }
1131436
public function select($statements)
    {
        if (!array_key_exists('tables', $statements)) {
            throw new Exception('No table specified.', 3);
        } elseif (!array_key_exists('selects', $statements)) {
            $statements['selects'][] = '*';
        }

        // From
        $tables = $this->arrayStr($statements['tables'], ', ');
        // Select
        $selects = $this->arrayStr($statements['selects'], ', ');


        // Wheres
        list($whereCriteria, $whereBindings) = $this->buildCriteriaWithType($statements, 'wheres', 'WHERE');
        // Group bys
        $groupBys = '';
        if (isset($statements['groupBys']) && $groupBys = $this->arrayStr($statements['groupBys'], ', ')) {
            $groupBys = 'GROUP BY ' . $groupBys;
        }

        // Order bys
        $orderBys = '';
        if (isset($statements['orderBys']) && is_array($statements['orderBys'])) {
            foreach ($statements['orderBys'] as $orderBy) {
                $orderBys .= $this->wrapSanitizer($orderBy['field']) . ' ' . $orderBy['type'] . ', ';
            }

            if ($orderBys = trim($orderBys, ', ')) {
                $orderBys = 'ORDER BY ' . $orderBys;
            }
        }

        // Limit and offset
        $limit = isset($statements['limit']) ? 'LIMIT ' . $statements['limit'] : '';
        $offset = isset($statements['offset']) ? 'OFFSET ' . $statements['offset'] : '';

        // Having
        list($havingCriteria, $havingBindings) = $this->buildCriteriaWithType($statements, 'havings', 'HAVING');

        // Joins
        $joinString = $this->buildJoin($statements);

        $sqlArray = array(
            'SELECT' . (isset($statements['distinct']) ? ' DISTINCT' : ''),
            $selects,
            'FROM',
            $tables,
            $joinString,
            $whereCriteria,
            $groupBys,
            $havingCriteria,
            $orderBys,
            $limit,
            $offset
        );

        $sql = $this->concatenateQuery($sqlArray);

        $bindings = array_merge(
            $whereBindings,
            $havingBindings
        );

        return compact('sql', 'bindings');
    }
263529
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 ),
            '' #static::getReturningClause( $returning, 't' )
        );

    }
1178816
public function startQuery($sql, array $params = null, array $types = null)
    {
        $msg = 'SQL: '.$sql;
        if ($params) {
            $msg .= PHP_EOL."\tPARAMS: ".json_encode($params);
        }
        if ($types) {
            $msg .= PHP_EOL."\tTYPES: ".json_encode($types);
        }
        $this->debug($msg);
    }
1030659
public static function setDataTypes(array $types) : void
    {
        // Datatypes key validation
        foreach ($types as $key => $value) {
            if (!preg_match('/^[A-Za-z0-9-_]+$/', $key)) {
                throw new \UnexpectedValueException("<b>$key</b> is not a valid datatype key", 1);

            } elseif (!is_string($value)) {
                throw new \LogicException("<b>$key</b> must have a string value", 1);
            }
        }

        // Put keys between brackets
        self::$datatypes = array_combine(
            array_map(function ($t) {
                return '/\{' . $t . '\}/';
            }, array_keys($types)),
            $types
        );
    }
18297
public function add($type, $data = [])
    {
        if ($this->type() === 'passed' && $type === 'failed') {
            $this->type('failed');
        }
        $data['type'] = $type;
        if (!isset($data['backtrace'])) {
            $data['backtrace'] = [];
        } else {
            $data['backtrace'] = Debugger::focus($this->block()->suite()->backtraceFocus(), $data['backtrace'], 1);
        }
        $child = new static($data + ['block' => $this->_block]);
        $this->_children[] = $child;
        return $child;
    }
1025001
public function compileInsert(Builder $query, array $values) {
    foreach($values as &$row) {
      $this->beforePut($row);
    }

    unset($row);

    $this->remapWheres($query);
    $sql = $this->grammar->compileInsert($query, $values);

    if($this->replace_into) {
      $sql = preg_replace('/^insert into/i', 'replace into', $sql);
      $this->replace_into = false;
    }

    return $sql;
  }
203826
public function generateUpdate($changeData, &$data, &$values, &$types)
    {
        // Prepare
        $data = "";

        // Generate the column definition
        $idx = 0;
        $max = count($changeData);
        foreach ($changeData as $column => $value) {
            // Prepare set part
            $data .= "$column = ?";
            $values[] = $this->prepareData($value);
            $types[] = $this->determinateType($value);

            // Adding ,
            if (($idx + 1) < $max) {
                $data .= ",";
            }

            $idx++;
        }
    }
134016
public function compileInsert(Builder $query, array $values)
    {
        // Essentially we will force every insert to be treated as a batch insert which
        // simply makes creating the SQL easier for us since we can utilize the same
        // basic routine regardless of an amount of records given to us to insert.
        $table = $this->wrapTable($query->from);
        if (!is_array(reset($values))) {
            $values = array($values);
        }

        $columns = $this->columnize(array_keys(reset($values)));

        // We need to build a list of parameter place-holders of values that are bound
        // to the query. Each insert should have the exact same amount of parameter
        // bindings so we can just go off the first list of values in this array.

        $rows = [];
        foreach ($values as $value) {
            $col_values = [];
            foreach ($value as $val) {
                $col_values[] = $this->parameter($val);
            }
            $rows[] = implode(',', $col_values);
        }
        $parameters = "(" . implode('), (', $rows) . ")";

        return "insert into $table ($columns) values $parameters";
    }
520565
public function addReference(&$data, $type)
    {
        if (!$this->validate($data)) {
            return false;
        }

        $this->store[$type][] =& $data;
        return $data;
    }
203123
public function insert(string $table, array $fields, string $onConflict = null, string $conflictTarget = null):array{
		$sql = parent::insert($table, $fields);

		if(in_array($onConflict, ['IGNORE', 'REPLACE'], true)){

			if(empty($conflictTarget)){
				throw new QueryException('postgres insert on conflict: no conflict target given');
			}

			$sql[] =  'ON CONFLICT ('.$this->quote($conflictTarget).') DO';

			switch($onConflict){
				case 'IGNORE':
					$sql[] = 'NOTHING';
					break;
				case 'REPLACE':
					$sql[] = $this->onConflictUpdate($fields);
					break;
			}

		}

		return $sql;
	}