dickserv

- irc bot with many useful commands
git clone git://git.acid.vegas/dickserv.git
database.py (2955B)
      1 #!/usr/bin/env python
      2 # DickServ IRC Bot - Developed by acidvegas in Python (https://acid.vegas/dickserv)
      3 # database.py
      4 
      5 import os
      6 import sqlite3
      7 
      8 # Globals
      9 db  = sqlite3.connect(os.path.join('data', 'bot.db'), check_same_thread=False)
     10 sql = db.cursor()
     11 
     12 def check():
     13 	tables = sql.execute('SELECT name FROM sqlite_master WHERE type=\'table\'').fetchall()
     14 	if not len(tables):
     15 		sql.execute('CREATE TABLE IGNORE (IDENT TEXT NOT NULL);')
     16 		sql.execute('CREATE TABLE SETTINGS (SETTING TEXT NOT NULL, VALUE INTEGER NOT NULL);')
     17 		sql.execute('INSERT INTO SETTINGS (SETTING,VALUE) VALUES (?, ?)', ('max_results',  5))
     18 		sql.execute('INSERT INTO SETTINGS (SETTING,VALUE) VALUES (?, ?)', ('max_todo',   100))
     19 		sql.execute('INSERT INTO SETTINGS (SETTING,VALUE) VALUES (?, ?)', ('max_todo_per', 5))
     20 		sql.execute('INSERT INTO SETTINGS (SETTING,VALUE) VALUES (?, ?)', ('todo_expire',  7))
     21 		sql.execute('CREATE TABLE TODO (DATE TEXT NOT NULL, IDENT TEXT NOT NULL, DATA TEXT NOT NULL);')
     22 		db.commit()
     23 
     24 class Ignore:
     25 	def add(ident):
     26 		sql.execute('INSERT INTO IGNORE (IDENT) VALUES (?)', (ident,))
     27 		db.commit()
     28 
     29 	def idents():
     30 		return list(item[0] for item in sql.execute('SELECT IDENT FROM IGNORE ORDER BY IDENT ASC').fetchall())
     31 
     32 	def remove(ident):
     33 		sql.execute('DELETE FROM IGNORE WHERE IDENT=?', (ident,))
     34 		db.commit()
     35 
     36 	def reset():
     37 		sql.execute('DROP TABLE IGNORE')
     38 		sql.execute('CREATE TABLE IGNORE (IDENT TEXT NOT NULL);')
     39 		db.commit()
     40 
     41 class Settings:
     42 	def get(setting):
     43 		return sql.execute('SELECT VALUE FROM SETTINGS WHERE SETTING=?', (setting,)).fetchone()[0]
     44 
     45 	def read():
     46 		return sql.execute('SELECT SETTING,VALUE FROM SETTINGS ORDER BY SETTING ASC').fetchall()
     47 
     48 	def settings():
     49 		return list(item[0] for item in sql.execute('SELECT SETTING FROM SETTINGS').fetchall())
     50 
     51 	def update(setting, value):
     52 		sql.execute('UPDATE SETTINGS SET VALUE=? WHERE SETTING=?', (value, setting))
     53 		db.commit()
     54 
     55 class Todo:
     56 	def add(date, ident, data):
     57 		sql.execute('INSERT INTO TODO (DATE,IDENT,DATA) VALUES (?,?,?)', (date, ident, data))
     58 		db.commit()
     59 
     60 	def expire_check():
     61 		todos = set(list(item[0] for item in sql.execute('SELECT DATE FROM TODO').fetchall()))
     62 		for date in todos:
     63 			if functions.timespan(date) > Settings.get('todo_expire'):
     64 				sql.execute('DELETE FROM TODO WHERE DATE=?', (date,))
     65 				db.commit()
     66 
     67 	def idents():
     68 		return list(item[0] for item in sql.execute('SELECT IDENT FROM TODO').fetchall())
     69 
     70 	def read(ident=None):
     71 		if ident:
     72 			return list(item[0] for item in sql.execute('SELECT DATA FROM TODO WHERE IDENT=?', (ident,)).fetchall())
     73 		else:
     74 			return sql.execute('SELECT DATE,IDENT,DATA FROM TODO ORDER BY DATE ASC, IDENT ASC, DATA ASC').fetchall()
     75 
     76 	def remove(ident, data):
     77 		sql.execute('DELETE FROM TODO WHERE IDENT=? AND DATA=?', (ident, data))
     78 		db.commit()
     79 
     80 	def reset():
     81 		sql.execute('DROP TABLE TODO')
     82 		sql.execute('CREATE TABLE TODO (DATE TEXT NOT NULL, IDENT TEXT NOT NULL, DATA TEXT NOT NULL);')
     83 		db.commit()