コード例 #1
0
ファイル: webapp.py プロジェクト: anandology/voterdb
 def where(self, **kwargs):
     conditions = []
     for k, v in kwargs.items():
         if not v:  # ignore None and empty lists
             continue
         elif isinstance(v, list):
             conditions.append(web.reparam(k + " in $v", locals()))
         else:
             conditions.append(web.reparam(k + "=$v", locals()))
     if not conditions:
         return "1=1"
     return web.SQLQuery.join(conditions, " AND ")
コード例 #2
0
ファイル: dbstore.py プロジェクト: EdwardBetts/infogami
 def process(c, ordering_func=None):
     # ordering_func is used when the query contains emebbabdle objects
     #
     # example: {'links': {'title: 'foo', 'url': 'http://example.com/foo'}}
     if c.datatype == 'ref':
         metadata = self.get_metadata(c.value)
         if metadata is None:
             # required object is not found so the query result wil be empty. 
             # Raise StopIteration to indicate empty result.
             raise StopIteration
         c.value = metadata.id
     if c.op == '~':
         op = Literal('LIKE')
         c.value = c.value.replace('*', '%')
     else:
         op = Literal(c.op)
         
     if c.key in ['key', 'type', 'created', 'last_modified']:
         #@@ special optimization to avoid join with thing.type when there are non-common properties in the query.
         #@@ Since type information is already present in property table, 
         #@@ getting property id is equivalent to join with type.
         if c.key == 'type' and type_required:
             return
             
         if isinstance(c.value, list):
             q = web.sqlors('thing.%s %s ' % (c.key, op), c.value)
         else:
             q = web.reparam('thing.%s %s $c.value' % (c.key, op), locals())
         xwheres = [q]
         
         # Add thing table explicitly because get_table is not called
         tables['_thing'] = DBTable("thing")
     else:
         table = get_table(c.datatype, c.key)
         key_id = self.get_property_id(type_id, c.key)
         if not key_id:
             raise StopIteration
             
         q1 = web.reparam('%(table)s.key_id=$key_id' % {'table': table}, locals())
         
         if isinstance(c.value, list):
             q2 = web.sqlors('%s.value %s ' % (table, op), c.value)
         else:
             q2 = web.reparam('%s.value %s $c.value' % (table, op), locals())
         
         xwheres = [q1, q2]
         if ordering_func:
             xwheres.append(ordering_func(table))
     wheres.extend(xwheres)
コード例 #3
0
ファイル: dbstore.py プロジェクト: EdwardBetts/infogami
 def process_sort(query):
     """Process sort field in the query and returns the db column to order by."""
     if query.sort:
         sort_key = query.sort.key
         if sort_key.startswith('-'):
             ascending = " desc"
             sort_key = sort_key[1:]
         else:
             ascending = ""
             
         if sort_key in ['key', 'type', 'created', 'last_modified']:
             order = 'thing.' + sort_key # make sure c.key is valid
             # Add thing table explicitly because get_table is not called
             tables['_thing'] = DBTable("thing")                
         else:   
             table = get_table(query.sort.datatype, sort_key)
             key_id = self.get_property_id(type_id, sort_key)
             if key_id is None:
                 raise StopIteration
             q = '%(table)s.key_id=$key_id' % {'table': table}
             wheres.append(web.reparam(q, locals()))
             order = table.label + '.value'
         return order + ascending
     else:
         return None
コード例 #4
0
ファイル: dbstore.py プロジェクト: EdwardBetts/infogami
    def versions(self, query):
        what = 'thing.key, version.revision, transaction.*'
        where = 'version.thing_id = thing.id AND version.transaction_id = transaction.id'

        if config.get('use_machine_comment'):
            what += ", version.machine_comment"
            
        def get_id(key):
            meta = self.get_metadata(key)
            if meta:
                return meta.id
            else:
                raise StopIteration
        
        for c in query.conditions:
            key, value = c.key, c.value
            assert key in ['key', 'type', 'author', 'ip', 'comment', 'created', 'bot']
            
            try:
                if key == 'key':
                    key = 'thing_id'
                    value = get_id(value)
                elif key == 'type':
                    key = 'thing.type'
                    value = get_id(value)
                elif key == 'author':
                    key = 'transaction.author_id'
                    value = get_id(value)
                else:
                    # 'bot' column is not enabled
                    if key == 'bot' and not config.use_bot_column:
                        raise StopIteration
                        
                    key = 'transaction.' + key
            except StopIteration:
                # StopIteration is raised when a non-existing object is referred in the query
                return []
                
            where += web.reparam(' AND %s=$value' % key, locals())
            
        sort = query.sort
        if sort and sort.startswith('-'):
            sort = sort[1:] + ' desc'

        sort = 'transaction.' + sort
        
        t = self.db.transaction()
        if config.query_timeout:
            self.db.query("SELECT set_config('statement_timeout', $query_timeout, false)", dict(query_timeout=config.query_timeout))
                
        result = self.db.select(['thing','version', 'transaction'], what=what, where=where, offset=query.offset, limit=query.limit, order=sort)
        result = result.list()
        author_ids = list(set(r.author_id for r in result if r.author_id))
        authors = self.get_metadata_list_from_ids(author_ids)
        
        t.commit()
        
        for r in result:
            r.author = r.author_id and authors[r.author_id].key
        return result
コード例 #5
0
ファイル: dbstore.py プロジェクト: rlugojr/infogami
        def process_sort(query):
            """Process sort field in the query and returns the db column to order by."""
            if query.sort:
                sort_key = query.sort.key
                if sort_key.startswith("-"):
                    ascending = " desc"
                    sort_key = sort_key[1:]
                else:
                    ascending = ""

                if sort_key in ["key", "type", "created", "last_modified"]:
                    order = "thing." + sort_key  # make sure c.key is valid
                    # Add thing table explicitly because get_table is not called
                    tables["_thing"] = DBTable("thing")
                else:
                    table = get_table(query.sort.datatype, sort_key)
                    key_id = self.get_property_id(type, sort_key)
                    if key_id is None:
                        raise StopIteration
                    q = "%(table)s.key_id=$key_id" % {"table": table}
                    wheres.append(web.reparam(q, locals()))
                    order = table.label + ".value"
                return order + ascending
            else:
                return None
コード例 #6
0
ファイル: db.py プロジェクト: candeira/openlibrary
def query(category, olid, offset=0, limit=10):
    category_id = get_category_id(category)
    
    if isinstance(olid, list):
        where = web.reparam('deleted=false AND category_id = $category_id AND ', locals()) \
                + web.sqlors('olid=', olid)
    elif olid is None:
        where = web.reparam('deleted=false AND category_id=$category_id', locals())
    else:
        where = web.reparam('deleted=false AND category_id=$category_id AND olid=$olid', locals())
    
    result = getdb().select('cover',
        what='*',
        where= where,
        order='last_modified desc', 
        offset=offset,
        limit=limit)
    return result.list()
コード例 #7
0
ファイル: db.py プロジェクト: RaceList/openlibrary
def query(category, olid, offset=0, limit=10):
    category_id = get_category_id(category)
    deleted = False
    
    if isinstance(olid, list):
        if len(olid) == 0:
            olid = [-1]
        where = web.reparam('deleted=$deleted AND category_id = $category_id AND olid IN $olid', locals())
    elif olid is None:
        where = web.reparam('deleted=$deleted AND category_id=$category_id', locals())
    else:
        where = web.reparam('deleted=$deleted AND category_id=$category_id AND olid=$olid', locals())
    
    result = getdb().select('cover',
        what='*',
        where= where,
        order='last_modified desc', 
        offset=offset,
        limit=limit)
    return result.list()
コード例 #8
0
ファイル: bulkupload.py プロジェクト: EdwardBetts/infogami
def sqlin(name, values):
    """
        >>> sqlin('id', [1, 2, 3, 4])
        <sql: 'id IN (1, 2, 3, 4)'>
        >>> sqlin('id', [])
        <sql: '1 = 2'>
    """
    def sqljoin(queries, sep):
        result = ""
        for q in queries:
            if result:
                result = result + sep
            result = result + q
        return result
    
    if not values:
        return web.reparam('1 = 2', {})
    else:
        values = [web.reparam('$v', locals()) for v in values]
        return name + ' IN ('+ sqljoin(values, ", ") + ')'
コード例 #9
0
ファイル: settings.py プロジェクト: riolet/SAM
 def __init__(self, db, session, subscription):
     """
     :type db: web.DB
     :type session: dict
     :type subscription: numbers.Integral
     :param db: 
     :param session: 
     :param subscription: 
     """
     self.db = db
     self.sub = subscription
     self.where = web.reparam("subscription=$id", {'id': self.sub})
     self.storage = session
コード例 #10
0
ファイル: dbstore.py プロジェクト: EdwardBetts/infogami
    def get_many(self, keys):
        if not keys:
            return '{}'

        xkeys = [web.reparam('$k', dict(k=k)) for k in keys]
        query = 'SELECT thing.key, data.data from thing, data ' \
            + 'WHERE data.revision = thing.latest_revision and data.thing_id=thing.id ' \
            + ' AND thing.key IN (' + self.sqljoin(xkeys, ', ') + ')'
            
        def process(query):
            yield '{\n'
            for i, r in enumerate(self.db.query(query)):
                if i:
                    yield ',\n'
                yield simplejson.dumps(r.key)
                yield ": "
                yield r.data
            yield '}'
        return "".join(process(query))
コード例 #11
0
ファイル: ports.py プロジェクト: riolet/SAM
    def get(self, ports):
        if isinstance(ports, list):
            arg = "({0})".format(",".join(map(str, ports)))
        else:
            arg = "({0})".format(ports)

        query = """SELECT aliases.port,
    COALESCE(ports.name, '') AS 'name',
    COALESCE(ports.protocols, '') AS 'protocols',
    COALESCE(ports.description, '') AS 'description',
    aliases.active,
    aliases.name AS alias_name,
    aliases.description AS alias_description
FROM {table_aliases} AS `aliases`
LEFT JOIN {table_ports} AS `ports`
    ON ports.port=aliases.port
WHERE aliases.port IN {port_list}
ORDER BY aliases.port ASC""".format(
            table_ports=self.table_ports,
            table_aliases=self.table_aliases,
            port_list=web.reparam(arg, {}))

        rows = self.db.query(query, vars={'plist': arg})
        return list(rows)
コード例 #12
0
ファイル: dbstore.py プロジェクト: rlugojr/infogami
    def get_many(self, keys):
        if not keys:
            return "{}"

        xkeys = [web.reparam("$k", dict(k=k)) for k in keys]
        query = (
            "SELECT thing.key, data.data from thing, data "
            + "WHERE data.revision = thing.latest_revision and data.thing_id=thing.id "
            + " AND thing.key IN ("
            + self.sqljoin(xkeys, ", ")
            + ")"
        )

        def process(query):
            yield "{\n"
            for i, r in enumerate(self.db.query(query)):
                if i:
                    yield ",\n"
                yield simplejson.dumps(r.key)
                yield ": "
                yield process_json(r.key, r.data)
            yield "}"

        return "".join(process(query))
コード例 #13
0
ファイル: dbstore.py プロジェクト: paulproteus/infogami
    def versions(self, query):
        what = 'thing.key, version.revision, transaction.*'
        where = 'version.thing_id = thing.id AND version.transaction_id = transaction.id'

        if config.get('use_machine_comment'):
            what += ", version.machine_comment"

        def get_id(key):
            meta = self.get_metadata(key)
            if meta:
                return meta.id
            else:
                raise StopIteration

        for c in query.conditions:
            key, value = c.key, c.value
            assert key in [
                'key', 'type', 'author', 'ip', 'comment', 'created', 'bot',
                'revision'
            ]

            try:
                if key == 'key':
                    key = 'thing_id'
                    value = get_id(value)
                elif key == 'revision':
                    key = 'version.revision'
                elif key == 'type':
                    key = 'thing.type'
                    value = get_id(value)
                elif key == 'author':
                    key = 'transaction.author_id'
                    value = get_id(value)
                else:
                    # 'bot' column is not enabled
                    if key == 'bot' and not config.use_bot_column:
                        bots = get_bot_users(self.db)
                        if value == True or str(value).lower() == "true":
                            where += web.reparam(
                                " AND transaction.author_id IN $bots",
                                {"bots": bots})
                        else:
                            where += web.reparam(
                                " AND (transaction.author_id NOT IN $bots OR transaction.author_id IS NULL)",
                                {"bots": bots})
                        continue
                    else:
                        key = 'transaction.' + key
            except StopIteration:
                # StopIteration is raised when a non-existing object is referred in the query
                return []

            where += web.reparam(' AND %s=$value' % key, locals())

        sort = query.sort
        if sort and sort.startswith('-'):
            sort = sort[1:] + ' desc'

        sort = 'transaction.' + sort

        t = self.db.transaction()
        if config.query_timeout:
            self.db.query(
                "SELECT set_config('statement_timeout', $query_timeout, false)",
                dict(query_timeout=config.query_timeout))

        result = self.db.select(['thing', 'version', 'transaction'],
                                what=what,
                                where=where,
                                offset=query.offset,
                                limit=query.limit,
                                order=sort)
        result = result.list()
        author_ids = list(set(r.author_id for r in result if r.author_id))
        authors = self.get_metadata_list_from_ids(author_ids)

        t.commit()

        for r in result:
            r.author = r.author_id and authors[r.author_id].key
        return result
コード例 #14
0
ファイル: dbstore.py プロジェクト: purhan/infogami
 def get_metadata_list(self, keys):
     where = web.reparam('site_id=$self.site_id', locals()) + web.sqlors(
         'key=', keys)
     result = self.db.select('thing', what='*', where=where).list()
     d = dict((r.key, r) for r in result)
     return d
コード例 #15
0
ファイル: read.py プロジェクト: strogo/infogami
    def recentchanges(self, limit=100, offset=0, **kwargs):
        tables = ["transaction t"]
        what = "t.*"
        order = "t.created DESC"
        wheres = ["1 = 1"]

        key = kwargs.pop("key", None)
        if key is not None:
            thing_id = self.get_thing_id(key)
            if thing_id is None:
                return []
            else:
                tables.append("version v")
                wheres.append("v.transaction_id = t.id AND v.thing_id = $thing_id")

        bot = kwargs.pop("bot", None)
        if bot is not None:
            bot_ids = [r.thing_id for r in self.db.query("SELECT thing_id FROM account WHERE bot='t'")] or [-1]
            if bot is True or str(bot).lower() == "true":
                wheres.append("t.author_id IN $bot_ids")
            else:
                wheres.append("(t.author_id NOT in $bot_ids OR t.author_id IS NULL)")

        author = kwargs.pop("author", None)
        if author is not None:
            author_id = self.get_thing_id(author)
            if not author_id:
                # Unknown author. Implies no changes by him.
                return []
            else:
                wheres.append("t.author_id=$author_id")

        ip = kwargs.pop("ip", None)
        if ip is not None:
            if not self._is_valid_ipv4(ip):
                return []
            else:
                # Don't include edits by logged in users when queried by ip.
                wheres.append("t.ip = $ip AND t.author_id is NULL")

        kind = kwargs.pop("kind", None)
        if kind is not None:
            wheres.append("t.action = $kind")

        begin_date = kwargs.pop("begin_date", None)
        if begin_date is not None:
            wheres.append("t.created >= $begin_date")

        end_date = kwargs.pop("end_date", None)
        if end_date is not None:
            # end_date is not included in the interval.
            wheres.append("t.created < $end_date")

        data = kwargs.pop("data", None)
        if data:
            for i, (k, v) in enumerate(data.items()):
                t = "ti%d" % i
                tables.append("transaction_index " + t)
                q = "%s.tx_id = t.id AND %s.key=$k AND %s.value=$v" % (t, t, t)
                # k, v are going to change in the next iter of the loop.
                # bind the current values by calling reparam.
                wheres.append(web.reparam(q, locals()))

        wheres = list(self._process_wheres(wheres, locals()))
        where = web.SQLQuery.join(wheres, " AND ")

        rows = self.db.select(
            tables, what=what, where=where, limit=limit, offset=offset, order=order, vars=locals()
        ).list()

        authors = self.get_keys(row.author_id for row in rows)

        return [self._process_transaction(row, authors) for row in rows]
コード例 #16
0
ファイル: read.py プロジェクト: strogo/infogami
 def _process_wheres(self, wheres, vars):
     for w in wheres:
         if isinstance(w, basestring):
             yield web.reparam(w, vars)
         else:
             yield w
コード例 #17
0
ファイル: dbstore.py プロジェクト: EdwardBetts/infogami
 def get_metadata_list(self, keys):
     where = web.reparam('site_id=$self.site_id', locals()) + web.sqlors('key=', keys)
     result = self.db.select('thing', what='*', where=where).list()
     d = dict((r.key, r) for r in result)
     return d
コード例 #18
0
ファイル: dbstore.py プロジェクト: rlugojr/infogami
    def versions(self, query):
        what = "thing.key, version.revision, transaction.*"
        where = "version.thing_id = thing.id AND version.transaction_id = transaction.id"

        if config.get("use_machine_comment"):
            what += ", version.machine_comment"

        def get_id(key):
            meta = self.get_metadata(key)
            if meta:
                return meta.id
            else:
                raise StopIteration

        for c in query.conditions:
            key, value = c.key, c.value
            assert key in ["key", "type", "author", "ip", "comment", "created", "bot", "revision"]

            try:
                if key == "key":
                    key = "thing_id"
                    value = get_id(value)
                elif key == "revision":
                    key = "version.revision"
                elif key == "type":
                    key = "thing.type"
                    value = get_id(value)
                elif key == "author":
                    key = "transaction.author_id"
                    value = get_id(value)
                else:
                    # 'bot' column is not enabled
                    if key == "bot" and not config.use_bot_column:
                        bots = get_bot_users(self.db)
                        if value == True or str(value).lower() == "true":
                            where += web.reparam(" AND transaction.author_id IN $bots", {"bots": bots})
                        else:
                            where += web.reparam(
                                " AND (transaction.author_id NOT IN $bots OR transaction.author_id IS NULL)",
                                {"bots": bots},
                            )
                        continue
                    else:
                        key = "transaction." + key
            except StopIteration:
                # StopIteration is raised when a non-existing object is referred in the query
                return []

            where += web.reparam(" AND %s=$value" % key, locals())

        sort = query.sort
        if sort and sort.startswith("-"):
            sort = sort[1:] + " desc"

        sort = "transaction." + sort

        t = self.db.transaction()
        if config.query_timeout:
            self.db.query(
                "SELECT set_config('statement_timeout', $query_timeout, false)",
                dict(query_timeout=config.query_timeout),
            )

        result = self.db.select(
            ["thing", "version", "transaction"],
            what=what,
            where=where,
            offset=query.offset,
            limit=query.limit,
            order=sort,
        )
        result = result.list()
        author_ids = list(set(r.author_id for r in result if r.author_id))
        authors = self.get_metadata_list_from_ids(author_ids)

        t.commit()

        for r in result:
            r.author = r.author_id and authors[r.author_id].key
        return result
コード例 #19
0
ファイル: dbstore.py プロジェクト: rlugojr/infogami
 def get_metadata_list(self, keys):
     where = web.reparam("site_id=$self.site_id", locals()) + web.sqlors("key=", keys)
     result = self.db.select("thing", what="*", where=where).list()
     d = dict((r.key, r) for r in result)
     return d
コード例 #20
0
 def _process_wheres(self, wheres, vars):
     for w in wheres:
         if isinstance(w, string_types):
             yield web.reparam(w, vars)
         else:
             yield w
コード例 #21
0
    def recentchanges(self, limit=100, offset=0, **kwargs):
        tables = ['transaction t']
        what = 't.*'
        order = 't.created DESC'
        wheres = ["1 = 1"]

        if offset < 0:
            offset = 0

        key = kwargs.pop('key', None)
        if key is not None:
            thing_id = self.get_thing_id(key)
            if thing_id is None:
                return []
            else:
                tables.append('version v')
                wheres.append('v.transaction_id = t.id AND v.thing_id = $thing_id')

        bot = kwargs.pop('bot', None)
        if bot is not None:
            bot_ids = get_bot_users(self.db)
            if bot is True or str(bot).lower() == "true":
                wheres.append("t.author_id IN $bot_ids")
            else:
                wheres.append("(t.author_id NOT in $bot_ids OR t.author_id IS NULL)")

        author = kwargs.pop('author', None)
        if author is not None:
            author_id = self.get_thing_id(author)
            if not author_id:
                # Unknown author. Implies no changes by him.
                return []
            else:
                wheres.append("t.author_id=$author_id")

        ip = kwargs.pop("ip", None)
        if ip is not None:
            if not self._is_valid_ipv4(ip):
                return []
            else:
                # Don't include edits by logged in users when queried by ip.
                wheres.append("t.ip = $ip AND t.author_id is NULL")

        kind = kwargs.pop('kind', None)
        if kind is not None:
            wheres.append('t.action = $kind')

        begin_date = kwargs.pop('begin_date', None)
        if begin_date is not None:
            wheres.append("t.created >= $begin_date")

        end_date = kwargs.pop('end_date', None)
        if end_date is not None:
            # end_date is not included in the interval.
            wheres.append("t.created < $end_date")

        data = kwargs.pop('data', None)
        if data:
            for i, (k, v) in enumerate(data.items()):
                t = 'ti%d' % i
                tables.append('transaction_index ' + t)
                q = '%s.tx_id = t.id AND %s.key=$k AND %s.value=$v' % (t, t, t)
                # k, v are going to change in the next iter of the loop.
                # bind the current values by calling reparam.
                wheres.append(web.reparam(q, locals()))


        wheres = list(self._process_wheres(wheres, locals()))
        where = web.SQLQuery.join(wheres, " AND ")

        rows = self.db.select(tables, what=what, where=where, limit=limit, offset=offset, order=order, vars=locals()).list()

        authors = self.get_keys(row.author_id for row in rows)

        return [self._process_transaction(row, authors) for row in rows]