function add_member($username, $password)
{
require 'password.php';
try {
define('DB_HOST', getenv('OPENSHIFT_MYSQL_DB_HOST'));
define('DB_PORT', getenv('OPENSHIFT_MYSQL_DB_PORT'));
define('DB_USER', getenv('OPENSHIFT_MYSQL_DB_USERNAME'));
define('DB_PASS', getenv('OPENSHIFT_MYSQL_DB_PASSWORD'));
define('DB_NAME', getenv('OPENSHIFT_GEAR_NAME'));
try {
$dsn = 'mysql:dbname=members;host=' . DB_HOST . ';port=' . DB_PORT;
$db = new PDO($dsn, DB_USER, DB_PASS);
} catch (PDOException $ex) {
echo 'Error!: ' . $ex->getMessage();
die;
}
$password = password_hash($password, PASSWORD_BCRYPT);
echo $password;
$query = $db->query("USE members");
$query = $db->query("INSERT INTO members (username, password) VALUES ('{$username}', '{$password}')");
header('Location: main_login.php');
return TRUE;
} catch (Exception $ex) {
return FALSE;
}
}
public function testEquipmentUpdate()
{
$this->delete($this->insert(array(Object::TIME_IN_SECONDS => 3600, Object::DISTANCE => 12, Object::SHOEID => 1)));
$this->delete($this->insert(array(Object::TIME_IN_SECONDS => 3600, Object::DISTANCE => 12, Object::SHOEID => 2)));
$this->assertEquals(array('km' => 10, 'time' => 3000), $this->PDO->query('SELECT `km`, `time` FROM `' . PREFIX . 'shoe` WHERE `id`=1 AND `accountid`=0')->fetch(PDO::FETCH_ASSOC));
$this->assertEquals(array('km' => 0, 'time' => 0), $this->PDO->query('SELECT `km`, `time` FROM `' . PREFIX . 'shoe` WHERE `id`=2 AND `accountid`=0')->fetch(PDO::FETCH_ASSOC));
}
function init_db($db_file_name)
{
global $db;
$need_init_db = !file_exists($db_file_name);
$dsn = 'sqlite:' . $db_file_name;
$db = new PDO($dsn);
if (!$db) {
die('failed to open sqlite.');
}
if ($need_init_db) {
$sql = <<<SQL
create table pages (
id integer primary key,
title varchar(255) unique,
body text,
created_at timestamp default current_timestamp,
updated_at timestamp default current_timestamp
)
SQL;
$result = $db->query($sql);
$sql = <<<SQL
insert into pages
(id, title, body)
values
(1, 'index', '...')
SQL;
$result = $db->query($sql);
}
}
/**
* Setting default values on the PDO instance like errormode, encoding and
* so on.
*/
public function setDefaultAttributes()
{
$this->_pdo->setAttribute(\PDO::ATTR_CASE, \PDO::CASE_NATURAL);
$this->_pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
$this->_pdo->setAttribute(\PDO::ATTR_ORACLE_NULLS, \PDO::NULL_TO_STRING);
$this->_pdo->query("SET NAMES utf8");
}
function optimizeDB($text)
{
global $pathToIndex;
// SETTING BEGIN
// Run the task every n days
$scheduleDays = 10;
// SETTING END
$loggixDB = $pathToIndex . Loggix_Core::LOGGIX_SQLITE_3;
$filename = $loggixDB . '.BAK';
if (!file_exists($filename) || filemtime($filename) + 86400 * $scheduleDays <= time()) {
if (copy($loggixDB, $filename)) {
chmod($filename, 0666);
$backupDB = 'sqlite:' . $filename;
$bdb = new PDO($backupDB);
// Garbage Collection (/admin/delete.php)
$maxLifeTime = get_cfg_var("session.gc_maxlifetime");
$expirationTime = time() - $maxLifeTime;
$sql = 'DELETE FROM ' . SESSION_TABLE . ' ' . 'WHERE ' . "sess_date < '" . $expirationTime . "'";
$bdb->query($sql);
// Vacuum DB
$bdb->query('VACUUM');
if (rename($loggixDB, $loggixDB . '.OLD')) {
copy($filename, $loggixDB);
chmod($loggixDB, 0666);
}
if (file_exists($loggixDB)) {
unlink($loggixDB . '.OLD');
}
}
}
return $text;
}
/**
* Вынимает данные пользователя из БД
* @param mixed $sourceUserId ИД пользователя или его логин
*/
public function pickUserData($sourceUserId)
{
// Подключаем базу - источник
$this->_debugMessage("Connecting to source database ...");
$dsn = "mysql:dbname={$this->_sourceDb['dbname']};";
$dsn .= "host={$this->_sourceDb['host']}";
$db = new PDO($dsn, $this->_sourceDb['user'], $this->_sourceDb['pass'], array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''));
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$result = $db->query("SELECT id FROM users\n WHERE id = '{$sourceUserId}' OR user_login = '******' LIMIT 1");
$sourceUserId = $result->fetchColumn();
$tablesSelected = array();
// Делаем выборку таблиц и их данных из массива
$this->_debugMessage("Selecting tables ...");
foreach ($this->_tablesTemplate as $table => $params) {
if (!isset($params['userId']) && !isset($params['selectCase'])) {
die('Can\'t select table without key or `where` statement!');
}
$sql = 'select * from `' . $table . '` where ';
$sql .= isset($params['selectCase']) ? str_ireplace('{$userId}', $sourceUserId, $params['selectCase']) : " `{$params['userId']}`={$sourceUserId};";
$result = $db->query($sql);
$tablesSelected[$table] = $result->fetchAll(PDO::FETCH_ASSOC);
$this->_debugMessage("`{$table}` selected;");
}
return $tablesSelected;
}
/**
* @inheritdoc
*/
public function initialize()
{
$createPhotoTable = <<<SQL
CREATE TABLE IF NOT EXISTS "photo" (
"uuid" TEXT NOT NULL PRIMARY KEY,
"resourceId" TEXT NOT NULL,
"name" TEXT NOT NULL,
"httpUrl" TEXT NOT NULL,
"position" INT NOT NULL,
"filePath" TEXT
)
SQL;
$this->pdo->query($createPhotoTable);
$createAlternativeTextTable = <<<SQL
CREATE TABLE IF NOT EXISTS "alternative_text" (
"photo_uuid" TEXT NOT NULL,
"alt" TEXT NOT NULL,
"lang" TEXT NOT NULL,
FOREIGN KEY(photo_uuid) REFERENCES photo(uuid)
)
SQL;
$this->pdo->query($createAlternativeTextTable);
$this->pdo->query("CREATE INDEX resource ON \"photo\" (resourceId);");
$this->pdo->query("CREATE INDEX fk_alt_photo_uuid ON \"alternative_text\" (photo_uuid);");
}
static function getSQLiteDB()
{
if (file_exists(SABRE_TEMPDIR . '/testdb.sqlite')) {
unlink(SABRE_TEMPDIR . '/testdb.sqlite');
}
$pdo = new PDO('sqlite:' . SABRE_TEMPDIR . '/testdb.sqlite');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->query('
CREATE TABLE calendarobjects (
id integer primary key asc,
calendardata text,
uri text,
calendarid integer,
lastmodified integer
);
');
$pdo->query('
CREATE TABLE calendars (
id integer primary key asc,
principaluri text,
displayname text,
uri text,
ctag integer,
description text,
calendarorder integer,
calendarcolor text,
timezone text,
components text
);');
$pdo->query('INSERT INTO calendars (principaluri,displayname,uri,description,calendarorder,calendarcolor,components)
VALUES ("principals/user1","user1 calendar","UUID-123467","Calendar description", "1", "#FF0000","VEVENT,VTODO");');
$stmt = $pdo->prepare('INSERT INTO calendarobjects (calendardata, uri, calendarid, lastmodified) VALUES (?, "UUID-2345", 1, DATETIME("NOW"))');
$stmt->execute(array(self::getTestCalendarData()));
return $pdo;
}
function getItems4Client()
{
$DB = new PDO('mysql:host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USERNAME, DB_PASSWORD);
$table = DB_TABLE;
//检查一下是否存在表
$sql = "SHOW TABLES LIKE '%{$table}%';";
$rc = $DB->query($sql)->rowCount();
if (!$rc) {
$sqlFile = file_get_contents('items.sql');
$DB->exec($sqlFile);
}
$sql = "SELECT * FROM `{$table}` LIMIT 0,10";
$query = $DB->query($sql);
$query->setFetchMode(PDO::FETCH_ASSOC);
$rt = $query->fetchAll();
if ($rt) {
$items = array();
foreach ($rt as $item) {
$tmp = array();
$tmp['openid'] = $item['tb_item_id'];
$tmp['iid'] = $item['tb_iid'];
$tmp['price'] = $item['price'] ? $item['price'] / 100 : $item['reserve_price'] / 100;
$tmp['name'] = $item['name'];
$tmp['pic'] = $item['pic'];
$tmp['itemType'] = $item['is_mall'] + 1;
// 客户端判断是 1:集市店,2:天猫店
$items[] = $tmp;
}
Ajax::go($items);
} else {
Ajax::error('数据查询失败.');
}
}
public function testLongWork()
{
// work_test
$pdo = new PDO_instance('sqlite::memory:', null, null, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
$pdo->query('CREATE TABLE IF NOT EXISTS `work_test` (
`key` varchar(128) DEFAULT NULL,
`value` varchar(256) DEFAULT NULL
);');
$pdo->query(" DELETE FROM `work_test`;");
// clear table data
for ($i = 1; $i <= 100; $i++) {
$pdo->query("INSERT INTO `work_test` (`key`, `value`) VALUES ('key" . $i . "', '" . mt_rand(0, 50) . "');");
}
$builder = new Builder($pdo);
$this->assertInstanceOf('\\YepSQL\\Builder', $builder);
#
$this->assertEquals(3230, count($builder->loadFromFile(__DIR__ . '/true_long.sql')));
$count = $pdo->query('SELECT count(*) FROM `work_test` WHERE `value` > 32;')->fetchColumn();
$re = $builder->workLongMore([':more' => 32])->fetchColumn();
print_r($re);
$this->assertEquals($count, $re);
#
$count = $pdo->query('SELECT count(*) FROM `work_test` WHERE `value` < 32;')->fetchColumn();
$re = $builder->workLongLess([':less' => 32])->fetchColumn();
print_r($re);
$this->assertEquals($count, $re);
#
}
public static function export(\PDO $database, $databaseName)
{
try {
$stmt = $database->query('SHOW TABLES');
$db = new Database($databaseName);
foreach ($stmt as $k => $v) {
$table = new Table();
$table->setName($v[0]);
$query = $database->query('SHOW COLUMNS FROM ' . $table->getName());
$columns = $query->fetchAll(\PDO::FETCH_ASSOC);
foreach ($columns as $c) {
$column = new Column();
$column->setName($c['Field']);
$column->setDefault($c['Default']);
$column->setExtra($c['Extra']);
$column->setKey($c['Key']);
$column->setNull($c['Null']);
$column->setType($c['Type']);
$table->addColumn($column);
}
$db->addTable($table);
}
$stmt->closeCursor();
} catch (\PDOException $e) {
echo "Error: " . $e->getMessage();
exit;
}
return $db;
}
/**
* Connects to the database without initializing contao
*
* @param OutputInterface $output = null
*
* @throws \Exception
* @return \PDO
*/
public function getConnection(OutputInterface $output = null)
{
if ($output == null) {
$output = new NullOutput();
}
if ($this->_connection) {
return $this->_connection;
}
$this->detectDbSettings($output);
if (!extension_loaded('pdo_mysql')) {
throw new \Exception('pdo_mysql extension is not installed');
}
if (strpos($this->dbSettings['host'], '/') !== false) {
$this->dbSettings['unix_socket'] = $this->dbSettings['host'];
unset($this->dbSettings['host']);
} else {
if (strpos($this->dbSettings['host'], ':') !== false) {
list($this->dbSettings['host'], $this->dbSettings['port']) = explode(':', $this->dbSettings['host']);
}
}
$this->_connection = new \PDO($this->dsn(), $this->dbSettings['username'], $this->dbSettings['password']);
/** @link http://bugs.mysql.com/bug.php?id=18551 */
$this->_connection->query("SET SQL_MODE=''");
try {
$this->_connection->query('USE `' . $this->dbSettings['dbname'] . '`');
} catch (\PDOException $e) {
}
$this->_connection->query("SET NAMES utf8");
$this->_connection->setAttribute(\PDO::ATTR_EMULATE_PREPARES, true);
$this->_connection->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
return $this->_connection;
}
/**
* Execute une requête SQL (mysql).
*
* @todo implémenter le template.
* @todo eviter de dupliquer les objets PDO, mais maintenir le renvoi d'une collection ouverte sur la BD
* @param string $sql
* @param string $template
* @throws PDOException
* @throws Afup_Directory_Exception
* @return integer|PDOStatement insert or select
*/
private function executeSql($sql, $template = null, $values = null)
{
if ($values) {
$stmt = $this->pdo->prepare($sql);
if ($stmt) {
if (isset($values[0]) && is_array($values[0])) {
foreach ($values as $value) {
$stmt->execute($value);
}
} else {
$stmt->execute($values);
}
}
} else {
$stmt = $this->pdo->query($sql, PDO::FETCH_ASSOC);
}
$queryType = strtolower(substr($sql, 0, 6));
switch ($queryType) {
case 'select':
return $stmt;
// break;
// break;
case 'insert':
case 'update':
case 'delete':
unset($stmt);
return $this->pdo->lastInsertId();
// break;
// break;
default:
throw new Afup_Directory_Exception("This request do not return values.");
break;
}
}
/**
* Drop test table, only on Travis CI
*/
public function tearDown()
{
if (!$this->isTravisCi()) {
return true;
}
$this->pdo->query("DROP TABLE hello");
}
function like($pid, $how)
{
// database connection
$dsn = "mysql:host=localhost;dbname=fundbook";
//$options = array(PDO::"MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');
$db = new PDO($dsn, "root", "fundbook");
$projects = $db->query("SELECT * FROM projects WHERE pid='{$pid}'");
foreach ($projects as $project) {
$author = $project["user"];
$goal = $project["goal"];
$category = $project["topic"];
$title = $project["name"];
$numBackers = $project["numBackers"];
$raisedAmt = $project["raisedAmt"];
}
if (isset($_COOKIE["email"])) {
$email = $_COOKIE["email"];
$interested = $db->query("SELECT * FROM topicInterests WHERE person='{$email}' and topic='{$category}'");
// then add to interest
foreach ($interested as $interest) {
if ($how == 'explicit') {
$data = array('action' => 'like', "msg" => "added already");
echo json_encode($data);
exit;
}
}
$db->query("INSERT INTO topicInterests VALUES ('{$email}', '{$category}')");
if ($how == 'explicit') {
$data = array('action' => 'like', 'msg' => 'added');
echo json_encode($data);
}
}
exit;
}
/**
* Override the template-fetching-function of the Parser
*
* @global string $IP
* @global string $wgTemplatePath
* @global string $wgTemplateExtension
* @global string $wgTemplatePrefix
* @param Title $title
* @return array
*/
function fetchTemplateAndTitle($title)
{
#echo "\n--- Trying to find offline template: $title ---\n";
global $wgTemplateDB, $wgTemplateFileID;
$finalTitle = $title;
$template_text = null;
# $$$ need to fix later for all languages
# We pad the title with '~' to force the database to import strings
$title_orig = '~' . $wgTemplateFileID . '~' . strtolower($title);
$db = new PDO('sqlite:' . $wgTemplateDB);
$tl = $db->quote($title_orig);
#echo "\n--- ($title_orig) --- \n";
$result = $db->query("SELECT body FROM templates WHERE title = {$tl} LIMIT 1");
$data = $result->fetchAll();
$max_loop_count = 25;
while ($max_loop_count && sizeof($data) == 0) {
$result = $db->query("SELECT redirect FROM redirects WHERE title = {$tl} LIMIT 1");
$data = $result->fetchAll();
if (sizeof($data) == 0) {
break;
}
$redirect = $db->quote($data[0]['redirect']);
$result = $db->query("SELECT body FROM templates WHERE title = {$redirect} LIMIT 1");
$data = $result->fetchAll();
--$max_loop_count;
}
if (sizeof($data) > 0) {
$template_text = substr($data[0]['body'], 1);
#echo "\n--- TT:($template_text):TT --- \n";
} else {
$template_text = '';
}
$ret = array($template_text, $finalTitle);
return $ret;
}
/**
* @param InputInterface $input
* @param OutputInterface $output
* @return bool|\PDO
*/
protected function validateDatabaseSettings(InputInterface $input, OutputInterface $output)
{
try {
$dsn = sprintf("mysql:host=%s;port=%s", $this->config->getString('db_host'), $this->config->getString('db_port'));
$db = new \PDO($dsn, $this->config->getString('db_user'), $this->config->getString('db_pass'));
$dbName = $this->config->getString('db_name');
if (!$db->query('USE `' . $dbName . '`')) {
$db->query("CREATE DATABASE `" . $dbName . "`");
$output->writeln('<info>Created database ' . $dbName . '</info>');
$db->query('USE `' . $dbName . '`');
// Check DB version
$statement = $db->query('SELECT VERSION()');
$mysqlVersion = $statement->fetchColumn(0);
if (version_compare($mysqlVersion, '5.6.0', '<')) {
throw new \Exception('MySQL Version must be >= 5.6.0');
}
return $db;
}
if ($input->getOption('noDownload') && !$input->getOption('forceUseDb')) {
$output->writeln("<error>Database {$this->config->getString('db_name')} already exists.</error>");
return false;
}
return $db;
} catch (\Exception $e) {
$output->writeln('<error>' . $e->getMessage() . '</error>');
} catch (\PDOException $e) {
$output->writeln('<error>' . $e->getMessage() . '</error>');
}
return false;
}
function getDatabase()
{
$dbobject = false;
global $BASE_DIR;
if (file_exists("{$BASE_DIR}/authserver/authd/gaasdata.sqlite")) {
try {
$dbobject = new PDO("sqlite:{$BASE_DIR}/authserver/authd/gaasdata.sqlite");
} catch (PDOException $exep) {
error_log("execpt on db open");
}
} else {
try {
$dbobject = new PDO("sqlite:{$BASE_DIR}/authserver/authd/gaasdata.sqlite");
} catch (PDOException $exep) {
error_log("execpt on db open");
}
$sql = 'CREATE TABLE "users" ("users_id" INTEGER PRIMARY KEY AUTOINCREMENT,"users_username" TEXT, "users_realname" TEXT, "users_password" TEXT, "users_tokendata" TEXT, "users_otk" TEXT);';
$dbobject->query($sql);
$sql = 'CREATE TABLE "radclients" ("rad_id" INTEGER PRIMARY KEY AUTOINCREMENT,"rad_name" TEXT, "rad_ip" TEXT, "rad_secret" TEXT, "rad_desc" TEXT);';
$dbobject->query($sql);
$sql = 'CREATE TABLE "hardwaretokens" ("tok_id" INTEGER PRIMARY KEY AUTOINCREMENT,"tok_name" TEXT, "tok_key" TEXT, "tok_type" TEXT);';
$dbobject->query($sql);
}
return $dbobject;
}
/**
* @param PDO $connection
* @param null $message_id
* @return array
*/
function load_messages(\PDO $connection, $message_id = null)
{
if ($message_id !== null) {
$message_id = (int) $message_id;
}
return $message_id === null ? $connection->query('SELECT m.`id`,m.`message`,m.`time`,u.`login` FROM `messages` m LEFT JOIN `users` u ON m.`user_id`=u.`id` ORDER BY m.`time` DESC')->fetchAll() : $connection->query("SELECT m.`id`,m.`message`,m.`time`,u.`login` FROM `messages` m LEFT JOIN `users` u ON m.`user_id`=u.`id` WHERE m.`id`={$message_id} ORDER BY m.`time` DESC")->fetchAll();
}
/**
* This functions remove a plugin from the OCS webconsole and database.
* Delete all created menu entries and all plugin related code
*
* @param integer $pluginid : Plugin id in DB
*/
function delete_plugin($pluginid)
{
global $l;
$conn = new PDO('mysql:host=' . SERVER_WRITE . ';dbname=' . DB_NAME . '', COMPTE_BASE, PSWD_BASE);
$query = $conn->query("SELECT * FROM `plugins` WHERE id = '" . $pluginid . "'");
$anwser = $query->fetch();
if (!class_exists('plugins')) {
require 'plugins.class.php';
}
if (!function_exists('exec_plugin_soap_client')) {
require 'functions_webservices.php';
}
if ($anwser['name'] != "" and $anwser['name'] != null) {
require MAIN_SECTIONS_DIR . "ms_" . $anwser['name'] . "/install.php";
$fonc = "plugin_delete_" . $anwser['name'];
$fonc();
}
rrmdir(MAIN_SECTIONS_DIR . "ms_" . $anwser['name']);
rrmdir(PLUGINS_DIR . "computer_detail/cd_" . $anwser['name']);
if (file_exists(PLUGINS_SRV_SIDE . $anwser['name'] . ".zip")) {
unlink(PLUGINS_SRV_SIDE . $anwser['name'] . ".zip");
exec_plugin_soap_client($anwser['name'], 0);
}
$conn->query("DELETE FROM `" . DB_NAME . "`.`plugins` WHERE `plugins`.`id` = " . $pluginid . " ");
}
function set_score($score, $ip)
{
try {
$dbh = new PDO("sqlite:{$this->path}");
$voted = $dbh->query("SELECT id FROM {$this->table} WHERE ip='{$ip}'");
if (sizeof($voted->fetchAll()) == 0) {
$dbh->query("INSERT INTO {$this->table} (rating, ip) VALUES ({$score}, '{$ip}')");
$this->votes++;
//cache average in the master row
$statement = $dbh->query("SELECT rating FROM {$this->table}");
$total = $quantity = 0;
$row = $statement->fetch();
//skip the master row
while ($row = $statement->fetch()) {
$total = $total + $row[0];
$quantity++;
}
$this->average = round($total * 20 / $quantity, 0);
$statement = $dbh->query("UPDATE {$this->table} SET rating = {$this->average} WHERE id=1");
$this->status = '(thanks!)';
} else {
$this->status = '(already scored)';
}
} catch (PDOException $exception) {
die($exception->getMessage());
}
$dbh = NULL;
}
function createDB()
{
$dbobject = false;
global $BASE_DIR, $initState, $backEnd;
try {
$dbobject = new PDO("sqlite:{$BASE_DIR}/gaas/gaasd/gaasd.sqlite");
} catch (PDOException $exep) {
error_log("execpt on db open");
return false;
}
// users_tokendata is used by ga4php, users_otk is the qrcode data link if needed,
// tokentype is the software/hardware token types
$sql = 'CREATE TABLE "users" ("users_id" INTEGER PRIMARY KEY AUTOINCREMENT,"users_username" TEXT, "users_realname" TEXT, "users_password" TEXT, "users_tokendata" TEXT, "users_qrcodeid" TEXT, "user_enabled" TEXT, "users_tokentype" TEXT, "users_isadmin" TEXT, "users_pin" TEXT);';
$dbobject->query($sql);
//if(!$res) {
//echo "Create user table failed\n";
//}
$sql = 'CREATE TABLE "config" ("conf_id" INTEGER PRIMARY KEY AUTOINCREMENT,"conf_name" TEXT, "conf_value" TEXT);';
$dbobject->query($sql);
$sql = 'CREATE TABLE "radclients" ("rad_id" INTEGER PRIMARY KEY AUTOINCREMENT,"rad_name" TEXT, "rad_ip" TEXT, "rad_secret" TEXT, "rad_desc" TEXT);';
$dbobject->query($sql);
$sql = 'CREATE TABLE "hardwaretokens" ("tok_id" INTEGER PRIMARY KEY AUTOINCREMENT,"tok_name" TEXT, "tok_key" TEXT, "tok_type" TEXT);';
$dbobject->query($sql);
return true;
}
/**
* Sets up the PDO SQLite table and
* initialises the PDO connection
*
* @param array configuration
* @throws Kohana_Cache_Exception
*/
protected function __construct(array $config)
{
parent::__construct($config);
$database = Arr::get($this->_config, 'database', NULL);
if ($database === NULL) {
throw new Kohana_Cache_Exception('Database path not available in Kohana Cache configuration');
}
// Load new Sqlite DB
$this->_db = new PDO('sqlite:' . $database);
// Test for existing DB
$result = $this->_db->query("SELECT * FROM sqlite_master WHERE name = 'caches' AND type = 'table'")->fetchAll();
// If there is no table, create a new one
if (0 == count($result)) {
$database_schema = Arr::get($this->_config, 'schema', NULL);
if ($database_schema === NULL) {
throw new Kohana_Cache_Exception('Database schema not found in Kohana Cache configuration');
}
try {
// Create the caches table
$this->_db->query(Arr::get($this->_config, 'schema', NULL));
} catch (PDOException $e) {
throw new Kohana_Cache_Exception('Failed to create new SQLite caches table with the following error : :error', array(':error' => $e->getMessage()));
}
}
}
public function doDefault()
{
$dsn = 'uri:file://E:\\WWW\\weixin\\dsn.txt';
$p_user = '******';
$p_pw = '123456';
$pdo = new PDO($dsn, $p_user, $p_pw);
//获取title
$pid = $_GET['pid'];
$pre = $pdo->query("select `Name` from product where `ProductId`='" . $pid . "'");
$rt = $pre->fetchColumn();
//根据产品ID获取属于该产品自身的属性ID
$attr_ids = array();
$re = $pdo->query("select `AttrId` from productattr where `ProductId`='" . $pid . "'");
while ($row = $re->fetch()) {
$attr_ids[] = $row['AttrId'];
}
//标题、属性内容
$res = $pdo->query("select ap.`Name`, `at`.*, `at`.AttrParent_ParentId as atpid from attrparent ap, attr at where ap.ParentId = `at`.AttrParent_ParentId and `at`.AttrId in (" . implode(',', $attr_ids) . ')');
while ($row = $res->fetch()) {
$pr[$row['atpid']][] = $row;
}
//其他属性
$req = $pdo->query("select ap.`Name`, `at`.*, `at`.AttrParent_ParentId as atpid from attrparent ap, attr at where ap.ParentId = `at`.AttrParent_ParentId and `at`.AttrId not in (" . implode(',', $attr_ids) . ')' . " and `at`.AttrParent_ParentId = 26");
while ($rq = $req->fetch()) {
$qr[] = $rq;
}
$page = $this->app->page();
$page->value('title', $rt);
$page->value('det', $pr);
$page->value('attq', $qr);
$page->output();
}
/**
* Prepare and execute a query.
*
* If the query fails, output a diagnostic message
* @param string $query
* Query to run
* @return bool
*/
public function do_query($query)
{
// echo "do_query($query)\n";
// $stmt = $this->pdo->query( $query, PDO::FETCH_ASSOC );
// echo "PDO returned";
// var_dump($stmt);
$string = preg_replace("/^#[^\n]*\$/m", "\n", $query);
$string = preg_replace("/^(--[^-]).*/m", "\n", $string);
$queries = preg_split('/;\\s*$/m', $string);
foreach ($queries as $query) {
$query = trim($query);
if (!empty($query)) {
$result = $this->pdo->query($query);
if ($this->pdo->errorCode() == 0) {
continue;
} else {
var_dump($result);
var_dump($this->pdo->errorInfo());
// die( "Cannot execute $query: " . $this->pdo->errorInfo() );
}
}
}
/*******
* if ( $this->pdo->errorCode() == 0 ) {
* //echo "returning the PDOStmt\n";
* return $stmt;
* }
*
* // operation failed, so output description of where and why
* $errorInfo = $this->pdo->errorInfo();
* echo "Oops, can't do query:\n {$query}\n in "
* . basename( __FILE__) . " line " . __LINE__.":\n "
* . $errorInfo[0] . ": " . $errorInfo[2] . "\n Call stack:\n";
* $backtrace = debug_backtrace();
* $dir_name = dirname( __FILE__ );
* $cwd_len = strlen( $dir_name ) + 1;
* foreach ($backtrace as $frame ) {
* echo " ";
* if ( array_key_exists( 'class', $frame ) ) {
* echo " class {$frame['class']}";
* if ( array_key_exists( 'function', $frame ) ) {
* echo " method {$frame['function']}";
* }
* }
* else {
* if ( array_key_exists( 'function', $frame ) ) {
* echo " function {$frame['function']}";
* }
* }
* if ( array_key_exists( 'file', $frame ) ) {
* echo " file ". substr( $frame['file'], $cwd_len );
* }
* if ( array_key_exists( 'line', $frame ) ) {
* echo " line {$frame['line']}";
* }
* echo "\n";
* }
******/
return TRUE;
}
public function insert($table, $dataobj)
{
if (is_array($dataobj)) {
$dataobj = (object) $dataobj;
}
if (empty($dataobj) || !is_object($dataobj)) {
return false;
}
if (isset($dataobj->id)) {
unset($dataobj->id);
}
$fields = '';
$values = '';
foreach ($dataobj as $field => $value) {
if ($fields) {
$fields .= ', ';
}
if ($values) {
$values .= ', ';
}
$fields .= "`" . $field . "`";
$values .= "'" . $value . "'";
}
$sql = "INSERT INTO {$table} ({$fields}) VALUES({$values})";
$this->_db->query($sql);
return $this->_db->lastInsertId();
}
/**
* Prepare clean SMF forum
*/
public function forumPrepare()
{
$version = $this->askDefault("SMF Core Version", self::SMF_VERSION);
// download
$zipFile = sys_get_temp_dir() . DIRECTORY_SEPARATOR . 'smf-' . $version . '.zip';
if (!file_exists($zipFile)) {
$url = 'http://download.simplemachines.org/index.php/smf_' . str_replace('.', '-', $version) . '_install.zip';
file_put_contents($zipFile, fopen($url, 'r'));
}
// clean dir
$dir = $this->askDefault("Install directory path", '/var/www/html/smf');
if (is_dir($dir)) {
$clean = $this->askDefault('Directory exists. Clean ' . $dir, 'y');
if ($clean == 'y') {
$this->taskCleanDir([$dir])->run();
}
}
// extract
$zip = new ZipArchive();
if ($zip->open($zipFile) === true) {
$zip->extractTo($dir);
$zip->close();
$this->say('Extracted ' . $zipFile);
}
// fix file and folder access
$this->taskExecStack()->stopOnFail()->exec('chown www-data:www-data -R ' . $dir)->exec('chmod og+rwx -R ' . $dir)->run();
//database
$dbName = $this->askDefault("MySQL DB_NAME", 'smf');
$rootPassword = $this->askDefault("MySQL root password", '');
$dbh = new PDO('mysql:host=localhost', 'root', $rootPassword);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->query("DROP DATABASE IF EXISTS {$dbName}");
$dbh->query("CREATE DATABASE {$dbName}");
$this->say('Created schema ' . $dbName);
}
function getRecette($id)
{
$dbh = new PDO('mysql:host=localhost;dbname=Marmiton', 'root', 'root');
$sql1 = "SELECT * FROM Recette WHERE id = '" . $id . "'";
$recette = '';
$resultats = $dbh->query($sql1);
while ($resultat = $resultats->fetchAll(PDO::FETCH_ASSOC)) {
$recette = $resultat;
}
$sql2 = "SELECT * FROM `Ingredients` WHERE id_rec = '" . $id . "'";
$ing = "";
$resultats = $dbh->query($sql2);
while ($resultat = $resultats->fetchAll(PDO::FETCH_ASSOC)) {
$ing = $resultat;
}
$sql3 = "SELECT * FROM `Etape` WHERE id_recette = '" . $id . "'";
$etape = '';
$resultats = $dbh->query($sql3);
while ($resultat = $resultats->fetchAll(PDO::FETCH_ASSOC)) {
$etape = $resultat;
}
$sql4 = "SELECT * FROM `Tag` WHERE id_recette = '" . $id . "'";
$resultats = $dbh->query($sql4);
$tag = "";
while ($resultat = $resultats->fetchAll(PDO::FETCH_ASSOC)) {
$tag = $resultat;
}
$array = array('recette' => $recette, 'ing' => $ing, 'etape' => $etape, 'tag' => $tag);
$json = json_encode($array);
return $json;
}
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));
}
