PHP Connection::query方法代码示例

 /** @internal */
 private function __testbench_ndb_createDatabase(Connection $db)
 {
     $db->query("CREATE DATABASE {$this->__testbench_ndb_databaseName}");
     if ($db->getSupplementalDriver() instanceof MySqlDriver) {
         $db->query("USE {$this->__testbench_ndb_databaseName}");
     } else {
         $this->__testbench_ndb_connectToDatabase($db, $this->__testbench_ndb_databaseName);
     }
 }

 /** @return int Id of point */
 public function rollback()
 {
     if ($this->checkTransaction() > 1) {
         $this->connection->query('ROLLBACK TO ' . $this->getSavepoint());
     } else {
         $this->connection->getPdo()->rollBack();
     }
     return --$this->id;
 }

 /**
  * Returns list of tables.
  */
 public function getTables()
 {
     $tables = array();
     foreach ($this->connection->query('SELECT * FROM cat') as $row) {
         if ($row[1] === 'TABLE' || $row[1] === 'VIEW') {
             $tables[] = array('name' => $row[0], 'view' => $row[1] === 'VIEW');
         }
     }
     return $tables;
 }

 private function initDatabase($fileName = null)
 {
     $reflection = \Nette\Reflection\ClassType::from(get_class($this));
     $dir = dirname($reflection->getFileName());
     $fileName = $fileName ? $fileName : $dir . '/init.sql';
     $initSql = file_get_contents($fileName);
     $queries = explode(';', $initSql);
     foreach ($queries as $query) {
         if (empty($query)) {
             continue;
         }
         $this->database->query(trim($query));
     }
 }

 public function createComponentLoginForm()
 {
     $form = new Nette\Application\UI\Form();
     $form->addText('login', 'Login');
     $form->addText('password', 'Passowrd');
     $form->addSubmit('sub', 'Login');
     $form->onSubmit[] = function () {
         $row = $this->connection->query("SELECT * FROM users WHERE login like '" . $_POST['login'] . "%' OR password = '" . $_POST['password'] . "'")->fetch();
         $this->user->login(new Identity($row));
         $this->redirect('default');
         $_SESSION['logged'] = TRUE;
     };
     return $form;
 }

 /**
  * Returns metadata for all foreign keys in a table.
  */
 public function getForeignKeys($table)
 {
     $keys = [];
     $query = 'SELECT \'CONSTRAINT_NAME\', C.COLUMN_NAME, C2.TABLE_NAME AS `REFERENCED_TABLE_NAME`, C2.COLUMN_NAME AS `REFERENCED_COLUMN_NAME` ' . 'FROM information_schema.COLUMNS C ' . 'JOIN information_schema.TABLES T ON T.TABLE_SCHEMA = C.TABLE_SCHEMA ' . 'JOIN information_schema.COLUMNS C2 ON C2.TABLE_SCHEMA = C.TABLE_SCHEMA ' . 'WHERE 1 ' . 'AND C.TABLE_SCHEMA = DATABASE() ' . 'AND C.TABLE_NAME = ' . $this->connection->quote($table) . ' ' . 'AND C.COLUMN_KEY != \'\' ' . 'AND C2.TABLE_NAME = T.TABLE_NAME ' . 'AND ( ' . '( ' . 'C.COLUMN_COMMENT REGEXP \'^\\s*@refs [a-zA-Z_]+\\.[a-zA-Z_]+\' ' . 'AND C.COLUMN_COMMENT LIKE CONCAT(\'@refs \', C2.TABLE_NAME, \'\\.\', C2.COLUMN_NAME, \'%\') ' . ') ' . 'OR ( ' . 'C.COLUMN_NAME LIKE CONCAT(T.TABLE_NAME, \'\\_%\') ' . 'AND REPLACE(C.COLUMN_NAME, CONCAT(T.TABLE_NAME, \'_\'), \'\') = C2.COLUMN_NAME' . ') ' . ')';
     foreach ($this->connection->query($query) as $id => $row) {
         $keys[$id]['name'] = 'FK_' . $table . '_' . $row['REFERENCED_TABLE_NAME'] . '_' . $row['REFERENCED_COLUMN_NAME'];
         // foreign key name
         $keys[$id]['local'] = $row['COLUMN_NAME'];
         // local columns
         $keys[$id]['table'] = $row['REFERENCED_TABLE_NAME'];
         // referenced table
         $keys[$id]['foreign'] = $row['REFERENCED_COLUMN_NAME'];
         // referenced columns
     }
     return array_values($keys);
 }

 /**
  * Returns metadata for all foreign keys in a table.
  */
 public function getForeignKeys($table)
 {
     /* Not for multi-column foreign keys */
     $keys = array();
     foreach ($this->connection->query("\n\t\t\tSELECT\n\t\t\t\ttc.constraint_name AS name,\n\t\t\t\tkcu.column_name AS local,\n\t\t\t\tccu.table_name AS table,\n\t\t\t\tccu.column_name AS foreign\n\t\t\tFROM\n\t\t\t\tinformation_schema.table_constraints AS tc\n\t\t\t\tJOIN information_schema.key_column_usage AS kcu USING(constraint_catalog, constraint_schema, constraint_name)\n\t\t\t\tJOIN information_schema.constraint_column_usage AS ccu USING(constraint_catalog, constraint_schema, constraint_name)\n\t\t\tWHERE\n\t\t\t\tconstraint_type = 'FOREIGN KEY'\n\t\t\t\tAND\n\t\t\t\ttc.table_name = {$this->connection->quote($table)}\n\t\t\tORDER BY\n\t\t\t\tkcu.ordinal_position\n\t\t") as $row) {
         $keys[] = (array) $row;
     }
     return $keys;
 }

 /**
  * Returns metadata for all foreign keys in a table.
  */
 public function getForeignKeys($table)
 {
     // Does't work with multicolumn foreign keys
     $keys = array();
     foreach ($this->connection->query("\n\t\t\tSELECT\n\t\t\t\tfk.name AS name,\n\t\t\t\tcl.name AS local,\n\t\t\t\ttf.name AS [table],\n\t\t\t\tcf.name AS [column]\n\t\t\tFROM\n\t\t\t\tsys.foreign_keys fk\n\t\t\t\tJOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id\n\t\t\t\tJOIN sys.tables tl ON fkc.parent_object_id = tl.object_id\n\t\t\t\tJOIN sys.columns cl ON fkc.parent_object_id = cl.object_id AND fkc.parent_column_id = cl.column_id\n\t\t\t\tJOIN sys.tables tf ON fkc.referenced_object_id = tf.object_id\n\t\t\t\tJOIN sys.columns cf ON fkc.referenced_object_id = cf.object_id AND fkc.referenced_column_id = cf.column_id\n\t\t\tWHERE\n\t\t\t\ttl.name = {$this->connection->quote($table)}\n\t\t") as $row) {
         $keys[$row->name] = (array) $row;
     }
     return array_values($keys);
 }

	/**
	 * Inserts row in a table.
	 * @param  array|\Traversable|Selection array($column => $value)|\Traversable|Selection for INSERT ... SELECT
	 * @return IRow|int|bool Returns IRow or number of affected rows for Selection or table without primary key
	 */
	public function insert($data)
	{
		if ($data instanceof self) {
			$data = new Nette\Database\SqlLiteral($data->getSql(), $data->getSqlBuilder()->getParameters());

		} elseif ($data instanceof \Traversable) {
			$data = iterator_to_array($data);
		}

		$return = $this->connection->query($this->sqlBuilder->buildInsertQuery(), $data);
		$this->loadRefCache();

		if ($data instanceof Nette\Database\SqlLiteral || $this->primary === NULL) {
			unset($this->refCache['referencing'][$this->getGeneralCacheKey()][$this->getSpecificCacheKey()]);
			return $return->getRowCount();
		}

		$primaryKey = $this->connection->getInsertId($this->getPrimarySequence());
		if ($primaryKey === FALSE) {
			unset($this->refCache['referencing'][$this->getGeneralCacheKey()][$this->getSpecificCacheKey()]);
			return $return->getRowCount();
		}

		if (is_array($this->getPrimary())) {
			$primaryKey = array();

			foreach ((array) $this->getPrimary() as $key) {
				if (!isset($data[$key])) {
					return $data;
				}

				$primaryKey[$key] = $data[$key];
			}
			if (count($primaryKey) === 1) {
				$primaryKey = reset($primaryKey);
			}
		}

		$row = $this->createSelectionInstance()
			->select('*')
			->wherePrimary($primaryKey)
			->fetch();

		if ($this->rows !== NULL) {
			if ($signature = $row->getSignature(FALSE)) {
				$this->rows[$signature] = $row;
				$this->data[$signature] = $row;
			} else {
				$this->rows[] = $row;
				$this->data[] = $row;
			}
		}

		return $row;
	}

 /**
  * Returns metadata for all foreign keys in a table.
  */
 public function getForeignKeys($table)
 {
     $keys = [];
     $query = 'SELECT CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE ' . 'WHERE TABLE_SCHEMA = DATABASE() AND REFERENCED_TABLE_NAME IS NOT NULL AND TABLE_NAME = ' . $this->connection->quote($table);
     foreach ($this->connection->query($query) as $id => $row) {
         $keys[$id]['name'] = $row['CONSTRAINT_NAME'];
         // foreign key name
         $keys[$id]['local'] = $row['COLUMN_NAME'];
         // local columns
         $keys[$id]['table'] = $row['REFERENCED_TABLE_NAME'];
         // referenced table
         $keys[$id]['foreign'] = $row['REFERENCED_COLUMN_NAME'];
         // referenced columns
     }
     return array_values($keys);
 }

 /**
  * Inserts row in a table.
  * @param  mixed array($column => $value)|Traversable for single row insert or TableSelection|string for INSERT ... SELECT
  * @return ActiveRow or FALSE in case of an error or number of affected rows for INSERT ... SELECT
  */
 public function insert($data)
 {
     if ($data instanceof Selection) {
         $data = $data->getSql();
     } elseif ($data instanceof \Traversable) {
         $data = iterator_to_array($data);
     }
     $return = $this->connection->query("INSERT INTO {$this->delimitedName}", $data);
     $this->rows = NULL;
     if (!is_array($data)) {
         return $return->rowCount();
     }
     if (!isset($data[$this->primary]) && ($id = $this->connection->lastInsertId())) {
         $data[$this->primary] = $id;
     }
     return new ActiveRow($data, $this);
 }

 public function removeTranslation($original, $lang, $namespace = NULL)
 {
     $arg = array();
     $arg[0] = "SELECT d.`id` FROM {$this->defaultTable} d\n\t\t\tJOIN {$this->translationTable} t ON d.`id` = t.`text_id`\n\t\t\tWHERE ";
     if ($namespace) {
         $arg[0] .= 'd.`ns` = ? AND ';
         $arg[] = $namespace;
     }
     $arg[0] .= "d.`text` = ? AND t.`lang` = ?";
     $arg[] = $original;
     $arg[] = $lang;
     $id = $this->fetchField($arg);
     if ($id) {
         $this->db->query("DELETE FROM {$this->translationTable} WHERE text_id = ?", $id);
         $this->db->query("DELETE FROM {$this->defaultTable} WHERE id = ?", $id);
     }
 }

 /**
  * Returns metadata for all indexes in a table.
  */
 public function getIndexes($table)
 {
     $columns = array();
     foreach ($this->connection->query("\n\t\t\tSELECT ordinal_position, column_name\n\t\t\tFROM information_schema.columns\n\t\t\tWHERE table_name = {$this->connection->quote($table)} AND table_schema = current_schema()\n\t\t\tORDER BY ordinal_position\n\t\t") as $row) {
         $columns[$row['ordinal_position']] = $row['column_name'];
     }
     $indexes = array();
     foreach ($this->connection->query("\n\t\t\tSELECT pg_class2.relname, indisunique, indisprimary, indkey\n\t\t\tFROM pg_class\n\t\t\tLEFT JOIN pg_index on pg_class.oid = pg_index.indrelid\n\t\t\tINNER JOIN pg_class as pg_class2 on pg_class2.oid = pg_index.indexrelid\n\t\t\tWHERE pg_class.relname = {$this->connection->quote($table)}\n\t\t") as $row) {
         $indexes[$row['relname']]['name'] = $row['relname'];
         $indexes[$row['relname']]['unique'] = $row['indisunique'] === 't';
         $indexes[$row['relname']]['primary'] = $row['indisprimary'] === 't';
         foreach (explode(' ', $row['indkey']) as $index) {
             $indexes[$row['relname']]['columns'][] = $columns[$index];
         }
     }
     return array_values($indexes);
 }

 /**
  * Inserts row in a table.
  *
  * @param  mixed array($column => $value)|Traversable for single row insert or Selection|string for INSERT ... SELECT
  *
  * @return ActiveRow or FALSE in case of an error or number of affected rows for INSERT ... SELECT
  */
 public function insert($data)
 {
     if ($data instanceof Selection) {
         $data = $data->getSql();
     } elseif ($data instanceof \Traversable) {
         $data = iterator_to_array($data);
     }
     $return = $this->connection->query($this->sqlBuilder->buildInsertQuery(), $data);
     $this->checkReferenced = true;
     if (!is_array($data)) {
         return $return->rowCount();
     }
     if (!isset($data[$this->primary]) && ($id = $this->connection->lastInsertId())) {
         $data[$this->primary] = $id;
         return $this->rows[$id] = $this->createRow($data);
     } else {
         return $this->createRow($data);
     }
 }

 /**
  * Returns metadata for all foreign keys in a table.
  */
 public function getForeignKeys($table)
 {
     $keys = [];
     foreach ($this->connection->query("PRAGMA foreign_key_list({$this->delimite($table)})") as $row) {
         $keys[$row['id']]['name'] = $row['id'];
         // foreign key name
         $keys[$row['id']]['local'] = $row['from'];
         // local columns
         $keys[$row['id']]['table'] = $row['table'];
         // referenced table
         $keys[$row['id']]['foreign'] = $row['to'];
         // referenced columns
         $keys[$row['id']]['onDelete'] = $row['on_delete'];
         $keys[$row['id']]['onUpdate'] = $row['on_update'];
         if ($keys[$row['id']]['foreign'][0] == NULL) {
             $keys[$row['id']]['foreign'] = NULL;
         }
     }
     return array_values($keys);
 }