PHP Connection::executeUpdate方法代码示例

 /**
  * {@inheritdoc}
  */
 public function createNewToken(PersistentTokenInterface $token)
 {
     $sql = 'INSERT INTO rememberme_token' . ' (class, username, series, value, lastUsed)' . ' VALUES (:class, :username, :series, :value, :lastUsed)';
     $paramValues = array('class' => $token->getClass(), 'username' => $token->getUsername(), 'series' => $token->getSeries(), 'value' => $token->getTokenValue(), 'lastUsed' => $token->getLastUsed());
     $paramTypes = array('class' => \PDO::PARAM_STR, 'username' => \PDO::PARAM_STR, 'series' => \PDO::PARAM_STR, 'value' => \PDO::PARAM_STR, 'lastUsed' => DoctrineType::DATETIME);
     $this->conn->executeUpdate($sql, $paramValues, $paramTypes);
 }

    public function setUp()
    {
        $this->eventNameResolver = Mockery::mock('SimpleES\\EventSourcing\\Event\\NameResolver\\ResolvesEventNames');
        $this->eventNameResolver->shouldReceive('resolveEventClass')->with('an_event_happened')->andReturn('SimpleES\\DoctrineDBALBridge\\Test\\Auxiliary\\AnEventHappened');
        $this->eventNameResolver->shouldReceive('resolveEventClass')->with('another_event_happened')->andReturn('SimpleES\\DoctrineDBALBridge\\Test\\Auxiliary\\AnotherEventHappened');
        $this->eventNameResolver->shouldReceive('resolveEventClass')->with('yet_another_event_happened')->andReturn('SimpleES\\DoctrineDBALBridge\\Test\\Auxiliary\\YetAnotherEventHappened');
        $this->serializer = Mockery::mock('SimpleES\\EventSourcing\\Serializer\\SerializesData');
        $this->serializer->shouldReceive('serialize')->with(Mockery::type('SimpleES\\EventSourcing\\Event\\DomainEvent'))->andReturn('{"foo": "bar"}');
        $this->serializer->shouldReceive('serialize')->with(Mockery::type('SimpleES\\EventSourcing\\Metadata\\Metadata'))->andReturn('{"bar": "foo"}');
        $this->serializer->shouldReceive('deserialize')->with('{"foo": "bar"}', Mockery::type('string'))->andReturn(Mockery::mock('SimpleES\\EventSourcing\\Event\\DomainEvent'));
        $this->serializer->shouldReceive('deserialize')->with('{"bar": "foo"}', 'SimpleES\\EventSourcing\\Metadata\\Metadata')->andReturn(new Metadata([]));
        $this->connection = DriverManager::getConnection(['driver' => 'pdo_sqlite', 'memory' => true], new Configuration());
        $sql = <<<EOQ
CREATE TABLE event_store (
    id INTEGER NOT NULL,
    event_id CHAR(36) NOT NULL,
    event_name VARCHAR(255) NOT NULL,
    event_payload CLOB NOT NULL,
    aggregate_id CHAR(36) NOT NULL,
    aggregate_version INTEGER NOT NULL,
    took_place_at CHAR(31) NOT NULL,
    metadata CLOB NOT NULL,
    PRIMARY KEY(id)
);
CREATE UNIQUE INDEX lookup_idx ON event_store (aggregate_id, aggregate_version);
EOQ;
        $this->connection->executeUpdate($sql);
        $this->eventStore = new DBALEventStore($this->eventNameResolver, $this->serializer, $this->connection, 'event_store');
    }

 /**
  * {@inheritdoc}
  */
 public function execute(LoggerInterface $logger)
 {
     foreach ($this->queries as $query) {
         $logger->notice($query);
         $this->connection->executeUpdate($query);
     }
 }

 public function executeUpgrade(Connection $connection)
 {
     // update action class names
     $actions = ['Fusio\\Action\\BeanstalkPush' => 'Fusio\\Impl\\Action\\MqBeanstalk', 'Fusio\\Action\\CacheResponse' => 'Fusio\\Impl\\Action\\CacheResponse', 'Fusio\\Action\\Composite' => 'Fusio\\Impl\\Action\\Composite', 'Fusio\\Action\\Condition' => 'Fusio\\Impl\\Action\\Condition', 'Fusio\\Action\\HttpRequest' => 'Fusio\\Impl\\Action\\HttpRequest', 'Fusio\\Action\\Pipe' => 'Fusio\\Action\\Pipe', 'Fusio\\Action\\RabbitMqPush' => 'Fusio\\Impl\\Action\\MqAmqp', 'Fusio\\Action\\SqlExecute' => 'Fusio\\Impl\\Action\\SqlExecute', 'Fusio\\Action\\SqlFetchAll' => 'Fusio\\Impl\\Action\\SqlFetchAll', 'Fusio\\Action\\SqlFetchRow' => 'Fusio\\Impl\\Action\\SqlFetchRow', 'Fusio\\Action\\StaticResponse' => 'Fusio\\Impl\\Action\\StaticResponse'];
     foreach ($actions as $oldClass => $newClass) {
         $connection->executeUpdate('UPDATE fusio_action SET class = :new_class WHERE class = :old_class', ['new_class' => $newClass, 'old_class' => $oldClass]);
     }
     // update connection class names
     $actions = ['Fusio\\Connection\\Beanstalk' => 'Fusio\\Impl\\Connection\\Beanstalk', 'Fusio\\Connection\\DBAL' => 'Fusio\\Impl\\Connection\\DBAL', 'Fusio\\Connection\\DBALAdvanced' => 'Fusio\\Impl\\Connection\\DBALAdvanced', 'Fusio\\Connection\\MongoDB' => 'Fusio\\Impl\\Connection\\MongoDB', 'Fusio\\Connection\\Native' => 'Fusio\\Impl\\Connection\\Native', 'Fusio\\Connection\\RabbitMQ' => 'Fusio\\Impl\\Connection\\RabbitMQ'];
     foreach ($actions as $oldClass => $newClass) {
         $connection->executeUpdate('UPDATE fusio_connection SET class = :new_class WHERE class = :old_class', ['new_class' => $newClass, 'old_class' => $oldClass]);
     }
     // update routes class names
     $routes = $connection->fetchAll('SELECT id, controller FROM fusio_routes');
     foreach ($routes as $route) {
         if (substr($route['controller'], 0, 6) == 'Fusio\\' && substr($route['controller'], 0, 11) != 'Fusio\\Impl\\') {
             $newController = 'Fusio\\Impl\\' . substr($route['controller'], 6);
             $connection->executeUpdate('UPDATE fusio_routes SET controller = :controller WHERE id = :id', ['controller' => $newController, 'id' => $route['id']]);
         }
     }
     // insert new classes table
     $data = $this->getInstallInserts();
     if (isset($data['fusio_connection_class'])) {
         foreach ($data['fusio_connection_class'] as $row) {
             $connection->insert('fusio_connection_class', $row);
         }
     }
     if (isset($data['fusio_action_class'])) {
         foreach ($data['fusio_action_class'] as $row) {
             $connection->insert('fusio_action_class', $row);
         }
     }
 }

 protected function truncateTable($tableName)
 {
     if (!in_array($tableName, $this->truncatedTables)) {
         $query = $this->connection->getDatabasePlatform()->getTruncateTableSql($tableName, true);
         $this->connection->executeUpdate($query);
         $this->truncatedTables[] = $tableName;
     }
 }

 /**
  * Tests whether autoincrement works
  *
  * @return boolean true if autoincrement works, false otherwise
  */
 protected function checkAutoincrement()
 {
     $this->connection->executeUpdate('INSERT INTO ' . $this->tableName . ' ("text") VALUES ("test")');
     $insertId = $this->connection->lastInsertId();
     $this->connection->executeUpdate('DELETE FROM ' . $this->tableName . ' WHERE "someid" = ?', array($insertId));
     // insert again
     $this->connection->executeUpdate('INSERT INTO ' . $this->tableName . ' ("text") VALUES ("test2")');
     $newInsertId = $this->connection->lastInsertId();
     return $insertId !== $newInsertId;
 }

 public function rebuild()
 {
     $data = $this->build();
     $n = 0;
     $this->generate($data, 0, 0, $n);
     foreach ($data as $id => $row) {
         if ($id == 0) {
             continue;
         }
         $this->connection->executeUpdate(sprintf('UPDATE %s SET nlevel = %d, nleft = %d, nright = %d where %s = %d', $this->table, $row->nlevel, $row->nleft, $row->nright, $this->fields['id'], $id));
     }
 }

 /**
  * Perform UPDATE statement for an entity. This function has support for
  * optimistic locking if the entities ClassMetadata has versioning enabled.
  *
  * @param object $entity        The entity object being updated
  * @param string $tableName     The name of the table being updated
  * @param array $data           The array of data to set
  * @param array $where          The condition used to update
  * @return void
  */
 protected function _doUpdate($entity, $tableName, $data, $where)
 {
     // Note: $tableName and column names in $data are already quoted for SQL.
     $set = array();
     foreach ($data as $columnName => $value) {
         $set[] = $columnName . ' = ?';
     }
     if ($isVersioned = $this->_class->isVersioned) {
         $versionField = $this->_class->versionField;
         $versionFieldType = $this->_class->getTypeOfField($versionField);
         $where[$versionField] = Type::getType($versionFieldType)->convertToDatabaseValue($this->_class->reflFields[$versionField]->getValue($entity), $this->_platform);
         $versionFieldColumnName = $this->_class->getQuotedColumnName($versionField, $this->_platform);
         if ($versionFieldType == 'integer') {
             $set[] = $versionFieldColumnName . ' = ' . $versionFieldColumnName . ' + 1';
         } else {
             if ($versionFieldType == 'datetime') {
                 $set[] = $versionFieldColumnName . ' = CURRENT_TIMESTAMP';
             }
         }
     }
     $params = array_merge(array_values($data), array_values($where));
     $sql = 'UPDATE ' . $tableName . ' SET ' . implode(', ', $set) . ' WHERE ' . implode(' = ? AND ', array_keys($where)) . ' = ?';
     $result = $this->_conn->executeUpdate($sql, $params);
     if ($isVersioned && !$result) {
         throw \Doctrine\ORM\OptimisticLockException::lockFailed();
     }
 }

 /**
  * {@inheritdoc}
  */
 public function gc($lifetime)
 {
     $now = time();
     $inactive = $now - (int) (System::getVar('secinactivemins') * 60);
     $daysold = $now - (int) (System::getVar('secmeddays') * 86400);
     $inactive = date('Y-m-d H:i:s', $inactive);
     $daysold = date('Y-m-d H:i:s', $daysold);
     switch (System::getVar('seclevel')) {
         case 'Low':
             // Low security - delete session info if user decided not to
             //                remember themself and inactivity timeout
             $where = "WHERE remember = 0\n                          AND lastused < '{$inactive}'";
             break;
         case 'Medium':
             // Medium security - delete session info if session cookie has
             // expired or user decided not to remember themself and inactivity timeout
             // OR max number of days have elapsed without logging back in
             $where = "WHERE (remember = 0\n                          AND lastused < '{$inactive}')\n                          OR (lastused < '{$daysold}')\n                          OR (uid = 0 AND lastused < '{$inactive}')";
             break;
         case 'High':
         default:
             // High security - delete session info if user is inactive
             $where = "WHERE lastused < '{$inactive}'";
             break;
     }
     try {
         $res = $this->conn->executeUpdate('DELETE FROM session_info ' . $where);
     } catch (\Exception $e) {
         // silently fail
         $res = false;
     }
     return (bool) $res;
 }

 /**
  * @param ClassMetadata $class
  * @param array $entityData
  * @param string $revType
  */
 private function saveRevisionEntityData($class, $entityData, $revType)
 {
     $params = array($this->getRevisionId(), $revType);
     $types = array(\PDO::PARAM_INT, \PDO::PARAM_STR);
     $fields = array();
     foreach ($class->associationMappings as $field => $assoc) {
         if (($assoc['type'] & ClassMetadata::TO_ONE) > 0 && $assoc['isOwningSide']) {
             $targetClass = $this->em->getClassMetadata($assoc['targetEntity']);
             if ($entityData[$field] !== null) {
                 $relatedId = $this->uow->getEntityIdentifier($entityData[$field]);
             }
             $targetClass = $this->em->getClassMetadata($assoc['targetEntity']);
             foreach ($assoc['sourceToTargetKeyColumns'] as $sourceColumn => $targetColumn) {
                 $fields[$sourceColumn] = true;
                 if ($entityData[$field] === null) {
                     $params[] = null;
                     $types[] = \PDO::PARAM_STR;
                 } else {
                     $params[] = $relatedId[$targetClass->fieldNames[$targetColumn]];
                     $types[] = $targetClass->getTypeOfColumn($targetColumn);
                 }
             }
         }
     }
     foreach ($class->fieldNames as $field) {
         if (array_key_exists($field, $fields)) {
             continue;
         }
         $params[] = $entityData[$field];
         $types[] = $class->fieldMappings[$field]['type'];
     }
     $this->conn->executeUpdate($this->getInsertRevisionSQL($class), $params, $types);
 }

 /**
  * Execute a query.
  *
  * If a query is one of DESCRIBE, SELECT, or SHOW
  * then use  <code>Connection::executeQuery</code>
  * otherwise pass it off to <code>Connection::executeUpdate</code>
  *
  * @param  string $sql       sql query string
  * @param  int    $limit     limit the number of results
  * @param  bool   $useCache  cache the query
  * @param  int    $cacheTime how long to cache the query for (in seconds)
  * @return object QueryFactoryResult
  */
 public function Execute($sql, $limit = null, $useCache = false, $cacheTime = 0)
 {
     $sql = trim($sql);
     $commandType = strtolower(substr($sql, 0, 3));
     if (!in_array($commandType, array('des', 'sel', 'sho'))) {
         return $this->conn->executeUpdate($sql);
     }
     if ($limit) {
         $sql .= ' LIMIT ' . (int) $limit;
     }
     $qcp = null;
     $resultClass = __NAMESPACE__ . '\\QueryFactoryResult';
     if ($this->hasResultCache) {
         $qcp = new QueryCacheProfile($cacheTime, self::CACHE_KEY);
         $resultClass = __NAMESPACE__ . '\\CachedQueryFactoryResult';
     }
     if ($useCache && $this->hasResultCache) {
         // @todo ArrayCache shouldn't count as a real cache for $useCache
         $stmt = $this->conn->executeCacheQuery($sql, array(), array(), $qcp);
     } else {
         $stmt = $this->conn->executeQuery($sql, array(), array(), $qcp);
     }
     $obj = new $resultClass($stmt);
     $obj->MoveNext();
     return $obj;
 }

    /**
     * {@inheritDoc}
     */
    public function reorderChildren(Node $node)
    {
        $this->assertLoggedIn();

        $values[':absPath'] = $node->getPath();
        $sql = "UPDATE phpcr_nodes SET sort_order = CASE CONCAT(
          namespace,
          (CASE namespace WHEN '' THEN '' ELSE ':' END),
          local_name
        )";

        $i = 0;

        foreach ($node->getNodeNames() as $name) {
            $values[':name' . $i] = $name;
            $values[':order' . $i] = $i; // use our counter to avoid gaps
            $sql .= " WHEN :name" . $i . " THEN :order" . $i;
            $i++;
        }

        $sql .= " ELSE sort_order END WHERE parent = :absPath";

        try {
            $this->conn->executeUpdate($sql, $values);
        } catch (DBALException $e) {
            throw new RepositoryException('Unexpected exception while reordering nodes', $e->getCode(), $e);
        }

        return true;
    }

 /**
  * Execute changes
  */
 public function executeChanges()
 {
     $platform = $this->sm->getDatabasePlatform();
     $sql = [];
     if (count($this->changedIndexes)) {
         foreach ($this->changedIndexes as $index) {
             $sql[] = $platform->getDropIndexSQL($index);
             $sql[] = $platform->getCreateIndexSQL($index, $this->table);
         }
     }
     if (count($this->dropIndexes)) {
         foreach ($this->dropIndexes as $index) {
             $sql[] = $platform->getDropIndexSQL($index);
         }
     }
     if (count($this->addedIndexes)) {
         foreach ($this->addedIndexes as $index) {
             $sql[] = $platform->getCreateIndexSQL($index, $this->table);
         }
     }
     if (count($sql)) {
         foreach ($sql as $query) {
             $this->db->executeUpdate($query);
         }
         $this->changedIndexes = [];
         $this->dropIndexes = [];
         $this->addedIndexes = [];
     }
 }

 /**
  * Update Website SQL entry according to the BackBee installation.
  *
  * @param     array    Website configuration, must contains 'domain' and 'label' keys
  * @return    array|InvalidArgumentException  Returns domain and label set up in database or an Exception
  */
 public function updateWebsite($configuration)
 {
     if (!is_array($configuration) || !isset($configuration['domain']) || !isset($configuration['label'])) {
         throw new \InvalidArgumentException('array expected with domain and label keyes');
     }
     $domain = $configuration['domain'];
     $label = $configuration['label'];
     try {
         $this->connection->beginTransaction();
         $stmt = $this->connection->executeUpdate('UPDATE site SET server_name = ? , label = ?', array($domain, $label));
         $this->connection->commit();
     } catch (\PDOException $e) {
         $this->connection->rollback();
         throw new \RuntimeException($e->getMessage(), (int) $e->getCode(), $e);
     }
     return $configuration;
 }

 protected function tearDownCommonEntities()
 {
     $this->conn->executeUpdate('DELETE FROM GeometryEntity');
     $this->conn->executeUpdate('DELETE FROM PointEntity');
     $this->conn->executeUpdate('DELETE FROM LineStringEntity');
     $this->conn->executeUpdate('DELETE FROM PolygonEntity');
     $this->conn->executeUpdate('DELETE FROM GeographyEntity');
 }