Skip to content

Commit

Permalink
[DB MIGRATION REQ'D] Speed up database interactions
Browse files Browse the repository at this point in the history 
* Create insights table, model, and DAO for storing dashboard data modules
* Pre-store results of the following in the insights table during crawl:
** FollowMySQLDAO::getLeastLikelyFollowersThisWeek
** PostMySQLDAO::getHotPosts
** ShortLinkMySQLDAO::getRecentClickStats
** PostMySQLDAO::getAllPostsByUsernameOrderedBy (most retweeted and most replied-to in last week)
** PostMySQLDAO::getClientsUsedByUserOnNetwork
* During dashboard render, retrieve those modules' data from insights, which is a much faster query
* Break out InstanceDAO::save subqueries into separate queries for easier MySQL EXPLAIN profiling
* Add user_id index to follows table to speed up total follows query
  • Loading branch information
@ginatrapani
ginatrapani committed Jun 18, 2012
1 parent 58dc741 commit 880e7df
Show file tree
Hide file tree
Showing 20 changed files with 1,050 additions and 269 deletions.
8 changes: 7 additions & 1 deletion tests/TestOfDAOFactory.php
View file
Expand Up @@ -375,6 +375,12 @@ public function testGetShortLinkDAO() {
$this->assertNotNull($dao);
$this->assertIsA($dao, 'ShortLinkMySQLDAO');
}

public function testInsightDAO() {
$dao = DAOFactory::getDAO('InsightDAO');
$this->assertNotNull($dao);
$this->assertIsA($dao, 'InsightMySQLDAO');
}
/**
* Test get InstallerDAO without a config file, override with array of config values
*/
Expand All @@ -387,7 +393,7 @@ public function testGetInstallerDAONoConfigFile(){
$this->assertTrue(isset($dao));
$this->assertIsA($dao, 'InstallerMySQLDAO');
$result = $dao->getTables();
$this->assertEqual(sizeof($result), 29);
$this->assertEqual(sizeof($result), 30);
$this->assertEqual($result[0], $cfg_values["table_prefix"].'encoded_locations');
$this->restoreConfigFile();
}
Expand Down
108 changes: 0 additions & 108 deletions tests/TestOfDashboardController.php
View file
Expand Up @@ -401,112 +401,4 @@ private function buildData($with_xss = false) {
return array($owner_builder, $instance_owner_builder_1, $instance_owner_builder_2, $instance_owner_builder_3,
$user_builders, $instance_builder_1, $instance_builder_2, $instance_builder_3, $post_builders);
}

public function testGetHotPostVisualizationData() {
$hot_posts = array(
(object)array(
'post_text' => 'First Post',
'favlike_count_cache' => 1,
'all_retweets' => 2,
'reply_count_cache' => 3,
),
(object)array(
'post_text' => 'Second Post',
'favlike_count_cache' => 10,
'all_retweets' => 20,
'reply_count_cache' => 30,
)
);

$result = DashboardController::getHotPostVisualizationData($hot_posts, 'twitter');
$this->assertEqual(gettype($result), 'string');

$visualization_object = json_decode($result);
$this->assertEqual(sizeof($visualization_object->rows), 2);
$this->assertEqual(sizeof($visualization_object->cols), 4);

$this->assertEqual($visualization_object->cols[0]->label, 'Tweet');
$this->assertEqual($visualization_object->cols[1]->label, 'Replies');
$this->assertEqual($visualization_object->cols[2]->label, 'Retweets');
$this->assertEqual($visualization_object->cols[3]->label, 'Favorites');

$this->assertEqual($visualization_object->rows[0]->c[0]->v, 'First Post...');
$this->assertEqual($visualization_object->rows[0]->c[1]->v, 3);
$this->assertEqual($visualization_object->rows[0]->c[2]->v, 2);
$this->assertEqual($visualization_object->rows[0]->c[3]->v, 1);

$result = DashboardController::getHotPostVisualizationData($hot_posts, 'facebook');
$this->assertEqual(gettype($result), 'string');

$visualization_object = json_decode($result);
$this->assertEqual(sizeof($visualization_object->rows), 2);
$this->assertEqual(sizeof($visualization_object->cols), 4);

$this->assertEqual($visualization_object->cols[0]->label, 'Post');
$this->assertEqual($visualization_object->cols[1]->label, 'Comments');
$this->assertEqual($visualization_object->cols[2]->label, 'Shares');
$this->assertEqual($visualization_object->cols[3]->label, 'Likes');

$this->assertEqual($visualization_object->rows[1]->c[0]->v, 'Second Post...');
$this->assertEqual($visualization_object->rows[1]->c[1]->v, 30);
$this->assertEqual($visualization_object->rows[1]->c[2]->v, 20);
$this->assertEqual($visualization_object->rows[1]->c[3]->v, 10);
}

public function testGetClientVisualizationData() {
$client_data = array(
'Client 1' => 50,
'Client 2' => 10,
);

$result = DashboardController::getClientUsageVisualizationData($client_data);
$this->assertEqual(gettype($result), 'string');

$visualization_object = json_decode($result);
$this->assertEqual(sizeof($visualization_object->rows), 2);
$this->assertEqual(sizeof($visualization_object->cols), 2);

$this->assertEqual($visualization_object->cols[0]->label, 'Client');
$this->assertEqual($visualization_object->cols[1]->label, 'Posts');

$this->assertEqual($visualization_object->rows[0]->c[0]->v, 'Client 1');
$this->assertEqual($visualization_object->rows[0]->c[0]->f, 'Client 1');
$this->assertEqual($visualization_object->rows[0]->c[1]->v, 50);

$this->assertEqual($visualization_object->rows[1]->c[0]->v, 'Client 2');
$this->assertEqual($visualization_object->rows[1]->c[1]->v, 10);
}

public function testGetClickStatsVisualizationData() {
$click_stats = array(
array('post_text'=>'Black Mirror punched me in the gut this weekend. Highly recommended. http://t.co/AnczD4Jc '.
'Thx @annaleen & @fraying',
'click_count' => 50),
array('post_text'=>'@saenz a geeky uncle&#39;s only <span class="googid">+Sprint</span> http://t.co/cxZTmWhk',
'click_count' => 150),
array('post_text'=>'I\'ll admit Glee made me cry last night. Then it made me cringe. http://t.co/lgjaJWcW ',
'click_count' => 23),
);

$result = DashboardController::getClickStatsVisualizationData($click_stats);
$this->assertEqual(gettype($result), 'string');

$visualization_object = json_decode($result);
$this->assertEqual(sizeof($visualization_object->rows), 3);
$this->assertEqual(sizeof($visualization_object->cols), 2);

$this->assertEqual($visualization_object->cols[0]->label, 'Link');
$this->assertEqual($visualization_object->cols[1]->label, 'Clicks');

$this->assertEqual($visualization_object->rows[0]->c[0]->v,
'Black Mirror punched me in the gut this weekend. Highly recommended. http://t.co/AnczD4Jc Thx @annal...');
$this->assertEqual($visualization_object->rows[0]->c[1]->v, 50);
$this->assertEqual($visualization_object->rows[1]->c[0]->v,
"@saenz a geeky uncle's only +Sprint http://t.co/cxZTmWhk...");
$this->assertEqual($visualization_object->rows[1]->c[1]->v, 150);

$this->assertEqual($visualization_object->rows[2]->c[0]->v,
'I\'ll admit Glee made me cry last night. Then it made me cringe. http://t.co/lgjaJWcW ...');
$this->assertEqual($visualization_object->rows[2]->c[1]->v, 23);
}
}
175 changes: 175 additions & 0 deletions tests/TestOfInsightMySQLDAO.php
View file
@@ -0,0 +1,175 @@
<?php
/**
*
* ThinkUp/tests/TestOfInsightMySQLDAO.php
*
* Copyright (c) 2012 Gina Trapani
*
* LICENSE:
*
* This file is part of ThinkUp (http://thinkupapp.com).
*
* ThinkUp is free software: you can redistribute it and/or modify it under the terms of the GNU General Public
* License as published by the Free Software Foundation, either version 2 of the License, or (at your option) any
* later version.
*
* ThinkUp is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied
* warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
* details.
*
* You should have received a copy of the GNU General Public License along with ThinkUp. If not, see
* <http://www.gnu.org/licenses/>.
*
* @license http://www.gnu.org/licenses/gpl.html
* @copyright 2012 Gina Trapani
* @author Gina Trapani
*/
require_once dirname(__FILE__).'/init.tests.php';
require_once THINKUP_WEBAPP_PATH.'_lib/extlib/simpletest/autorun.php';
require_once THINKUP_WEBAPP_PATH.'config.inc.php';

class TestOfInsightMySQLDAO extends ThinkUpUnitTestCase {
public function setUp() {
parent::setUp();
$this->builders = self::buildData();
}

protected function buildData() {
$builders = array();
$builders[] = FixtureBuilder::build('insights', array('date'=>'2012-05-01', 'slug'=>'avg_replies_per_week',
'instance_id'=>'1', 'text'=>'Retweet spike! Your post got retweeted 110 times',
'emphasis'=>Insight::EMPHASIS_HIGH));

//Set up array of owner objects
$o1["id"] = 10;
$o1["full_name"] = "Jack Black";
$o1['email'] = "jackblack@example.com";
$o1['last_login'] = '2012-05-01';
$o1["is_admin"] = 0;
$o1["is_activated"] = 1;
$o1["account_status"] = "Valid";
$o1["failed_logins"] = 0;

$o2["id"] = 10;
$o2["full_name"] = "Jill White";
$o2['email'] = "jillwhite@example.com";
$o2['last_login'] = '2012-05-01';
$o2["is_admin"] = 0;
$o2["is_activated"] = 1;
$o2["account_status"] = "Valid";
$o2["failed_logins"] = 0;

$o3["id"] = 10;
$o3["full_name"] = "Joe Schmoe";
$o3['email'] = "joeschmoek@example.com";
$o3['last_login'] = '2012-05-01';
$o3["is_admin"] = 0;
$o3["is_activated"] = 1;
$o3["account_status"] = "Valid";
$o3["failed_logins"] = 0;

$owner_1 = new Owner($o1);
$owner_2 = new Owner($o2);
$owner_3 = new Owner($o3);

$owners = array();
$owners[] = $owner_1;
$owners[] = $owner_2;
$owners[] = $owner_3;

$builders[] = FixtureBuilder::build('insights', array('date'=>'2012-06-15', 'slug'=>'a_bunch_of_owners',
'instance_id'=>'1', 'text'=>'Here are owners', 'related_data'=>serialize($owners),
'emphasis'=>Insight::EMPHASIS_HIGH));

return $builders;
}

public function tearDown() {
$this->builders = null;
parent::tearDown();
}

public function testGetInsight() {
$dao = new InsightMySQLDAO();
$result = $dao->getInsight('avg_replies_per_week', 1, '2012-05-01');
$this->assertIsA($result, 'Insight');
$this->assertEqual($result->slug, 'avg_replies_per_week');
$this->assertEqual($result->date, '2012-05-01');
$this->assertEqual($result->instance_id, 1);
$this->assertEqual($result->text, 'Retweet spike! Your post got retweeted 110 times');
$this->assertEqual($result->emphasis, Insight::EMPHASIS_HIGH);

$result = $dao->getInsight('avg_replies_per_week', 1, '2012-05-02');
$this->assertNull($result);
}

public function testGetPreCachedInsightData() {
$dao = new InsightMySQLDAO();
$results = $dao->getPreCachedInsightData('avg_replies_per_week', 1, '2012-05-01');
$this->assertNull($result);

$result = $dao->getPreCachedInsightData('avg_replies_per_week', 1, '2012-05-02');
$this->assertNull($result);

$result = $dao->getPreCachedInsightData('a_bunch_of_owners', 1, '2012-06-15');
$this->assertNotNull($result);
$this->assertEqual(sizeof($result), 3);
$this->assertIsA($result[0], 'Owner');
$this->assertEqual($result[0]->full_name, 'Jack Black');
$this->assertEqual($result[1]->full_name, 'Jill White');
$this->assertEqual($result[2]->full_name, 'Joe Schmoe');
}

public function testInsertInsight() {
$dao = new InsightMySQLDAO();
//date specified
$result = $dao->insertInsight('avg_replies_per_week', 1, '2012-05-05', 'Oh hai! You rock');
$this->assertTrue($result);

$result = $dao->getInsight('avg_replies_per_week', 1, '2012-05-05');
$this->assertEqual($result->text, 'Oh hai! You rock');
$this->assertNull($result->related_data);
$this->assertEqual($result->emphasis, Insight::EMPHASIS_LOW);

//inserting existing insight should update
$result = $dao->insertInsight('avg_replies_per_week', 1, '2012-05-05', 'Oh hai! Updated: You rock',
Insight::EMPHASIS_HIGH);
$this->assertTrue($result);

//assert update was successful
$result = $dao->getInsight('avg_replies_per_week', 1, '2012-05-05');
$this->assertEqual($result->text, 'Oh hai! Updated: You rock');
$this->assertEqual($result->emphasis, Insight::EMPHASIS_HIGH);
}

public function testUpdateInsight() {
$dao = new InsightMySQLDAO();

//update existing baseline
$result = $dao->updateInsight('avg_replies_per_week', 1, '2012-05-01', 'LOLlerskates', Insight::EMPHASIS_MED);
$this->assertTrue($result);
//check that value was updated
$result = $dao->getInsight('avg_replies_per_week', 1, '2012-05-01');
$this->assertEqual($result->text, 'LOLlerskates');
$this->assertEqual($result->emphasis, Insight::EMPHASIS_MED);

//update nonexistent baseline
$result = $dao->updateInsight('avg_replies_per_week', 1, '2012-05-10', 'ooooh burn');
$this->assertFalse($result);
}

public function testDeleteInsight() {
$dao = new InsightMySQLDAO();

//delete existing baseline
$result = $dao->deleteInsight('avg_replies_per_week', 1, '2012-05-01', 'LOLlerskates', Insight::EMPHASIS_MED);
$this->assertTrue($result);
//check that insight was deleted
$result = $dao->getInsight('avg_replies_per_week', 1, '2012-05-01');
$this->assertNull($result);

//delete nonexistent baseline
$result = $dao->deleteInsight('avg_replies_per_week', 1, '2012-05-10', 'ooooh burn');
$this->assertFalse($result);
}
}

0 comments on commit 880e7df

Please sign in to comment.