/**
* getTopicById
*
* Will return topic data
*
* @param int $topicID Identification number of topic
* @return StdClass Topic data
*/
public static function getTopicById($topicID)
{
return \DBI::getConnection('slave')->sendQuery('SELECT
ft.id,
ft.is_closed,
ft.subforum_id,
ft.title topic_title,
ft.description topic_description,
fts.posts_count,
fts.views_count,
sf.title subforum_title,
sf.forum_id,
ff.title forum_title
FROM forum_topics ft
INNER JOIN forum_topics_stat fts
ON fts.topic_id = ft.id
INNER JOIN forum_subforums sf
ON sf.id = ft.subforum_id
INNER JOIN forum_forums ff
ON ff.id = sf.forum_id
WHERE ft.id = :topic_id
', array(':topic_id' => $topicID))->fetch(\PDO::FETCH_OBJ);
}
/**
* Create a new translatable email
*
* @param DBObject $context
* @param string $translation_id
* @param array $variables
*
* @return TranslatableEmail
*/
public static function create($translation_id, $variables)
{
$email = new self();
// Get translation data and variables
$email->translation_id = $translation_id;
$email->variables = array();
if ($variables) {
foreach ($variables as $k => $v) {
// Convert DBObject types to type/id pairs for saving
if ($v instanceof DBObject) {
$v = array('dbobject_type' => get_class($v), 'dbobject_id' => $v->id);
}
$email->variables[$k] = $v;
}
}
// Add meta
$email->created = time();
// Generate token until it is indeed unique
$email->token = Utilities::generateUID(function ($token) {
$statement = DBI::prepare('SELECT * FROM ' . TranslatableEmail::getDBTable() . ' WHERE token = :token');
$statement->execute(array(':token' => $token));
$data = $statement->fetch();
return !$data;
});
$email->save();
return $email;
}
/**
* getLinkByPostId
*
* Return redirection link value
*
* @param int $postID Identification number of post
* @return string Redirection link
*/
public static function getLinkByPostId($postID)
{
$conn = \DBI::getConnection('slave');
// try get post data
$postData = $conn->sendQuery('SELECT
fp.id post_id,
ft.id topic_id
FROM forum_posts fp
INNER JOIN forum_topics ft
ON ft.id = fp.topic_id
WHERE fp.id = :post_id', array(':post_id' => $postID))->fetch(\PDO::FETCH_OBJ);
if (!$postData) {
throw new \SystemErrorException(array('title' => \View::$language->forum_to_post_error, 'description' => \View::$language->forum_to_post_post_not_found));
}
// try get post offset
$postsOffset = $conn->sendQuery('SELECT
COUNT(1) cnt
FROM forum_posts
WHERE topic_id = :topic_id
AND id <= :post_id', array('topic_id' => $postData->topic_id, 'post_id' => $postData->post_id))->fetch(\PDO::FETCH_COLUMN);
// calculate page number
$pageNumber = $postsOffset / \App::getConfig('forum')->posts_per_page;
// TODO posts per page from member custom settings
$pageNumber = ceil($pageNumber);
// build link
$link = '/forum/topic?id=' . $postData->topic_id;
if ($pageNumber > 1) {
$link .= '&page=' . $pageNumber;
}
$link .= '#topic-post-' . $postData->post_id;
return $link;
}
/**
* getSubForums
*
* Will return array of subforums
*
* @param int $forumID Identification number of forum
* @return array Subforums data
*/
public static function getSubForums($forumID = null)
{
$params = array();
$filter = '';
if ($forumID) {
$params[':forum_id'] = $forumID;
$filter = 'WHERE sf.forum_id = :forum_id';
}
return \DBI::getConnection('slave')->sendQuery("SELECT\n\n sf.id,\n sf.forum_id,\n sf.title,\n sf.description,\n\n sfs.topics_count,\n (sfs.posts_count - sfs.topics_count) posts_count,\n\n fp.id last_post_id,\n fp.last_modified last_post_modified,\n\n ft.title last_topic_title,\n\n a.id last_post_author_id,\n a.login last_post_author_login\n\n FROM forum_subforums sf\n INNER JOIN forum_subforums_stat sfs\n ON sfs.subforum_id = sf.id\n INNER JOIN forum_forums ff\n ON ff.id = sf.forum_id\n LEFT JOIN forum_posts fp\n ON fp.id = sfs.last_post_id\n LEFT JOIN forum_topics ft\n ON ft.id = fp.topic_id\n LEFT JOIN members a\n ON a.id = fp.authored_by\n {$filter}\n ORDER BY ff.sort ASC, sf.sort ASC\n ", $params)->fetchAll(\PDO::FETCH_OBJ);
}
/**
* getPostsByTopicId
*
* Will return array of posts by identification number of topic
*
* @param int $topicID Identification number of topic
* @param int $offset Offset number of first post
* @param int $limit Number of posts per page
* @return array Posts data
*/
public static function getPostsByTopicId($topicID, $offset, $limit)
{
$topicPosts = \DBI::getConnection('slave')->sendQuery("SELECT\n\n fp.id,\n fp.topic_start,\n fp.edited_by,\n fp.moderated_by,\n fp.creation_date,\n fp.last_modified,\n IF(fp.creation_date = fp.last_modified, 0, 1) is_modified,\n fp.post_html,\n\n fm.posts_count author_posts_count,\n\n a.id author_id,\n a.login author_login,\n a.avatar author_avatar,\n ag.name author_group_name\n\n FROM forum_posts fp\n INNER JOIN forum_members fm\n ON fm.author_id = fp.authored_by\n LEFT JOIN members a\n ON a.id = fm.author_id\n LEFT JOIN groups ag\n ON ag.id = a.group_id\n WHERE fp.topic_id = :topic_id\n ORDER BY fp.topic_start DESC, fp.creation_date ASC\n LIMIT {$offset}, {$limit}\n ", array(':topic_id' => $topicID))->fetchAll(\PDO::FETCH_OBJ);
$hCnf = \App::getConfig('hosts');
$mCnf = \App::getConfig('member-defaults');
foreach ($topicPosts as $item) {
if (!$item->author_avatar) {
$item->author_avatar = '//' . $hCnf->st . $mCnf->avatar;
}
}
return $topicPosts;
}
/**
* Constructor
*
* @param integer $id identifier of user to load from database (null if loading not wanted)
* @param array $data data to create the notes_log from (if already fetched from database)
*
* @throws NotesLogNotFoundException
*/
protected function __construct($id = null, $data = null)
{
if (!is_null($id)) {
$statement = DBI::prepare('SELECT * FROM ' . self::getDBTable() . ' WHERE id = :id');
$statement->execute(array(':id' => $id));
$data = $statement->fetch();
if (!$data) {
throw new NotesLogNotFoundException('id = ' . $id);
}
}
if ($data) {
$this->fillFromDBData($data);
}
}
/**
* getSubForumById
*
* Will return subforum data
*
* @param int $subForumID Identification number of subforum
* @return array Subforum data
*/
public static function getSubForumById($subForumID)
{
return \DBI::getConnection('slave')->sendQuery('SELECT
sf.id,
sf.forum_id,
sf.title,
sf.description,
fss.topics_count,
ff.title forum_title
FROM forum_subforums sf
INNER JOIN forum_subforums_stat fss
ON fss.subforum_id = sf.id
INNER JOIN forum_forums ff
ON ff.id = sf.forum_id
WHERE sf.id = :subforum_id', array(':subforum_id' => $subForumID))->fetch(\PDO::FETCH_OBJ);
}
/**
* trySignIn
*
* Try sign in process
*
* @param StdClass $signInData Member sign in data
* @return null
*/
public static function trySignIn(\StdClass $signInData)
{
\View::addLanguageItem('SignInHelper');
$memberData = \DBI::getConnection('master')->sendQuery('SELECT cookie, password, status
FROM members
WHERE email = :email OR login = :login', array(':email' => $signInData->login, ':login' => $signInData->login))->fetch(\PDO::FETCH_OBJ);
// member data not found
if (!$memberData) {
throw new \MemberErrorException(array('title' => \View::$language->sign_in_helper_error, 'description' => \View::$language->sign_in_helper_login_or_password_invalid));
}
// compare password
$hasSamePassword = \common\CryptHelper::verifyHash($signInData->password, $memberData->password);
if (!$hasSamePassword) {
throw new \MemberErrorException(array('title' => \View::$language->sign_in_helper_error, 'description' => \View::$language->sign_in_helper_login_or_password_invalid));
}
// sign in success
$cnf = \App::getConfig('main')->system;
$exp = time() + $cnf->cookie_expires_time;
setcookie($cnf->cookie_name, $memberData->cookie, $exp, '/');
throw new \MemberSuccessException(array('title' => \View::$language->sign_in_helper_success, 'description' => \View::$language->sign_in_helper_success_description, 'redirection' => '/'));
}
/**
* Constructor
*
* @param integer $id identifier of user to load from database (null if loading not wanted)
* @param array $data data to create the user from (if already fetched from database)
*
* @throws UserNotFoundException
*/
protected function __construct($id = null, $data = null)
{
if (!is_null($id)) {
// Load from database if id given
$statement = DBI::prepare('SELECT * FROM ' . self::getDBTable() . ' WHERE id = :id');
$statement->execute(array(':id' => $id));
$data = $statement->fetch();
}
if ($data) {
// Fill properties from provided data
$this->fillFromDBData($data);
$this->hasPreferences = true;
} else {
// New user, set base data
$this->id = $id;
$this->created = time();
}
// Generate user remote auth secret
if (Config::get('auth_remote_user_autogenerate_secret') && !$this->auth_secret) {
$this->auth_secret = hash('sha256', $this->id . '|' . time() . '|' . Utilities::generateUID());
$this->save();
}
}
/**
* getTree
*
* Will return forums tree data
*
* @param int $forumID Identification number of forum
* @return array Forums tree data
*/
public static function getTree($forumID = null)
{
$params = array();
$filter = '';
if ($forumID) {
$params[':forum_id'] = $forumID;
$filter = 'WHERE ff.id = :forum_id';
}
// get forums
$forums = \DBI::getConnection('slave')->sendQuery("SELECT\n ff.id,\n ff.title,\n ff.description,\n (NULL) subforums\n FROM forum_forums ff\n INNER JOIN forum_forums_stat ffs\n ON ffs.forum_id = ff.id\n AND ffs.subforums_count > 0\n {$filter}\n ORDER BY ff.sort ASC", $params)->fetchAll(\PDO::FETCH_OBJ);
// get subforums
if ($forums) {
$subForums = SubForumsHelper::getSubForums($forumID);
foreach ($forums as $forum) {
$forum->subforums = array();
foreach ($subForums as $subForum) {
if ($subForum->forum_id == $forum->id) {
$forum->subforums[] = $subForum;
}
}
}
}
return $forums;
}
//==============================================================
// experiment using "magic methods"
//users2 is the table name... you can see where this can go...
$result = $DB->users2->FetchAll();
//SERVER::dump($result);
$DB->CloseConnection();
//CLOSE DATABASE
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
//
// MYSQLi
//
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
require_once 'lib/dbi.class.php';
/*
MYSQL dynamic fetch... work in progress...
used in my user search page we had to build
It looks bulkey, but the power comes from its ability to dynamicly build complex sqli securly...
/*/
$myDBI = new DBI($DBc, true);
$fetchParams = array('select_items' => array('id', 'username', 'email', 'fname', 'lname', 'lastlogin', 'num_sucess_login', 'num_fail_login', 'blocked'), 'from' => 'users2', 'where_items' => array(array('condition' => 'AND', 'field' => 's:username', 'operator' => '=', 'value' => 'jordan'), array('condition' => 'OR', 'field' => 's:username', 'operator' => 'LIKE', 'value' => '%bob%')));
//SERVER::dump($fetchParams); // preview fetch params
$searchResults = $myDBI->FetchRows($fetchParams, $meta);
//SERVER::dump($searchResults);
/*note* nice addition would be ability to use equivalent of perenthises
perhaps a group of conditions inside an array.. would require recursion however
*/
$myDBI->Disconnect();
public static function getByID($guestID)
{
$query = 'SELECT * FROM ' . static::getDBTable() . " where id = :id";
$statement = DBI::prepare($query);
$statement->execute(array('id' => $guestID));
$objects = array();
return $statement->fetch();
}
/**
* This function allwso to know if current pad has guest passed in parameters
*
* @param GuestComponent $guest
* @return boolean true if has,n false otherwhise
*/
public function hasGuest($guest)
{
$tmpGuest = GuestComponent::getByEmail($guest->email);
if ($tmpGuest) {
$query = 'SELECT * FROM ' . PadGuestComponent::getDBTable() . " where pad_id = :pad_id AND guest_id = :guest_id ";
$statement = DBI::prepare($query);
$statement->execute(array('pad_id' => $this->id, 'guest_id' => $guest->id));
$datas = $statement->fetch();
if (is_array($datas)) {
return true;
}
}
return false;
}
<?php
error_reporting(E_ALL);
$DOC_ROOT = $_SERVER['DOCUMENT_ROOT'];
$USER_DIR = '/martin';
$PROJECT_NAME = '/hem';
$APP_ROOT = $DOC_ROOT . $USER_DIR . $PROJECT_NAME;
$PEAR_DIR = $APP_ROOT . '/pear';
$APP_FRAMEWORK_DIR = $APP_ROOT . '/framework';
$APP_FRAMEWORK_CLASSES_DIR = $APP_FRAMEWORK_DIR . '/classes';
$PATH = $PEAR_DIR . $APP_FRAMEWORK_DIR . $APP_FRAMEWORK_CLASSES_DIR;
ini_set(' include_path', ':' . $PATH . ':' . ini_get('include_path'));
require_once 'DB.php';
require_once 'class.DBI.php';
$DB_URL = 'mysql://*****:*****@localhost/test';
$dbi = new DBI($DB_URL);
echo "DBI Version " . $dbi->apiVersion() . " loaded <br/>";
if (!$dbi->isConnected()) {
echo "Connection failed for {$DB_URL}: " . $dbi->getError() . "<br/>";
exit;
}
$statement = "SELECT ID, text FROM test";
$result = $dbi->query($statement);
if ($result == NULL) {
echo "Database error: " . $dbi->getError() . "<br/>";
} else {
if (!$result->numRows()) {
echo "Database error: No rows found<br/>";
} else {
echo "<pre>ID\text<br/>";
while ($row = $result->fetchRow()) {
/**
* createUser
*
* Create a new user
*
* @param StdClass $userData User data
* @return int User identification number
*/
public function createUser(\StdClass $userData)
{
$conn = \DBI::getConnection('master');
$conn->sendQuery("INSERT INTO members (\n group_id,\n cookie,\n email,\n login,\n password,\n time_zone,\n creation_date,\n last_ip,\n last_visit,\n status,\n activation_hash,\n avatar\n ) VALUES (\n :group_id,\n '',\n :email,\n :login,\n :password,\n :time_zone,\n NOW(),\n '',\n NOW(),\n :status,\n :activation_hash,\n :avatar\n )", array(':group_id' => $userData->group_id, ':email' => $userData->email, ':login' => $userData->login, ':password' => $userData->password, ':time_zone' => $userData->time_zone, ':status' => $userData->status, ':activation_hash' => $userData->activation_hash, ':avatar' => $userData->avatar));
return $conn->lastInsertId();
}
/**
* getAllTopicsCount
*
* Will return number of all available topics
*
* @return int Number of all available topics
*/
public static function getAllTopicsCount()
{
return (int) \DBI::getConnection('slave')->sendQuery('SELECT SUM(topics_count) cnt FROM forum_subforums_stat')->fetch(\PDO::FETCH_COLUMN);
}
public function customDelete()
{
// Remove from database
$s = DBI::prepare('DELETE FROM ' . static::getDBTable() . ' WHERE pad_id = :pad_id AND guest_id = :guest_id');
$s->execute(array('pad_id' => $this->pad_id, 'guest_id' => $this->guest_id));
// Remove from object cache
self::purgeCache(get_called_class(), $this->pad_id);
}
/**
* sendQuery
*
* Return executed PDOStatement result object
*
* @param string Query string
* @param array Parameters for placeholders
* @return PDOStatement Result object
*/
public function sendQuery($queryString, array $queryParams = array())
{
try {
DBI::addToStat($queryString);
$stmt = $this->_PDO->prepare($queryString);
$stmt->execute($queryParams);
} catch (PDOException $e) {
if ($this->_PDO->inTransaction()) {
$this->_PDO->rollBack();
}
throw new SystemErrorException(array('title' => 'Database error', 'description' => $e->getMessage()));
}
return $stmt;
}
/**
* Generate a new survey id
*
* @return string
*
* @throws SurveyUIDGeneratorTriedTooMuchException
*/
private static function generateUID()
{
return self::generateKey(self::UID_LENGTH, function ($uid) {
$check = DBI::prepare('SELECT id FROM ' . self::getDBTable() . ' WHERE id = :id');
$check->execute(array(':id' => $uid));
return !$check->fetch();
});
}
/**
* Table columns format update.
*
* @param string $table table name
* @param string $column column name
* @param array $definition column definition
* @param array $problems problematic options
*/
public static function updateTableColumnFormat($table, $column, $definition, $problems)
{
if (in_array('type', $problems)) {
// Cahnge data type
$type = '';
switch ($definition['type']) {
case 'int':
case 'uint':
$size = array_key_exists('size', $definition) ? $definition['size'] : 'medium';
if (!$size) {
$size = 'medium';
}
$s2s = array('small' => 'smallint', 'medium' => 'integer', 'big' => 'bigint');
$type .= $s2s[$size];
break;
case 'string':
$type .= 'character varying(' . $definition['size'] . ')';
break;
case 'bool':
$type .= 'boolean';
break;
case 'text':
$type .= 'text';
break;
case 'date':
$type .= 'date';
break;
case 'datetime':
$type .= 'timestamp';
break;
case 'time':
$type .= 'time';
break;
}
if ($type) {
DBI::exec('ALTER TABLE ' . $table . ' ALTER COLUMN ' . $column . ' TYPE ' . $type);
}
}
// Options defaults
foreach (array('null', 'primary', 'unique', 'autoinc') as $k) {
if (!array_key_exists($k, $definition)) {
$definition[$k] = false;
}
}
// Change nullable
if (in_array('null', $problems)) {
if ($definition['null']) {
DBI::exec('ALTER TABLE ' . $table . ' ALTER COLUMN ' . $column . ' DROP NOT NULL');
} else {
DBI::exec('ALTER TABLE ' . $table . ' ALTER COLUMN ' . $column . ' SET NOT NULL');
}
}
// Change default
if (in_array('default', $problems)) {
if (array_key_exists('default', $definition)) {
if (is_null($definition['default'])) {
DBI::exec('ALTER TABLE ' . $table . ' ALTER COLUMN ' . $column . ' SET DEFAULT NULL');
} else {
if (is_bool($definition['default'])) {
DBI::exec('ALTER TABLE ' . $table . ' ALTER COLUMN ' . $column . ' SET DEFAULT ' . ($definition['default'] ? '1' : '0'));
} else {
DBI::prepare('ALTER TABLE ' . $table . ' ALTER COLUMN ' . $column . ' SET DEFAULT :default') - execute(array(':default' => $definition['default']));
}
}
} else {
DBI::exec('ALTER TABLE ' . $table . ' ALTER COLUMN ' . $column . ' DROP DEFAULT');
}
}
// Change primary
if (in_array('primary', $problems)) {
if ($definition['primary']) {
DBI::exec('ALTER TABLE ' . $table . ' ADD CONSTRAINT primary_' . $column . ' PRIMARY KEY (' . $column . ')');
} else {
DBI::exec('ALTER TABLE ' . $table . ' DROP CONSTRAINT primary_' . $column);
}
}
// Change unique
if (in_array('unique', $problems)) {
if ($definition['unique']) {
DBI::exec('ALTER TABLE ' . $table . ' ADD CONSTRAINT unique_' . $column . ' UNIQUE (' . $column . ')');
} else {
DBI::exec('ALTER TABLE ' . $table . ' DROP CONSTRAINT unique_' . $column);
}
}
// Change autoinc
if (in_array('autoinc', $problems)) {
if ($definition['autoinc']) {
self::createSequence($table, $column);
} else {
DBI::exec('ALTER TABLE ' . $table . ' ALTER COLUMN ' . $column . ' DROP DEFAULT');
// Should we drop the sequence as well ?
}
}
}
/**
* getTopics
*
* Will return topics data
*
* @param int $subForumID Identification number of subforum
* @param int $offset Offset number of first topic
* @param int $limit Number of topics per page
* @return array Topics data
*/
public static function getTopics($subForumID, $offset, $limit)
{
return \DBI::getConnection('slave')->sendQuery("SELECT\n\n ft.id,\n ft.creation_date,\n ft.last_modified,\n ft.is_locked,\n ft.is_important,\n ft.is_closed,\n ft.title,\n ft.description,\n\n IF(fts.posts_count > 1, fts.posts_count - 1, 0) posts_count,\n fts.views_count,\n fts.last_post_id,\n\n fp.last_modified last_post_modified\n,\n ta.id author_id,\n ta.login author_login,\n\n pa.id last_post_author_id,\n pa.login last_post_author_login\n\n FROM forum_topics ft\n INNER JOIN forum_topics_stat fts\n ON fts.topic_id = ft.id\n LEFT JOIN forum_posts fp\n ON fp.id = fts.last_post_id\n LEFT JOIN members ta\n ON ta.id = ft.authored_by\n LEFT JOIN members pa\n ON pa.id = fp.authored_by\n WHERE ft.subforum_id = :subforum_id\n ORDER BY ft.is_locked DESC, ft.creation_date DESC\n LIMIT {$offset}, {$limit}\n ", array(':subforum_id' => $subForumID))->fetchAll(\PDO::FETCH_OBJ);
}
/**
* Update object record
*
* @param array $data database compliant data such as returned by toDBData
* @param string $key_name field_name to use as primary key (string or array of strings)
* @param string $where where clause extension (optionnal)
*/
public static function updateRecord($data, $key_name, $where = null)
{
$event = new Event('dbobject_update', static::getClassName(), $data, $key_name, $where);
$table = static::getDBTable();
$event->trigger(function ($class, $data, $key_name, $where) use($table) {
$placeholders = array();
$values = array();
// Filter
$key_names = is_array($key_name) ? $key_name : array($key_name);
$key_names = array_filter($key_names);
// Build update pairs
foreach ($data as $field_name => $value) {
if (!in_array($field_name, $key_names)) {
$placeholders[] = $field_name . ' = :' . $field_name;
}
$values[':' . $field_name] = $value;
}
// Build filter
$where_parts = array();
foreach ($key_names as $key_name) {
$where_parts[] = $key_name . ' = :' . $key_name;
}
if ($where) {
$where_parts[] = $where;
}
// Run the query
$s = DBI::prepare('UPDATE ' . $table . ' SET ' . implode(', ', $placeholders) . (count($where_parts) ? ' WHERE (' . implode(') AND (', $where_parts) . ')' : ''));
$s->execute($values);
});
}
/**
* getStat
*
* Return statistics
*
* @return array Statistics data
*/
public static function getStat()
{
return array('db' => DBI::getStat(), 'memory' => self::getMemoryUsage(), 'time' => sprintf('%1.6f', microtime(true) - self::$_startTime) . ' sec.');
}
/**
* _initPermissions
*
* Set member role permissions with group_id
*
* @return null
*/
private static function _initPermissions()
{
$conn = DBI::getConnection('slave');
$permissions = $conn->sendQuery('SELECT
p.name
FROM groups_permissions gp
INNER JOIN permissions p
ON p.id = gp.permission_id
WHERE gp.group_id = :group_id', array(':group_id' => self::$_profile->group_id))->fetch(PDO::FETCH_OBJ);
if ($permissions) {
self::$_permissions = $permissions;
}
}
/**
* Connect to database
*
* @throws DBIConnexionMissingParameterException
* @throws DBIConnexionException
*/
private static function connect()
{
if (!is_null(self::$instance)) {
return;
}
// Get config, check mandatory parameters
$config = Config::get('db_*');
$config['dsn'] = Config::get('dsn');
foreach (array('type', 'host', 'database', 'port', 'username', 'password', 'driver_options', 'charset', 'collation') as $p) {
if (!array_key_exists($p, $config)) {
$config[$p] = null;
}
}
// Build dsn from individual components if not defined
if (!$config['dsn']) {
if (!$config['type']) {
$config['type'] = 'pgsql';
}
$params = array();
if (!$config['host']) {
throw new DBIConnexionMissingParameterException('host');
}
$params[] = 'host=' . $config['host'];
if (!$config['database']) {
throw new DBIConnexionMissingParameterException('database');
}
$params[] = 'dbname=' . $config['database'];
if ($config['port']) {
$params[] = 'port=' . $config['port'];
}
$config['dsn'] = $config['type'] . ':' . implode(';', $params);
}
// Check that required parameters are not empty
if (!$config['username']) {
throw new DBIConnexionMissingParameterException('username');
}
if (!$config['password']) {
throw new DBIConnexionMissingParameterException('password');
}
if (!$config['driver_options']) {
$config['driver_options'] = array();
}
// Try to connect, cast any thrown exception
try {
// Connect
self::$instance = new PDO($config['dsn'], $config['username'], $config['password'], $config['driver_options']);
// Set options : throw if error, do not cast returned values to string, fetch as associative array by default
self::$instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
self::$instance->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
self::$instance->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
// db_charset given in config ?
if ($config['charset']) {
if ($config['collation']) {
self::prepare('SET NAMES :charset COLLATE :collation')->execute(array(':charset' => $config['charset'], ':collation' => $config['collation']));
} else {
self::prepare('SET NAMES :charset')->execute(array(':charset' => $config['charset']));
}
}
} catch (Exception $e) {
throw new DBIConnexionException('DBI connect error : ' . $e->getMessage());
}
}
/**
* Table columns format update.
*
* @param string $table table name
* @param string $column column name
* @param array $definition column definition
* @param array $problems problematic options
*/
public static function updateTableColumnFormat($table, $column, $definition, $problems)
{
$primaryKeys = array();
if (isset($definition['primary']) && $definition['primary'] === true) {
// Needs to add a PK
$column_dfn = self::getTableColumns($table, false);
// Getting all other PK
if (is_array($column_dfn)) {
foreach ($column_dfn as $col) {
if (isset($col['Key']) && $col['Key'] === 'PRI') {
$primaryKeys[] = $col['Field'];
}
}
}
// Drop primary keys only if there is at least on primary key
if (count($primaryKeys) > 0) {
$query = 'ALTER TABLE ' . $table . ' DROP PRIMARY KEY; ';
}
$primaryKeys[] = $column;
unset($definition['primary']);
} else {
if (in_array('primary', $problems)) {
// Needs to remove a PK
// First remove all PK
$query = 'ALTER TABLE ' . $table . ' DROP PRIMARY KEY; ';
// Get all PK of the table
$column_dfn = self::getTableColumns($table, false);
// Creating list of PK without the PK to remove
if (is_array($column_dfn)) {
foreach ($column_dfn as $col) {
if (isset($col['Key']) && $col['Key'] === 'PRI' && $col['Field'] !== $column) {
$primaryKeys[] = $col['Field'];
}
}
}
}
}
$query .= 'ALTER TABLE ' . $table . ' MODIFY ' . $column . ' ' . self::columnDefinition($definition) . '; ';
// Finaly, if PK have to be created, create these
if (count($primaryKeys) > 0) {
$query .= 'ALTER TABLE ' . $table . ' ADD PRIMARY KEY (' . implode(', ', $primaryKeys) . ')';
}
DBI::exec($query);
}
public function FetchRows($params, &$meta = null)
{
//parse params **not finished more validation and switches to come
$selectItems = $params['select_items'];
$where = !empty($params['where_items']) ? $params['where_items'] : null;
$table = $params['from'];
$isWhereString = isset($params['where_items']) && is_array($params['where_items']) && count($params['where_items']) > 0;
///////////////////////////////////////////////////////////
//BUILD QUERY /////////////////////////////////////////////
$SQL = '';
//BUILD SELCET ============================================
$selectString = 'SELECT ' . join(", ", (array) $selectItems);
//BUILD WHERE ==============================================
$fromString = 'FROM ' . $table;
//BUILD WHERE ==============================================
//$isWhereString
//$where
$whereString = '';
if (!empty($where) && is_array($where)) {
$whereBindParams = array();
$whereString = $this->buildWhere($isWhereString, $whereBindParams, $where);
} else {
$whereString = 'WHERE 1 = 1';
}
//COMPILE STATEMENT ===========================================
$SQL = $selectString . ' ' . $fromString . ' ' . $whereString;
//SERVER::dump($SQL);
/////////////////////////////////////////////////////////// <-- split seperate functions
//RUN QUERY /////////////////////////////////////////////// <-- might need to accept other params from order by or group
//PREPAIR STATEMENT =========================================
$theStatement = $this->connection->prepare($SQL);
if (!empty($whereBindParams)) {
call_user_func_array(array($theStatement, 'bind_param'), $whereBindParams);
// replace $whereBindParams with list of fields from emta data of statement
}
//BIND RESULT ================================================
$bind_result = array();
if (!empty($selectItems) && is_array($selectItems)) {
DBI::bindResult($theStatement, $selectItems, $bind_result);
// modify to expect from statement
}
//EXECUTE FETCH ===========================================
$theStatement->execute();
$result = array();
while ($theStatement->fetch()) {
$aRow = array();
//clone values of referenced array
foreach ($bind_result as $key => $value) {
$aRow[$key] = $value;
}
$result[] = $aRow;
}
$theStatement->close();
return $result;
}
/**
* close
*
* Close storage resource.
* Nothing here, compatible only
*
* @return bool Status
*/
public function close()
{
DBI::getConnection('master')->commit();
return true;
}
public function execute()
{
$numargs = func_num_args();
if ($numargs) {
$this->bindings = func_get_args();
}
# bindings is initialized to an emtpy array in the constructor
$stmt = '';
$success = true;
if ($this->bindtypes === "positional") {
if (count($this->sections) > 1) {
foreach ($this->sections as $s) {
$stmt .= $s;
if (count($this->bindings) < 1) {
$this->success = false;
break;
}
$v = array_shift($this->bindings);
$stmt .= $this->dbh->quote($v);
}
} else {
$stmt = $this->sections[0];
}
} else {
$stmt = $this->sql;
$this->bindings = array_shift($this->bindings);
if (!is_array($this->bindings)) {
/* named binding parameters used, but didn't pass an array */
throw new DBIException('named binding parameters used, but did not pass an array');
}
$search1 = array();
$replace1 = array();
foreach ($this->bindings as $k => $v) {
$search1[] = "?:{$k}";
$v = $this->dbh->quote($v);
if (preg_match('/^\\w+:join$/', $k)) {
if (is_array($v)) {
$v = join(',', $v);
} else {
throw new DBIException(":join named parameter ({$k}) specified for non-array value");
}
}
if (is_array($v)) {
throw new DBIException("query value is an array");
}
$replace1[] = $v;
}
/* slight chance that the ?:\w+ string could appear in a quoted string */
/* if there is a binding they didn't specify, let the SQL parser detect it */
foreach ($search1 as $k => $v) {
$stmt = preg_replace('/\\?' . $v . '\\b/', $replace1[$k], $stmt);
}
}
if (!$stmt) {
/* no statement resulted? */
throw new DBIException('empty statement, this should not happen');
}
if (!$success) {
/* too few binding parameters */
throw new DBIException("too few binding parameters in query {$stmt}");
}
$this->bindings = array();
$qstart = DBI::getmicrotime();
$this->cursor_handle = $this->dbh->dbd->query($stmt);
$qend = DBI::getmicrotime();
$this->executed_stmt = $stmt;
if (preg_match('/^\\s*(\\w+)\\b/', $stmt, $m)) {
@DBI::$statement_types[strtolower($m[1])]++;
}
$qlen = sprintf('%0.5f', $qend - $qstart);
if (!empty($_SERVER['debugsql'])) {
d($stmt, 'execution time: ' . $qlen . ' sec');
}
#error_log($stmt);
$this->execution_time = $qlen;
DBI::$query_runtime += $qlen;
if (!$this->cursor_handle) {
$e = new DBIException($this->dbh->dbd->error());
$e->setStatement($stmt);
throw $e;
}
return $this->cursor_handle;
}
/**
* Table columns format update.
*
* @param string $table table name
* @param string $column column name
* @param array $definition column definition
* @param array $problems problematic options
*/
public static function updateTableColumnFormat($table, $column, $definition, $problems)
{
$query = 'ALTER TABLE ' . $table . ' MODIFY ' . $column . ' ' . self::columnDefinition($definition);
DBI::exec($query);
}
