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())
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 listitem(lang):
conn = utils.get_dbconn()
curs = conn.cursor()
items = {}
curs.execute(
"SELECT categ, COALESCE(menu->'%s', menu->'en') AS menu FROM dynpoi_categ;"
% lang)
for res in curs.fetchall():
items[res['categ']] = dict(res)
items[res['categ']]['item'] = {}
curs.execute(
"SELECT item, categ, COALESCE(menu->'%s', menu->'en') AS menu FROM dynpoi_item;"
% lang)
for res in curs.fetchall():
items[res['categ']]['item'][res['item']] = dict(res)
items[res['categ']]['item'][res['item']]['class'] = {}
curs.execute(
"SELECT class, dynpoi_class.item, dynpoi_item.categ, COALESCE(Max(title->'%s'), Max(title->'en')) AS title FROM dynpoi_class JOIN dynpoi_item ON dynpoi_item.item=dynpoi_class.item GROUP BY class, dynpoi_class.item, dynpoi_item.categ"
% lang)
for res in curs.fetchall():
if items[res['categ']]['item'].get(res['item']):
items[res['categ']]['item'][res['item']]['class'][
res['class']] = res['title']
return items
def listitem(lang):
conn = utils.get_dbconn()
curs = conn.cursor()
items = {}
curs.execute("SELECT categ, COALESCE(menu->'%s', menu->'en') AS menu FROM dynpoi_categ;" % lang)
for res in curs.fetchall():
items[res["categ"]] = dict(res)
items[res["categ"]]["item"] = {}
curs.execute("SELECT item, categ, COALESCE(menu->'%s', menu->'en') AS menu FROM dynpoi_item;" % lang)
for res in curs.fetchall():
items[res["categ"]]["item"][res["item"]] = dict(res)
items[res["categ"]]["item"][res["item"]]["class"] = {}
curs.execute(
"SELECT DISTINCT class, dynpoi_class.item, dynpoi_item.categ, COALESCE(title->'%s', title->'en') AS title FROM dynpoi_class JOIN dynpoi_item ON dynpoi_item.item=dynpoi_class.item"
% lang
)
for res in curs.fetchall():
if items[res["categ"]]["item"].get(res["item"]):
items[res["categ"]]["item"][res["item"]]["class"][res["class"]] = res["title"]
return items
def setUp(self):
utils.pg_base = "osmose_test"
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())
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 setUp(self):
utils.pg_base = "osmose_test"
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())
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 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 listitem(lang):
conn = utils.get_dbconn()
curs = conn.cursor()
items = {}
curs.execute("SELECT categ, COALESCE(menu->'%s', menu->'en') AS menu FROM dynpoi_categ;" % lang)
for res in curs.fetchall():
items[res['categ']] = dict(res)
items[res['categ']]['item'] = {}
curs.execute("SELECT item, categ, COALESCE(menu->'%s', menu->'en') AS menu FROM dynpoi_item;" % lang)
for res in curs.fetchall():
items[res['categ']]['item'][res['item']] = dict(res)
items[res['categ']]['item'][res['item']]['class'] = {}
curs.execute("SELECT class.class, class.item, dynpoi_item.categ, COALESCE(title->'%s', title->'en') AS title FROM class JOIN dynpoi_item ON dynpoi_item.item=class.item" % lang)
for res in curs.fetchall():
if items[res['categ']]['item'].get(res['item']):
items[res['categ']]['item'][res['item']]['class'][res['class']] = res['title']
return items
def update(source, url, logger=printlogger(), remote_ip=""):
source_id = int(source["id"])
## open connections
dbconn = utils.get_dbconn()
dbcurs = dbconn.cursor()
## xml parser
parser = make_parser()
parser.setContentHandler(
update_parser(source_id, source, url, remote_ip, dbconn, dbcurs))
## download the file if needed
if url.startswith("http://"):
socket.setdefaulttimeout(180)
tmp_path = "/tmp/osmose/"
if not os.path.exists(tmp_path):
os.makedirs(tmp_path)
fname = tempfile.mktemp(dir=tmp_path, prefix="update")
urllib.urlretrieve(url, fname)
#mysock = urllib.urlopen(source["url"])
#open(fname,'w').write(mysock.read())
istemp = True
else:
fname = url
istemp = False
## open the file
if url.endswith(".bz2"):
import bz2
f = bz2.BZ2File(fname)
elif url.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,
lat = marker.lat,
lon = marker.lon
FROM
marker
WHERE
marker.source = %s AND
marker.elems != '' AND
dynpoi_status.source = marker.source AND
dynpoi_status.class = marker.class AND
dynpoi_status.subclass = marker.subclass AND
dynpoi_status.elems = marker.elems
""", (source_id, ))
execute_sql(
dbcurs, """
UPDATE
dynpoi_status
SET
subtitle = marker.subtitle
FROM
marker
WHERE
marker.source = %s AND
marker.elems = '' AND
dynpoi_status.source = marker.source AND
dynpoi_status.class = marker.class AND
dynpoi_status.subclass = marker.subclass AND
dynpoi_status.elems = marker.elems AND
dynpoi_status.lat = marker.lat AND
dynpoi_status.lon = marker.lon
""", (source_id, ))
## remove false positive no longer present
# execute_sql(dbcurs, """DELETE FROM dynpoi_status
# WHERE (source,class,subclass,elems) NOT IN (SELECT source,class,subclass,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
marker.elems != '' AND
dynpoi_status.source = marker.source AND
dynpoi_status.class = marker.class AND
dynpoi_status.subclass = marker.subclass AND
dynpoi_status.elems = marker.elems
""", (source_id, ))
execute_sql(
dbcurs, """
DELETE FROM
marker
USING
dynpoi_status
WHERE
marker.source = %s AND
marker.elems = '' AND
dynpoi_status.source = marker.source AND
dynpoi_status.class = marker.class AND
dynpoi_status.subclass = marker.subclass AND
dynpoi_status.elems = marker.elems AND
dynpoi_status.lat = marker.lat AND
dynpoi_status.lon = marker.lon
""", (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
if istemp:
os.remove(fname)
#! /usr/bin/env python
#-*- coding: utf-8 -*-
import cgi, re, sys, os, psycopg2.extras
import utils
conn = utils.get_dbconn()
#psycopg2.extras.register_hstore(conn, globally=True, oid=1408668)
#psycopg2.extras.register_hstore(conn)
cur = conn.cursor("1")
cur_mod = conn.cursor()
sql = """
SELECT m.source, m.class, m.subclass, m.lat, m.lon, m.item,
m.subtitle_en, m.subtitle_fr, m.elems, m.data
FROM dynpoi_marker m
"""
cur.execute(sql)
num_err = 0
many_res = cur.fetchmany(1000)
while many_res:
print(num_err, "\r", end=' ')
sys.stdout.flush()
num_err += 1000
for res in many_res:
sql_marker = """
INSERT INTO marker (source,class,subclass,lat,lon,elems,item,subtitle)
#! /usr/bin/env python
#-*- coding: utf-8 -*-
import cgi, re, sys, os, psycopg2.extras
import utils
conn = utils.get_dbconn()
#psycopg2.extras.register_hstore(conn, globally=True, oid=1408668)
#psycopg2.extras.register_hstore(conn)
cur = conn.cursor("1")
cur_mod = conn.cursor()
sql = """
SELECT m.source, m.class, m.subclass, m.lat, m.lon, m.item,
m.subtitle_en, m.subtitle_fr, m.elems, m.data
FROM dynpoi_marker m
"""
cur.execute(sql)
num_err = 0
many_res = cur.fetchmany(1000)
while many_res:
print num_err, "\r",
sys.stdout.flush()
num_err += 1000
for res in many_res:
sql_marker = """
def update(source, o, logger=printlogger()):
source_id = int(source["id"])
## open connections
dbconn = utils.get_dbconn()
dbcurs = dbconn.cursor()
## xml parser
parser = make_parser()
parser.setContentHandler(update_parser(source_id, source, dbcurs, o))
dbcurs.execute(
"SELECT remote_url from dynpoi_update u join dynpoi_update_last l on u.source = l.source and u.timestamp = l.timestamp where l.source = %d"
% int(source["id"]))
url = [x[0] for x in dbcurs.fetchall()][0]
## download the file if needed
if url.startswith("http://"):
fname = tempfile.mktemp()
urllib.urlretrieve(url, fname)
#mysock = urllib.urlopen(source["url"])
#open(fname,'w').write(mysock.read())
istemp = True
else:
return
fname = url
istemp = False
## open the file
if url.endswith(".bz2"):
import bz2
f = bz2.BZ2File(fname)
elif url.endswith(".gz"):
import gzip
f = gzip.open(fname)
else:
f = open(fname)
o.write("<html>\n")
o.write("<head>\n")
o.write("<title>Statistiques pour %s</title>\n" % source["comment"])
o.write("<link rel='stylesheet' type='text/css' href='style.css' />\n")
o.write("</head>\n")
o.write("<body>\n")
o.write("<h1>Statistiques pour %s</h1>\n" % source["comment"])
## parse the file
parser.parse(f)
o.write("</body>\n")
o.write("</html>\n")
## commit and close
dbconn.commit()
dbconn.close()
## close and delete
f.close()
del f
if istemp:
os.remove(fname)
def update(source, url, logger=printlogger(), remote_ip=""):
source_id = int(source["id"])
## open connections
dbconn = utils.get_dbconn()
dbcurs = dbconn.cursor()
## xml parser
parser = make_parser()
parser.setContentHandler(update_parser(source_id, source, url, remote_ip, dbconn, dbcurs))
## download the file if needed
if url.startswith("http://"):
socket.setdefaulttimeout(180)
tmp_path = "/tmp/osmose/"
if not os.path.exists(tmp_path):
os.makedirs(tmp_path)
fname = tempfile.mktemp(dir=tmp_path, prefix="update")
urllib.urlretrieve(url, fname)
# mysock = urllib.urlopen(source["url"])
# open(fname,'w').write(mysock.read())
istemp = True
else:
fname = url
istemp = False
## open the file
if url.endswith(".bz2"):
import bz2
f = bz2.BZ2File(fname)
elif url.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,
"""SELECT * FROM marker
WHERE (source,class,subclass,elems) IN (SELECT source,class,subclass,elems FROM dynpoi_status WHERE source = %s AND elems != '')""",
(source_id,),
)
for res in dbcurs.fetchall():
execute_sql(
dbcurs,
"""UPDATE dynpoi_status SET subtitle = %s,
lat = %s, lon = %s
WHERE source = %s AND class = %s AND subclass = %s AND elems = %s""",
(res["subtitle"], res["lat"], res["lon"], res["source"], res["class"], res["subclass"], res["elems"]),
)
execute_sql(
dbcurs,
"""SELECT * FROM marker
WHERE (source,class,subclass,lat,lon) IN (SELECT source,class,subclass,lat,lon FROM dynpoi_status WHERE source = %s AND elems = '')""",
(source_id,),
)
for res in dbcurs.fetchall():
execute_sql(
dbcurs,
"""UPDATE dynpoi_status SET subtitle = %s
WHERE source = %s AND class = %s AND subclass = %s AND lat = %s AND lon = %s""",
(res["subtitle"], res["source"], res["class"], res["subclass"], res["lat"], res["lon"]),
)
## remove false positive no longer present
# execute_sql(dbcurs, """DELETE FROM dynpoi_status
# WHERE (source,class,subclass,elems) NOT IN (SELECT source,class,subclass,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
WHERE (source,class,subclass,elems) IN (SELECT source,class,subclass,elems FROM dynpoi_status WHERE source = %s AND elems != '')""",
(source_id,),
)
execute_sql(
dbcurs,
"""DELETE FROM marker
WHERE (source,class,subclass,lat,lon) IN (SELECT source,class,subclass,lat,lon FROM dynpoi_status WHERE source = %s AND elems = '')""",
(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
if istemp:
os.remove(fname)
def update(source, o, logger = printlogger()):
source_id = int(source["id"])
## open connections
dbconn = utils.get_dbconn()
dbcurs = dbconn.cursor()
## xml parser
parser = make_parser()
parser.setContentHandler(update_parser(source_id, source, dbcurs, o))
dbcurs.execute("SELECT remote_url from dynpoi_update u join dynpoi_update_last l on u.source = l.source and u.timestamp = l.timestamp where l.source = %d" % int(source["id"]))
url = [x[0] for x in dbcurs.fetchall()][0]
## download the file if needed
if url.startswith("http://"):
fname = tempfile.mktemp()
urllib.urlretrieve(url, fname)
#mysock = urllib.urlopen(source["url"])
#open(fname,'w').write(mysock.read())
istemp = True
else:
return
fname = url
istemp = False
## open the file
if url.endswith(".bz2"):
import bz2
f = bz2.BZ2File(fname)
elif url.endswith(".gz"):
import gzip
f = gzip.open(fname)
else:
f = open(fname)
o.write("<html>\n")
o.write("<head>\n")
o.write("<title>Statistiques pour %s</title>\n" % source["comment"])
o.write("<link rel='stylesheet' type='text/css' href='style.css' />\n")
o.write("</head>\n")
o.write("<body>\n")
o.write("<h1>Statistiques pour %s</h1>\n" % source["comment"])
## parse the file
parser.parse(f)
o.write("</body>\n")
o.write("</html>\n")
## commit and close
dbconn.commit()
dbconn.close()
## close and delete
f.close()
del f
if istemp:
os.remove(fname)
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,
lat = marker.lat,
lon = marker.lon
FROM
marker
WHERE
marker.source = %s AND
marker.elems != '' AND
dynpoi_status.source = marker.source AND
dynpoi_status.class = marker.class AND
dynpoi_status.subclass = marker.subclass AND
dynpoi_status.elems = marker.elems
""", (source_id, ))
execute_sql(
dbcurs, """
UPDATE
dynpoi_status
SET
subtitle = marker.subtitle
FROM
marker
WHERE
marker.source = %s AND
marker.elems = '' AND
dynpoi_status.source = marker.source AND
dynpoi_status.class = marker.class AND
dynpoi_status.subclass = marker.subclass AND
dynpoi_status.elems = marker.elems AND
dynpoi_status.lat = marker.lat AND
dynpoi_status.lon = marker.lon
""", (source_id, ))
## remove false positive no longer present
# execute_sql(dbcurs, """DELETE FROM dynpoi_status
# WHERE (source,class,subclass,elems) NOT IN (SELECT source,class,subclass,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
marker.elems != '' AND
dynpoi_status.source = marker.source AND
dynpoi_status.class = marker.class AND
dynpoi_status.subclass = marker.subclass AND
dynpoi_status.elems = marker.elems
""", (source_id, ))
execute_sql(
dbcurs, """
DELETE FROM
marker
USING
dynpoi_status
WHERE
marker.source = %s AND
marker.elems = '' AND
dynpoi_status.source = marker.source AND
dynpoi_status.class = marker.class AND
dynpoi_status.subclass = marker.subclass AND
dynpoi_status.elems = marker.elems AND
dynpoi_status.lat = marker.lat AND
dynpoi_status.lon = marker.lon
""", (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 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,
lat = marker.lat,
lon = marker.lon
FROM
marker
WHERE
marker.source = %s AND
marker.elems != '' AND
dynpoi_status.source = marker.source AND
dynpoi_status.class = marker.class AND
dynpoi_status.subclass = marker.subclass AND
dynpoi_status.elems = marker.elems
""", (source_id, ))
execute_sql(dbcurs, """
UPDATE
dynpoi_status
SET
subtitle = marker.subtitle
FROM
marker
WHERE
marker.source = %s AND
marker.elems = '' AND
dynpoi_status.source = marker.source AND
dynpoi_status.class = marker.class AND
dynpoi_status.subclass = marker.subclass AND
dynpoi_status.elems = marker.elems AND
dynpoi_status.lat = marker.lat AND
dynpoi_status.lon = marker.lon
""", (source_id, ))
## remove false positive no longer present
# execute_sql(dbcurs, """DELETE FROM dynpoi_status
# WHERE (source,class,subclass,elems) NOT IN (SELECT source,class,subclass,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
marker.elems != '' AND
dynpoi_status.source = marker.source AND
dynpoi_status.class = marker.class AND
dynpoi_status.subclass = marker.subclass AND
dynpoi_status.elems = marker.elems
""", (source_id, ))
execute_sql(dbcurs, """
DELETE FROM
marker
USING
dynpoi_status
WHERE
marker.source = %s AND
marker.elems = '' AND
dynpoi_status.source = marker.source AND
dynpoi_status.class = marker.class AND
dynpoi_status.subclass = marker.subclass AND
dynpoi_status.elems = marker.elems AND
dynpoi_status.lat = marker.lat AND
dynpoi_status.lon = marker.lon
""", (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