Auto-respond to Twitter tweets and archive messages in DB2

README.md

Flow to automatically respond to tweets in Twitter. If the tag "archive" is used, then the incoming tweet is stored in the DB2-based sqldb service. A simple Web service is provided to retrieve the archived tweets from DB2.

To work, the table "twitarchive" needs to be created.

create table twitarchive(
id int generated always as identity,
tstamp timestamp,
tweet varchar(200),
username varchar(100)
);```

flow.json

[{"id":"327e9ef9.299322","type":"twitter in","twitter":"","tags":"@twitteruser","user":"false","name":"","topic":"tweets","x":315,"y":653,"z":"710b37a5.df17d8","wires":[["45ff93cc.4b141c"]]},{"id":"45ff93cc.4b141c","type":"function","name":"Process Request","func":"var autoanswer=\"no\";\nvar archive=\"no\";\nvar lang=\"und\";\n\nif (typeof msg.tweet.retweeted_status == 'undefined')\n{\n\tif (typeof msg.tweet.entities.hashtags != 'undefined')\n\t{\n\t\tmsg.tweet.entities.hashtags.forEach(function(hashtag) {\n\t\t    if (hashtag['text'] == \"autoanswer\")\n\t\t    {\n\t\t    \tautoanswer = \"yes\";\n\t\t    }\n\t    if (hashtag['text'] == \"archive\")\n\t\t    {\n\t\t    \tarchive = \"yes\";\n\t\t    }\n\t\t});\n\t}\n    \n    if (typeof msg.tweet.lang != 'undefined')\n    {\n        lang=msg.tweet.lang;\n    }\n\t\n\tvar msg = {\n\t\tuser: msg.tweet.user.screen_name,\n\t\tanswer: autoanswer,\n\t\tarchive: archive,\n\t\tlang: lang,\n\t\ttweet: msg.tweet.text\n\t};\n\t\n\tvar twitarchive = { payload : {\n\t    TSTAMP: 'TIMESTAMP',\n\t    USERNAME: msg.user,\n\t    TWEET: msg.tweet }\n\t};\n\t\n\tif (archive == \"yes\")\n\t{\n\t    return [msg, twitarchive];\n\t}\n\telse\n\t{\n\t\treturn [msg, null];\n\t}\n}\n","outputs":"2","noerr":0,"x":504.9444580078125,"y":653.1944580078125,"z":"710b37a5.df17d8","wires":[["ec4f4ff4.4689e8"],["d5c1cf59.008ef"]]},{"id":"c00c0622.c28b28","type":"twitter out","twitter":"","name":"Reply","x":957.25,"y":644.861083984375,"z":"710b37a5.df17d8","wires":[]},{"id":"ec4f4ff4.4689e8","type":"function","name":"Process API Response","func":"if (msg.answer == \"yes\")\n{\n\tvar messages = [\n\t\t\"Hello again\",\n\t\t\"sprichst du auch deutsch?\",\n\t\t\"I did it\",\n\t\t\"all save I guess\",\n\t\t\"stored in DB2\",\n\t\t\"may the archive hold\",\n\t\t\"not sure what you mean\",\n\t\t\"at least I am trying to make this interesting...\",\n\t\t\"one of us is ...\",\n\t\t\"yeah\"\n\t];\n\t\n\tvar hashtags = [\n\t\t\"#db2\",\n\t\t\"#dashdb\",\n\t\t\"#db2luw\",\n\t\t\"#bluemix\",\n\t\t\"#nodered\"\n\t];\n\t\n\tvar message = messages[Math.floor(Math.random() * messages.length)];\n\tvar hashtag = hashtags[Math.floor(Math.random() * hashtags.length)];\n\t\n\n\tmsg.payload = \"@\" + msg.user + \" \" + message + \" \" + hashtag +\" [lang:\"+msg.lang+\"]\";\n\t\n\tfollowup_msg = {\n\t\tpayload: \"@\" + msg.user + \" p.s. this was powered by #NodeRED on #Bluemix\"\n\t}\n\t\n\treturn [ msg, followup_msg ];\n}\nelse\n{\n\tmsg.payload = \"@\" + msg.user + \" Ok\" +\" [lang:\"+msg.lang+\"]\";\n\treturn [ msg, null ];\n}\n","outputs":"2","noerr":0,"x":737.6944580078125,"y":651.861083984375,"z":"710b37a5.df17d8","wires":[["c00c0622.c28b28"],["3543f624.518f72"]]},{"id":"3543f624.518f72","type":"delay","name":"Delay","pauseType":"delay","timeout":"7","timeoutUnits":"seconds","rate":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":930.583251953125,"y":694.4444580078125,"z":"710b37a5.df17d8","wires":[["30884133.476f06"]]},{"id":"30884133.476f06","type":"twitter out","twitter":"","name":"Followup response","x":1122.4443359375,"y":693.4444580078125,"z":"710b37a5.df17d8","wires":[]},{"id":"d5c1cf59.008ef","type":"sqldb out","service":"SQL Database","table":"SCHEMA.TABLENAME","name":"tweet store","x":691,"y":709,"z":"710b37a5.df17d8","wires":[]},{"id":"1fbb6497.6cfb03","type":"http in","name":"messages in","url":"/messages","method":"get","swaggerDoc":"","x":311,"y":487,"z":"710b37a5.df17d8","wires":[["d224a580.282818"]]},{"id":"67777787.0010c8","type":"http response","name":"output","x":935,"y":510,"z":"710b37a5.df17d8","wires":[]},{"id":"d224a580.282818","type":"sqldb in","service":"SQL Database","query":"select * from schema.tablename","params":"","name":"sql","x":465,"y":493,"z":"710b37a5.df17d8","wires":[["62189e4e.98dc48"]]},{"id":"62189e4e.98dc48","type":"template","name":"Output","field":"payload","format":"handlebars","template":"<html>\n    <head>\n        <style>\ntable, th, td {\n    border: 1px solid black;\n}\nth {\n    text-align: left;\n}\ntd {\n    padding: 15px;\n}\nth {\n    background-color: green;\n    color: white;\n}\n\n</style>\n        <title>Archived Tweets</title>\n    </head>\n    <body>\n        <h1>Archived Tweets</h1>\n        The following tweets have been archived in DB2 so far:\n        <table>\n            <tr><th>User</th><th>Tweet</th><th>Time</th></tr>\n            {{#payload}}<tr>\n                <td><a href=\"http://twitter.com/{{USERNAME}}\">{{USERNAME}}</a></td><td>{{TWEET}}</td><td>{{TSTAMP}}</td>\n            </tr>{{/payload}}\n        </table>\n    </body>\n</html>","x":681,"y":509,"z":"710b37a5.df17d8","wires":[["67777787.0010c8"]]}]