def compileRegex(tableName, regexList): regex = '' try: with Sqlite3Client(os.path.join(SQLITE_DIR, 'autoreply.db')) as s3c: for qa in s3c.data_source('select * from %s'%tableName): regex = qa[0] regexList.append((re.compile(qa[0]), qa[1])) except: raise Exception('Error occured when loading regex table %s: %s is not a correct regex'%( tableName, regex))
def detectFiles(tableName): if not os.path.exists(FILE_DIR): os.makedirs(FILE_DIR) fileName = '' try: with Sqlite3Client(os.path.join(SQLITE_DIR, 'autoreply.db')) as s3c: for qa in s3c.data_source('select * from %s'%tableName): if qa[1][:5] == '@fil@': fileName = qa[1][5:] with open(os.path.join(FILE_DIR, fileName)): pass except: raise Exception('Error occured when loading "%s" in table %s, it should be in storage/upload'%(fileName, tableName))
def change_msg_format(self, groupName, userName, msg): if msg[:5] in ['@fil@', '@img@']: return msg with Sqlite3Client(os.path.join(SQL_DIR, SQL_NAME)) as s3c: nickName = s3c.query( 'select * from memberlist where groupname=? and username=?', [groupName, userName])[0][2] # return ('%s:<br/>@%s\342\200\205%s'%(userName.encode('utf8'), # nickName.encode('utf8'), msg.encode('utf8'))).decode('utf8') return ( '@%s\342\200\205%s' % (nickName.encode('utf8'), msg.encode('utf8'))).decode('utf8')
def update_group(self, client, groupName): memberList = client.get_batch_contract(groupName) memberDict = {member['UserName']: member for member in memberList} with Sqlite3Client(os.path.join(SQL_DIR, SQL_NAME)) as s3c: q = s3c.query('select * from memberlist where groupname=?', (groupName, )) for member in q: del memberDict[member[1]] for userName, member in memberDict.items(): s3c.insert_data( 'memberlist', (groupName, member['UserName'], member['NickName']))
def vote(msg, storageClass, userName): if not VOTE_KEYWORD in msg: return False # key in sqlite3->other: vote # value in sqlite3->other: 0 for voted, 1 for not voted # values should be strings voteList = load_vote_list() if voteList is None: return False status = get_status(storageClass, userName) for voteItem in voteList: # load vote details if not voteItem['name'] in msg: continue if voteItem.has_key('maxvote'): maxVote = voteItem['maxvote'] else: maxVote = 1 # load user details if status.has_key('vote@' + voteItem['name']): currentVote = int(status['vote@' + voteItem['name']]) if currentVote <= 0: return u'你已经在“%s”中完成了投票' % voteItem['name'] else: currentVote = maxVote # check vote candidate = None for c in voteItem['candidates']: # bug will occur if one candidates's name contains another's if c in msg: candidate = c break if candidate is None: return u'投票选项包括: ' + ', '.join(voteItem['candidates']) # update vote database with Sqlite3Client(storageClass.sqlDir) as s3c: s3c.execute( 'create table if not exists vote (name text, candidate text, PYQuanPin text, time text)' ) PYQuanPin = storageClass.find_PYQuanPin(userName) s3c.insert_data('vote', items=[ voteItem['name'], candidate, PYQuanPin, int(time.time()) ]) # update user status status['vote@' + voteItem['name']] = str(currentVote - 1) store_status(storageClass, userName, status) return u'已经成功投票给“%s”%s' % (candidate, '' if currentVote == 1 else u',你还可以投%s票' % (currentVote - 1)) return u'目前进行的投票有: ' + ', '.join( [voteItem['name'] for voteItem in voteList])
def update_user(self, PYQuanPin, **kwargs): with Sqlite3Client(self.sqlDir) as s3c: dataInStorage = s3c.query('select count(*) from memberList where PYQuanPin = ?', (PYQuanPin,))[0][0] if dataInStorage == 0: dataDict = {'NickName': '', 'UserName': '', 'Other': ''} else: dataTuple = s3c.query('select * from memberList where PYQuanPin = ?', (PYQuanPin,))[0] dataDict = {'NickName': dataTuple[1], 'UserName': dataTuple[2], 'Other': dataTuple[3]} s3c.execute('delete from memberList where PYQuanPin = ?', (PYQuanPin,)) for key, value in kwargs.items(): dataDict[key] = value try: s3c.insert_data('memberList', items = [PYQuanPin, dataDict['NickName'], dataDict['UserName'], dataDict['Other']]) except: return dataDict return True
def find_nickname(self, u): with Sqlite3Client(self.sqlDir) as s3c: members = s3c.query('select * from memberList where UserName = ?', (u,)) for member in members: return member[1]
def find_user(self, n): with Sqlite3Client(self.sqlDir) as s3c: members = s3c.query('select * from memberList where NickName = ?', (n,)) for member in members: return member[2]
def find_PYQuanPin(self, userName): with Sqlite3Client(self.sqlDir) as s3c: members = s3c.query('select * from memberList where UserName = ?', (userName,)) for member in members: return member[0]
def store_msg(self, userName, msg, fromto): with Sqlite3Client(self.sqlDir) as s3c: s3c.insert_data('message', [int(time.time()), msg, self.find_nickname(userName), fromto]) if fromto == 'from': self.lastInputUserName = userName
def find_msg_list(self, userName, count): with Sqlite3Client(self.sqlDir) as s3c: r = s3c.query('select * from message where nickname=? order by time desc limit ?', (self.find_nickname(userName), count)) return r
def store_msg(self, content, groupName, userName, fromto): with Sqlite3Client(os.path.join(SQL_DIR, SQL_NAME)) as s3c: s3c.insert_data( 'messages', [groupName, int(time.time()), content, userName, fromto])
def touch_user(self, groupName, userName): with Sqlite3Client(os.path.join(SQL_DIR, SQL_NAME)) as s3c: r = s3c.query( 'select count(*) from memberlist where groupname=? and username=?', [groupName, userName])[0][0] return True if r else False
def clear_table(self): with Sqlite3Client(os.path.join(SQL_DIR, SQL_NAME)) as s3c: s3c.execute('delete from memberlist')
import os, re, time from plugin.Sqlite3Client import Sqlite3Client SQL_DIR = os.path.join('storage', 'grouptalk') SQL_NAME = 'grouptalk.db' if not os.path.exists(SQL_DIR): os.mkdir(SQL_DIR) with Sqlite3Client(os.path.join(SQL_DIR, SQL_NAME)) as s3c: s3c.execute( 'create table if not exists messages (groupname text, time integer, message text, username text, fromto text)' ) s3c.execute( 'create table if not exists memberlist (groupname text, username text, nickname text)' ) class GroupRobot: def __init__(self): pass def clear_table(self): with Sqlite3Client(os.path.join(SQL_DIR, SQL_NAME)) as s3c: s3c.execute('delete from memberlist') def touch_user(self, groupName, userName): with Sqlite3Client(os.path.join(SQL_DIR, SQL_NAME)) as s3c: r = s3c.query( 'select count(*) from memberlist where groupname=? and username=?', [groupName, userName])[0][0] return True if r else False
def search_nickname(self, n): r = [] with Sqlite3Client(self.sqlDir) as s3c: members = s3c.query('select * from memberList where NickName like ?', ('%' + n + '%',)) for member in members: r.append(member[1]) return r
def get_other(self, userName): with Sqlite3Client(self.sqlDir) as s3c: members = s3c.query('select * from memberList where UserName = ?', (userName,)) for member in members: return member[3]
def load_sql_storage(self): self.sqlDir = os.path.join(config.ACC_DIR, '%s.db'%self.nickName) with Sqlite3Client(self.sqlDir) as s3c: s3c.execute('create table if not exists message (time integer, message text, nickname text, fromto text)') s3c.execute('create table if not exists memberList (PYQuanPin text, NickName text, UserName text, Other text)')