/webapp/_lib/model/class.ExportMySQLDAO.php
PHP | 359 lines | 286 code | 30 blank | 43 comment | 33 complexity | f2a0a90e55e511c5016f4d744c862481 MD5 | raw file
Possible License(s): Apache-2.0, GPL-2.0, GPL-3.0, LGPL-2.1
- <?php
- /**
- *
- * ThinkUp/webapp/_lib/model/class.ExportMySQLDAO.php
- *
- * Copyright (c) 2011-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/>.
- *
- * Export MySQL Data Access Object
- *
- * @license http://www.gnu.org/licenses/gpl.html
- * @copyright 2011-2012 Gina Trapani
- * @author Gina Trapani <ginatrapani[at]gmail[dot]com>
- */
- class ExportMySQLDAO extends PDODAO implements ExportDAO {
- /**
- * Name of the temporary posts export table.
- * @var str
- */
- static $exported_posts_table_name = 'posts_tmp';
- /**
- * Name of the temporary follows export table.
- * @var str
- */
- static $exported_follows_table_name = 'follows_tmp';
- public function createExportedPostsTable() {
- $q = "CREATE TABLE #prefix#".self::$exported_posts_table_name." LIKE #prefix#posts;";
- if ($this->profiler_enabled) Profiler::setDAOMethod(__METHOD__);
- $stmt = $this->execute($q);
- $q = "ALTER TABLE #prefix#".self::$exported_posts_table_name." DROP id";
- $stmt = $this->execute($q);
- return $this->doesExportedPostsTableExist();
- }
- public function doesExportedPostsTableExist() {
- $q = "SHOW TABLES LIKE '#prefix#" . self::$exported_posts_table_name . "'";
- if ($this->profiler_enabled) Profiler::setDAOMethod(__METHOD__);
- $stmt = $this->execute($q);
- $tables = $this->getDataRowAsArray($stmt);
- return !empty($tables);
- }
- public function dropExportedPostsTable() {
- if ( self::doesExportedPostsTableExist() ) {
- if ($this->profiler_enabled) Profiler::setDAOMethod(__METHOD__);
- $q = "DROP TABLE #prefix#".self::$exported_posts_table_name;
- $stmt = $this->execute($q);
- }
- return !$this->doesExportedPostsTableExist();
- }
- public function createExportedFollowsTable() {
- $q = "CREATE TABLE #prefix#".self::$exported_follows_table_name." LIKE #prefix#follows;";
- if ($this->profiler_enabled) Profiler::setDAOMethod(__METHOD__);
- $stmt = $this->execute($q);
- return $this->doesExportedFollowsTableExist();
- }
- public function doesExportedFollowsTableExist() {
- $q = "SHOW TABLES LIKE '#prefix#" . self::$exported_follows_table_name . "'";
- if ($this->profiler_enabled) Profiler::setDAOMethod(__METHOD__);
- $stmt = $this->execute($q);
- $tables = $this->getDataRowAsArray($stmt);
- return !empty($tables);
- }
- public function dropExportedFollowsTable() {
- if ( self::doesExportedFollowsTableExist() ) {
- if ($this->profiler_enabled) Profiler::setDAOMethod(__METHOD__);
- $q = "DROP TABLE #prefix#".self::$exported_follows_table_name;
- $stmt = $this->execute($q);
- }
- return !$this->doesExportedFollowsTableExist();
- }
- public function exportPostsByServiceUser($username, $service) {
- if ( !self::doesExportedPostsTableExist() ) {
- self::createExportedPostsTable();
- }
- //select all-but-id into posts_to_export from posts where service user is the author
- $q = "INSERT IGNORE INTO #prefix#".self::$exported_posts_table_name." SELECT ";
- $q .= self::getExportFields('posts') . " ";
- $q .= "FROM #prefix#posts WHERE ";
- $q .= "author_username = :author_username AND network = :network";
- $vars = array(
- ':author_username'=>$username,
- ':network'=>$service
- );
- if ($this->profiler_enabled) Profiler::setDAOMethod(__METHOD__);
- $stmt = $this->execute($q, $vars);
- return $this->getUpdateCount($stmt);
- }
- public function exportRepliesRetweetsOfPosts($posts_to_process) {
- if ( !self::doesExportedPostsTableExist() ) {
- self::createExportedPostsTable();
- }
- $total_posts_exported = 0;
- foreach ($posts_to_process as $post) {
- $q = "INSERT IGNORE INTO #prefix#".self::$exported_posts_table_name." SELECT ";
- $q .= self::getExportFields('posts'). " ";
- $q .= "FROM #prefix#posts WHERE ";
- $q .= "in_reply_to_post_id = :post_id AND network=:network;";
- $vars = array("post_id"=>$post->post_id, "network"=>$post->network);
- $stmt = $this->execute($q, $vars);
- $total_posts_exported = $total_posts_exported + $this->getUpdateCount($stmt);
- $q = "INSERT IGNORE INTO #prefix#".self::$exported_posts_table_name." SELECT ";
- $q .= self::getExportFields('posts'). " ";
- $q .= "FROM #prefix#posts WHERE ";
- $q .= "in_retweet_of_post_id = :post_id AND network=:network;";
- $vars = array("post_id"=>$post->post_id, "network"=>$post->network);
- $stmt = $this->execute($q, $vars);
- $total_posts_exported = $total_posts_exported + $this->getUpdateCount($stmt);
- }
- return $total_posts_exported;
- }
- public function exportMentionsOfServiceUser($username, $service) {
- if ( !self::doesExportedPostsTableExist() ) {
- self::createExportedPostsTable();
- }
- $author_username = '@'.$username;
- //select all-but-id into posts_to_export from posts where service username is mentioned
- $q = "INSERT IGNORE INTO #prefix#".self::$exported_posts_table_name." SELECT ";
- $q .= self::getExportFields('posts') . " ";
- $q .= "FROM #prefix#posts WHERE ";
- $q .= "network = :network AND ";
- if ( strlen($username) > PostMySQLDAO::FULLTEXT_CHAR_MINIMUM ) {
- $q .= "MATCH (`post_text`) AGAINST(:author_username IN BOOLEAN MODE) ";
- } else {
- $author_username = '%'.$author_username .'%';
- $q .= "post_text LIKE :author_username ";
- }
- $vars = array(
- ':author_username'=>$author_username,
- ':network'=>$service
- );
- if ($this->profiler_enabled) Profiler::setDAOMethod(__METHOD__);
- $stmt = $this->execute($q, $vars);
- return $this->getUpdateCount($stmt);
- }
- public function exportPostsServiceUserRepliedTo($username, $service) {
- if ( !self::doesExportedPostsTableExist() ) {
- self::createExportedPostsTable();
- }
- $page = 1;
- $page_size = 500;
- $total_posts_inserted = 0;
- $posts_to_insert = self::getRepliedToPostIDs($username, $service, $page, $page_size);
- while (count($posts_to_insert) > 0 ) {
- foreach ($posts_to_insert as $post) {
- $q = "INSERT IGNORE INTO #prefix#".self::$exported_posts_table_name." SELECT ";
- $q .= self::getExportFields('posts') . " ";
- $q .= "FROM #prefix#posts WHERE ";
- $q .= "network = :network AND post_id=:post_id;";
- $vars = array(
- ':post_id'=>$post['post_id'],
- ':network'=>$service
- );
- if ($this->profiler_enabled) Profiler::setDAOMethod(__METHOD__);
- $stmt = $this->execute($q, $vars);
- $total_posts_inserted = $total_posts_inserted + $this->getUpdateCount($stmt);
- }
- $page = $page+1;
- $posts_to_insert = self::getRepliedToPostIDs($username, $service, $page, $page_size);
- }
- return $total_posts_inserted;
- }
- private function getRepliedToPostIDs($username, $network, $page, $page_size) {
- $page = $page - 1;
- $start_on = $page * $page_size;
- $q = "SELECT in_reply_to_post_id as post_id FROM #prefix#posts WHERE ";
- $q .= "author_username = :author_username AND network=:network AND in_reply_to_post_id IS NOT null ";
- $q .= "LIMIT ".$start_on.", ".$page_size;
- $vars = array(
- ':author_username'=>$username,
- ':network'=>$network
- );
- if ($this->profiler_enabled) Profiler::setDAOMethod(__METHOD__);
- $stmt = $this->execute($q, $vars);
- return $this->getDataRowsAsArrays($stmt);
- }
- public function exportFavoritesOfServiceUser($user_id, $service, $favorites_file) {
- if ( !self::doesExportedPostsTableExist() ) {
- self::createExportedPostsTable();
- }
- $q = "SELECT * INTO OUTFILE '$favorites_file' FROM #prefix#favorites WHERE fav_of_user_id = :user_id ".
- "AND network = :network;";
- $vars = array(
- ':user_id'=>$user_id,
- ':network'=>$service
- );
- $stmt = $this->execute($q, $vars);
- $q = "SELECT post_id FROM #prefix#favorites WHERE fav_of_user_id = :user_id AND network = :network;";
- $stmt = $this->execute($q, $vars);
- $fav_ids = $this->getDataRowsAsArrays($stmt);
- $total_favs_exported = 0;
- foreach ($fav_ids as $post) {
- $q = "INSERT IGNORE INTO #prefix#".self::$exported_posts_table_name." SELECT ";
- $q .= self::getExportFields('posts'). " ";
- $q .= "FROM #prefix#posts WHERE ";
- $q .= "post_id = :post_id AND network = :network";
- $vars = array(
- ':post_id'=>$post['post_id'],
- ':network'=>$service
- );
- $stmt = $this->execute($q, $vars);
- $total_favs_exported = $total_favs_exported + $this->getUpdateCount($stmt);
- }
- return $total_favs_exported;
- }
- public function exportPostsLinksUsersToFile($posts_file, $links_file, $users_file) {
- if (file_exists($posts_file)) {
- unlink($posts_file);
- }
- if (file_exists($links_file)) {
- unlink($links_file);
- }
- if (file_exists($users_file)) {
- unlink($users_file);
- }
- if ( !self::doesExportedPostsTableExist() ) {
- self::createExportedPostsTable();
- }
- $q = "SELECT * INTO OUTFILE '$posts_file' FROM #prefix#".self::$exported_posts_table_name;
- $stmt = $this->execute($q);
- $q = "SELECT ".$this->getExportFields('links', 'l')." INTO OUTFILE '$links_file' FROM #prefix#links l ";
- $q .= "INNER JOIN #prefix#posts p ON l.post_key = p.id;";
- $stmt = $this->execute($q);
- $q = "SELECT DISTINCT ".$this->getExportFields('users', 'u')." INTO OUTFILE '$users_file' ";
- $q .= "FROM #prefix#users u INNER JOIN #prefix#".self::$exported_posts_table_name.
- " p ON p.author_user_id = u.user_id AND p.network = u.network;";
- $stmt = $this->execute($q);
- }
- public function exportFollowsUsersToFile($user_id, $network, $follows_file, $users_followers_file,
- $users_followees_file) {
- if (file_exists($follows_file)) {
- unlink($follows_file);
- }
- if (file_exists($users_followers_file)) {
- unlink($users_followers_file);
- }
- if (file_exists($users_followees_file)) {
- unlink($users_followees_file);
- }
- self::createExportedFollowsTable();
- //export follows to temp table
- $q = "INSERT IGNORE INTO #prefix#".self::$exported_follows_table_name." SELECT * FROM #prefix#follows ";
- $q .= "WHERE network=:network AND user_id = :user_id;";
- $vars = array(
- ':user_id'=>$user_id,
- ':network'=>$network
- );
- $stmt = $this->execute($q, $vars);
- //export followees to temp table
- $q = "INSERT IGNORE INTO #prefix#".self::$exported_follows_table_name." SELECT * FROM #prefix#follows ";
- $q .= "WHERE network=:network AND follower_id = :user_id;";
- $vars = array(
- ':user_id'=>$user_id,
- ':network'=>$network
- );
- $stmt = $this->execute($q, $vars);
- //export temp table to file
- $q = "SELECT * INTO OUTFILE '$follows_file' FROM #prefix#".self::$exported_follows_table_name." ";
- $stmt = $this->execute($q, $vars);
- //export users join on temp table followers
- $q = "SELECT DISTINCT ".$this->getExportFields('users', 'u'). " FROM #prefix#users u ";
- $q .= "INNER JOIN #prefix#".self::$exported_follows_table_name.
- " f ON f.network = u.network AND f.follower_id = u.user_id ";
- $q .= "INTO OUTFILE '$users_followees_file' ";
- $vars = array(
- ':user_id'=>$user_id,
- ':network'=>$network
- );
- $stmt = $this->execute($q, $vars);
- //export users join on temp table followers
- $q = "SELECT DISTINCT ".$this->getExportFields('users', 'u'). " FROM #prefix#users u ";
- $q .= "INNER JOIN #prefix#".self::$exported_follows_table_name.
- " f ON f.network = u.network AND f.user_id = u.user_id ";
- $q .= "INTO OUTFILE '$users_followers_file' ";
- $vars = array(
- ':user_id'=>$user_id,
- ':network'=>$network
- );
- $stmt = $this->execute($q, $vars);
- //drop temp table
- self::dropExportedFollowsTable();
- }
- public function exportFollowerCountToFile($user_id, $network, $file) {
- if (file_exists($file)) {
- unlink($file);
- }
- $q = "SELECT * INTO OUTFILE '$file' FROM #prefix#follower_count WHERE ";
- $q .= "network=:network AND network_user_id=:user_id GROUP by date;";
- $vars = array(
- ':user_id'=>$user_id,
- ':network'=>$network
- );
- $stmt = $this->execute($q, $vars);
- }
- public function exportGeoToFile($file) {
- if (file_exists($file)) {
- unlink($file);
- }
- $q = "SELECT * INTO OUTFILE '$file' FROM #prefix#encoded_locations;";
- $stmt = $this->execute($q);
- }
- public function getExportFields($table_name, $prefix='') {
- $q = "DESCRIBE #prefix#".$table_name.";";
- $stmt = $this->execute($q);
- $fields = $this->getDataRowsAsArrays($stmt);
- $fields_string = '';
- foreach ($fields as $field) {
- if ($fields_string != '') {
- $fields_string .= ", ";
- }
- if ($field['Field'] != 'id') {
- $fields_string .= $prefix.(($prefix!='')?".":"").$field['Field'];
- }
- }
- return $fields_string;
- }
- }