def listCredentials():
""" query all credentials from database. within close connection.
:return: resultSet array
"""
sql = 'SELECT mail,tel,credential FROM authorization'
query = AppQuery()
try:
return query.query(sql)
finally:
query.close()
def listAccounts():
""" query all accounts from database. within close connection.
:return: resultSet tuple
"""
sql = 'SELECT mail,create_time FROM authorization'
query = AppQuery()
results = []
try:
for row in query.query(sql):
results.append('%-36s %s' % (row[0], row[1]))
finally:
query.close()
return results
def analyseAndPersistMessages(mail, messages):
""" save messages from api to database.last_msg. within close connection.
And take unread messages that not noticed over N-times during T-hours
1. delete messages if old records after T-hours(expired).
2. delete messages if old records not in this messages list(already read).
3. insert this messages with current time(local timezone). So all records is current unread messages.
4. total count of the records from sub query if records count <= N in a group by msg_id.
:param mail:
:param messages:
:return:
"""
args = []
_N, _T = 2, 6
for msg in messages:
args.append((mail, '%s+%s' % (msg['id'], msg['threadId'])))
query = AppQuery()
try:
sql = "DELETE FROM last_msg WHERE mail=? AND julianday(log_time)<julianday('now')-?"
cleared = query.update_auto(sql, mail, _T / 24.0)
log.debug('clear repeated expiry logs count=%d' % cleared)
if args:
sql = "delete from last_msg where mail=? and msg_id not in ('%s')" % "','".join(i[1] for i in args)
cleared = query.update_auto(sql, mail)
log.debug('clear read count=%d' % cleared)
sql = "INSERT INTO last_msg (mail, msg_id, log_time) VALUES (?,?, datetime('now'))"
inserted = query.update_batch(sql, args)
log.debug('insert unread count=%d' % inserted)
sql = 'SELECT count(*) FROM (' \
' SELECT msg_id FROM last_msg ' \
' WHERE mail=? ' \
' GROUP BY msg_id ' \
' HAVING count(*) <= ?' \
' )'
lastUnread = query.query(sql, mail, _N)[0][0]
log.debug('lastUnread count=%d' % lastUnread)
return lastUnread
else:
sql = "DELETE FROM last_msg WHERE mail=?"
cleared = query.update_auto(sql, mail)
log.debug('clear read COUNT=%d' % cleared)
return 0
finally:
query.close()
def updateCredential(mail, credential):
""" update credential of special account. within close connection.
:param mail:
:param credential: credential should be a string.
"""
sql = "UPDATE authorization SET credential=?, refresh_time=datetime('now') WHERE mail=?"
query = AppQuery()
try:
query.update_auto(sql, credential, mail)
finally:
query.close()
def persistAccount(mail, tel, id, credential):
""" save an account/credential to database. within close connection.
:param mail:
:param tel:
:param id:
:param credential: credential should be a string.
"""
sql = 'INSERT INTO authorization (mail, user_id, tel, credential, create_time)' \
' VALUES (?, ?, ?, ?, ?)'
query = AppQuery()
try:
query.update_auto(sql, mail, id, tel, credential, datetime.datetime.now())
finally:
query.close()
def removeAccount(mail):
""" remove account/credential from db. within close connection.
:param mail:
:return: affected count
"""
sql = 'DELETE FROM authorization WHERE mail=?'
query = AppQuery()
try:
affected = query.update_auto(sql, mail)
sql = 'SELECT count(*) FROM authorization'
count = query.query(sql)[0][0]
if count == 0:
CronTask().remove()
return affected
finally:
query.close()
