Logging#

The framework allows to log sent messages for each GUILD/USER (if you set the “logging” to True inside the GUILD or USER object). There are 2 different types of logs:

Relational Database Log (SQL)#

New in version v1.9.

Changed in version v2.1: Turned into an optional feature.

pip install discord-advert-framework[sql]

This type of logging enables saving logs to a remote server inside the database. Currently only Microsoft SQL server is supported.. In addition to being smaller in size, database logging takes up less space and it allows easier data analysis.

Usage#

To use a SQL base for logging, you need to pass the run function with the sql_manager parameter and pass it the LoggerSQL object.

from datetime import timedelta
import daf

rolls = [
    "https://i.pinimg.com/originals/b7/fb/80/b7fb80122cf46d0e584f3a0768aef282.gif",
    "https://bit.ly/3sHrjQZ",
    "https://static.wikia.nocookie.net/a1dea591-8a10-4c02-a573-5321c601c129",
    "https://www.gifcen.com/wp-content/uploads/2022/03/rickroll-gif-4.gif",
    "https://bit.ly/3u5D8Dt",
    "http://static1.squarespace.com/static/60503ac20951e15087fbe7b8/60504609ee9c445722c9dd4e/60e3f9b541eb1b01e8e46854/1627103366283/RalphRoll.gif?format=1500w",
    "https://i.imgflip.com/56bhvt.gif",
    "https://www.youtube.com/watch?v=dQw4w9WgXcQ"
]

@daf.data_function
def get(st):
    item = st.pop(0)
    st.append(item)
    return item

servers = [
    daf.GUILD(
        snowflake=12345,
        messages=[
            daf.TextMESSAGE(None, timedelta(seconds=10), get(rolls.copy()), [12345], "edit", timedelta(seconds=5))
        ],
        logging=True
    )
]


daf.run(
    token="OSDSJ44JNnnJNJ2NJDBWQUGHSHFAJSHDUQHFDBADVAHJVERAHGDVAHJSVDE",                # Example account token
    is_user=False, 
    server_list=servers,
    sql_manager=daf.LoggerSQL("username", "password", "server address", "database name") # *Note: The database must be created manually,
                                                                                        #  everything else in the database is then created automatically
)

Features#

  • Automatic creation of tables, procedures, functions, views, triggers

  • Caching for faster logging

  • Low redundancy for reduced file size

  • Automatic error recovery:

    • Automatic reconnect on disconnect - Retries 3 times in delays of 5 minutes, then switches to file logging

    • If tables are deleted, they are automatically recreated

    • If cached values get corrupted, they are automatically re-cached

    • If there are un-recoverable errors, the framework switches to file logging

Note

The database must already exist! However it can be completely empty, no need to manually create the schema.

ER diagram of the logs#

_images/er_diagram.png

Tables#

MessageLOG#

Description:

This table contains the actual logs of sent messages, if the message type is DirectMESSAGE, then all the information is stored in this table. If the types are Voice/Text MESSAGE, then part of the log (to which channels it sent), is saved in the MessageChannelLOG table.

Attributes:

  • [Primary Key] id: int - This is an internal ID of the log inside the database.

  • sent_data: int - Foreign key pointing to a row inside the DataHISTORY table.

  • message_type: int - Foreign key ID pointing to a entry inside the MessageTYPE table.

  • guild_id: int - Foreign key pointing to GuildUSER table.

  • message_mode: int - Foreign key pointing to MessageMODE table. This is non-null only for DirectMESSAGE.

  • dm_reason: str - If MessageTYPE is not DirectMESSAGE or the send attempt was successful, this is NULL, otherwise it contains the string representation of the error that caused the message send attempt to be unsuccessful.

  • timestamp: datetime - The timestamp of the message send attempt.

DataHISTORY#

Description:

This table contains all the different data that was ever advertised. Every element is unique and is not replicated. This table exist to reduce redundancy and file size of the logs whenever same data is advertised multiple times. When a log is created, it is first checked if the data sent was already sent before, if it was the id to the existing DataHISTORY row is used, else a new row is created.

Attributes:

  • [Primary Key] id: int - Internal ID of data inside the database.

  • content: str - Actual data that was sent.

MessageTYPE#

Description:

This is a lookup table containing the the different message types that exist within the framework (Messages).

Attributes:

  • [Primary Key] id: int - Internal ID of the message type inside the database.

  • name: str - The name of the actual message type.

GuildUSER#

Description:

The table contains all the guilds/users the framework ever generated a log for.

Attributes:

  • [Primary Key] id: int - Internal ID of the Guild/User inside the database.

  • snowflake_id: int - The discord (snowflake) ID of the User/Guild

  • name: str - Name of the Guild/User

  • guild_type: int - Foreign key pointing to GuildTYPE table.

MessageMODE#

Description:

This is a lookup table containing the the different message modes available by TextMESSAGE / DirectMESSAGE, it is set to null for VoiceMESSAGE.

Attributes:

  • [Primary Key] id: int - Internal identifier of the message mode inside the database.

  • name: str - The name of the actual message mode.

GuildTYPE#

Description:

This is a lookup table containing types of the guilds inside the framework (Guilds (Servers)).

Attributes:

  • [Primary Key] id: int - Internal identifier of the guild type inside the database.

  • name: str - The name of the guild type.

CHANNEL#

Description:

The table contains all the channels that the framework ever advertised into.

Attributes:

  • [Primary Key] id: int - Internal identifier of the channel inside the database

  • snowflake_id: int - The discord (snowflake) identifier representing specific channel

  • name: str - The name of the channel

  • guild_id: int - Foreign key pointing to a row inside the GuildUSER table. It points to a guild that the channel is part of.

MessageChannelLOG#

Description:

Since messages can send into multiple channels, each MessageLOG has multiple channels which cannot be stored inside the MessageLOG. This is why this table exists. It contains channels of each MessageLOG.

Attributes:

  • [Primary Key] [Foreign Key] log_id: int ~ Foreign key pointing to a row inside MessageLOG (to which log this channel log belongs to).

  • [Primary Key] [Foreign Key] channel_id ~ Foreign key pointing to a row inside the CHANNEL table.

SQL custom data types#

Warning

These will be removed in the next version.

This sections contains descriptions on all SQL data types that are user-defined.

t_tmp_channel_log#

Description:

This is only used in the sp_save_log procedure to accept a list of channels it was attempted to send into and the reason for failure. This is a custom table type that contains attributes:

Attributes:

  • id: int ~ Internal DB id pointing to CHANNEL table.

  • reason: nvarchar ~ Reason why sending to the channel failed, if it was successful then this is NULL.

SQL Stored Procedures (SP)#

Warning

These will be removed in the next version.

This section contains the description on all the saved procedures inside the SQL database.

sp_save_log#

sp_save_log(@sent_data nvarchar(max),
        @message_type smallint,
        @guild_id int,
        @message_mode smallint,
        @dm_reason nvarchar(max),
        @channels t_tmp_channel_log READONLY
Description:

This procedure is used by the SQL python module to store the log instead of using SQLAlchemy for faster saving speed.

Attributes:

  • sent_data: nvarchar - The data that was sent (jsonized).

  • message_type: smallint - Internal DB id that points to MessageTYPE table.

  • guild_id: int ~ Internal DB id that points to GuildUSER table.

  • message_mode: smallint - Internal DB id that points to MessageMODE table.

  • dm_reason: nvarchar - This can only be different from NULL if the type of message is DirectMESSAGE. In the case that the message type is DirectMESSAGE, then this attribute is different from NULL when the send attempt failed, if send attempt succeeded, then this is NULL.

  • channels: t_tmp_channel_log ~ Table Valued Parameter (TVP) - Holds channels it was advertised into, if message type is DirectMESSAGE, this is an empty table.

SQL User Defined Functions (UDF)#

Warning

These will be removed in the next version.

This section contains the description on all user defined functions inside the SQL database.

fn_log_success_rate#

fn_log_success_rate(@log_id int)
Description:

This UDF can only be used for logs that have channels, so logs that originated from TextMESSAGE or VoiceMESSAGE. The UDF calculates relative success of sent channels for a specific log entry (successful channels / all channels) which is a number between 0 and 1, 0 meaning no channels succeeded and 1 meaning all channels succeeded.

Parameters:

  • log_id: int - The DB id of a certain log that is inside the database.

Return:

The UDF returns the success rate - a number of type decimal with 5 decimals of precision.

Note

If the log_id is an id of a message log that has no channels, the UDF will return 1.

fn_guilduser_success_rate#

fn_log_success_rate(@snowflake_id bigint, @limit int = 1000)
Description:

This UDF returns relative success rate for specific GUILD/USER based on the last few logs. Success rate is defined as (number of fully successful send attempts) / (number of all send attempts)

Parameters:

  • snowflake_id: bigint ~ Discord’s ID (snowflake id) of the USER or GUILD you want to get the success rate for

  • limit: int ~ How many of the latest logs you want to use to calculate the relative success rate

Return:

The UDF returns the success rate ~ a number of type decimal with 5 decimals of precision.

Note

If no logs exist for specific GUILD/USER, 1 is returned.

Views#

Warning

These will be removed in the next version.

This section contains the description on all views inside the SQL database.

vMessageLogFullDETAIL#

Description:

The MessageLOG table contains mostly internal DB ids which makes it hard to see anything directly from it. This is why the vMessageLogFullDETAIL view exists. It contains all the information inside the MessageLOG table, but expanded with actual values and not just IDs making it easier to view the content of the log.

Triggers#

Warning

These will be removed in the next version.

This section contains the description on all the triggers inside the SQL database.

tr_delete_msg_log#

Description:

Entries in MessageChannelLOG get deleted if an entry inside MessageLOG gets deleted due to the MessageChannelLOG table having a cascading foreign key pointing to the MessageLOG table. However reverse is not the same, the MessageLOG table does not have anything pointing to the MessageChannelLOG table meaning that cascading based on foreign keys is not possible. This trigger’s job is to delete an entry inside the MessageLOG when all the entries in MessageChannelLOG referencing it get deleted.

JSON Logging (file)#

The logs are written in the JSON format and saved into a JSON file, that has the name of the guild or an user you were sending messages into. The JSON files are fragmented by day and stored into folder Year/Month/Day, this means that each day a new JSON file will be generated for that specific day for easier managing, for example, if today is 13.07.2022, the log will be saved into the file that is located in

History
└───2022
│   └───07
│       └───13
|           └─── #David's dungeon.json

Code Example#

Code to produce JSON logs#
from datetime import timedelta
from daf import trace
import daf

rolls = [
    "https://i.pinimg.com/originals/b7/fb/80/b7fb80122cf46d0e584f3a0768aef282.gif",
    "https://bit.ly/3sHrjQZ",
    "https://static.wikia.nocookie.net/a1dea591-8a10-4c02-a573-5321c601c129",
    "https://www.gifcen.com/wp-content/uploads/2022/03/rickroll-gif-4.gif",
    "https://bit.ly/3u5D8Dt",
    "http://static1.squarespace.com/static/60503ac20951e15087fbe7b8/60504609ee9c445722c9dd4e/60e3f9b541eb1b01e8e46854/1627103366283/RalphRoll.gif?format=1500w",
    "https://i.imgflip.com/56bhvt.gif",
    "https://www.youtube.com/watch?v=dQw4w9WgXcQ"
]

@daf.data_function
def get(st):
    item = st.pop(0)
    st.append(item)
    return item

servers = [
    daf.GUILD(
        snowflake=12345,
        messages=[
            daf.TextMESSAGE(None, timedelta(seconds=5), get(rolls.copy()), [12345], "edit", timedelta(seconds=5))
        ],
        logging=True
    )
]


daf.run(
    token="OSDSJ44JNnnJNJ2NJDBWQUGHSHFAJSHDUQHFDBADVAHJVERAHGDVAHJSVDE",   # Example account token
    is_user=False, 
    server_list=servers
)

Example of a log#

JSON file log#
{
    "name": "\\David's dungeon",
    "id": 863071397207212052,
    "type": "GUILD",
    "message_history": [
        {
            "sent_data": {
                "text": "https://i.imgflip.com/56bhvt.gif"
            },
            "channels": {
                "successful": [
                    {
                        "name": "sp",
                        "id": 978332728615378984
                    }
                ],
                "failed": []
            },
            "type": "TextMESSAGE",
            "mode": "edit",
            "index": 6,
            "timestamp": "23.05.2022 18:28:15"
        },
        {
            "sent_data": {
                "text": "http://static1.squarespace.com/static/60503ac20951e15087fbe7b8/60504609ee9c445722c9dd4e/60e3f9b541eb1b01e8e46854/1627103366283/RalphRoll.gif?format=1500w"
            },
            "channels": {
                "successful": [
                    {
                        "name": "sp",
                        "id": 978332728615378984
                    }
                ],
                "failed": []
            },
            "type": "TextMESSAGE",
            "mode": "edit",
            "index": 5,
            "timestamp": "23.05.2022 18:28:09"
        },
        {
            "sent_data": {
                "text": "https://bit.ly/3u5D8Dt"
            },
            "channels": {
                "successful": [
                    {
                        "name": "sp",
                        "id": 978332728615378984
                    }
                ],
                "failed": []
            },
            "type": "TextMESSAGE",
            "mode": "edit",
            "index": 4,
            "timestamp": "23.05.2022 18:28:04"
        },
        {
            "sent_data": {
                "text": "https://www.gifcen.com/wp-content/uploads/2022/03/rickroll-gif-4.gif"
            },
            "channels": {
                "successful": [
                    {
                        "name": "sp",
                        "id": 978332728615378984
                    }
                ],
                "failed": []
            },
            "type": "TextMESSAGE",
            "mode": "edit",
            "index": 3,
            "timestamp": "23.05.2022 18:27:59"
        },
        {
            "sent_data": {
                "text": "https://static.wikia.nocookie.net/a1dea591-8a10-4c02-a573-5321c601c129"
            },
            "channels": {
                "successful": [
                    {
                        "name": "sp",
                        "id": 978332728615378984
                    }
                ],
                "failed": []
            },
            "type": "TextMESSAGE",
            "mode": "edit",
            "index": 2,
            "timestamp": "23.05.2022 18:27:54"
        },
        {
            "sent_data": {
                "text": "https://bit.ly/3sHrjQZ"
            },
            "channels": {
                "successful": [
                    {
                        "name": "sp",
                        "id": 978332728615378984
                    }
                ],
                "failed": []
            },
            "type": "TextMESSAGE",
            "mode": "edit",
            "index": 1,
            "timestamp": "23.05.2022 18:27:49"
        },
        {
            "sent_data": {
                "text": "https://i.pinimg.com/originals/b7/fb/80/b7fb80122cf46d0e584f3a0768aef282.gif"
            },
            "channels": {
                "successful": [
                    {
                        "name": "sp",
                        "id": 978332728615378984
                    }
                ],
                "failed": []
            },
            "type": "TextMESSAGE",
            "mode": "edit",
            "index": 0,
            "timestamp": "23.05.2022 18:27:44"
        }
    ]
}