Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
[DB MIGRATION REQ'D] Support for checkins
First of two commits that will add Foursquare support:
This one is the data model modifications and core application changes required for posts with places, i.e., checkins.
Adds Place model object. Adds and map_image and icon fields to the places table, and is_archive_loaded_posts to instances table.
Also adds "Posts on this day from years past" to dashboard across networks.
  • Loading branch information
AaronKalair authored and ginatrapani committed Aug 28, 2012
1 parent 63b4aa8 commit 94cc96d
Show file tree
Hide file tree
Showing 12 changed files with 271 additions and 45 deletions.
49 changes: 47 additions & 2 deletions tests/TestOfPlaceMySQLDAO.php
Expand Up @@ -78,7 +78,7 @@ public function testInsertPlaceBBoxOnly() {
);
$this->dao->insertPlace($place, 123456, 'twitter');
$res = $this->dao->getPlaceByID('1a16a1d70500c27d');
$this->assertEqual(sizeof($res), 10);
$this->assertEqual(sizeof($res), 12);
$this->assertEqual($res['place_id'], '1a16a1d70500c27d');
$this->assertEqual($res['name'], 'Hyde Park');
$this->assertPattern('/POINT\(-97.72446/', $res['longlat']);
Expand Down Expand Up @@ -142,7 +142,7 @@ public function testInsertPlace() {
$this->dao->insertPlace($place1, 123456, 'twitter');
$this->dao->insertPlace($place2, 123457, 'twitter');
$res = $this->dao->getPlaceByID('1a16a1d70500c27d');
$this->assertEqual(sizeof($res), 10);
$this->assertEqual(sizeof($res), 12);
$this->assertEqual($res['place_id'], '1a16a1d70500c27d');
$this->assertEqual($res['name'], 'Hyde Park');
$this->assertPattern('/POINT\(-97.72446/', $res['longlat']);
Expand All @@ -163,6 +163,51 @@ public function testInsertPlace() {
$this->assertEqual($res['post_id'], 123457);
}

public function testInsertGenericPlace() {
// Set all possible fields
$places['id'] = 123;
$places['place_type'] = "Park";
$places['name'] = "A Park";
$places['full_name'] = "The Greatest Park";
$places['country_code'] = "UK";
$places['country'] = "United Kingdom";
$places['icon'] = "http://www.iconlocation.com";
$places['lat_lng'] = 'POINT(51.514 -0.1167)';
$places['bounding_box'] = 'POLYGON((-0.213503 51.512805,-0.105303 51.512805,-0.105303 51.572068,'.
'-0.213503 51.572068, -0.213503 51.512805)))';
$places['map_image'] = "http://www.mapimage.com";

// Insert the place
$this->dao->insertGenericPlace($places, 1234, 'foursquare');
// Get the place from the database
$res = $this->dao->getPlaceByID('123');

// Check all 12 fields were returned
$this->assertEqual(sizeof($res), 12);
// Check the place ID was set correctly
$this->assertEqual($res['place_id'], '123');
// Check the type was set correctly
$this->assertEqual($res['place_type'], 'Park');
// Check the name was set correctly
$this->assertEqual($res['name'], 'A Park');
// Check the fullname was set correctly
$this->assertEqual($res['full_name'], 'The Greatest Park');
// Check the country code was set correctly
$this->assertEqual($res['country_code'], 'UK');
// Check the country was set correctly
$this->assertEqual($res['country'], 'United Kingdom');
// Check the icon was set correctly
$this->assertEqual($res['icon'], 'http://www.iconlocation.com');
// Check the point was set correctly
$this->assertPattern('/POINT\(51.514/', $res['longlat']);
$this->assertPattern('/ -0.1167/', $res['longlat']);
// Check the bounding box was set correctly
$this->assertEqual($res['bounding_box'], 'POLYGON((-0.213503 51.512805,-0.105303 51.512805,-0.105303 51.572068,'.
'-0.213503 51.572068,-0.213503 51.512805))');
// Check the map image was set correctly
$this->assertEqual($res['map_image'], 'http://www.mapimage.com');
}

public function testInsertPlacePointCoordsOnly() {
$place = array (
'point_coords' => array(
Expand Down
5 changes: 5 additions & 0 deletions webapp/_lib/model/class.Instance.php
Expand Up @@ -94,6 +94,10 @@ class Instance {
* @var str Date and time of the earliest reply authored by the instance in the datastore.
*/
var $earliest_reply_in_system;
/**
* @var bool Whether or not all the instance's posts have been backfilled.
*/
var $is_archive_loaded_posts = false;
/**
* @var bool Whether or not all the instance's replies have been backfilled.
*/
Expand Down Expand Up @@ -140,6 +144,7 @@ public function __construct($row = false) {
$this->percentage_links = $row['percentage_links'];
$this->earliest_post_in_system = $row['earliest_post_in_system'];
$this->earliest_reply_in_system = $row['earliest_reply_in_system'];
$this->is_archive_loaded_posts = PDODAO::convertDBToBool($row['is_archive_loaded_posts']);
$this->is_archive_loaded_replies = PDODAO::convertDBToBool($row['is_archive_loaded_replies']);
$this->is_archive_loaded_follows = PDODAO::convertDBToBool($row['is_archive_loaded_follows']);
$this->is_public = PDODAO::convertDBToBool($row['is_public']);
Expand Down
3 changes: 3 additions & 0 deletions webapp/_lib/model/class.InstanceMySQLDAO.php
Expand Up @@ -401,6 +401,7 @@ public function save($instance_object, $user_xml_total_posts_by_owner, $logger =

$is_archive_loaded_follows = $this->convertBoolToDB($i->is_archive_loaded_follows);
$is_archive_loaded_replies = $this->convertBoolToDB($i->is_archive_loaded_replies);
$is_archive_loaded_posts = $this->convertBoolToDB($i->is_archive_loaded_posts);

//former subquery 1 for owner_favs_in_system
$q = "SELECT COUNT(*) AS owner_favs_in_system FROM #prefix#favorites ";
Expand Down Expand Up @@ -460,6 +461,7 @@ public function save($instance_object, $user_xml_total_posts_by_owner, $logger =
$q .= "total_follows_in_system = :total_follows_in_system, ";
$q .= "is_archive_loaded_follows = :ialf, ";
$q .= "is_archive_loaded_replies = :ialr, ";
$q .= "is_archive_loaded_posts = :ialp, ";
// For performance reasons, set this to null for now.
$q .= "earliest_reply_in_system = null, ";
// The former subquery is a performance hog, and the field is not in use.
Expand All @@ -485,6 +487,7 @@ public function save($instance_object, $user_xml_total_posts_by_owner, $logger =
':total_follows_in_system' => (int) $total_follows_in_system,
':ialf' => $is_archive_loaded_follows,
':ialr' => $is_archive_loaded_replies,
':ialp' => $is_archive_loaded_posts,
//':earliest_post_in_system' => $earliest_post_in_system,
//':username' => "%".$i->network_username."%",
':ppd' => $posts_per_day,
Expand Down
96 changes: 96 additions & 0 deletions webapp/_lib/model/class.Place.php
@@ -0,0 +1,96 @@
<?php
/**
*
* ThinkUp/webapp/_lib/model/class.Place.php
*
* Copyright (c) 2012 Aaron Kalair
*
* 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 Aaron Kalair
* @author Aaron Kalair <aaronkalair[at]gmail[dot]com>
*/

class Place {
/**
* @var int Internal unique ID.
*/
var $id;
/**
* @var str Place ID on a given network.
*/
var $place_id;
/**
* @var str Type of place.
*/
var $place_type;
/**
* @var str Short name of a place.
*/
var $name;
/**
* @var str Full name of a place.
*/
var $full_name;
/**
* @var str Country code where the place is located.
*/
var $country_code;
/**
* @var str Country where the place is located.
*/
var $country;
/**
* @var str The network this place appears on in lower-case, e.g. twitter or facebook.
*/
var $network;
/**
* @var point Longitude/lattitude point.
*/
var $longlat;
/**
* @var polygon Bounding box of place.
*/
var $bounding_box;
/**
* @var icon Icon that represents the place
*/
var $icon;
/**
* @var map_image URL to a image of the map representing the area this location is in
*/
var $map_image;

public function __construct($row = false) {
if ($row) {
$this->id = $row['id'];
$this->place_id = $row['place_id'];
$this->place_type = $row['place_type'];
$this->name = $row['name'];
$this->full_name = $row['full_name'];
$this->country_code = $row['country_code'];
$this->country = $row['country'];
$this->network = $row['network'];
$this->longlat = $row['longlat'];
$this->bounding_box = $row['bounding_box'];
$this->icon = $row['icon'];
$this->map_image = $row['map_image'];
}
}
}

43 changes: 40 additions & 3 deletions webapp/_lib/model/class.PlaceMySQLDAO.php
Expand Up @@ -25,7 +25,6 @@
* @author Amy Unruh
*/
class PlaceMySQLDAO extends PDODAO implements PlaceDAO {

public function insertPlace(array $place, $post_id, $network) {
if (!$place) {
return null;
Expand Down Expand Up @@ -63,6 +62,8 @@ public function insertPlace(array $place, $post_id, $network) {
':network' => $network,
':bounding_box' => $polystr
);
if ($this->profiler_enabled) Profiler::setDAOMethod(__METHOD__);

$ps = $this->execute($q, $vars);
$res = $this->getUpdateCount($ps);
}
Expand All @@ -82,14 +83,47 @@ public function insertPlace(array $place, $post_id, $network) {
':point' => $pcstr,
':network' => $network
);
if ($this->profiler_enabled) Profiler::setDAOMethod(__METHOD__);

$ps = $this->execute($q, $vars);
$res2 = $this->getUpdateCount($ps);
}
}

public function insertGenericPlace(array $place, $network) {
if (!$place) {
return null;
}
$q = "INSERT IGNORE INTO #prefix#places ";
$q .= "(place_id, place_type, name, full_name, country_code, country, network, bounding_box, longlat, icon, ";
$q .= "map_image) VALUES (:place_id, :place_type, :name, :full_name, :country_code, :country, :network, ";
$q .= "PolygonFromText(:bounding_box), GeomFromText(:latlng), :icon, :map_image )";
$vars = array(
':place_id' => isset($place['id']) ? (string)$place['id'] : null,
':place_type' => isset($place['place_type']) ? $place['place_type'] : null,
':name' => isset($place['name']) ? $place['name'] : null,
':full_name' => isset($place['full_name']) ? $place['full_name'] : null,
':country_code' => isset($place['country_code']) ? $place['country_code'] : null,
':country' => isset($place['country']) ? $place['country'] : null,
':network' => isset($network) ? $network : null,
':bounding_box' => isset($place['bounding_box']) ? $place['bounding_box'] : null,
':latlng' => isset($place['lat_lng']) ? $place['lat_lng'] : null,
':icon' => isset($place['icon']) ? $place['icon'] : null,
':map_image' => isset($place['map_image']) ? $place['map_image'] : null
);
if ($this->profiler_enabled) Profiler::setDAOMethod(__METHOD__);

$ps = $this->execute($q, $vars);
$res = $this->getUpdateCount($ps);
return $res;
}

public function getPlaceByID($place_id) {
$q = "SELECT id, place_id, place_type, name, full_name, country_code, country, network, AsText(longlat) " .
"AS longlat, AsText(bounding_box) AS bounding_box FROM #prefix#places WHERE place_id = :place_id";
$q = "SELECT id, place_id, place_type, name, full_name, country_code, country, network, icon, map_image, ";
$q .= "AsText(longlat) AS longlat, AsText(bounding_box) AS bounding_box FROM #prefix#places ";
$q .= "WHERE place_id = :place_id";
if ($this->profiler_enabled) Profiler::setDAOMethod(__METHOD__);

$ps = $this->execute($q, array( ':place_id' => $place_id));
$row = $this->getDataRowAsArray($ps);
if ($row) {
Expand All @@ -102,6 +136,9 @@ public function getPlaceByID($place_id) {
public function getPostPlace($post_id, $network = 'twitter') {
$q = "SELECT id, AsText(longlat) AS longlat, post_id, place_id, network FROM #prefix#places_posts " .
"WHERE post_id = :post_id AND network = :network";

if ($this->profiler_enabled) Profiler::setDAOMethod(__METHOD__);

$ps = $this->execute($q, array(
':post_id' => (string)$post_id,
':network' => $network));
Expand Down
6 changes: 3 additions & 3 deletions webapp/_lib/model/class.Post.php
Expand Up @@ -182,10 +182,11 @@ class Post {
* @return Post
*/
public function __construct($val) {
$this->id = $val["id"];
// a fix for getPost() where the join of the links table column links.id overides the posts.id
if (!isset($this->id) && isset($val["post_key"])) {
if (isset($val["post_key"])) {
$this->id = $val["post_key"];
} else {
$this->id = $val["id"];
}
$this->post_id = $val["post_id"];
$this->author_user_id = $val["author_user_id"];
Expand Down Expand Up @@ -294,7 +295,6 @@ public static function extractMentions($post_text) {
*/
public static function extractHashtags($post_text) {
preg_match_all('/(^|[^a-z0-9_])#([a-z0-9_]+)/i', $post_text, $matches);

// sometimes there's leading or trailing whitespace on the match, trim it
foreach ($matches[0] as $key=>$match) {
$matches[0][$key] = trim($match, ' ');
Expand Down
12 changes: 10 additions & 2 deletions webapp/_lib/model/interface.PlaceDAO.php
Expand Up @@ -44,9 +44,17 @@ public function insertPlace(array $place, $post_id, $network);
public function getPlaceByID($place_id);
/**
* Get a post's place information.
* @param int $post_id
* @param str $post_id
* @param str $network
* @return array tu_places_posts array
* @return array Rows/columns from tu_places_posts table
*/
public function getPostPlace($post_id, $network = 'twitter');
/**
* Insert a place into storage. Performs minimal pre-processing on the data passed in.
* Note: longlat needs to be of type 'point' and bounding box of type 'polygon'
* @var arr $place Array of data to insert
* @var str $network Network this place is from
* @return mixed Update count or null
*/
public function insertGenericPlace(array $place, $network);
}
23 changes: 23 additions & 0 deletions webapp/_lib/view/dashboard.tpl
Expand Up @@ -133,6 +133,29 @@
{/foreach}
</div>
{/if}
{if $posts_flashback|@count > 0 }
<div class="section">
<h2>Flashback: On This Day In Years Past</h2>
{if $instance->network eq 'foursquare'}
<style type="text/css">
{literal}
.map-image-container { width: 130px; height: 130px; padding-bottom : 30px; }
img.map-image2 {float:left;margin:6px 0 0 0;width:150px;height:150px;}
img.place-icon2 {position: relative;width: 32px;height: 32px;top: -146px;left: 5px;}
{/literal}
</style>
{foreach from=$posts_flashback item=post name=foo}
{include file="_post.checkin.tpl"}
{/foreach}
{else}
{foreach from=$posts_flashback key=tid item=post name=foo}
{include file="_post.counts_no_author.tpl" post=$post show_favorites_instead_of_retweets=false}
{/foreach}
{/if}
</div>
{/if}
{if $instance->network eq 'twitter' }
<div class="section" style="float : left; clear : none; width : 345px;">
{include file="_dashboard.posttypes.tpl"}
Expand Down
7 changes: 5 additions & 2 deletions webapp/install/sql/build-db_mysql.sql
@@ -1,6 +1,6 @@
--
-- ThinkUp Database Creation Script
-- Auto-generated by thinkup/extras/scripts/migratedb script on 2012-07-05
-- Auto-generated by thinkup/extras/scripts/migratedb script on 2012-08-27
--

ALTER DATABASE DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Expand Down Expand Up @@ -169,6 +169,7 @@ CREATE TABLE tu_instances (
percentage_links decimal(4,2) DEFAULT NULL COMMENT 'Percent of an instance''s posts which contain links.',
earliest_post_in_system datetime DEFAULT NULL COMMENT 'Date and time of the earliest post authored by the instance in the datastore.',
earliest_reply_in_system datetime DEFAULT NULL COMMENT 'Date and time of the earliest reply authored by the instance in the datastore.',
is_archive_loaded_posts int(1) NOT NULL DEFAULT '0' COMMENT 'Whether or not all the instance''s posts have been backfilled.',
is_archive_loaded_replies int(1) NOT NULL DEFAULT '0' COMMENT 'Whether or not all the instance''s replies have been backfilled.',
is_archive_loaded_follows int(1) NOT NULL DEFAULT '0' COMMENT 'Whether or not all the instance''s follows have been backfilled.',
is_public int(1) NOT NULL DEFAULT '0' COMMENT 'Whether or not instance is public in ThinkUp, that is, viewable when no ThinkUp user is logged in.',
Expand Down Expand Up @@ -335,6 +336,8 @@ CREATE TABLE tu_places (
network varchar(20) NOT NULL DEFAULT 'twitter' COMMENT 'The network this place appears on in lower-case, e.g. twitter or facebook.',
longlat point DEFAULT NULL COMMENT 'Longitude/lattitude point.',
bounding_box polygon DEFAULT NULL COMMENT 'Bounding box of place.',
icon varchar(255) DEFAULT NULL COMMENT 'URL to an icon which represents the place type.',
map_image varchar(255) DEFAULT NULL COMMENT 'URL to an image of a map representing the area this place is in.',
PRIMARY KEY (id),
UNIQUE KEY place_id (place_id,network)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Places on a given network.';
Expand Down Expand Up @@ -500,7 +503,7 @@ CREATE TABLE tu_users (
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Service user details.';


-- Dump completed on 2012-07-05 23:51:04
-- Dump completed on 2012-08-27 18:12:34

--
-- Insert DB Version
Expand Down
@@ -0,0 +1,3 @@
ALTER TABLE tu_places ADD icon VARCHAR ( 255 ) COMMENT 'URL to an icon which represents the place type.';
ALTER TABLE tu_places ADD map_image VARCHAR( 255 ) COMMENT 'URL to an image of a map representing the area this place is in.';
ALTER TABLE tu_instances ADD is_archive_loaded_posts INT( 1 ) NOT NULL DEFAULT 0 COMMENT 'Whether or not all the instance''s posts have been backfilled.' AFTER earliest_reply_in_system;

0 comments on commit 94cc96d

Please sign in to comment.