def getBingoFromCardbox (userid, cardbox=0): """ Returns a list of alphagrams that are due, length 7 or greater """ with lite.connect(getDBFile(userid)) as con: cur = con.cursor() cur.execute("select question from questions where cardbox is not null and length(question) >= 7 and difficulty in (-1,0,2) order by case cardbox when ? then -2 else difficulty end, cardbox, next_scheduled limit 1", (cardbox,)) result = cur.fetchone() if result is not None: return result[0] cur.execute("select question from next_added where length(question) >= 7 order by timeStamp limit 1") result = cur.fetchone() if result is not None: addWord(result[0], cur) return result[0] # need to get from study order studyOrderIndex = getPrefs("studyOrderIndex", userid) cur.execute("select question from questions where cardbox is not null and length(question) >= 7") allCardboxBingos = [ x[0] for x in cur.fetchall() ] with xs.getMysqlCon() as mysqlcon: mysqlcon.execute("select alphagram from studyOrder where length(alphagram) >= 7 and studyOrderIndex > %s order by studyOrderIndex", studyOrderIndex) for row in mysqlcon.fetchall(): if row[0] not in allCardboxBingos: addWord(row[0], cur) return row[0] return None
def setPrefs2(prefName, userid, prefValue): with xs.getMysqlCon() as con: command = "update user_prefs set " + prefName + " = %s where userid = %s" try: con.execute(command, (prefValue, userid)) except: return "Error updating user_prefs" return "success"
def isAlphagramValid(alpha): chCommand = "select count(*) from words where alphagram = %s" with xs.getMysqlCon() as con: if con is not None: con.execute(chCommand, (alpha,)) return con.fetchone()[0] > 0 else: return False return False
def getDef (word) : with xs.getMysqlCon() as con: if con is None: return " " command = "select definition from words where word = %s" con.execute(command, word) try: return con.fetchone()[0] except: return " "
def getAnagrams (alpha) : ''' Takes in an alphagram and returns a list of words ''' x = [] with xs.getMysqlCon() as con: if con is not None: con.execute("select word from words where alphagram = '{0}'".format(alpha)) for row in con.fetchall(): x.append(row[0]) return x
def getHooks (word) : ''' Takes in a word Returns a tuple (front hooks, word, back hooks) ''' with xs.getMysqlCon() as con: if con is not None: command = "select front_hooks, word, back_hooks from words where word = %s" con.execute(command, word) return con.fetchone() else: return (None, None, None)
def getDots (word) : ''' takes in a word, returns a list of two booleans does the word lose the front / back letter and still make a word? ''' with xs.getMysqlCon() as con: if con is not None: command = "select count(*) from words where word = %s" con.execute(command, word[1:]) numFront = con.fetchone()[0] con.execute(command, word[:-1]) numBack = con.fetchone()[0] return [numFront > 0, numBack > 0]
def updateActive (userid, timestamp=None) : if timestamp is None: timestamp = int(time.time()) with xs.getMysqlCon() as con: if con is None: result["status"] = "Chat Database Connection Failed" else: try: command = "update login set last_active = %s where userid = %s" if (userid != 0): # 0 is the system user con.execute(command, (timestamp, userid)) except mysql.Error, e: result["status"] = "MySQL error %d %s " % (e.args[0], e.args[1])
def getAllPrefs(userid): with xs.getMysqlCon() as con: command = "select studyOrderIndex, closet, newWordsAtOnce, reschedHrs, showNumSolutions, cb0max, showHints, schedVersion from user_prefs where userid = %s" con.execute(command, userid) row = con.fetchone() result = {} result["studyOrderIndex"] = row[0] result["closet"] = row[1] result["newWordsAtOnce"] = row[2] result["reschedHrs"] = row[3] result["showNumSolutions"] = row[4] result["cb0max"] = row[5] result["showHints"] = row[6] result["schedVersion"] = row[7] return result
def appendChatToResult(line): temp = line.split(',') userid = temp[0] timeStamp = temp[1] message = ','.join(temp[2:]).strip() expire = not message with xs.getMysqlCon() as con: if con is None: pass else: con.execute("select photo, name from login where userid = %s", userid) row = con.fetchone() return {"chatDate": int(timeStamp), "photo": row[0], "name": row[1], "chatText": message, "chatUser": userid, "expire": expire}
def post(userid, message, chatTime=None, expire=False): # chatTime is in milliseconds - epoch*1000 # message is unicode result = {} now = int(time.time()) if int(userid) > 10: ua.updateActive(userid) AUTOLOGOFF = .5 # in hours logoffTime = now - (3600 * AUTOLOGOFF) if chatTime is None: chatTime = now * 1000 # this is a little kludgy # bad data spooled to the chat file hoses everything if userid is None: pass result["status"] = "Chat Missing Userid" elif not expire and message is None: result["status"] = "Chat Missing Message" else: try: with xs.getMysqlCon(useUnicode=True) as con: if expire: command = "delete from chat where userid = %s and timeStamp = %s" con.execute(command, (userid, chatTime)) msg = userid + u',' + unicode(chatTime) + u',' + u'\n' result["msg"] = msg else: command = 'insert into chat (userid, timeStamp, message) values (%s, %s, %s)' con.execute(command, (userid.encode('utf8'), chatTime, message.encode('utf8'))) msg = userid + u',' + unicode( chatTime) + u',' + message + u'\n' result["msg"] = msg command = "select userid from login where last_active > %s" con.execute(command, [logoffTime]) for row in con.fetchall(): filename = os.path.join('chats', row[0] + '.chat') with open(filename, 'a') as f: f.write(msg.encode('utf8')) result["status"] = "success" except mysql.Error, e: result["status"] = "MySQL error %d %s " % (e.args[0], e.args[1]) except:
def getFromStudyOrder (numNeeded, userid, cur): result = [ ] studyOrderIndex = getPrefs("studyOrderIndex", userid) if numNeeded < 1: return result with xs.getMysqlCon() as mysqlcon: if mysqlcon is None: return result cur.execute("select question from questions where next_scheduled is not null union all select question from next_added") allQuestions = set([x[0] for x in cur.fetchall()]) while len(result) < numNeeded: mysqlcon.execute("select alphagram, studyOrderIndex from studyOrder where studyOrderIndex between %s and %s order by studyOrderIndex", (studyOrderIndex, studyOrderIndex + 100)) allStudyOrder = [row for row in mysqlcon.fetchall() if row[0] not in allQuestions] for row in allStudyOrder: result.append(row[0]) studyOrderIndex = row[1]+1 if len(result) >= numNeeded: break if len(allStudyOrder) == 0: studyOrderIndex = studyOrderIndex + 101 # allStudyOrder = mysqlcon.fetchall() # studyOrder = list(set([x[0] for x in allStudyOrder]) - set(allQuestions)) # try: # studyOrderIndex = min([x[1] for x in allStudyOrder if x[0] in studyOrder]) # except: # studyOrderIndex = 0 # command = "select alphagram from studyOrder where studyOrderIndex = %s" # while len(result) < numNeeded: # mysqlcon.execute(command, studyOrderIndex) # a = mysqlcon.fetchone() # if a is None: # return result # if a[0] in allQuestions: # pass # else: # result.append(a[0]) # studyOrderIndex = studyOrderIndex + 1 setPrefs(userid=userid, studyOrderIndex=studyOrderIndex) return result
def setPrefs (userid, prefName = None, prefValue = None, studyOrderIndex = None, closet = None, newWordsAtOnce = None, reschedHrs = None, showNumSolutions = None): # TODO: check to be sure the inputs are correct data type, etc with xs.getMysqlCon() as con: try: if con is None: return "Error: DB Conn Failed Updating user_prefs" if prefName is not None: command = "update user_prefs set %s = %s where userid = %s" command.execute(command, (prefName, prefValue, userid)) else: command = "select studyOrderIndex, closet, newWordsAtOnce, reschedHrs, showNumSolutions from user_prefs where userid = %s" con.execute(command, userid) newPrefs = list(con.fetchone()) if studyOrderIndex is not None: newPrefs[0] = studyOrderIndex if closet is not None: newPrefs[1] = closet if newWordsAtOnce is not None: newPrefs[2] = newWordsAtOnce if reschedHrs is not None: newPrefs[3] = reschedHrs if showNumSolutions is not None: newPrefs[4] = showNumSolutions command = "update user_prefs set studyOrderIndex = %s, closet = %s, newWordsAtOnce = %s, reschedHrs = %s, showNumSolutions = %s where userid = %s" con.execute(command, tuple(newPrefs) + (userid,)) return "Success updating user preferences" except mysql.Error, e: return "MySQL error updating user_prefs %d %s" % (e.args[0], e.args[1])
def getPrefs (prefName, userid): with xs.getMysqlCon() as con: command = "select " + prefName + " from user_prefs where userid = %s" con.execute(command, userid) return con.fetchone()[0]
import xerafinChat as xchat import time result = {"personal": 0, "daily": {"score": 0, "userid": 0}} error = {"status": "success"} try: params = json.load(sys.stdin) userid = params["userid"] score = params["score"] try: gameover = params["gameOver"] except: gameover = False with xs.getMysqlCon() as con: con.execute("select score from invaders_personal where userid = %s", userid) for row in con.fetchall(): result["personal"] = row[0] con.execute("select score, userid from invaders_daily where dateStamp = curdate()") for row in con.fetchall(): result["daily"]["score"] = row[0] result["daily"]["userid"] = row[1] if score > result["personal"]: result["personal"] = score con.execute("update invaders_personal set score = %s where userid = %s", (score, userid)) if score >= result["daily"]["score"]: result["daily"] = {"score": score, "userid": userid} con.execute("update invaders_daily set score = %s, userid = %s where dateStamp = curdate()", (score, userid)) if gameover and int(score) > 0: command = "select name from login where userid = %s"
import xerafinSetup as xs import time import os, sys import math #print "Content-type: text/html\n\n" #print "<html><head>" #print "<title>Cron Test</title>" #print "</head><body>" now = int(time.time()) try: with xs.getMysqlCon(True) as con: con.execute("select count(*), sum(questionsAnswered) from leaderboard where dateStamp = curdate() - interval 1 day group by dateStamp") row = con.fetchone() users = row[0] questions = row[1] # Add daily summary to lb_summary command = "insert into lb_summary (period, dateStamp, questionsAnswered, numUsers) values (%s, CURDATE() - interval 1 day, %s, %s)" con.execute(command, ('DAY', questions, users)) try: if time.strftime("%A") == "Monday": command = "insert into lb_summary(period, dateStamp, questionsAnswered, numUsers) select 'WEEK', min(dateStamp), sum(questionsAnswered), count(distinct userid) from leaderboard where DATE_FORMAT(dateStamp, '%Y%u') = DATE_FORMAT(curdate() - interval 1 day, '%Y%u') group by DATE_FORMAT(dateStamp, '%Y%u')" con.execute(command) except: pass