def delTupleMail(con, name, avatarId, id_mail):
name = util.quote(name, "char")
avatarId = util.quote(avatarId, "char")
query = """
SELECT id_mail_message
FROM imap_mail_message
WHERE id_mail_message IN(
SELECT id_mail_message
FROM imap_box_message
WHERE id_mail_box =(
SELECT id_mail_box
FROM imap_mail_box
WHERE name_mail_box = %s
AND username = %s
)
)
AND id_mail_message = %d
""" % (name, avatarId, id_mail)
cursor = con.cursor()
cursor.execute(query)
results = cursor.fetchone()
results = str(results[0])
results = int(results)
query = """
UPDATE imap_mail_message
SET active = 1
WHERE id_mail_message = %d
""" % results
cursor.execute(query)
def getLastTuple(con, name, avatarId):
nbTuple = nbTupleMail(con, name, avatarId)
if nbTuple == 0:
return None
elif nbTuple == 1:
return getTupleMail(con, name, avatarId, 1)
else:
name = util.quote(name, "char")
avatarId = util.quote(avatarId, "char")
query = """
SELECT id_mail_message
FROM imap_mail_message
WHERE id_mail_message >= ALL(
SELECT id_mail_message
FROM imap_mail_message
WHERE id_mail_message IN(
SELECT id_mail_message
FROM imap_box_message
WHERE id_mail_box =(
SELECT id_mail_box
FROM imap_mail_box
WHERE name_mail_box = %s
AND username = %s
)
)
)
""" % (name, avatarId)
cursor = con.cursor()
cursor.execute(query)
results = cursor.fetchone()
if results:
results = str(results[0])
results = int(results)
return results
def getIdMailMessage(con, name, avatarId, index):
index -= 1
print "index: %d" % index
if index == -1:
return getIdMailMessageLast(con, name, avatarId)
else:
name = util.quote(name, "char")
avatarId = util.quote(avatarId, "char")
query = """
SELECT id_mail_message
FROM imap_mail_message
WHERE id_mail_message in(
SELECT id_mail_message
FROM imap_box_message
WHERE id_mail_box =(
SELECT id_mail_box
FROM imap_mail_box
WHERE name_mail_box = %s
AND username = %s
)
)
LIMIT %d,1
""" % (name, avatarId, index)
cursor = con.cursor()
cursor.execute(query)
results = cursor.fetchone()
results = str(results[0])
results = int(results)
return results
def nbTupleFilter(con, name, avatarId, flag=None):
if flag == None:
return 0
else:
flag = util.quote(flag, "char")
name = util.quote(name, "char")
avatarId = util.quote(avatarId, "char")
query = """
SELECT id_mail_message
FROM imap_message_flag
WHERE id_flag =(
SELECT id_flag
FROM imap_flags
WHERE name = %s
)
AND id_mail_message IN(
SELECT id_mail_message
FROM imap_box_message
WHERE id_mail_box =(
SELECT id_mail_box
FROM imap_mail_box
WHERE name_mail_box = %s
AND username = %s
)
)
""" % (flag, name, avatarId)
cursor = con.cursor()
cursor.execute(query)
results = cursor.fetchone()
if results:
results = str(results[0])
results = int(results)
return results
def getTupleMail(con, name, avatarId, index):
index = index - 1
if index == -1:
return getLastTuple(con, name, avatarId)
name = util.quote(name, "char")
avatarId = util.quote(avatarId, "char")
query = """
SELECT id_mail_message
FROM imap_mail_message
WHERE id_mail_message IN(
SELECT id_mail_message
FROM imap_box_message
WHERE id_mail_box =(
SELECT id_mail_box
FROM imap_mail_box
WHERE name_mail_box = %s
AND username = %s
)
)
LIMIT %d, 1
""" % (name, avatarId, index)
cursor = con.cursor()
cursor.execute(query)
results = cursor.fetchone()
results = str(results[0])
results = int(results)
return results
def getFlagsWithId(con, name, avatarId, idMail):
name = util.quote(name, "char")
avatarId = util.quote(avatarId, "char")
query = """
SELECT name
FROM imap_flags
WHERE id_flag IN(
SELECT id_flag
FROM imap_message_flag
WHERE id_mail_message IN(
SELECT id_mail_message
FROM imap_box_message
WHERE id_mail_box =(
SELECT id_mail_box
FROM imap_mail_box
WHERE name_mail_box = %s
AND username = %s
)
)
AND id_mail_message = %d
)
""" % (name, avatarId, idMail)
cursor = con.cursor()
cursor.execute(query)
results = cursor.fetchall()
retour = []
for flag in results:
flag = "".join(flag)
retour.append(flag)
return retour
def render(self, request):
title = request.args['title'][0]
body = request.args['body'][0]
query = """
Insert into posts (title, body) values (%s, %s)
""" % (dbutil.quote(title, "char"), dbutil.quote(body, "text"))
self.db.runQuery(query).addCallback(self._saved, request).addErrback(
self._saveFailed, request)
return server.NOT_DONE_YET
def getVirtualTab(con, name, avatarId):
#DETAIL
#virtual[1]:
#{
#####'username': '******',
#####'active': '0',
#####'from': 'greenlamp@localhost',
#####'name': 'Inbox',
#####'content': 'ceci est un test de contenu',
#####'to': 'greenlamp@localhost',
#####'date': 'Mon, 13 Feb 2012 13:43:53 +0100 (CET)',
#####'content-type': 'Content-Type: text/html; charset=iso-8859-1',
#####'id': '10',
#####'subject': 'Pour gabriel'
#}
virtual = {}
pos = 1
active = 0
if name.lower() == "trash":
active = 1
name = util.quote(name, "char")
avatarId = util.quote(avatarId, "char")
query = """
SELECT *
FROM imap_mail_message
WHERE id_mail_message IN(
SELECT id_mail_message
FROM imap_box_message
WHERE id_mail_box =(
SELECT id_mail_box
FROM imap_mail_box
WHERE name_mail_box = %s
AND username = %s
)
)
AND active = %d
""" % (name, avatarId, active)
cursor = con.cursor()
cursor.execute(query)
results = cursor.fetchall()
for result in results:
virtual[pos] = {}
virtual[pos]["id"] = "%d" % result[0]
virtual[pos]["from"] = "%s" % result[1]
virtual[pos]["to"] = "%s" % result[2]
virtual[pos]["subject"] = "%s" % result[3]
virtual[pos]["date"] = "%s" % result[4]
virtual[pos]["content-type"] = "%s" % result[5]
virtual[pos]["content"] = "%s" % result[6]
virtual[pos]["active"] = "%d" % result[7]
virtual[pos]["name"] = name[1:-1]
virtual[pos]["username"] = avatarId[1:-1]
pos += 1
return virtual
def getIdMailBox(con, name, avatarId):
name = util.quote(name, "char")
avatarId = util.quote(avatarId, "char")
query = """
SELECT id_mail_box
FROM imap_mail_box
WHERE name_mail_box = %s
AND username = %s
""" % (name, avatarId)
cursor = con.cursor()
cursor.execute(query)
results = cursor.fetchone()
results = str(results[0])
results = int(results)
return results
def createBox(con, avatarId, name):
if not isAlreadyExists(con, name, avatarId):
avatarId = util.quote(avatarId, "char")
name = util.quote(name, "char")
query = """
INSERT INTO imap_mail_box(
username, name_mail_box)
VALUES(%s, %s)""" % (avatarId, name)
cursor = con.cursor()
try:
cursor.execute(query)
except Exception:
print traceback.print_exc(file=sys.stdout)
else:
print "box(%s) déja existante" % name
def element_match(self, element):
"""Compare a given variable exactly to the element's content, not including
leading and trailing whitespace.
"""
return "(%s = %s)" % (
self.varLookup(element.getAttributeNS(None, 'var')),
quote(XML.shallowText(element).strip(), 'varchar'))
def quote_value(self, value, type):
"""Format a value for use in an SQL statement.
@param value: a value to format as data in SQL.
@param type: a key in util.dbTypeMap.
"""
return quote(value, type, string_escaper=self.escape_string)
def element_like(self, element):
"""Compare a given variable to the element's content using SQL's 'LIKE' operator,
not including leading and trailing whitespace. This is case-insensitive, and includes
the '%' wildcard which may be placed at the beginning or end of the string.
"""
return "(%s LIKE %s)" % (self.varLookup(element.getAttributeNS(None, 'var')),
quote(XML.shallowText(element).strip(), 'varchar'))
def quote_value(self, value, type):
"""Format a value for use in an SQL statement.
@param value: a value to format as data in SQL.
@param type: a key in util.dbTypeMap.
"""
return quote(value, type, string_escaper=self.escape_string)
def element_like(self, element):
"""Compare a given variable to the element's content using SQL's 'LIKE' operator,
not including leading and trailing whitespace. This is case-insensitive, and includes
the '%' wildcard which may be placed at the beginning or end of the string.
"""
return "(%s LIKE %s)" % (
self.varLookup(element.getAttributeNS(None, 'var')),
quote(XML.shallowText(element).strip(), 'varchar'))
def testQuoting(self):
for value, typ, expected in [(12, "integer", "12"),
("foo'd", "text", "'foo''d'"),
("\x00abc\\s\xFF", "bytea",
"'\\\\000abc\\\\\\\\s\\377'"),
(12, "text", "'12'"),
(u"123'456", "text", u"'123''456'")]:
self.assertEquals(util.quote(value, typ), expected)
def isAlreadyExists(con, name, avatarId):
avatarId = util.quote(avatarId, "char")
name = util.quote(name, "char")
query = """
SELECT count(*)
FROM imap_mail_box
WHERE name_mail_box = %s
AND username = %s
""" % (name, avatarId)
cursor = con.cursor()
cursor.execute(query)
results = cursor.fetchone()
results = int(str(results[0]))
if results == 0:
return False
else:
return True
def testQuoting(self):
for value, typ, expected in [
(12, "integer", "12"),
("foo'd", "text", "'foo''d'"),
("\x00abc\\s\xFF", "bytea", "'\\\\000abc\\\\\\\\s\\377'"),
(12, "text", "'12'"),
(u"123'456", "text", u"'123''456'")
]:
self.assertEquals(util.quote(value, typ), expected)
def requestAvatar(self, avatarId, mind, *interfaces):
if simplecred.INamedUserAvatar in interfaces:
userQuery = """
select username, firstname, lastname
from user where userid = %s
""" % dbutil.quote(avatarId, "int")
return self.dbconn.runQuery(userQuery).addCallback(
self._gotQueryResults)
else:
raise KeyError("None of the requested interfaces is supported")
def nbTupleMail(con, name, avatarId):
name = util.quote(name, "char")
avatarId = util.quote(avatarId, "char")
query = """
SELECT count(*)
FROM imap_box_message
WHERE id_mail_box IN(
SELECT id_mail_box
FROM imap_mail_box
WHERE name_mail_box = %s
AND username = %s
)
""" % (name, avatarId)
cursor = con.cursor()
cursor.execute(query)
results = cursor.fetchone()
results = str(results[0])
results = int(results)
return results
def requestAvatar(self, avatarId, mind, *interfaces):
if simplecred.INamedUserAvatar in interfaces:
userQuery = """
select username, firstname, lastname
from user where userid = %s
""" % dbutil.quote(avatarId, "int")
return self.dbconn.runQuery(userQuery).addCallback(
self._gotQueryResults)
else:
raise KeyError("None of the requested interfaces is supported")
def _quote(self, fields, fld = None):
quoted_fields = fields
if type(fields) is dict:
quoted_fields = {}
for field in fields:
if self._fields.has_key(field) is True:
quoted_fields[field] = dbutil.quote(fields[field], self._fields.get(field))
else:
log.error('Cannot quote sql param:" %s. It was be ignored!' % field)
elif type(fields) is list:
quoted_fields = []
for fld_value in fields:
if self._fields.has_key(fld) is True:
quoted_fields.append(dbutil.quote(fld_value, self._fields.get(fld)))
else:
log.error('Cannot quote sql param:" %s. It was be ignored!' % fld)
else:
quoted_fields = dbutil.quote(str(fields), self._fields.get(fld))
return quoted_fields
def requestAvatarId(self, credentials):
log.info('DbAuthChecker::requestAvatarId')
log.debug(credentials)
query = """SELECT
id, password
FROM
users
WHERE
email = %s AND password = %s""" % (dbutil.quote(credentials.username, "char"), dbutil.quote(credentials.password, "char"))
log.debug('query: ' + query)
return self.dbconn.runQuery(query).addCallback(self._gotQueryResults, credentials)
def getNameAllBoxes(con, avatarId):
listNameBoxes = []
avatarId = util.quote(avatarId, "char")
query = """
SELECT name_mail_box
FROM imap_mail_box
WHERE username = %s
""" % avatarId
cursor = con.cursor()
cursor.execute(query)
results = cursor.fetchall()
for name in results:
name = "".join(name)
listNameBoxes.append(name)
return listNameBoxes
def requestAvatar(self, avatarId, mind, *interfaces):
log.info('DbRealm::requestAvatar')
log.debug(avatarId)
log.debug(mind)
if IUser in interfaces:
query = """SELECT
*
FROM
users
WHERE id = %s""" % dbutil.quote(avatarId, "int")
return self.dbconn.runQuery(query).addCallback(self._gotQueryResults)
else:
raise KeyError("None of the requested interfaces is supported")
def getIdFlagWithName(con, name):
name = util.quote(name, "char")
query = """
SELECT id_flag
FROM imap_flags
WHERE name = %s
""" % name
cursor = con.cursor()
try:
cursor.execute(query)
except Exception:
print "le flag n'existe pas"
results = cursor.fetchone()
if results:
results = int(str(results[0]))
return results
def element_match(self, element):
"""Compare a given variable exactly to the element's content, not including
leading and trailing whitespace.
"""
return "(%s = %s)" % (self.varLookup(element.getAttributeNS(None, 'var')),
quote(XML.shallowText(element).strip(), 'varchar'))
def fetch_password(self, credentials, callback):
query = "select rowid,password from users where username = %s" % (
dbutil.quote(credentials.username, "char"))
return self._db_conn.runQuery(query).addCallback(callback, credentials)
def requestAvatarId(self, credentials):
query = "select userid, password from user where username = %s" % (
dbutil.quote(credentials.username, "char"))
return self.dbconn.runQuery(query).addCallback(self._gotQueryResults,
credentials)
def fetch_password(self,credentials,callback):
query = "select rowid,password from users where username = %s" % (
dbutil.quote(credentials.username, "char"))
return self._db_conn.runQuery(query).addCallback(
callback, credentials)
def fetch_avatar(self,avatar_id,callback):
query = "select username,fullname from users where rowid = %s" % (
dbutil.quote(avatar_id, "int"))
return self._db_conn.runQuery(query).addCallback(callback)
def fetch_avatar(self, avatar_id, callback):
query = "select username,fullname from users where rowid = %s" % (
dbutil.quote(avatar_id, "int"))
return self._db_conn.runQuery(query).addCallback(callback)
def requestAvatarId(self, credentials):
query = "select userid, password from user where username = %s" % (
dbutil.quote(credentials.username, "char"))
return self.dbconn.runQuery(query).addCallback(
self._gotQueryResults, credentials)
def requestAvatarId(self, credentials):
param = util.quote(credentials.username, "char")
query = "SELECT username, password from imap_users where username = %s" % param
results = self.con.runQuery(query)
results.addCallback(self._gotResult, credentials)
return results
