static function getPaged($start, $direction, $limit, $filters)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
settype($start, "integer");
settype($limit, "integer");
$direction = $mysqli->real_escape_string($direction);
$sql = " select a.*,l.name as user_name from sc_comment a,sc_login l ";
$q = new MySQL\Query($mysqli);
$q->setAlias("com\\indigloo\\sc\\model\\Comment", "a");
//raw condition
$q->addCondition("l.id = a.login_id");
$q->filter($filters);
$sql .= $q->get();
$sql .= $q->getPagination($start, $direction, "a.id", $limit);
if (Config::getInstance()->is_debug()) {
Logger::getInstance()->debug("sql => {$sql} \n");
}
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
//reverse rows for 'before' direction
if ($direction == 'before') {
$results = array_reverse($rows);
return $results;
}
return $rows;
}
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 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 getTmpPSData($postId)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
settype($postId, "integer");
$sql = " select * from sc_tmp_ps where post_id = %d ";
$sql = sprintf($sql, $postId);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
return $rows;
}
static function getLatest($limit)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
settype($limit, "integer");
$sql = " select * from sc_login order by id desc limit %d ";
$sql = sprintf($sql, $limit);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
return $rows;
}
function process_groups($mysqli)
{
//process groups
$sql = " select post_id from sc_site_tracker where group_flag = 0 order by id desc limit 50";
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
$groupDao = new \com\indigloo\sc\dao\Group();
foreach ($rows as $row) {
$postId = $row["post_id"];
$groupDao->process($postId);
}
}
static function getFollowers($loginId, $limit)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
settype($loginId, "integer");
settype($limit, "integer");
$sql = " select u.name, u.login_id, u.photo_url from sc_denorm_user u, ";
$sql .= " sc_follow s where u.login_id = s.follower_id and s.following_id = %d limit %d ";
$sql = sprintf($sql, $loginId, $limit);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
return $rows;
}
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 getPaged($start, $direction, $limit)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
settype($start, "integer");
settype($limit, "integer");
$direction = $mysqli->real_escape_string($direction);
$sql = " select f.* from sc_feedback f ";
$q = new MySQL\Query($mysqli);
$sql .= $q->getPagination($start, $direction, "f.id", $limit);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
//reverse rows for 'before' direction
if ($direction == 'before') {
$results = array_reverse($rows);
return $results;
}
return $rows;
}
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;
}
static function deleteOnId($mediaId)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
settype($mediaId, "integer");
$sql = " delete from sc_media where id = ? ";
MySQL\Helper::executeSQL($mysqli, $sql);
$stmt = $mysqli->prepare($sql);
if ($stmt) {
$stmt->bind_param("i", $mediaId);
$stmt->execute();
if ($mysqli->affected_rows != 1) {
MySQL\Error::handle($stmt);
}
$stmt->close();
} else {
MySQL\Error::handle($mysqli);
}
}
static function capture($emails, $message)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
$sql = " insert into sc_email_capture(email,message,created_on) values ";
//sanitize message
$message = $mysqli->real_escape_string($message);
$isize = sizeof($emails);
if ($isize == 0) {
return;
}
for ($index = 0; $index < $isize; $index++) {
//last one?
$suffix = $index == $isize - 1 ? "" : ",";
$email = $emails[$index];
//sanitize email
$email = $mysqli->real_escape_string($email);
$sql .= sprintf(" ('%s','%s',now())%s ", $email, $message, $suffix);
}
MySQL\Helper::executeSQL($mysqli, $sql);
}
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++;
}
}
static function getLikeOnItemId($itemId)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
settype($itemId, "integer");
//@todo change UI if num_likes > 10
$sql = " select l.id as login_id, l.name as user_name ";
$sql .= " from sc_bookmark b , sc_login l ";
$sql .= " where b.subject_id = l.id and b.verb = 1 and b.object_id = %d limit 10 ";
$sql = sprintf($sql, $itemId);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
return $rows;
}
function process_activities($mysqli)
{
/*
* process activities data
* @imp activities should be brought in the order that they have happened
* so sort on id ASC
*/
$sql = " select * from sc_activity where op_bit = 0 order by id limit 50";
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
$activityDao = new \com\indigloo\sc\dao\Activity();
foreach ($rows as $row) {
try {
$sql2 = " update sc_activity set op_bit = 1 where id = " . $row["id"];
$feed = $activityDao->pushToRedis($row);
$activityDao->sendMail($row, $feed);
//flip the op_bit for this activity
MySQL\Helper::executeSQL($mysqli, $sql2);
} catch (\Exception $ex) {
Logger::getInstance()->error($ex->getMessage());
}
}
}
static function getPagedOnCategory($start, $direction, $limit, $code)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
$code = $mysqli->real_escape_string($code);
$direction = $mysqli->real_escape_string($direction);
settype($start, "integer");
settype($limit, "integer");
$sql = " select q.*,l.name as user_name from sc_post q,sc_login l ";
$codeCondition = sprintf("cat_code = '%s'", $code);
$q = new MySQL\Query($mysqli);
$q->addCondition("l.id = q.login_id");
$q->addCondition($codeCondition);
$sql .= $q->get();
$sql .= $q->getPagination($start, $direction, "q.id", $limit);
if (Config::getInstance()->is_debug()) {
Logger::getInstance()->debug("sql => {$sql} \n");
}
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
//reverse rows for 'before' direction
if ($direction == 'before') {
$results = array_reverse($rows);
return $results;
}
return $rows;
}
function getQuorum($index, $token, $hits, $offset, $limit)
{
if (Util::tryEmpty($token)) {
return array();
}
Util::isEmpty("index", $index);
$sql = sprintf("select id from %s where match('", $index);
$sql .= '\\"' . $token . '\\"\\/' . $hits . "')";
$sql .= sprintf(" limit %d,%d ", $offset, $limit);
$rows = MySQL\Helper::fetchRows($this->connx, $sql);
$ids = array();
foreach ($rows as $row) {
array_push($ids, $row["id"]);
}
return $ids;
}
static function getPagedUserGroups($start, $direction, $limit, $filters)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
$sql = "select ug.* from sc_user_group ug";
//sanitize input
settype($start, "integer");
settype($limit, "integer");
$direction = $mysqli->real_escape_string($direction);
$q = new MySQL\Query($mysqli);
$q->setAlias("com\\indigloo\\sc\\model\\Group", "ug");
$q->filter($filters);
$sql .= $q->get();
$sql .= $q->getPagination($start, $direction, "ug.id", $limit);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
//reverse rows for 'before' direction
if ($direction == 'before') {
$results = array_reverse($rows);
return $results;
}
return $rows;
}
<?php
include 'sc-app.inc';
include APP_CLASS_LOADER;
use com\indigloo\mysql as MySQL;
use com\indigloo\Configuration as Config;
use com\indigloo\sc\util\PseudoId;
$mysqli = MySQL\Connection::getInstance()->getHandle();
$sql = " select max(id) as max_id from sc_post ";
$row = MySQL\Helper::fetchRow($mysqli, $sql);
$maxId = $row['max_id'];
echo "Max id = {$maxId} \n";
for ($i = 1; $i <= $maxId; $i++) {
$ei = PseudoId::encode($i);
update($mysqli, $i, $ei);
}
function update($mysqli, $x, $ex)
{
$sql = "update sc_post set pseudo_id = ? where id = ? ";
$stmt = $mysqli->prepare($sql);
if ($stmt) {
$stmt->bind_param("ss", $ex, $x);
$stmt->execute();
$stmt->close();
}
}
$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 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 getPagedItems($start, $direction, $limit, $filters)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
settype($start, "integer");
settype($limit, "integer");
$direction = $mysqli->real_escape_string($direction);
$sql = " select login.name as user_name, post.*, li.item_id, li.id as sort_id " . " from sc_list_item li " . " left join sc_post post on li.item_id = post.id " . " left join sc_login login on login.id = post.login_id ";
$q = new MySQL\Query($mysqli);
$q->setAlias("com\\indigloo\\sc\\model\\ListItem", "li");
$q->filter($filters);
$condition = $q->get();
$sql .= $condition;
$sql .= $q->getPagination($start, $direction, "li.id", $limit);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
//reverse rows for 'before' direction
if ($direction == 'before') {
$results = array_reverse($rows);
return $results;
}
return $rows;
}
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;
}
$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++;
}
//close resources
$mysqli->close();
fclose($fhandle);
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++;
}
}
