PHP PDO::exec方法代码示例

 public function setUp()
 {
     date_default_timezone_set('UTC');
     if (DB_DRIVER === 'mysql') {
         $pdo = new PDO('mysql:host=' . DB_HOSTNAME, DB_USERNAME, DB_PASSWORD);
         $pdo->exec('DROP DATABASE ' . DB_NAME);
         $pdo->exec('CREATE DATABASE ' . DB_NAME);
         $pdo = null;
     } elseif (DB_DRIVER === 'postgres') {
         $pdo = new PDO('pgsql:host=' . DB_HOSTNAME, DB_USERNAME, DB_PASSWORD);
         $pdo->exec('DROP DATABASE ' . DB_NAME);
         $pdo->exec('CREATE DATABASE ' . DB_NAME . ' WITH OWNER ' . DB_USERNAME);
         $pdo = null;
     }
     $this->container = new Pimple\Container();
     $this->container->register(new Kanboard\ServiceProvider\DatabaseProvider());
     $this->container->register(new Kanboard\ServiceProvider\ClassProvider());
     $this->container['dispatcher'] = new TraceableEventDispatcher(new EventDispatcher(), new Stopwatch());
     $this->container['db']->logQueries = true;
     $this->container['logger'] = new Logger();
     $this->container['logger']->setLogger(new File('/dev/null'));
     $this->container['httpClient'] = new FakeHttpClient();
     $this->container['emailClient'] = $this->getMockBuilder('EmailClient')->setMethods(array('send'))->getMock();
     $this->container['userNotificationType'] = $this->getMockBuilder('\\Kanboard\\Model\\UserNotificationType')->setConstructorArgs(array($this->container))->setMethods(array('getType'))->getMock();
 }

    public function initTables()
    {
        try {
            $this->con->exec('DROP TABLE [book]');
            $this->con->exec('DROP TABLE [author]');
        } catch (PDOException $e) {
            // do nothing - the tables probably don't exist yet
        }
        $this->con->exec('CREATE TABLE [book]
		(
			[id] INTEGER  NOT NULL PRIMARY KEY,
			[title] VARCHAR(255)  NOT NULL,
			[isbn] VARCHAR(24)  NOT NULL,
			[price] FLOAT,
			[author_id] INTEGER,
			FOREIGN KEY (author_id) REFERENCES author(id)
		)');
        $this->con->exec('CREATE TABLE [author]
		(
			[id] INTEGER  NOT NULL PRIMARY KEY,
			[first_name] VARCHAR(128)  NOT NULL,
			[last_name] VARCHAR(128)  NOT NULL,
			[email] VARCHAR(128)
		)');
    }

 /**
  * Create a database connection.
  *
  * @return PDO  The database connection.
  */
 private function connect()
 {
     try {
         $db = new PDO($this->dsn, $this->username, $this->password);
     } catch (PDOException $e) {
         throw new Exception('Failed to connect to \'' . $this->dsn . '\': ' . $e->getMessage());
     }
     $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     /* Ensure that we are operating with UTF-8 encoding.
      * This command is for MySQL. Other databases may need different commands.
      */
     $driver = explode(':', $this->dsn, 2);
     $driver = strtolower($driver[0]);
     /* Driver specific initialization. */
     switch ($driver) {
         case 'mysql':
             /* Use UTF-8. */
             $db->exec("SET NAMES 'utf8'");
             break;
         case 'pgsql':
             /* Use UTF-8. */
             $db->exec("SET NAMES 'UTF8'");
             break;
     }
     return $db;
 }

function formthrottle_too_many_submissions($ip)
{
    $tooManySubmissions = false;
    try {
        if (in_array("sqlite", PDO::getAvailableDrivers(), TRUE)) {
            $db = new PDO('sqlite:muse-throttle-db.sqlite3');
        } else {
            if (function_exists("sqlite_open")) {
                $db = new PDO('sqlite2:muse-throttle-db');
            }
        }
    } catch (PDOException $Exception) {
        return $tooManySubmissions;
    }
    if ($db) {
        $res = $db->query("SELECT 1 FROM sqlite_master WHERE type='table' AND name='Submission_History';");
        if (!$res or $res->fetchColumn() == 0) {
            $db->exec("CREATE TABLE Submission_History (IP VARCHAR(39), Submission_Date TIMESTAMP)");
        }
        $db->exec("DELETE FROM Submission_History WHERE Submission_Date < DATETIME('now','-2 hours')");
        $stmt = $db->prepare("INSERT INTO Submission_History (IP,Submission_Date) VALUES (:ip, DATETIME('now'))");
        $stmt->bindParam(':ip', $ip);
        $stmt->execute();
        $stmt->closeCursor();
        $stmt = $db->prepare("SELECT COUNT(1) FROM Submission_History WHERE IP = :ip;");
        $stmt->bindParam(':ip', $ip);
        $stmt->execute();
        if ($stmt->fetchColumn() > 25) {
            $tooManySubmissions = true;
        }
        // Close file db connection
        $db = null;
    }
    return $tooManySubmissions;
}

    /**
     * Connect to local sqlite db
     *
     * @param string $locking_mode        	
     * @return \PDO
     */
    private function cache_local_connect($locking_mode = 'NORMAL')
    {
        try {
            $table = <<<EOD
CREATE TABLE "cachedb" (
"itemid"  INTEGER PRIMARY KEY AUTOINCREMENT,
"title"  TEXT,
"link"  TEXT,
"stemmed"  TEXT,
"metaphone"  TEXT,
"ts"  DATETIME DEFAULT (datetime( 'now', 'utc'))
);
EOD;
            $cachelocal = new \PDO('sqlite::memory:');
            $cachelocal->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
            $cachelocal->setAttribute(\PDO::ATTR_TIMEOUT, 20);
            $cachelocal->exec("PRAGMA locking_mode = {$locking_mode}");
            $cachelocal->exec($table);
            // Create custom function for similar titles
            $cachelocal->sqliteCreateFunction('similarity', array('Rss\\Util\\Cache\\FeedCache\\FeedCache', 'SIMILARITY'), 2);
            // Create custom function for all terms in a title included in a published one.
            $cachelocal->sqliteCreateFunction('allterms', array('Rss\\Util\\Cache\\FeedCache\\FeedCache', 'ALLTERMS'), 2);
            return $cachelocal;
        } catch (\Exception $e) {
            $m = 'Error connecting to sqlite ' . $e->getCode() . ' ' . $e->getMessage();
            $this->fail($m);
        }
    }

    /**
     * Update statistics
     * @return mixed false on failure, number of affected rows otherwise
     */
    public function run()
    {
        $whereAccount = null !== $this->accountID ? 'WHERE `eqp`.`accountid` = ' . $this->accountID : '';
        $result = $this->PDO->exec('UPDATE `' . PREFIX . 'equipment`
			CROSS JOIN(
				SELECT
					`eqp`.`id` AS `eqpid`,
					SUM(`tr`.`distance`) AS `km`,
					SUM(`tr`.`s`) AS `s` 
				FROM `' . PREFIX . 'equipment` AS `eqp` 
				LEFT JOIN `' . PREFIX . 'activity_equipment` AS `aeqp` ON `eqp`.`id` = `aeqp`.`equipmentid` 
				LEFT JOIN `' . PREFIX . 'training` AS `tr` ON `aeqp`.`activityid` = `tr`.`id`
				' . $whereAccount . '
				GROUP BY `eqp`.`id`
			) AS `new`
			SET
				`distance` = IFNULL(`new`.`km`, 0),
				`time` = IFNULL(`new`.`s`, 0)
			WHERE `id` = `new`.`eqpid`');
        if ($result !== false) {
            $Factory = new \Runalyze\Model\Factory($this->accountID);
            $Factory->clearCache('equipment');
        }
        return $result;
    }

 function pdoConnect($dbs = null)
 {
     if ($dbs) {
         $dbname = $this->_strDBSName;
         $dbuser = $this->_strDBSUser;
         $dbpass = $this->_strDBSPassword;
     } else {
         $dbname = $this->_strDBName;
         $dbuser = $this->_strDBUser;
         $dbpass = $this->_strDBPassword;
     }
     try {
         ini_set('max_execution_time', 300);
         $dsn = "mysql:host=" . $this->_strDBHost . ";dbname=" . $dbname;
         $db = new PDO($dsn, "{$dbuser}", "{$dbpass}");
         $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
         $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
         $db->exec("SET names utf8");
         $db->exec("SET time_zone = 'your timezone'");
     } catch (PDOException $e) {
         print "Error!: " . $e->getMessage() . "<br/>";
         die;
     }
     return $db;
 }

 /**
  * Changes current database
  * @param  string  $database database name
  * @return boolean true on success, false - otherwise
  */
 public function changeDb($database)
 {
     if ($this->conn->exec('USE ' . $database) !== false) {
         return true;
     }
     return false;
 }

 public function testEmptySpecificKeys()
 {
     $this->PDO->exec('INSERT INTO `' . PREFIX . 'table` (`key`, `foo`, `bar`) VALUES ("1", "", "")');
     $Updater = new UpdaterForObject_MockTester($this->PDO);
     $Updater->update(new UpdaterObject_MockTester(array('foo' => 'test', 'bar' => 'test')), array());
     $this->assertEquals(array('key' => '1', 'foo' => '', 'bar' => ''), $this->PDO->query('SELECT `key`, `foo`, `bar` FROM `' . PREFIX . 'table`')->fetch(PDO::FETCH_ASSOC));
 }

    /**
     * Create the database tables
     *
     * @param  PDO $pdo
     */
    private function createSchema($pdo)
    {
        // create author table
        $pdo->exec(<<<SQL
CREATE TABLE IF NOT EXISTS author (
  id VARCHAR(36) NOT NULL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  biography TEXT
);
SQL
);
        // create book table
        $pdo->exec(<<<SQL
CREATE TABLE IF NOT EXISTS book (
  id VARCHAR(36) NOT NULL PRIMARY KEY,
  author_id VARCHAR(36) NOT NULL,
  title VARCHAR(100) NOT NULL,
  isbn VARCHAR(13),
  date_published DATE,

  FOREIGN KEY (author_id) REFERENCES author (id)
);
SQL
);
    }

function db_connect($url, $persistent = true)
{
    global $db_conn, $db_scheme;
    $url = parse_url($url);
    $scheme = $url['scheme'];
    $host = urldecode($url['host']);
    if (isset($url['port'])) {
        $host = $host . ':' . $url['port'];
    }
    $user = urldecode($url['user']);
    $pass = isset($url['pass']) ? urldecode($url['pass']) : '';
    $path = urldecode($url['path']);
    if ($path[0] == '/') {
        $path = substr($path, 1);
    }
    $dsn = "{$scheme}:host={$host};dbname={$path}";
    $options = array(PDO::ATTR_PERSISTENT => $persistent ? true : false);
    try {
        $db_conn = new PDO($dsn, $user, $pass, $options);
        $db_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $db_conn->exec("SET NAMES 'utf8'");
        if ($scheme == 'mysql') {
            $db_conn->exec("SET SQL_MODE='ANSI_QUOTES'");
        }
        $db_scheme = $scheme;
    } catch (PDOException $e) {
        die($e->getMessage());
    }
    return $db_conn;
}

 public static function setUpBeforeClass()
 {
     if (DB_DRIVER === 'sqlite') {
         @unlink(DB_FILENAME);
         $pdo = new PDO('sqlite:' . DB_FILENAME);
     } else {
         if (DB_DRIVER === 'mysql') {
             $pdo = new PDO('mysql:host=' . DB_HOSTNAME, DB_USERNAME, DB_PASSWORD);
             $pdo->exec('DROP DATABASE ' . DB_NAME);
             $pdo->exec('CREATE DATABASE ' . DB_NAME);
             $pdo = new PDO('mysql:host=' . DB_HOSTNAME . ';dbname=' . DB_NAME, DB_USERNAME, DB_PASSWORD);
         } else {
             if (DB_DRIVER === 'postgres') {
                 $pdo = new PDO('pgsql:host=' . DB_HOSTNAME, DB_USERNAME, DB_PASSWORD);
                 $pdo->exec('DROP DATABASE ' . DB_NAME);
                 $pdo->exec('CREATE DATABASE ' . DB_NAME . ' WITH OWNER ' . DB_USERNAME);
                 $pdo = new PDO('pgsql:host=' . DB_HOSTNAME . ';dbname=' . DB_NAME, DB_USERNAME, DB_PASSWORD);
             }
         }
     }
     $service = new ServiceProvider\Database();
     $service->getInstance();
     $pdo->exec("UPDATE settings SET value='" . API_KEY . "' WHERE option='api_token'");
     $pdo->exec("UPDATE settings SET value='Europe/Paris' WHERE option='application_timezone'");
     $pdo = null;
 }

 /**
  * Get database connection object
  *
  * @throws \Ip\Exception\Db
  * @return \PDO
  */
 public function getConnection()
 {
     if ($this->pdoConnection) {
         return $this->pdoConnection;
     }
     $dbConfig = ipConfig()->get('db');
     ipConfig()->set('db', null);
     if (empty($dbConfig)) {
         throw new \Ip\Exception\Db("Can't connect to database. No connection config found or \\Ip\\Db::disconnect() has been used.");
     }
     try {
         if (array_key_exists('driver', $dbConfig) && $dbConfig['driver'] == 'sqlite') {
             $dsn = 'sqlite:' . $dbConfig['database'];
             $this->pdoConnection = new \PDO($dsn);
             $this->pdoConnection->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
         } else {
             $dsn = 'mysql:host=' . str_replace(':', ';port=', $dbConfig['hostname']);
             if (!empty($dbConfig['database'])) {
                 $dsn .= ';dbname=' . $dbConfig['database'];
             }
             $this->pdoConnection = new \PDO($dsn, $dbConfig['username'], $dbConfig['password']);
             $this->pdoConnection->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
             $dt = new \DateTime();
             $offset = $dt->format("P");
             $this->pdoConnection->exec("SET time_zone='{$offset}';");
             $this->pdoConnection->exec("SET CHARACTER SET " . $dbConfig['charset']);
         }
     } catch (\PDOException $e) {
         throw new \Ip\Exception\Db("Can't connect to database. Stack trace hidden for security reasons");
         //PHP traces all details of error including DB password. This could be a disaster on live server. So we hide that data.
     }
     $this->tablePrefix = $dbConfig['tablePrefix'];
     return $this->pdoConnection;
 }

 public function exec($query)
 {
     if (is_null($this->_pdo)) {
         return;
     }
     return $this->_pdo->exec($query);
 }

    /**
     * Sets up the fixture, for example, opens a network connection.
     * This method is called before a test is executed.
     * @group mysql
     */
    public static function setUpBeforeClass()
    {
        $database = MYSQL_DSN;
        $pdo = new \PDO(MYSQL_DSN, MYSQL_USER, MYSQL_PASSWORD);
        $pdo->exec('drop table if exists user');
        $pdo->exec('drop table if exists item');
        $create = <<<SQL
CREATE TABLE `user` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(45) DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
SQL;
        $pdo->exec($create);
        $create2 = <<<SQL
CREATE TABLE `item` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(45) DEFAULT NULL,
   `created_at` timestamp,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
SQL;
        $r = $pdo->exec($create2);
        $insert = <<<SQL
insert into user values(1, 'sato');
insert into user values(2, 'suzuki');
insert into user values(3, 'takahashi');
insert into user values(4, 'tanaka');
insert into user values(5, 'ito');
SQL;
        $pdo->exec($insert);
        $pdo = null;
    }