It’s been a while since my last writings. Hhhhmm … lets write about social network, Twitter (Images will be updated soon :P) Lately, I’ve been addicted using Twitter bec. of its simplicity. Let’s see the time frame from popular social network I’ve been following recently;
- 2004 : Time of Friendster, anybody in Indonesia get crazed and hunting for testimonials from others 😛
- 2008 : Year of Facebook, by that time FB still have that sleezy user interface and not booming yet in Indonesia.
- 10.2009 : First time I joined Twitter.
The idea(s) to elaborate about Twitter has been stuck in my mind for months. I want to decrypt it components into logics and tables. Lets use reverse engineering, shall we 😉 Actually we can firmly slice Twitter from its web services by translating its functions 1-by-1. Just go to this link http://dev.twitter.com/doc/recent. You can read and translate its logic into your own but I like it when I can translate it by my own thoughts. In here, I’ll only slice pieces of it relates to following & followers + public timeline + home timeline + DM.
Before I continue with this writing, I should remind u all that this is just my personal opinions. I don’t want to start any flame wars here. Any comments should be addresses in appropriate manners. I’m open for any suggestions, comments, and critics.
1. Lets begin with the ‘following & followers’.
How Twitter determine friendship is different than other Social Network, such as Facebook. In Facebook/Friendster when we already confirmed a person as a friend, automatically we will also added as their friend. Its a two-way trust, under the hood aka Database, query’s working to determine this friendship will have to do some ‘heavy duty’ to display our friends list.
Assuming this is our table structure
CREATE TABLE `twitter`.`friendship` (
`id` INT NOT NULL ,
`user_id` INT NOT NULL ,
`follow_id` INT NOT NULL ,
`friendtime` DATETIME NOT NULL ,
`block` TINYINT( 1 ) NOT NULL ,
`spam` TINYINT( 1 ) NOT NULL ,
`pending` TINYINT( 1 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
to retrieve those who following me, just call this query
SELECT p.username, p.private, p.verified, f.follow_id fid
FROM friendship f
INNER JOIN profile p ON (p.id = f.follow_id)
WHERE f.user_id = ourID AND f.pending = 0;
and for people I followed
SELECT p.username, p.private, p.verified, f.user_id fid
FROM friendship f
INNER JOIN profile p ON (p.id = f.follow_id)
WHERE f.follow_id = ourID AND f.pending = 0;
p.username = display username
p.private = display lock icon
p.verified = display verified icon
Simple SELECT queries right 🙂 But for FB/FS their query will be much complicated because it will need to make a JOIN function with the same table that already been aliased to another name.
SELECT f1.follow_id
FROM friendship f1 LEFT JOIN friendship f2 ON (f2.user_id = f1.follow_id)
WHERE f1.user_id = ourID AND f1.pending = 0;
Why using aliases for the same table and join it? Because this query will be useful to check whether we already being inserted on as friend on column follow_id or user_id
2. Public Timeline
Another assumption of our table
CREATE TABLE `twitter`.`status` (
`id` INT( 11 ) NOT NULL ,
`user_id` INT( 11 ) NOT NULL ,
`to_id` INT( 11 ) NOT NULL ,
`reply_id` INT( 11 ) NOT NULL ,
`via_id` INT( 11 ) NOT NULL ,
`comment` VARCHAR( 140 ) NOT NULL ,
`inserttime` DATETIME NOT NULL ,
`private` TINYINT( 1 ) NOT NULL,
`DM` TINYINT( 1 ) NOT NULL,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
To display Public Timeline, we will need to have a join with table ‘friendship’ + ‘via’
SELECT p.username, p.private, f.user_id, s.comment, s.inserttime, s.reply_id, v.name
FROM status s
INNER JOIN friendship f ON (f.follow_id = s.user_id)
INNER JOIN profile p ON (p.id = f.user_id)
INNER JOIN via v ON (v.id = s.via_id)
WHERE f.user_id = ourID AND s.DM = 0;
It will display something like this
@aryonp (lock/no lock icon) blablabla … in reply to someone via something
Details:
@aryonp = p.username
(lock/no lock icon) = p.private
blablabla … = s.comment
in reply to someone = s.reply_id
via something = v.name
3. Home Timeline
Having the same table with no. 2, this is the query that we use to display our own Timeline
SELECT p.username, p.private, f.user_id, s.comment, s.inserttime, s.reply_id, v.name
FROM status s
INNER JOIN profile p ON (p.id = f.user_id)
INNER JOIN via v ON (v.id = s.via_id)
WHERE f.user_id = ourID AND s.DM = 0;
4. Direct Messages/DM
I have notice that using the same table ‘status’ we can create also DM function, just change its query into this to display all messages relate. DM can only be sent if you follow your friend and he/she following you back.
Inbox:
SELECT p.username, p.private, f.user_id, s.comment, s.inserttime, s.reply_id
FROM status s
INNER JOIN friendship f ON (f.follow_id = s.user_id)
INNER JOIN profile p ON (p.id = f.user_id)
WHERE f.to_id = ourID AND s.DM = 1;
Outbox:
SELECT p.username, p.private, f.user_id, s.comment, s.inserttime, s.reply_id
FROM status s
INNER JOIN friendship f ON (f.follow_id = s.user_id)
INNER JOIN profile p ON (p.id = f.user_id)
WHERE f.user_id = ourID AND s.DM = 1;
Why there’s still s.reply_id? Because in Twitter newest interface we can see all messages relate can be chained into one conversation 🙂 Well, that’s all from me. Any comments or else can be address to below comment box. I hope this can be an inspiration for u all to see whats going on behind the Twitter curtain 😉
Twitter – Under the hood by M. Aryo N. Pratama is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.
Based on a work at www.halilintar.org.
Permissions beyond the scope of this license may be available at http://www.halilintar.org/.