source: OpenRLabs-Git/deploy/rlabs-docker/web2py-rlabs/gluon/packages/dal/pydal/contrib/imap_adapter.py

main
Last change on this file was 42bd667, checked in by David Fuertes <dfuertes@…>, 4 years ago

Historial Limpio
  • Property mode set to 100755
File size: 43.1 KB
Line 
1# -*- coding: utf-8 -*-
2import datetime
3import re
4import sys
5
6from .._globals import IDENTITY, GLOBAL_LOCKER
7from .._compat import PY2, integer_types, basestring
8from ..connection import ConnectionPool
9from ..objects import Field, Query, Expression
10from ..helpers.classes import SQLALL
11from ..helpers.methods import use_common_filters
12from ..adapters.base import NoSQLAdapter
13
14long = integer_types[-1]
15
16
17class IMAPAdapter(NoSQLAdapter):
18
19    """ IMAP server adapter
20
21    This class is intended as an interface with
22    email IMAP servers to perform simple queries in the
23    web2py DAL query syntax, so email read, search and
24    other related IMAP mail services (as those implemented
25    by brands like Google(r), and Yahoo!(r)
26    can be managed from web2py applications.
27
28    The code uses examples by Yuji Tomita on this post:
29    http://yuji.wordpress.com/2011/06/22/python-imaplib-imap-example-with-gmail/#comment-1137
30    and is based in docs for Python imaplib, python email
31    and email IETF's (i.e. RFC2060 and RFC3501)
32
33    This adapter was tested with a small set of operations with Gmail(r). Other
34    services requests could raise command syntax and response data issues.
35
36    It creates its table and field names "statically",
37    meaning that the developer should leave the table and field
38    definitions to the DAL instance by calling the adapter's
39    .define_tables() method. The tables are defined with the
40    IMAP server mailbox list information.
41
42    .define_tables() returns a dictionary mapping dal tablenames
43    to the server mailbox names with the following structure:
44
45    {<tablename>: str <server mailbox name>}
46
47    Here is a list of supported fields:
48
49    ===========   ============== ===========
50    Field         Type           Description
51    ===========   ============== ===========
52    uid           string
53    answered      boolean        Flag
54    created       date
55    content       list:string    A list of dict text or html parts
56    to            string
57    cc            string
58    bcc           string
59    size          integer        the amount of octets of the message*
60    deleted       boolean        Flag
61    draft         boolean        Flag
62    flagged       boolean        Flag
63    sender        string
64    recent        boolean        Flag
65    seen          boolean        Flag
66    subject       string
67    mime          string         The mime header declaration
68    email         string         The complete RFC822 message (*)
69    attachments   list           Each non text part as dict
70    encoding      string         The main detected encoding
71    ===========   ============== ===========
72
73    (*) At the application side it is measured as the length of the RFC822
74    message string
75
76    WARNING: As row id's are mapped to email sequence numbers,
77    make sure your imap client web2py app does not delete messages
78    during select or update actions, to prevent
79    updating or deleting different messages.
80    Sequence numbers change whenever the mailbox is updated.
81    To avoid this sequence numbers issues, it is recommended the use
82    of uid fields in query references (although the update and delete
83    in separate actions rule still applies).
84    ::
85
86        # This is the code recommended to start imap support
87        # at the app's model:
88
89        imapdb = DAL("imap://user:password@server:port", pool_size=1) # port 993 for ssl
90        imapdb.define_tables()
91
92    Here is an (incomplete) list of possible imap commands::
93
94        # Count today's unseen messages
95        # smaller than 6000 octets from the
96        # inbox mailbox
97
98        q = imapdb.INBOX.seen == False
99        q &= imapdb.INBOX.created == datetime.date.today()
100        q &= imapdb.INBOX.size < 6000
101        unread = imapdb(q).count()
102
103        # Fetch last query messages
104        rows = imapdb(q).select()
105
106        # it is also possible to filter query select results with limitby and
107        # sequences of mailbox fields
108
109        set.select(<fields sequence>, limitby=(<int>, <int>))
110
111        # Mark last query messages as seen
112        messages = [row.uid for row in rows]
113        seen = imapdb(imapdb.INBOX.uid.belongs(messages)).update(seen=True)
114
115        # Delete messages in the imap database that have mails from mr. Gumby
116
117        deleted = 0
118        for mailbox in imapdb.tables
119            deleted += imapdb(imapdb[mailbox].sender.contains("gumby")).delete()
120
121        # It is possible also to mark messages for deletion instead of ereasing them
122        # directly with set.update(deleted=True)
123
124
125        # This object give access
126        # to the adapter auto mailbox
127        # mapped names (which native
128        # mailbox has what table name)
129
130        imapdb.mailboxes <dict> # tablename, server native name pairs
131
132        # To retrieve a table native mailbox name use:
133        imapdb.<table>.mailbox
134
135        ### New features v2.4.1:
136
137        # Declare mailboxes statically with tablename, name pairs
138        # This avoids the extra server names retrieval
139
140        imapdb.define_tables({"inbox": "INBOX"})
141
142        # Selects without content/attachments/email columns will only
143        # fetch header and flags
144
145        imapdb(q).select(imapdb.INBOX.sender, imapdb.INBOX.subject)
146
147    """
148
149    drivers = ("imaplib",)
150    types = {
151        "string": str,
152        "text": str,
153        "date": datetime.date,
154        "datetime": datetime.datetime,
155        "id": long,
156        "boolean": bool,
157        "integer": int,
158        "bigint": long,
159        "blob": str,
160        "list:string": str,
161    }
162
163    dbengine = "imap"
164
165    REGEX_URI = re.compile(
166        "^(?P<user>[^:]+)(\:(?P<password>[^@]*))?@(?P<host>\[[^/]+\]|[^\:@]+)(\:(?P<port>[0-9]+))?$"
167    )
168
169    def __init__(
170        self,
171        db,
172        uri,
173        pool_size=0,
174        folder=None,
175        db_codec="UTF-8",
176        credential_decoder=IDENTITY,
177        driver_args={},
178        adapter_args={},
179        after_connection=None,
180    ):
181
182        super(IMAPAdapter, self).__init__(
183            db=db,
184            uri=uri,
185            pool_size=pool_size,
186            folder=folder,
187            db_codec=db_codec,
188            credential_decoder=credential_decoder,
189            driver_args=driver_args,
190            adapter_args=adapter_args,
191            after_connection=after_connection,
192        )
193
194        # db uri: user@example.com:password@imap.server.com:123
195        # TODO: max size adapter argument for preventing large mail transfers
196
197        self.find_driver(adapter_args)
198        self.credential_decoder = credential_decoder
199        self.driver_args = driver_args
200        self.adapter_args = adapter_args
201        self.mailbox_size = None
202        self.static_names = None
203        self.charset = sys.getfilesystemencoding()
204        # imap class
205        self.imap4 = None
206        uri = uri.split("://")[1]
207
208        """ MESSAGE is an identifier for sequence number"""
209
210        self.flags = {
211            "deleted": "\\Deleted",
212            "draft": "\\Draft",
213            "flagged": "\\Flagged",
214            "recent": "\\Recent",
215            "seen": "\\Seen",
216            "answered": "\\Answered",
217        }
218        self.search_fields = {
219            "id": "MESSAGE",
220            "created": "DATE",
221            "uid": "UID",
222            "sender": "FROM",
223            "to": "TO",
224            "cc": "CC",
225            "bcc": "BCC",
226            "content": "TEXT",
227            "size": "SIZE",
228            "deleted": "\\Deleted",
229            "draft": "\\Draft",
230            "flagged": "\\Flagged",
231            "recent": "\\Recent",
232            "seen": "\\Seen",
233            "subject": "SUBJECT",
234            "answered": "\\Answered",
235            "mime": None,
236            "email": None,
237            "attachments": None,
238        }
239
240        m = self.REGEX_URI.match(uri)
241        user = m.group("user")
242        password = m.group("password")
243        host = m.group("host")
244        port = int(m.group("port"))
245        over_ssl = False
246        if port == 993:
247            over_ssl = True
248
249        driver_args.update(host=host, port=port, password=password, user=user)
250
251        def connector(driver_args=driver_args):
252            # it is assumed sucessful authentication alLways
253            # TODO: support direct connection and login tests
254            if over_ssl:
255                self.imap4 = self.driver.IMAP4_SSL
256            else:
257                self.imap4 = self.driver.IMAP4
258            connection = self.imap4(driver_args["host"], driver_args["port"])
259            data = connection.login(driver_args["user"], driver_args["password"])
260
261            # static mailbox list
262            connection.mailbox_names = None
263
264            # dummy dbapi functions
265            connection.cursor = lambda: self.fake_cursor
266            connection.close = lambda: None
267            connection.commit = lambda: None
268
269            return connection
270
271        self.db.define_tables = self.define_tables
272        self.connector = connector
273        self.reconnect()
274
275    def reconnect(self, f=None):
276        """
277        IMAP4 Pool connection method
278
279        imap connection lacks of self cursor command.
280        A custom command should be provided as a replacement
281        for connection pooling to prevent uncaught remote session
282        closing
283
284        """
285        if getattr(self, "connection", None) is not None:
286            return
287        if f is None:
288            f = self.connector
289
290        if not self.pool_size:
291            self.connection = f()
292            self.cursor = self.connection.cursor()
293        else:
294            POOLS = ConnectionPool.POOLS
295            uri = self.uri
296            while True:
297                GLOBAL_LOCKER.acquire()
298                if not uri in POOLS:
299                    POOLS[uri] = []
300                if POOLS[uri]:
301                    self.connection = POOLS[uri].pop()
302                    GLOBAL_LOCKER.release()
303                    self.cursor = self.connection.cursor()
304                    if self.cursor and self.check_active_connection:
305                        try:
306                            # check if connection is alive or close it
307                            result, data = self.connection.list()
308                        except:
309                            # Possible connection reset error
310                            # TODO: read exception class
311                            self.connection = f()
312                    break
313                else:
314                    GLOBAL_LOCKER.release()
315                    self.connection = f()
316                    self.cursor = self.connection.cursor()
317                    break
318        self.after_connection_hook()
319
320    def get_last_message(self, tablename):
321        last_message = None
322        # request mailbox list to the server if needed.
323        if not isinstance(self.connection.mailbox_names, dict):
324            self.get_mailboxes()
325        try:
326            result = self.connection.select(self.connection.mailbox_names[tablename])
327            last_message = int(result[1][0])
328            # Last message must be a positive integer
329            if last_message == 0:
330                last_message = 1
331        except (IndexError, ValueError, TypeError, KeyError):
332            e = sys.exc_info()[1]
333            self.db.logger.debug(
334                "Error retrieving the last mailbox" + " sequence number. %s" % str(e)
335            )
336        return last_message
337
338    def get_uid_bounds(self, tablename):
339        if not isinstance(self.connection.mailbox_names, dict):
340            self.get_mailboxes()
341        # fetch first and last messages
342        # return (first, last) messages uid's
343        last_message = self.get_last_message(tablename)
344        result, data = self.connection.uid("search", None, "(ALL)")
345        uid_list = data[0].strip().split()
346        if len(uid_list) <= 0:
347            return None
348        else:
349            return (uid_list[0], uid_list[-1])
350
351    def convert_date(self, date, add=None, imf=False):
352        if add is None:
353            add = datetime.timedelta()
354        """ Convert a date object to a string
355        with d-Mon-Y style for IMAP or the inverse
356        case
357
358        add <timedelta> adds to the date object
359        """
360        months = [
361            None,
362            "JAN",
363            "FEB",
364            "MAR",
365            "APR",
366            "MAY",
367            "JUN",
368            "JUL",
369            "AUG",
370            "SEP",
371            "OCT",
372            "NOV",
373            "DEC",
374        ]
375        if isinstance(date, basestring):
376            # Prevent unexpected date response format
377            try:
378                if "," in date:
379                    dayname, datestring = date.split(",")
380                else:
381                    dayname, datestring = None, date
382                date_list = datestring.strip().split()
383                year = int(date_list[2])
384                month = months.index(date_list[1].upper())
385                day = int(date_list[0])
386                hms = list(map(int, date_list[3].split(":")))
387                return datetime.datetime(year, month, day, hms[0], hms[1], hms[2]) + add
388            except (ValueError, AttributeError, IndexError) as e:
389                self.db.logger.error("Could not parse date text: %s. %s" % (date, e))
390                return None
391        elif isinstance(date, (datetime.date, datetime.datetime)):
392            if imf:
393                date_format = "%a, %d %b %Y %H:%M:%S %z"
394            else:
395                date_format = "%d-%b-%Y"
396            return (date + add).strftime(date_format)
397        else:
398            return None
399
400    @staticmethod
401    def header_represent(f, r):
402        from email.header import decode_header
403
404        text, encoding = decode_header(f)[0]
405        if encoding:
406            text = text.decode(encoding).encode("utf-8")
407        return text
408
409    def encode_text(self, text, charset, errors="replace"):
410        """ convert text for mail to unicode"""
411        if text is None:
412            text = ""
413        if PY2:
414            if isinstance(text, str):
415                if charset is None:
416                    text = unicode(text, "utf-8", errors)
417                else:
418                    text = unicode(text, charset, errors)
419            else:
420                raise Exception("Unsupported mail text type %s" % type(text))
421            return text.encode("utf-8")
422        else:
423            if isinstance(text, bytes):
424                return text.decode("utf-8")
425            return text
426
427    def get_charset(self, message):
428        charset = message.get_content_charset()
429        return charset
430
431    def get_mailboxes(self):
432        """ Query the mail database for mailbox names """
433        if self.static_names:
434            # statically defined mailbox names
435            self.connection.mailbox_names = self.static_names
436            return self.static_names.keys()
437
438        mailboxes_list = self.connection.list()
439        self.connection.mailbox_names = dict()
440        mailboxes = list()
441        x = 0
442        for item in mailboxes_list[1]:
443            x = x + 1
444            item = item.strip()
445            if not "NOSELECT" in item.upper():
446                sub_items = item.split('"')
447                sub_items = [
448                    sub_item for sub_item in sub_items if len(sub_item.strip()) > 0
449                ]
450                # mailbox = sub_items[len(sub_items) -1]
451                mailbox = sub_items[-1].strip()
452                # remove unwanted characters and store original names
453                # Don't allow leading non alphabetic characters
454                mailbox_name = re.sub(
455                    "^[_0-9]*", "", re.sub("[^_\w]", "", re.sub("[/ ]", "_", mailbox))
456                )
457                mailboxes.append(mailbox_name)
458                self.connection.mailbox_names[mailbox_name] = mailbox
459
460        return mailboxes
461
462    def get_query_mailbox(self, query):
463        nofield = True
464        tablename = None
465        attr = query
466        while nofield:
467            if hasattr(attr, "first"):
468                attr = attr.first
469                if isinstance(attr, Field):
470                    return attr.tablename
471                elif isinstance(attr, Query):
472                    pass
473                else:
474                    return None
475            else:
476                return None
477        return tablename
478
479    def is_flag(self, flag):
480        if self.search_fields.get(flag, None) in self.flags.values():
481            return True
482        else:
483            return False
484
485    def define_tables(self, mailbox_names=None):
486        """
487        Auto create common IMAP fileds
488
489        This function creates fields definitions "statically"
490        meaning that custom fields as in other adapters should
491        not be supported and definitions handled on a service/mode
492        basis (local syntax for Gmail(r), Ymail(r)
493
494        Returns a dictionary with tablename, server native mailbox name
495        pairs.
496        """
497        if mailbox_names:
498            # optional statically declared mailboxes
499            self.static_names = mailbox_names
500        else:
501            self.static_names = None
502        if not isinstance(self.connection.mailbox_names, dict):
503            self.get_mailboxes()
504
505        names = self.connection.mailbox_names.keys()
506
507        for name in names:
508            self.db.define_table(
509                "%s" % name,
510                Field("uid", writable=False),
511                Field("created", "datetime", writable=False),
512                Field("content", "text", writable=False),
513                Field("to", writable=False),
514                Field("cc", writable=False),
515                Field("bcc", writable=False),
516                Field("sender", writable=False),
517                Field("size", "integer", writable=False),
518                Field("subject", writable=False),
519                Field("mime", writable=False),
520                Field("email", "text", writable=False, readable=False),
521                Field("attachments", "text", writable=False, readable=False),
522                Field("encoding", writable=False),
523                Field("answered", "boolean"),
524                Field("deleted", "boolean"),
525                Field("draft", "boolean"),
526                Field("flagged", "boolean"),
527                Field("recent", "boolean", writable=False),
528                Field("seen", "boolean"),
529            )
530
531            # Set a special _mailbox attribute for storing
532            # native mailbox names
533            self.db[name].mailbox = self.connection.mailbox_names[name]
534
535            # decode quoted printable
536            self.db[name].to.represent = self.db[name].cc.represent = self.db[
537                name
538            ].bcc.represent = self.db[name].sender.represent = self.db[
539                name
540            ].subject.represent = self.header_represent
541
542        # Set the db instance mailbox collections
543        self.db.mailboxes = self.connection.mailbox_names
544        return self.db.mailboxes
545
546    def create_table(self, *args, **kwargs):
547        # not implemented
548        # but required by DAL
549        pass
550
551    def select(self, query, fields, attributes):
552        """  Searches and Fetches records and return web2py rows
553        """
554        # move this statement elsewhere (upper-level)
555        if use_common_filters(query):
556            query = self.common_filter(query, [self.get_query_mailbox(query),])
557
558        import email
559
560        # get records from imap server with search + fetch
561        # convert results to a dictionary
562        tablename = None
563        fetch_results = list()
564
565        if isinstance(query, Query):
566            tablename = self.get_table(query)._dalname
567            mailbox = self.connection.mailbox_names.get(tablename, None)
568            if mailbox is None:
569                raise ValueError("Mailbox name not found: %s" % mailbox)
570            else:
571                # select with readonly
572                result, selected = self.connection.select(mailbox, True)
573                if result != "OK":
574                    raise Exception("IMAP error: %s" % selected)
575                self.mailbox_size = int(selected[0])
576                search_query = "(%s)" % str(query).strip()
577                search_result = self.connection.uid("search", None, search_query)
578                # Normal IMAP response OK is assumed (change this)
579                if search_result[0] == "OK":
580                    # For "light" remote server responses just get the first
581                    # ten records (change for non-experimental implementation)
582                    # However, light responses are not guaranteed with this
583                    # approach, just fewer messages.
584                    limitby = attributes.get("limitby", None)
585                    messages_set = search_result[1][0].split()
586                    # descending order
587                    messages_set.reverse()
588                    if limitby is not None:
589                        # TODO: orderby, asc/desc, limitby from complete message set
590                        messages_set = messages_set[int(limitby[0]) : int(limitby[1])]
591
592                    # keep the requests small for header/flags
593                    if any(
594                        [
595                            (field.name in ["content", "size", "attachments", "email"])
596                            for field in fields
597                        ]
598                    ):
599                        imap_fields = "(RFC822 FLAGS)"
600                    else:
601                        imap_fields = "(RFC822.HEADER FLAGS)"
602
603                    if len(messages_set) > 0:
604                        # create fetch results object list
605                        # fetch each remote message and store it in memmory
606                        # (change to multi-fetch command syntax for faster
607                        # transactions)
608                        for uid in messages_set:
609                            # fetch the RFC822 message body
610                            typ, data = self.connection.uid("fetch", uid, imap_fields)
611                            if typ == "OK":
612                                fr = {
613                                    "message": int(data[0][0].split()[0]),
614                                    "uid": long(uid),
615                                    "email": email.message_from_string(data[0][1]),
616                                    "raw_message": data[0][1],
617                                }
618                                fr["multipart"] = fr["email"].is_multipart()
619                                # fetch flags for the message
620                                if PY2:
621                                    fr["flags"] = self.driver.ParseFlags(data[1])
622                                else:
623                                    fr["flags"] = self.driver.ParseFlags(
624                                        bytes(data[1], "utf-8")
625                                    )
626                                fetch_results.append(fr)
627                            else:
628                                # error retrieving the message body
629                                raise Exception(
630                                    "IMAP error retrieving the body: %s" % data
631                                )
632                else:
633                    raise Exception("IMAP search error: %s" % search_result[1])
634        elif isinstance(query, (Expression, basestring)):
635            raise NotImplementedError()
636        else:
637            raise TypeError("Unexpected query type")
638
639        imapqry_dict = {}
640        imapfields_dict = {}
641
642        if len(fields) == 1 and isinstance(fields[0], SQLALL):
643            allfields = True
644        elif len(fields) == 0:
645            allfields = True
646        else:
647            allfields = False
648        if allfields:
649            colnames = [
650                "%s.%s" % (tablename, field) for field in self.search_fields.keys()
651            ]
652        else:
653            colnames = [field.longname for field in fields]
654
655        for k in colnames:
656            imapfields_dict[k] = k
657
658        imapqry_list = list()
659        imapqry_array = list()
660        for fr in fetch_results:
661            attachments = []
662            content = []
663            size = 0
664            n = int(fr["message"])
665            item_dict = dict()
666            message = fr["email"]
667            uid = fr["uid"]
668            charset = self.get_charset(message)
669            flags = fr["flags"]
670            raw_message = fr["raw_message"]
671            # Return messages data mapping static fields
672            # and fetched results. Mapping should be made
673            # outside the select function (with auxiliary
674            # instance methods)
675
676            # pending: search flags states trough the email message
677            # instances for correct output
678
679            # preserve subject encoding (ASCII/quoted printable)
680
681            if "%s.id" % tablename in colnames:
682                item_dict["%s.id" % tablename] = n
683            if "%s.created" % tablename in colnames:
684                item_dict["%s.created" % tablename] = self.convert_date(message["Date"])
685            if "%s.uid" % tablename in colnames:
686                item_dict["%s.uid" % tablename] = uid
687            if "%s.sender" % tablename in colnames:
688                # If there is no encoding found in the message header
689                # force utf-8 replacing characters (change this to
690                # module's defaults). Applies to .sender, .to, .cc and .bcc fields
691                item_dict["%s.sender" % tablename] = message["From"]
692            if "%s.to" % tablename in colnames:
693                item_dict["%s.to" % tablename] = message["To"]
694            if "%s.cc" % tablename in colnames:
695                if "Cc" in message.keys():
696                    item_dict["%s.cc" % tablename] = message["Cc"]
697                else:
698                    item_dict["%s.cc" % tablename] = ""
699            if "%s.bcc" % tablename in colnames:
700                if "Bcc" in message.keys():
701                    item_dict["%s.bcc" % tablename] = message["Bcc"]
702                else:
703                    item_dict["%s.bcc" % tablename] = ""
704            if "%s.deleted" % tablename in colnames:
705                item_dict["%s.deleted" % tablename] = "\\Deleted" in flags
706            if "%s.draft" % tablename in colnames:
707                item_dict["%s.draft" % tablename] = "\\Draft" in flags
708            if "%s.flagged" % tablename in colnames:
709                item_dict["%s.flagged" % tablename] = "\\Flagged" in flags
710            if "%s.recent" % tablename in colnames:
711                item_dict["%s.recent" % tablename] = "\\Recent" in flags
712            if "%s.seen" % tablename in colnames:
713                item_dict["%s.seen" % tablename] = "\\Seen" in flags
714            if "%s.subject" % tablename in colnames:
715                item_dict["%s.subject" % tablename] = message["Subject"]
716            if "%s.answered" % tablename in colnames:
717                item_dict["%s.answered" % tablename] = "\\Answered" in flags
718            if "%s.mime" % tablename in colnames:
719                item_dict["%s.mime" % tablename] = message.get_content_type()
720            if "%s.encoding" % tablename in colnames:
721                item_dict["%s.encoding" % tablename] = charset
722
723            # Here goes the whole RFC822 body as an email instance
724            # for controller side custom processing
725            # The message is stored as a raw string
726            # >> email.message_from_string(raw string)
727            # returns a Message object for enhanced object processing
728            if "%s.email" % tablename in colnames:
729                # WARNING: no encoding performed (raw message)
730                item_dict["%s.email" % tablename] = raw_message
731
732            # Size measure as suggested in a Velocity Reviews post
733            # by Tim Williams: "how to get size of email attachment"
734            # Note: len() and server RFC822.SIZE reports doesn't match
735            # To retrieve the server size for representation would add a new
736            # fetch transaction to the process
737            for part in message.walk():
738                maintype = part.get_content_maintype()
739                if ("%s.attachments" % tablename in colnames) or (
740                    "%s.content" % tablename in colnames
741                ):
742                    payload = part.get_payload(decode=True)
743                    if payload:
744                        filename = part.get_filename()
745                        values = {"mime": part.get_content_type()}
746                        if (filename or not "text" in maintype) and (
747                            "%s.attachments" % tablename in colnames
748                        ):
749                            values.update(
750                                {
751                                    "payload": payload,
752                                    "filename": filename,
753                                    "encoding": part.get_content_charset(),
754                                    "disposition": part["Content-Disposition"],
755                                }
756                            )
757                            attachments.append(values)
758                        elif ("text" in maintype) and (
759                            "%s.content" % tablename in colnames
760                        ):
761                            values.update(
762                                {
763                                    "text": self.encode_text(
764                                        payload, self.get_charset(part)
765                                    )
766                                }
767                            )
768                            content.append(values)
769
770                if "%s.size" % tablename in colnames:
771                    if part is not None:
772                        size += len(str(part))
773            item_dict["%s.content" % tablename] = content
774            item_dict["%s.attachments" % tablename] = attachments
775            item_dict["%s.size" % tablename] = size
776            imapqry_list.append(item_dict)
777
778        # extra object mapping for the sake of rows object
779        # creation (sends an array or lists)
780        for item_dict in imapqry_list:
781            imapqry_array_item = list()
782            for fieldname in colnames:
783                imapqry_array_item.append(item_dict[fieldname])
784            imapqry_array.append(imapqry_array_item)
785
786        # parse result and return a rows object
787        colnames = colnames
788        processor = attributes.get("processor", self.parse)
789        return processor(imapqry_array, fields, colnames)
790
791    def insert(self, table, fields):
792        def add_payload(message, obj):
793            payload = Message()
794            encoding = obj.get("encoding", "utf-8")
795            if encoding and (encoding.upper() in ("BASE64", "7BIT", "8BIT", "BINARY")):
796                payload.add_header("Content-Transfer-Encoding", encoding)
797            else:
798                payload.set_charset(encoding)
799            mime = obj.get("mime", None)
800            if mime:
801                payload.set_type(mime)
802            if "text" in obj:
803                payload.set_payload(obj["text"])
804            elif "payload" in obj:
805                payload.set_payload(obj["payload"])
806            if "filename" in obj and obj["filename"]:
807                payload.add_header(
808                    "Content-Disposition", "attachment", filename=obj["filename"]
809                )
810            message.attach(payload)
811
812        mailbox = table.mailbox
813        d = dict(((k.name, v) for k, v in fields))
814        date_time = d.get("created") or datetime.datetime.now()
815        struct_time = date_time.timetuple()
816        if len(d) > 0:
817            message = d.get("email", None)
818            attachments = d.get("attachments", [])
819            content = d.get("content", [])
820            flags = " ".join(
821                [
822                    "\\%s" % flag.capitalize()
823                    for flag in (
824                        "answered",
825                        "deleted",
826                        "draft",
827                        "flagged",
828                        "recent",
829                        "seen",
830                    )
831                    if d.get(flag, False)
832                ]
833            )
834            if not message:
835                from email.message import Message
836
837                mime = d.get("mime", None)
838                charset = d.get("encoding", None)
839                message = Message()
840                message["from"] = d.get("sender", "")
841                message["subject"] = d.get("subject", "")
842                message["date"] = self.convert_date(date_time, imf=True)
843
844                if mime:
845                    message.set_type(mime)
846                if charset:
847                    message.set_charset(charset)
848                for item in ("to", "cc", "bcc"):
849                    value = d.get(item, "")
850                    if isinstance(value, basestring):
851                        message[item] = value
852                    else:
853                        message[item] = ";".join([i for i in value])
854                if not message.is_multipart() and (
855                    not message.get_content_type().startswith("multipart")
856                ):
857                    if isinstance(content, basestring):
858                        message.set_payload(content)
859                    elif len(content) > 0:
860                        message.set_payload(content[0]["text"])
861                else:
862                    [add_payload(message, c) for c in content]
863                    [add_payload(message, a) for a in attachments]
864                message = message.as_string()
865
866            result, data = self.connection.append(mailbox, flags, struct_time, message)
867            if result == "OK":
868                uid = int(re.findall("\d+", str(data))[-1])
869                return self.db(table.uid == uid).select(table.id).first().id
870            else:
871                raise Exception("IMAP message append failed: %s" % data)
872        else:
873            raise NotImplementedError("IMAP empty insert is not implemented")
874
875    def update(self, table, query, fields):
876        # TODO: the adapter should implement an .expand method
877        commands = list()
878        rowcount = 0
879        tablename = table._dalname
880        if use_common_filters(query):
881            query = self.common_filter(query, [tablename,])
882        mark = []
883        unmark = []
884        if query:
885            for item in fields:
886                field = item[0]
887                name = field.name
888                value = item[1]
889                if self.is_flag(name):
890                    flag = self.search_fields[name]
891                    if (value is not None) and (flag != "\\Recent"):
892                        if value:
893                            mark.append(flag)
894                        else:
895                            unmark.append(flag)
896            result, data = self.connection.select(
897                self.connection.mailbox_names[tablename]
898            )
899            string_query = "(%s)" % query
900            result, data = self.connection.search(None, string_query)
901            store_list = [
902                item.strip() for item in data[0].split() if item.strip().isdigit()
903            ]
904            # build commands for marked flags
905            for number in store_list:
906                result = None
907                if len(mark) > 0:
908                    commands.append((number, "+FLAGS", "(%s)" % " ".join(mark)))
909                if len(unmark) > 0:
910                    commands.append((number, "-FLAGS", "(%s)" % " ".join(unmark)))
911
912        for command in commands:
913            result, data = self.connection.store(*command)
914            if result == "OK":
915                rowcount += 1
916            else:
917                raise Exception("IMAP storing error: %s" % data)
918        return rowcount
919
920    def count(self, query, distinct=None):
921        counter = 0
922        tablename = self.get_query_mailbox(query)
923        if query and tablename is not None:
924            if use_common_filters(query):
925                query = self.common_filter(query, [tablename,])
926            result, data = self.connection.select(
927                self.connection.mailbox_names[tablename]
928            )
929            string_query = "(%s)" % query
930            result, data = self.connection.search(None, string_query)
931            store_list = [
932                item.strip() for item in data[0].split() if item.strip().isdigit()
933            ]
934            counter = len(store_list)
935        return counter
936
937    def delete(self, table, query):
938        counter = 0
939        tablename = table._dalname
940        if query:
941            if use_common_filters(query):
942                query = self.common_filter(query, [tablename,])
943            result, data = self.connection.select(
944                self.connection.mailbox_names[tablename]
945            )
946            string_query = "(%s)" % query
947            result, data = self.connection.search(None, string_query)
948            store_list = [
949                item.strip() for item in data[0].split() if item.strip().isdigit()
950            ]
951            for number in store_list:
952                result, data = self.connection.store(number, "+FLAGS", "(\\Deleted)")
953                if result == "OK":
954                    counter += 1
955                else:
956                    raise Exception("IMAP store error: %s" % data)
957            if counter > 0:
958                result, data = self.connection.expunge()
959        return counter
960
961    def BELONGS(self, first, second):
962        result = None
963        name = self.search_fields[first.name]
964        if name == "MESSAGE":
965            values = [str(val) for val in second if str(val).isdigit()]
966            result = "%s" % ",".join(values).strip()
967
968        elif name == "UID":
969            values = [str(val) for val in second if str(val).isdigit()]
970            result = "UID %s" % ",".join(values).strip()
971
972        else:
973            raise Exception("Operation not supported")
974        # result = "(%s %s)" % (self.expand(first), self.expand(second))
975        return result
976
977    def CONTAINS(self, first, second, case_sensitive=False):
978        # silently ignore, only case sensitive
979        result = None
980        name = self.search_fields[first.name]
981
982        if name in ("FROM", "TO", "SUBJECT", "TEXT"):
983            result = '%s "%s"' % (name, self.expand(second))
984        else:
985            if first.name in ("cc", "bcc"):
986                result = '%s "%s"' % (first.name.upper(), self.expand(second))
987            elif first.name == "mime":
988                result = 'HEADER Content-Type "%s"' % self.expand(second)
989            else:
990                raise Exception("Operation not supported")
991        return result
992
993    def GT(self, first, second):
994        result = None
995        name = self.search_fields[first.name]
996        if name == "MESSAGE":
997            last_message = self.get_last_message(first.tablename)
998            result = "%d:%d" % (int(self.expand(second)) + 1, last_message)
999        elif name == "UID":
1000            # GT and LT may not return
1001            # expected sets depending on
1002            # the uid format implemented
1003            try:
1004                pedestal, threshold = self.get_uid_bounds(first.tablename)
1005            except TypeError:
1006                e = sys.exc_info()[1]
1007                self.db.logger.debug("Error requesting uid bounds: %s", str(e))
1008                return ""
1009            try:
1010                lower_limit = int(self.expand(second)) + 1
1011            except (ValueError, TypeError):
1012                e = sys.exc_info()[1]
1013                raise Exception("Operation not supported (non integer UID)")
1014            result = "UID %s:%s" % (lower_limit, threshold)
1015        elif name == "DATE":
1016            result = "SINCE %s" % self.convert_date(second, add=datetime.timedelta(1))
1017        elif name == "SIZE":
1018            result = "LARGER %s" % self.expand(second)
1019        else:
1020            raise Exception("Operation not supported")
1021        return result
1022
1023    def GE(self, first, second):
1024        result = None
1025        name = self.search_fields[first.name]
1026        if name == "MESSAGE":
1027            last_message = self.get_last_message(first.tablename)
1028            result = "%s:%s" % (self.expand(second), last_message)
1029        elif name == "UID":
1030            # GT and LT may not return
1031            # expected sets depending on
1032            # the uid format implemented
1033            try:
1034                pedestal, threshold = self.get_uid_bounds(first.tablename)
1035            except TypeError:
1036                e = sys.exc_info()[1]
1037                self.db.logger.debug("Error requesting uid bounds: %s", str(e))
1038                return ""
1039            lower_limit = self.expand(second)
1040            result = "UID %s:%s" % (lower_limit, threshold)
1041        elif name == "DATE":
1042            result = "SINCE %s" % self.convert_date(second)
1043        else:
1044            raise Exception("Operation not supported")
1045        return result
1046
1047    def LT(self, first, second):
1048        result = None
1049        name = self.search_fields[first.name]
1050        if name == "MESSAGE":
1051            result = "%s:%s" % (1, int(self.expand(second)) - 1)
1052        elif name == "UID":
1053            try:
1054                pedestal, threshold = self.get_uid_bounds(first.tablename)
1055            except TypeError:
1056                e = sys.exc_info()[1]
1057                self.db.logger.debug("Error requesting uid bounds: %s", str(e))
1058                return ""
1059            try:
1060                upper_limit = int(self.expand(second)) - 1
1061            except (ValueError, TypeError):
1062                e = sys.exc_info()[1]
1063                raise Exception("Operation not supported (non integer UID)")
1064            result = "UID %s:%s" % (pedestal, upper_limit)
1065        elif name == "DATE":
1066            result = "BEFORE %s" % self.convert_date(second)
1067        elif name == "SIZE":
1068            result = "SMALLER %s" % self.expand(second)
1069        else:
1070            raise Exception("Operation not supported")
1071        return result
1072
1073    def LE(self, first, second):
1074        result = None
1075        name = self.search_fields[first.name]
1076        if name == "MESSAGE":
1077            result = "%s:%s" % (1, self.expand(second))
1078        elif name == "UID":
1079            try:
1080                pedestal, threshold = self.get_uid_bounds(first.tablename)
1081            except TypeError:
1082                e = sys.exc_info()[1]
1083                self.db.logger.debug("Error requesting uid bounds: %s", str(e))
1084                return ""
1085            upper_limit = int(self.expand(second))
1086            result = "UID %s:%s" % (pedestal, upper_limit)
1087        elif name == "DATE":
1088            result = "BEFORE %s" % self.convert_date(second, add=datetime.timedelta(1))
1089        else:
1090            raise Exception("Operation not supported")
1091        return result
1092
1093    def NE(self, first, second=None):
1094        if (second is None) and isinstance(first, Field):
1095            # All records special table query
1096            if first.type == "id":
1097                return self.GE(first, 1)
1098        result = self.NOT(self.EQ(first, second))
1099        result = result.replace("NOT NOT", "").strip()
1100        return result
1101
1102    def EQ(self, first, second):
1103        name = self.search_fields[first.name]
1104        result = None
1105        if name is not None:
1106            if name == "MESSAGE":
1107                # query by message sequence number
1108                result = "%s" % self.expand(second)
1109            elif name == "UID":
1110                result = "UID %s" % self.expand(second)
1111            elif name == "DATE":
1112                result = "ON %s" % self.convert_date(second)
1113
1114            elif name in self.flags.values():
1115                if second:
1116                    result = "%s" % (name.upper()[1:])
1117                else:
1118                    result = "NOT %s" % (name.upper()[1:])
1119            else:
1120                raise Exception("Operation not supported")
1121        else:
1122            raise Exception("Operation not supported")
1123        return result
1124
1125    def AND(self, first, second):
1126        result = "%s %s" % (self.expand(first), self.expand(second))
1127        return result
1128
1129    def OR(self, first, second):
1130        result = "OR %s %s" % (self.expand(first), self.expand(second))
1131        return "%s" % result.replace("OR OR", "OR")
1132
1133    def NOT(self, first):
1134        result = "NOT %s" % self.expand(first)
1135        return result
Note: See TracBrowser for help on using the repository browser.