function open($path, $name)
{
if (Config::getInstance()->is_debug()) {
$message = sprintf("session_open : path=%s name %s", $path, $name);
Logger::getInstance()->debug($message);
}
$this->dbh = PDOWrapper::getHandle();
return TRUE;
}
/**
* function to create a facebook user data in our system. we populate the following tables
* sc_login
* sc_facebook
* sc_denorm_user (via a trigger)
* The data manipulated via our web forms is always stored in sc_denorm_table
* sc_facebook is for first time creation only. We could have removed the columns from
* sc_facebook that are already present in sc_denorm_user. However for lookup or other
* purposes (e.g. sc_user.email), common columns are a necessary evil. We should never
* update sc_facebook and other user base tables (sc_twitter, sc_user etc.) via our web forms.
*
*
*/
static function create($facebookId, $name, $firstName, $lastName, $link, $gender, $email, $provider, $access_token, $expires, $remoteIp)
{
$dbh = NULL;
try {
$sql1 = "insert into sc_login (provider,name,created_on,access_token,expire_on,ip_address) ";
$sql1 .= " values(:provider,:name,now(),:access_token, %s, :ip_address) ";
$dbh = PDOWrapper::getHandle();
//Tx start
$dbh->beginTransaction();
$expiresOn = "(now() + interval " . $expires . " second)";
$sql1 = sprintf($sql1, $expiresOn);
$stmt1 = $dbh->prepare($sql1);
$stmt1->bindParam(":name", $name);
$stmt1->bindParam(":provider", $provider);
$stmt1->bindParam(":access_token", $access_token);
$stmt1->bindParam(":ip_address", $remoteIp);
$stmt1->execute();
$stmt1 = NULL;
$loginId = $dbh->lastInsertId();
settype($loginId, "integer");
$sql2 = " insert into sc_facebook(facebook_id,name,first_name,last_name,link,gender,";
$sql2 .= " email,login_id,ip_address,created_on) ";
$sql2 .= " values(?,?,?,?,?,?,?,?,?,now()) ";
$stmt2 = $dbh->prepare($sql2);
$stmt2->bindParam(1, $facebookId);
$stmt2->bindParam(2, $name);
$stmt2->bindParam(3, $firstName);
$stmt2->bindParam(4, $lastName);
$stmt2->bindParam(5, $link);
$stmt2->bindParam(6, $gender);
$stmt2->bindParam(7, $email);
$stmt2->bindParam(8, $loginId);
$stmt2->bindParam(9, $remoteIp);
$stmt2->execute();
$stmt2 = NULL;
//Tx end
$dbh->commit();
$dbh = null;
return $loginId;
} catch (\PDOException $e) {
$dbh->rollBack();
$dbh = null;
throw new DBException($e->getMessage(), $e->getCode());
} catch (\Exception $ex) {
$dbh->rollBack();
$dbh = null;
$message = $ex->getMessage();
throw new DBException($message);
}
}
static function create($loginId, $name, $ownerId, $postId, $title, $comment)
{
$dbh = NULL;
try {
// insert into sc_comment, adjust counters via trigger
$sql1 = " insert into sc_comment(post_id,description,login_id, created_on) ";
$sql1 .= " values(:post_id,:comment,:login_id,now()) ";
$dbh = PDOWrapper::getHandle();
//Tx start
$dbh->beginTransaction();
$stmt1 = $dbh->prepare($sql1);
$stmt1->bindParam(":post_id", $postId);
$stmt1->bindParam(":comment", $comment);
$stmt1->bindParam(":login_id", $loginId);
$stmt1->execute();
$stmt1 = NULL;
$sql2 = " insert into sc_activity(owner_id,subject_id,subject,object_id, ";
$sql2 .= " object,verb, verb_name, op_bit, content,created_on) ";
$sql2 .= " values(:owner_id, :subject_id, :subject, :object_id, ";
$sql2 .= " :object, :verb, :verb_name, :op_bit, :content,now()) ";
$verb = AppConstants::COMMENT_VERB;
$op_bit = 0;
$verbName = AppConstants::STR_COMMENT;
$content = Util::abbreviate($comment, 100);
$stmt2 = $dbh->prepare($sql2);
$stmt2->bindParam(":owner_id", $ownerId);
$stmt2->bindParam(":subject_id", $loginId);
$stmt2->bindParam(":object_id", $postId);
$stmt2->bindParam(":subject", $name);
$stmt2->bindParam(":object", $title);
$stmt2->bindParam(":verb", $verb);
$stmt2->bindParam(":verb_name", $verbName);
$stmt2->bindParam(":op_bit", $op_bit);
$stmt2->bindParam(":content", $content);
$stmt2->execute();
$stmt2 = NULL;
//Tx end
$dbh->commit();
$dbh = null;
} catch (\PDOException $e) {
$dbh->rollBack();
$dbh = null;
throw new DBException($e->getMessage(), $e->getCode());
} catch (\Exception $ex) {
$dbh->rollBack();
$dbh = null;
$message = $ex->getMessage();
throw new DBException($message);
}
}
/**
* function to create a google user data in our system. we populate the following tables
* sc_login
* sc_google_user
* sc_denorm_user (via a trigger)
* The data manipulated via our web forms is always stored in sc_denorm_table
* sc_google_user is for first time creation only.
* We should never update sc_google_user via our web forms.
*
*
*/
static function create($googleId, $email, $name, $firstName, $lastName, $photo, $provider, $remoteIp)
{
$dbh = NULL;
try {
$sql1 = "insert into sc_login(provider,name,ip_address,created_on) ";
$sql1 .= " values(:provider,:name, :ip_address,now()) ";
$dbh = PDOWrapper::getHandle();
//Tx start
$dbh->beginTransaction();
$stmt1 = $dbh->prepare($sql1);
$stmt1->bindParam(":name", $name);
$stmt1->bindParam(":provider", $provider);
$stmt1->bindParam(":ip_address", $remoteIp);
$stmt1->execute();
$stmt1 = NULL;
$loginId = $dbh->lastInsertId();
settype($loginId, "integer");
$sql2 = " insert into sc_google_user(google_id,email,name,first_name,last_name,";
$sql2 .= " photo,login_id,ip_address,created_on) ";
$sql2 .= " values(?,?,?,?,?,?,?,?,now()) ";
$stmt2 = $dbh->prepare($sql2);
$stmt2->bindParam(1, $googleId);
$stmt2->bindParam(2, $email);
$stmt2->bindParam(3, $name);
$stmt2->bindParam(4, $firstName);
$stmt2->bindParam(5, $lastName);
$stmt2->bindParam(6, $photo);
$stmt2->bindParam(7, $loginId);
$stmt2->bindParam(8, $remoteIp);
$stmt2->execute();
$stmt2 = NULL;
//Tx end
$dbh->commit();
$dbh = null;
return $loginId;
} catch (\PDOException $e) {
$dbh->rollBack();
$dbh = null;
throw new DBException($e->getMessage(), $e->getCode());
} catch (\Exception $ex) {
$dbh->rollBack();
$dbh = null;
$message = $ex->getMessage();
throw new DBException($message);
}
}
/**
* function to create a 3mik user. we populate following tables
* sc_login
* sc_user
* sc_denorm_user (via a trigger)
*
*/
static function create($provider, $userName, $firstName, $lastName, $email, $password, $remoteIp)
{
$dbh = NULL;
try {
//canonical form of email
$email = strtolower(trim($email));
$password = trim($password);
$sql1 = "insert into sc_login (provider,name,ip_address,created_on) ";
$sql1 .= " values(:provider,:name, :ip_address,now()) ";
$dbh = PDOWrapper::getHandle();
//Tx start
$dbh->beginTransaction();
$stmt = $dbh->prepare($sql1);
$stmt->bindParam(":name", $userName);
$stmt->bindParam(":provider", $provider);
$stmt->bindParam(":ip_address", $remoteIp);
$stmt->execute();
$stmt = NULL;
$loginId = $dbh->lastInsertId();
settype($loginId, "integer");
//@throws DBException
\com\indigloo\auth\User::create('sc_user', $firstName, $lastName, $userName, $email, $password, $loginId, $remoteIp);
//Tx end
$dbh->commit();
$dbh = null;
} catch (\PDOException $e) {
$dbh->rollBack();
$dbh = null;
throw new DBException($e->getMessage(), $e->getCode());
} catch (\Exception $ex) {
$dbh->rollBack();
$dbh = null;
$message = $ex->getMessage();
throw new DBException($message);
}
}
static function removeFollower($followerId, $followingId)
{
$dbh = NULL;
try {
$sql1 = " delete from sc_follow where follower_id = :follower_id ";
$sql1 .= " and following_id = :following_id ";
$dbh = PDOWrapper::getHandle();
//Tx start
$dbh->beginTransaction();
$stmt1 = $dbh->prepare($sql1);
$stmt1->bindParam(":follower_id", $followerId);
$stmt1->bindParam(":following_id", $followingId);
$stmt1->execute();
$stmt1 = NULL;
$sql2 = " insert into sc_activity(owner_id,subject_id,subject,object_id, ";
$sql2 .= " object,verb, verb_name, op_bit, created_on) ";
$sql2 .= " values(:owner_id, :subject_id, :subject, :object_id, ";
$sql2 .= " :object, :verb, :verb_name, :op_bit, now()) ";
$verb = AppConstants::UNFOLLOW_VERB;
$op_bit = 0;
$verbName = AppConstants::STR_UNFOLLOW;
$ownerId = -1;
$subject = "_NA_";
$object = "_NA_";
$stmt2 = $dbh->prepare($sql2);
$stmt2->bindParam(":owner_id", $ownerId);
$stmt2->bindParam(":subject_id", $followerId);
$stmt2->bindParam(":object_id", $followingId);
$stmt2->bindParam(":subject", $subject);
$stmt2->bindParam(":object", $object);
$stmt2->bindParam(":verb", $verb);
$stmt2->bindParam(":verb_name", $verbName);
$stmt2->bindParam(":op_bit", $op_bit);
$stmt2->execute();
$stmt2 = NULL;
//Tx end
$dbh->commit();
$dbh = null;
} catch (\PDOException $e) {
$dbh->rollBack();
$dbh = null;
throw new DBException($e->getMessage(), $e->getCode());
} catch (\Exception $ex) {
$dbh->rollBack();
$dbh = null;
$message = $ex->getMessage();
throw new DBException($message);
}
}
static function set_bu_bit($loginId, $value, $sessionId)
{
$dbh = NULL;
try {
$sql1 = "update sc_denorm_user set updated_on = now(), bu_bit = :value ";
$sql1 .= " where login_id = :login_id";
$dbh = PDOWrapper::getHandle();
//Tx start
$dbh->beginTransaction();
$stmt1 = $dbh->prepare($sql1);
$stmt1->bindParam(":login_id", $loginId);
$stmt1->bindParam(":value", $value);
$stmt1->execute();
$stmt1 = NULL;
if (!empty($sessionId)) {
//clear banned user session immediately!
$sql2 = "delete from sc_php_session where session_id = :session_id ";
$stmt2 = $dbh->prepare($sql2);
$stmt2->bindParam(":session_id", $sessionId);
$stmt2->execute();
$stmt2 = NULL;
}
//Tx end
$dbh->commit();
$dbh = null;
} catch (\PDOException $e) {
$dbh->rollBack();
$dbh = null;
throw new DBException($e->getMessage(), $e->getCode());
} catch (\Exception $ex) {
$dbh->rollBack();
$dbh = null;
$message = $ex->getMessage();
throw new DBException($message);
}
}
static function addRow($ownerId, $subjectId, $objectId, $subject, $object, $verb, $content)
{
$dbh = NULL;
try {
$dbh = PDOWrapper::getHandle();
//Tx start
$dbh->beginTransaction();
$sql = " insert into sc_activity(owner_id,subject_id,subject,object_id, ";
$sql .= " object,verb, verb_name, op_bit, content, created_on) ";
$sql .= " values(:owner_id, :subject_id, :subject, :object_id, ";
$sql .= " :object, :verb, :verb_name, :op_bit, :content, now()) ";
$op_bit = 0;
$verbName = NULL;
switch ($verb) {
case AppConstants::LIKE_VERB:
$verbName = AppConstants::STR_LIKE;
break;
case AppConstants::SAVE_VERB:
$verbName = AppConstants::STR_SAVE;
break;
case AppConstants::COMMENT_VERB:
$verbName = AppConstants::STR_COMMENT;
break;
case AppConstants::FOLLOW_VERB:
$verbName = AppConstants::STR_FOLLOW;
break;
case AppConstants::UNFOLLOW_VERB:
$verbName = AppConstants::STR_UNFOLLOW;
break;
case AppConstants::POST_VERB:
$verbName = AppConstants::STR_POST;
break;
default:
$message = "Unknown activity verb : aborting! ";
trigger_error($message, E_USER_ERROR);
}
$stmt = $dbh->prepare($sql);
$stmt->bindParam(":owner_id", $ownerId);
$stmt->bindParam(":subject_id", $subjectId);
$stmt->bindParam(":object_id", $objectId);
$stmt->bindParam(":subject", $subject);
$stmt->bindParam(":object", $object);
$stmt->bindParam(":verb", $verb);
$stmt->bindParam(":verb_name", $verbName);
$stmt->bindParam(":op_bit", $op_bit);
$stmt->bindParam(":content", $content);
$stmt->execute();
$stmt = NULL;
//Tx end
$dbh->commit();
$dbh = null;
} catch (\PDOException $e) {
$dbh->rollBack();
$dbh = null;
throw new DBException($e->getMessage(), $e->getCode());
} catch (\Exception $ex) {
$dbh->rollBack();
$dbh = null;
$message = $ex->getMessage();
throw new DBException($message);
}
}
static function add($ownerId, $subjectId, $subject, $objectId, $objectType, $title, $verb)
{
$dbh = NULL;
try {
//@todo column object should be renamed to object_type
//@todo column object_title should be renamed to object
// insert into sc_bookmark, adjust counters via trigger
$sql1 = " insert into sc_bookmark(owner_id,subject_id,subject,object_id, ";
$sql1 .= " object, object_title, verb,created_on) ";
$sql1 .= " values(:owner_id, :subject_id, :subject, :object_id, :object_type, ";
$sql1 .= " :object, :verb, now()) ";
$dbh = PDOWrapper::getHandle();
//Tx start
$dbh->beginTransaction();
$stmt1 = $dbh->prepare($sql1);
$stmt1->bindParam(":owner_id", $ownerId);
$stmt1->bindParam(":subject_id", $subjectId);
$stmt1->bindParam(":object_id", $objectId);
$stmt1->bindParam(":subject", $subject);
$stmt1->bindParam(":object", $title);
$stmt1->bindParam(":object_type", $objectType);
$stmt1->bindParam(":verb", $verb);
$stmt1->execute();
$stmt1 = NULL;
$sql2 = " insert into sc_activity(owner_id,subject_id,subject,object_id, ";
$sql2 .= " object,verb, verb_name, op_bit, created_on) ";
$sql2 .= " values(:owner_id, :subject_id, :subject, :object_id, ";
$sql2 .= " :object, :verb, :verb_name, :op_bit, now()) ";
$verb = AppConstants::LIKE_VERB;
$op_bit = 0;
$verbName = AppConstants::STR_LIKE;
$stmt2 = $dbh->prepare($sql2);
$stmt2->bindParam(":owner_id", $ownerId);
$stmt2->bindParam(":subject_id", $subjectId);
$stmt2->bindParam(":object_id", $objectId);
$stmt2->bindParam(":subject", $subject);
$stmt2->bindParam(":object", $title);
$stmt2->bindParam(":verb", $verb);
$stmt2->bindParam(":verb_name", $verbName);
$stmt2->bindParam(":op_bit", $op_bit);
$stmt2->execute();
$stmt2 = NULL;
//Tx end
$dbh->commit();
$dbh = null;
} catch (\PDOException $e) {
$dbh->rollBack();
$dbh = null;
throw new DBException($e->getMessage(), $e->getCode());
} catch (\Exception $ex) {
$dbh->rollBack();
$dbh = null;
$message = $ex->getMessage();
throw new DBException($message);
}
}
static function deleteItems($loginId, $listId, $itemIds)
{
//input
settype($loginId, "integer");
settype($listId, "integer");
if (empty($itemIds)) {
return;
}
try {
$dbh = PDOWrapper::getHandle();
// *** Tx start ***
$dbh->beginTransaction();
// #1 : delete items
$sqlt = " delete from sc_list_item where list_id = %d and item_id = %d ";
foreach ($itemIds as $itemId) {
settype($itemId, "integer");
$sql = sprintf($sqlt, $listId, $itemId);
//fire SQL statement
$dbh->exec($sql);
}
// #2: get items_json within this Tx
$sql2 = " select post.id, post.images_json from sc_post post, sc_list_item li ";
$sql2 .= " where li.item_id = post.id and li.list_id = %d limit 4 ";
$sql2 = sprintf($sql2, $listId);
$stmt2 = $dbh->prepare($sql2);
$stmt2->execute();
$rows = $stmt2->fetchAll();
$stmt2->closeCursor();
$stmt2 = NULL;
$bucket = array();
foreach ($rows as $row) {
$itemId = $row["id"];
$json = $row["images_json"];
$images = json_decode($json);
if (!empty($images) && sizeof($images) > 0) {
$image = $images[0];
$imgv = \com\indigloo\sc\html\Post::convertImageJsonObj($image);
$view = new \stdClass();
$view->id = $row["id"];
$view->thumbnail = $imgv["thumbnail"];
array_push($bucket, $view);
}
}
$items_json = json_encode($bucket);
if ($items_json === FALSE || $items_json == NULL) {
$items_json = '[]';
$errorMsg = sprintf(" json encode error : list delete : id :: %d", $listId);
Logger::getInstance()->error($errorMsg);
}
// #3 : update list.id.item_count and list.id.items_json
$sql3 = " update sc_list set items_json = :items_json, ";
$sql3 .= " item_count = (select count(id) from sc_list_item where list_id = :list_id)";
$sql3 .= " where id = :list_id ";
$stmt3 = $dbh->prepare($sql3);
$stmt3->bindParam(":list_id", $listId);
$stmt3->bindParam(":items_json", $items_json);
$stmt3->execute();
$stmt3 = NULL;
// **** Tx end ****
$dbh->commit();
$dbh = null;
} catch (\PDOException $e) {
$dbh->rollBack();
$dbh = null;
throw new DBException($e->getMessage(), $e->getCode());
} catch (\Exception $ex) {
$dbh->rollBack();
$dbh = null;
throw new DBException($ex->getMessage(), $ex->getCode());
}
}
static function process($postId, $loginId, $version, $catCode, $group_slug)
{
//sanitize input
settype($postId, "integer");
settype($loginId, "integer");
settype($version, "integer");
$sqlm1 = "insert ignore into sc_group_master(token,name,cat_code,created_on) values('%s','%s','%s',now()) ";
$sqlm2 = "insert ignore into sc_user_group(login_id,token,name,created_on) values('%d','%s', '%s', now()) ";
$sqlm3 = "update sc_site_tracker set group_flag = 1 where post_id = %d and version = %d ";
$dbh = NULL;
try {
$dbh = PDOWrapper::getHandle();
//Tx start
$dbh->beginTransaction();
$slugs = explode(Constants::SPACE, $group_slug);
foreach ($slugs as $slug) {
if (Util::tryEmpty($slug)) {
continue;
}
//do processing
$name = \com\indigloo\util\StringUtil::convertKeyToName($slug);
$sql = sprintf($sqlm1, $slug, $name, $catCode);
$dbh->exec($sql);
$sql = sprintf($sqlm2, $loginId, $slug, $name);
$dbh->exec($sql);
}
//All group slugs for post processed
$sql = sprintf($sqlm3, $postId, $version);
$dbh->exec($sql);
//Tx end
$dbh->commit();
$dbh = null;
} catch (\PDOException $e) {
$dbh->rollBack();
$dbh = null;
throw new DBException($e->getMessage(), $e->getCode());
} catch (\Exception $ex) {
$dbh->rollBack();
$dbh = null;
$message = $ex->getMessage();
throw new DBException($message);
}
}
static function create($title, $description, $loginId, $name, $linksJson, $imagesJson, $groupSlug, $categoryCode)
{
$dbh = NULL;
try {
$sql1 = " insert into sc_post(title,description,login_id,links_json, ";
$sql1 .= " images_json,group_slug,cat_code, pseudo_id,created_on) ";
$sql1 .= " values (:title,:description,:login_id,:links_json,:images_json, ";
$sql1 .= " :group_slug, :cat_code, :pseudo_id, now()) ";
$dbh = PDOWrapper::getHandle();
//Tx start
$dbh->beginTransaction();
//insert into sc_post, change counters via trigger
$stmt1 = $dbh->prepare($sql1);
$stmt1->bindParam(":title", $title);
$stmt1->bindParam(":description", $description);
$stmt1->bindParam(":login_id", $loginId);
$stmt1->bindParam(":links_json", $linksJson);
$stmt1->bindParam(":images_json", $imagesJson);
$stmt1->bindParam(":group_slug", $groupSlug);
$stmt1->bindParam("cat_code", $categoryCode);
// @see http://drupal.org/node/1369332
// pseudo_id is part of a UNIQUE index and mysql has to lock
// the index attached to pseudo_id if we do not insert anything
// NULL not being comparable to anything, it doesn't participate
// in uniqueness constraints and MySQL doesn't have to lock the index.
// $pseudoId = NULL ;
// $stmt1->bindParam(":pseudo_id", $pseudoId);
$stmt1->bindValue(":pseudo_id", null, \PDO::PARAM_STR);
$stmt1->execute();
$stmt1 = NULL;
$postId = $dbh->lastInsertId();
settype($postId, "integer");
$itemId = PseudoId::encode($postId);
if (strlen($itemId) > 32) {
throw new DBException("exceeds pseudo_id column size of 32");
}
$sql2 = "update sc_post set pseudo_id = :item_id where id = :post_id ";
$stmt2 = $dbh->prepare($sql2);
$stmt2->bindParam(":item_id", $itemId);
$stmt2->bindParam(":post_id", $postId);
$stmt2->execute();
$stmt2 = NULL;
$sql3 = " insert into sc_activity(owner_id,subject_id,subject,object_id, ";
$sql3 .= " object,verb, verb_name, op_bit, created_on) ";
$sql3 .= " values(:owner_id, :subject_id, :subject, :object_id, ";
$sql3 .= " :object, :verb, :verb_name, :op_bit, now()) ";
$verb = AppConstants::POST_VERB;
$op_bit = 0;
$verbName = AppConstants::STR_POST;
$stmt3 = $dbh->prepare($sql3);
$stmt3->bindParam(":owner_id", $loginId);
$stmt3->bindParam(":subject_id", $loginId);
$stmt3->bindParam(":object_id", $itemId);
$stmt3->bindParam(":subject", $name);
$stmt3->bindParam(":object", $title);
$stmt3->bindParam(":verb", $verb);
$stmt3->bindParam(":verb_name", $verbName);
$stmt3->bindParam(":op_bit", $op_bit);
$stmt3->execute();
$stmt3 = NULL;
//Tx end
$dbh->commit();
$dbh = null;
return $itemId;
} catch (\PDOException $e) {
$dbh->rollBack();
$dbh = null;
throw new DBException($e->getMessage(), $e->getCode());
} catch (\Exception $ex) {
$dbh->rollBack();
$dbh = null;
throw new DBException($ex->getMessage());
}
}
