PHP Database::exec方法代码示例

 protected function tearDown()
 {
     try {
         $this->db->exec('DROP TABLE IF EXISTS test');
     } catch (Exception $e) {
         $this->fail($e->getMessage());
     }
 }

 /**
  * Drop ThinkUp tables
  *
  * @TODO: Use PDO instead of deprecated Database class.
  * @param Database $db
  */
 public function drop($db)
 {
     global $TEST_DATABASE;
     //Delete test data by dropping all existing tables
     $q = "SHOW TABLES FROM " . $TEST_DATABASE;
     $result = $db->exec($q);
     while ($row = mysql_fetch_assoc($result)) {
         $q = "DROP TABLE " . $row['Tables_in_' . $TEST_DATABASE];
         $db->exec($q);
     }
 }

 public static function insertTables(Smarty $smarty)
 {
     if (ConfigurationChecks::getResult() != CheckResult::OK) {
         header('Location: index.php');
         exit;
     }
     $sql = file_get_contents(SYSTEM_ROOT . '/install/install.sql');
     try {
         $db = new Database('mysql:dbname=' . DATABASE_NAME . ';host=' . DATABASE_HOST, DATABASE_USER, DATABASE_PASS);
         $sql = $db->prepare('SHOW TABLES');
         $sql->execute();
         if ($sql->rowCount() == 0 || Utils::getGET('confirm', false) != false) {
             $insert = file_get_contents('./install.sql');
             $db->exec($insert);
             header('Location: index.php?action=user');
             exit;
         } else {
             $smarty->assign('heading', 'Database tables');
             $smarty->assign('curStep', 2);
             $smarty->display('confirm.tpl');
         }
     } catch (PDOException $e) {
         $smarty->assign('heading', 'Database tables');
         $smarty->assign('error', $e->getMessage());
         $smarty->assign('url', 'index.php?action=tables&confirm=yes');
         $smarty->assign('curStep', 2);
         $smarty->display('error.tpl');
     }
 }

 /**
  * Set up the database tables, views, etc.
  *
  */
 protected function finalDatabaseSetup()
 {
     $this->db = $this->finalDatabasePrimary();
     // Let's iterate through the SQL files and run them all
     $driver = $this->db->getDriver();
     $files = \Airship\list_all_files(ROOT . '/Installer/sql/' . $driver, 'sql');
     \sort($files);
     foreach ($files as $file) {
         $query = \file_get_contents($file);
         try {
             $this->db->exec($query);
         } catch (\PDOException $e) {
             var_dump($e->getMessage());
             var_dump($query);
             exit(1);
         }
     }
     switch ($driver) {
         case 'pgsql':
             $this->databaseFinalPgsql();
             break;
         default:
             die('Unsupported primary database driver');
     }
 }

 public function __construct(API $base, Database $database, $key_prefix)
 {
     parent::__construct($key_prefix, $base);
     $this->database = $database;
     if ($database instanceof MySQL) {
         $sql = 'CREATE TABLE IF NOT EXISTS spindash_cache (item_key VARCHAR(255) NOT NULL, item_expires INT(11) UNSIGNED NOT NULL, item_value LONGTEXT NOT NULL, UNIQUE KEY (item_key)) ENGINE = InnoDB DEFAULT CHARSET=UTF8';
         $database->exec($sql);
     }
     if ($database instanceof SQLite) {
         $sql = 'CREATE TABLE IF NOT EXISTS spindash_cache (item_key TEXT, item_expires INTEGER, item_value TEXT, UNIQUE (item_key))';
         $database->exec($sql);
     }
     if ($database instanceof PostgreSQL) {
         $sql = 'CREATE TABLE IF NOT EXISTS spindash_cache (item_key VARCHAR(255) NOT NULL, item_expires INTEGER NOT NULL, item_value TEXT NOT NULL, UNIQUE (item_key))';
         $database->exec($sql);
     }
 }

 public function install()
 {
     Database::exec("\n\t\tCREATE TABLE IF NOT EXISTS `users`\n\t\t(\n\t\t\t`uni_id`\t\t\t\tint(10)\t\t\tunsigned\tNOT NULL\tDEFAULT '0',\n\t\t\t\n\t\t\t`role`\t\t\t\t\tvarchar(12)\t\t\t\t\tNOT NULL\tDEFAULT '',\n\t\t\t`flair`\t\t\t\t\tvarchar(22)\t\t\t\t\tNOT NULL\tDEFAULT '',\n\t\t\t`clearance`\t\t\t\ttinyint(1)\t\t\t\t\tNOT NULL\tDEFAULT '0',\n\t\t\t\n\t\t\t`handle`\t\t\t\tvarchar(22)\t\t\t\t\tNOT NULL\tDEFAULT '',\n\t\t\t`display_name`\t\t\tvarchar(22)\t\t\t\t\tNOT NULL\tDEFAULT '',\n\t\t\t\n\t\t\t`timezone`\t\t\t\tvarchar(22)\t\t\t\t\tNOT NULL\tDEFAULT '',\n\t\t\t\n\t\t\t`date_joined`\t\t\tint(10)\t\t\tunsigned\tNOT NULL\tDEFAULT '0',\n\t\t\t`date_lastLogin`\t\tint(10)\t\t\tunsigned\tNOT NULL\tDEFAULT '0',\n\t\t\t\n\t\t\t`auth_token`\t\t\tvarchar(22)\t\t\t\t\tNOT NULL\tDEFAULT '',\n\t\t\t\n\t\t\tUNIQUE (`uni_id`)\n\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY KEY(uni_id) PARTITIONS 7;\n\t\t");
     // Vertical partitioning of the user's table. This creates duplicate content, but will greatly speed up
     // lookup times for handles. There is overhead for two tables, but its a negligible impact compared to the
     // horizontal partitioning we gain on the user's table.
     Database::exec("\n\t\tCREATE TABLE IF NOT EXISTS `users_handles`\n\t\t(\n\t\t\t`handle`\t\t\t\tvarchar(22)\t\t\t\t\tNOT NULL\tDEFAULT '',\n\t\t\t`uni_id`\t\t\t\tint(10)\t\t\tunsigned\tNOT NULL\tDEFAULT '0',\n\t\t\t\n\t\t\tUNIQUE (`handle`, `uni_id`)\n\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8\n\t\t\n\t\tPARTITION BY RANGE COLUMNS(handle) (\n\t\t\tPARTITION p0 VALUES LESS THAN ('a'),\n\t\t\tPARTITION p1 VALUES LESS THAN ('e'),\n\t\t\tPARTITION p2 VALUES LESS THAN ('i'),\n\t\t\tPARTITION p3 VALUES LESS THAN ('m'),\n\t\t\tPARTITION p4 VALUES LESS THAN ('q'),\n\t\t\tPARTITION p5 VALUES LESS THAN ('u'),\n\t\t\tPARTITION p6 VALUES LESS THAN MAXVALUE\n\t\t);\n\t\t");
     return $this->isInstalled();
 }

 function testExecutingSQLWithUnSetTablePrefixShouldFail()
 {
     global $TWITALYTIC_CFG;
     $TWITALYTIC_CFG['table_prefix'] = 'tw_';
     $this->expectException();
     $db = new Database($TWITALYTIC_CFG);
     $conn = $db->getConnection();
     $sql_result = $db->exec("SELECT \n\t\t\t\tuser_id \n\t\t\tFROM \n\t\t\t\t%prefix%users \n\t\t\tWHERE \n\t\t\t\tuser_id = 930061");
     $db->closeConnection($conn);
 }

function uploadData($name, $username, $password, $email)
{
    $db = new Database();
    $db->exec("CREATE TABLE IF NOT EXISTS recipes (username TEXT PRIMARY KEY, password TEXT, email TEXT, name TEXT)");
    $stmt = $db->prepare("INSERT INTO recipes VALUES (:us, :ps, :em, :n)");
    $stmt->bindValue(':us', $username);
    $stmt->bindValue(':ps', $password);
    $stmt->bindValue(':em', $email);
    $stmt->bindValue(':n', $name);
    $stmt->execute();
}

 public function archiveTodo($item_id)
 {
     $db_link = new Database();
     $sql = 'SELECT todo_archived FROM todos WHERE todo_id = "' . (int) $item_id . '"';
     $toggle = 1;
     if ($db_link->query($sql)->fetch()->todo_archived == 1) {
         $toggle = 0;
     }
     $sql = 'UPDATE todos SET todo_archived = ' . $toggle . ' WHERE todo_id = "' . (int) $item_id . '"';
     $db_link->exec($sql);
 }

 public function archiveProject($id)
 {
     $db_link = new Database();
     $sql = 'SELECT project_archived FROM projects WHERE project_id = "' . (int) $id . '"';
     $toggle = 1;
     if ($db_link->query($sql)->fetch()->project_archived == 1) {
         $toggle = 0;
     }
     $sql = 'UPDATE projects SET project_archived = ' . $toggle . ' WHERE project_id = "' . (int) $id . '"';
     $db_link->exec($sql);
 }

 public function exec($sql, array $params = null)
 {
     try {
         return parent::exec($sql, $params);
     } catch (PDOException $e) {
         $info = $this->errorInfo();
         switch ($info[1]) {
             case 8:
                 throw new ReadOnlyDatabaseException();
             default:
                 throw $e;
         }
     }
 }

 function testExecutingSQLWithUnSetTablePrefixShouldFail()
 {
     global $THINKTANK_CFG;
     $THINKTANK_TEST_CFG['table_prefix'] = 'tw_';
     $THINKTANK_TEST_CFG['db_password'] = $THINKTANK_CFG['db_password'];
     $THINKTANK_TEST_CFG['db_host'] = $THINKTANK_CFG['db_host'];
     $THINKTANK_TEST_CFG['db_name'] = $THINKTANK_CFG['db_name'];
     $THINKTANK_TEST_CFG['db_user'] = $THINKTANK_CFG['db_user'];
     $this->expectException();
     $db = new Database($THINKTANK_TEST_CFG);
     $conn = $db->getConnection();
     $sql_result = $db->exec("SELECT\n\t\t\t\tuser_id \n\t\t\tFROM \n\t\t\t\t%prefix%users \n\t\t\tWHERE \n\t\t\t\tuser_id = 930061");
     $db->closeConnection($conn);
 }

 public static function copy($sourceTable, $destinationTable, $sqlWhere = "", $sqlArray = array(), $limit = 1000, $move = false)
 {
     // Protect Tables
     if (!IsSanitized::variable($destinationTable) or !IsSanitized::variable($sourceTable)) {
         return false;
     }
     // Make sure the backup table exists
     Database::exec("CREATE TABLE IF NOT EXISTS " . $destinationTable . " LIKE " . $sourceTable);
     // Begin the Database_Transfer
     Database::startTransaction();
     // Insert Rows into Database_Transfer Table
     Database::query("INSERT INTO " . $destinationTable . " SELECT * FROM " . $sourceTable . ($sqlWhere != "" ? " WHERE " . Sanitize::variable($sqlWhere, " ,`!=<>?()") : "") . ($limit ? ' LIMIT ' . (int) $limit : ''), $sqlArray);
     $newCount = Database::$rowsAffected;
     if ($move === true) {
         // Delete Rows from Original Table (if applicable)
         Database::query("DELETE FROM " . $sourceTable . ($sqlWhere != "" ? " WHERE " . Sanitize::variable($sqlWhere, " ,`!=<>?()") : ""), $sqlArray);
         // If the number of inserts matches the number of deletions, commit the transaction
         return Database::endTransaction($newCount == Database::$rowsAffected);
     }
     return Database::endTransaction();
 }

 /**
  * atualiza os dados no BD
  *
  * @param Pessoa $pessoa
  * @return int pessoa id
  */
 public function atualizar(Pessoa $pessoa)
 {
     try {
         self::valida($pessoa, "atualizar");
         $this->values = $pessoa->getPessoaValores();
         $this->fields = array_keys($pessoa->getPessoaValores());
         $this->sql = "UPDATE pessoa SET\n\t\t\t\t\t  nome = ?\n\t\t\t\t\t, email=? \n\t\t\t\t\t, endereco=?\n\t\t\t\t\t, complemento=?\n\t\t\t\t\t, numero=?\n\t\t\t\t\t, cep=?\n\t\t\t\t\t, UF = ?\n\t\t\t\t\t, cidade = ?\n\t\t\t\t\t, bairro =?\n\t\t\t\t\t, idresiduo =?\n\t\t\t\t\t, limitecredito =?\n\t\t\t\t\t, inadimplente =?\n\t\t\t\t\t, idempresa =?\n                                        , idcontato = ?\n\t\t\t\t\tWHERE id=?";
         $this->values = array();
         $this->values = array($pessoa->nome, $pessoa->email, $pessoa->endereco, $pessoa->complemento, $pessoa->numero, $pessoa->cep, $pessoa->UF, $pessoa->cidade, $pessoa->bairro, $pessoa->idresiduo, $pessoa->limitecredito, $pessoa->inadimplente, $pessoa->idempresa, $pessoa->idcontato, $pessoa->id);
         parent::exec();
         $telefoneDAO = new TelefoneDAO();
         $telefoneDAO->atualizarTelefones($pessoa->id, $pessoa->telefones);
         $telefoneDAO->apagarTelefones($pessoa->id, explode(",", $_POST['removerTels']));
         $enderecoDAO = new EnderecoDAO();
         $enderecoDAO->atualizarEnderecos($pessoa->id, $pessoa->enderecos);
     } catch (Exception $e) {
         $erro = new FormException();
         $erro->addError($e->getMessage());
         throw $erro;
     }
     return $pessoa->id;
 }

 public function clear()
 {
     Database::exec('TRUNCATE TABLE ' . $this->name . ';');
 }