public static function load_many_from_query($sql, $args)
{
$sth = Dal::query($sql, $args);
$items = array();
while ($r = Dal::row_assoc($sth)) {
$rev = new Review();
$rev->load_from_row($r);
$items[] = $rev;
}
return $items;
}
static function get($genre, $language)
{
$r = Dal::query("SELECT * FROM {oneliners} WHERE is_active=1 AND genre=? AND language=?", array($genre, $language));
if (!$r) {
return NULL;
}
$oneliners = array();
while ($ret = Dal::row_assoc($r)) {
$oneliners[$ret['rating']][] = $ret['body'];
}
return $oneliners;
}
public static function get_network_by_address($network_name)
{
if ($network_name == 'www' || empty($network_name)) {
return self::get_mothership_info();
}
$sth = Dal::query("SELECT * FROM {networks} WHERE is_active=1 AND address=?", array($network_name));
return self::from_array(Dal::row_assoc($sth));
}
public static function get_network_by_address($network_name)
{
$sth = Dal::query("SELECT * FROM {networks} WHERE is_active=1 AND address=?", array($network_name));
return Network::from_array(Dal::row_assoc($sth));
}
public static function get_next_id_for_user($uid)
{
$entity_ids = array();
$r = Dal::query("SELECT entity_id, attribute_value AS user_id\n FROM {entities} AS E\n LEFT OUTER JOIN {entityattributes} AS EA ON E.id = EA.id\n WHERE entity_service='internal' AND entity_type='points'\n AND attribute_name='user_id'\n AND attribute_value=?", array($uid));
while ($points = Dal::row_assoc($r)) {
$entity_ids[] = preg_replace("/^(\\w+:)/", '', $points['entity_id']);
}
sort($entity_ids);
// echo "UID: $uid<pre>" . print_r($entity_ids, 1) . "</pre>";
$last = array_pop($entity_ids) + 1;
return "{$uid}:" . (string) $last;
}
static function get_all_comments($uid = 0, $show = 'ALL', $page = 1, $sort_by = 'created', $direction = 'DESC')
{
if ($show == 'ALL') {
$limit = "";
} else {
$limit = "LIMIT " . ($page - 1) * $show . ", {$show}";
}
$select_fields = "ct.collection_id ct_collection_id, ct.author_id ct_author_id, ct.title comment_title";
foreach (array("comment_id", "content_id", "created", "ip_addr", "name", "email", "homepage", "subject", "comment", "akismet_spam", "spam_state", "user_id") as $field) {
$select_fields .= ", cmt.{$field} {$field}";
}
if (!$uid) {
// selecting from all active comments in the system
$sth = Dal::query("SELECT {$select_fields}\n FROM {comments} cmt\n LEFT JOIN {contents} ct ON ct.content_id = cmt.content_id\n WHERE cmt.is_active = 1\n ORDER BY {$sort_by} {$direction}\n {$limit}");
} else {
// selecting comments that are administerable by the given user
$sth = Dal::query("(SELECT {$select_fields}\n FROM {contents} ct /*FORCE KEY(content_for_user)*/\n LEFT JOIN {comments} cmt ON ct.content_id = cmt.content_id /*FORCE KEY(comments_for_content)*/\n WHERE ct.collection_id = -1\n AND ct.author_id = ?\n AND ct.is_active = 1\n AND cmt.is_active = 1)\n UNION\n (SELECT {$select_fields}\n FROM {groups_users} gu /*FORCE KEY(groups_for_user)*/\n LEFT JOIN {contents} ct ON gu.group_id = ct.collection_id /*FORCE KEY(content_for_collection)*/\n LEFT JOIN {comments} cmt ON cmt.content_id = ct.content_id /*FORCE KEY(comments_for_content)*/\n WHERE gu.user_id = ?\n AND gu.user_type = 'owner'\n AND ct.is_active = 1\n AND cmt.is_active = 1)\n ORDER BY {$sort_by} {$direction}\n {$limit}", array($uid, $uid));
}
$cmts = array();
while ($r = Dal::row_assoc($sth)) {
$cmts[] = $r;
}
$sth->free();
return $cmts;
}
public static function load_attributes($id)
{
$r = Dal::query("SELECT * FROM {entityrelationattributes}\n \tWHERE id=?", array($id));
if (!$r) {
return NULL;
}
// get attributes
$relation_attributes = array();
while ($row = Dal::row_assoc($r)) {
$relation_attributes[$row['attribute_name']] = array('value' => $row['attribute_value'], 'permission' => $row['attribute_permission']);
}
return $relation_attributes;
}
function explain_query($sql, $args, $query_time)
{
if (!preg_match("/^\\s*SELECT/i", $sql)) {
return;
}
echo "================================================================================\n";
echo sprintf("%.04f s | SQL: %s\n", $query_time, $sql);
global $_timed_queries;
$_timed_queries[$sql][] = $query_time;
$explain = Dal::query("EXPLAIN {$sql}", $args);
$tables = array();
while ($r = Dal::row_assoc($explain)) {
if (!empty($r['table'])) {
$tables[] = $r['table'];
}
echo "\n";
foreach ($r as $k => $v) {
echo sprintf("%15s: %s\n", $k, $v);
}
}
foreach ($tables as $table) {
echo "--------------------------------------------------------------------------------\n";
try {
$create_table = Dal::query_one("SHOW CREATE TABLE {$table}");
} catch (PAException $e) {
if ($e->getCode() != DB_QUERY_FAILED) {
throw $e;
}
$bits = preg_split("/(\\s+|,)/", $sql);
$pos = array_search($table, $bits);
if ($pos === NULL) {
throw new PAException(GENERAL_SOME_ERROR, "Failed to find real name for table {$table} in query {$sql}");
}
$table = strtolower($bits[$pos - 1]) == 'as' ? $bits[$pos - 2] : $bits[$pos - 1];
$create_table = Dal::query_one("SHOW CREATE TABLE {$table}");
}
echo $create_table[1] . "\n";
}
}
public static function load_attributes($id)
{
// cached already?
$cache_key = "entity_attributes:{$id}";
$entity_attributes = Cache::getExtCache(0, $cache_key);
if (!empty($entity_attributes)) {
return $entity_attributes;
}
// load from DB otherwise
$r = Dal::query("SELECT * FROM {entityattributes}\n \tWHERE id=?", array($id));
if (!$r) {
return NULL;
}
// get attributes
$entity_attributes = array();
while ($row = Dal::row_assoc($r)) {
$entity_attributes[$row['attribute_name']] = array('value' => $row['attribute_value'], 'permission' => $row['attribute_permission']);
}
Cache::setExtCache(0, $cache_key, $entity_attributes);
return $entity_attributes;
}
public static function load_many_from_query($cls, $sql, $args = NULL)
{
$sth = Dal::query($sql, $args);
$items = array();
while ($r = Dal::row_assoc($sth)) {
$obj = new $cls();
$obj->load_from_row($r);
$items[] = $obj;
}
return $items;
}
function dump_schema()
{
echo '<table border="1">';
$th = Dal::query("SHOW TABLES");
while ($tr = Dal::row($th))
{
list($tname) = $tr;
echo "<tr><td><b>TABLE: $tname</b></td></tr>";
$ch = Dal::query("DESCRIBE $tname");
while ($cr = Dal::row_assoc($ch))
{
echo '<tr>';
foreach ($cr as $k => $v)
echo "<td>$v</td>";
echo '</tr>';
}
}
echo '</table>';
}
/**
* getting degree 1 relations list for a user.
* @param $user_id user_id of the user of which degree 1 relations are to be loaded
* @return $relations_id, an array containing the user_id of all degree 1 relations.
*/
public static function get_stats()
{
$sql = "SELECT status, count(*) AS count FROM {relations}\n\t\t\tGROUP BY status";
$r = Dal::query($sql);
$stats = array();
if ($r->numRows()) {
while ($c = Dal::row_assoc($r)) {
$stats[$c['status']] = $c['count'];
}
}
if (PA::$extra['reciprocated_relationship']) {
// we will have two paired relations
$stats['approved'] = (int) ((int) $stats['approved'] / 2);
}
return $stats;
}
