I'm hoping someone can give me some insight as to why I'm not receiving accurate results. I'm using tmi.js to create a chat bot for Twitch (or rather a specific channel). The guide in the link provides a very basic introduction on what to do and since starting, I've added a lot more commands and functions. My issue is related to 1 specific command and SQLite (or I guess SQLite3).
The command is simply !time and that returns the amount of time that a user has spent in the stream. I've made the command either accept no extra parameters or accept 1 extra (the extra being who to search for in the database), however when accepting none the call to the SQLite file only sometimes fails (has about I'd say 65-75% success rate) but when providing the extra parameter, the success rate increases to about 98+%.
Here's some useful information about the program:
The overall function's parameters are: channel, tags, message, self
tags is a JSON object that looks like this (important parts + some extra for visual help):
{
...,
flags: null,
'user-type': null,
username: 'user001',
'message-type': 'chat'
}
myMessage is an array (or at least I believe it to be an array and not just a generic object) and in this specific case for !time there are really only 2 options for it:
['!time'] OR ['!time', 'user001']
I create myMessage by simply doing: const myMessage = message.toString().split(' ');
Side note: to handle any non-English characters (and invisible characters), I set message as follows (and then I do the creation of myMessage): message = message.replace(/[^ -\~]/g, '');
For the bot to decide what response to return to the user (i.e. figure out which command was requested), I've simply created a switch-case statement like this:
switch(myMessage[0].toLowerCase()) {
case '!time':
}
My SQLite database is very simple in terms of structure and it's "CREATE TABLE" looks like this (the index was auto-added by SQLite and I didn't explicitly add it in):
CREATE TABLE "users" (
"index" INTEGER,
"Name" TEXT,
"Time" REAL
);
As I stated at the beginning the issue I'm having is inconsistent calls/returned data from the database. The actual code for the !time case statement is as follows:
const db = new sqlite3.Database('./users.db');
var sql = `SELECT time FROM users WHERE name = ?`;
if(myMessage[1] === undefined) { //search the database for the user that called the command
var username = tags.username;
db.get(sql, username.toLowerCase(), (err, row) => {
if(row === undefined) {
client.say(channel, `@${tags.username} You haven't spent enough time here`);
else {
//do some math calculations to get hours and minutes
client.say(channel, `@${tags.username} You have spent ${hours} hours and ${minutes} minutes in the stream`);
}
});
db.close();
}
else { //search for the other user that is requested
db.get(sql, myMessage[1].toLowerCase(), (err, row) => {
if(row === undefined)
client.say(channel, `@${tags.username}, ${myMessage[1]} hasn't spent enough time here`);
else {
//do the same math calculations as before
client.say(channel, `@${tags.username}, ${myMessage[1]} has spent ${hours} hours and ${minutes} minutes in the stream`);
}
});
db.close();
}
So the issue is that when just typing !time, the myMessage array results in the first one listed above and that is only succeeding (i.e. row is NOT undefined) about 65-75% of the time. Yet when typing something like !time user001 the myMessage array results in the second one listed above and that succeeds almost every time, if not every time.
Other variations of the db.get function that I've tried are:
db.get("SELECT time FROM users WHERE name = $name", {$name: tags.username (or myMessage[1].toLowerCase()}, (err, row) => {...})
db.get(sql, [tags.username (or username.toLowerCase()) ], (err, row) => {...})
Overall, I'm not sure what could be causing the db.get to not every time get the right user especially when I can use the second search and find them immediately or go and actually look into the file and see them there. I'm hoping someone can guide me in the right direction as to how I can fix this (I hope this post isn't way too long).