Opportunity: handling likes nicely

[SamHames]

Having worked with the new Twitter like endpoints the data model is a real pain, which I think is a real opportunity for tidy_tweet to do something nice. The Twitter endpoints give the following information:

• liking-users gives a reverse chronological list of user profiles liking the tweet
• liked-tweets gives a reverse chronological list of liked tweets made by a user

In both cases, there is no indicator in the Twitter JSON of which tweet (or user) was liked by which user, however twarc injects the requested URL with that information into the __twarc field, allowing us to recover the proper relation from the data by itself.

A suggested approach is to insert the associated tweet/user profiles into the relevant tables, and create a liked_tweet table as below (with an index on (user_id, tweet_id) too):

create table if not exists liked_tweet (
    tweet_id integer references tweet(tweet_id),
    user_id integer references user(user_id),
    primary key (tweet_id, user_id)
);