A Telegram Bot for PostgreSQL
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
telegram_fdw
.gitignore
LICENSE teaching elephant to talk Mar 5, 2016
README.md
setup.py

README.md

telegram_fdw - a Telegram BOT, for PostgreSQL

telegram_fdw is a Telegram BOT implemented using the PostgreSQL foreign data wrapper interface.

Install

telegram_fdw was tested under PostgreSQL versions 9.4 and 9.5.0 (Multicorn actually has a bug on PostgreSQL 9.5.1)

To install telegram_fdw on Debian is easy, just follow the steps:

sudo apt-get install postgresql-9.4-python-multicorn
sudo pip install twx.botapi
sudo pip install http://github.com/guedes/telegram_fdw/archive/v0.1.0.zip

The example bellow shows how to use the extension on database monitoring that owner is the user monitor.

First, creating the multicorn extension:

psql -d monitoring -U postgres
create extension multicorn;
grant usage on foreign data wrapper multicorn to monitor;

Then, create the multicorn server on monitoring database:

psql -d monitoring -U monitor
create server multicorn_srv
       foreign data wrapper multicorn
       options (wrapper 'telegram_fdw.TelegramForeignDataWrapper');

Creating bots

To create a bot, just create a foreign table, like:

create foreign table telegram_monitor_bot 
(
       update_id     bigint,
       chat_id     	 bigint,
       message	     varchar,
       payload		 json
)
server multicorn_srv
options (primary_key 'update_id', bot_id '<<THE BOT TOKEN>>');

NOTE: See how BotFather can give you THE BOT TOKEN

Testing your bot

In the Telegram app, search your bot (created in BotFather) and send it a messagem.

You can get all messages sent to your bot with a simple SELECT:

SELECT * from telegram_monitor_bot;

 update_id |  chat_id  |  message  |                                                                                                                                                                                                                                                                                                                                                                   payload                                                                                                                                                                                                                                                                                                                                                                    

-----------+-----------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 58493114  | 11123574  | Hi Bot!!  | {"update": {"message": {"delete_chat_photo": null, "new_chat_photo": null, "text": "Hi Bot!!", "sticker": null, "video": null, "chat": {"id": 1123574, "type": "private", "title": null, "username": "testuser", "first_name": "John", "last_name": "Smith"}, "group_chat_created": null, "forward_date": null, "left_chat_participant": null, "location": null, "photo": null, "document": null, "forward_from": null, "new_chat_participant": null, "date": 145210377, "audio": null, "reply_to_message": null, "sender": {"id": 11123574, "first_name": "John", "last_name": "Smith", "username": "testuser"}, "message_id": 101, "caption": null, "contact": null, "voice": null, "new_chat_title": null}, "update_id": 58493114}}
(1 row)

To list all current chats:

SELECT DISTINCT
	payload #>> '{update,message,chat,id}'         as chat_id,
	payload #>> '{update,message,chat,first_name}' as first_name,
	payload #>> '{update,message,chat,last_name}'  as last_name,
	payload #>> '{update,message,chat,username}'   as username
FROM telegram_monitor_bot;

  chat_id  | first_name | last_name | username 
-----------+------------+-----------+----------
 11123574  | John       | Smith     | testuser
 11329588  | Foo        | Bar       | foobar
(1 row)

NOTE: payload returns a json field so we can use json functions to get the values.

Your bot can respond a message to a chat, using the respective chat_id. From the example above let's respond to Foo Bar:

INSERT INTO telegram_monitor_bot (chat_id, message)
VALUES (11329588, 'Hi! How are you?');

What about send a little 🐘 ?

INSERT INTO telegram_monitor_bot (chat_id, message)
VALUES (11329588, E'Hi! \U0001f418');

TIP: Emoji Unicode is a Telegram Bot that receives an emoji and returns the respective UNICODE.

That's it! Have fun!

TODO

  1. a BG Worker to async communication,
  2. functions to help creating bots, send messages, etc,
  3. a MonitorBot as an example of using KeyboardMarkup,
  4. translate :emojis: to respective UNICODE
  5. ...

License

telegram_fdw was created by Dickson S. Guedes and is released under PostgreSQL License.

See LICENSE file for information.