https://github.com/adsau59/supbot
Basic
✔Intermediate
Advanced
In this Supbot tutorial, we will take a look on how database works in Supbot API. We will see why should we use database of Supbot API instead of coding it manually, then we will create Pin Feature inside Whatsapp using which clients will be able to pin messages, delete the pinned messages and show pins when needed.
In simple terms, database is a way by which we can store, access, manage and update data easily. Supbot API uses JDBC and SQlite to create database. Database class of Supbot API implements some methods which simplifies creating new tables and versioning them.
In order to create a new database table in Supbot you will have to,
Database Constructor Definition
Constructor of Client Database
For the Bot to use the correct version of the database table you want, you will have to specify which version you want along with the database name in the Constructor. Name is used as a reference to log in console and to save the version in config.json file.
When the bot is executed, the bot will try to upgrade/dowgrade to the specified version from the current version using VersionChangers. New VersionChangers can be added by using addVersionChanger() method. The version of newly created Database is 0, so to use the database you will have to use addVersionChanger to upgrade it to version 1.
addVersionChanger definition in Database class
addVersionChanger of ChatgroupDatabase
Console output when Database upgrades
addVersionChanger method helps us to call specific query according to which version it currently is, so if the current version is 2 and you tell it to use version 3, it will use the addVersionChanger with version 3 to upgrade the database.
VersionChanger contains 2 methods that have to be implemented, upTo method will upgrade the table to specified version, downFrom method will downgrade from specified version to previous one. In the upTo and downFrom methods jdbc connection is passed using the parameter which can be used to create statements to execute query in the sqlite database.
saveClient in ClientDatabase
For the bot to comunicate with the database, to insert/select/delete from database static methods are using in Supbot API, in the method a connection has to be created with the Supbot SQlite database file using the connect() method (connect method is a method of Supbot not JDBC that is defined inside Database class). Then by using prepareStatement() queries can be created and executeQuery() to executue them.
Pin feature
After we learn about Database in Supbot API, we will create a Pin feature. The Pin feature will have 3 commands.
Creating the pin command you will learn how you could save a row into the database along with the client information to remember the author and the group in which it was pinned.
Creating the show pin command you will learn how you could select rows from database and display it with format as a whatsapp message.
Creating the delete-pin command you will learn how to delete targeted rows from the database and how can you add logic like, to check if the client has enough permission to perform an action.
