lib/database.js

   1 var sqlite3 = require("sqlite3")
   2 var logger = require("./logger")
   3 var async = require("async")
   4
   5 module.exports = {
   6     init: function(logger, maxVideoLength) {
   7         var db = new Database(logger, maxVideoLength)
   8         return db
   9     }
  10 }
  11
  12 function Database(logger, maxVideoLength) {
  13     this.db = new sqlite3.Database("./cytubebot.db")
  14     this.logger = logger
  15     this.maxVideoLength = maxVideoLength
  16     this.createTables()
  17 }
  18
  19 // Creates the tables if they do not exist
  20 Database.prototype.createTables = function() {
  21     this.db.serialize()
  22     this.db.run("CREATE TABLE IF NOT EXISTS users(uname TEXT, blacklisted TEXT, block TEXT, primary key(uname))")
  23     this.db.run("CREATE TABLE IF NOT EXISTS chat(timestamp INTEGER, username TEXT, msg TEXT, channel TEXT)")
  24     this.db.run("CREATE TABLE IF NOT EXISTS " +
  25         "videos(type TEXT, id TEXT, duration_ms INTEGER, title TEXT, flags INTEGER, primary key(type, id))")
  26     this.db.run("CREATE TABLE IF NOT EXISTS video_stats(type TEXT, id TEXT, uname TEXT)")
  27     this.db.run("CREATE TABLE IF NOT EXISTS user_count(timestamp INTEGER, count INTEGER, primary key(timestamp, count))")
  28     this.db.run("CREATE TABLE IF NOT EXISTS version(key TEXT, value TEXT, PRIMARY KEY(key))")
  29
  30     this.updateTables()
  31 };
  32
  33 // Updates the tables as needed
  34 Database.prototype.updateTables = function() {
  35     var self = this
  36     this.getVersion(function(version) {
  37         if (!version) {
  38             var update = self.db.prepare("INSERT INTO version(key, value) VALUES (?, ?)", ['dbversion', '1'])
  39             update.run(function() {
  40                 self.db.run("ALTER TABLE users ADD rank INTEGER")
  41                 self.db.parallelize()
  42             })
  43         }
  44     })
  45 };
  46
  47 // Sets a flag on a video
  48 // type - The video type eg. "yt"
  49 // id - The ID of the video
  50 // flags - The flag, should be 1
  51 // title - Title of the video
  52 Database.prototype.flagVideo = function(type, id, flags, title) {
  53     this.logger.syslog.log("*** Flagging video: " + title + " with flag: " + flags)
  54
  55     var stmt = this.db.prepare("UPDATE videos SET flags = ? WHERE type = ? AND id = ?", [flags, type, id])
  56     stmt.run()
  57
  58     stmt.finalize()
  59 };
  60
  61 // WARNING - This is experimental
  62 // Deletes videos from the database that are like like
  63 // We serialize the database to stop the final getVideosCount from executing
  64 // before the other queries have run
  65 // like - What to match. Example: %skrillex% will delete all videos
  66 // with the word "skrillex" in it
  67 // callback - The callback function, sends a chatMsg with how many videos
  68 // we deleted
  69 Database.prototype.deleteVideos = function(like, callback) {
  70     var db = this
  71     this.logger.syslog.log("*** Deleting videos where title like " + like)
  72     var before = 0
  73     var after = 0
  74     var videoIds = {}
  75
  76     var getAfter = function() {
  77         db.getVideosCount(function(num) {
  78             after = num
  79             callback(before - after)
  80         })
  81     }
  82
  83     var deleteVideos = function() {
  84         for (var i = 0; i < videoIds.length; i++) {
  85             var stmt1 = db.db.prepare("DELETE FROM videos WHERE id = ? " +
  86                 "AND type = ?", [videoIds[i]["id"], videoIds[i]["type"]])
  87             var stmt2 = db.db.prepare("DELETE FROM video_stats WHERE id = ? AND type = ?", [videoIds[i]["id"], videoIds[i]["type"]])
  88
  89             stmt1.run()
  90             stmt2.run()
  91         }
  92         getAfter()
  93     }
  94
  95     var getVideoIds = function() {
  96         db.db.all("SELECT id, type FROM videos WHERE title LIKE ? AND flags = 0", (like), function(err, rows) {
  97             if (err) {
  98                 return
  99             }
 100             videoIds = rows
 101             deleteVideos()
 102         })
 103     }
 104
 105     var start = function() {
 106         db.getVideosCount(function(num) {
 107             before = num
 108             getVideoIds()
 109         })
 110     }
 111
 112     // Lets get on the ride
 113     this.db.serialize(start)
 114 };
 115
 116 // Inserts a chatMsg into the chat table
 117 // msg - The message that we are inserting
 118 // time - The timestamp of the message
 119 // nick - The user who said it
 120 // room - The room in which it was said
 121 Database.prototype.insertChat = function(msg, time, nick, room) {
 122     var stmt = this.db.prepare("INSERT INTO chat VALUES(?, ?, ?, ?)", [time, nick, msg, room])
 123     stmt.run()
 124
 125     stmt.finalize()
 126 };
 127
 128 // Inserts a video into the database
 129 // site - The type of video eg. "yt"
 130 // vid - The ID of the video
 131 // title - The title of the video
 132 // dur - The duration of the video
 133 // nick - The user who added the video
 134 Database.prototype.insertVideo = function(site, vid, title, dur, nick) {
 135     this.logger.syslog.log("*** Inserting: " + title + " into the database")
 136
 137     var stmt1 = this.db.prepare("INSERT OR IGNORE INTO videos VALUES(?, ?, ?, ?, ?)", [site, vid, dur * 1000, title, 0])
 138     var stmt2 = this.db.prepare("INSERT INTO video_stats VALUES(?, ?, ?)", [site, vid, nick])
 139
 140     stmt1.run()
 141     stmt1.finalize()
 142
 143     stmt2.run()
 144     stmt2.finalize()
 145 };
 146
 147 // Inserts a user into the user table
 148 // username - The user we are adding
 149 // rank - The users rank
 150 Database.prototype.insertUser = function(username, rank) {
 151     if (!username) {
 152         return
 153     }
 154
 155     var stmt = this.db.prepare("INSERT OR IGNORE INTO users VALUES (?, 'false', 'false', ?)", [username, rank])
 156     stmt.run()
 157
 158     stmt.finalize()
 159 };
 160
 161 // Sets the blacklisted flag on the user table
 162 // username - The user we are setting the flag on
 163 // flag - The flag to set
 164 // callback - The callback function
 165 Database.prototype.insertUserBlacklist = function(username, flag, callback) {
 166     this.logger.syslog.log("Setting blacklist: " + flag + " on user: " + username)
 167
 168     var stmt = this.db.prepare("UPDATE users SET blacklisted = ? WHERE uname = ?", [flag, username])
 169     stmt.run(callback)
 170 };
 171
 172 // Sets the block column of user
 173 // user - The user
 174 // flag - The value
 175 Database.prototype.insertUserBlock = function(username, flag, callback) {
 176     this.logger.syslog.log("*** Setting block: " + flag + " on user: " + username)
 177     var stmt = this.db.prepare("UPDATE users SET block = ? WHERE uname = ?", [flag, username])
 178
 179     stmt.run(callback)
 180 };
 181
 182 // Handles changes to a user's rank
 183 // user - The user whose rank we are changing
 184 // rank - The rank to set
 185 Database.prototype.insertUserRank = function(username, rank) {
 186     var stmt = this.db.prepare("UPDATE users SET rank = ? WHERE uname = ?", [rank, username])
 187     stmt.run()
 188 };
 189
 190 // Inserts the usercount, from a usercount frame
 191 // count - The number of users at timestamp
 192 // timestamp - The time the frame was sent
 193 Database.prototype.insertUsercount = function(count, timestamp) {
 194     var stmt = this.db.prepare("INSERT INTO user_count VALUES(?, ?)", [timestamp, count])
 195     stmt.run()
 196 };
 197
 198 // Gets all the users with a blacklist
 199 // callback - The callback function
 200 Database.prototype.getAllBlacklistedUsers = function(callback) {
 201     var stmt = this.db.prepare("SELECT uname FROM users WHERE blacklisted = '1'")
 202     var users = []
 203
 204     stmt.all(function(err, rows) {
 205         if (rows) {
 206             for (var i = 0; i < rows.length; i++) {
 207                 users.push(rows[i]["uname"])
 208             }
 209             callback(users)
 210         }
 211     })
 212 };
 213
 214 // Gets all the blocked users
 215 Database.prototype.getAllBlockedUsers = function(callback) {
 216     var stmt = this.db.prepare("SELECT uname FROM users WHERE block = '1'")
 217     var users = []
 218
 219     stmt.all(function(err, rows) {
 220         if (rows) {
 221             for (var i = 0; i < rows.length; i++) {
 222                 users.push(rows[i]["uname"])
 223             }
 224             callback(users)
 225         }
 226     })
 227 };
 228
 229 // Gets the usercounts for the average users chart
 230 // Basically ported from naoko
 231 // callback - The callback function
 232 Database.prototype.getAverageUsers = function(callback) {
 233     var select_cls = "SELECT STRFTIME('%s', STRFTIME('%Y-%m-%dT%H:00', timestamp/1000, 'UNIXEPOCH'))*1000 AS timestamp," +
 234         " CAST(ROUND(AVG(count)) AS INTEGER) AS count FROM user_count "
 235     var group_cls = " GROUP BY STRFTIME('%Y%m%d%H', timestamp/1000, 'UNIXEPOCH')"
 236     var sql = select_cls + group_cls
 237
 238     var stmt = this.db.prepare(sql)
 239     var returnData = []
 240
 241     stmt.all(function(err, rows) {
 242         if (err) {
 243             return
 244         }
 245
 246         // Format data for google charts
 247         for (var i = 0; i < rows.length; i++) {
 248             returnData.push([rows[i]["timestamp"], rows[i]["count"]])
 249         }
 250         callback(null, returnData)
 251     })
 252 };
 253
 254 // Gets the amount of messages by each user
 255 // Used for the chat stats chart
 256 // callback - The callback function
 257 Database.prototype.getChatStats = function(callback) {
 258     var select_cls = "SELECT username, count(*) as count FROM chat "
 259     var group_cls = " GROUP BY username ORDER BY count(*) DESC"
 260     var sql = select_cls + group_cls
 261     var stmt = this.db.prepare(sql)
 262     var returnData = []
 263
 264     stmt.all(function(err, rows) {
 265         if (err) {
 266             return
 267         }
 268
 269         // Format data for google charts
 270         for (var i = 0; i < rows.length; i++) {
 271             if (rows[i]["username"] !== "") {
 272                 returnData.push([rows[i]["username"], rows[i]["count"]])
 273             }
 274         }
 275         callback(null, returnData)
 276     })
 277 };
 278
 279 // Does ANALYZE on the database
 280 // Used to get the counts of videos, users, and chat
 281 // callback - The callback function
 282 Database.prototype.getGeneralStats = function(callback) {
 283     var self = this
 284     var stmt = "ANALYZE"
 285     var stmt2 = "SELECT stat FROM sqlite_stat1 WHERE tbl = 'users' OR tbl = 'videos' OR tbl = 'chat'"
 286
 287     this.db.serialize(function() {
 288         self.db.run(stmt)
 289         self.db.all(stmt2, function(err, rows) {
 290             if (rows)
 291                 callback(rows)
 292         })
 293     })
 294 };
 295
 296 // Gets the 10 most popular videos
 297 // Used for the popular videos chart
 298 // callback - The callback function
 299 Database.prototype.getPopularVideos = function(callback) {
 300     var select_cls = "SELECT videos.type, videos.id, videos.title, videos.flags & 1, count(*) AS count FROM videos, video_stats"
 301     var where_cls = " WHERE video_stats.type = videos.type AND video_stats.id = videos.id AND NOT videos.flags & 2 "
 302     var group_cls = " GROUP BY videos.type, videos.id ORDER BY count(*) DESC LIMIT 10"
 303     var sql = select_cls + where_cls + group_cls
 304
 305     var stmt = this.db.prepare(sql)
 306
 307     var returnData = []
 308
 309     stmt.all(function(err, rows) {
 310         if (err) {
 311             return
 312         }
 313
 314         // Format data for google charts
 315         for (var i = 0; i < rows.length; i++) {
 316             returnData.push([rows[i]["type"], rows[i]["id"], rows[i]["title"],
 317                 rows[i]["flags"], rows[i]["count"]
 318             ])
 319         }
 320         callback(null, returnData)
 321     })
 322 };
 323
 324 // Gets a chat message
 325 // If nick is given, it will select a quote from that user
 326 // If no nick is given, it will select a random quote
 327 // nick - The username we are getting a quote for
 328 // callback - The callback function
 329 Database.prototype.getQuote = function(nick, callback) {
 330     nick = nick.split(" ")[0]
 331     var stmt = {}
 332
 333     if (nick) {
 334         stmt = this.db.prepare("SELECT username, msg, timestamp FROM chat WHERE " +
 335             "username = ? COLLATE NOCASE ORDER BY RANDOM() LIMIT 1", [nick])
 336
 337         stmt.get(function(err, row) {
 338             if (row)
 339                 return callback(row)
 340         })
 341         return callback(0)
 342     }
 343
 344     stmt = "SELECT username, msg, timestamp FROM chat WHERE msg NOT LIKE '/me%' " +
 345         "AND msg NOT LIKE '$%' ORDER BY RANDOM() LIMIT 1"
 346     this.db.get(stmt, function(err, row) {
 347         if (row)
 348             callback(row)
 349     })
 350
 351 };
 352
 353 // Fetches all of the stats required by the stats page
 354 // Functions are chained together with the last function
 355 // giving the callback the final returnData object
 356 // room - The room the bot is currently in
 357 // callback - The callback function
 358 Database.prototype.getStats = function(room, callback) {
 359     var self = this
 360
 361     // Lets go on another ride
 362     async.parallel({
 363         userVideoStats: self.getVideoStats.bind(self),
 364         userChatStats: self.getChatStats.bind(self),
 365         popularVideos: self.getPopularVideos.bind(self),
 366         averageUsers: self.getAverageUsers.bind(self)
 367     }, function(err, results) {
 368         if (err) {
 369             return
 370         }
 371
 372         results["room"] = room
 373         callback(results)
 374     })
 375 };
 376
 377 // Checks whether a user is blacklisted
 378 // username - The user we are checking
 379 // callback - The callback function
 380 Database.prototype.getUserBlacklist = function(username, callback) {
 381     var stmt = this.db.prepare("SELECT blacklisted FROM users WHERE uname = ?", [username])
 382
 383     stmt.get(function(err, row) {
 384         if (typeof row !== "undefined") {
 385             callback(row["blacklisted"])
 386         }
 387     })
 388 };
 389
 390 // Selects the autodelete column for user
 391 // username - The user we are looking up
 392 // callback - Callback function
 393 Database.prototype.getUserBlock = function(username, callback) {
 394     var stmt = this.db.prepare("SELECT block FROM users WHERE uname = ?", [username])
 395
 396     stmt.get(function(err, row) {
 397         if (typeof row !== "undefined") {
 398             callback(row["block"])
 399         }
 400     })
 401 };
 402
 403 // Gets a user's rank
 404 // Callback - callback function
 405 Database.prototype.getUserRank = function(username, callback) {
 406     var stmt = this.db.prepare("SELECT rank FROM users WHERE uname = ?", [username])
 407
 408     stmt.get(function(err, row) {
 409         if (typeof row !== "undefined") {
 410             callback(row["rank"])
 411         }
 412     })
 413 };
 414
 415 // Gets the database version
 416 Database.prototype.getVersion = function(callback) {
 417     var stmt = this.db.prepare("SELECT value FROM version WHERE key = 'dbversion'")
 418
 419     stmt.get(function(err, row) {
 420         if (row === undefined) {
 421             callback(null)
 422         } else {
 423             callback(row)
 424         }
 425     })
 426 };
 427
 428 // Used by the addRandom() method
 429 // Fetches num random videos, if num is zero it fetches 1 video
 430 // Limits videos to those under 10 minutes and whose type is yt, dm, or vm
 431 // num - The number of videos we are getting
 432 // callback - The callback function
 433 Database.prototype.getVideos = function(num, callback) {
 434     if (!num) {
 435         num = 1
 436     }
 437
 438     var stmt = this.db.prepare("SELECT type, id, duration_ms, title FROM videos " +
 439         "WHERE flags = 0 AND duration_ms < ? AND (type = 'yt' OR type = 'dm' OR type = 'vm') " +
 440         "ORDER BY RANDOM() LIMIT ?", [this.maxVideoLength, num])
 441
 442     stmt.all(function(err, rows) {
 443         callback(rows)
 444     })
 445 };
 446
 447 Database.prototype.getVideosCountForClean = function(callback) {
 448     var self = this
 449
 450     this.db.get("SELECT count(*) AS count FROM videos WHERE flags = 0 "
 451         + "AND type = 'yt' AND duration_ms < ?", [this.maxVideoLength], function(err, row) {
 452         if (err) {
 453             return self.logger.errlog.log(err)
 454         }
 455
 456         callback(row["count"])
 457     })
 458 };
 459
 460 // Gets the number of videos in the database
 461 // callback - The callback function
 462 Database.prototype.getVideosCount = function(callback) {
 463     var self = this
 464
 465     this.db.get("SELECT count(*) AS count FROM videos", function(err, row) {
 466         if (err) {
 467             return self.logger.errlog.log(err)
 468         }
 469
 470         callback(row["count"])
 471     })
 472 };
 473
 474 // Gets the number of videos added by each user
 475 // Used by the video by user chart
 476 // callback - The callback function
 477 Database.prototype.getVideoStats = function(callback) {
 478     var select_cls = "SELECT uname, count(*) AS count FROM video_stats vs, videos v "
 479     var where_cls = " WHERE vs.type = v.type AND vs.id = v.id AND NOT v.flags & 2 "
 480     var group_cls = " GROUP BY uname ORDER BY count(*) DESC"
 481     var sql = select_cls + where_cls + group_cls
 482     var stmt = this.db.prepare(sql)
 483     var returnData = []
 484
 485     stmt.all(function(err, rows) {
 486         if (err) {
 487             return
 488         }
 489
 490         // Format data for google charts
 491         for (var i = 0; i < rows.length; i++) {
 492             if (rows[i]["uname"] !== "") {
 493                 returnData.push([rows[i]["uname"], rows[i]["count"]])
 494             }
 495         }
 496
 497         callback(null, returnData)
 498     })
 499 };
 500
 501 // Gets the flag of a video
 502 // type - The type of the video we are looking up
 503 // id - The ID of the video we are looking up
 504 // callback - The callback function
 505 Database.prototype.getVideoFlag = function(type, id, callback) {
 506     var stmt = this.db.prepare("SELECT flags FROM videos videos WHERE type = ? AND id = ?", [type, id])
 507
 508     stmt.get(function(err, row) {
 509         if (row) {
 510             callback(row)
 511         } else {
 512             callback(0)
 513         }
 514     })
 515 };