def setUp(self):
utils.pg_host = "localhost"
utils.pg_base = "osmose_test"
utils.pg_pass = "******"
utils.db_string = "host='%s' port='%s' dbname='%s' user='******' password='******'" % (
utils.pg_host, utils.pg_port, utils.pg_base, utils.pg_user,
utils.pg_pass)
self.dbconn = utils.get_dbconn()
self.dbcurs = self.dbconn.cursor()
self.dbcurs.execute(open("tools/database/drop.sql", "r").read())
self.dbcurs.execute(open("tools/database/schema.sql", "r").read())
# Re-initialise search_path as cleared by schema.sql
self.dbcurs.execute("SET search_path TO \"$user\", public;")
self.dbcurs.execute(
"INSERT INTO source (id, country, analyser) VALUES (%s, %s, %s);",
(1, "xx1", "yy1"))
self.dbcurs.execute(
"INSERT INTO source (id, country, analyser) VALUES (%s, %s, %s);",
(2, "xx2", "yy2"))
self.dbcurs.execute(
"INSERT INTO source_password (source_id, password) VALUES (%s, %s);",
(1, "xx1"))
self.dbcurs.execute(
"INSERT INTO source_password (source_id, password) VALUES (%s, %s);",
(2, "xx2"))
self.dbconn.commit()
def remove_bug(error_id, status):
PgConn = utils.get_dbconn()
PgCursor = PgConn.cursor()
# find source
PgCursor.execute(
"SELECT source,class,subclass,elems,lat,lon FROM marker WHERE id = %s;",
(error_id, ))
source_id = None
for res in PgCursor.fetchall():
source_id = res["source"]
class_id = res["class"]
sub_class = res["subclass"]
elems = res["elems"]
lat = res["lon"]
lon = res["lat"]
if not source_id:
return -1
if len(elems) > 1:
PgCursor.execute(
"""DELETE FROM dynpoi_status
WHERE source=%s AND class=%s AND subclass=%s AND elems=%s;""",
(source_id, class_id, sub_class, elems))
else:
PgCursor.execute(
"""DELETE FROM dynpoi_status
WHERE source=%s AND class=%s AND subclass=%s AND lat=%s AND lon=%s;""",
(source_id, class_id, sub_class, lat, lon))
PgCursor.execute(
"""INSERT INTO dynpoi_status
(id,source,class,subclass,elems,date,status,lat,lon,subtitle)
SELECT id,source,class,subclass,elems,NOW(),%s,
lat,lon,subtitle
FROM marker
WHERE id = %s
ON CONFLICT DO NOTHING""", (status, error_id))
PgCursor.execute("DELETE FROM marker WHERE id = %s;", (error_id, ))
PgCursor.execute(
"UPDATE dynpoi_class SET count = count - 1 WHERE source = %s AND class = %s;",
(source_id, class_id))
PgConn.commit()
return 0
def remove_bug(error_id, status):
PgConn = utils.get_dbconn()
PgCursor = PgConn.cursor()
# find source
PgCursor.execute("SELECT source,class,subclass,elems,lat,lon FROM marker WHERE id = %s;",
(error_id, ))
source_id = None
for res in PgCursor.fetchall():
source_id = res["source"]
class_id = res["class"]
sub_class = res["subclass"]
elems = res["elems"]
lat = res["lon"]
lon = res["lat"]
if not source_id:
return -1
if len(elems) > 1:
PgCursor.execute("""DELETE FROM dynpoi_status
WHERE source=%s AND class=%s AND subclass=%s AND elems=%s;""",
(source_id,class_id,sub_class,elems))
else:
PgCursor.execute("""DELETE FROM dynpoi_status
WHERE source=%s AND class=%s AND subclass=%s AND lat=%s AND lon=%s;""",
(source_id,class_id,sub_class,lat,lon))
PgCursor.execute("""INSERT INTO dynpoi_status
(id,source,class,subclass,elems,date,status,lat,lon,subtitle)
SELECT id,source,class,subclass,elems,NOW(),%s,
lat,lon,subtitle
FROM marker
WHERE id = %s
ON CONFLICT DO NOTHING""",
(status,error_id))
PgCursor.execute("DELETE FROM marker WHERE id = %s;", (error_id, ))
PgCursor.execute("UPDATE dynpoi_class SET count = count - 1 WHERE source = %s AND class = %s;", (source_id, class_id))
PgConn.commit()
return 0
#! /usr/bin/env python
#-*- coding: utf-8 -*-
import sys, os, cgi
root = "/data/project/polygons/polygons-generation"
sys.path.append(root)
from tools import utils
form = cgi.FieldStorage()
rel_id = int(form.getvalue("id", -1))
params = str(form.getvalue("params", -1))
show = utils.show
PgConn = utils.get_dbconn()
PgCursor = PgConn.cursor()
sql = """select ST_AsEWKT(geom)
from polygons where id = %s AND params = %s"""
PgCursor.execute(sql, (rel_id, params))
results = PgCursor.fetchall()
if len(results) == 0:
show(u"Status: 500")
show(u"Content-Type: text/plain; charset=utf-8")
print
show(u"Error: Polygons wasn't correctly generated")
sys.exit(0)
show(u"Content-Type: text/plain; charset=utf-8")
def update(source_id, fname, logger=printlogger(), remote_ip=""):
## open connections
dbconn = utils.get_dbconn()
dbcurs = dbconn.cursor()
## xml parser
parser = make_parser()
parser.setContentHandler(
update_parser(source_id, fname, remote_ip, dbconn, dbcurs))
## open the file
if fname.endswith(".bz2"):
import bz2
f = bz2.BZ2File(fname)
elif fname.endswith(".gz"):
import gzip
f = gzip.open(fname)
else:
f = open(fname)
## parse the file
parser.parse(f)
## update subtitle from new errors
execute_sql(
dbcurs, """
UPDATE
dynpoi_status
SET
subtitle = marker.subtitle
FROM
marker
WHERE
marker.source = %s AND
dynpoi_status.uuid = marker.uuid
""", (source_id, ))
## remove false positive no longer present
# execute_sql(dbcurs, """DELETE FROM dynpoi_status
# WHERE (source,class,elems) NOT IN (SELECT source,class,elems FROM marker WHERE source = %s) AND
# source = %s AND
# date < now()-interval '7 day'""",
# (source_id, source_id, ))
execute_sql(
dbcurs, """
DELETE FROM
marker
USING
dynpoi_status
WHERE
marker.source = %s AND
dynpoi_status.uuid = marker.uuid
""", (source_id, ))
execute_sql(
dbcurs, """UPDATE dynpoi_class
SET count = (SELECT count(*) FROM marker
WHERE marker.source = dynpoi_class.source AND
marker.class = dynpoi_class.class)
WHERE dynpoi_class.source = %s""", (source_id, ))
## commit and close
dbconn.commit()
dbconn.close()
## close and delete
f.close()
del f
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)