Introduction
SAP SQL Anywhere is a relational database management system solution. It was also known as Sybase SQL Anywhere prior to the acquisition of Sybase by SAP back in 2010. SAP SQL Anywhere provides several standard interfaces (ODBC, ADO.NET, etc.), as well as few special interfaces (Perl, PHP). Some of many features of this database include strong encryption, scalability for thousands of users, and resource efficiency with minimal memory footprint. Because of such features SAP SQL Anywhere makes a strong candidate to base your IoT solutions on.
During development of a project that partly involves various chat-bots, I chose to use this database as the foundation. I’ve managed to come up with a basic notification system via the Telegram messenger, that utilizes SAP SQL Anywhere database. Therefore, the main purpose of this article is to share my experience integrating this RDMS in such project, the current progress, as well as demonstrate usage, implementation and capabilities of SAP SQL Anywhere RDMS in the chat-bot development by dissecting this project. I'll also include a link to this project's repository at the end of the article, so you shall be able to explore its codebase yourself.
It’s also worth mentioning that I developed the very first working prototype back in late 2019 – way before the current situation with the pandemic took place. You may learn more about chat-bots' potential by reviewing reports like this one, as well as some forecasts.
Implementation Overview
I chose to code my bot in Python using sqlanydb as a driver for SAP SQL Anywhere and AIOgram as a framework for Telegram Bot API. I also decided to implement the computer vision elements and a QR-code detector from one of my earlier projects, so the bot sends notification based on a QR-code that our camera detects. Consider the following model: suppose our products are stored in boxes with QR-codes that encode the recipients' addresses, and the webcam can capture it as soon as the boxes arrive at the warehouse. Once a QR-code on a box is captured, our application decodes it, queries the SAP SQL Anywhere database and notifies a user via Telegram about the product's arrival. It is, however, just one possible implementation of such features. For this computer vision portion of the project I used OpenCV and NumPy modules.
Hence, this project currently ended up consisting out of three main components:
- SAP SQL Anywhere database interaction (via sqlanydb);
- QR-code detection and decoding from the camera's video feed (via OpenCV and NumPy);
- Running the bot itself using asynchronous HTTP requests (via AIOgram).
To sum it up, here you can see a rough flowchart of the whole application:
Main flowchart.
Let’s do a brief overview on each of the above components along with more in-depth flowcharts and some code snippets:
First, we create our SAP SQL Anywhere database by running the "dbinit" CLI-utility:
dbinit -dba admin,YourPassword -p 4k -z UTF8 -ze UTF8 -zn UTF8 orders.dbThis command creates a database file "orders.db", sets the database admin (DBA) to a user "admin" (with "YourPassword" as the password), sets the page-size to 4KB and specifies the encoding as UTF-8. Now we can connect to it via SQL Central and create a table to store details about our customers' orders. Here's a query setting up such table using SQL Central's Interactive SQL utility:
CREATE TABLE Orders (
-- ID of an order
id UNSIGNED INT PRIMARY KEY NOT NULL IDENTITY,
-- Product's name
product NVARCHAR(24) NOT NULL,
-- Product's model
model NVARCHAR(20),
-- Product's price (in Euros)
price DECIMAL(10,2) NOT NULL,
-- Amount of the product
amount UNSIGNED INT NOT NULL DEFAULT 1,
-- Weight of the product (in kilograms)
weight DECIMAL(8,3) NOT NULL,
-- Customer's first name
first_name NVARCHAR(16) NOT NULL,
-- Customer's last name
last_name NVARCHAR(20),
-- Customer's physical address
address NVARCHAR(48) NOT NULL,
-- Customer's Telegram ID
telegram_id UNSIGNED INT NOT NULL,
-- Customer's timezone
timezone NVARCHAR(16) DEFAULT 'UTC',
-- Customer's prefered locale
locale NVARCHAR(5) DEFAULT 'en_US'
);Here's an example of a record from this table:
A record from the "Orders" table.
This database is then easily connected to the bot, granted the credentials (in the example above: “admin” as UID and “YourPassword” as password) have been added to the environment variables. Thus, the bot got access to the “Orders” table and is free to analyze and manipulate the provided data:
conn = sqlanydb.connect(uid=config.DB_UID, pwd=config.DB_PASSWORD)
curs = conn.cursor()After a successful connection to the SAP SQL Anywhere database bot attempts to open a capturing device (such as a built-in webcam, for example) and to stream its video feed in a new window:
cap = cv2.VideoCapture(0)Once the device is opened, our application adds some basic UI on top of the feed. For example, we look for QR-codes to appear in certain region (a square in the center defined by a CLI-argument) of the feed, so a special function (see its code here) draws this region on top of the stream for visual guidance:
UI overlaid on top of the web-cam stream
Next flowchart illustrates the logic of a module (see its implementation here) responsible for detecting and decoding QR-codes appearing on the stream:
QR-code detection flowchart.
Let’s break it down a bit: this module's main function searches for an object inside the square region (depicted on the screenshot above) that
- is brighter than a certain threshold (defined as a CLI-argument);
- has got area larger than a certain threshold (also defined as a CLI-argument);
- is fully contained inside the square region.
If all these conditions are met, that means there might be a QR-code on the feed. The following two screenshots might help illustrate the point:
QR-code appears on the feed's frame.
Our app analyses the frame's contents.
The app then passes down the cropped image of a potential QR-code for further analysis. If it turns out to be a false positive (i.e. the object is non-decodable) then the bot keeps on monitoring the feed, but if there is indeed a QR-code, bot decodes it, queries the table in our SAP SQL Anywhere database about order information based on that QR-code, and sends a notification via Telegram to a user specified in the order’s record (see the code here). It follows this flowchart:
Notification flowchart.
So the decoded data is interpreted as the user's address from the "address" field in the `Orders` table. Once again, it's just one of the possibilities I chose for this example – the QR-code can encode whatever can be connected to the customer. Their physical address just seemed to be a unique enough property for our purposes. Hence, the bot queries for a record that has the decoded data under the "address" field, extracts the customer's Telegram UserID (as well as everything relevant to the order) and sends a notification.
It's also worth mentioning that as of now the sqlanydb driver doesn't support sanitization of our SQL-queries. The module, however is still under development, so this feature may become available in its future releases. But for now we should check whether the decoded string contains in the set of all available addresses from our "Orders" table first. So if we get a QR-code that encodes a string like
"'; DROP TABLE Orders;"our data would be safe.
Here’s also an example of such notification in action:
Notification example.
Please note the automatic conversion to the user’s preferred locale and time zone. Also the bot accounts for the user not being available at the moment, as well as for various technical malfunctions. After the notification is sent the bot will carry on and await the next QR-code.
Finally, the bot interaction itself is fully governed by the AIOgram module. There’s a lot to unpack and since the most of it would be out of the scope of this article, please consult their official documentation, as well as the Telegram Bot API (as of writing this article, Telegram Bot API version 5.0 has been introduced offering tons of new features) if you’re interested. I’ll limit myself to presenting a snippet demonstrating the locale setting functionality via a bot command “/lang”:
@dp.message_handler(commands=["lang"])
async def cmd_lang(message: Message) -> None:
"""Handles the "/lang" command from a Telegram user. Allows the user to change the locale from the chosen one.
Outputs the message in the language that was initially chosen by the user.
Args:
message (Message): User's Telegram message that is sent to the bot.
"""
query = "SELECT locale FROM %s.%s WHERE telegram_id=%d;"
curs.execute(
query
% (
config.DB_UID,
config.DB_TABLE_NAME,
message.from_user.id,
)
)
(lang,) = curs.fetchone()
logger.debug('Got user\'s {} current language "{}"', message.from_user.id, lang)
str_lang = "Please choose your language\." if lang.startswith("en") else "Пожалуйста, выберите язык\."
btn_en = InlineKeyboardButton(" English", callback_data="lang_en")
btn_ru = InlineKeyboardButton(" Русский", callback_data="lang_ru")
inline_kb = InlineKeyboardMarkup().add(btn_en, btn_ru)
await bot.send_message(message.chat.id, str_lang, reply_markup=inline_kb)
logger.info("User {} called /lang", message.from_user.id)
@dp.callback_query_handler(lambda c: c.data.startswith("lang"))
async def set_lang(cb_query: CallbackQuery) -> None:
"""Handles the callback that sets the user preferred locale. Updates the locale in the table.
Args:
cb_query (CallbackQuery): User's Telegram callback query that is sent to the bot.
"""
lang = "en_US" if cb_query.data.endswith("en") else "ru_RU"
info = "Setting your language..." if lang.startswith("en") else "Настраиваю язык..."
await bot.answer_callback_query(cb_query.id, text=info)
try:
query = "UPDATE %s.%s SET locale='%s' WHERE telegram_id=%d;"
curs.execute(
query
% (
config.DB_UID,
config.DB_TABLE_NAME,
lang,
cb_query.from_user.id,
)
)
logger.debug("Commiting the changes")
conn.commit()
except sqlanydb.Error as ex:
logger.exception(ex)
return
str_setlang = (
"Language is set to English\.\nCall /lang to change it\."
if lang.startswith("en")
else "Ваш язык Русский\.\nВызовите команду /lang, чтобы его изменить\."
)
logger.info('User {} set the language to "{}"', cb_query.from_user.id, lang)
await bot.send_message(cb_query.from_user.id, str_setlang)Once again, there’s some interaction with the SAP SQL Anywhere database, as well as the error handling. By the way, note that here we don't run any additional checks on the queries. That's because the only part of that's not hardcoded is the user's Telegram ID, and it's always just an integer.
And here's the output in Telegram itself:
Bot init and language setup.
Overall, I chose AIOgram because of it being a fully asynchronous and well documented framework with many extensive features. As a result of all three components running harmoniously, we have an automatic notification of our client about his/her order delivery status via a popular messaging app. The client can easily set the preferred language (English or Russian) in the bot, and the timestamps are auto-adjusted based on the client's time zone.
In Conclusion
As already mentioned above, I made and presented this example-project way before the covid-19 pandemic broke out. However, because of this situation the delivery services must be on its highest right now. Hence, this article is my attempt to provide a source for a potential chat-bot implementation by means of SAP SQL Anywhere RDMS, as well as a minimal working example using Telegram.
As for the SAP SQL Anywhere product, it proved itself very efficient in this project, and I believe that because of its scalability and low memory footprint, it'll have no problem performing on the full scale of such ambitious projects. There’s still plenty of room for improving and extending the functionality. Thus, the next logical step would be integration with SAP Cloud Platform – I’m currently working on it and there might be the continuation of this article based on my results.
Lastly, the project itself is open-source and is available on SAP Samples Github repository under the Apache Software License, version 2.0, so feel free to browse and inspect the code. In case you would like to run it and test it locally, please refer to the "Download and installation" section of the repository’s README: it consists of a step-by-step setup of a minimal working example along with other details. If you have any suggestions, questions and/or criticism, please share it in a comment below or via an issue/PR on Github – any feedback would be greatly appreciated!
- SAP Managed Tags:
