Twitter postings mit der Datenbank (SQL, PLSQL, Java)

Twitter postings by the database

Seit einiger Zeit verwenden meine Teamkollegen und ich den Twitter-Account @oraclebudb, um interessante Neuerungen, die keine langen Texte erfordern, bekanntzumachen. Wenn Ihr also stets über Neuigkeiten zur Oracle-Datenbank wie neue Releases, Patchsets, deutschsprachige Veranstaltungen oder ähnliches informiert sein wollt, einfach an diesen Account dranhängen ("Folgen").

Nun ist man ab und zu unterwegs und kann einen Tweet nicht immer von seinem iPhone oder PC absetzen - man möchte einen Tweet "planen" und dann zu einem festgelegten Zeitpunkt, bspw. "Montag 10:00 Uhr" automatisch absetzen. Sowas kann übrigens auch sehr interessant sein, wenn man Twitter für regelmäßige Statusmeldungen über etwas nutzen möchte - ich könnte mir sehr gut vorstellen, zum Beispiel die Pegelstände von Flüssen (vor allem bei Hochwasser) regelmäßig über Twitter zu posten. Auch hier braucht es einen Automatismus, von Hand geht nix mehr.

Der Nutzen ist klar - die Frage ist also: Wie setzt man es um? Und ich möchte es natürlich mit der Oracle-Datenbank, also mit SQL und PL/SQL machen. Eine erste Google-Suche führte mich denn auch zu zahlreichen Blogs und Webseiten mit PL/SQL-Codefragmenten, die alle eines gemein hatten: Ich brachte es nicht zum Laufen (wobei das wohl eher an mir lag) Allein das von Twitter verwendete OAuth-Verfahren, welches von einer Anwendung genutzt werden muss, implementiert man nicht mal eben in 5 Minuten in PL/SQL.

Im Java-Bereich gibt es allerdings eine sehr populäre Bibliothek für Twitter: twitter4j. Und da man in einer Oracle-Datenbank auch Java laufen lassen kann, habe ich mich entschlossen, das Rad nicht neu in PL/SQL zu erfinden, sondern das zu nutzen, was da ist ...

Im folgenden findet Ihr also eine Anleitung, wie man eine PL/SQL-Prozedur in die Datenbank legt, die unter einem bestimmten Account twittert. Mit dieser Variante kann nur der Account-Eigentümer per PL/SQL twittern - die Autorisierung durch andere Nutzer ist prinzipiell möglich, hier aber nicht beschrieben.

Schritt 1: Applikation auf "dev.twitter.com" registrieren:

Loggt euch mit eurem Twitter-Account auf dev.twitter.com ein. Navigiert im Menü oben rechts (dort, wo Ihr euren Accountnamen seht) zum Bereich My Applications und klickt dann die Schaltfläche Create a new Application. Füllt dann den Dialog aus. Ihr braucht einen Namen, eine Beschreibung und eine Webseite (die muss es für den Anfang aber nicht wirklich geben). Die Callback-URL wird für heute nicht benötigt (Abbildung 1). Dann müsst Ihr mit dem CAPTCHA fertig werden und eure Anwendung ist erstellt.

Abbildung 1: Anwendung erstellen

Wenn die Anwendung erstellt wurde, seht Ihr bereits einige wichtige URLs und das Schlüsselpaar für die Anwendung selbst (Consumer Key und Consumer Secret).

Abbildung 2: Die Anwendung wurde erstellt

Nun navigiert Ihr zu den Settings eurer neuen Anwendung und stellt die Berechtigungen auf Read and Write. Das ist wichtig, denn das Absenden eines Tweets ist ein "Write.

Abbildung 3: Vergeben der Berechtigung "Read and Write"

Nun zurück zur ersten Reiterkarte Details. Unten seht Ihr eine Schaltfläche Create My Access Token - damit generiert Ihr die nötigen Accountschlüssel für den eigenen Account (Access Token und Access Token Secret). Diese, den Consumer Key und das Consumer Secret bitte merken - das brauchen wir in der Stored Procedure.

Abbildung 4: Access Token und Access Token Secret

Nun seid Ihr mit dem Registrieren der Anwendung fertig. Jetzt geht es zur Oracle-Datenbank.

Schritt 2: Twitter4j in die Datenbank laden.

Ladet twitter4j von der Webseite twitter4j.org herunter. Twitter4j ist Open Source unter der Apache Lizenz - kann also für alle Anwendungsfälle, sei es kommerziell oder nicht, verwendet werden. Das ZIP-Archiv muss in ein Verzeichnis eurer Wahl ausgepackt werden - am besten legt Ihr es gleich auf den Datenbankserver.

Abbildung 5: ZIP Archiv "twitter4j"

Die Java-Bibliotheken befinden sich im Unterverzeichnis lib. Für einfache Status-Updates braucht Ihr nur das Archiv twitter4j-core-2.2.4.jar. Das wird nun wie folgt in die Datenbank geladen.

$ loadjava -user {dbuser}/{dbpassword} -o -r -v twitter4j-core-2.2.4.jar

Ihr sehr nun einige Meldungen und zum Schluß kommen einige Fehlermeldungen, die Ihr aber ignorieren könnt.

skipping : resource META-INF/maven/org.twitter4j/twitter4j-core/pom.xml
skipping : resource META-INF/maven/org.twitter4j/twitter4j-core/pom.properties
The following operations failed
    class twitter4j/internal/logging/SLF4JLogger: resolution
    class twitter4j/internal/logging/SLF4JLoggerFactory: resolution
exiting  : Failures occurred during processing

Schritt 3: Java-Klasse für ein Status-Update erstellen

Es wäre sehr aufwändig, die Methoden der Twitter4j-Bibliothek direkt auf PL/SQL abzubilden. Dafür sind es zu viele und vielfach werden Java-Objekte übergeben, deren Mapping auf Datenbanktypen nicht einfach ist. Der beste Weg ist es, die Operationen, die man haben möchte, in Java zu implementieren, dabei einfache Funktionssignaturen zu verwenden und diese "Top-Level"-Methoden dann nach PL/SQL abzubilden. Zuerst also die Java-Klasse, die auf Basis der Twitter4j-Beispiele gebaut wurde. Achtet darauf, in der Methode setOAuthAccessToken die Schlüssel von dev.twitter.com einzutragen. Weiter vorne in den Calls zu System.setProperty müsst Ihr euren Proxy-Server eintragen, wenn die Datenbank hinter einer Firewall steht. Wenn nicht, entfernt die Zeilen einfach.

create or replace and compile java source named TwitterStatusUpdate as
import twitter4j.*;
import twitter4j.conf.*;
import twitter4j.auth.*;

import java.io.*;
import java.util.*;
import oracle.jdbc.*;
import oracle.sql.*;
import java.sql.*;

public class UpdateStatus {
  static Twitter twitter = null;

  static  {
    System.setProperty("http.proxyHost", "{Proxy-Server, falls nötig}");
    System.setProperty("http.proxyPort", "{Proxy-Server-Port, falls nötig}");
  
    ConfigurationBuilder confbuilder  = new ConfigurationBuilder(); 
    confbuilder.setOAuthAccessToken("{Access-Token aus dev.twitter.com}") 
    .setOAuthAccessTokenSecret("{Access-Token-Secret aus dev.twitter.com}") 
    .setOAuthConsumerKey("{Consumer-Key aus dev.twitter.com}") 
    .setOAuthConsumerSecret("{Consumer-Secret aus dev.twitter.com}"); 
    twitter = new TwitterFactory(confbuilder.build()).getInstance(); 
  }

  public static String updateStatus(String newStatus) throws Exception {
    Status status = twitter.updateStatus(newStatus);
    return status.getText();
  }
}
/
sho err

Schritt 4: PL/SQL Wrapper für die Java-Methode erstellen

Für diese statische Java-Metjode updateStatus, die einen String entgegennimmt und zurückgibt, lässt sich nun sehr einfach ein PL/SQL-Mapping erstellen.

create or replace function set_twitter_status(p_new_status in varchar2) return varchar2
is language java name 'UpdateStatus.updateStatus(java.lang.String) return java.lang.String';
/
sho err

Schritt 5: Privilegienvergabe

Ein Twitter-Posting wird über das Netzwerk abgesetzt - das darf ein "normaler" Datenbankuser mit Java erstmal nicht. Damit die obige Java-Klasse auch tatsächlich funktioniert, muss der Datenbankuser Privilegien zum Netzwerkzugriff und zum Einstellen des Proxy-Servers (falls nötig) erhalten. Das erledigt man wie folgt - das Skript muss als DBA gestartet werden.

begin
  dbms_java.grant_permission( 
    '{DB-SCHEMA}', 'SYS:java.util.PropertyPermission', 'http.proxyHost', 'read,write' 
  );
  dbms_java.grant_permission( 
    '{DB-SCHEMA}', 'SYS:java.util.PropertyPermission', 'http.proxyPort', 'read,write' 
  );
  dbms_java.grant_permission( 
    '{DB-SCHEMA}', 'SYS:java.net.SocketPermission', 'api.twitter.com:80', 'connect, resolve' 
  );
end;
/

Abschluß

Dann ist es fertig. Nun könnt Ihr testen ...

SQL> select set_twitter_status('another test at friday') from dual;

SET_TWITTER_STATUS('ANOTHERTESTATFRIDAY')
------------------------------------------------------------------------
another test at friday

1 Zeile wurde ausgewählt.

Wenn Ihr euch die Twitter-Seite eures Accounts anseht, solltet Ihr das Posting nun sehen ... und von hier aus ist eine Automatisierung kein Thema mehr - ob man nun per Scheduler-Job regelmäßig etwas twittert, eine APEX-Anwendung zum Planen der Tweets erstellt oder andere Dinge tut - mit den Mitteln der Datenbank kann dies nun beliebig genutzt werden. Eine einfaches Beispiel ist demnach eine APEX-Anwendung mit einem Formular auf eine Tabelle. Im Formular kann man Tweets und eine Zeitpunkt eintragen, zu dem der Tweet gepostet werden soll. Ein DBMS_SCHEDULER-Job läuft dann regelmäßig los und postet automatisch alle "fälligen" Tweets. Und natürlich kann man mit twitter4j noch mehr tun als nur Status-Updates abzusetzen, aber wie man bspw. die Home-Timeline abruft, dazu mache ich am besten ein eigenes Blog Posting. Viel Spaß auf jeden Fall beim Ausprobieren ...

My teammates and me are using the twitter account @oraclebudb, to post interesting news about the Oracle database (Events in german language, Releases and more) for a german audience. So if you are a german reader and interested in regular updates about the Oracle database, follow @oraclebudb. English readers might follow @oracledatabase.

But sometimes one wants to schedule a tweet - perhaps for tomorrow at 10:00am. If one wants to use Twitter for regular updates about something (think about floodwater levels for a river) some kind of automation or scheduling is needed - manual tweets are not possible. So the question is: How to implement this? I'd like to do it with APEX, the Oracle database and PL/SQL. So I asked Mr. Google how to do this and I got some PL/SQL fragments which I could not manage to work on my system. But I saw that the "OAuth" mechanism which Twitter uses for authenticating is something which I cannot implement in 5 minutes in PL/SQL.

Java developers have the very popular twitter4j. library - and since the Oracle database can run Java Stored Procures I decided not to reinvent the wheel and to use things already there (XE-Users: Sorry). The following posting describes how to do automatic tweets from the Oracle database using the Twitter4j library and some Java and PL/SQL coding with the "own" twitter account; the library allows also authorization for other Twitter accounts - but this is not described here.

Step 1: Register your application on "dev.twitter.com":

Log into dev.twitter.com with your Twitter account. In the menu in the upper right corner (where you can see your Twitter username) navigate to My Applications and click on Create a new Application. Then fill in the form. You need a name, a description and a Website URL (which don't need to work for now). The callback URL is not needed - after that manage the CAPTCA and your application is registered.

Register your application

You now see the key pair (Consumer Key and Consumer Secret) for your application. We will need that later on.

The application has been registered.

Now navigate to the Settings tab and adjust the Privileges of your Application. It will need Read and Write privileges, since a tweet is a "Write".

Grant "Read and Write" privileges to the application

Then go back to the Details tab and navigate down to the button named Create My Access Token - this will generate the keys for your account. (Access Token and Access Token Secret). Our application has to create a signature using the two application keys as well as the two account keys - but the Twitter4j library will do the necessary stuff.

Generated "Access Token" and "Access Token Secret"

Now you're done with registering the application. We can now start the implementation in the Oracle database.

Step 2: Load the "twitter4j" library into the database.

Download twitter4j from twitter4j.org. Twitter4j is Open Source software and it uses the Apache License. So you are free to use for either commercial or non-commercial usage without a fee. Unpack the ZIP archive into a folder of your choice - it's best to place it directly on the database server.

Contents of the downloaded ZIP archive

The java libraries are located in the subfolder lib. For simple status updates (tweets) the archive twitter4j-core-2.2.4.jar is sufficient. Load it into the Oracle database as follows.

$ loadjava -user {dbuser}/{dbpassword} -o -r -v twitter4j-core-2.2.4.jar

You will see some error messages at the end of the loading process, which you can ignore.

skipping : resource META-INF/maven/org.twitter4j/twitter4j-core/pom.xml
skipping : resource META-INF/maven/org.twitter4j/twitter4j-core/pom.properties
The following operations failed
    class twitter4j/internal/logging/SLF4JLogger: resolution
    class twitter4j/internal/logging/SLF4JLoggerFactory: resolution
exiting  : Failures occurred during processing

Step 3: Create a java method doing a status update

It would be a lot of work to map all the API methods to PL/SQL procedures and functions. There are many methods and most of the have java objects in their signature. It's better to implement the needed operations as Java methods with simple signatures and finally map those java methods to PL/SQL. So the following code creates a simple java method "updateStatus" which can be mapped to PL/SQL very easily. This java code has been created based on the twitter4j examples, which are also part of the downloaded archive. Note the placeholders for the key pairs from dev.twitter.com in the call of setOAuthAccessToken which need to be replaced. The calls to System.setProperty in the previous lines also contain placeholders for the proxy server and port, which also need to be adjusted to your environment.

create or replace and compile java source named TwitterStatusUpdate as
import twitter4j.*;
import twitter4j.conf.*;
import twitter4j.auth.*;

import java.io.*;
import java.util.*;
import oracle.jdbc.*;
import oracle.sql.*;
import java.sql.*;

public class UpdateStatus {
  static Twitter twitter = null;

  static  {
    System.setProperty("http.proxyHost", "{Proxy server, if required}");
    System.setProperty("http.proxyPort", "{Proxy server port, if required}");
  
    ConfigurationBuilder confbuilder  = new ConfigurationBuilder(); 
    confbuilder.setOAuthAccessToken("{Access token from dev.twitter.com}") 
    .setOAuthAccessTokenSecret("{Access token secret from dev.twitter.com}") 
    .setOAuthConsumerKey("{Consumer key from dev.twitter.com}") 
    .setOAuthConsumerSecret("{Consumer secret from dev.twitter.com}"); 
    twitter = new TwitterFactory(confbuilder.build()).getInstance(); 
  }

  public static String updateStatus(String newStatus) throws Exception {
    Status status = twitter.updateStatus(newStatus);
    return status.getText();
  }
}
/
sho err

Step 4: Create the PL/SQL wrapper

The created Java method "updateStatus" just takes and returns a java string. The mapping to SQL and PL/SQL is, as already stated, very easy:

create or replace function set_twitter_status(p_new_status in varchar2) return varchar2
is language java name 'UpdateStatus.updateStatus(java.lang.String) return java.lang.String';
/
sho err

Step 5: Grant privileges

Doing a tweet is a network request. A "normal" database user does not have privileges to execute network operations in Java. To get the above java class actually working the database schema needs java privileges to set the proxy server (if needed) and to do the network operation. The following SQL script, which has to be executed with DBA privileges, does the job.

begin
  dbms_java.grant_permission( 
    '{DB-SCHEMA}', 'SYS:java.util.PropertyPermission', 'http.proxyHost', 'read,write' 
  );
  dbms_java.grant_permission( 
    '{DB-SCHEMA}', 'SYS:java.util.PropertyPermission', 'http.proxyPort', 'read,write' 
  );
  dbms_java.grant_permission( 
    '{DB-SCHEMA}', 'SYS:java.net.SocketPermission', 'api.twitter.com:80', 'connect, resolve' 
  );
end;
/

Final testing

And then you are done. Try it ...

SQL> select set_twitter_status('another test at friday') from dual;

SET_TWITTER_STATUS('ANOTHERTESTATFRIDAY')
------------------------------------------------------------------------
another test at friday

1 row selected.

Point your browser to your twitter page - you should see that posing - done by SQL in the Oracle database. From this point we can use this procedure as any other one. Creating a DBMS_SCHEDULER job is as easy as creating an APEX application from which tweets are being posted. A simple example is an APEX application with a form on a table where the user can enter a tweet and a timestamp at which to post the tweet. A DBMS_SCHEDULER job runs in regular intervals an posts all tweets due. The twitter4j API allows much more operations ... we could query the Home Timeline and pass it back to the SQL - but this is another story ... for another blog posting ...

Ausführungspläne mit DBMS_XPLAN

Heute wollte ich eigentlich ein Blog-Posting über die verschiedenen Möglichkeiten von DBMS_XPLAN bringen ... das kann ja wesentlich mehr als DBMS_XPLAN.DISPLAY(). Es ist allein schon lohnenswert, sich die verschiedenen Varianten des Parameters FORMAT anzusehen. Allerdings haben meine Kollegen, die die DBA Community-Seite betreiben, hierzu schon einiges geschrieben ... und man soll das Rad nicht neu erfinden. Daher verweise ich heute einfach nur auf den entsprechenden Community-Tipp.

Viel Spaß beim Lesen!

My intention for today was a Blog Posting about the DBMS_XPLAN package - this package can do much more than just DBMS_XPLAN.DISPLAY(). For instance - looking at the variants of the argument is worthful for its own. But the collegues maintaining the german DBA Community pages have done a lot of work on this, and I don't want to reinvent the wheel. German readers might just continue reading the Community How To on "Execution Plans"; english readers might try the "Google-Translate" version.

Have fun!