gurchandansingh_sub&dtb.py

import paho.mqtt.client as mqtt #This line imports the mqtt clients, and the various function to use
import sqlite3 #this line lets us use SQLite, which is needed for the database
from datetime import datetime #this line is specially there, so that we can use a specific timestamp for our database
import time  # Add this line to import the time module

# SQLite connection and the cursor for the database
conn = sqlite3.connect('mqtt_data.db')
c = conn.cursor()

# Creates the tables if it doesn't already exist in the database
c.execute('''CREATE TABLE IF NOT EXISTS mqtt_data
             (timestamp DATETIME PRIMARY KEY, 
              total_likes INTEGER,
              total_comments INTEGER,
              total_notifications INTEGER)''')#this bit of the code explains the various columns that are going to be added, and their datatype

# Function to retrieve the latest counts from the database
def get_latest_counts():
    c.execute("SELECT * FROM mqtt_data ORDER BY timestamp DESC LIMIT 1")#it gets the data from the code, and makes sure a new row is added if there is a different timestamp
    row = c.fetchone()
    if row:
        return row[1:]  # Exclude the timestamp is similar to one in the database already
    else:
        return (0, 0, 0)  # Return default values if no data is found

# The callback for when the client receives a CONNACK response from the server.
def on_connect(client, userdata, flags, rc):
    print("Connected with result code " + str(rc))
    # Subscribing in on_connect() means that if we lose the connection and
    # reconnect then subscriptions will be renewed.
    # We have three topics, that can be subscribed too, which in my case are the Likes, Notification and Comments
    # These topics are for my individual part of the code, which tell us how many likes, comments and notification a post has received
    client.subscribe("Likes")
    client.subscribe("Notification")
    client.subscribe("Comments")

# The callback for when a PUBLISH message is received from the server.
def on_message(client, userdata, msg): #define the function named 'on_message'
    timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')#obtains current timeframe in the format displayed
    print("Received message on topic '" + msg.topic + "': " + msg.payload.decode())#simple print statement
    c.execute("INSERT OR IGNORE INTO mqtt_data (timestamp, total_likes, total_comments, total_notifications) VALUES (?, 0, 0, 0)", (timestamp,))
    #the line above executes a SQL Query, however if the timestamp received is already in the database, it ignores the data entries

    if msg.topic == "Likes": #this bit of the code checks for the topic 'Likes', and update the data entry into the database with the proper timestamp
        c.execute("UPDATE mqtt_data SET total_likes = total_likes + ? WHERE timestamp = ?", (int(msg.payload.decode()), timestamp))
        c.execute("UPDATE mqtt_data SET total_notifications = total_likes + total_comments WHERE timestamp = ?", (timestamp,))

    elif msg.topic == "Comments": #same as above, but it checks for 'Comments'
        c.execute("UPDATE mqtt_data SET total_comments = total_comments + 1 WHERE timestamp = ?", (timestamp,))
        c.execute("UPDATE mqtt_data SET total_notifications = total_likes + total_comments WHERE timestamp = ?", (timestamp,))

    elif msg.topic == "Notification": #same as above but it checks for 'Notificaion', however it counts the total likes and comments
        # Update total_notifications when a Notification message is received
        c.execute("UPDATE mqtt_data SET total_notifications = total_likes + total_comments WHERE timestamp = ?", (timestamp,))
    conn.commit()# this line simply commits the changes, and has the data entry saved permanently

# Function to display the latest counts
def display_latest_counts(): #defining the funtioon
    while True:
        likes, comments, notifications = get_latest_counts()#retrives the function that has been declared beforehand
        print("Total Likes:", likes)
        print("Total Comments:", comments)
        print("Total Notifications:", notifications)
        print("--------------------")
        time.sleep(5)  # Update the display every 5 seconds

client = mqtt.Client() #creating a MQTT client, simple named 'client'
client.on_connect = on_connect #assigning the 'on_connect'-function, that can be called once the client connects to the broker
client.on_message = on_message #assigning the 'on_connect'-function, that can be called once the client receives a message from the broker

client.connect("broker.hivemq.com", 1883, 60) #defines which broker we are connecting, and through which port.
#It also tells us to keep the connection running for 60 seconds, before considering that the connection is lost or broken

# Start a thread to continuously display the latest counts
import threading #imports the threading module, which allows us to execute multiple tasks at once
display_thread = threading.Thread(target=display_latest_counts)#creates a new thread, which targets 'display_latest_counts'
display_thread.daemon = True #the daemon attributes is used and set to 'True', so that we can exit the program anytime
display_thread.start()
#it starts the thread and allows the program to display the latest count of likes, notification and comments
#while simultaneously processing other tasks

client.loop_forever()#it loops the program, and keeps it going as long as a connection is there