function detect_bad_utf8($mysqli, $flag = false)
{
$sql = "select max(id) as total from sc_post ";
$row = MySQL\Helper::fetchRow($mysqli, $sql);
$total = $row["total"];
$pageSize = 50;
$pages = ceil($total / $pageSize);
$count = 0;
while ($count <= $pages) {
$start = $count * $pageSize + 1;
$end = $start + ($pageSize - 1);
$sql = " select pseudo_id,title,description from sc_post where (id <= {end}) and (id >= {start} ) ";
$sql = str_replace(array("{end}", "{start}"), array(0 => $end, 1 => $start), $sql);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
printf("processing row between %d and %d \n", $start, $end);
foreach ($rows as $row) {
$description = $row['description'];
if (!Util::isUtf8($description)) {
printf("Bad utf-8 for item - %s \n", $row['pseudo_id']);
if ($flag) {
$clean_description = Util::filterBadUtf8($description);
$clean_title = Util::filterBadUtf8($row['title']);
update_clean_utf8($mysqli, $row['pseudo_id'], $clean_title, $clean_description);
}
}
}
sleep(1);
$count++;
}
}
static function getUserCounters($loginId)
{
settype($loginId, "integer");
$mysqli = MySQL\Connection::getInstance()->getHandle();
$sql = "select * from sc_user_counter where login_id = %d ";
$sql = sprintf($sql, $loginId);
$row = MySQL\Helper::fetchRow($mysqli, $sql);
return $row;
}
static function getOnHash($hash)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
$hash = $mysqli->real_escape_string($hash);
$sql = " select * from sc_site_master where hash = '%s' ";
$sql = sprintf($sql, $hash);
$row = MySQL\Helper::fetchRow($mysqli, $sql);
return $row;
}
static function getOnLoginId($loginId)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
settype($loginId, "integer");
$sql = " select * from sc_user where login_id = %d ";
$sql = sprintf($sql, $loginId);
$row = MySQL\Helper::fetchRow($mysqli, $sql);
return $row;
}
static function getOnId($commentId)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
settype($commentId, "integer");
$sql = " select a.*,l.name as user_name from sc_comment a,sc_login l ";
$sql .= " where l.id = a.login_id and a.id = %d ";
$sql = sprintf($sql, $commentId);
$row = MySQL\Helper::fetchRow($mysqli, $sql);
return $row;
}
static function find($followerId, $followingId)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
settype($followerId, "integer");
settype($followingId, "integer");
$sql = " select count(id) as count from sc_follow";
$sql .= " where follower_id = %d and following_id = %d ";
$sql = sprintf($sql, $followerId, $followingId);
$row = MySQL\Helper::fetchRow($mysqli, $sql);
return $row;
}
static function getOnTwitterId($twitterId)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
$twitterId = $mysqli->real_escape_string($twitterId);
if (strlen($twitterId) > 64) {
trigger_error("Twitter id is longer than 64 chars", E_USER_ERROR);
}
$sql = " select * from sc_twitter where twitter_id = '%s' ";
$sql = sprintf($sql, $twitterId);
$row = MySQL\Helper::fetchRow($mysqli, $sql);
return $row;
}
static function find($subjectId, $objectId, $verb)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
settype($subjectId, "integer");
settype($objectId, "integer");
settype($verb, "integer");
$sql = " select count(id) as count from sc_bookmark ";
$sql .= " where subject_id = %d and object_id = %d and verb = %d ";
$sql = sprintf($sql, $subjectId, $objectId, $verb);
$row = MySQL\Helper::fetchRow($mysqli, $sql);
return $row;
}
static function isPending($email)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
$email = $mysqli->real_escape_string($email);
// if we had raised as request in last 20 minutes
// then we already have a mail request pending and next
// attempt should be made after some time
$sql = " select count(id) as count from sc_mail_queue where email = '%s' ";
$sql .= " and (created_on > now() - INTERVAL 20 MINUTE) ";
$sql = sprintf($sql, $email);
$row = MySQL\Helper::fetchRow($mysqli, $sql);
return $row;
}
static function login($tableName, $email, $password)
{
// signal successful login by returning a valid loginId
// otherwise throw an exception
// caller should handle this exception
$loginId = NULL;
if (empty($tableName)) {
trigger_error("User Table name is not supplied", E_USER_ERROR);
exit(1);
}
$mysqli = MySQL\Connection::getInstance()->getHandle();
$password = trim($password);
$email = trim($email);
// change empty password - for time resistant attacks
if (empty($password)) {
$password = "******";
}
$sql = " select * from {table} where is_active = 1 and email = '" . $email . "' ";
$sql = str_replace("{table}", $tableName, $sql);
$row = MySQL\Helper::fetchRow($mysqli, $sql);
if (!empty($row)) {
$dbSalt = $row['salt'];
$dbPassword = $row['password'];
// compute the digest using form password and db Salt
$message = $password . $dbSalt;
$computedDigest = sha1($message);
$outcome = strcmp($dbPassword, $computedDigest);
//good password
// get and return loginId
if ($outcome == 0) {
$loginId = $row["login_id"];
//set user data in session
$udata = array();
$udata["is_admin"] = $row["is_admin"];
$udata["is_staff"] = $row["is_staff"];
$udata["login_id"] = $loginId;
$_SESSION[self::USER_DATA] = $udata;
}
}
if (empty($loginId) || is_null($loginId)) {
throw new \com\indigloo\exception\DBException("wrong email or password", 401);
}
return $loginId;
}
function run_diagnostic($mysqli, $flag = false)
{
$sql = "select max(id) as total from sc_post ";
$row = MySQL\Helper::fetchRow($mysqli, $sql);
$total = $row["total"];
$pageSize = 50;
$pages = ceil($total / $pageSize);
$count = 0;
while ($count <= $pages) {
$start = $count * $pageSize + 1;
$end = $start + ($pageSize - 1);
$sql = " select pseudo_id,created_on,images_json from sc_post where (id <= {end}) and (id >= {start} ) ";
$sql = str_replace(array("{end}", "{start}"), array(0 => $end, 1 => $start), $sql);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
foreach ($rows as $row) {
$images = json_decode($row["images_json"]);
foreach ($images as $image) {
detect_bad($row["pseudo_id"], $row["created_on"], $image);
}
}
$count++;
}
}
function load_in_redis($mysqli)
{
$sql = "select max(id) as total from sc_activity ";
$row = MySQL\Helper::fetchRow($mysqli, $sql);
$total = $row["total"];
$pageSize = 50;
$pages = ceil($total / $pageSize);
$count = 0;
$activityDao = new \com\indigloo\sc\dao\Activity();
while ($count <= $pages) {
$start = $count * $pageSize + 1;
$end = $start + ($pageSize - 1);
$sql = " select * from sc_activity where (id <= {end}) and (id >= {start} ) ";
$sql = str_replace(array("{end}", "{start}"), array(0 => $end, 1 => $start), $sql);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
foreach ($rows as $row) {
$activityDao->pushToRedis($row);
}
printf("processed rows between %s and %s \n", $start, $end);
flush();
sleep(1);
$count++;
}
}
$start = $count * 20;
$end = $start + 20;
$sql = " select id,images_json from sc_post where (id <= {end}) and (id >= {start} ) ";
$sql = str_replace(array("{end}", "{start}"), array(0 => $end, 1 => $start), $sql);
$mysqli = MySQL\Connection::getInstance()->getHandle();
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
foreach ($rows as $row) {
printf("processing row id %d \n", $row['id']);
$images = json_decode($row["images_json"]);
$data = array();
foreach ($images as $image) {
printf("processing image id %d \n", $image->id);
//load media DB row
// update sc_post.id with new media DB Row
$sql = " select * from sc_media where id = " . $image->id;
$mediaDBRow = MySQL\Helper::fetchRow($mysqli, $sql);
$mediaVO = \com\indigloo\media\Data::create($mediaDBRow);
array_push($data, $mediaVO);
}
$strMediaVO = json_encode($data);
updateItem($mysqli, $row['id'], $strMediaVO);
sleep(1);
}
$count++;
}
function updateItem($mysqli, $postId, $strMediaVO)
{
$updateSQL = " update sc_post set images_json = ? where id = ? ";
$stmt = $mysqli->prepare($updateSQL);
if ($stmt) {
$stmt->bind_param("si", $strMediaVO, $postId);
static function getOnId($listId)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
settype($listId, "int");
$sql = " select * from sc_list where id = %d ";
$sql = sprintf($sql, $listId);
$row = MySQL\Helper::fetchRow($mysqli, $sql);
return $row;
}
static function glget($key)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
$sql = "select t_value from sc_glob_table where t_key = '%s'";
$sql = sprintf($sql, $key);
$row = MySQL\Helper::fetchRow($mysqli, $sql);
return $row;
}
static function getTotal($filters)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
$sql = " select count(id) as count from sc_denorm_user ";
$q = new MySQL\Query($mysqli);
$q->filter($filters);
$condition = $q->get();
$sql .= $condition;
$row = MySQL\Helper::fetchRow($mysqli, $sql);
return $row;
}
if (empty($row["email"])) {
return;
}
$fname = empty($row["first_name"]) ? "_EMPTY_" : $row["first_name"];
$lname = empty($row["last_name"]) ? "_EMPTY_" : $row["last_name"];
$email = $row["email"];
$buffer = sprintf("%s,%s,%s \n", $fname, $lname, $email);
fwrite($fhandle, $buffer);
}
// + start
$mysqli = \com\indigloo\mysql\Connection::getInstance()->getHandle();
$ufile = "3mik-users.csv";
$fhandle = fopen($ufile, "w");
fwrite($fhandle, "first name,last name,email \n");
$sql = "select count(id) as total from sc_denorm_user ";
$row = MySQL\Helper::fetchRow($mysqli, $sql);
$total = $row["total"];
$pageSize = 50;
$pages = ceil($total / $pageSize);
$count = 0;
while ($count <= $pages) {
$start = $count * $pageSize + 1;
$end = $start + ($pageSize - 1);
$sql = " select * from sc_denorm_user where (id <= {end}) and (id >= {start} ) ";
$sql = str_replace(array("{end}", "{start}"), array(0 => $end, 1 => $start), $sql);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
printf("processing user rows between %d and %d \n", $start, $end);
foreach ($rows as $row) {
write_csv_row($fhandle, $row);
}
$count++;
function post_to_activity($mysqli)
{
$sql = "select max(id) as total from sc_post";
$row = MySQL\Helper::fetchRow($mysqli, $sql);
$total = $row["total"];
$pageSize = 50;
$pages = ceil($total / $pageSize);
$count = 0;
$userDao = new \com\indigloo\sc\dao\User();
$activityDao = new \com\indigloo\sc\dao\Activity();
while ($count <= $pages) {
$start = $count * $pageSize + 1;
$end = $start + ($pageSize - 1);
$sql = " select * from sc_post where (id <= {end}) and (id >= {start} ) ";
$sql = str_replace(array("{end}", "{start}"), array(0 => $end, 1 => $start), $sql);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
foreach ($rows as $row) {
$subjectId = $row['login_id'];
$ownerId = $row['login_id'];
$postId = $row['id'];
$objectId = PseudoId::encode($postId);
$userDBRow = $userDao->getOnLoginId($subjectId);
$subject = $userDBRow['name'];
$object = $row['title'];
$object = Util::filterBadUtf8($object);
$verb = \com\indigloo\sc\Constants::POST_VERB;
$activityDao->addRow($ownerId, $subjectId, $objectId, $subject, $object, $verb);
}
flush();
sleep(1);
$count++;
}
}
