How I Built a Twitter App for the APEX & ORDS Community

How I Built a Twitter App for the APEX & ORDS Community

Jon Dixon's photo
Jon Dixon
Aug 30, 2022·

10 min read

Table of contents

Introduction

Note (18-MAR-2023): When Twitter announced they were going to charge for using their APIs, I closed down the Twitter App that I mention in this post. Aside from this, the content remains relevant.

This post will describe the Web App I built to track Twitter activity for Oracle Application Express (APEX) and Oracle REST Data Services (ORDS). The goal of the App is to feature developers that contribute to these communities via Twitter and also to understand better how the community uses and responds to Twitter content.

🎁 The bonus for me is that this project allowed me to use the development tools I love the most to help the developer community.

🔔 You will learn how to set up a Twitter Developer account, call several Twitter APIs via Postman, and see the inner workings of the App itself. I have also made the code available from this GitHub Repositiory.

Demonstration

Let's start with a brief screen recording of the App.

Oracle_APEX_Twitter_App_ScreenRecording.gif

Architecture

This section describes the software the App was developed with and the hardware on which it is running.

Software

The App was built using Oracle Application Express (APEX) version 22.1 running on Oracle Autonomous Database and Oracle REST Data Services Version 22.2.

Oracle APEX is a low-code development platform that enables you to build scalable, secure enterprise apps with world-class features that can be deployed anywhere. Using APEX, developers can quickly develop and deploy compelling apps that solve real problems and provide immediate value.

Hardware

The App is running on the Oracle Cloud Infrastructure (OCI) Free Tier Autonomous Database Service at the cost of 💲0 per month. Read my post for more details on this architecture.

Design

In this section, I will focus on some of the critical design elements for the App.

Mobile First

From the beginning, I wanted the App to run just as well on mobile as on desktop. APEX's out-of-the-box Progressive Web App (PWA) and Responsive UI capabilities made this possible without any additional coding on my part.

Performance

Even though the Tweet and Tweeter data is being fetched from Twitter over REST, I wanted to ensure the App was fast. I did this by caching much of the Tweet-related data (Tweets, Re-Tweets, Quoted Tweets) in tables on the Oracle database in which APEX runs.

Local table caching was made possible using APEX Automations, which allowed me to quickly create an hourly schedule to capture the latest Tweet and Tweeter information.

Caching data locally makes searches and charting super fast. Detailed information about a user's profile is fetched over REST in real-time if the Tweeter profile has not already been captured.

Data Model

The data model is simple and consists of four tables

  • CNDEMO_TWT_CAPTURE

    • Stores search criteria used by the Tweet Capture Process

  • CNDEMO_TWT_TWEETS

    • Stores Tweets and Re-Tweets captured by the Tweet Capture Process

  • CNDEMO_TWT_RELS

    • Stores relationships between tweets and the Search Criteria. This handles the scenario where a Tweet could belong to more than one set of search/capture criteria

  • CNDEMO_TWT_USERS

    • Stores user profile information captured by the User Capture Process

Flexibility

The table CNDEMO_TWT_CAPTURE stores the hashtags and @ mentions, I want to capture. The hourly capture process uses this table to determine which Tweets to search. Having this flexibility allows me to easily add additional hashtags in the future.

Twitter APIs

This section focuses on the Twitter APIs that I used to capture the data used by the App.

Getting Started

Signing up for a developer account was easy. You can get started by following this link.

Optionally signup for 'Elevated' access. Amongst other things, 'Elevated' access allows you to query 2 million Tweets/month instead of the Standard 500k. Use this link to compare the different plan levels. Aside from having to complete a brief questionnaire, upgrading to 'Elevated' access was straightforward. 'Elevated' access was granted almost immediately after submitting the questionnaire. I am assuming this was an automated approval.

You will also find these links useful when getting started with the Twitter APIs:

Authentication

After signing up for a developer account, you must create an App within a Project. In the below screenshot, you can see I have one App called 'Cloud Nueva APEX Demo' in the project 'Cloud Nueva'.

Screenshot_of_Twitter_Developer_Portal.png

Once you create an App, Twitter will present you with 'Keys & Tokens'; keep these in a safe place. We will use the 'API Key' and 'API Secret Key' shortly. The screenshot below shows the end of the Wizard process to create a new App (the process is straightforward).

Screenshot_of_Twitter_Developer_App_Keys.png

Don't worry; I have already deleted all of the API Keys and Bearer Tokens you may see in this post. 😉

We can then use these API Key and the API Secret Key keys as part of the 'OAuth2 Client Credentials' flow in Postman to get an access_token as follows:

Postman_Authentication_Step_1.png

Postman_Authentication_Step_2.png

Note: Unlike many OAuth services that expire the access_token (also known as the Bearer Token) after a period of time, Twitter does not expire this access_token. If you call https://api.twitter.com/oauth2/token multiple times, you will get back the same access_token. This is in fact the same 'Bearer Token' generated when we created the 'App' in the Twitter Developer Account in the previous step.

To revoke the Bearer Token / access_token, you can click the 'Revoke' button from the Twitter Developer Portal as seen in the screenshot below.

Screenshot_of_Twitter_Developer_Revoke_Bearer.png

🔗 You can read more about using the 'App only authentication and OAuth 2.0 Bearer Token' authentication method here

Tweet Search Recent

The main API is the Tweet Search Recent API. This API allows you to search Tweets created in the past seven days.

The seven-day search limit is one notable limitation of even the 'Elevated' level Twitter Developer Account. As you will see later, I have worked around this by implementing a nightly process to loop through all tweets captured between seven and twenty-one days ago and call the Tweets Lookup API to update them. Looking back at older tweets was another reason I chose to store Tweets in the Oracle database.

The following screenshot shows how to fetch Tweets with a mention of @oracleAPEX or the hashtag #orclAPEX.

Postman_Tweet_Search_Step_1.png

Postman_Tweet_Search_Step_2.png

  • The Bearer token is the access_token obtained in the authentication step.

Pagination

Pagination is handled via the max_results and the next_token query string parameters.

  • Passing max_results of 100 (the maximum value) will fetch up to 100 Tweets at a time (assuming your search returns 100 or more Tweets.

  • The meta section at the end of the API response includes the number of Tweets returned by the search. If there are more than 100, then the API will also include a field called next_token.

"meta": {
        "newest_id": "1562224116723490817",
        "oldest_id": "1561742996932108290",
        "result_count": 100,
        "next_token": "b26v89c19zqg8o3fpz8kbizzndmockeoekrzulr87t3zx"
    }

If you call the API again and pass in a parameter called next_token with the value you just received, the API will fetch the next 100 Tweets. This way, you can fetch as many search results as you like (within your account limits).

Postman_Tweet_Search_Step_3.png

Users

The other API, the APEX App, uses extensively is the Users Lookup API. The App uses this API to fetch details for users that have Tweeted about APEX or ORDS. It allows you to pass a list of up to 100 usernames and returns details for these usernames.

The screenshots below show the Postman setup to fetch details for these two users jondixonus,cloud_nueva.

Postman_Users_Step_1.png

Postman_Users_Step_2.png

APEX Application

Now that we understand the Twitter APIs, we can look at some of the more significant code behind the App. I don't plan to explain every line of code, so I encourage you to download the code from my Github Repository now.

APEX Web Credentials

We first need to create some APEX Web Credentials to store the Twitter OAuth2 Client ID and Client Secret. APEX Web Credentials make securely storing credentials like these super easy.

APEX_Web_Credentials.png

🔗 See my post Secure your Secrets with APEX Web Credentials for more details on APEX Web Credentials.

APEX Automations

APEX Automations allow you to schedule code to run (locally or on a remote database) based on an event or schedule.

🔗 See my post Scheduling Made Easy with APEX Automations for more details on APEX Automations.

The Twitter App uses two Automations:

  1. Capture Tweets - Hourly

  2. Refresh Tweets and Users - Nightly

Capture Tweets - Hourly

  • This Automation calls the procedure cndemo_twtr_utl_pk.capture_tweets every hour. The procedure uses the APEX PL/SQL API apex_web_service.make_rest_request to call the Twitter Recent Search API, fetching all APEX-related Tweets for the past seven days in batches of 100.

    • Any Tweets not already in the database are added, and any existing Tweets are updated.

    • The reason for the update is to update Tweets that have received additional likes etc., since their original capture.

Refresh Tweets and Users - Nightly

This Automation runs nightly and calls two procedures, one after the other.

  • refresh_tweets

    • This procedure calls the Tweets lookup endpoint to get updated data for Tweets in my database between 7 and 21 days old.

    • The 'Tweets lookup' API allows you to submit up to 100 Tweet ids to fetch details for. It will fetch details for these Tweets no matter how old they are.

    • Fetching details of specific older tweets helps me get around the problem that the Search Recent API only beings back Tweets for the past seven days.

    • I assume that any Tweets older than 21 days are not worth updating.

  • refresh_users

    • This procedure uses the Users lookup endpoint to get updated follower counts etc., for all users that have Tweeted about APEX.

    • The 'Users lookup' API allows you to submit up to 100 User ids to fetch details for. The procedure loops through all users in the Oracle database, calling the 'Users lookup' API in batches of 100.

Batching Users and Tweets into fewer REST API calls reduce network round trips and significantly improves performance.

PL/SQL Code

The bulk of the code in the package cndemo_twtr_utl_pk is used in the Tweet and Tweeter capture Automations. I have added many comments to the code, so hopefully, it is self-explanatory.

APEX Pages

Home Page - Page 1

This page uses the APEX Cards Region to summarize information for each configured search. I am utilizing APEX Template Directives to present a summarized information table in the center of each card.

APEX_Twitter_Home_Page.png

🔗 See my post Client-Side Formatting with APEX Template Directives for more on Template Directives.

Top 10s - Page 10

This page highlights the most active Tweets and Tweeters. I implemented a scoring system based on Original Tweets Vs. ReTweets and Likes to illustrate the most effective Tweets and Tweeters. The page allows you to easily navigate to specific Tweets so that you can like or re-tweet them. I used APEX Classic Reports to build the four regions on this page.

APEX_Twitter_Top10s_1.png

You can click on a username to view their Twitter profile information.

APEX_Twitter_Top10s_2.png

Charts - Page 30

This page shows three charts illustrating week-by-week Twitter activity and the most popular days of the week to tweet. When you visit this page, it calls the procedure page30_load, which summarizes Tweet data into an APEX Collection.

APEX Collections are a great way to cache session specific data. Summarizing the data in this way improves the page's overall performance (each chart fetches 10s of records instead of thousands) while still giving the user real-time data.

APEX_Twitter_Charts.png

Tweet Search - Page 20

This page allows you to search across all Tweets captured by the App. This search capability provides users a great way to search for Tweets focused entirely on APEX. This page utilizes two important APEX Features; Smart Filters and Order By Items.

Smart filters are particularly effective as they combine 'Google Style' row search criteria entry with the ability to present lists or ranges from which a user can select.

In the below screenshot I am selecting tweets for a specific tweeter:

APEX_Twitter_Tweeter_Search.png

In the screenshot below, I searched for Tweets that contained ckeditor1 and image and applied a filter to only show 'Original' Tweets.

APEX_Twitter_Tweeter_Search_2.png

Conclusion

I had a lot of fun building this App. Side projects like these are a great way to flex features that may not get used in your day-to-day corporate development job. They can also benefit the development community. I encourage you to find a side project that gets you excited!

🔗 Read More

4articles4weeks#week3orclapexorclordsOracle