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;
}
