function testOwnerIdMemberCount() { $networks = array(); $sth = Dal::query("SELECT network_id, address, member_count, owner_id FROM networks WHERE is_active=1"); while (list($net_id, $address, $member_count, $owner_id) = Dal::row($sth)) { $networks[$net_id] = array("address" => $address, "member_count" => $member_count, "owner_id" => $owner_id); } // count all members for all networks $sth = Dal::query("SELECT network_id, COUNT(user_id) FROM networks_users GROUP BY network_id"); while (list($net_id, $member_count) = Dal::row($sth)) { $networks[$net_id]['calc_member_count'] = $member_count; } // find all owners $sth = Dal::query("SELECT network_id, user_id FROM networks_users where user_type='owner'"); while (list($net_id, $owner_id) = Dal::row($sth)) { $networks[$net_id]['calc_owner_id'] = $owner_id; } // verify them all $ok = TRUE; foreach ($networks as $nid => $net) { $address = $net['address']; $mc = $net['member_count']; $cmc = $net['calc_member_count']; $oi = $net['owner_id']; $coi = (int) $net['calc_owner_id']; if ($cmc && ($mc != $cmc || $oi != $coi)) { echo "NetworkDataTest ERROR: Network {$nid} [{$address}]: member_count {$mc}, calc {$cmc} | owner_id {$oi}, found {$coi}\n"; $ok = FALSE; } } $this->assertTrue($ok); }
public static function recalculate_total_link_counts() { $sth = Dal::query("SELECT id FROM spam_domains"); while ($r = Dal::row($sth)) { set_time_limit(60); $domain_id = $r[0]; SpamDomain::recalculate_link_counts_for_domain_id($domain_id); } }
public function load($badge_tag) { $this->badge_tag = $badge_tag; $sth = Dal::query("SELECT badge_id, title, badge_config FROM {blog_badges} WHERE is_active=1 AND user_id=? AND badge_tag=?", array($this->user_id, $badge_tag)); if (!($r = Dal::row($sth))) { throw new PAException(ROW_DOES_NOT_EXIST, "No widget with tag {$badge_tag} found for user {$this->user_id}"); } list($this->badge_id, $this->title, $this->config) = $r; $this->config = unserialize($this->config); }
public static function get_valid_networks() { $sth = Dal::query("SHOW TABLES"); $tables = array(); while ($r = Dal::row($sth)) { $tables[$r[0]] = 1; } $sth = Dal::query("SELECT address FROM networks WHERE is_active=1"); $networks = array(); while ($r = Dal::row($sth)) { $address = $r[0]; if ($address == 'default' || isset($tables[$address . "_comments"])) { // comments table available - assume network has been initialised $networks[] = $address; } } // if we haven't run net_extra yet, the default network won't have an entry, so we add it in manually now. if (!in_array("default", $networks)) { $networks[] = "default"; } return $networks; }
echo "Page {$page} of {$pages}; current mem use {$start_mem}..."; $cmt_rows = Comment::get_all_comments(0, $per_page, $page); echo " after comments loaded, mem usage is " . memory_get_usage() . "\n"; $del_ct = 0; foreach ($cmt_rows as $cmt_row) { $cmt = new Comment(); $cmt->load_from_row($cmt_row); $del_ct += $cmt->index_spam_domains(TRUE); $cmt->index_words(); } echo "{$del_ct} comments deleted due to blacklisting or excessive linking\n"; unset($cmt_rows); unset($cmt_row); $end_mem = memory_get_usage(); // echo "end of page - mem used $end_mem (delta ".($end_mem - $start_mem).").\n"; echo "Counting up totals\n"; SpamDomain::recalculate_total_link_counts(); } echo "Analyzed {$total} comments\n"; echo "Worst domains:\n"; $sth = Dal::query("SELECT id,domain,count,active_count FROM spam_domains ORDER BY count DESC LIMIT 25"); while ($r = Dal::row($sth)) { list($domain_id, $domain, $count, $active_count) = $r; echo "{$count}: {$domain} (id={$domain_id}); {$active_count} not deleted\n"; } echo "Worst domains with still-active comments:\n"; $sth = Dal::query("SELECT id,domain,count,active_count FROM spam_domains WHERE active_count <> 0 ORDER BY count DESC LIMIT 25"); while ($r = Dal::row($sth)) { list($domain_id, $domain, $count, $active_count) = $r; echo "{$count}: {$domain} (id={$domain_id}); {$active_count} not deleted\n"; }
public function get_profile_fields($information_type, $field_names) { $data = array(); // look for already-cached data, and build sql to fill out the // final part of the WHERE clause - e.g. ... "AND field_name IN // (?, ?, ?)" $args = array($this->user_id, $information_type); $field_names_sql = array(); foreach ($field_names as $field_name) { $cache_key = "profile:{$this->user_id}:{$information_type}:{$field_name}"; $field_value = $data[$field_name] = Cache::getValue($cache_key); if ($field_value == NULL) { $args[] = $field_name; $field_names_sql[] = "?"; } } if (!empty($field_names_sql)) { // we don't have all the required data yet: SELECT out what is // left to get, put it into the data array ($data = // array("field1" => "field value 1", ...)), and cache it. $sth = Dal::query("SELECT field_name, field_value FROM {user_profile_data} WHERE user_id=? AND field_type=? AND field_name IN (" . implode(", ", $field_names_sql) . ") AND seq IS NULL", $args); while ($r = Dal::row($sth)) { list($field_name, $field_value) = $r; $data[$field_name] = $field_value; $cache_key = "profile:{$this->user_id}:{$information_type}:{$field_name}"; Cache::setValue($cache_key, $field_value); } } return $data; }
function table_exists($tablename) { //$sql = "DESCRIBE $tablename"; $sql = "SHOW TABLES LIKE '" . Dal::quote($tablename) . "'"; $res = Dal::query($sql); while (list($tname) = Dal::row($res)) { if ($tname == $tablename) { return TRUE; } } return FALSE; }
function spam_check() { // check terms first $sth = Dal::query("SELECT term FROM spam_terms WHERE blacklist=1"); while ($r = Dal::row($sth)) { $term = $r[0]; if (strpos($this->comment, $term) !== FALSE || strpos($this->subject, $term) !== FALSE || strpos($this->homepage, $term) !== FALSE) { return TRUE; // spam! } } // now check domains and count up links $link_ct = 0; foreach ($this->get_link_hosts() as $domain => $links) { $domain = new SpamDomain($domain); if ($domain->blacklisted) { return TRUE; // spam! } foreach ($links as $url => $linktexts) { $link_ct += count($linktexts); } } // check number of links if ($link_ct >= 10) { return TRUE; // spam! } // not spam return FALSE; }
/** method to get a mapping of all relation type ids and relation * strings. * * returns an array with keys corresponding to relation_type_id * values and values corresponding to relation_type values from the * relation_classifications table, e.g. array(1 => "havent met", 2 * * => "some other classification", ...) */ public static function get_relation_classifications() { $sth = Dal::query("SELECT relation_type_id, relation_type FROM {relation_classifications}"); $ret = array(); while ($r = Dal::row($sth)) { list($rid, $rcode) = $r; $ret[$rid] = $rcode; } return $ret; }
public function migrateLegacyFiles($dry_run = TRUE) { $this->dry_run = $dry_run; require_once PA::$path . "/db/Dal/DbUpdate.php"; echo "Migrating legacy files to new storage system\n"; $this->all_files = array(); if (!($h = opendir(PA::$path . '/web/files'))) { throw new PAException(GENERAL_SOME_ERROR, "Unable to open web/files directory"); } while (false !== ($f = readdir($h))) { if ($f[0] == '.') { continue; } $this->all_files[$f] = TRUE; } closedir($h); $this->unmatched = count($this->all_files); $this->unmatchable = 0; $this->matched = 0; $this->dupes = 0; echo "{$this->unmatched} files found\n"; echo "Matching with user images ...\n"; $sql = Dal::validate_sql("SELECT user_id,picture FROM {users}", $network); $sth = Dal::query($sql); while ($r = Dal::row($sth)) { list($uid, $pic) = $r; // user avatar $this->_matchLegacyFile($pic, array("role" => "avatar", "user" => $uid)); //TODO: user header image } $this->_dumpMatchResults(); $networks = DbUpdate::get_valid_networks(); echo "Processing " . count($networks) . " networks\n"; foreach ($networks as $network) { echo " Network: {$network}\n"; // network level stuff list($network_id, $act, $logo, $extra) = Dal::query_one("SELECT network_id, is_active, inner_logo_image, extra FROM networks WHERE address=?", array($network)); assert($act); // sanity check $extra = unserialize($extra); // network avatar $this->_matchLegacyFile($logo, array("role" => "avatar", "network" => $network_id)); // network header image $header_image = @$extra["basic"]["header_image"]["name"]; if (!empty($header_image)) { $this->_matchLegacyFile($header_image, array("role" => "header", "network" => $network_id)); } // emblems foreach (unserialize(Dal::query_first(Dal::validate_sql("SELECT data FROM {moduledata} WHERE modulename='LogoModule'"))) as $emblem) { $this->_matchLegacyFile($emblem["file_name"], array("role" => "emblem", "network" => $network_id)); } // group pictures $sth = Dal::query(Dal::validate_sql("SELECT collection_id, picture FROM {contentcollections} WHERE type=1 AND is_active=1", $network)); while ($r = Dal::row($sth)) { list($cid, $pic) = $r; $this->_matchLegacyFile($pic, array("role" => "avatar", "network" => $network_id, "group" => $cid)); $header = Dal::query_first(Dal::validate_sql("SELECT header_image FROM groups WHERE group_id=?", $network), array($cid)); $this->_matchLegacyFile($header, array("role" => "header", "network" => $network_id, "group" => $cid)); } /* disabled until we update peopleaggregator.net $sth = Dal::query(Dal::validate_sql("SELECT group_id, header_image FROM {groups}", $network)); while ($r = Dal::row($sth)) { list ($gid, $pic) = $r; $this->_matchLegacyFile($network, "group", $gid, $pic); } */ //TODO: advertisements // images, audio, video foreach (array("image", "audio", "video") as $table) { $sth = Dal::query(Dal::validate_sql('SELECT mc.content_id, mc.' . $table . '_file, c.author_id, c.collection_id, c.is_active FROM {' . $table . 's} mc LEFT JOIN {contents} c ON mc.content_id=c.content_id HAVING c.is_active=1', $network)); while ($r = Dal::row($sth)) { list($cid, $fn, $uid, $ccid, $act) = $r; $this->_matchLegacyFile($fn, array("role" => "media", "network" => $network_id, "content" => $cid)); } } } $this->_dumpMatchResults(); foreach ($this->all_files as $fn => $v) { if ($v === TRUE) { echo " * unmatchable: {$fn}\n"; } } echo "Overall results from web/files: "; $this->_dumpMatchResults(); }
public static function findByTitle($title) { $enc_title = Dal::quote($title); $groups = array(); $sth = Dal::query("SELECT collection_id,title FROM {contentcollections} WHERE title LIKE '%{$enc_title}%' AND is_active=1"); while ($r = Dal::row($sth)) { list($ccid, $title) = $r; $groups[] = array('ccid' => (int) $ccid, 'title' => $title); } return $groups; }
public static function query_one($sql, $args = NULL) { $sth = Dal::query($sql, $args); $ret = Dal::row($sth); return $ret; }
function table_exists($tablename) { $sql = 'SHOW TABLES LIKE \'' . Dal::quote($tablename) . '\''; $res = Dal::query($sql); while (list($tname) = Dal::row($res)) { if ($tname == $tablename) { return TRUE; } } return FALSE; }
function get_all_modified() { $sth = Dal::query("SELECT kind,path,hash FROM svn_objects WHERE is_active=1 ORDER BY path"); $changes = array(); while (list($kind, $leaf, $hash) = Dal::row($sth)) { $path = "{$this->root}/{$leaf}"; $change = $this->_check_modified($kind, $path, $hash); if ($change) { $changes[] = array($kind, $leaf, $change); } } if (!count($changes)) { return NULL; } return $changes; }