/** * Creates trends from the given array * * @param array $trends * @return array */ public static function create_from_array($trends) { if (empty($trends)) { return; } Swiftriver_Mutex::obtain(get_class(), 3600); // Hash array with droplet_hash as key and index in droplets array that contain that hash $trends_idx = array(); foreach ($trends as $key => &$trend) { $hash = md5($trend['river_id'] . $trend['date_pub'] . $trend['tag'] . $trend['tag_type']); $trend['hash'] = $hash; $trends_idx[$hash] = $key; } // Find the drops that already exist by their droplet_hash $found_query = DB::select('hash', 'id')->from('river_tag_trends')->where('hash', 'IN', array_keys($trends_idx)); $found = $found_query->execute()->as_array(); // Create a query to update existing trends and // remove them from the trends_idx $update_query = NULL; foreach ($found as $hash) { if ($update_query) { $update_query .= ' union all '; } $count = $trends[$trends_idx[$hash['hash']]]['count']; $update_query .= 'select ' . $hash['id'] . ' id, ' . $count . ' count'; unset($trends_idx[$hash['hash']]); } if ($update_query) { $query = "UPDATE `river_tag_trends` JOIN (" . $update_query . ") a " . "USING (`id`) SET `river_tag_trends`.`count` = `river_tag_trends`.`count` + `a`.`count`"; DB::query(Database::UPDATE, $query)->execute(); } if (!empty($trends_idx)) { // Get a range of IDs to be used in inserting the new drops $base_id = Model_River_Tag_Trend::get_ids(count($trends_idx)); // Insert into the droplets table $query = DB::insert('river_tag_trends', array('id', 'hash', 'river_id', 'date_pub', 'tag', 'tag_type', 'count')); foreach ($trends_idx as $hash => $key) { $query->values(array('id' => $base_id++, 'hash' => $trends[$key]['hash'], 'river_id' => $trends[$key]['river_id'], 'date_pub' => $trends[$key]['date_pub'], 'tag' => $trends[$key]['tag'], 'tag_type' => $trends[$key]['tag_type'], 'count' => $trends[$key]['count'])); } $query->execute(); } Swiftriver_Mutex::release(get_class()); }
/** * Populate the droplet metadata tables. * * @param array $drops Drop array */ public static function add_metadata(&$drops) { // Build queries for creating entries in the meta tables droplet_tags, droplet_links and so on $drops_idx = array(); $river_check_query = NULL; $tags_ref = array(); $tag_values = NULL; $tag_check_query = NULL; $semantics_complete = array(); $link_values = NULL; $drop_links = NULL; $media_values = NULL; $drop_images = NULL; $media_thumbnail_values = NULL; $media_complete = array(); $place_values = NULL; $place_check_query = NULL; foreach ($drops as $key => $drop) { // Create an in memory drop reference $drops_idx[$drop['id']] = $key; // Place drops into rivers if (isset($drop['river_id'])) { foreach ($drop['river_id'] as $river_id) { // Subquery to find new river drops $subquery = DB::select(array(DB::expr($drop['id']), 'droplet_id'), array(DB::expr($river_id), 'river_id')); if (!$river_check_query) { $river_check_query = $subquery; } else { $river_check_query = $subquery->union($river_check_query, TRUE); } } } // Create a query to insert tags into droplets_tags if (isset($drop['tags'])) { foreach ($drop['tags'] as $tag) { // Create an in memory tag id -> tag detail reference $tags_ref[$tag['id']] = array('tag_name' => $tag['tag_name'], 'tag_type' => $tag['tag_type']); // Values for the drop tags insert query if ($tag_values) { $tag_values .= ','; } $tag_values .= '(' . $drop['id'] . ',' . $tag['id'] . ')'; // Subquery to find new tags $subquery = DB::select(array(DB::expr($drop['id']), 'droplet_id'), array(DB::expr($tag['id']), 'tag_id')); if (!$tag_check_query) { $tag_check_query = $subquery; } else { $tag_check_query = $subquery->union($tag_check_query, TRUE); } } } // Find drops that have complete semantic processing if (isset($drop['semantics_complete'])) { $semantics_complete[] = $drop['id']; } if (isset($drop['links'])) { foreach ($drop['links'] as $link) { if ($link_values) { $link_values .= ','; } $link_values .= '(' . $drop['id'] . ',' . $link['id'] . ')'; // Store drop original link for updating the droplets table if (isset($link['id']) and isset($link['original_url']) and $link['original_url']) { if ($drop_links) { $drop_links .= ' union all '; } $drop_links .= 'select ' . $link['id'] . ' drop_link, ' . $drop['id'] . ' id'; } } } if (isset($drop['media'])) { foreach ($drop['media'] as $media) { if ($media_values) { $media_values .= ','; } $media_values .= '(' . $drop['id'] . ',' . $media['id'] . ')'; if ($media['droplet_image']) { if ($drop_images) { $drop_images .= ' union all '; } $drop_images .= 'select ' . $media['id'] . ' droplet_image, ' . $drop['id'] . ' id'; } if (isset($media['thumbnails'])) { foreach ($media['thumbnails'] as $thumbnail) { if ($media_thumbnail_values) { $media_thumbnail_values .= ','; } $media_thumbnail_values .= '(' . $media['id'] . ',' . $thumbnail['size'] . ",'" . addslashes($thumbnail['url']) . "')"; } } } } // Find drops that have completed media processing if (isset($drop['media_complete'])) { $media_complete[] = $drop['id']; } if (isset($drop['places'])) { foreach ($drop['places'] as $place) { // Create an in memory tag id -> tag detail reference $places_ref[$place['id']] = array('place_name' => $place['place_name']); if ($place_values) { $place_values .= ','; } $place_values .= '(' . $drop['id'] . ',' . $place['id'] . ')'; // Subquery to find new places $subquery = DB::select(array(DB::expr($drop['id']), 'droplet_id'), array(DB::expr($place['id']), 'place_id')); if (!$place_check_query) { $place_check_query = $subquery; } else { $place_check_query = $subquery->union($place_check_query, TRUE); } } } } // Find river drops already in the DB $existing_river_drops = DB::select('droplet_id', 'river_id')->from('rivers_droplets')->where('droplet_id', 'IN', array_keys($drops_idx))->execute()->as_array(); // Find the new river drops we are just about to add $new_river_drops = NULL; $max_river_drop_ids = array(); if ($river_check_query) { Swiftriver_Mutex::obtain('rivers_droplets', 3600); $sub = DB::select('droplet_id', 'river_id')->from('rivers_droplets')->where('droplet_id', 'IN', array_keys($drops_idx)); $new_river_drops = DB::select('droplet_id', 'river_id')->distinct(TRUE)->from(array($river_check_query, 'a'))->where(DB::expr('(`droplet_id`, `river_id`)'), 'NOT IN', $sub)->execute()->as_array(); if (!empty($new_river_drops)) { $base_id = Model_Droplet::get_ids(count($new_river_drops), 'rivers_droplets'); // Insert into the rivers_droplets table $query = DB::insert('rivers_droplets', array('id', 'river_id', 'droplet_id', 'droplet_date_pub')); foreach ($new_river_drops as $new_river_drop) { $drops_key = $drops_idx[$new_river_drop['droplet_id']]; $date_pub = $drops[$drops_key]['droplet_date_pub']; $river_id = $new_river_drop['river_id']; $id = $base_id++; $query->values(array('id' => $id, 'river_id' => $river_id, 'droplet_id' => $new_river_drop['droplet_id'], 'droplet_date_pub' => $date_pub)); if (!isset($max_river_drop_ids[$river_id])) { $max_river_drop_ids[$river_id] = array('max_id' => $id, 'count' => 1); } else { $max_river_drop_ids[$river_id]['count'] += 1; if ($id > $max_river_drop_ids[$river_id]['max_id']) { $max_river_drop_ids[$river_id]['max_id'] = $id; } } } $query->execute(); $max_river_drops_query = NULL; foreach ($max_river_drop_ids as $key => $value) { if ($max_river_drops_query) { $max_river_drops_query .= ' union all '; } $max_river_drops_query .= 'select ' . $key . ' id, ' . $value['max_id'] . ' max_id, ' . $value['count'] . ' cnt'; } // Update river max_drop_id $update_rivers_sql = "UPDATE `rivers` JOIN (" . $max_river_drops_query . ") a " . "USING (`id`) SET `rivers`.`max_drop_id` = `a`.`max_id` " . "WHERE `rivers`.`max_drop_id` < `a`.`max_id`"; DB::query(Database::UPDATE, $update_rivers_sql)->execute(); // Update drop count $update_rivers_sql = "UPDATE `rivers` JOIN (" . $max_river_drops_query . ") a " . "USING (`id`) SET `rivers`.`drop_count` = `rivers`.`drop_count` + `a`.`cnt` "; DB::query(Database::UPDATE, $update_rivers_sql)->execute(); } Swiftriver_Mutex::release('rivers_droplets'); } // Find the new drop tags $new_drop_tags = array(); if ($tag_check_query) { $sub = DB::select('droplet_id', 'tag_id')->from('droplets_tags')->where('droplet_id', 'IN', array_keys($drops_idx)); $new_tags = DB::select('droplet_id', 'tag_id')->from(array($tag_check_query, 'a'))->where(DB::expr('(`droplet_id`, `tag_id`)'), 'NOT IN', $sub)->execute()->as_array(); foreach ($new_tags as $new_tag) { if (!isset($new_drop_tags[$new_tag['droplet_id']])) { $new_drop_tags[$new_tag['droplet_id']] = array(); } $new_drop_tags[$new_tag['droplet_id']][] = array('id' => $new_tag['tag_id'], 'tag' => $tags_ref[$new_tag['tag_id']]['tag_name'], 'tag_type' => $tags_ref[$new_tag['tag_id']]['tag_type']); } } // Update droplets tags $all_drop_tags = array(); if ($tag_values) { $insert_tags_sql = "INSERT IGNORE INTO `droplets_tags` (`droplet_id`, `tag_id`) " . "VALUES " . $tag_values; DB::query(Database::INSERT, $insert_tags_sql)->execute(); // Get all tags(new + existing) for drops that have just been added // to a river if ($new_river_drops) { $drop_tags = DB::select('droplet_id', 'tags.id', 'tag', 'tag_type')->from('droplets_tags')->join('tags', 'INNER')->on('droplets_tags.tag_id', '=', 'tags.id')->where('droplet_id', 'IN', array_keys($drops_idx))->execute()->as_array(); foreach ($drop_tags as $drop_tag) { if (!isset($all_drop_tags[$drop_tag['droplet_id']])) { $all_drop_tags[$drop_tag['droplet_id']] = array(); } $all_drop_tags[$drop_tag['droplet_id']][] = array('id' => $drop_tag['id'], 'tag' => $drop_tag['tag'], 'tag_type' => $drop_tag['tag_type']); } } } // Update drops that completed semantic processing if (!empty($semantics_complete)) { DB::update('droplets')->set(array('processing_status' => DB::expr('processing_status | ' . self::PROCESSING_FLAG_SEMANTICS)))->where("id", "IN", $semantics_complete)->execute(); } // Update droplet links if ($link_values) { $insert_links_sql = "INSERT IGNORE INTO `droplets_links` (`droplet_id`, `link_id`) " . "VALUES " . $link_values; DB::query(Database::INSERT, $insert_links_sql)->execute(); } // Set the drop's original url if ($drop_links) { $update_orig_url_sql = "UPDATE `droplets` JOIN (" . $drop_links . ") a " . "USING (`id`) SET `droplets`.`original_url` = `a`.`drop_link`"; DB::query(Database::UPDATE, $update_orig_url_sql)->execute(); } // Update droplet media if ($media_values) { $insert_media_sql = "INSERT IGNORE INTO `droplets_media` (`droplet_id`, `media_id`) " . "VALUES " . $media_values; DB::query(Database::INSERT, $insert_media_sql)->execute(); } // Insert thumbnails if ($media_thumbnail_values) { $insert_thumbnail_sql = "INSERT IGNORE INTO `media_thumbnails` (`media_id`, `size`, `url`) " . "VALUES " . $media_thumbnail_values; DB::query(Database::INSERT, $insert_thumbnail_sql)->execute(); } // Set drop image if ($drop_images) { $update_images_sql = "UPDATE `droplets` JOIN (" . $drop_images . ") a " . "USING (`id`) SET `droplets`.`droplet_image` = `a`.`droplet_image`"; DB::query(Database::UPDATE, $update_images_sql)->execute(); } // Update drops that completed media processing if (!empty($media_complete)) { DB::update('droplets')->set(array('processing_status' => DB::expr('processing_status | ' . self::PROCESSING_FLAG_MEDIA)))->where("id", "IN", $media_complete)->execute(); } // Find the new drop places $new_drop_places = array(); if ($place_check_query) { $sub = DB::select('droplet_id', 'place_id')->from('droplets_places')->where('droplet_id', 'IN', array_keys($drops_idx)); $new_places = DB::select('droplet_id', 'place_id')->from(array($place_check_query, 'a'))->where(DB::expr('(`droplet_id`, `place_id`)'), 'NOT IN', $sub)->execute()->as_array(); foreach ($new_places as $new_place) { if (!isset($new_drop_places[$new_place['droplet_id']])) { $new_drop_places[$new_place['droplet_id']] = array(); } $new_drop_places[$new_place['droplet_id']][] = array('id' => $new_place['place_id'], 'tag' => $places_ref[$new_place['place_id']]['place_name'], 'tag_type' => 'place'); } } // Update droplet places $all_drop_places = array(); if ($place_values) { $insert_places_sql = "INSERT IGNORE INTO `droplets_places` (`droplet_id`, `place_id`) " . "VALUES " . $place_values; DB::query(Database::INSERT, $insert_places_sql)->execute(); // Get all places(new + existing) for drops that have just been added // to a river if ($new_river_drops) { $drop_places = DB::select('droplet_id', 'places.id', 'place_name')->from('droplets_places')->join('places', 'INNER')->on('droplets_places.place_id', '=', 'places.id')->where('droplet_id', 'IN', array_keys($drops_idx))->execute()->as_array(); foreach ($drop_places as $drop_place) { if (!isset($all_drop_places[$drop_place['droplet_id']])) { $all_drop_places[$drop_place['droplet_id']] = array(); } $all_drop_places[$drop_place['droplet_id']][] = array('id' => $drop_place['id'], 'tag' => $drop_place['place_name'], 'tag_type' => 'place'); } } } // Update trends $trends = array(); // Update for drops already in the DB but we are adding new tags if ($existing_river_drops) { $trends = array_merge($trends, self::get_tag_trends($existing_river_drops, $new_drop_tags, $drops, $drops_idx)); $trends = array_merge($trends, self::get_tag_trends($existing_river_drops, $new_drop_places, $drops, $drops_idx)); } // Update for drops that already exist in the DB with tags but we are adding // the drop into a new river if ($new_river_drops) { $trends = array_merge($trends, self::get_tag_trends($new_river_drops, $all_drop_tags, $drops, $drops_idx)); $trends = array_merge($trends, self::get_tag_trends($new_river_drops, $all_drop_places, $drops, $drops_idx)); } if (!empty($trends)) { Model_River_Tag_Trend::create_from_array($trends); } }
/** * @return void */ public function action_trends() { $this->droplets_view = View::factory('pages/river/trend'); $this->droplets_view->river_base_url = $this->river_base_url; $this->active = 'trends'; $this->droplets_view->trends = array(); $cur_date = new DateTime(null, new DateTimeZone('UTC')); $tag_types = array('person' => 'People', 'place' => 'Places', 'organization' => 'Organizations'); $periods = array('hour' => 'This Hour', 'day' => 'Today', 'week' => 'This Week', 'month' => 'This Month', 'all' => 'All Time'); foreach ($tag_types as $type_key => $type_title) { $has_data = FALSE; foreach ($periods as $period_key => $period_value) { $start_time = $this->get_period_start_time($period_key, $cur_date); $data = Model_River_Tag_Trend::get_trend($this->river->id, $start_time, $type_key); foreach ($data as &$trend) { $trend['url'] = $this->river_base_url . '?' . ($type_key == 'place' ? 'places' : 'tags') . '=' . urlencode($trend['tag']); if ($start_time) { $trend['url'] .= '&start_date=' . urlencode($start_time); } } $this->droplets_view->trends[$type_title]['data'][$period_value] = $data; if (!empty($data)) { $has_data = TRUE; } } $this->droplets_view->trends[$type_title]['has_data'] = $has_data; } }