예제 #1
0
def user(db, lang, username=None, format=None):
    params = query._params()
    if username:
        params.users = utils.pg_escape(username.decode("utf-8")).split(",")
    params.limit = 500
    params.full = True
    username = "******".join(params.users)

    if not params.users:
        return template('byuser/index', translate=utils.translator(lang))

    errors = query._gets(db, params)
    count = len(errors)
    if request.path.startswith("/api") or format == "json":
        out = OrderedDict()
        for res in errors:
            res["timestamp"] = str(res["timestamp"])
            res["lat"] = float(res["lat"])
            res["lon"] = float(res["lon"])
        out["issues"] = map(dict, errors)
        return out

    elif format == 'rss':
        response.content_type = "application/rss+xml"
        return template('byuser/byuser.rss', username=username, users=params.users, count=count, errors=errors, translate=utils.translator(lang), website=utils.website)

    else:
        return template('byuser/byuser', username=username, users=params.users, count=count, errors=errors, translate=utils.translator(lang), website=utils.website, main_website=utils.main_website, remote_url_read=utils.remote_url_read, html_escape=html_escape)
예제 #2
0
def user(db, lang, username=None, format=None):
    params = query._params()
    if username:
        params.users = utils.pg_escape(username.decode("utf-8")).split(",")
    params.limit = 500
    params.full = True
    username = "******".join(params.users)

    if not params.users:
        return template('byuser/index')

    errors = query._gets(db, params)
    count = len(errors)
    if request.path.startswith("/api") or format == "json":
        out = OrderedDict()
        out["description"] = ["id", "item", "lat", "lon", "source", "class", "elems", "subclass", "subtitle", "comment", "title", "level", "timestamp", "menu", "username", "date"]
        for res in errors:
            res["timestamp"] = str(res["timestamp"])
            res["date"] = str(res["date"])
        out["byusers"] = errors
        return out

    elif format == 'rss':
        response.content_type = "application/rss+xml"
        return template('byuser/byuser.rss', username=username, users=params.users, count=count, errors=errors, translate=utils.translator(lang), website=utils.website)

    else:
        return template('byuser/byuser', username=username, users=params.users, count=count, errors=errors, translate=utils.translator(lang), website=utils.website, main_website=utils.main_website, remote_url_read=utils.remote_url_read, html_escape=html_escape)
예제 #3
0
def _user_count(db, username=None):
    params = query._params()
    if username:
        params.users = utils.pg_escape(username.decode("utf-8")).split(",")

    if not params.users:
        return

    res = query._count(db, params, ['dynpoi_class.level'], ['dynpoi_class.level'])
    ret = {1:0, 2:0, 3:0}
    for (l, c) in res:
        ret[l] = c

    return ret
예제 #4
0
def _user_count(db, username=None):
    params = query._params()
    if username:
        params.users = utils.pg_escape(username.decode("utf-8")).split(",")

    if not params.users:
        return

    res = query._count(db, params, ['class.level'], ['class.level'])
    ret = {1:0, 2:0, 3:0}
    for (l, c) in res:
        ret[l] = c

    return ret
예제 #5
0
def user(db, lang, username=None, format=None):
    params = query._params()
    if username:
        params.users = utils.pg_escape(username.decode("utf-8")).split(",")
    params.limit = 500
    params.full = True
    username = "******".join(params.users)

    if not params.users:
        return template('byuser/index')

    errors = query._gets(db, params)
    count = len(errors)
    if request.path.startswith("/api") or format == "json":
        out = OrderedDict()
        out["description"] = [
            "id", "item", "lat", "lon", "source", "class", "elems", "subclass",
            "subtitle", "comment", "title", "level", "timestamp", "menu",
            "username", "date"
        ]
        for res in errors:
            res["timestamp"] = str(res["timestamp"])
            res["date"] = str(res["date"])
        out["byusers"] = errors
        return out

    elif format == 'rss':
        response.content_type = "application/rss+xml"
        return template('byuser/byuser.rss',
                        username=username,
                        users=params.users,
                        count=count,
                        errors=errors,
                        translate=utils.translator(lang),
                        website=utils.website)

    else:
        return template('byuser/byuser',
                        username=username,
                        users=params.users,
                        count=count,
                        errors=errors,
                        translate=utils.translator(lang),
                        website=utils.website,
                        main_website=utils.main_website,
                        remote_url_read=utils.remote_url_read,
                        html_escape=html_escape)
예제 #6
0
 class Params:
     bbox = request.params.get('bbox', default=None)
     item = request.params.get('item')
     source = request.params.get('source', default='')
     classs = request.params.get('class', default='')
     users = utils.pg_escape(
         request.params.get('username', default='').decode('utf-8'))
     level = request.params.get('level', default='1,2,3')
     full = request.params.get('full', default=False)
     zoom = request.params.get('zoom', type=int, default=10)
     limit = request.params.get('limit', type=int, default=100)
     country = request.params.get('country', default=None)
     useDevItem = request.params.get('useDevItem', default=False)
     status = request.params.get('status', default="open")
     start_date = request.params.get('start_date', default=None)
     end_date = request.params.get('end_date', default=None)
     tags = request.params.get('tags', default=None)
     fixable = request.params.get('fixable', default=None)
예제 #7
0
def _build_param(db,
                 bbox,
                 source,
                 item,
                 level,
                 users,
                 classs,
                 country,
                 useDevItem,
                 status,
                 tags,
                 fixable,
                 forceTable=[],
                 summary=False,
                 stats=False,
                 start_date=None,
                 end_date=None,
                 last_update=None,
                 tilex=None,
                 tiley=None,
                 zoom=None,
                 osm_type=None,
                 osm_id=None):
    join = ""
    where = ["1=1"]

    if summary:
        join += "dynpoi_class AS marker"
    elif stats:
        join += "stats AS marker"
    elif status in ("done", "false"):
        join += "dynpoi_status AS marker"
        where.append("marker.status = '%s'" % status)
    else:
        join += "marker"

    if source:
        sources = source.split(",")
        source2 = []
        for source in sources:
            source = source.split("-")
            if len(source) == 1:
                source2.append("(marker.source=%d)" % int(source[0]))
            else:
                source2.append("(marker.source=%d AND marker.class=%d)" %
                               (int(source[0]), int(source[1])))
        sources2 = " OR ".join(source2)
        where.append("(%s)" % sources2)

    tables = list(forceTable)
    tablesLeft = []

    if join.startswith("marker"):
        itemField = "marker"
    else:
        if item:
            tables.append("dynpoi_class")
        itemField = "dynpoi_class"

    if (level and level != "1,2,3") or tags:
        tables.append("class")
    if country is not None:
        tables.append("source")
    if not stats:
        tables.append("dynpoi_item")
        if useDevItem:
            tablesLeft.append("dynpoi_item")
    if last_update:
        tables.append("dynpoi_update_last")

    if "dynpoi_class" in tables:
        join += """
        JOIN dynpoi_class ON
            marker.source = dynpoi_class.source AND
            marker.class = dynpoi_class.class"""

    if "class" in tables:
        join += """
        JOIN class ON
            """ + itemField + """.item = class.item AND
            """ + itemField + """.class = class.class"""

    if "source" in tables:
        join += """
        JOIN source ON
            marker.source = source.id"""

    if "dynpoi_item" in tables:
        join += """
        %sJOIN dynpoi_item ON
            %s.item = dynpoi_item.item""" % ("LEFT " if "dynpoi_item"
                                             in tablesLeft else "", itemField)

    if "dynpoi_update_last" in tables:
        join += """
        JOIN dynpoi_update_last ON
            dynpoi_update_last.source = marker.source"""

    if item != None:
        where.append(_build_where_item(item, itemField))

    if level and level != "1,2,3":
        where.append("class.level IN (%s)" % level)

    if classs:
        where.append("marker.class IN (%s)" %
                     ','.join(map(lambda c: str(int(c)), classs.split(','))))

    if bbox:
        where.append(
            "marker.lat BETWEEN %f AND %f AND marker.lon BETWEEN %f AND %f" %
            (bbox[1], bbox[3], bbox[0], bbox[2]))
        # Compute a tile to use index
        tilex, tiley, zoom = tiles.bbox2tile(*bbox)
        if zoom < 8:
            zoom = 8
            tilex, tiley = tiles.lonlat2tile((bbox[0] + bbox[2]) / 2,
                                             (bbox[1] + bbox[3]) / 2, zoom)

    if tilex and tiley and zoom:
        where.append(
            "lonlat2z_order_curve(lon, lat) BETWEEN zoc18min(z_order_curve({x}, {y}), {z}) AND zoc18max(z_order_curve({x}, {y}), {z}) AND lat > -90"
            .format(z=zoom, x=tilex, y=tiley))

    if country is not None:
        if len(country) >= 1 and country[-1] == "*":
            country = country[:-1] + "%"
        where.append("source.country LIKE '%s'" % country)

    if not status in ("done", "false") and useDevItem == True:
        where.append("dynpoi_item.item IS NULL")

    if not status in ("done", "false") and users:
        where.append("ARRAY['%s'] && marker_usernames(marker.elems)" %
                     "','".join(
                         map(
                             lambda user: utils.pg_escape(
                                 db.mogrify(user).decode('utf-8')), users)))

    if stats:
        if start_date and end_date:
            where.append(
                "marker.timestamp_range && tsrange('{0}', '{1}', '[]')".format(
                    start_date.isoformat(), end_date.isoformat()))
        elif start_date:
            where.append(
                "marker.timestamp_range && tsrange('{0}', NULL, '[)')".format(
                    start_date.isoformat()))
        elif end_date:
            where.append(
                "marker.timestamp_range && tsrange(NULL, '{1}', '(]')".format(
                    end_date.isoformat()))
    elif status in ("done", "false"):
        if start_date:
            where.append("marker.date > '%s'" % start_date.isoformat())
        if end_date:
            where.append("marker.date < '%s'" % end_date.isoformat())

    if tags:
        where.append("class.tags::text[] && ARRAY['%s']" %
                     "','".join(map(utils.pg_escape, tags)))

    if fixable == 'online':
        where.append(
            "(SELECT bool_or(fix->'id' != '0'::jsonb) FROM (SELECT jsonb_array_elements(unnest(fixes))) AS t(fix))"
        )
    elif fixable == 'josm':
        where.append("fixes IS NOT NULL")

    if osm_type and osm_id and join.startswith("marker"):
        where.append('ARRAY[%s::bigint] <@ marker_elem_ids(elems)' %
                     (osm_id, ))  # Match the index
        where.append(
            '(SELECT bool_or(elem->\'type\' = \'"%s"\'::jsonb AND elem->\'id\' = \'%s\'::jsonb) FROM (SELECT unnest(elems)) AS t(elem))'
            % (osm_type[0].upper(), osm_id))  # Recheck with type

    return (join, " AND\n        ".join(where))
예제 #8
0
    def update_timestamp(self, attrs):
        self.ts = attrs.get("timestamp",
                            time.strftime("%Y-%m-%dT%H:%M:%SZ", time.gmtime()))
        self.version = attrs.get("version", None)
        self.analyser_version = attrs.get("analyser_version", None)

        if not self._tstamp_updated:
            try:
                execute_sql(
                    self._dbcurs,
                    "INSERT INTO dynpoi_update (source, timestamp, remote_url, remote_ip, version, analyser_version) VALUES(%s, %s, %s, %s, %s, %s);",
                    (self._source_id, utils.pg_escape(
                        self.ts), utils.pg_escape(self._source_url),
                     utils.pg_escape(
                         self._remote_ip), utils.pg_escape(self.version),
                     utils.pg_escape(self.analyser_version)))
            except psycopg2.IntegrityError:
                self._dbconn.rollback()
                execute_sql(
                    self._dbcurs,
                    "SELECT count(*) FROM dynpoi_update WHERE source = %s AND \"timestamp\" = %s",
                    (self._source_id, utils.pg_escape(self.ts)))
                r = self._dbcurs.fetchone()
                if r["count"] == 1:
                    raise OsmoseUpdateAlreadyDone(
                        "source=%s and timestamp=%s are already present" %
                        (self._source_id, utils.pg_escape(self.ts)))
                else:
                    raise

            execute_sql(
                self._dbcurs,
                "UPDATE dynpoi_update_last SET timestamp=%s, version=%s, analyser_version=%s, remote_ip=%s WHERE source=%s;",
                (utils.pg_escape(self.ts), utils.pg_escape(
                    self.version), utils.pg_escape(self.analyser_version),
                 utils.pg_escape(self._remote_ip), self._source_id))
            if self._dbcurs.rowcount == 0:
                execute_sql(
                    self._dbcurs,
                    "INSERT INTO dynpoi_update_last(source, timestamp, version, analyser_version, remote_ip) VALUES(%s, %s, %s, %s, %s);",
                    (self._source_id, utils.pg_escape(
                        self.ts), utils.pg_escape(self.version),
                     utils.pg_escape(self.analyser_version),
                     utils.pg_escape(self._remote_ip)))

            self._tstamp_updated = True
예제 #9
0
    def endElement(self, name):
        self.element_stack.pop()

        if name == u"analyser":
            for class_id, uuid in self.all_uuid.items():
                execute_sql(
                    self._dbcurs,
                    "DELETE FROM marker WHERE source = %s AND class = %s AND uuid != ALL (%s::uuid[])",
                    (self._source_id, class_id, uuid))

        elif name == u"error":
            ## add data at all location
            if len(self._error_locations) == 0:
                print("No location on error found on line %d" %
                      self.locator.getLineNumber())
                return

            elems = list(
                filter(
                    lambda e: e,
                    map(
                        lambda elem: dict(
                            filter(
                                lambda k_v: k_v[1], {
                                    'type': elem['type'][0].upper(),
                                    'id': int(elem['id']),
                                    'tags': elem['tag'],
                                    'username': elem['user'],
                                }.items())) if elem['type'] in
                        ('node', 'way', 'relation') else dict(
                            filter(lambda k_v: k_v[1], {
                                'tags': elem['tag'],
                                'username': elem['user'],
                            }.items())) if elem['type'] in ('infos') else None,
                        self._error_elements)))

            fixes = list(
                map(
                    lambda fix: list(
                        map(
                            lambda elem: dict(
                                filter(
                                    lambda k_v: k_v[1], {
                                        'type': elem['type'][0].upper(),
                                        'id': int(elem['id']),
                                        'create': elem['create'],
                                        'modify': elem['modify'],
                                        'delete': elem['delete'],
                                    }.items())),
                            filter(
                                lambda elem: elem['type'] in
                                ('node', 'way', 'relation'), fix))),
                    self._fixes))

            sql_uuid = u"SELECT ('{' || encode(substring(digest(%(source)s || '/' || %(class)s || '/' || %(subclass)s || '/' || %(elems_sig)s, 'sha256') from 1 for 16), 'hex') || '}')::uuid AS uuid"

            ## sql template
            sql_marker = u"INSERT INTO marker (uuid, source, class, item, lat, lon, elems, fixes, subtitle) "
            sql_marker += u"VALUES (('{' || encode(substring(digest(%(source)s || '/' || %(class)s || '/' || %(subclass)s || '/' || %(elems_sig)s, 'sha256') from 1 for 16), 'hex') || '}')::uuid, "
            sql_marker += u"%(source)s, %(class)s, %(item)s, %(lat)s, %(lon)s, %(elems)s::jsonb[], %(fixes)s::jsonb[], %(subtitle)s) "
            sql_marker += u"ON CONFLICT (uuid) DO "
            sql_marker += u"UPDATE SET item = %(item)s, lat = %(lat)s, lon = %(lon)s, elems = %(elems)s::jsonb[], fixes = %(fixes)s::jsonb[], subtitle = %(subtitle)s "
            sql_marker += u"WHERE marker.uuid = ('{' || encode(substring(digest(%(source)s || '/' || %(class)s || '/' || %(subclass)s || '/' || %(elems_sig)s, 'sha256') from 1 for 16), 'hex') || '}')::uuid AND "
            sql_marker += u"      marker.source = %(source)s AND marker.class = %(class)s AND "
            sql_marker += u"      (marker.item != %(item)s OR marker.lat != %(lat)s OR marker.lon != %(lon)s OR marker.elems != %(elems)s::jsonb[] OR marker.fixes != %(fixes)s::jsonb[] OR marker.subtitle != %(subtitle)s) "
            sql_marker += u"RETURNING uuid"

            for location in self._error_locations:
                lat = float(location["lat"])
                lon = float(location["lon"])

                params = {
                    "source":
                    self._source_id,
                    "class":
                    self._class_id,
                    "subclass":
                    self._class_sub,
                    "item":
                    self._class_item[self._class_id],
                    "lat":
                    lat,
                    "lon":
                    lon,
                    "elems_sig":
                    '_'.join(
                        map(lambda elem: elem['type'] + str(elem['id']),
                            self._error_elements)),
                    "elems":
                    list(map(lambda elem: json.dumps(elem), elems))
                    if elems else None,
                    "fixes":
                    list(map(lambda fix: json.dumps(fix), fixes))
                    if fixes else None,
                    "subtitle":
                    self._error_texts,
                }

                execute_sql(self._dbcurs, sql_uuid, params)
                r = self._dbcurs.fetchone()
                if r and r[0]:
                    self.all_uuid[self._class_id].append(r[0])

                execute_sql(self._dbcurs, sql_marker, params)
                self._dbcurs.fetchone()

        elif name in [u"node", u"way", u"relation", u"infos"]:
            if self.elem_mode == "info":
                self._elem[u"tag"] = self._elem_tags
                self._error_elements.append(self._elem)
            else:
                self._elem[u"create"] = self._fix_create
                self._elem[u"modify"] = self._fix_modify
                self._elem[u"delete"] = self._fix_delete
                self._fix.append(self._elem)

        elif name == u"class":
            self.all_uuid[self._class_id] = []

            # Commit class update on its own transaction. Avoid lock the class table and block other updates.
            dbconn = utils.get_dbconn()
            dbcurs = dbconn.cursor()
            sql = u"INSERT INTO class (class, item, title, level, tags, detail, fix, trap, example, source, resource, timestamp) "
            sql += u"VALUES (%(class)s, %(item)s, %(title)s, %(level)s, %(tags)s, %(detail)s, %(fix)s, %(trap)s, %(example)s, %(source)s, %(resource)s, %(timestamp)s) "
            sql += u"ON CONFLICT (item, class) DO "
            sql += u"UPDATE SET title = %(title)s, level = %(level)s, tags = %(tags)s, detail = %(detail)s, fix = %(fix)s, trap = %(trap)s, example = %(example)s, source = %(source)s, resource = %(resource)s, timestamp = %(timestamp)s "
            sql += u"WHERE class.class = %(class)s AND class.item = %(item)s AND class.timestamp < %(timestamp)s AND "
            sql += u"      (class.title != %(title)s OR class.level != %(level)s OR class.tags != %(tags)s::varchar[] OR class.detail != %(detail)s OR class.fix != %(fix)s OR class.trap != %(trap)s OR class.example != %(example)s OR class.source != %(source)s OR class.resource != %(resource)s)"
            execute_sql(
                dbcurs, sql, {
                    'class': self._class_id,
                    'item': self._class_item[self._class_id],
                    'title': self._class_title,
                    'level': self._class_level,
                    'tags': self._class_tags,
                    'detail': self._class_detail or None,
                    'fix': self._class_fix or None,
                    'trap': self._class_trap or None,
                    'example': self._class_example or None,
                    'source': self._class_source or None,
                    'resource': self._class_resource or None,
                    'timestamp': utils.pg_escape(self.ts),
                })
            dbconn.commit()
            dbconn.close()

            sql = u"INSERT INTO dynpoi_class (source, class, item, timestamp) "
            sql += u"VALUES (%(source)s, %(class)s, %(item)s, %(timestamp)s)"
            sql += u"ON CONFLICT (source, class) DO "
            sql += u"UPDATE SET item = %(item)s, timestamp = %(timestamp)s "
            sql += u"WHERE dynpoi_class.source = %(source)s AND dynpoi_class.class = %(class)s"
            execute_sql(
                self._dbcurs, sql, {
                    'source': self._source_id,
                    'class': self._class_id,
                    'item': self._class_item[self._class_id],
                    'timestamp': utils.pg_escape(self.ts),
                })

        elif name == u"fixes":
            self.elem_mode = "info"
        elif name == u"fix" and self.element_stack[-1] == u"fixes":
            self._fixes.append(self._fix)