PY2 = sys.version_info[0] == 2
    def implements_bool(cls):
    def implements_iterator(cls):
    def to_bytes(obj, charset="utf-8", errors="strict"):
    def to_native(obj, charset="utf8", errors="strict"):
    def to_bytes(obj, charset="utf-8", errors="strict"):
    def to_native(obj, charset="utf8", errors="strict"):
def with_metaclass(meta, *bases):
        def __new__(cls, name, this_bases, d):
def to_unicode(obj, charset="utf-8", errors="strict"):
    class metaclass(meta): class Serializable(object):
    def as_dict(self, flat=False, sanitize=True):
        return self.__dict__

    def as_xml(self, sanitize=True):
        return serializers.xml(self.as_dict(flat=True, sanitize=sanitize))

    def as_json(self, sanitize=True):
        return serializers.json(self.as_dict(flat=True, sanitize=sanitize))

    def as_yaml(self, sanitize=True):
        return serializers.yaml(self.as_dict(flat=True, sanitize=sanitize)) class SQLCallableList(list):
    def __call__(self):
        return copy.copy(self) class BasicStorage(object):
    def __init__(self, *args, **kwargs):
        return self.__dict__.__init__(*args, **kwargs)

    def __getitem__(self, key):
        return self.__dict__.__getitem__(str(key))

    __setitem__ = object.__setattr__

    def __delitem__(self, key):
        try:
            delattr(self, key)
        except AttributeError:
            raise KeyError(key)

    def __bool__(self):
        return len(self.__dict__) > 0

    __iter__ = lambda self: self.__dict__.__iter__()

    __str__ = lambda self: self.__dict__.__str__()

    __repr__ = lambda self: self.__dict__.__repr__()

    has_key = __contains__ = lambda self, key: key in self.__dict__

    def get(self, key, default=None):
        return self.__dict__.get(key, default)

    def update(self, *args, **kwargs):
        return self.__dict__.update(*args, **kwargs)

    def keys(self):
        return self.__dict__.keys()

    def iterkeys(self):
        return iterkeys(self.__dict__)

    def values(self):
        return self.__dict__.values()

    def itervalues(self):
        return itervalues(self.__dict__)

    def items(self):
        return self.__dict__.items()

    def iteritems(self):
        return iteritems(self.__dict__)

    pop = lambda self, *args, **kwargs: self.__dict__.pop(*args, **kwargs)

    clear = lambda self, *args, **kwargs: self.__dict__.clear(*args, **kwargs)

    copy = lambda self, *args, **kwargs: self.__dict__.copy(*args, **kwargs) class RecordUpdater(RecordOperator):
    def __call__(self, **fields):
        colset, db, tablename, id = self.colset, self.db, self.tablename, self.id
        table = db[tablename]
        newfields = fields or dict(colset)
        for fieldname in list(newfields.keys()):
            if fieldname not in table.fields or table[fieldname].type == "id":
                del newfields[fieldname]
        table._db(table._id == id, ignore_common_filters=True).update(**newfields)
        colset.update(newfields)
        return colset class RecordDeleter(RecordOperator):
    def __call__(self):
        return self.db(self.db[self.tablename]._id == self.id).delete() class TimingHandler(ExecutionHandler):
    MAXSTORAGE = 100

    def _timings(self):
        THREAD_LOCAL._pydal_timings_ = getattr(THREAD_LOCAL, "_pydal_timings_", [])
        return THREAD_LOCAL._pydal_timings_

    @property
    def timings(self):
        return self._timings()

    def before_execute(self, command):
        self.t = time.time()

    def after_execute(self, command):
        dt = time.time() - self.t
        self.timings.append((command, dt))
        del self.timings[: -self.MAXSTORAGE] def hide_password(uri):
    if isinstance(uri, (list, tuple)):
        return [hide_password(item) for item in uri]
    return re.sub(REGEX_CREDENTIALS, "******", uri) def smart_query(fields, text):
    from ..objects import Field, Table

    if not isinstance(fields, (list, tuple)):
        fields = [fields]
    new_fields = []
    for field in fields:
        if isinstance(field, Field):
            new_fields.append(field)
        elif isinstance(field, Table):
            for ofield in field:
                new_fields.append(ofield)
        else:
            raise RuntimeError("fields must be a list of fields")
    fields = new_fields
    field_map = {}
    for field in fields:
        n = field.name.lower()
        if not n in field_map:
            field_map[n] = field
        n = str(field).lower()
        if not n in field_map:
            field_map[n] = field
    constants = {}
    i = 0
    while True:
        m = re.search(REGEX_CONST_STRING, text)
        if not m:
            break
        text = "%s#%i%s" % (text[: m.start()], i, text[m.end() :])
        constants[str(i)] = m.group()[1:-1]
        i += 1
    text = re.sub(r"\s+", " ", text).lower()
    for a, b in [
        ("&", "and"),
        ("|", "or"),
        ("~", "not"),
        ("==", "="),
        ("<", "<"),
        (">", ">"),
        ("<=", "<="),
        (">=", ">="),
        ("<>", "!="),
        ("=<", "<="),
        ("=>", ">="),
        ("=", "="),
        (" less or equal than ", "<="),
        (" greater or equal than ", ">="),
        (" equal or less than ", "<="),
        (" equal or greater than ", ">="),
        (" less or equal ", "<="),
        (" greater or equal ", ">="),
        (" equal or less ", "<="),
        (" equal or greater ", ">="),
        (" not equal to ", "!="),
        (" not equal ", "!="),
        (" equal to ", "="),
        (" equal ", "="),
        (" equals ", "="),
        (" less than ", "<"),
        (" greater than ", ">"),
        (" starts with ", "startswith"),
        (" ends with ", "endswith"),
        (" not in ", "notbelongs"),
        (" in ", "belongs"),
        (" is ", "="),
    ]:
        if a[0] == " ":
            text = text.replace(" is" + a, " %s " % b)
        text = text.replace(a, " %s " % b)
    text = re.sub(r"\s+", " ", text).lower()
    text = re.sub(r"(?P<a>[\<\>\!\=])\s+(?P<b>[\<\>\!\=])",
                  r"\g<a>\g<b>", text)
    query = field = neg = op = logic = None
    for item in text.split():
        if field is None:
            if item == "not":
                neg = True
            elif not neg and not logic and item in ("and", "or"):
                logic = item
            elif item in field_map:
                field = field_map[item]
            else:
                raise RuntimeError("Invalid syntax")
        elif not field is None and op is None:
            op = item
        elif not op is None:
            if item.startswith("#"):
                if not item[1:] in constants:
                    raise RuntimeError("Invalid syntax")
                value = constants[item[1:]]
            else:
                value = item
                if field.type in ("text", "string", "json"):
                    if op == "=":
                        op = "like"
            if op == "=":
                new_query = field == value
            elif op == "<":
                new_query = field < value
            elif op == ">":
                new_query = field > value
            elif op == "<=":
                new_query = field <= value
            elif op == ">=":
                new_query = field >= value
            elif op == "!=":
                new_query = field != value
            elif op == "belongs":
                new_query = field.belongs(value.split(","))
            elif op == "notbelongs":
                new_query = ~field.belongs(value.split(","))
            elif field.type == "list:string":
                if op == "contains":
                    new_query = field.contains(value)
                else:
                    raise RuntimeError("Invalid operation")
            elif field.type in ("text", "string", "json", "upload"):
                if op == "contains":
                    new_query = field.contains(value)
                elif op == "like":
                    new_query = field.ilike(value)
                elif op == "startswith":
                    new_query = field.startswith(value)
                elif op == "endswith":
                    new_query = field.endswith(value)
                else:
                    raise RuntimeError("Invalid operation")
            elif field._db._adapter.dbengine == "google:datastore" and field.type in (
                "list:integer",
                "list:string",
                "list:reference",
            ):
                if op == "contains":
                    new_query = field.contains(value)
                else:
                    raise RuntimeError("Invalid operation")
            else:
                raise RuntimeError("Invalid operation")
            if neg:
                new_query = ~new_query
            if query is None:
                query = new_query
            elif logic == "and":
                query &= new_query
            elif logic == "or":
                query |= new_query
            field = op = neg = logic = None
    return query def auto_validators(field):
    db = field.db
    field_type = field.type
    #: don't apply default validation on custom types
    if isinstance(field_type, SQLCustomType):
        if hasattr(field_type, "validator"):
            return field_type.validator
        else:
            field_type = field_type.type
    elif not isinstance(field_type, str):
        return []
    #: if a custom method is provided, call it
    if callable(db.validators_method):
        return db.validators_method(field)
    #: apply validators from validators dict if present
    if not db.validators or not isinstance(db.validators, dict):
        return []
    field_validators = db.validators.get(field_type, [])
    if not isinstance(field_validators, (list, tuple)):
        field_validators = [field_validators]
    return field_validators def auto_represent(field):
    if field.represent:
        return field.represent
    if (
        field.db
        and field.type.startswith("reference")
        and field.type.find(".") < 0
        and field.type[10:] in field.db.tables
    ):
        referenced = field.db[field.type[10:]]
        return _repr_ref(referenced)
    elif (
        field.db
        and field.type.startswith("list:reference")
        and field.type.find(".") < 0
        and field.type[15:] in field.db.tables
    ):
        referenced = field.db[field.type[15:]]
        return _repr_ref_list(referenced)
    return field.represent def uuidstr():
    return str(uuid.uuid4()) REGEX_PYTHON_KEYWORDS = re.compile(
    "^(False|True|and|as|assert|break|class|"
    "continue|def|del|elif|else|except|exec|finally|for|from|global|if|import|"
    "in|is|lambda|nonlocal|not|or|pass|print|raise|return|try|while|with|yield)$"
) REGEX_DBNAME = re.compile(r"^(\w+)(:\w+)*") class RestParser(object):
    def __init__(self, db):
        self.db = db

    def auto_table(self, table, base="", depth=0):
        patterns = []
        for field in self.db[table].fields:
            if base:
                tag = "%s/%s" % (base, field.replace("_", "-"))
            else:
                tag = "/%s/%s" % (table.replace("_", "-"), field.replace("_", "-"))
            f = self.db[table][field]
            if not f.readable:
                continue
            if f.type == "id" or "slug" in field or f.type.startswith("reference"):
                tag += "/{%s.%s}" % (table, field)
                patterns.append(tag)
                patterns.append(tag + "/:field")
            elif f.type.startswith("boolean"):
                tag += "/{%s.%s}" % (table, field)
                patterns.append(tag)
                patterns.append(tag + "/:field")
            elif f.type in ("float", "double", "integer", "bigint"):
                tag += "/{%s.%s.ge}/{%s.%s.lt}" % (table, field, table, field)
                patterns.append(tag)
                patterns.append(tag + "/:field")
            elif f.type.startswith("list:"):
                tag += "/{%s.%s.contains}" % (table, field)
                patterns.append(tag)
                patterns.append(tag + "/:field")
            elif f.type in ("date", "datetime"):
                tag += "/{%s.%s.year}" % (table, field)
                patterns.append(tag)
                patterns.append(tag + "/:field")
                tag += "/{%s.%s.month}" % (table, field)
                patterns.append(tag)
                patterns.append(tag + "/:field")
                tag += "/{%s.%s.day}" % (table, field)
                patterns.append(tag)
                patterns.append(tag + "/:field")
            if f.type in ("datetime", "time"):
                tag += "/{%s.%s.hour}" % (table, field)
                patterns.append(tag)
                patterns.append(tag + "/:field")
                tag += "/{%s.%s.minute}" % (table, field)
                patterns.append(tag)
                patterns.append(tag + "/:field")
                tag += "/{%s.%s.second}" % (table, field)
                patterns.append(tag)
                patterns.append(tag + "/:field")
            if depth > 0:
                for f in self.db[table]._referenced_by:
                    tag += "/%s[%s.%s]" % (table, f.tablename, f.name)
                    patterns.append(tag)
                    patterns += self.auto_table(table, base=tag, depth=depth - 1)
        return patterns

    def parse(self, patterns, args, vars, queries=None, nested_select=True):
        """
        Example:
            Use as::

                db.define_table('person',Field('name'),Field('info'))
                db.define_table('pet',
                    Field('ownedby',db.person),
                    Field('name'),Field('info')
                )

                @request.restful()
                def index():
                    def GET(*args,**vars):
                        patterns = [
                            "/friends[person]",
                            "/{person.name}/:field",
                            "/{person.name}/pets[pet.ownedby]",
                            "/{person.name}/pets[pet.ownedby]/{pet.name}",
                            "/{person.name}/pets[pet.ownedby]/{pet.name}/:field",
                            ("/dogs[pet]", db.pet.info=='dog'),
                            ("/dogs[pet]/{pet.name.startswith}", db.pet.info=='dog'),
                            ]
                        parser = db.parse_as_rest(patterns,args,vars)
                        if parser.status == 200:
                            return dict(content=parser.response)
                        else:
                            raise HTTP(parser.status,parser.error)

                    def POST(table_name,**vars):
                        if table_name == 'person':
                            return db.person.validate_and_insert(**vars)
                        elif table_name == 'pet':
                            return db.pet.validate_and_insert(**vars)
                        else:
                            raise HTTP(400)
                    return locals()
        """

        if patterns == "auto":
            patterns = []
            for table in self.db.tables:
                if not table.startswith("auth_"):
                    patterns.append("/%s[%s]" % (table, table))
                    patterns += self.auto_table(table, base="", depth=1)
        else:
            i = 0
            while i < len(patterns):
                pattern = patterns[i]
                if not isinstance(pattern, str):
                    pattern = pattern[0]
                tokens = pattern.split("/")
                if tokens[-1].startswith(":auto") and re.match(
                    REGEX_SQUARE_BRACKETS, tokens[-1]
                ):
                    new_patterns = self.auto_table(
                        tokens[-1][tokens[-1].find("[") + 1 : -1], "/".join(tokens[:-1])
                    )
                    patterns = patterns[:i] + new_patterns + patterns[i + 1 :]
                    i += len(new_patterns)
                else:
                    i += 1
        if "/".join(args) == "patterns":
            return self.db.Row(
                {"status": 200, "pattern": "list", "error": None, "response": patterns}
            )
        for pattern in patterns:
            basequery, exposedfields = None, []
            if isinstance(pattern, tuple):
                if len(pattern) == 2:
                    pattern, basequery = pattern
                elif len(pattern) > 2:
                    pattern, basequery, exposedfields = pattern[0:3]
            otable = table = None
            if not isinstance(queries, dict):
                dbset = self.db(queries)
                if basequery is not None:
                    dbset = dbset(basequery)
            i = 0
            tags = pattern[1:].split("/")
            if len(tags) != len(args):
                continue
            for tag in tags:
                if re.match(REGEX_SEARCH_PATTERN, tag):
                    tokens = tag[1:-1].split(".")
                    table, field = tokens[0], tokens[1]
                    if not otable or table == otable:
                        if len(tokens) == 2 or tokens[2] == "eq":
                            query = self.db[table][field] == args[i]
                        elif tokens[2] == "ne":
                            query = self.db[table][field] != args[i]
                        elif tokens[2] == "lt":
                            query = self.db[table][field] < args[i]
                        elif tokens[2] == "gt":
                            query = self.db[table][field] > args[i]
                        elif tokens[2] == "ge":
                            query = self.db[table][field] >= args[i]
                        elif tokens[2] == "le":
                            query = self.db[table][field] <= args[i]
                        elif tokens[2] == "year":
                            query = self.db[table][field].year() == args[i]
                        elif tokens[2] == "month":
                            query = self.db[table][field].month() == args[i]
                        elif tokens[2] == "day":
                            query = self.db[table][field].day() == args[i]
                        elif tokens[2] == "hour":
                            query = self.db[table][field].hour() == args[i]
                        elif tokens[2] == "minute":
                            query = self.db[table][field].minutes() == args[i]
                        elif tokens[2] == "second":
                            query = self.db[table][field].seconds() == args[i]
                        elif tokens[2] == "startswith":
                            query = self.db[table][field].startswith(args[i])
                        elif tokens[2] == "contains":
                            query = self.db[table][field].contains(args[i])
                        else:
                            raise RuntimeError("invalid pattern: %s" % pattern)
                        if len(tokens) == 4 and tokens[3] == "not":
                            query = ~query
                        elif len(tokens) >= 4:
                            raise RuntimeError("invalid pattern: %s" % pattern)
                        if not otable and isinstance(queries, dict):
                            dbset = self.db(queries[table])
                            if basequery is not None:
                                dbset = dbset(basequery)
                        dbset = dbset(query)
                    else:
                        raise RuntimeError("missing relation in pattern: %s" % pattern)
                elif (
                    re.match(REGEX_SQUARE_BRACKETS, tag)
                    and args[i] == tag[: tag.find("[")]
                ):
                    ref = tag[tag.find("[") + 1 : -1]
                    if "." in ref and otable:
                        table, field = ref.split(".")
                        selfld = "_id"
                        if self.db[table][field].type.startswith("reference "):
                            refs = [
                                x.name
                                for x in self.db[otable]
                                if x.type == self.db[table][field].type
                            ]
                        else:
                            refs = [
                                x.name
                                for x in self.db[table]._referenced_by
                                if x.tablename == otable
                            ]
                        if refs:
                            selfld = refs[0]
                        if nested_select:
                            try:
                                dbset = self.db(
                                    self.db[table][field].belongs(
                                        dbset._select(self.db[otable][selfld])
                                    )
                                )
                            except ValueError:
                                return self.db.Row(
                                    {
                                        "status": 400,
                                        "pattern": pattern,
                                        "error": "invalid path",
                                        "response": None,
                                    }
                                )
                        else:
                            items = [
                                item.id
                                for item in dbset.select(self.db[otable][selfld])
                            ]
                            dbset = self.db(self.db[table][field].belongs(items))
                    else:
                        table = ref
                        if not otable and isinstance(queries, dict):
                            dbset = self.db(queries[table])
                        dbset = dbset(self.db[table])
                elif tag == ":field" and table:
                    # print 're3:'+tag
                    field = args[i]
                    if field not in self.db[table]:
                        break
                    # hand-built patterns should respect .readable=False as well
                    if not self.db[table][field].readable:
                        return self.db.Row(
                            {
                                "status": 418,
                                "pattern": pattern,
                                "error": "I'm a teapot",
                                "response": None,
                            }
                        )
                    try:
                        distinct = vars.get("distinct", False) == "True"
                        offset = to_num(vars.get("offset", None) or 0)
                        limits = (
                            offset,
                            to_num(vars.get("limit", None) or 1000) + offset,
                        )
                    except ValueError:
                        return self.db.Row(
                            {"status": 400, "error": "invalid limits", "response": None}
                        )
                    items = dbset.select(
                        self.db[table][field], distinct=distinct, limitby=limits
                    )
                    if items:
                        return self.db.Row(
                            {"status": 200, "response": items, "pattern": pattern}
                        )
                    else:
                        return self.db.Row(
                            {
                                "status": 404,
                                "pattern": pattern,
                                "error": "no record found",
                                " response": None,
                            }
                        )
                elif tag != args[i]:
                    break
                otable = table
                i += 1
                if i == len(tags) and table:
                    if hasattr(self.db[table], "_id"):
                        ofields = vars.get("order", self.db[table]._id.name).split("|")
                    else:
                        ofields = vars.get(
                            "order", self.db[table]._primarykey[0]
                        ).split("|")
                    try:
                        orderby = [
                            self.db[table][f]
                            if not f.startswith("~")
                            else ~self.db[table][f[1:]]
                            for f in ofields
                        ]
                    except (KeyError, AttributeError):
                        return self.db.Row(
                            {
                                "status": 400,
                                "error": "invalid orderby",
                                "response": None,
                            }
                        )
                    if exposedfields:
                        fields = [
                            field
                            for field in self.db[table]
                            if str(field).split(".")[-1] in exposedfields
                            and field.readable
                        ]
                    else:
                        fields = [field for field in self.db[table] if field.readable]
                    count = dbset.count()
                    try:
                        offset = to_num(vars.get("offset", None) or 0)
                        limits = (
                            offset,
                            to_num(vars.get("limit", None) or 1000) + offset,
                        )
                    except ValueError:
                        return self.db.Row(
                            {
                                "status": 400,
                                "error": " invalid limits",
                                "response": None,
                            }
                        )
                    try:
                        response = dbset.select(
                            limitby=limits, orderby=orderby, *fields
                        )
                    except ValueError:
                        return self.db.Row(
                            {
                                "status": 400,
                                "pattern": pattern,
                                "error": "invalid path",
                                "response": None,
                            }
                        )
                    return self.db.Row(
                        {
                            "status": 200,
                            "response": response,
                            "pattern": pattern,
                            "count": count,
                        }
                    )
        return self.db.Row(
            {"status": 400, "error": "no matching pattern", "response": None}
        ) class Serializers(object):
    def _json_parse(self, o):
    def __getattr__(self, name):
    def json(self, value):
    def yaml(self, value): class Table(Serializable, BasicStorage):

    """
    Represents a database table

    Example::
        You can create a table as::
            db = DAL(...)
            db.define_table('users', Field('name'))

        And then::

            db.users.insert(name='me') # print db.users._insert(...) to see SQL
            db.users.drop()

    """

    def __init__(self, db, tablename, *fields, **args):
        """
        Initializes the table and performs checking on the provided fields.

        Each table will have automatically an 'id'.

        If a field is of type Table, the fields (excluding 'id') from that table
        will be used instead.

        Raises:
            SyntaxError: when a supplied field is of incorrect type.
        """
        # import DAL here to avoid circular imports
        from .base import DAL

        super(Table, self).__init__()
        self._actual = False  # set to True by define_table()
        self._db = db
        self._migrate = None
        self._tablename = self._dalname = tablename
        if (
            not isinstance(tablename, str)
            or hasattr(DAL, tablename)
            or not REGEX_VALID_TB_FLD.match(tablename)
            or REGEX_PYTHON_KEYWORDS.match(tablename)
        ):
            raise SyntaxError(
                "Field: invalid table name: %s, "
                'use rname for "funny" names' % tablename
            )
        self._rname = args.get("rname") or db and db._adapter.dialect.quote(tablename)
        self._raw_rname = args.get("rname") or db and tablename
        self._sequence_name = (
            args.get("sequence_name")
            or db
            and db._adapter.dialect.sequence_name(self._raw_rname)
        )
        self._trigger_name = (
            args.get("trigger_name")
            or db
            and db._adapter.dialect.trigger_name(tablename)
        )
        self._common_filter = args.get("common_filter")
        self._format = args.get("format")
        self._singular = args.get("singular", tablename.replace("_", " ").capitalize())
        self._plural = args.get("plural")
        # horrible but for backward compatibility of appadmin
        if "primarykey" in args and args["primarykey"] is not None:
            self._primarykey = args.get("primarykey")

        self._before_insert = [attempt_upload_on_insert(self)]
        self._before_update = [delete_uploaded_files, attempt_upload_on_update(self)]
        self._before_delete = [delete_uploaded_files]
        self._after_insert = []
        self._after_update = []
        self._after_delete = []

        self._virtual_fields = []
        self._virtual_methods = []

        self.add_method = MethodAdder(self)

        fieldnames = set()
        newfields = []
        _primarykey = getattr(self, "_primarykey", None)
        if _primarykey is not None:
            if not isinstance(_primarykey, list):
                raise SyntaxError(
                    "primarykey must be a list of fields from table '%s'" % tablename
                )
            if len(_primarykey) == 1:
                self._id = [
                    f
                    for f in fields
                    if isinstance(f, Field) and f.name == _primarykey[0]
                ][0]
        elif not [
            f
            for f in fields
            if (isinstance(f, Field) and f.type == "id")
            or (isinstance(f, dict) and f.get("type", None) == "id")
        ]:
            field = Field("id", "id")
            newfields.append(field)
            fieldnames.add("id")
            self._id = field

        virtual_fields = []

        def include_new(field):
            newfields.append(field)
            fieldnames.add(field.name)
            if field.type == "id":
                self._id = field

        for field in fields:
            if isinstance(field, (FieldVirtual, FieldMethod)):
                virtual_fields.append(field)
            elif isinstance(field, Field) and field.name not in fieldnames:
                if field.db is not None:
                    field = copy.copy(field)
                include_new(field)
            elif isinstance(field, (list, tuple)):
                for other in field:
                    include_new(other)
            elif isinstance(field, Table):
                table = field
                for field in table:
                    if field.name not in fieldnames and field.type != "id":
                        t2 = not table._actual and self._tablename
                        include_new(field.clone(point_self_references_to=t2))
            elif isinstance(field, dict) and field["fieldname"] not in fieldnames:
                include_new(Field(**field))
            elif not isinstance(field, (Field, Table)):
                raise SyntaxError(
                    "define_table argument is not a Field, Table of list: %s" % field
                )
        fields = newfields
        self._fields = SQLCallableList()
        self.virtualfields = []

        if db and db._adapter.uploads_in_blob is True:
            uploadfields = [f.name for f in fields if f.type == "blob"]
            for field in fields:
                fn = field.uploadfield
                if (
                    isinstance(field, Field)
                    and field.type == "upload"
                    and fn is True
                    and not field.uploadfs
                ):
                    fn = field.uploadfield = "%s_blob" % field.name
                if (
                    isinstance(fn, str)
                    and fn not in uploadfields
                    and not field.uploadfs
                ):
                    fields.append(
                        Field(fn, "blob", default="", writable=False, readable=False)
                    )

        fieldnames_set = set()
        reserved = dir(Table) + ["fields"]
        if db and db._check_reserved:
            check_reserved_keyword = db.check_reserved_keyword
        else:

            def check_reserved_keyword(field_name):
                if field_name in reserved:
                    raise SyntaxError("field name %s not allowed" % field_name)

        for field in fields:
            field_name = field.name
            check_reserved_keyword(field_name)
            if db and db._ignore_field_case:
                fname_item = field_name.lower()
            else:
                fname_item = field_name
            if fname_item in fieldnames_set:
                raise SyntaxError(
                    "duplicate field %s in table %s" % (field_name, tablename)
                )
            else:
                fieldnames_set.add(fname_item)

            self.fields.append(field_name)
            self[field_name] = field
            if field.type == "id":
                self["id"] = field
            field.bind(self)
        self.ALL = SQLALL(self)

        if _primarykey is not None:
            for k in _primarykey:
                if k not in self.fields:
                    raise SyntaxError(
                        "primarykey must be a list of fields from table '%s "
                        % tablename
                    )
                else:
                    self[k].notnull = True
        for field in virtual_fields:
            self[field.name] = field

    @property
    def fields(self):
        return self._fields

    def _structure(self):
        keys = [
            "name",
            "type",
            "writable",
            "listable",
            "searchable",
            "regex",
            "options",
            "default",
            "label",
            "unique",
            "notnull",
            "required",
        ]

        def noncallable(obj):
            return obj if not callable(obj) else None

        return [
            {key: noncallable(getattr(field, key)) for key in keys}
            for field in self
            if field.readable and not field.type == "password"
        ]

    @cachedprop
    def _upload_fieldnames(self):
        return set(field.name for field in self if field.type == "upload")

    def update(self, *args, **kwargs):
        raise RuntimeError("Syntax Not Supported")

    def _enable_record_versioning(
        self,
        archive_db=None,
        archive_name="%(tablename)s_archive",
        is_active="is_active",
        current_record="current_record",
        current_record_label=None,
        migrate=None,
        redefine=None,
    ):
        db = self._db
        archive_db = archive_db or db
        archive_name = archive_name % dict(tablename=self._dalname)
        if archive_name in archive_db.tables():
            return  # do not try define the archive if already exists
        fieldnames = self.fields()
        same_db = archive_db is db
        field_type = self if same_db else "bigint"
        clones = []
        for field in self:
            nfk = same_db or not field.type.startswith("reference")
            clones.append(
                field.clone(unique=False, type=field.type if nfk else "bigint")
            )

        d = dict(format=self._format)
        if migrate:
            d["migrate"] = migrate
        elif isinstance(self._migrate, basestring):
            d["migrate"] = self._migrate + "_archive"
        elif self._migrate:
            d["migrate"] = self._migrate
        if redefine:
            d["redefine"] = redefine
        archive_db.define_table(
            archive_name,
            Field(current_record, field_type, label=current_record_label),
            *clones,
            **d
        )

        self._before_update.append(
            lambda qset, fs, db=archive_db, an=archive_name, cn=current_record: archive_record(
                qset, fs, db[an], cn
            )
        )
        if is_active and is_active in fieldnames:
            self._before_delete.append(lambda qset: qset.update(is_active=False))
            newquery = lambda query, t=self, name=self._tablename: reduce(
                AND,
                [
                    tab.is_active == True
                    for tab in db._adapter.tables(query).values()
                    if tab._raw_rname == self._raw_rname
                ],
            )
            query = self._common_filter
            if query:
                self._common_filter = lambda q: reduce(AND, [query(q), newquery(q)])
            else:
                self._common_filter = newquery

    def _validate(self, **vars):
        errors = Row()
        for key, value in iteritems(vars):
            value, error = getattr(self, key).validate(value, vars.get("id"))
            if error:
                errors[key] = error
        return errors

    def _create_references(self):
        db = self._db
        pr = db._pending_references
        self._referenced_by_list = []
        self._referenced_by = []
        self._references = []
        for field in self:
            # fieldname = field.name  #FIXME not used ?
            field_type = field.type
            if isinstance(field_type, str) and (
                field_type.startswith("reference ")
                or field_type.startswith("list:reference ")
            ):

                is_list = field_type[:15] == "list:reference "
                if is_list:
                    ref = field_type[15:].strip()
                else:
                    ref = field_type[10:].strip()

                if not ref:
                    SyntaxError("Table: reference to nothing: %s" % ref)
                if "." in ref:
                    rtablename, throw_it, rfieldname = ref.partition(".")
                else:
                    rtablename, rfieldname = ref, None
                if rtablename not in db:
                    pr[rtablename] = pr.get(rtablename, []) + [field]
                    continue
                rtable = db[rtablename]
                if rfieldname:
                    if not hasattr(rtable, "_primarykey"):
                        raise SyntaxError(
                            "keyed tables can only reference other keyed tables (for now)"
                        )
                    if rfieldname not in rtable.fields:
                        raise SyntaxError(
                            "invalid field '%s' for referenced table '%s'"
                            " in table '%s'" % (rfieldname, rtablename, self._tablename)
                        )
                    rfield = rtable[rfieldname]
                else:
                    rfield = rtable._id
                if is_list:
                    rtable._referenced_by_list.append(field)
                else:
                    rtable._referenced_by.append(field)
                field.referent = rfield
                self._references.append(field)
            else:
                field.referent = None
        if self._tablename in pr:
            referees = pr.pop(self._tablename)
            for referee in referees:
                if referee.type.startswith("list:reference "):
                    self._referenced_by_list.append(referee)
                else:
                    self._referenced_by.append(referee)

    def _filter_fields(self, record, id=False):
        return dict(
            [
                (k, v)
                for (k, v) in iteritems(record)
                if k in self.fields and (getattr(self, k).type != "id" or id)
            ]
        )

    def _build_query(self, key):
        """ for keyed table only """
        query = None
        for k, v in iteritems(key):
            if k in self._primarykey:
                if query:
                    query = query & (getattr(self, k) == v)
                else:
                    query = getattr(self, k) == v
            else:
                raise SyntaxError(
                    "Field %s is not part of the primary key of %s"
                    % (k, self._tablename)
                )
        return query

    def __getitem__(self, key):
        if str(key).isdigit() or (Key is not None and isinstance(key, Key)):
            # non negative key or gae
            return (
                self._db(self._id == str(key))
                .select(limitby=(0, 1), orderby_on_limitby=False)
                .first()
            )
        elif isinstance(key, dict):
            # keyed table
            query = self._build_query(key)
            return (
                self._db(query).select(limitby=(0, 1), orderby_on_limitby=False).first()
            )
        elif key is not None:
            try:
                return getattr(self, key)
            except:
                raise KeyError(key)

    def __call__(self, key=DEFAULT, **kwargs):
        for_update = kwargs.get("_for_update", False)
        if "_for_update" in kwargs:
            del kwargs["_for_update"]

        orderby = kwargs.get("_orderby", None)
        if "_orderby" in kwargs:
            del kwargs["_orderby"]

        if key is not DEFAULT:
            if isinstance(key, Query):
                record = (
                    self._db(key)
                    .select(
                        limitby=(0, 1),
                        for_update=for_update,
                        orderby=orderby,
                        orderby_on_limitby=False,
                    )
                    .first()
                )
            elif not str(key).isdigit():
                record = None
            else:
                record = (
                    self._db(self._id == key)
                    .select(
                        limitby=(0, 1),
                        for_update=for_update,
                        orderby=orderby,
                        orderby_on_limitby=False,
                    )
                    .first()
                )
            if record:
                for k, v in iteritems(kwargs):
                    if record[k] != v:
                        return None
            return record
        elif kwargs:
            query = reduce(
                lambda a, b: a & b,
                [getattr(self, k) == v for k, v in iteritems(kwargs)],
            )
            return (
                self._db(query)
                .select(
                    limitby=(0, 1),
                    for_update=for_update,
                    orderby=orderby,
                    orderby_on_limitby=False,
                )
                .first()
            )
        else:
            return None

    def __setitem__(self, key, value):
        if key is None:
            # table[None] = value (shortcut for insert)
            self.insert(**self._filter_fields(value))
        elif str(key).isdigit():
            # table[non negative key] = value (shortcut for update)
            if not self._db(self._id == key).update(**self._filter_fields(value)):
                raise SyntaxError("No such record: %s" % key)
        elif isinstance(key, dict):
            # keyed table
            if not isinstance(value, dict):
                raise SyntaxError("value must be a dictionary: %s" % value)
            if set(key.keys()) == set(self._primarykey):
                value = self._filter_fields(value)
                kv = {}
                kv.update(value)
                kv.update(key)
                if not self.insert(**kv):
                    query = self._build_query(key)
                    self._db(query).update(**self._filter_fields(value))
            else:
                raise SyntaxError(
                    "key must have all fields from primary key: %s" % self._primarykey
                )
        else:
            if isinstance(value, FieldVirtual):
                value.bind(self, str(key))
                self._virtual_fields.append(value)
            elif isinstance(value, FieldMethod):
                value.bind(self, str(key))
                self._virtual_methods.append(value)
            self.__dict__[str(key)] = value

    def __setattr__(self, key, value):
        if key[:1] != "_" and key in self:
            raise SyntaxError("Object exists and cannot be redefined: %s" % key)
        self[key] = value

    def __delitem__(self, key):
        if isinstance(key, dict):
            query = self._build_query(key)
            if not self._db(query).delete():
                raise SyntaxError("No such record: %s" % key)
        elif not str(key).isdigit() or not self._db(self._id == key).delete():
            raise SyntaxError("No such record: %s" % key)

    def __iter__(self):
        for fieldname in self.fields:
            yield getattr(self, fieldname)

    def __repr__(self):
        return "<Table %s (%s)>" % (self._tablename, ", ".join(self.fields()))

    def __str__(self):
        if self._tablename == self._dalname:
            return self._tablename
        return self._db._adapter.dialect._as(self._dalname, self._tablename)

    @property
    @deprecated("sqlsafe", "sql_shortref", "Table")
    def sqlsafe(self):
        return self.sql_shortref

    @property
    @deprecated("sqlsafe_alias", "sql_fullref", "Table")
    def sqlsafe_alias(self):
        return self.sql_fullref

    @property
    def sql_shortref(self):
        if self._tablename == self._dalname:
            return self._rname
        return self._db._adapter.sqlsafe_table(self._tablename)

    @property
    def sql_fullref(self):
        if self._tablename == self._dalname:
            if self._db._adapter.dbengine == "oracle":
                return self._db._adapter.dialect.quote(self._rname)
            return self._rname
        return self._db._adapter.sqlsafe_table(self._tablename, self._rname)

    def query_name(self, *args, **kwargs):
        return (self.sql_fullref,)

    def _drop(self, mode=""):
        return self._db._adapter.dialect.drop_table(self, mode)

    def drop(self, mode=""):
        return self._db._adapter.drop_table(self, mode)

    def _filter_fields_for_operation(self, fields):
        new_fields = {}  # format: new_fields[name] = (field, value)
        input_fieldnames = set(fields)
        table_fieldnames = set(self.fields)
        empty_fieldnames = OrderedDict((name, name) for name in self.fields)
        for name in list(input_fieldnames & table_fieldnames):
            field = getattr(self, name)
            value = field.filter_in(fields[name]) if field.filter_in else fields[name]
            new_fields[name] = (field, value)
            del empty_fieldnames[name]
        return list(empty_fieldnames), new_fields

    def _compute_fields_for_operation(self, fields, to_compute):
        row = OpRow(self)
        for name, tup in iteritems(fields):
            field, value = tup
            if isinstance(
                value,
                (
                    types.LambdaType,
                    types.FunctionType,
                    types.MethodType,
                    types.BuiltinFunctionType,
                    types.BuiltinMethodType,
                ),
            ):
                value = value()
            row.set_value(name, value, field)
        for name, field in to_compute:
            try:
                row.set_value(name, field.compute(row), field)
            except (KeyError, AttributeError):
                # error silently unless field is required!
                if field.required and name not in fields:
                    raise RuntimeError("unable to compute required field: %s" % name)
        return row

    def _fields_and_values_for_insert(self, fields):
        empty_fieldnames, new_fields = self._filter_fields_for_operation(fields)
        to_compute = []
        for name in empty_fieldnames:
            field = getattr(self, name)
            if field.compute:
                to_compute.append((name, field))
            elif field.default is not None:
                new_fields[name] = (field, field.default)
            elif field.required:
                raise RuntimeError("Table: missing required field: %s" % name)
        return self._compute_fields_for_operation(new_fields, to_compute)

    def _fields_and_values_for_update(self, fields):
        empty_fieldnames, new_fields = self._filter_fields_for_operation(fields)
        to_compute = []
        for name in empty_fieldnames:
            field = getattr(self, name)
            if field.compute:
                to_compute.append((name, field))
            if field.update is not None:
                new_fields[name] = (field, field.update)
        return self._compute_fields_for_operation(new_fields, to_compute)

    def _insert(self, **fields):
        row = self._fields_and_values_for_insert(fields)
        return self._db._adapter._insert(self, row.op_values())

    def insert(self, **fields):
        row = self._fields_and_values_for_insert(fields)
        if any(f(row) for f in self._before_insert):
            return 0
        ret = self._db._adapter.insert(self, row.op_values())
        if ret and self._after_insert:
            for f in self._after_insert:
                f(row, ret)
        return ret

    def _validate_fields(self, fields, defattr="default", id=None):
        from .validators import CRYPT

        response = Row()
        response.id, response.errors, new_fields = None, Row(), Row()
        for field in self:
            # we validate even if not passed in case it is required
            error = default = None
            if not field.required and not field.compute:
                default = getattr(field, defattr)
                if callable(default):
                    default = default()
            if not field.compute:
                ovalue = fields.get(field.name, default)
                value, error = field.validate(ovalue, id)
            if error:
                response.errors[field.name] = "%s" % error
            elif field.type == "password" and ovalue == CRYPT.STARS:
                pass
            elif field.name in fields:
                # only write if the field was passed and no error
                new_fields[field.name] = value
        return response, new_fields

    def validate_and_insert(self, **fields):
        response, new_fields = self._validate_fields(fields, "default")
        if not response.errors:
            response.id = self.insert(**new_fields)
        return response

    def validate_and_update(self, _key, **fields):
        record = self(**_key) if isinstance(_key, dict) else self(_key)
        response, new_fields = self._validate_fields(fields, "update", record.id)
        #: do the update
        if not response.errors and record:
            if "_id" in self:
                myset = self._db(self._id == record[self._id.name])
            else:
                query = None
                for key, value in iteritems(_key):
                    if query is None:
                        query = getattr(self, key) == value
                    else:
                        query = query & (getattr(self, key) == value)
                myset = self._db(query)
            response.updated = myset.update(**new_fields)
        if record:
            response.id = record.id
        return response

    def update_or_insert(self, _key=DEFAULT, **values):
        if _key is DEFAULT:
            record = self(**values)
        elif isinstance(_key, dict):
            record = self(**_key)
        else:
            record = self(_key)
        if record:
            record.update_record(**values)
            newid = None
        else:
            newid = self.insert(**values)
        return newid

    def validate_and_update_or_insert(self, _key=DEFAULT, **fields):
        if _key is DEFAULT or _key == "":
            primary_keys = {}
            for key, value in iteritems(fields):
                if key in self._primarykey:
                    primary_keys[key] = value
            if primary_keys != {}:
                record = self(**primary_keys)
                _key = primary_keys
            else:
                required_keys = {}
                for key, value in iteritems(fields):
                    if getattr(self, key).required:
                        required_keys[key] = value
                record = self(**required_keys)
                _key = required_keys
        elif isinstance(_key, dict):
            record = self(**_key)
        else:
            record = self(_key)

        if record:
            response = self.validate_and_update(_key, **fields)
            if hasattr(self, "_primarykey"):
                primary_keys = {}
                for key in self._primarykey:
                    primary_keys[key] = getattr(record, key)
                response.id = primary_keys
        else:
            response = self.validate_and_insert(**fields)
        return response

    def bulk_insert(self, items):
        """
        here items is a list of dictionaries
        """
        data = [self._fields_and_values_for_insert(item) for item in items]
        if any(f(el) for el in data for f in self._before_insert):
            return 0
        ret = self._db._adapter.bulk_insert(self, [el.op_values() for el in data])
        ret and [
            [f(el, ret[k]) for k, el in enumerate(data)] for f in self._after_insert
        ]
        return ret

    def _truncate(self, mode=""):
        return self._db._adapter.dialect.truncate(self, mode)

    def truncate(self, mode=""):
        return self._db._adapter.truncate(self, mode)

    def import_from_csv_file(
        self,
        csvfile,
        id_map=None,
        null="<NULL>",
        unique="uuid",
        id_offset=None,  # id_offset used only when id_map is None
        transform=None,
        validate=False,
        encoding="utf-8",
        **kwargs
    ):
        """
        Import records from csv file.
        Column headers must have same names as table fields.
        Field 'id' is ignored.
        If column names read 'table.file' the 'table.' prefix is ignored.

        - 'unique' argument is a field which must be unique (typically a
          uuid field)
        - 'restore' argument is default False; if set True will remove old values
          in table first.
        - 'id_map' if set to None will not map ids

        The import will keep the id numbers in the restored table.
        This assumes that there is a field of type id that is integer and in
        incrementing order.
        Will keep the id numbers in restored table.
        """
        if validate:
            inserting = self.validate_and_insert
        else:
            inserting = self.insert

        delimiter = kwargs.get("delimiter", ",")
        quotechar = kwargs.get("quotechar", '"')
        quoting = kwargs.get("quoting", csv.QUOTE_MINIMAL)
        restore = kwargs.get("restore", False)
        if restore:
            self._db[self].truncate()

        reader = csv_reader(
            csvfile,
            delimiter=delimiter,
            encoding=encoding,
            quotechar=quotechar,
            quoting=quoting,
        )
        colnames = None
        if isinstance(id_map, dict):
            if self._tablename not in id_map:
                id_map[self._tablename] = {}
            id_map_self = id_map[self._tablename]

        def fix(field, value, id_map, id_offset):
            list_reference_s = "list:reference"
            if value == null:
                value = None
            elif field.type == "blob":
                value = base64.b64decode(value)
            elif field.type == "double" or field.type == "float":
                if not value.strip():
                    value = None
                else:
                    value = float(value)
            elif field.type in ("integer", "bigint"):
                if not value.strip():
                    value = None
                else:
                    value = long(value)
            elif field.type.startswith("list:string"):
                value = bar_decode_string(value)
            elif field.type.startswith(list_reference_s):
                ref_table = field.type[len(list_reference_s) :].strip()
                if id_map is not None:
                    value = [
                        id_map[ref_table][long(v)] for v in bar_decode_string(value)
                    ]
                else:
                    value = [v for v in bar_decode_string(value)]
            elif field.type.startswith("list:"):
                value = bar_decode_integer(value)
            elif id_map and field.type.startswith("reference"):
                try:
                    value = id_map[field.type[9:].strip()][long(value)]
                except KeyError:
                    pass
            elif id_offset and field.type.startswith("reference"):
                try:
                    value = id_offset[field.type[9:].strip()] + long(value)
                except KeyError:
                    pass
            return value

        def is_id(colname):
            if colname in self:
                return getattr(self, colname).type == "id"
            else:
                return False

        first = True
        unique_idx = None
        for lineno, line in enumerate(reader):
            if not line:
                return
            if not colnames:
                # assume this is the first line of the input, contains colnames
                colnames = [x.split(".", 1)[-1] for x in line]

                cols, cid = {}, None
                for i, colname in enumerate(colnames):
                    if is_id(colname):
                        cid = colname
                    elif colname in self.fields:
                        cols[colname] = getattr(self, colname)
                    if colname == unique:
                        unique_idx = i
            elif len(line) == len(colnames):
                # every other line contains instead data
                items = dict(zip(colnames, line))
                if transform:
                    items = transform(items)

                ditems = dict()
                csv_id = None
                for field in self:
                    fieldname = field.name
                    if fieldname in items:
                        try:
                            value = fix(field, items[fieldname], id_map, id_offset)
                            if field.type != "id":
                                ditems[fieldname] = value
                            else:
                                csv_id = long(value)
                        except ValueError:
                            raise RuntimeError("Unable to parse line:%s" % (lineno + 1))
                if not (id_map or csv_id is None or id_offset is None or unique_idx):
                    curr_id = inserting(**ditems)
                    if first:
                        first = False
                        # First curr_id is bigger than csv_id,
                        # then we are not restoring but
                        # extending db table with csv db table
                        id_offset[self._tablename] = (
                            (curr_id - csv_id) if curr_id > csv_id else 0
                        )
                    # create new id until we get the same as old_id+offset
                    while curr_id < csv_id + id_offset[self._tablename]:
                        self._db(getattr(self, cid) == curr_id).delete()
                        curr_id = inserting(**ditems)
                # Validation. Check for duplicate of 'unique' &,
                # if present, update instead of insert.
                elif not unique_idx:
                    new_id = inserting(**ditems)
                else:
                    unique_value = line[unique_idx]
                    query = getattr(self, unique) == unique_value
                    record = self._db(query).select().first()
                    if record:
                        record.update_record(**ditems)
                        new_id = record[self._id.name]
                    else:
                        new_id = inserting(**ditems)
                if id_map and csv_id is not None:
                    id_map_self[csv_id] = new_id
            if lineno % 1000 == 999:
                self._db.commit()

    def as_dict(self, flat=False, sanitize=True):
        table_as_dict = dict(
            tablename=str(self),
            fields=[],
            sequence_name=self._sequence_name,
            trigger_name=self._trigger_name,
            common_filter=self._common_filter,
            format=self._format,
            singular=self._singular,
            plural=self._plural,
        )

        for field in self:
            if (field.readable or field.writable) or (not sanitize):
                table_as_dict["fields"].append(
                    field.as_dict(flat=flat, sanitize=sanitize)
                )
        return table_as_dict

    def with_alias(self, alias):
        try:
            if self._db[alias]._rname == self._rname:
                return self._db[alias]
        except AttributeError:  # we never used this alias
            pass
        other = copy.copy(self)
        other["ALL"] = SQLALL(other)
        other["_tablename"] = alias
        for fieldname in other.fields:
            tmp = getattr(self, fieldname).clone()
            tmp.bind(other)
            other[fieldname] = tmp
        if "id" in self and "id" not in other.fields:
            other["id"] = other[self.id.name]
        other._id = other[self._id.name]
        setattr(self._db._aliased_tables, alias, other)
        return other

    def on(self, query):
        return Expression(self._db, self._db._adapter.dialect.on, self, query)

    def create_index(self, name, *fields, **kwargs):
        return self._db._adapter.create_index(self, name, *fields, **kwargs)

    def drop_index(self, name, if_exists = False):
        return self._db._adapter.drop_index(self, name, if_exists) class Field(Expression, Serializable):

    Virtual = FieldVirtual
    Method = FieldMethod
    Lazy = FieldMethod  # for backward compatibility

    """
    Represents a database field

    Example:
        Usage::

            a = Field(name, 'string', length=32, default=None, required=False,
                requires=IS_NOT_EMPTY(), ondelete='CASCADE',
                notnull=False, unique=False,
                regex=None, options=None,
                uploadfield=True, widget=None, label=None, comment=None,
                uploadfield=True, # True means store on disk,
                                  # 'a_field_name' means store in this field in db
                                  # False means file content will be discarded.
                writable=True, readable=True, searchable=True, listable=True,
                update=None, authorize=None,
                autodelete=False, represent=None, uploadfolder=None,
                uploadseparate=False # upload to separate directories by uuid_keys
                                     # first 2 character and tablename.fieldname
                                     # False - old behavior
                                     # True - put uploaded file in
                                     #   <uploaddir>/<tablename>.<fieldname>/uuid_key[:2]
                                     #        directory)
                uploadfs=None        # a pyfilesystem where to store upload
                )

    to be used as argument of `DAL.define_table`

    """

    def __init__(
        self,
        fieldname,
        type="string",
        length=None,
        default=DEFAULT,
        required=False,
        requires=DEFAULT,
        ondelete="CASCADE",
        notnull=False,
        unique=False,
        uploadfield=True,
        widget=None,
        label=None,
        comment=None,
        writable=True,
        readable=True,
        searchable=True,
        listable=True,
        regex=None,
        options=None,
        update=None,
        authorize=None,
        autodelete=False,
        represent=None,
        uploadfolder=None,
        uploadseparate=False,
        uploadfs=None,
        compute=None,
        custom_store=None,
        custom_retrieve=None,
        custom_retrieve_file_properties=None,
        custom_delete=None,
        filter_in=None,
        filter_out=None,
        custom_qualifier=None,
        map_none=None,
        rname=None,
        **others
    ):
        self._db = self.db = None  # both for backward compatibility
        self.table = self._table = None
        self.op = None
        self.first = None
        self.second = None
        if PY2 and isinstance(fieldname, unicode):
            try:
                fieldname = str(fieldname)
            except UnicodeEncodeError:
                raise SyntaxError("Field: invalid unicode field name")
        self.name = fieldname = cleanup(fieldname)
        if (
            not isinstance(fieldname, str)
            or hasattr(Table, fieldname)
            or not REGEX_VALID_TB_FLD.match(fieldname)
            or REGEX_PYTHON_KEYWORDS.match(fieldname)
        ):
            raise SyntaxError(
                "Field: invalid field name: %s, "
                'use rname for "funny" names' % fieldname
            )

        if not isinstance(type, (Table, Field)):
            self.type = type
        else:
            self.type = "reference %s" % type

        self.length = (
            length if length is not None else DEFAULTLENGTH.get(self.type, 512)
        )
        self.default = default if default is not DEFAULT else (update or None)
        self.required = required  # is this field required
        self.ondelete = ondelete.upper()  # this is for reference fields only
        self.notnull = notnull
        self.unique = unique
        # split to deal with decimal(,)
        self.regex = regex
        if not regex and isinstance(self.type, str):
            self.regex = DEFAULT_REGEX.get(self.type.split("(")[0])
        self.options = options
        self.uploadfield = uploadfield
        self.uploadfolder = uploadfolder
        self.uploadseparate = uploadseparate
        self.uploadfs = uploadfs
        self.widget = widget
        self.comment = comment
        self.writable = writable
        self.readable = readable
        self.searchable = searchable
        self.listable = listable
        self.update = update
        self.authorize = authorize
        self.autodelete = autodelete
        self.represent = (
            list_represent
            if represent is None and type in ("list:integer", "list:string")
            else represent
        )
        self.compute = compute
        self.isattachment = True
        self.custom_store = custom_store
        self.custom_retrieve = custom_retrieve
        self.custom_retrieve_file_properties = custom_retrieve_file_properties
        self.custom_delete = custom_delete
        self.filter_in = filter_in
        self.filter_out = filter_out
        self.custom_qualifier = custom_qualifier
        self.label = label if label is not None else fieldname.replace("_", " ").title()
        self.requires = requires if requires is not None else []
        self.map_none = map_none
        self._rname = self._raw_rname = rname
        stype = self.type
        if isinstance(self.type, SQLCustomType):
            stype = self.type.type
        self._itype = REGEX_TYPE.match(stype).group(0) if stype else None
        for key in others:
            setattr(self, key, others[key])

    def bind(self, table):
        if self._table is not None:
            raise ValueError("Field %s is already bound to a table" % self.longname)
        self.db = self._db = table._db
        self.table = self._table = table
        self.tablename = self._tablename = table._tablename
        if self._db and self._rname is None:
            self._rname = self._db._adapter.sqlsafe_field(self.name)
            self._raw_rname = self.name

    def set_attributes(self, *args, **attributes):
        self.__dict__.update(*args, **attributes)
        return self

    def clone(self, point_self_references_to=False, **args):
        field = copy.copy(self)
        if point_self_references_to and self.type == "reference %s" % self._tablename:
            field.type = "reference %s" % point_self_references_to
        field.__dict__.update(args)
        field.db = field._db = None
        field.table = field._table = None
        field.tablename = field._tablename = None
        if self._db and self._rname == self._db._adapter.sqlsafe_field(self.name):
            # Reset the name because it may need to be requoted by bind()
            field._rname = field._raw_rname = None
        return field

    def store(self, file, filename=None, path=None):
        # make sure filename is a str sequence
        filename = "{}".format(filename)
        if self.custom_store:
            return self.custom_store(file, filename, path)
        if isinstance(file, cgi.FieldStorage):
            filename = filename or file.filename
            file = file.file
        elif not filename:
            filename = file.name
        filename = os.path.basename(filename.replace("/", os.sep).replace("\\", os.sep))
        m = re.search(REGEX_UPLOAD_EXTENSION, filename)
        extension = m and m.group(1) or "txt"
        uuid_key = uuidstr().replace("-", "")[-16:]
        encoded_filename = to_unicode(base64.urlsafe_b64encode(to_bytes(filename)))
        # Fields that are not bound to a table use "tmp" as the table name
        tablename = getattr(self, "_tablename", "tmp")
        newfilename = "%s.%s.%s.%s" % (
            tablename,
            self.name,
            uuid_key,
            encoded_filename,
        )
        newfilename = (
            newfilename[: (self.length - 1 - len(extension))] + "." + extension
        )
        self_uploadfield = self.uploadfield
        if isinstance(self_uploadfield, Field):
            blob_uploadfield_name = self_uploadfield.uploadfield
            keys = {
                self_uploadfield.name: newfilename,
                blob_uploadfield_name: file.read(),
            }
            self_uploadfield.table.insert(**keys)
        elif self_uploadfield is True:
            if self.uploadfs:
                dest_file = self.uploadfs.open(text_type(newfilename), "wb")
            else:
                if path:
                    pass
                elif self.uploadfolder:
                    path = self.uploadfolder
                elif self.db is not None and self.db._adapter.folder:
                    path = pjoin(self.db._adapter.folder, "..", "uploads")
                    path = os.path.abspath(path)
                else:
                    raise RuntimeError(
                        "you must specify a Field(..., uploadfolder=...)"
                    )
                if self.uploadseparate:
                    if self.uploadfs:
                        raise RuntimeError("not supported")
                    path = pjoin(path, "%s.%s" % (tablename, self.name), uuid_key[:2])
                if not exists(path):
                    os.makedirs(path)
                pathfilename = pjoin(path, newfilename)
                dest_file = open(pathfilename, "wb")
            try:
                shutil.copyfileobj(file, dest_file)
            except IOError:
                raise IOError(
                    'Unable to store file "%s" because invalid permissions, '
                    "readonly file system, or filename too long" % pathfilename
                )
            dest_file.close()
        return newfilename

    def retrieve(self, name, path=None, nameonly=False):
        """
        If `nameonly==True` return (filename, fullfilename) instead of
        (filename, stream)
        """
        self_uploadfield = self.uploadfield
        if self.custom_retrieve:
            return self.custom_retrieve(name, path)
        if self.authorize or isinstance(self_uploadfield, str):
            row = self.db(self == name).select().first()
            if not row:
                raise NotFoundException
        if self.authorize and not self.authorize(row):
            raise NotAuthorizedException
        file_properties = self.retrieve_file_properties(name, path)
        filename = file_properties["filename"]
        if isinstance(self_uploadfield, str):  # ## if file is in DB
            stream = BytesIO(to_bytes(row[self_uploadfield] or ""))
        elif isinstance(self_uploadfield, Field):
            blob_uploadfield_name = self_uploadfield.uploadfield
            query = self_uploadfield == name
            data = self_uploadfield.table(query)[blob_uploadfield_name]
            stream = BytesIO(to_bytes(data))
        elif self.uploadfs:
            # ## if file is on pyfilesystem
            stream = self.uploadfs.open(text_type(name), "rb")
        else:
            # ## if file is on regular filesystem
            # this is intentionally a string with filename and not a stream
            # this propagates and allows stream_file_or_304_or_206 to be called
            fullname = pjoin(file_properties["path"], name)
            if nameonly:
                return (filename, fullname)
            stream = open(fullname, "rb")
        return (filename, stream)

    def retrieve_file_properties(self, name, path=None):
        m = re.match(REGEX_UPLOAD_PATTERN, name)
        if not m or not self.isattachment:
            raise TypeError("Can't retrieve %s file properties" % name)
        self_uploadfield = self.uploadfield
        if self.custom_retrieve_file_properties:
            return self.custom_retrieve_file_properties(name, path)
        try:
            try:
                filename = to_unicode(
                    base64.b16decode(m.group("name"), True)
                )  # Legacy file encoding is base 16 lowercase
            except (binascii.Error, TypeError):
                filename = to_unicode(
                    base64.urlsafe_b64decode(m.group("name"))
                )  # New encoding is base 64
            filename = re.sub(REGEX_UPLOAD_CLEANUP, "_", filename)
        except (TypeError, AttributeError):
            filename = name
        # ## if file is in DB
        if isinstance(self_uploadfield, (str, Field)):
            return dict(path=None, filename=filename)
        # ## if file is on filesystem
        if not path:
            if self.uploadfolder:
                path = self.uploadfolder
            else:
                path = pjoin(self.db._adapter.folder, "..", "uploads")
                path = os.path.abspath(path)
        if self.uploadseparate:
            t = m.group("table")
            f = m.group("field")
            u = m.group("uuidkey")
            path = pjoin(path, "%s.%s" % (t, f), u[:2])
        return dict(path=path, filename=filename)

    def formatter(self, value):
        if value is None:
            return self.map_none
        requires = self.requires
        if not requires or requires is DEFAULT:
            return value
        if not isinstance(requires, (list, tuple)):
            requires = [requires]
        elif isinstance(requires, tuple):
            requires = list(requires)
        else:
            requires = copy.copy(requires)
        requires.reverse()
        for item in requires:
            if hasattr(item, "formatter"):
                value = item.formatter(value)
        return value

    def validate(self, value, record_id=None):
        requires = self.requires
        if not requires or requires is DEFAULT:
            return ((value if value != self.map_none else None), None)
        if not isinstance(requires, (list, tuple)):
            requires = [requires]
        for validator in requires:
            # notice that some validator may have different behavior
            # depending on the record id, for example
            # IS_NOT_IN_DB should exclude the current record_id from check
            (value, error) = validator(value, record_id)
            if error:
                return (value, error)
        return ((value if value != self.map_none else None), None)

    def count(self, distinct=None):
        return Expression(self.db, self._dialect.count, self, distinct, "integer")

    def as_dict(self, flat=False, sanitize=True):
        attrs = (
            "name",
            "authorize",
            "represent",
            "ondelete",
            "custom_store",
            "autodelete",
            "custom_retrieve",
            "filter_out",
            "uploadseparate",
            "widget",
            "uploadfs",
            "update",
            "custom_delete",
            "uploadfield",
            "uploadfolder",
            "custom_qualifier",
            "unique",
            "writable",
            "compute",
            "map_none",
            "default",
            "type",
            "required",
            "readable",
            "requires",
            "comment",
            "label",
            "length",
            "notnull",
            "custom_retrieve_file_properties",
            "filter_in",
        )
        serializable = (int, long, basestring, float, tuple, bool, type(None))

        def flatten(obj):
            if isinstance(obj, dict):
                return dict((flatten(k), flatten(v)) for k, v in obj.items())
            elif isinstance(obj, (tuple, list, set)):
                return [flatten(v) for v in obj]
            elif isinstance(obj, serializable):
                return obj
            elif isinstance(obj, (datetime.datetime, datetime.date, datetime.time)):
                return str(obj)
            else:
                return None

        d = dict()
        if not (sanitize and not (self.readable or self.writable)):
            for attr in attrs:
                if flat:
                    d.update({attr: flatten(getattr(self, attr))})
                else:
                    d.update({attr: getattr(self, attr)})
            d["fieldname"] = d.pop("name")
        return d

    def __bool__(self):
        return True

    def __str__(self):
        if self._table:
            return "%s.%s" % (self.tablename, self.name)
        return "<no table>.%s" % self.name

    def __hash__(self):
        return id(self)

    @property
    def sqlsafe(self):
        if self._table is None:
            raise SyntaxError("Field %s is not bound to any table" % self.name)
        return self._table.sql_shortref + "." + self._rname

    @property
    @deprecated("sqlsafe_name", "_rname", "Field")
    def sqlsafe_name(self):
        return self._rname

    @property
    def longname(self):
        if self._table is None:
            raise SyntaxError("Field %s is not bound to any table" % self.name)
        return self._table._tablename + "." + self.name class Rows(BasicRows):
    """
    A wrapper for the return value of a select. It basically represents a table.
    It has an iterator and each row is represented as a `Row` dictionary.
    """

    # ## TODO: this class still needs some work to care for ID/OID

    def __init__(
        self, db=None, records=[], colnames=[], compact=True, rawrows=None, fields=[]
    ):
        self.db = db
        self.records = records
        self.fields = fields
        self.colnames = colnames
        self.compact = compact
        self.response = rawrows

    def __repr__(self):
        return "<Rows (%s)>" % len(self.records)

    def setvirtualfields(self, **keyed_virtualfields):
        """
        For reference::

            db.define_table('x', Field('number', 'integer'))
            if db(db.x).isempty(): [db.x.insert(number=i) for i in range(10)]

            from gluon.dal import lazy_virtualfield

            class MyVirtualFields(object):
                # normal virtual field (backward compatible, discouraged)
                def normal_shift(self): return self.x.number+1
                # lazy virtual field (because of @staticmethod)
                @lazy_virtualfield
                def lazy_shift(instance, row, delta=4): return row.x.number+delta
            db.x.virtualfields.append(MyVirtualFields())

            for row in db(db.x).select():
                print row.number, row.normal_shift, row.lazy_shift(delta=7)

        """
        if not keyed_virtualfields:
            return self
        for row in self.records:
            for (tablename, virtualfields) in iteritems(keyed_virtualfields):
                attributes = dir(virtualfields)
                if tablename not in row:
                    box = row[tablename] = Row()
                else:
                    box = row[tablename]
                updated = False
                for attribute in attributes:
                    if attribute[0] != "_":
                        method = getattr(virtualfields, attribute)
                        if hasattr(method, "__lazy__"):
                            box[attribute] = VirtualCommand(method, row)
                        elif type(method) == types.MethodType:
                            if not updated:
                                virtualfields.__dict__.update(row)
                                updated = True
                            box[attribute] = method()
        return self

    def __add__(self, other):
        if self.colnames != other.colnames:
            raise Exception("Cannot & incompatible Rows objects")
        records = self.records + other.records
        return self.__class__(
            self.db,
            records,
            self.colnames,
            fields=self.fields,
            compact=self.compact or other.compact,
        )

    def __and__(self, other):
        if self.colnames != other.colnames:
            raise Exception("Cannot & incompatible Rows objects")
        records = []
        other_records = list(other.records)
        for record in self.records:
            if record in other_records:
                records.append(record)
                other_records.remove(record)
        return self.__class__(
            self.db,
            records,
            self.colnames,
            fields=self.fields,
            compact=self.compact or other.compact,
        )

    def __or__(self, other):
        if self.colnames != other.colnames:
            raise Exception("Cannot | incompatible Rows objects")
        records = [record for record in other.records if record not in self.records]
        records = self.records + records
        return self.__class__(
            self.db,
            records,
            self.colnames,
            fields=self.fields,
            compact=self.compact or other.compact,
        )

    def __len__(self):
        return len(self.records)

    def __getslice__(self, a, b):
        return self.__class__(
            self.db,
            self.records[a:b],
            self.colnames,
            compact=self.compact,
            fields=self.fields,
        )

    def __getitem__(self, i):
        if isinstance(i, slice):
            return self.__getslice__(i.start, i.stop)
        row = self.records[i]
        keys = list(row.keys())
        if self.compact and len(keys) == 1 and keys[0] != "_extra":
            return row[keys[0]]
        return row

    def __iter__(self):
        """
        Iterator over records
        """

        for i in xrange(len(self)):
            yield self[i]

    def __eq__(self, other):
        if isinstance(other, Rows):
            return self.records == other.records
        else:
            return False

    def column(self, column=None):
        return [r[str(column) if column else self.colnames[0]] for r in self]

    def first(self):
        if not self.records:
            return None
        return self[0]

    def last(self):
        if not self.records:
            return None
        return self[-1]

    def append(self, row):
        self.records.append(row)

    def insert(self, position, row):
        self.records.insert(position, row)

    def find(self, f, limitby=None):
        """
        Returns a new Rows object, a subset of the original object,
        filtered by the function `f`
        """
        if not self:
            return self.__class__(
                self.db, [], self.colnames, compact=self.compact, fields=self.fields
            )
        records = []
        if limitby:
            a, b = limitby
        else:
            a, b = 0, len(self)
        k = 0
        for i, row in enumerate(self):
            if f(row):
                if a <= k:
                    records.append(self.records[i])
                k += 1
                if k == b:
                    break
        return self.__class__(
            self.db, records, self.colnames, compact=self.compact, fields=self.fields
        )

    def exclude(self, f):
        """
        Removes elements from the calling Rows object, filtered by the function
        `f`, and returns a new Rows object containing the removed elements
        """
        if not self.records:
            return self.__class__(
                self.db, [], self.colnames, compact=self.compact, fields=self.fields
            )
        removed = []
        i = 0
        while i < len(self):
            row = self[i]
            if f(row):
                removed.append(self.records[i])
                del self.records[i]
            else:
                i += 1
        return self.__class__(
            self.db, removed, self.colnames, compact=self.compact, fields=self.fields
        )

    def sort(self, f, reverse=False):
        """
        Returns a list of sorted elements (not sorted in place)
        """
        rows = self.__class__(
            self.db, [], self.colnames, compact=self.compact, fields=self.fields
        )
        # When compact=True, iterating over self modifies each record,
        # so when sorting self, it is necessary to return a sorted
        # version of self.records rather than the sorted self directly.
        rows.records = [
            r
            for (r, s) in sorted(
                zip(self.records, self), key=lambda r: f(r[1]), reverse=reverse
            )
        ]
        return rows

    def join(self, field, name=None, constraint=None, fields=[], orderby=None):
        if len(self) == 0:
            return self
        mode = "referencing" if field.type == "id" else "referenced"
        func = lambda ids: field.belongs(ids)
        db, ids, maps = self.db, [], {}
        if not fields:
            fields = [f for f in field._table if f.readable]
        if mode == "referencing":
            # try all refernced field names
            names = (
                [name]
                if name
                else list(
                    set(
                        f.name for f in field._table._referenced_by if f.name in self[0]
                    )
                )
            )
            # get all the ids
            ids = [row.get(name) for row in self for name in names]
            # filter out the invalid ids
            ids = filter(lambda id: str(id).isdigit(), ids)
            # build the query
            query = func(ids)
            if constraint:
                query = query & constraint
            tmp = not field.name in [f.name for f in fields]
            if tmp:
                fields.append(field)
            other = db(query).select(*fields, orderby=orderby, cacheable=True)
            for row in other:
                id = row[field.name]
                maps[id] = row
            for row in self:
                for name in names:
                    row[name] = maps.get(row[name])
        if mode == "referenced":
            if not name:
                name = field._tablename
            # build the query
            query = func([row.id for row in self])
            if constraint:
                query = query & constraint
            name = name or field._tablename
            tmp = not field.name in [f.name for f in fields]
            if tmp:
                fields.append(field)
            other = db(query).select(*fields, orderby=orderby, cacheable=True)
            for row in other:
                id = row[field]
                if not id in maps:
                    maps[id] = []
                if tmp:
                    try:
                        del row[field.name]
                    except:
                        del row[field.tablename][field.name]
                        if not row[field.tablename] and len(row.keys()) == 2:
                            del row[field.tablename]
                            row = row[row.keys()[0]]
                maps[id].append(row)
            for row in self:
                row[name] = maps.get(row.id, [])
        return self

    def group_by_value(self, *fields, **args):
        """
        Regroups the rows, by one of the fields
        """
        one_result = False
        if "one_result" in args:
            one_result = args["one_result"]

        def build_fields_struct(row, fields, num, groups):
            """
            helper function:
            """
            if num > len(fields) - 1:
                if one_result:
                    return row
                else:
                    return [row]

            key = fields[num]
            value = row[key]

            if value not in groups:
                groups[value] = build_fields_struct(row, fields, num + 1, {})
            else:
                struct = build_fields_struct(row, fields, num + 1, groups[value])

                # still have more grouping to do
                if isinstance(struct, dict):
                    groups[value].update()
                # no more grouping, first only is off
                elif isinstance(struct, list):
                    groups[value] += struct
                # no more grouping, first only on
                else:
                    groups[value] = struct

            return groups

        if len(fields) == 0:
            return self

        # if select returned no results
        if not self.records:
            return {}

        grouped_row_group = dict()

        # build the struct
        for row in self:
            build_fields_struct(row, fields, 0, grouped_row_group)

        return grouped_row_group

    def render(self, i=None, fields=None):
        """
        Takes an index and returns a copy of the indexed row with values
        transformed via the "represent" attributes of the associated fields.

        Args:
            i: index. If not specified, a generator is returned for iteration
                over all the rows.
            fields: a list of fields to transform (if None, all fields with
                "represent" attributes will be transformed)
        """
        if i is None:
            return (self.render(i, fields=fields) for i in range(len(self)))
        if not self.db.has_representer("rows_render"):
            raise RuntimeError(
                "Rows.render() needs a `rows_render` \
                               representer in DAL instance"
            )
        row = copy.deepcopy(self.records[i])
        keys = list(row.keys())
        if not fields:
            fields = [f for f in self.fields if isinstance(f, Field) and f.represent]
        for field in fields:
            row[field._tablename][field.name] = self.db.represent(
                "rows_render",
                field,
                row[field._tablename][field.name],
                row[field._tablename],
            )

        if self.compact and len(keys) == 1 and keys[0] != "_extra":
            return row[keys[0]]
        return row

    def __getstate__(self):
        ret = self.__dict__.copy()
        ret.pop("fields", None)
        return ret

    def _restore_fields(self, fields):
        if not hasattr(self, "fields"):
            self.fields = fields
        return self class Row(BasicStorage):

    """
    A dictionary that lets you do d['a'] as well as d.a
    this is only used to store a `Row`
    """

    def __getitem__(self, k):
        key = str(k)

        _extra = BasicStorage.get(self, "_extra", None)
        if _extra is not None:
            v = _extra.get(key, DEFAULT)
            if v is not DEFAULT:
                return v

        try:
            return BasicStorage.__getattribute__(self, key)
        except AttributeError:
            pass

        m = REGEX_TABLE_DOT_FIELD.match(key)
        if m:
            key2 = m.group(2)
            try:
                return BasicStorage.__getitem__(self, m.group(1))[key2]
            except (KeyError, TypeError):
                pass
            try:
                return BasicStorage.__getitem__(self, key2)
            except KeyError:
                pass

        lg = BasicStorage.get(self, "__get_lazy_reference__", None)
        if callable(lg):
            v = self[key] = lg(key)
            return v

        raise KeyError(key)

    def __repr__(self):
        return "<Row %s>" % self.as_dict(custom_types=[LazySet])

    def __int__(self):
        return self.get("id")

    def __long__(self):
        return long(int(self))

    def __hash__(self):
        return id(self)

    __str__ = __repr__

    __call__ = __getitem__

    def __getattr__(self, k):
        try:
            return self.__getitem__(k)
        except KeyError:
            raise AttributeError

    def __copy__(self):
        return Row(self)

    def __eq__(self, other):
        try:
            return self.as_dict() == other.as_dict()
        except AttributeError:
            return False

    def get(self, key, default=None):
        try:
            return self.__getitem__(key)
        except (KeyError, AttributeError, TypeError):
            return default

    def as_dict(self, datetime_to_str=False, custom_types=None):
        SERIALIZABLE_TYPES = [str, int, float, bool, list, dict]
        DT_INST = (datetime.date, datetime.datetime, datetime.time)
        if PY2:
            SERIALIZABLE_TYPES += [unicode, long]
        if isinstance(custom_types, (list, tuple, set)):
            SERIALIZABLE_TYPES += custom_types
        elif custom_types:
            SERIALIZABLE_TYPES.append(custom_types)
        d = dict(self)
        for k in list(d.keys()):
            v = d[k]
            if d[k] is None:
                continue
            elif isinstance(v, Row):
                d[k] = v.as_dict()
            elif isinstance(v, Reference):
                d[k] = long(v)
            elif isinstance(v, decimal.Decimal):
                d[k] = float(v)
            elif isinstance(v, DT_INST):
                if datetime_to_str:
                    d[k] = v.isoformat().replace("T", " ")[:19]
            elif not isinstance(v, tuple(SERIALIZABLE_TYPES)):
                del d[k]
        return d

    def as_xml(self, row_name="row", colnames=None, indent="  "):
        def f(row, field, indent="  "):
            if isinstance(row, Row):
                spc = indent + "  \n"
                items = [f(row[x], x, indent + "  ") for x in row]
                return "%s<%s>\n%s\n%s</%s>" % (
                    indent,
                    field,
                    spc.join(item for item in items if item),
                    indent,
                    field,
                )
            elif not callable(row):
                if re.match(REGEX_ALPHANUMERIC, field):
                    return "%s<%s>%s</%s>" % (indent, field, row, field)
                else:
                    return '%s<extra name="%s">%s</extra>' % (indent, field, row)
            else:
                return None

        return f(self, row_name, indent=indent)

    def as_json(
        self, mode="object", default=None, colnames=None, serialize=True, **kwargs
    ):
        """
        serializes the row to a JSON object
        kwargs are passed to .as_dict method
        only "object" mode supported

        `serialize = False` used by Rows.as_json

        TODO: return array mode with query column order

        mode and colnames are not implemented
        """

        item = self.as_dict(**kwargs)
        if serialize:
            return serializers.json(item)
        else:
            return item class Set(Serializable):

    """
    Represents a set of records in the database.
    Records are identified by the `query=Query(...)` object.
    Normally the Set is generated by `DAL.__call__(Query(...))`

    Given a set, for example::

        myset = db(db.users.name=='Max')

    you can::

        myset.update(db.users.name='Massimo')
        myset.delete() # all elements in the set
        myset.select(orderby=db.users.id, groupby=db.users.name, limitby=(0, 10))

    and take subsets:

       subset = myset(db.users.id<5)

    """

    def __init__(self, db, query, ignore_common_filters=None):
        self.db = db
        self._db = db  # for backward compatibility
        self.dquery = None

        # if query is a dict, parse it
        if isinstance(query, dict):
            query = self.parse(query)

        if (
            ignore_common_filters is not None
            and use_common_filters(query) == ignore_common_filters
        ):
            query = copy.copy(query)
            query.ignore_common_filters = ignore_common_filters
        self.query = query

    def __repr__(self):
        return "<Set %s>" % str(self.query)

    def __call__(self, query, ignore_common_filters=False):
        return self.where(query, ignore_common_filters)

    def where(self, query, ignore_common_filters=False):
        if query is None:
            return self
        elif isinstance(query, Table):
            query = self.db._adapter.id_query(query)
        elif isinstance(query, str):
            query = Expression(self.db, query)
        elif isinstance(query, Field):
            query = query != None
        if self.query:
            return Set(
                self.db, self.query & query, ignore_common_filters=ignore_common_filters
            )
        else:
            return Set(self.db, query, ignore_common_filters=ignore_common_filters)

    def _count(self, distinct=None):
        return self.db._adapter._count(self.query, distinct)

    def _select(self, *fields, **attributes):
        adapter = self.db._adapter
        tablenames = adapter.tables(
            self.query,
            attributes.get("join", None),
            attributes.get("left", None),
            attributes.get("orderby", None),
            attributes.get("groupby", None),
        )
        fields = adapter.expand_all(fields, tablenames)
        return adapter._select(self.query, fields, attributes)

    def _delete(self):
        db = self.db
        table = db._adapter.get_table(self.query)
        return db._adapter._delete(table, self.query)

    def _update(self, **update_fields):
        db = self.db
        table = db._adapter.get_table(self.query)
        row = table._fields_and_values_for_update(update_fields)
        return db._adapter._update(table, self.query, row.op_values())

    def as_dict(self, flat=False, sanitize=True):
        if flat:
            uid = dbname = uri = None
            codec = self.db._db_codec
            if not sanitize:
                uri, dbname, uid = (self.db._dbname, str(self.db), self.db._db_uid)
            d = {"query": self.query.as_dict(flat=flat)}
            d["db"] = {"uid": uid, "codec": codec, "name": dbname, "uri": uri}
            return d
        else:
            return self.__dict__

    def parse(self, dquery):
        """Experimental: Turn a dictionary into a Query object"""
        self.dquery = dquery
        return self.build(self.dquery)

    def build(self, d):
        """Experimental: see .parse()"""
        op, first, second = (d["op"], d["first"], d.get("second", None))
        left = right = built = None

        if op in ("AND", "OR"):
            if not (type(first), type(second)) == (dict, dict):
                raise SyntaxError("Invalid AND/OR query")
            if op == "AND":
                built = self.build(first) & self.build(second)
            else:
                built = self.build(first) | self.build(second)
        elif op == "NOT":
            if first is None:
                raise SyntaxError("Invalid NOT query")
            built = ~self.build(first)  # pylint: disable=invalid-unary-operand-type
        else:
            # normal operation (GT, EQ, LT, ...)
            for k, v in {"left": first, "right": second}.items():
                if isinstance(v, dict) and v.get("op"):
                    v = self.build(v)
                if isinstance(v, dict) and ("tablename" in v):
                    v = self.db[v["tablename"]][v["fieldname"]]
                if k == "left":
                    left = v
                else:
                    right = v

            if hasattr(self.db._adapter, op):
                opm = getattr(self.db._adapter, op)

            if op == "EQ":
                built = left == right
            elif op == "NE":
                built = left != right
            elif op == "GT":
                built = left > right
            elif op == "GE":
                built = left >= right
            elif op == "LT":
                built = left < right
            elif op == "LE":
                built = left <= right
            elif op in ("JOIN", "LEFT_JOIN", "RANDOM", "ALLOW_NULL"):
                built = Expression(self.db, opm)
            elif op in (
                "LOWER",
                "UPPER",
                "EPOCH",
                "PRIMARY_KEY",
                "COALESCE_ZERO",
                "RAW",
                "INVERT",
            ):
                built = Expression(self.db, opm, left)
            elif op in (
                "COUNT",
                "EXTRACT",
                "AGGREGATE",
                "SUBSTRING",
                "REGEXP",
                "LIKE",
                "ILIKE",
                "STARTSWITH",
                "ENDSWITH",
                "ADD",
                "SUB",
                "MUL",
                "DIV",
                "MOD",
                "AS",
                "ON",
                "COMMA",
                "NOT_NULL",
                "COALESCE",
                "CONTAINS",
                "BELONGS",
            ):
                built = Expression(self.db, opm, left, right)
            # expression as string
            elif not (left or right):
                built = Expression(self.db, op)
            else:
                raise SyntaxError("Operator not supported: %s" % op)

        return built

    def isempty(self):
        return not self.select(limitby=(0, 1), orderby_on_limitby=False)

    def count(self, distinct=None, cache=None):
        db = self.db
        if cache:
            sql = self._count(distinct=distinct)
            if isinstance(cache, dict):
                cache_model = cache["model"]
                time_expire = cache["expiration"]
                key = cache.get("key")
                if not key:
                    key = db._uri + "/" + sql
                    key = hashlib_md5(key).hexdigest()
            else:
                cache_model, time_expire = cache
                key = db._uri + "/" + sql
                key = hashlib_md5(key).hexdigest()
            return cache_model(
                key,
                lambda self=self, distinct=distinct: db._adapter.count(
                    self.query, distinct
                ),
                time_expire,
            )
        return db._adapter.count(self.query, distinct)

    def select(self, *fields, **attributes):
        adapter = self.db._adapter
        tablenames = adapter.tables(
            self.query,
            attributes.get("join", None),
            attributes.get("left", None),
            attributes.get("orderby", None),
            attributes.get("groupby", None),
        )
        fields = adapter.expand_all(fields, tablenames)
        return adapter.select(self.query, fields, attributes)

    def iterselect(self, *fields, **attributes):
        adapter = self.db._adapter
        tablenames = adapter.tables(
            self.query,
            attributes.get("join", None),
            attributes.get("left", None),
            attributes.get("orderby", None),
            attributes.get("groupby", None),
        )
        fields = adapter.expand_all(fields, tablenames)
        return adapter.iterselect(self.query, fields, attributes)

    def nested_select(self, *fields, **attributes):
        adapter = self.db._adapter
        tablenames = adapter.tables(
            self.query,
            attributes.get("join", None),
            attributes.get("left", None),
            attributes.get("orderby", None),
            attributes.get("groupby", None),
        )
        fields = adapter.expand_all(fields, tablenames)
        return adapter.nested_select(self.query, fields, attributes)

    def cte(self, name, *fields, **attributes):
        adapter = self.db._adapter
        tablenames = adapter.tables(
            self.query,
            attributes.get("join", None),
            attributes.get("left", None),
            attributes.get("orderby", None),
            attributes.get("groupby", None),
        )
        attributes['cte'] = True
        fields = adapter.expand_all(fields, tablenames)
        return adapter.nested_select(self.query, fields, attributes).with_alias(name)



    def delete(self):
        db = self.db
        table = db._adapter.get_table(self.query)
        if any(f(self) for f in table._before_delete):
            return 0
        ret = db._adapter.delete(table, self.query)
        ret and [f(self) for f in table._after_delete]
        return ret

    def delete_naive(self):
        """
        Same as delete but does not call table._before_delete and _after_delete
        """
        db = self.db
        table = db._adapter.get_table(self.query)
        ret = db._adapter.delete(table, self.query)
        return ret

    def update(self, **update_fields):
        db = self.db
        table = db._adapter.get_table(self.query)
        row = table._fields_and_values_for_update(update_fields)
        if not row._values:
            raise ValueError("No fields to update")
        if any(f(self, row) for f in table._before_update):
            return 0
        ret = db._adapter.update(table, self.query, row.op_values())
        ret and [f(self, row) for f in table._after_update]
        return ret

    def update_naive(self, **update_fields):
        """
        Same as update but does not call table._before_update and _after_update
        """
        table = self.db._adapter.get_table(self.query)
        row = table._fields_and_values_for_update(update_fields)
        if not row._values:
            raise ValueError("No fields to update")
        ret = self.db._adapter.update(table, self.query, row.op_values())
        return ret

    def validate_and_update(self, **update_fields):
        table = self.db._adapter.get_table(self.query)
        response = Row()
        response.errors = Row()
        new_fields = copy.copy(update_fields)
        for key, value in iteritems(update_fields):
            value, error = table[key].validate(value, update_fields.get("id"))
            if error:
                response.errors[key] = "%s" % error
            else:
                new_fields[key] = value
        if response.errors:
            response.updated = None
        else:
            row = table._fields_and_values_for_update(new_fields)
            if not row._values:
                raise ValueError("No fields to update")
            if any(f(self, row) for f in table._before_update):
                ret = 0
            else:
                ret = self.db._adapter.update(table, self.query, row.op_values())
                ret and [f(self, row) for f in table._after_update]
            response.updated = ret
        return response class BaseAdapter(with_metaclass(AdapterMeta, ConnectionPool)):
    dbengine = "None"
    drivers = ()
    uploads_in_blob = False
    support_distributed_transaction = False

    def __init__(
        self,
        db,
        uri,
        pool_size=0,
        folder=None,
        db_codec="UTF-8",
        credential_decoder=IDENTITY,
        driver_args={},
        adapter_args={},
        after_connection=None,
        entity_quoting=False,
    ):
        super(BaseAdapter, self).__init__()
        self._load_dependencies()
        self.db = db
        self.uri = uri
        self.pool_size = pool_size
        self.folder = folder
        self.db_codec = db_codec
        self.credential_decoder = credential_decoder
        self.driver_args = driver_args
        self.adapter_args = adapter_args
        self.expand = self._expand
        self._after_connection = after_connection
        self.set_connection(None)
        self.find_driver()
        self._initialize_()

    def _load_dependencies(self):
        from ..dialects import dialects
        from ..parsers import parsers
        from ..representers import representers

        self.dialect = dialects.get_for(self)
        self.parser = parsers.get_for(self)
        self.representer = representers.get_for(self)

    def _initialize_(self):
        self._find_work_folder()

    @property
    def types(self):
        return self.dialect.types

    @property
    def _available_drivers(self):
        return [
            driver
            for driver in self.drivers
            if driver in iterkeys(self.db._drivers_available)
        ]

    def _driver_from_uri(self):
        rv = None
        if self.uri:
            items = self.uri.split("://", 1)[0].split(":")
            rv = items[1] if len(items) > 1 else None
        return rv

    def find_driver(self):
        if getattr(self, "driver", None) is not None:
            return
        requested_driver = self._driver_from_uri() or self.adapter_args.get("driver")
        if requested_driver:
            if requested_driver in self._available_drivers:
                self.driver_name = requested_driver
                self.driver = self.db._drivers_available[requested_driver]
            else:
                raise RuntimeError("Driver %s is not available" % requested_driver)
        elif self._available_drivers:
            self.driver_name = self._available_drivers[0]
            self.driver = self.db._drivers_available[self.driver_name]
        else:
            raise RuntimeError(
                "No driver of supported ones %s is available" % str(self.drivers)
            )

    def connector(self):
        return self.driver.connect(self.driver_args)

    def test_connection(self):
        pass

    @with_connection
    def close_connection(self):
        rv = self.connection.close()
        self.set_connection(None)
        return rv

    def tables(self, *queries):
        tables = dict()
        for query in queries:
            if isinstance(query, Field):
                key = query.tablename
                if tables.get(key, query.table) is not query.table:
                    raise ValueError("Name conflict in table list: %s" % key)
                tables[key] = query.table
            elif isinstance(query, (Expression, Query)):
                tmp = [x for x in (query.first, query.second) if x is not None]
                tables = merge_tablemaps(tables, self.tables(*tmp))
        return tables

    def get_table(self, *queries):
        tablemap = self.tables(*queries)
        if len(tablemap) == 1:
            return tablemap.popitem()[1]
        elif len(tablemap) < 1:
            raise RuntimeError("No table selected")
        else:
            raise RuntimeError("Too many tables selected (%s)" % str(list(tablemap)))

    def common_filter(self, query, tablist):
        tenant_fieldname = self.db._request_tenant
        for table in tablist:
            if isinstance(table, basestring):
                table = self.db[table]
            # deal with user provided filters
            if table._common_filter is not None:
                query = query & table._common_filter(query)
            # deal with multi_tenant filters
            if tenant_fieldname in table:
                default = table[tenant_fieldname].default
                if default is not None:
                    newquery = table[tenant_fieldname] == default
                    if query is None:
                        query = newquery
                    else:
                        query = query & newquery
        return query

    def _expand(self, expression, field_type=None, colnames=False, query_env={}):
        return str(expression)

    def expand_all(self, fields, tabledict):
        new_fields = []
        append = new_fields.append
        for item in fields:
            if isinstance(item, SQLALL):
                new_fields += item._table
            elif isinstance(item, str):
                m = REGEX_TABLE_DOT_FIELD.match(item)
                if m:
                    tablename, fieldname = m.groups()
                    append(self.db[tablename][fieldname])
                else:
                    append(Expression(self.db, lambda item=item: item))
            else:
                append(item)
        # ## if no fields specified take them all from the requested tables
        if not new_fields:
            for table in tabledict.values():
                for field in table:
                    append(field)
        return new_fields

    def parse_value(self, value, field_itype, field_type, blob_decode=True):
        # [Note - gi0baro] I think next if block can be (should be?) avoided
        if field_type != "blob" and isinstance(value, str):
            try:
                value = value.decode(self.db._db_codec)
            except Exception:
                pass
        if PY2 and isinstance(value, unicode):
            value = value.encode("utf-8")
        if isinstance(field_type, SQLCustomType):
            value = field_type.decoder(value)
        if not isinstance(field_type, str) or value is None:
            return value
        elif field_type == "blob" and not blob_decode:
            return value
        else:
            return self.parser.parse(value, field_itype, field_type)

    def _add_operators_to_parsed_row(self, rid, table, row):
        for key, record_operator in iteritems(self.db.record_operators):
            setattr(row, key, record_operator(row, table, rid))
        if table._db._lazy_tables:
            row["__get_lazy_reference__"] = LazyReferenceGetter(table, rid)

    def _add_reference_sets_to_parsed_row(self, rid, table, tablename, row):
        for rfield in table._referenced_by:
            referee_link = self.db._referee_name and self.db._referee_name % dict(
                table=rfield.tablename, field=rfield.name
            )
            if referee_link and referee_link not in row and referee_link != tablename:
                row[referee_link] = LazySet(rfield, rid)

    def _regex_select_as_parser(self, colname):
        return re.search(REGEX_SELECT_AS_PARSER, colname)

    def _parse(
        self,
        row,
        tmps,
        fields,
        colnames,
        blob_decode,
        cacheable,
        fields_virtual,
        fields_lazy,
    ):
        new_row = defaultdict(self.db.Row)
        extras = self.db.Row()
        #: let's loop over columns
        for (j, colname) in enumerate(colnames):
            value = row[j]
            tmp = tmps[j]
            tablename = None
            #: do we have a real column?
            if tmp:
                (tablename, fieldname, table, field, ft, fit) = tmp
                colset = new_row[tablename]
                #: parse value
                value = self.parse_value(value, fit, ft, blob_decode)
                if field.filter_out:
                    value = field.filter_out(value)
                colset[fieldname] = value
                #! backward compatibility
                if ft == "id" and fieldname != "id" and "id" not in table.fields:
                    colset["id"] = value
                #: additional parsing for 'id' fields
                if ft == "id" and not cacheable:
                    self._add_operators_to_parsed_row(value, table, colset)
                    #: table may be 'nested_select' which doesn't have '_referenced_by'
                    if hasattr(table, "_referenced_by"):
                        self._add_reference_sets_to_parsed_row(
                            value, table, tablename, colset
                        )
            #: otherwise we set the value in extras
            else:
                #: fields[j] may be None if only 'colnames' was specified in db.executesql()
                f_itype, ftype = (
                    fields[j] and [fields[j]._itype, fields[j].type] or [None, None]
                )
                value = self.parse_value(value, f_itype, ftype, blob_decode)
                extras[colname] = value
                if not fields[j]:
                    new_row[colname] = value
                else:
                    new_column_match = self._regex_select_as_parser(colname)
                    if new_column_match is not None:
                        new_column_name = new_column_match.group(1)
                        new_row[new_column_name] = value
        #: add extras if needed (eg. operations results)
        if extras:
            new_row["_extra"] = extras
        #: add virtuals
        new_row = self.db.Row(**new_row)
        for tablename in fields_virtual.keys():
            for f, v in fields_virtual[tablename][1]:
                try:
                    new_row[tablename][f] = v.f(new_row)
                except (AttributeError, KeyError):
                    pass  # not enough fields to define virtual field
            for f, v in fields_lazy[tablename][1]:
                try:
                    new_row[tablename][f] = v.handler(v.f, new_row)
                except (AttributeError, KeyError):
                    pass  # not enough fields to define virtual field
        return new_row

    def _parse_expand_colnames(self, fieldlist):
        """
        - Expand a list of colnames into a list of
          (tablename, fieldname, table_obj, field_obj, field_type)
        - Create a list of table for virtual/lazy fields
        """
        fields_virtual = {}
        fields_lazy = {}
        tmps = []
        for field in fieldlist:
            if not isinstance(field, Field):
                tmps.append(None)
                continue
            table = field.table
            tablename, fieldname = table._tablename, field.name
            ft = field.type
            fit = field._itype
            tmps.append((tablename, fieldname, table, field, ft, fit))
            if tablename not in fields_virtual:
                fields_virtual[tablename] = (
                    table,
                    [(f.name, f) for f in table._virtual_fields],
                )
                fields_lazy[tablename] = (
                    table,
                    [(f.name, f) for f in table._virtual_methods],
                )
        return (fields_virtual, fields_lazy, tmps)

    def parse(self, rows, fields, colnames, blob_decode=True, cacheable=False):
        (fields_virtual, fields_lazy, tmps) = self._parse_expand_colnames(fields)
        new_rows = [
            self._parse(
                row,
                tmps,
                fields,
                colnames,
                blob_decode,
                cacheable,
                fields_virtual,
                fields_lazy,
            )
            for row in rows
        ]
        rowsobj = self.db.Rows(self.db, new_rows, colnames, rawrows=rows, fields=fields)
        # Old style virtual fields
        for tablename, tmp in fields_virtual.items():
            table = tmp[0]
            # ## old style virtual fields
            for item in table.virtualfields:
                try:
                    rowsobj = rowsobj.setvirtualfields(**{tablename: item})
                except (KeyError, AttributeError):
                    # to avoid breaking virtualfields when partial select
                    pass
        return rowsobj

    def iterparse(self, sql, fields, colnames, blob_decode=True, cacheable=False):
        """
        Iterator to parse one row at a time.
        It doesn't support the old style virtual fields
        """
        return IterRows(self.db, sql, fields, colnames, blob_decode, cacheable)

    def adapt(self, value):
        return value

    def represent(self, obj, field_type):
        if isinstance(obj, CALLABLETYPES):
            obj = obj()
        return self.representer.represent(obj, field_type)

    def _drop_table_cleanup(self, table):
        del self.db[table._tablename]
        del self.db.tables[self.db.tables.index(table._tablename)]
        self.db._remove_references_to(table)

    def drop_table(self, table, mode=""):
        self._drop_table_cleanup(table)

    def rowslice(self, rows, minimum=0, maximum=None):
        return rows

    def sqlsafe_table(self, tablename, original_tablename=None):
        return tablename

    def sqlsafe_field(self, fieldname):
        return fieldname class NullAdapter(BaseAdapter):
    def _load_dependencies(self):
        from ..dialects.base import CommonDialect

        self.dialect = CommonDialect(self)

    def find_driver(self):
        pass

    def connector(self):
        return NullDriver() def default_validators(db, field):
    """
    Field type validation, using web2py's validators mechanism.

    makes sure the content of a field is in line with the declared
    fieldtype
    """

    field_type = field.type
    field_unique = field.unique
    field_notnull = field.notnull

    is_ref = field_type.startswith("reference")
    is_list = field_type.startswith("list:")
    if is_ref or field_type.startswith("list:reference"):
        table_field = field_type[10 if is_ref else 15 :].split(".", 1)
        table_name = table_field[0]
        field_name = table_field[-1]
        requires = None
        if table_name in db.tables:
            referenced = db[table_name]
            if len(table_field) == 1:
                requires = validators.IS_IN_DB(
                    db,
                    referenced._id,
                    label=getattr(referenced, "_format", None),
                    multiple=is_list,
                )
            elif field_name in referenced.fields:
                requires = validators.IS_IN_DB(
                    db,
                    getattr(referenced, field_name),
                    label=getattr(referenced, "_format", None),
                    multiple=is_list,
                )
        if requires:
            if field_unique:
                requires._and = validators.IS_NOT_IN_DB(db, field)
            if not field_notnull:
                requires = validators.IS_EMPTY_OR(requires)
        return requires

    if isinstance(field.options, (list, tuple)):
        requires = validators.IS_IN_SET(field.options, multiple=is_list)
    else:
        requires = []
        if field_type in (("string", "text", "password")):
            requires.append(validators.IS_LENGTH(field.length))
        elif field_type == "json":
            requires.append(validators.IS_EMPTY_OR(validators.IS_JSON()))
        elif field_type == "double" or field_type == "float":
            requires.append(validators.IS_FLOAT_IN_RANGE(-1e100, 1e100))
        elif field_type == "integer":
            requires.append(validators.IS_INT_IN_RANGE(-(2 ** 31), 2 ** 31))
        elif field_type == "bigint":
            requires.append(validators.IS_INT_IN_RANGE(-(2 ** 63), 2 ** 63))
        elif field_type.startswith("decimal"):
            requires.append(validators.IS_DECIMAL_IN_RANGE(-(10 ** 10), 10 ** 10))
        elif field_type == "date":
            requires.append(validators.IS_DATE())
        elif field_type == "time":
            requires.append(validators.IS_TIME())
        elif field_type == "datetime":
            requires.append(validators.IS_DATETIME())

        if field_unique:
            requires.insert(0, validators.IS_NOT_IN_DB(db, field))
        if (field_notnull or field_unique) and field_type not in (
            "boolean",
            "password",
            "string",
            "text",
            "upload",
        ):
            requires.insert(0, validators.IS_NOT_EMPTY())
        elif not field_notnull and not field_unique and requires:
            null = "" if field.type == "password" else None
            requires[0] = validators.IS_EMPTY_OR(requires[0], null=null)

        if len(requires) == 1:
            requires = requires[0]

    return requires or None
import glob
import logging
import socket
import threading
import time
import traceback
import urllib
import contextlib
from ._compat import (
    PY2,
    pickle,
    hashlib_md5,
    pjoin,
    copyreg,
    integer_types,
    with_metaclass,
    long,
    unquote,
    iteritems,
)
from ._globals import GLOBAL_LOCKER, THREAD_LOCAL, DEFAULT
from ._load import OrderedDict
from .helpers.classes import (
    Serializable,
    SQLCallableList,
    BasicStorage,
    RecordUpdater,
    RecordDeleter,
    TimingHandler,
)
from .helpers.methods import (
    hide_password,
    smart_query,
    auto_validators,
    auto_represent,
    uuidstr,
)
from .helpers.regex import REGEX_PYTHON_KEYWORDS, REGEX_DBNAME
from .helpers.rest import RestParser
from .helpers.serializers import serializers
from .objects import Table, Field, Rows, Row, Set
from .adapters.base import BaseAdapter, NullAdapter
from .default_validators import default_validators
        from .drivers import DRIVERS, is_jdbc
                        from .adapters import adapters
            from .contrib.reserved_sql_keywords import ADAPTERS as RSK
                        # if there are multiple URIs to try in sequence, do not defer connection
                        if len(uris) > 1:
                            self._adapter.connector()
                        connected = True
                        break
                    except SyntaxError:
                        raise
                    except Exception:
                        tb = traceback.format_exc()
                        self.logger.debug(
                            "DEBUG: connect attempt %i, connection error:\n%s" % (k, tb)
                        )
                if connected:
                    break
                else:
                    time.sleep(1)
            if not connected:
                raise RuntimeError(
                    "Failure to connect, tried %d times:\n%s" % (attempts, tb)
                )
        else:
            self._adapter = NullAdapter(
                db=self,
                pool_size=0,
                uri="None",
                folder=folder,
                db_codec=db_codec,
                after_connection=after_connection,
                entity_quoting=entity_quoting,
            )
            migrate = fake_migrate = False
            self.validators_method = None
            self.validators = None
        adapter = self._adapter
        self._uri_hash = table_hash or hashlib_md5(adapter.uri).hexdigest()
        if check_reserved:

            self.RSK = RSK
        self._migrate = migrate
        self._fake_migrate = fake_migrate
        self._migrate_enabled = migrate_enabled
        self._fake_migrate_all = fake_migrate_all
        if self.serializers is not None:
            for k, v in self.serializers.items():
                serializers._custom_[k] = v
        if auto_import or tables:
            self.import_table_definitions(adapter.folder, tables=tables)

    @contextlib.contextmanager
    def single_transaction(self):
        self._adapter.reconnect()
        try:
            yield self
        except Exception:
            self._adapter.rollback()
        else:
            self._adapter.commit()
        finally:
            self.close()

    @property
    def tables(self):
        return self._tables

    @property
    def _timings(self):
        return getattr(THREAD_LOCAL, "_pydal_timings_", [])

    @property
    def _lastsql(self):
        return self._timings[-1] if self._timings else None

    def import_table_definitions(
        self, path, migrate=False, fake_migrate=False, tables=None
    ):
        if tables:
            for table in tables:
                self.define_table(**table)
        else:
            pattern = pjoin(path, self._uri_hash + "_*.table")
            for filename in glob.glob(pattern):
                tfile = self._adapter.migrator.file_open(filename, "r" if PY2 else "rb")
                try:
                    sql_fields = pickle.load(tfile)
                    name = filename[len(pattern) - 7 : -6]
                    mf = [
                        (
                            value["sortable"],
                            Field(
                                key,
                                type=value["type"],
                                length=value.get("length", None),
                                notnull=value.get("notnull", False),
                                unique=value.get("unique", False),
                            ),
                        )
                        for key, value in iteritems(sql_fields)
                    ]
                    mf.sort(key=lambda a: a[0])
                    self.define_table(
                        name,
                        *[item[1] for item in mf],
                        **dict(migrate=migrate, fake_migrate=fake_migrate)
                    )
                finally:
                    self._adapter.migrator.file_close(tfile)

    def check_reserved_keyword(self, name):
        """
        Validates `name` against SQL keywords
        Uses self._check_reserved which is a list of operators to use.
        """
        for backend in self._check_reserved:
            if name.upper() in self.RSK[backend]:
                raise SyntaxError(
                    'invalid table/column name "%s" is a "%s" reserved SQL/NOSQL keyword'
                    % (name, backend.upper())
                )

    def parse_as_rest(self, patterns, args, vars, queries=None, nested_select=True):

        return RestParser(self).parse(patterns, args, vars, queries, nested_select)