Beispiel #1
0
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)
Beispiel #2
0
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
Beispiel #3
0
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
Beispiel #4
0
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
Beispiel #5
0
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
Beispiel #6
0
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
Beispiel #7
0
 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
Beispiel #8
0
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
Beispiel #9
0
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
Beispiel #10
0
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
Beispiel #11
0
 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'))
Beispiel #12
0
    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)
Beispiel #13
0
 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'))
Beispiel #14
0
    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)
Beispiel #15
0
 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)
Beispiel #17
0
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)
Beispiel #19
0
 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")
Beispiel #20
0
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
Beispiel #21
0
 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") 
Beispiel #22
0
    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
Beispiel #23
0
    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)
Beispiel #24
0
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
Beispiel #25
0
    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")
Beispiel #26
0
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
Beispiel #27
0
 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'))
Beispiel #28
0
 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)
Beispiel #29
0
 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)
Beispiel #30
0
 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)
Beispiel #31
0
 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)
Beispiel #32
0
 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)
Beispiel #33
0
 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)
Beispiel #34
0
 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