Ep.3 Database | Supbot Whatsapp Chatbot API

Repository

https://github.com/adsau59/supbot

What Will I Learn?

• What is Database in Supbot API?
• Why are we using Database of Supbot?
• How can we use it?

Requirements

• Java Development Kit, IDEA intellij
• Google Chrome, Selenium Chrome Webdrivers
• Supbot API
• DB Browser for SQlite

Required Knowledge

• Java
• JDBC and SQLite Query Language
• Commands and Feature of Supbot API

Resources for this Tutorial:

• Tutorial Repository
• Source without exercise solution
• Source with exercise solution

Difficulty

Basic
✔Intermediate
Advanced

Description

Outline and Overview

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.

Database

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.

How to create Database?

In order to create a new database table in Supbot you will have to,

• First create a class and extend it with Database.
• Then create a constructor.
• Then add the Database to the bot by writing Bot.getDatabaseManager().add() method in addThingsInBot method in Main class.

Constructor

_{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

_{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.

Static Methods

_{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

_{Pin feature}

After we learn about Database in Supbot API, we will create a Pin feature. The Pin feature will have 3 commands.

• pin, which will save the message.
• show-pins, which shows all the saved messages in the group.
• delete-pin, which delete the saved pin.

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.

Video Tutorial

Curriculum

• Ep.0 Introduction to Supbot API
• Ep.1 Setting up Supbot
• Ep.2 Commands and Feature

Proof of Work Done

https://github.com/adsau59