ついったったーの情報をデータベースに突っ込む！
久々に Perl 書いた
#! /usr/bin/perl use strict; use warnings; use utf8; use DBI; use HTTP::Date; use Net::Twitter; use File::Basename; use Data::Dumper; use Config::Pit; my $config = pit_get('twitter.com'); my $dbh = DBI->connect('dbi:SQLite:twitter.db', '', '', { AutoCommit => 0 }); die DBI->errstr unless $dbh; my $twitter = Net::Twitter->new( username => $config->{username}, password => $config->{password} ); my $limit = $twitter->rate_limit_status; if ($limit->{remaining_hits} == 0) { while($limit->{reset_time_in_seconds} - time() > -10) { sleep(1); print "limit remaining time: " . ($limit->{reset_time_in_seconds} - time()) . " s \n"; } } my $rv = $dbh->do('CREATE TABLE IF NOT EXISTS status (id INTEGER PRIMARY KEY, last_cursor INTEGER)'); die DBI->errstr unless $rv; $rv = $dbh->do(q/ CREATE TABLE IF NOT EXISTS user ( id INTEGER PRIMARY KEY ON CONFLICT REPLACE, screen_name TEXT, name TEXT, created_at DATETIME, description TEXT, url TEXT, lang TEXT, location TEXT, profile_image_url TEXT, followers_count INTEGER, friends_count INTEGER, favourites_count INTEGER, statuses_count INTEGER, time_zone TEXT, utc_offset INTEGER, geo_enabled BOOLEAN, profile_background_image_url TEXT, profile_text_color INTEGER, profile_link_color INTEGER, profile_sidebar_fill_color INTEGER, profile_sidebar_border_color INTEGER, profile_background_color INTEGER, profile_background_tile INTEGER, notifications BOOLEAN, following BOOLEAN, protected BOOLEAN, verified BOOLEAN, contributors_enabled INTEGER, last_status_source TEXT, last_status_truncated INTEGER, last_status_favorited INTEGER, last_status_created_at DATETIME, last_status_text TEXT, last_status_in_reply_to_user_id INTEGER, last_status_id INTEGER, last_status_in_reply_to_status_id INTEGER, last_status_in_reply_to_screen_name TEXT ) /); die DBI->errstr unless $rv; my $sth_save_status = $dbh->prepare(q/ INSERT OR REPLACE INTO status ( id, last_cursor ) VALUES (1, ?) /); die DBI->errstr unless $sth_save_status; my $sth_load_status = $dbh->prepare(q/ SELECT id, last_cursor FROM status WHERE id = 1 /); die DBI->errstr unless $sth_load_status; my $sth_insert_user = $dbh->prepare(q/ INSERT OR REPLACE INTO user ( id, screen_name, name, created_at, description, url, lang, location, profile_image_url, followers_count, friends_count, favourites_count, statuses_count, time_zone, utc_offset, geo_enabled, profile_background_image_url, profile_text_color, profile_link_color, profile_sidebar_fill_color, profile_sidebar_border_color, profile_background_color, profile_background_tile, notifications, following, protected, verified, contributors_enabled, last_status_source, last_status_truncated, last_status_favorited, last_status_created_at, last_status_text, last_status_in_reply_to_user_id, last_status_id, last_status_in_reply_to_status_id, last_status_in_reply_to_screen_name ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) /); die DBI->errstr unless $sth_insert_user; my $sth_check_user = $dbh->prepare(q/ SELECT id FROM user WHERE id = ? /); die DBI->errstr unless $sth_check_user; my $followers; my $cursor = -1; $rv = $sth_load_status->execute; die $sth_load_status->errstr unless $rv; my $status_row = $sth_load_status->fetchrow_arrayref; if ($status_row) { my ($id, $last_cursor) = @$status_row; $cursor = $last_cursor; print "loaded $cursor\n"; } while (1) { last unless $cursor; eval { $followers = $twitter->followers({ cursor => $cursor }); if (defined($followers)) { eval { for my $tweeter (@{$followers->{users}}) { insert_user($tweeter); } my $rv = $sth_save_status->execute($followers->{next_cursor}); die $sth_save_status->errstr unless $rv; $dbh->commit; }; if ($@) { $dbh->rollback; die $@; } } else { print "current_cursor = " . $cursor . "!!!!\n"; print "try again!!!!\n"; print $twitter->http_message . "\n"; if ($twitter->http_message eq 'Bad Request') { die $twitter->http_message; } $followers = { next_cursor => ($cursor - 100000000000000) } } }; if ($@) { die $@; } $cursor = $followers->{next_cursor}; } $cursor = -1; while (1) { last unless $cursor; my $follower_ids; my $tweeter_id; eval { $follower_ids = $twitter->followers_ids({ cursor => $cursor }); if (defined($follower_ids)) { for $tweeter_id (@{$follower_ids->{ids}}) { eval { my $rv = $sth_check_user->execute($tweeter_id); die $sth_check_user->errstr unless $rv; my $tweeter_row = $sth_check_user->fetchrow_arrayref; unless ($tweeter_row) { my $tweeter = $twitter->show_user($tweeter_id); if ($tweeter) { insert_user($tweeter); } else { print "id = $tweeter_id , message = " . $twitter->http_message . "\n"; if ($twitter->http_message eq 'Bad Request') { die $twitter->http_message; } } } $dbh->commit; }; if ($@) { $dbh->rollback; die $@; } } } else { print "current_cursor = " . $cursor . "!!!!\n"; die $twitter->http_message; } }; if ($@) { die $@; } $cursor = $follower_ids->{next_cursor}; } sub insert_user { my $tweeter = shift; my $rv = $sth_insert_user->execute( $tweeter->{id}, $tweeter->{screen_name}, $tweeter->{name}, create_iso_time($tweeter->{created_at}), $tweeter->{description}, $tweeter->{url}, $tweeter->{lang}, $tweeter->{location}, $tweeter->{profile_image_url}, $tweeter->{followers_count}, $tweeter->{friends_count}, $tweeter->{favourites_count}, $tweeter->{statuses_count}, $tweeter->{time_zone}, $tweeter->{utc_offset}, $tweeter->{geo_enabled}, $tweeter->{profile_background_image_url}, $tweeter->{profile_text_color}, $tweeter->{profile_link_color}, $tweeter->{profile_sidebar_fill_color}, $tweeter->{profile_sidebar_border_color}, $tweeter->{profile_background_color}, $tweeter->{profile_background_tile}, $tweeter->{notifications}, $tweeter->{following}, $tweeter->{protected}, $tweeter->{verified}, $tweeter->{contributors_enabled}, $tweeter->{status}->{source}, $tweeter->{status}->{truncated}, $tweeter->{status}->{favorited}, create_iso_time($tweeter->{status}->{created_at}), $tweeter->{status}->{text}, $tweeter->{status}->{in_reply_to_user_id}, $tweeter->{status}->{id}, $tweeter->{status}->{in_reply_to_status_id}, $tweeter->{status}->{in_reply_to_screen_name}, ); die $sth_insert_user->errstr unless $rv; print "data insert " . $tweeter->{screen_name} . "\n"; } sub create_iso_time { my $created_at = shift; if (defined $created_at) { $created_at =~ s/\+0000/UTC/; $created_at = HTTP::Date::time2iso(HTTP::Date::str2time($created_at)); } else { $created_at = undef; } return $created_at; }
