Tweeting StackExchange Questions with Spring Social

1. Introduction

This is the third and final article about a small side-project – a bot that automatically tweets Questions from various Q&A StackExchange sites on specialized accounts (full list at the end of the article).

The first article discussed building a simple client for the StackExchange REST API. In the second article we set up the interaction with Twitter using Spring Social.

This article will describe the final part of the implementation – the part responsible with the interaction between the Stackexchange client and the TwitterTemplate.

Note that this tutorial uses the Spring Social API, which is now deprecated. As a result, the supporting code shown here is no longer maintained.

2. The Tweet Stackexchange Service

The interaction between the Stackexchange Client – exposing the raw Questions, and the TwitterTemplate – fully set up and able to tweet – is a very simple service – the TweetStackexchangeService. The API published by this is:

public void tweetTopQuestionBySite(String site, String twitterAccount){ ... }
public void tweetTopQuestionBySiteAndTag(String site, String twitterAccount, String tag){ ... }

The functionality is simple – these APIs will keep reading Questions from the Stackexchange REST API (through the client), until one is found that has not been tweeted before on that particular account.

When that question is found, it is tweeted via the TwitterTemplate corresponding to that account, and a very simple Question entity is saved locally. This entity is only storing the id of the Question and the Twitter Account that it has been tweeted on.

For example the following question:

Binding a list in @RequestParam

Has been tweeted on the SpringTip account.

The Question entity simply contains:

• the id of the Question – 4596351 in this case
• the Twitter Account on which the question has been tweeted – SpringAtSO
• the Stackexcange Site from which the question originates – Stackoverflow

We need to keep track of this information so that we know which questions have already been tweeted and which haven’t.

3. The Scheduler

The scheduler makes use of the Spring’s scheduled task capabilities – these are enabled via the Java configuration:

@Configuration
@EnableScheduling
public class ContextConfig {
   //
}

The actual scheduler is relativelly simple:

@Component
@Profile(SpringProfileUtil.DEPLOYED)
public class TweetStackexchangeScheduler {

   @Autowired
   private TweetStackexchangeService service;

   // API

   @Scheduled(cron = "0 0 1,5 * * *")
   public void tweetStackExchangeTopQuestion() throws JsonProcessingException, IOException {
      service.tweetTopQuestionBySiteAndTag("StackOverflow", Tag.clojure.name(), "BestClojure", 1);
      String randomSite = StackexchangeUtil.pickOne("SuperUser", "StackOverflow");
      service.tweetTopQuestionBySiteAndTag(randomSite, Tag.bash.name(), "BestBash", 1);
   }
}

There are two tweet operations configured above – one tweets from StackOverflow questions which are tagged with “clojure” on the Best Of Clojure twitter account.

The other operation tweets questions tagged with “bash” – and because these kind of questions actually appear on multiple sites from the Stackexchange network: StackOverflow, SuperUser and AskUbuntu, there is first a quick selection process to pick one of these sites, after which the question is tweeted.

Finally, the cron job is scheduled to run at 1 AM and 5 AM each day.

4. Setup

This being a pet project, it started out with a very simple database structure – it’s still simple now, but it was even more so. So one of the main goals was to be able to easily change the database structure – there are of course several tools for database migrations, but they’re all overkill for such a simple project.

So I decided to keep setup data in a simple text format – that is going to be updated semi-automatically.

Setup has two main steps:

• the ids of the questions tweeted on each twitter account are retrieved and stored in a text file
• the database schema is dropped and the application restarted – this will create the schema again and setup all data from the text file back into the new database

4.1. The Raw Setup Data

The process of retrieving the data the existing database is simple enough with JDBC; first we define a RowMapper:

class TweetRowMapper implements RowMapper<String> {
   private Map<String, List<Long>> accountToQuestions;

   public TweetRowMapper(Map<String, List<Long>> accountToQuestions) {
      super();
      this.accountToQuestions = accountToQuestions;
   }

   public String mapRow(ResultSet rs, int line) throws SQLException {
      String questionIdAsString = rs.getString("question_id");
      long questionId = Long.parseLong(questionIdAsString);
      String account = rs.getString("account");

      if (accountToQuestions.get(account) == null) {
         accountToQuestions.put(account, Lists.<Long> newArrayList());
      }
      accountToQuestions.get(account).add(questionId);
      return "";
   }
}

This will build a list of Questions for each Twitter account.

Next, we’re going to use this in a simple test:

@Test
public void whenQuestionsAreRetrievedFromTheDB_thenNoExceptions() {
   Map<String, List<Long>> accountToQuestionsMap = Maps.newHashMap();
   jdbcTemplate.query
      ("SELECT * FROM question_tweet;", new TweetRowMapper(accountToQuestionsMap));

   for (String accountName : accountToQuestionsMap.keySet()) {
      System.out.println
         (accountName + "=" + valuesAsCsv(accountToQuestionsMap.get(accountName)));
   }
}

After retrieving the Questions for an account, the test will simply list them out; for example:

SpringAtSO=3652090,1079114,5908466,...

4.2. Restoring the Setup Data

The lines of data generated by the previous step are stored in a setup.properties file which is made available to Spring:

@Configuration
@PropertySource({ "classpath:setup.properties" })
public class PersistenceJPAConfig {
   //
}

When the application starts up, the setup process is performed. This simple process uses a Spring ApplicationListener, listening on a ContextRefreshedEvent:

@Component
public class StackexchangeSetup implements ApplicationListener<ContextRefreshedEvent> {
    private boolean setupDone;

    public void onApplicationEvent(ContextRefreshedEvent event) {
        if (!setupDone) {
            recreateAllQuestionsOnAllTwitterAccounts();
            setupDone = true;
        }
    }
}

Finally, the questions are retrieved from the setup.properties file and recreated:

private void recreateAllQuestionsOnTwitterAccount(String twitterAccount) {
   String tweetedQuestions = env.getProperty(twitterAccount.name();
   String[] questionIds = tweetedQuestions.split(",");
   recreateQuestions(questionIds, twitterAccount);
}
void recreateQuestions(String[] questionIds, String twitterAccount) {
   List<String> stackSitesForTwitterAccount = twitterAccountToStackSites(twitterAccount);
   String site = stackSitesForTwitterAccount.get(0);
   for (String questionId : questionIds) {
      QuestionTweet questionTweet = new QuestionTweet(questionId, twitterAccount, site);
      questionTweetDao.save(questionTweet);
   }
}

This simple process allows easy updates of the DB structure – since the data is fully erased and fully re-created, there is no need to do any actual migration whatsoever.

5. Full List of Accounts

The full list of Twitter accounts is:

• SpringTip – Spring questions from StackOverflow
• JavaTopSO – Java questions from StackOverflow
• RESTDaily – REST questions from StackOverflow
• BestJPA – JPA questions from StackOverflow
• MavenFact – Maven questions from StackOverflow
• BestGit – Git questions from StackOverflow
• AskUbuntuBest – AskUbuntu best overal questions (all topics)
• ServerFaultBest – ServerFault best questions (all topics)
• BestBash – best Bash questions from StackOverflow, ServerFault and AskUbuntu
• ClojureFact – Clojure questions from StackOverflow
• ScalaFact – Scala questions from StackOverflow
• EclipseFacts – Eclipse questions from StackOverflow
• jQueryDaily – jQuery questions from StackOverflow
• BestAlgorithms – Algorithm questions from StackOverflow

2 tweets per day are created on each of these accounts, with the highest rated questions on their specific subject.

6. Conclusion

This third article finishes the series about integrating with StackOverflow and other StackExchange sites to retrieve questions through their REST API, and integrating with Twitter and Spring Social to tweet these questions. A potential direction worth exploring is doing the same with Google Plus – probably using Pages, not accounts.

14 Twitter accounts are up and running as a result of this project – focusing on various topics and producing low-volume and hopefully high-quality content.