def test_protocol_create():
proto = Protocol(Session, MappedClass)
request = FakeRequest({})
request.body = '{"type": "FeatureCollection", "features": [{"type": "Feature", "properties": {"text": "foo"}, "geometry": {"type": "Point", "coordinates": [45, 5]}}, {"type": "Feature", "properties": {"text": "foo"}, "geometry": {"type": "Point", "coordinates": [45, 5]}}]}'
response = FakeResponse()
proto.create(request, response, execute=False)
assert response.status == 201
assert len(Session.new) == 2
for obj in Session.new:
assert obj["text"] == "foo"
assert obj.geometry.x == 45
assert obj.geometry.y == 5
Session.rollback()
class PointsController(BaseController):
readonly = False # if set to True, only GET is supported
def __init__(self):
self.protocol = Protocol(Session, Point, self.readonly)
def index(self, format='json'):
"""GET /: return all features."""
# If no filter argument is passed to the protocol index method
# then the default MapFish filter is used. This default filter
# is constructed based on the box, lon, lat, tolerance GET
# params.
#
# If you need your own filter with application-specific params
# taken into acount, create your own filter and pass it to the
# protocol index method.
#
# E.g.
#
# default_filter = create_default_filter(
# request, Point
# )
# compare_filter = comparison.Comparison(
# comparison.Comparison.ILIKE,
# Point.mycolumnname,
# value=myvalue
# )
# filter = logical.Logical(logical.Logical.AND, [default_filter, compare_filter])
# return self.protocol.index(request, response, format=format, filter=filter)
#
return self.protocol.index(request, response, format=format)
def show(self, id, format='json'):
"""GET /id: Show a specific feature."""
return self.protocol.show(request, response, id, format=format)
def create(self):
"""POST /: Create a new feature."""
return self.protocol.create(request, response)
def update(self, id):
"""PUT /id: Update an existing feature."""
return self.protocol.update(request, response, id)
def delete(self, id):
"""DELETE /id: Delete an existing feature."""
return self.protocol.delete(request, response, id)
def test_protocol_query():
proto = Protocol(Session, MappedClass)
request = FakeRequest({})
query = proto._query(request, execute=False)
assert "SELECT" in str(query)
request = FakeRequest({"queryable": "id", "id__eq": "1"})
query = proto._query(request, execute=False)
assert "WHERE" in str(query)
request = FakeRequest({"queryable": "id", "id__eq": "1"})
filter = create_attr_filter(request, MappedClass)
query = proto._query(FakeRequest({}), filter=filter, execute=False)
assert "WHERE" in str(query)
request = FakeRequest({"limit": "2"})
query = proto._query(request, execute=False)
assert "LIMIT 2" in str(query)
request = FakeRequest({"maxfeatures": "2"})
query = proto._query(request, execute=False)
assert "LIMIT 2" in str(query)
request = FakeRequest({"limit": "2", "offset": "10"})
query = proto._query(request, execute=False)
assert "OFFSET 10" in str(query)
request = FakeRequest({"order_by": "text"})
query = proto._query(request, execute=False)
assert "ORDER BY" in str(query)
assert "ASC" in str(query)
request = FakeRequest({"sort": "text"})
query = proto._query(request, execute=False)
assert "ORDER BY" in str(query)
assert "ASC" in str(query)
request = FakeRequest({"order_by": "text", "dir": "DESC"})
query = proto._query(request, execute=False)
assert "ORDER BY" in str(query)
assert "DESC" in str(query)
def __init__(self):
self.protocol = Protocol(Session,
Address,
self.readonly,
before_create=self.before_create)
class AddressesController(BaseController):
readonly = False # if set to True, only GET is supported
def __init__(self):
self.protocol = Protocol(Session,
Address,
self.readonly,
before_create=self.before_create)
def __before__(self):
rootUrl = config['root_path']
def index(self, format='json'):
"""GET /: return all features."""
# If no filter argument is passed to the protocol index method
# then the default MapFish filter is used. This default filter
# is constructed based on the box, lon, lat, tolerance GET
# params.
#
# If you need your own filter with application-specific params
# taken into acount, create your own filter and pass it to the
# protocol index method.
#
# E.g.
#
# default_filter = create_default_filter(
# request, Address
# )
# compare_filter = comparison.Comparison(
# comparison.Comparison.ILIKE,
# Address.mycolumnname,
# value=myvalue
# )
# filter = logical.Logical(logical.Logical.AND, [default_filter, compare_filter])
# return self.protocol.index(request, response, format=format, filter=filter)
#
#
# You can also create filters using sqlalchemy syntax.
# It is possible for example to mix a custom sqlalchemy filter
# with the default mapfish filter.
#
# E.g.
#
# from sqlalchemy.sql import and_
#
# default_filter = create_default_filter(
# request, Address
# )
# compare_filter = Address.mycolumnname.ilike('%myvalue%')
# if default_filter is not None:
# filter = and_(default_filter.to_sql_expr(), compare_filter)
# else:
# filter = compare_filter
# return self.protocol.index(request, response, format=format, filter=filter)
default_filter = create_default_filter(request, Address)
# Convert attribute KVP to filter
for column in Address.__table__.columns:
if column.name in request.params:
column_name = column.name
column_value = request.params[column.name]
# PGString, PGInteger are supported
# PGDateTime, Geometry, NullType are not supported
if str(column.type).find('PGInteger') > -1:
compareFilter = comparison.Comparison(
comparison.Comparison.EQUAL_TO,
Address.__table__.columns[column_name],
value=column_value)
if default_filter is not None:
default_filter = and_(default_filter.to_sql_expr(),
compareFilter)
else:
default_filter = compareFilter
if str(column.type).find('PGString') > -1:
compareFilter = comparison.Comparison(
comparison.Comparison.LIKE,
Address.__table__.columns[column_name],
value=column_value)
if default_filter is not None:
default_filter = and_(default_filter.to_sql_expr(),
compareFilter)
else:
default_filter = compareFilter
# Check query for full text search
if 'query' in request.params:
# http://lowmanio.co.uk/blog/entries/postgresql-full-text-search-and-sqlalchemy/
terms = request.params.get('query').split()
terms = ' & '.join(
[term + ('' if term.isdigit() else ':*') for term in terms])
if 'attrs' in request.params:
attributes = request.params.get('attrs').split(',')
if (len(attributes) == 3) and (
'street' in request.params.get('attrs')) and (
'city' in request.params.get('attrs')) and (
'housenumber' in request.params.get('attrs')):
tsvector = 'tsvector_street_housenumber_city'
elif (len(attributes)
== 1) and ('street' in request.params.get('attrs')):
tsvector = 'tsvector_street'
else:
attributes = " || ' ' ||".join(
[attribute for attribute in attributes])
tsvector = attributes
else:
tsvector = 'tsvector_street_housenumber_city'
ftsFilter = "%(tsvector)s @@ to_tsquery('english', '%(terms)s')" % {
'tsvector': tsvector,
'terms': terms
}
if default_filter is not None:
filter = and_(default_filter.to_sql_expr(), ftsFilter)
else:
filter = ftsFilter
if format == 'csv':
return self.exportCsv(request, filter)
if format == 'zip':
return self.exportZip(request, filter)
json = self.protocol.index(request,
response,
format=format,
filter=filter)
if 'callback' in request.params:
response.headers[
'Content-Type'] = 'text/javascript; charset=utf-8'
return request.params['callback'] + '(' + json + ');'
else:
response.headers['Content-Type'] = 'application/json'
return json
else:
if format == 'csv':
return self.exportCsv(request, default_filter)
if format == 'zip':
return self.exportZip(request, default_filter)
return self.protocol.index(request,
response,
format=format,
filter=default_filter)
def createCsvFile(self, request, filter):
io = StringIO.StringIO()
writer = csv.writer(io, delimiter=';')
objs = self.protocol._query(request, filter=filter)
def removeNone(value):
if len(value) == 4 and value == 'None':
return ''
else:
return value
for f in [
self.protocol._filter_attrs(o.toFeature(), request)
for o in objs if o.geometry
]:
row = map(lambda v: removeNone(str(v)), f.properties.values())
row.insert(0, str(f.id))
if (f.geometry is not None):
row.append(str(f.geometry.coordinates[0]))
row.append(str(f.geometry.coordinates[1]))
writer.writerow(row)
output = io.getvalue()
io.close()
return output
def exportCsv(self, request, filter):
response.content_type = 'text/csv; charset=utf-8'
response.content_disposition = 'attachment; filename="addresses.csv"'
return self.createCsvFile(request, filter)
def exportZip(self, request, filter):
imz = InMemoryZip()
imz.append("addresses.csv", self.createCsvFile(request, filter))
response.content_type = 'application/zip; charset=utf-8'
response.content_disposition = 'attachment; filename="addresses.zip"'
return imz.read()
def show(self, id, format='json'):
"""GET /id: Show a specific feature."""
if (id == 'count'):
return self.protocol.count(request)
elif (id == 'countCreatedToday'):
return self.countCreatedToday(request)
elif (id == 'countUpdatedToday'):
return self.countUpdatedToday(request)
elif (id == 'statistic'):
return self.statistic(request)
elif (id == 'weekstatistic'):
return self.weekstatistic(request)
elif (id == 'generalstatistics'):
return self.generalstatistics(request)
elif (id == 'checkSession'):
return self.checkSession()
elif (id == 'createSession'):
return self.createSession()
else:
return self.protocol.show(request, response, id, format=format)
def create(self):
"""POST /: Create a new feature."""
return self.protocol.create(request, response)
def update(self, id):
"""PUT /id: Update an existing feature."""
return self.protocol.update(request, response, id)
def delete(self, id):
"""DELETE /id: Delete an existing feature."""
return self.protocol.delete(request, response, id)
def before_create(self, request, feature):
feature.properties['ipaddress'] = request.environ['REMOTE_ADDR']
if isinstance(feature.id, int):
feature.properties['time_updated'] = datetime.now()
else:
feature.properties['time_updated'] = None
def checkSession(self):
return 'True'
def createSession(self):
return 'True'
def countCreatedToday(self, request):
# Create SQL Query
sqlQuery = "select count(1) from address where time_created::date=now()::date"
# Execute query
result = Session.execute(sqlQuery)
for row in result:
for column in row:
return str(column)
def countUpdatedToday(self, request):
# Create SQL Query
sqlQuery = "select count(*) from address where time_updated::date=now()::date"
# Execute query
result = Session.execute(sqlQuery)
for row in result:
for column in row:
return str(column)
def statistic(self, request):
if 'lang' in request.params:
c.lang = request.params.get('lang')
else:
c.lang = 'en'
c.charset = 'utf-8'
# Create SQL Query
sqlQuery = "select created_by, count(1) as numberAddresses " \
" from address where extract(week from time_created) = extract(week from now()) "\
" and extract(year from time_created) = extract (year from now()) "\
" group by created_by "\
" order by numberAddresses DESC "\
" LIMIT 20"
# Execute query
result = Session.execute(sqlQuery)
weekCreator = []
for row in result:
weekRow = []
for column in row:
weekRow.append(str(column))
weekCreator.append(weekRow)
c.weekCreator = weekCreator
c.count = locale.format("%s", self.protocol.count(request), True)
return render('/statistic.mako')
def weekstatistic(self, request):
if 'lang' in request.params:
c.lang = request.params.get('lang')
else:
c.lang = 'en'
c.charset = 'utf-8'
# Create SQL Query
sqlQuery = "select extract(year from time_created) || '/' || to_char(extract(week from time_created),'00'), count(1) as numberAddresses "\
" from address "\
" group by 1 "\
" order by 1 desc"
# Execute query
result = Session.execute(sqlQuery)
weekCreator = []
for row in result:
weekRow = []
for column in row:
weekRow.append(str(column))
weekCreator.append(weekRow)
c.weekCreator = weekCreator
c.count = locale.format("%s", self.protocol.count(request), True)
return render('/weekstatistic.mako')
def generalstatistics(self, request):
condition = ''
user = ''
if 'limit' in request.params:
limit = int(request.params.get('limit'))
else:
limit = 100
if 'orderby' in request.params:
orderby = request.params.get('orderby')
else:
orderby = 'address.time_updated, address.time_created desc'
if 'user' in request.params:
user = request.params.get('user')
condition = " AND created_by='%s'" % user
if 'street' in request.params:
street = request.params.get('street')
condition += " AND street='%s'" % street
if 'zip' in request.params:
zip = request.params.get('zip')
condition += " AND postcode='%s'" % zip
if 'city' in request.params:
city = request.params.get('city')
condition += " AND city='%s'" % city
if 'country' in request.params:
country = request.params.get('country')
condition += " AND country='%s'" % country
if 'date' in request.params:
date = request.params.get('date')
condition += " AND (to_char(time_created,'YYYYMMDD')='%s' OR to_char(time_updated,'YYYYMMDD')='%s')" % (
date, date)
if 'datesince' in request.params:
datesince = int(request.params.get('datesince'))
condition += " AND (to_number(to_char(time_created,'YYYYMMDD'),'99999999')>=%s OR to_number(to_char(time_updated,'YYYYMMDD'),'99999999')>=%s)" % (
datesince, datesince)
if 'qmname' in request.params:
tables2use = ", qm_regions"
qmname = request.params.get('qmname')
condition += " AND contains(qm_regions.geom,address.geom) and qm_regions.qmname='%s'" % qmname
else:
tables2use = ""
c.charset = 'utf-8'
# Create SQL Query
sqlQuery = "SELECT address.id, address.created_by,address.street, address.housenumber, address.housename, address.postcode, address.city, address.country,"\
" address.time_created, address.time_updated, ST_y(ST_Transform(address.geom,900913)) AS lat, ST_x(ST_Transform(address.geom,900913)) as lng "\
" FROM address %s"\
" WHERE address.quality='Digitized' %s "\
" ORDER BY %s "\
" limit %i " % (tables2use, condition, orderby, limit)
# Execute query
result = Session.execute(sqlQuery)
statCreator = []
for row in result:
statRow = []
for column in row:
if str(type(column)).find(
'float') == 7: #this means a float value
statRow.append(round(column, 4))
else:
statRow.append(str(column))
statCreator.append(statRow)
c.count = len(statCreator)
c.statCreator = statCreator
return render('/statreport.mako')
def __init__(self):
self.protocol = Protocol(Session, Point, self.readonly)
def __init__(self):
self.protocol = Protocol(Session, Tile, self.readonly)
class TilesController(BaseController):
readonly = False # if set to True, only GET is supported
def __init__(self):
self.protocol = Protocol(Session, Tile, self.readonly)
def index(self, format='json'):
"""GET /: return all features."""
# If no filter argument is passed to the protocol index method
# then the default MapFish filter is used. This default filter
# is constructed based on the box, lon, lat, tolerance GET
# params.
#
# If you need your own filter with application-specific params
# taken into acount, create your own filter and pass it to the
# protocol index method.
#
# E.g.
#
# default_filter = create_default_filter(
# request, Tile
# )
# compare_filter = comparison.Comparison(
# comparison.Comparison.ILIKE,
# Tile.mycolumnname,
# value=myvalue
# )
# filter = logical.Logical(logical.Logical.AND, [default_filter, compare_filter])
# return self.protocol.index(request, response, format=format, filter=filter)
#
#
# You can also create filters using sqlalchemy syntax.
# It is possible for example to mix a custom sqlalchemy filter
# with the default mapfish filter.
#
# E.g.
#
# from sqlalchemy.sql import and_
#
# default_filter = create_default_filter(
# request, Tile
# )
# compare_filter = Tile.mycolumnname.ilike('%myvalue%')
# if default_filter is not None:
# filter = and_(default_filter.to_sql_expr(), compare_filter)
# else:
# filter = compare_filter
# return self.protocol.index(request, response, format=format, filter=filter)
return self.protocol.index(request, response, format=format)
def show(self, id, format='json'):
"""GET /id: Show a specific feature."""
return self.protocol.show(request, response, id, format=format)
def create(self):
"""POST /: Create a new feature."""
return self.protocol.create(request, response)
def update(self, id):
"""PUT /id: Update an existing feature."""
return self.protocol.update(request, response, id)
def delete(self, id):
"""DELETE /id: Delete an existing feature."""
return self.protocol.delete(request, response, id)
def __init__(self):
self.protocol = Protocol(Session, Country, self.readonly)
def __init__(self):
self.protocol = Protocol(Session, self.readonly)
