ScrollingText

Welcome back to another, and probably the last, instalment of the tweet dump project.

The old tweet dump code was done locally. I wanted to see how things would be affected if I ran the tweet dump code to a remote server. Just to get an idea on how travel would affect latency.

So I changed things up a bit, I created a sql database through my hosting provider and imported the schema into the remote database. At which point I also modified up the tweet dump code to use this new remote database. And after running the python script 5 times, here are my numbers:

In my tweetdump code I have a lot of going back and forth between the server and client;

def getId(in_id):
	sql =  "select id from ids where twit_id = '" + in_id + "'"
	return runQuery(sql)
 
def addId(in_id):
	sql= "insert into ids values (null, '" + in_id + "')"
	runQuery(sql)
 
def addTweet(in_id, in_tweet):
	sql = "insert into tweets values ('" + in_id + "',\"" + in_tweet + "\")"
	runQuery(sql)
...
site_id = getId(ids[j])
if( not site_id):
    addId(ids[j])
    site_id = getId(ids[j])

When you see the three functions making up the code block near the bottom, you might realize that there are three seperate sql calls to the server. And while this might be fine for a local database, this is not good for a remote one. So I decided to try my hand at creating a sql function to reduce some of the back and forth between the two entities.

delimiter //
 
DROP FUNCTION IF EXISTS `get_set_ids`//
CREATE FUNCTION get_set_ids( in_tweet_id BIGINT )
RETURNS int(10) UNSIGNED
BEGIN
DECLARE new_id int(10) UNSIGNED;
SELECT id INTO new_id FROM ids WHERE twit_id = in_tweet_id;
IF ISNULL(new_id) THEN
     INSERT INTO ids VALUES (NULL, in_tweet_id);
     SELECT id INTO new_id FROM ids WHERE twit_id = in_tweet_id;
END IF;
RETURN new_id;
END //
 
delimiter ;

The function above takes the guess work away from the client, and keeps it within the server. By doing this we avoid and entire round of communication between the client & server. So in theory we should see at least some kind of speed-up.

With the function above created I then went and modified the script to take advantage of the function:

def get_set_id(in_id):
	sql = "select get_set_ids(" + in_id + ")"
	return runQuery(sql)
...
site_id = get_set_id(ids[j])
addTweet(str(site_id[0][0]), final_texts[j])

After a bit of testing to make sure things worked, I ran five timed tests (on the same hardware and from the same location, to try and reduce any variables that might crop up).

The difference between the two averages is about 22 seconds. And to be honest, I did not expect these changes to cut down my times by about half. This was a little bit of a shock to me. I guess in this end this is an example of how design can really matter.

Sorry, today's post will not contain anything technical. The past two weeks I've been busy studying for my last finals, of which I passed. :) As well as packing and moving from Portland to San Francisco. Because of all the craziness that life has bestowed upon me recently I won't be able to post anything geeky this week. But there is a silver lining to this, I'll post something worth while next week.

Also, I've decided to change my format slightly, I will be posting twice a month, instead of weekly, from now on. I'm doing this in the hopes in reducing the timeline of posting, that I can produce technical posts of higher quality, which I'm sure everyone would appreciate.

See you all next week.

First off, Happy Thanksgiving to everyone, US readers especially. I know I should have written this sooner, but the holiday kept me pretty busy this year. And I hope you can accept my late well wishes.

And after you've had your fill of turkey, stuffing, and some pumpkin pie maybe you got the chance to play around with what I posted last week. And now lets build from that. Let's change the situation around a little bit; and say that a command line utility isn't your thing for this project. And you would like to use PHP.

First off, you'll want to make sure that your web server has curl compiled in. And the easiest way is to use phpinfo() to find it, and you'll want to look for this:

If you see something similar to what I have above, your good. Otherwise you'll either need to recompile your php to include libcurl, or complain to your hosting provider. Afterwards we can accomplish what we want with a couple lines of code:

And now in your PHP code, you'll have a variable called $content that will contain all of the html code from the website. And how you wanna parse information outside of that is up to you. Of course if anyone has questions I will be happy to help out as best as I can.

According to Wikipedia web scraping is "is a computer software technique of extracting information from websites." So let's say you wanted to write a script to grab the number of trophies from Sony's PS3 system. Web scraping might be a technique you might want to consider. First move would be to use a web browser to get the URL to eventually scrape:

"http://profiles.us.playstation.com/playstation/psn/profiles/L_Cypher" (yes, that is my ps3 user id and yes those are my stats)

Next step one would probably use a text browser to facilitate the web scraping, like wget. So next step would be to put these two together and see what happens:

wget http://profiles.us.playstation.com/playstation/psn/profiles/L_Cypher

But what is this:

wget <a href="http://profiles.us.playstation.com/playstation/psn/profiles/L_Cypher
--2009-11-17" title="http://profiles.us.playstation.com/playstation/psn/profiles/L_Cypher
--2009-11-17">http://profiles.us.playstation.com/playstation/psn/profiles/L_Cypher
--2...</a> 19:52:56--  <a href="http://profiles.us.playstation.com/playstation/psn/profiles/L_Cypher
Resolving" title="http://profiles.us.playstation.com/playstation/psn/profiles/L_Cypher
Resolving">http://profiles.us.playstation.com/playstation/psn/profiles/L_Cypher
Res...</a> profiles.us.playstation.com... 174.129.213.213
Connecting to profiles.us.playstation.com|174.129.213.213|:80... connected.
HTTP request sent, awaiting response... 403 Forbidden
2009-11-17 19:52:56 ERROR 403: Forbidden.

Let's see if this is what happens when we use curl (another website grabber):
 curl http://profiles.us.playstation.com/playstation/psn/profiles/L_Cypher

Now the response is even different than when using wget, but the most important part is in the middle of the response ( shortened for readability):

<h2 align="center">Forbidden</h2>
<h4 align="center">Access is forbidden to this URL</h4>

So, now we see 3 inconsistencies here. With a "normal" web browser, things work fine. Wget is down right rejected, and curl is reject but a little more politely. At this point it looks like our web scraping project would seem lost, but I don't give up that quickly. One thing these three things have in common is a user agent. So if we can tell the website we are a different user agent than what we really are, things should work. Luckily enough for curl has this ability. And after a little research we can now try new command to see if it works:

curl -A 'Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.4) Gecko/20091030 Gentoo Firefox/3.5.4' http://profiles.us.playstation.com/playstation/psn/profiles/L_Cypher

And VIOLA, we now get access to the site as if we were using firefox. (html code not included for readability. If you wanna see the code you can run it yourself.)

This is a good idea to keep in your mental toolbox, you never know when you might need a trick like this in the future.

Update (4/18/2010): A nice guy named John over at ps3inside.de emailed me and told me that the link above no longer works. I tested it myself and John is correct. It turns out that Sony changed things up a little bit and now the trophies page is all done in javascript. Being that javascript isn't html the code above will not work.