def test_multiple_engines(self):
default = sa.create_engine(self.db1.url)
stats = sa.create_engine(self.db2.url)
sqlahelper.add_engine(default)
sqlahelper.add_engine(stats, "stats")
# Can we retrieve the engines?
self.assertIs(sqlahelper.get_engine(), default)
self.assertIs(sqlahelper.get_engine("default"), default)
self.assertIs(sqlahelper.get_engine("stats"), stats)
# Are the session binding and base binding set correctly?
self.assertIs(sqlahelper.get_session().bind, default)
self.assertIs(sqlahelper.get_base().metadata.bind, default)
def test_multiple_engines_without_default(self):
db1 = sa.create_engine(self.db1.url)
db2 = sa.create_engine(self.db2.url)
sqlahelper.add_engine(db1, "db1")
sqlahelper.add_engine(db2, "db2")
# Can we retrieve the engines?
self.assertIs(sqlahelper.get_engine("db1"), db1)
self.assertIs(sqlahelper.get_engine("db2"), db2)
# There should be no default engine
self.assertIsNone(sqlahelper.get_session().bind)
self.assertIsNone(sqlahelper.get_base().metadata.bind)
self.assertIsNone(sqlahelper.get_engine())
def test_multiple_engines(self):
default = sa.create_engine(self.db1.url)
stats = sa.create_engine(self.db2.url)
sqlahelper.add_engine(default)
sqlahelper.add_engine(stats, "stats")
# Can we retrieve the engines?
self.assertIs(sqlahelper.get_engine(), default)
self.assertIs(sqlahelper.get_engine("default"), default)
self.assertIs(sqlahelper.get_engine("stats"), stats)
# Are the session binding and base binding set correctly?
self.assertIs(sqlahelper.get_session().bind, default)
self.assertIs(sqlahelper.get_base().metadata.bind, default)
def test_multiple_engines_without_default(self):
db1 = sa.create_engine(self.db1.url)
db2 = sa.create_engine(self.db2.url)
sqlahelper.add_engine(db1, "db1")
sqlahelper.add_engine(db2, "db2")
# Can we retrieve the engines?
self.assertIs(sqlahelper.get_engine("db1"), db1)
self.assertIs(sqlahelper.get_engine("db2"), db2)
# There should be no default engine
self.assertIsNone(sqlahelper.get_session().bind)
self.assertIsNone(sqlahelper.get_base().metadata.bind)
self.assertIsNone(sqlahelper.get_engine())
def setUp(self): # noqa
import sqlahelper
import transaction
from c2cgeoportal.models import DBSession, Role, User, Functionality
from c2cgeoportal.lib.dbreflection import init
create_default_ogcserver()
role1 = Role(name=u"__test_role1")
user1 = User(username=u"__test_user1",
password=u"__test_user1",
role=role1)
role2 = Role(name=u"__test_role2")
user2 = User(username=u"__test_user2",
password=u"__test_user2",
role=role2)
functionality1 = Functionality(u"__test_s", u"db")
functionality2 = Functionality(u"__test_a", u"db1")
functionality3 = Functionality(u"__test_a", u"db2")
role2.functionalities = [
functionality1, functionality2, functionality3
]
DBSession.add_all([user1, user2, role1, role2])
transaction.commit()
engine = sqlahelper.get_engine()
init(engine)
def setUp(self): # noqa
import sqlahelper
import transaction
from c2cgeoportal.models import DBSession, Role, User, Interface, TreeItem
from c2cgeoportal.lib.dbreflection import init
for treeitem in DBSession.query(TreeItem).all():
DBSession.delete(treeitem)
self.metadata = None
self.layer_ids = []
self.role = Role(name=u"__test_role")
self.user = User(
username=u"__test_user",
password=u"__test_user",
role=self.role
)
self.main = Interface(name=u"main")
DBSession.add(self.user)
DBSession.add(self.role)
DBSession.add(self.main)
transaction.commit()
engine = sqlahelper.get_engine()
init(engine)
def setUp(self): # noqa
import sqlahelper
import transaction
from c2cgeoportal.models import DBSession, Role, User, Interface, TreeItem
from c2cgeoportal.lib.dbreflection import init
for treeitem in DBSession.query(TreeItem).all():
DBSession.delete(treeitem)
self.metadata = None
self.layer_ids = []
self.role = Role(name=u"__test_role")
self.user = User(username=u"__test_user",
password=u"__test_user",
role=self.role)
self.main = Interface(name=u"main")
DBSession.add(self.user)
DBSession.add(self.role)
DBSession.add(self.main)
transaction.commit()
engine = sqlahelper.get_engine()
init(engine)
def get_info_from_mymaps(self, rows, attributes_to_remove):
features = []
ids = []
for row in rows:
category_id = row['category_id']
map_id = row['map_id']
cur_id = str(map_id) + "--" + str(category_id)
if cur_id not in ids:
ids.append(cur_id)
engine = sqlahelper.get_engine("mymaps")
query = "select ST_AsGeoJSON(ST_Collect (geometry)) as geometry\
, sum(ST_Length(geometry)) as length FROM\
public.feature_with_map_with_colors where\
category_id = %(category_id)d and map_id = '%(map_id)s'"\
% {'category_id': category_id, 'map_id': map_id}
res = engine.execute(query)
for feature in res.fetchall():
geometry = geojson_loads(feature['geometry'])
attributes = dict(row)
attributes['length'] = round(feature['length'] / 1000, 2)
self.remove_attributes(attributes,
attributes_to_remove,
"geometry")
features.append(self.to_feature(geometry, attributes, ""))
return features
def setUp(self): # noqa
import sqlahelper
import transaction
from c2cgeoportal.models import DBSession, Role, User, Functionality
from c2cgeoportal.lib.dbreflection import init
role1 = Role(name=u"__test_role1")
user1 = User(
username=u"__test_user1",
password=u"__test_user1",
role=role1
)
role2 = Role(name=u"__test_role2")
user2 = User(
username=u"__test_user2",
password=u"__test_user2",
role=role2
)
functionality1 = Functionality(u"__test_s", u"db")
functionality2 = Functionality(u"__test_a", u"db1")
functionality3 = Functionality(u"__test_a", u"db2")
role2.functionalities = [functionality1, functionality2, functionality3]
DBSession.add_all([user1, user2, role1, role2])
transaction.commit()
engine = sqlahelper.get_engine()
init(engine)
def setUp(self):
import sqlahelper
import transaction
from c2cgeoportal.models import DBSession, Role, User, Functionality
from c2cgeoportal.lib.dbreflection import init
role1 = Role(name=u'__test_role1')
user1 = User(
username=u'__test_user1',
password=u'__test_user1',
role=role1
)
role2 = Role(name=u'__test_role2')
user2 = User(
username=u'__test_user2',
password=u'__test_user2',
role=role2
)
functionality1 = Functionality(u'__test_s', u'db')
functionality2 = Functionality(u'__test_a', u'db1')
functionality3 = Functionality(u'__test_a', u'db2')
user2.functionalities = [functionality1, functionality2, functionality3]
DBSession.add(user1)
DBSession.add(user2)
transaction.commit()
engine = sqlahelper.get_engine()
init(engine)
def setUp(self):
import transaction
import sqlahelper
from sqlalchemy import Column, types, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from c2cgeoportal.models import DBSession
from c2cgeoportal.lib.dbreflection import _association_proxy
engine = sqlahelper.get_engine()
Base = declarative_base(bind=engine)
class Child(Base):
__tablename__ = 'child'
id = Column(types.Integer, primary_key=True)
name = Column(types.Unicode)
def __init__(self, name):
self.name = name
class Parent(Base):
__tablename__ = 'parent'
id = Column(types.Integer, primary_key=True)
child1_id = Column(types.Integer, ForeignKey('child.id'))
child2_id = Column(types.Integer, ForeignKey('child.id'))
child1_ = relationship(Child, primaryjoin=(child1_id == Child.id))
child1 = _association_proxy('child1_', 'name')
child2_ = relationship(Child, primaryjoin=(child2_id == Child.id))
child2 = _association_proxy('child2_', 'name')
Base.metadata.create_all()
DBSession.add_all([Child('foo'), Child('bar')])
transaction.commit()
self.metadata = Base.metadata
self.cls = Parent
def initialized(ev):
PTAH = ptah.get_settings(ptah.CFG_ID_PTAH, ev.registry)
# mail
PTAH['Mailer'] = DummyMailer()
PTAH['full_email_address'] = formataddr(
(PTAH['email_from_name'], PTAH['email_from_address']))
# sqla
SQLA = ptah.get_settings(ptah.CFG_ID_SQLA, ev.registry)
url = SQLA['url']
if url:
engine_args = {}
if SQLA['cache']:
cache = {}
engine_args['execution_options'] = \
{'compiled_cache': cache}
SQLA['sqlalchemy_cache'] = cache
try:
engine = sqlahelper.get_engine()
except: # pragma: no cover
engine = sqlalchemy.engine_from_config(
{'sqlalchemy.url': url}, 'sqlalchemy.', **engine_args)
sqlahelper.add_engine(engine)
# ptah manage
if PTAH['manage']:
ev.config.add_route(
'ptah-manage', '/ptah-manage/*traverse',
factory=ptah.manage.PtahManageRoute, use_global_views=True)
ptah.manage.set_access_manager(
ptah.manage.PtahAccessManager())
def setUp(self):
import transaction
import sqlahelper
from sqlalchemy import Column, types, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from c2cgeoportal.models import DBSession
from c2cgeoportal.lib.dbreflection import _association_proxy
engine = sqlahelper.get_engine()
Base = declarative_base(bind=engine)
class Child(Base):
__tablename__ = 'child'
id = Column(types.Integer, primary_key=True)
name = Column(types.Unicode)
def __init__(self, name):
self.name = name
class Parent(Base):
__tablename__ = 'parent'
id = Column(types.Integer, primary_key=True)
child1_id = Column(types.Integer, ForeignKey('child.id'))
child2_id = Column(types.Integer, ForeignKey('child.id'))
child1_ = relationship(Child, primaryjoin=(child1_id==Child.id))
child1 = _association_proxy('child1_', 'name')
child2_ = relationship(Child, primaryjoin=(child2_id==Child.id))
child2 = _association_proxy('child2_', 'name')
Base.metadata.create_all()
DBSession.add_all([Child('foo'), Child('bar')])
transaction.commit()
self.metadata = Base.metadata
self.cls = Parent
def main(): # pragma: nocover
env = bootstrap("development.ini")
from geoportailv3.models import LuxGetfeatureDefinition
package = env["registry"].settings["package"]
directory = "%s/locale/" % package
destination = path.join(directory, "%s-tooltips.pot" % package)
w = codecs.open(destination, "wt", encoding="utf-8")
w.write(
u'''#, fuzzy
msgid ""
msgstr ""
"MIME-Version: 1.0\\n"
"Content-Type: text/plain; charset=utf-8\\n"
"Content-Transfer-Encoding: 8bit\\n"
'''
)
dbsession = sqlahelper.get_session()
results = dbsession.query(LuxGetfeatureDefinition).\
filter(LuxGetfeatureDefinition.remote_template == False).\
filter(LuxGetfeatureDefinition.template == 'default_gisgr.html').all() # noqa
fields = []
for result in results:
engine = None
first_row = None
if result.query is not None and len(result.query) > 0:
engine = sqlahelper.get_engine(result.engine)
first_row = engine.execute("SELECT * FROM " + result.query).first()
if result.rest_url is not None and len(result.rest_url) > 0:
first_row = _get_external_data(
result.rest_url,
'96958.90059551848,61965.61097091329,' +
'97454.77280739773,62463.21618929457', result.layer)
if first_row is not None:
attributes = dict(first_row)
attributes = remove_attributes(
attributes,
result.attributes_to_remove,
result.geometry_column)
for attribute in attributes:
if attribute not in fields:
fields.append(attribute)
w.write(
u'''#: engine:%(engine)s Layer:%(layer)s Role:%(role)s
msgid "f_%(name)s"
msgstr ""
''' % {
"engine": result.engine_gfi,
"layer": result.layer,
"role": result.role,
"name": attribute,
}
)
print("tooltips Pot file updated: %s" % destination)
def test_ptahinit_sqla(self):
self._settings = {'sqla.url': 'sqlite://'}
self.init_ptah()
import sqlahelper
engine = sqlahelper.get_engine()
self.assertIsNotNone(engine)
def setUp(self):
import sqlahelper
from c2cgeoportal.lib.dbreflection import init
self.metadata = None
engine = sqlahelper.get_engine()
init(engine)
def setUp(self):
try:
engine = sqlahelper.get_engine()
except: # pragma: no cover
engine = sqlalchemy.engine_from_config({"sqlalchemy.url": "sqlite://"})
sqlahelper.add_engine(engine)
self._setup_pyramid()
def setUp(self): # noqa
import sqlahelper
from c2cgeoportal.lib.dbreflection import init
self.metadata = None
engine = sqlahelper.get_engine()
init(engine)
def loginchange(self):
new_password = self.request.params.get('newPassword', None)
new_password_confirm = self.request.params.get('confirmNewPassword',
None)
if new_password is None or new_password_confirm is None:
raise HTTPBadRequest(
'"newPassword" and "confirmNewPassword" should be \
available in request params')
# check if loggedin
if not self.request.user:
raise HTTPUnauthorized('bad credentials')
if new_password != new_password_confirm:
raise HTTPBadRequest("the new password and the new password \
confirmation don't match")
u = self.request.user
u._set_password(new_password)
u.is_password_changed = True
DBSession.flush()
log.info("password changed for user: %s" % self.request.user.username)
# handle replication
if 'auth_replication_enabled' in self.request.registry.settings and \
self.request.registry.settings['auth_replication_enabled'] == \
'true': # pragma: no cover
try:
log.debug(
"trying to find if engine set for replication exists")
engine = sqlahelper.get_engine('replication')
except RuntimeError:
log.debug("engine for replication doesn't exist yet, trying \
to create")
engine = engine_from_config(self.request.registry.settings,
'sqlalchemy_replication.')
sqlahelper.add_engine(engine, 'replication')
DBSession2 = scoped_session(sessionmaker(bind=engine))
dbuser_r = DBSession2.query(User).filter(
User.id == self.request.user.id)
result = dbuser_r.all()
if len(result) == 0:
msg = 'user not found in replication target database: %s' \
% self.request.user.username
log.exception(msg)
return HTTPBadRequest(msg) # pragma nocover
else:
u_r = dbuser_r.all()[0]
u_r._set_password(new_password)
u_r.is_password_changed = True
DBSession2.commit()
log.info("password changed in replication target database \
for user: %s" % self.request.user.username)
return Response('true', cache_control="no-cache")
def setUp(self):
try:
engine = sqlahelper.get_engine()
except:
engine = sqlalchemy.engine_from_config(
{'sqlalchemy.url': 'sqlite://'})
sqlahelper.add_engine(engine)
self._setup_pyramid()
self._setup_ptah()
def _create_table(self, tablename):
""" Test functions use this function to create a table object.
Each test function should call this function only once. And
there should not be two test functions that call this function
with the same ptable_name value.
"""
import sqlahelper
from sqlalchemy import Table, Column, ForeignKey, types
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from geoalchemy2 import Geometry, func
engine = sqlahelper.get_engine()
Base = declarative_base(bind=engine) # noqa
session = sessionmaker(bind=engine)()
postgis_version = session.execute(func.postgis_version()).scalar()
management = postgis_version.startswith("1.")
if self._tables is None:
self._tables = []
ctable = Table("%s_child" % tablename,
Base.metadata,
Column("id", types.Integer, primary_key=True),
Column("name", types.Unicode),
schema="public")
ctable.create()
self._tables.append(ctable)
ptable = Table(tablename,
Base.metadata,
Column("id", types.Integer, primary_key=True),
Column("child1_id", types.Integer,
ForeignKey("public.%s_child.id" % tablename)),
Column("child2_id", types.Integer,
ForeignKey("public.%s_child.id" % tablename)),
Column("point", Geometry("POINT",
management=management)),
Column("linestring",
Geometry("LINESTRING", management=management)),
Column("polygon",
Geometry("POLYGON", management=management)),
Column("multipoint",
Geometry("MULTIPOINT", management=management)),
Column(
"multilinestring",
Geometry("MULTILINESTRING", management=management)),
Column("multipolygon",
Geometry("MULTIPOLYGON", management=management)),
schema="public")
ptable.create()
self._tables.append(ptable)
self.metadata = Base.metadata
def loginchange(self):
new_password = self.request.params.get('newPassword', None)
new_password_confirm = self.request.params.get('confirmNewPassword', None)
if new_password is None or new_password_confirm is None:
raise HTTPBadRequest('"newPassword" and "confirmNewPassword" should be \
available in request params')
# check if loggedin
if not self.request.user:
raise HTTPUnauthorized('bad credentials')
if new_password != new_password_confirm:
raise HTTPBadRequest("the new password and the new password \
confirmation don't match")
u = self.request.user
u._set_password(new_password)
u.is_password_changed = True
DBSession.flush()
log.info("password changed for user: %s" % self.request.user.username)
# handle replication
if 'auth_replication_enabled' in self.request.registry.settings and \
self.request.registry.settings['auth_replication_enabled'] == \
'true': # pragma: no cover
try:
log.debug("trying to find if engine set for replication exists")
engine = sqlahelper.get_engine('replication')
except RuntimeError:
log.debug("engine for replication doesn't exist yet, trying \
to create")
engine = engine_from_config(
self.request.registry.settings,
'sqlalchemy_replication.')
sqlahelper.add_engine(engine, 'replication')
DBSession2 = scoped_session(sessionmaker(bind=engine))
dbuser_r = DBSession2.query(User).filter(User.id == self.request.user.id)
result = dbuser_r.all()
if len(result) == 0:
msg = 'user not found in replication target database: %s' \
% self.request.user.username
log.exception(msg)
return HTTPBadRequest(msg) # pragma nocover
else:
u_r = dbuser_r.all()[0]
u_r._set_password(new_password)
u_r.is_password_changed = True
DBSession2.commit()
log.info("password changed in replication target database \
for user: %s" % self.request.user.username)
return Response('true', cache_control="no-cache")
def main(): # pragma: nocover
env = bootstrap("development.ini")
from geoportailv3.models import LuxGetfeatureDefinition
package = env["registry"].settings["package"]
directory = "%s/locale/" % package
destination = path.join(directory, "%s-tooltips.pot" % package)
w = codecs.open(destination, "wt", encoding="utf-8")
w.write(u'''#, fuzzy
msgid ""
msgstr ""
"MIME-Version: 1.0\\n"
"Content-Type: text/plain; charset=utf-8\\n"
"Content-Transfer-Encoding: 8bit\\n"
''')
dbsession = sqlahelper.get_session()
results = dbsession.query(LuxGetfeatureDefinition).\
filter(LuxGetfeatureDefinition.remote_template == False).\
filter(LuxGetfeatureDefinition.template == 'default.html').all() # noqa
fields = []
for result in results:
engine = sqlahelper.get_engine(result.engine)
first_row = None
if result.query is not None and len(result.query) > 0:
first_row = engine.execute("SELECT * FROM " + result.query).first()
if result.rest_url is not None and len(result.rest_url) > 0:
first_row = _get_external_data(
result.rest_url, '96958.90059551848,61965.61097091329,' +
'97454.77280739773,62463.21618929457', result.layer)
if first_row is not None:
attributes = dict(first_row)
attributes = remove_attributes(attributes,
result.attributes_to_remove,
result.geometry_column)
for attribute in attributes:
if attribute not in fields:
fields.append(attribute)
w.write(
u'''#: engine:%(engine)s Layer:%(layer)s Role:%(role)s
msgid "f_%(name)s"
msgstr ""
''' % {
"engine": result.engine,
"layer": result.layer,
"role": result.role,
"name": attribute,
})
print("tooltips Pot file updated: %s" % destination)
def _create_table(self, tablename):
""" Test functions use this function to create a table object.
Each test function should call this function only once. And
there should not be two test functions that call this function
with the same ptable_name value.
"""
import sqlahelper
from sqlalchemy import Table, Column, ForeignKey, types
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from geoalchemy2 import Geometry, func
engine = sqlahelper.get_engine()
Base = declarative_base(bind=engine) # noqa
session = sessionmaker(bind=engine)()
postgis_version = session.execute(func.postgis_version()).scalar()
management = postgis_version.startswith("1.")
if self._tables is None:
self._tables = []
ctable = Table(
"{0!s}_child".format(tablename), Base.metadata,
Column("id", types.Integer, primary_key=True),
Column("name", types.Unicode),
schema="public"
)
ctable.create()
self._tables.append(ctable)
ptable = Table(
tablename, Base.metadata,
Column("id", types.Integer, primary_key=True),
Column(
"child1_id", types.Integer,
ForeignKey("public.{0!s}_child.id".format(tablename))
),
Column(
"child2_id", types.Integer,
ForeignKey("public.{0!s}_child.id".format(tablename))
),
Column("point", Geometry("POINT", management=management)),
Column("linestring", Geometry("LINESTRING", management=management)),
Column("polygon", Geometry("POLYGON", management=management)),
Column("multipoint", Geometry("MULTIPOINT", management=management)),
Column("multilinestring", Geometry("MULTILINESTRING", management=management)),
Column("multipolygon", Geometry("MULTIPOLYGON", management=management)),
schema="public"
)
ptable.create()
self._tables.append(ptable)
self.metadata = Base.metadata
def sqla_initializing(ev):
url = SQLA.url
if url:
engine_args = {}
if SQLA.cache:
engine_args['execution_options'] = \
{'compiled_cache': SQL_compiled_cache}
try:
engine = sqlahelper.get_engine()
except:
engine = sqlalchemy.engine_from_config(
{'sqlalchemy.url': url}, 'sqlalchemy.', **engine_args)
sqlahelper.add_engine(engine)
def setUp(self):
if self._init_sqla:
try:
engine = sqlahelper.get_engine()
except: # pragma: no cover
engine = sqlalchemy.engine_from_config(
{'sqlalchemy.url': 'sqlite://'})
sqlahelper.add_engine(engine)
self.init_pyramid()
if self._init_ptah: # pragma: no cover
self.init_ptah()
def setUp(self):
if self._init_sqla:
try:
engine = sqlahelper.get_engine()
except: # pragma: no cover
engine = sqlalchemy.engine_from_config(
{'sqlalchemy.url': 'sqlite://'})
sqlahelper.add_engine(engine)
self.init_pyramid()
if self._init_ptah: # pragma: no cover
self.init_ptah()
def initializing(ev):
# auth
if not SECURITY.secret:
SECURITY.secret = uuid.uuid4().get_hex()
pname = SECURITY.policy
if pname not in ('', 'no-policy'):
policyFactory, attrs, kw = types[pname]
settings = []
for attr in attrs:
settings.append(SECURITY.get(attr))
kwargs = {'wild_domain': False}
for attr in kw:
kwargs[attr] = SECURITY.get(attr)
policy = policyFactory(*settings, **kwargs)
config.registry.registerUtility(policy, IAuthenticationPolicy)
if SECURITY.authorization:
config.registry.registerUtility(
ACLAuthorizationPolicy(), IAuthorizationPolicy)
# mail
smtp_mailer = SMTPMailer(
hostname = MAIL.host,
port = MAIL.port,
username = MAIL.username or None,
password = MAIL.password or None,
no_tls = MAIL.no_tls,
force_tls = MAIL.force_tls,
debug_smtp = MAIL.debug)
MAIL.Mailer = DirectMailDelivery(smtp_mailer)
MAIL.full_from_address = formataddr((MAIL.from_name, MAIL.from_address))
# sqla
url = SQLA.url
if url:
engine_args = {}
if SQLA.cache:
engine_args['execution_options'] = \
{'compiled_cache': SQL_compiled_cache}
try:
engine = sqlahelper.get_engine()
except:
engine = sqlalchemy.engine_from_config(
{'sqlalchemy.url': url}, 'sqlalchemy.', **engine_args)
sqlahelper.add_engine(engine)
def db_chooser_tween(request):
session = DBSession()
old = session.bind
method_path = "{0!s} {1!s}".format(request.method, request.path)
force_master = any(r.match(method_path) for r in master_paths)
if not force_master and (request.method in ("GET", "OPTIONS") or
any(r.match(method_path) for r in slave_paths)):
log.debug("Using slave database for: " + method_path)
session.bind = sqlahelper.get_engine("slave")
else:
log.debug("Using master database for: " + method_path)
try:
return handler(request)
finally:
session.bind = old
def db_chooser_tween(request):
session = request.session
old = session.bind
method_path = "{0!s} {1!s}".format(request.method, request.path)
force_master = any(r.match(method_path) for r in master_paths)
if not force_master and (request.method in ("GET", "OPTIONS") or any(
r.match(method_path) for r in slave_paths)):
log.debug("Using slave database for: " + method_path)
session.bind = sqlahelper.get_engine("slave")
else:
log.debug("Using master database for: " + method_path)
try:
return handler(request)
finally:
session.bind = old
def get_poi_template(self):
layer = self.request.params.get('layer', None)
if layer is None:
return HTTPBadRequest()
self.dbsession = sqlahelper.get_session()
luxgetfeaturedefinitions = self.get_lux_feature_definition(layer)
if len(luxgetfeaturedefinitions) is not 1:
return HTTPBadRequest()
if luxgetfeaturedefinitions[0].poi_id_collection is None:
return HTTPBadRequest()
engine = sqlahelper.get_engine(luxgetfeaturedefinitions[0].engine)
poi_fields = [
"id", "id_collection", "line_num", "easting", "northing", "zip",
"town", "street", "poi_name", "description", "type", "url",
"active", "core_data", "master_id", "data_type", "synchro_date",
"creation_date", "num", "matching_address", "accuracy", "ratio",
"master_collection_id", "file_id", "x", "y", "user_field1",
"user_field2", "user_field3", "user_field4", "user_field5",
"phone", "mail", "id_poi", "country", "category"
]
query = "SELECT mapv3_html FROM public.tooltips WHERE \
id_collection=%d AND is_default=true"\
% (luxgetfeaturedefinitions[0].poi_id_collection)
res = engine.execute(query)
for row in res.fetchall():
content = row['mapv3_html'].replace("$%7B", "${").\
replace("%7D", "}")
for field in poi_fields:
content = content.replace(
"${%(field)s}" % ({
'field': field
}), "{{feature['attributes']\
['%(field)s']}}" % ({
'field': field
}))
response = "<h1>{{layers['layerLabel'] | translate}}</h1>\
<div class=\"poi-feature\"\
ng-repeat=\"feature in layers['features']\">%s\
</div>" % (content)
return Response(response)
return HTTPBadRequest()
def setUp(self):
import sqlahelper
import transaction
from c2cgeoportal.models import DBSession, Role, User
from c2cgeoportal.lib.dbreflection import init
self.metadata = None
self.layer_ids = []
self.role = Role(name=u'__test_role')
self.user = User(username=u'__test_user',
password=u'__test_user',
role=self.role)
DBSession.add(self.user)
transaction.commit()
engine = sqlahelper.get_engine()
init(engine)
def get_info_from_mymaps(self, layer_id, rows, attributes_to_remove):
features = []
ids = []
for row in rows:
category_id = row['category_id']
if 'id' in row:
fid = row['id']
else:
fid = None
map_id = row['map_id']
cur_id = str(map_id) + "--" + str(category_id)
if cur_id not in ids:
ids.append(cur_id)
geometry = geojson_loads(row['st_asgeojson'])
if geometry['type'] == "LineString" or\
geometry['type'] == "MultiLineString":
engine = sqlahelper.get_engine("mymaps")
query = "select ST_AsGeoJSON(ST_Collect (geometry)) as geometry\
, sum(ST_Length(geometry)) as length FROM\
public.feature_with_map_with_colors where\
category_id = %(category_id)d and map_id = '%(map_id)s'"\
% {'category_id': category_id, 'map_id': map_id}
res = engine.execute(query)
for feature in res.fetchall():
geometry = geojson_loads(feature['geometry'])
attributes = dict(row)
attributes['length'] = round(feature['length'] / 1000,
2)
self.remove_attributes(attributes,
attributes_to_remove,
"geometry")
features.append(
self.to_feature(layer_id, fid, geometry,
attributes, ""))
else:
attributes = dict(row)
self.remove_attributes(attributes, attributes_to_remove,
"geometry")
features.append(
self.to_feature(layer_id, fid, geometry, attributes,
""))
return features
def _create_table(self, tablename):
""" Test functions use this function to create a table object.
Each test function should call this function only once. And
there should not be two test functions that call this function
with the same ptable_name value.
"""
import sqlahelper
from sqlalchemy import Table, Column, ForeignKey, types
from sqlalchemy.ext.declarative import declarative_base
from geoalchemy import GeometryExtensionColumn, GeometryDDL
from geoalchemy import (Point, LineString, Polygon, MultiPoint,
MultiLineString, MultiPolygon)
engine = sqlahelper.get_engine()
Base = declarative_base(bind=engine)
ctable = Table('%s_child' % tablename,
Base.metadata,
Column('id', types.Integer, primary_key=True),
Column('name', types.Unicode),
schema='public')
ctable.create()
ptable = Table(tablename,
Base.metadata,
Column('id', types.Integer, primary_key=True),
Column('child1_id', types.Integer,
ForeignKey('public.%s_child.id' % tablename)),
Column('child2_id', types.Integer,
ForeignKey('public.%s_child.id' % tablename)),
GeometryExtensionColumn('point', Point),
GeometryExtensionColumn('linestring', LineString),
GeometryExtensionColumn('polygon', Polygon),
GeometryExtensionColumn('multipoint', MultiPoint),
GeometryExtensionColumn('multilinestring',
MultiLineString),
GeometryExtensionColumn('multipolygon', MultiPolygon),
schema='public')
GeometryDDL(ptable)
ptable.create()
self.metadata = Base.metadata
def setUp(self):
import sqlahelper
import transaction
from c2cgeoportal.models import DBSession, Role, User
from c2cgeoportal.lib.dbreflection import init
self.metadata = None
self.layer_ids = []
self.role = Role(name=u'__test_role')
self.user = User(username=u'__test_user',
password=u'__test_user',
role=self.role
)
DBSession.add(self.user)
transaction.commit()
engine = sqlahelper.get_engine()
init(engine)
def _create_table(self, tablename):
""" Test functions use this function to create a table object.
Each test function should call this function only once. And
there should not be two test functions that call this function
with the same ptable_name value.
"""
import sqlahelper
from sqlalchemy import Table, Column, ForeignKey, types
from sqlalchemy.ext.declarative import declarative_base
from geoalchemy import GeometryExtensionColumn, GeometryDDL
from geoalchemy import (Point, LineString, Polygon,
MultiPoint, MultiLineString, MultiPolygon)
engine = sqlahelper.get_engine()
Base = declarative_base(bind=engine)
ctable = Table('%s_child' % tablename, Base.metadata,
Column('id', types.Integer, primary_key=True),
Column('name', types.Unicode),
schema='public'
)
ctable.create()
ptable = Table(tablename, Base.metadata,
Column('id', types.Integer, primary_key=True),
Column('child1_id', types.Integer,
ForeignKey('public.%s_child.id' % tablename)),
Column('child2_id', types.Integer,
ForeignKey('public.%s_child.id' % tablename)),
GeometryExtensionColumn('point', Point),
GeometryExtensionColumn('linestring', LineString),
GeometryExtensionColumn('polygon', Polygon),
GeometryExtensionColumn('multipoint', MultiPoint),
GeometryExtensionColumn('multilinestring', MultiLineString),
GeometryExtensionColumn('multipolygon', MultiPolygon),
schema='public'
)
GeometryDDL(ptable)
ptable.create()
self.metadata = Base.metadata
def get_poi_template(self):
layer = self.request.params.get('layer', None)
if layer is None:
return HTTPBadRequest()
self.dbsession = sqlahelper.get_session()
luxgetfeaturedefinitions = self.get_lux_feature_definition(layer)
if len(luxgetfeaturedefinitions) is not 1:
return HTTPBadRequest()
if luxgetfeaturedefinitions[0].poi_id_collection is None:
return HTTPBadRequest()
engine = sqlahelper.get_engine(luxgetfeaturedefinitions[0].engine)
poi_fields = ["id", "id_collection", "line_num", "easting", "northing",
"zip", "town", "street", "poi_name", "description",
"type", "url", "active", "core_data", "master_id",
"data_type", "synchro_date", "creation_date", "num",
"matching_address", "accuracy", "ratio",
"master_collection_id", "file_id", "x", "y",
"user_field1", "user_field2", "user_field3",
"user_field4", "user_field5", "phone", "mail",
"id_poi", "country", "category"]
query = "SELECT mapv3_html FROM public.tooltips WHERE \
id_collection=%d AND is_default=true"\
% (luxgetfeaturedefinitions[0].poi_id_collection)
res = engine.execute(query)
for row in res.fetchall():
content = row['mapv3_html'].replace("$%7B", "${").\
replace("%7D", "}")
for field in poi_fields:
content = content.replace("${%(field)s}" % ({'field': field}),
"{{feature['attributes']\
['%(field)s']}}"
% ({'field': field}))
response = "<h1>{{layers['layerLabel'] | translate}}</h1>\
<div class=\"poi-feature\"\
ng-repeat=\"feature in layers['features']\">%s\
</div>" % (content)
return Response(response)
return HTTPBadRequest()
def setUp(self): # noqa
import transaction
import sqlahelper
from sqlalchemy import Column, types, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from c2cgeoportal.models import DBSession
from c2cgeoportal.lib.dbreflection import _AssociationProxy
engine = sqlahelper.get_engine()
Base = declarative_base(bind=engine) # noqa
class Child(Base):
__tablename__ = "child"
id = Column(types.Integer, primary_key=True)
name = Column(types.Unicode)
def __init__(self, name):
self.name = name
class Parent(Base):
__tablename__ = "parent"
id = Column(types.Integer, primary_key=True)
child1_id = Column(types.Integer, ForeignKey("child.id"))
child2_id = Column(types.Integer, ForeignKey("child.id"))
child1_ = relationship(Child, primaryjoin=(child1_id == Child.id))
child1 = _AssociationProxy("child1_", "name")
child2_ = relationship(Child, primaryjoin=(child2_id == Child.id))
child2 = _AssociationProxy("child2_", "name")
Child.__table__.create()
Parent.__table__.create()
self._tables = [Parent.__table__, Child.__table__]
DBSession.add_all([Child("foo"), Child("bar")])
transaction.commit()
self.metadata = Base.metadata
self.cls = Parent
def setUp(self): # noqa
import transaction
import sqlahelper
from sqlalchemy import Column, types, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from c2cgeoportal.models import DBSession
from c2cgeoportal.lib.dbreflection import _AssociationProxy
engine = sqlahelper.get_engine()
Base = declarative_base(bind=engine) # noqa
class Child(Base):
__tablename__ = "child"
id = Column(types.Integer, primary_key=True)
name = Column(types.Unicode)
def __init__(self, name):
self.name = name
class Parent(Base):
__tablename__ = "parent"
id = Column(types.Integer, primary_key=True)
child1_id = Column(types.Integer, ForeignKey("child.id"))
child2_id = Column(types.Integer, ForeignKey("child.id"))
child1_ = relationship(Child, primaryjoin=(child1_id == Child.id))
child1 = _AssociationProxy("child1_", "name")
child2_ = relationship(Child, primaryjoin=(child2_id == Child.id))
child2 = _AssociationProxy("child2_", "name")
Child.__table__.create()
Parent.__table__.create()
self._tables = [Parent.__table__, Child.__table__]
DBSession.add_all([Child("foo"), Child("bar")])
transaction.commit()
self.metadata = Base.metadata
self.cls = Parent
def main(global_config, **settings):
""" This function returns a Pyramid WSGI application.
"""
config = Configurator(
root_factory=Root, settings=settings,
locale_negotiator=locale_negotiator,
authentication_policy=create_authentication(settings)
)
# config.add_subscriber(add_cors_headers_response_callback, NewRequest)
# overwrite print routes
config.add_route(
"lux_printproxy_report_create",
"/printproxy/report.{format}",
request_method="POST"
)
config.add_route(
"lux_printproxy_status",
"/printproxy/status/{ref}.json",
request_method="GET"
)
config.add_route(
"lux_printproxy_report_get",
"/printproxy/report/{ref}",
request_method="GET"
)
config.add_route(
"lux_printproxy_report_cancel",
"/printproxy/cancel/{ref}",
request_method="DELETE"
)
# mymaps routes
config.add_route(
"mymaps",
"/mymaps",
request_method="HEAD"
)
config.add_route(
"mymaps_image",
"/",
request_method="HEAD"
)
config.add_route(
"mymaps_getcategories",
"mymaps/categories",
request_method="GET"
)
config.add_route(
"mymaps_getallcategories",
"mymaps/allcategories",
request_method="GET"
)
config.add_route(
"mymaps_getpublicmaps",
"/mymaps/public_maps",
request_method="GET"
)
config.add_route(
"mymaps_getpublicategories",
"/mymaps/public_categories",
request_method="GET"
)
config.add_route(
"mymaps_getmaps",
"/mymaps/maps",
request_method="GET"
)
config.add_route(
"mymaps_features",
"/mymaps/features/{map_id}",
request_method="GET"
)
config.add_route(
"mymaps_map_info",
"/mymaps/map_info/{map_id}",
request_method="GET"
)
config.add_route(
"predefined_wms",
"/predefined_wms",
request_method="GET"
)
config.add_route(
"mymaps_create",
"/mymaps/create",
request_method="POST"
)
config.add_route(
"mymaps_rate",
"/mymaps/rate/{map_id}"
)
config.add_route(
"mymaps_update",
"/mymaps/update/{map_id}",
request_method="PUT"
)
config.add_route(
"mymaps_map",
"/mymaps/map/{map_id}",
request_method="GET"
)
config.add_route(
"mymaps_comment",
"/mymaps/comment/{map_id}",
request_method="POST"
)
config.add_route(
"mymaps_upload_image",
"/mymaps/upload_image",
request_method="POST"
)
config.add_route(
"mymaps_upload_symbol",
"/mymaps/upload_symbol",
request_method="POST"
)
config.add_route(
"mymaps_get_image",
"/mymaps/images/{filename}",
request_method="GET"
)
config.add_route(
"mymaps_get_symbol",
"/mymaps/symbol/{symbol_id}",
request_method="GET"
)
config.add_route(
"mymaps_users_categories",
"/mymaps/get_users_categories",
request_method="GET"
)
config.add_route(
"mymaps_get_symbols",
"/mymaps/symbols",
request_method="GET"
)
config.add_route(
"mymaps_delete",
"/mymaps/delete/{map_id}",
request_method="DELETE"
)
config.add_route(
"mymaps_delete_all_features",
"/mymaps/delete_all_features/{map_id}",
request_method="DELETE"
)
config.add_route(
"mymaps_delete_feature",
"/mymaps/delete_feature/{feature_id}",
request_method="DELETE"
)
config.add_route(
"mymaps_save_feature",
"/mymaps/save_feature/{map_id}",
request_method="POST"
)
config.add_route(
"mymaps_save_features",
"/mymaps/save_features/{map_id}",
request_method="POST"
)
config.add_route(
"mymaps_save_order",
"/mymaps/save_order/{map_id}",
request_method="POST"
)
config.add_route(
"mymaps_copy",
"/mymaps/copy/{map_id}",
request_method="POST"
)
config.add_route(
"exportgpxkml",
"/mymaps/exportgpxkml",
request_method="POST"
)
config.add_route(
"get_arrow_color",
"/mymaps/getarrow"
)
config.add_route(
"getroute",
"/mymaps/getroute"
)
config.add_route(
"getremoteroute",
"/router/getroute"
)
# geocoder routes
config.add_route(
"reverse_geocode",
"/geocode/reverse"
)
config.add_route(
"geocode",
"/geocode/search"
)
config.add_route(
"feedback",
"/feedback"
)
# pag routes
config.add_route(
"pag_url",
"/pag"
)
# pag routes
config.add_route(
"pag_report",
"/pag/report/{oid}.pdf"
)
# pag routes
config.add_route(
"casipo_url",
"/casipo"
)
# pag routes
config.add_route(
"casipo_report",
"/casipo/report/{oid}.pdf"
)
# pds routes
config.add_route(
"pds_url",
"/pds"
)
# pag routes
config.add_route(
"pds_report",
"/pds/report/{oid}.pdf"
)
config.add_route(
"pag_files",
"/pag/files/{_file}"
)
config.add_route(
"get_png",
"/legends/get"
)
config.add_route(
"get_html",
"/legends/get_html"
)
# full text search routes
config.add_route("fulltextsearch", "/fulltextsearch")
# layer search routes
config.add_route("layersearch", "/layersearch")
# cms search routes
config.add_route("cmssearch", "/cmssearch")
# jsapi routes
config.add_route(
'jsapiloader',
'/apiv3loader.js'
)
config.add_route(
'jsapiexample',
'/api-example'
)
config.add_route(
'jsapilayers',
'/jsapilayers'
)
config.include('c2cgeoportal')
config.include('pyramid_closure')
add_mako_renderer(config, '.appcache')
config.add_translation_dirs('geoportailv3:locale/')
# initialize database
engines = config.get_settings()['sqlalchemy_engines']
if engines:
config.registry.dbmakers = {}
for engine in engines:
if 'url' not in engines[engine]:
sqlahelper.add_engine(
sqlalchemy.create_engine(engines[engine]), name=engine)
else:
sqlahelper.add_engine(
sqlalchemy.create_engine(engines[engine]['url']),
name=engine)
config.registry.dbmakers[engine] = sessionmaker(
bind=sqlahelper.get_engine(engine))
config.add_request_method(init_db(engine),
'db_'+engine, reify=True)
from geoportailv3.views.authentication import ldap_user_validator, \
get_user_from_request
ldap_settings = config.get_settings()['ldap']
if ldap_settings:
config.include('pyramid_ldap')
"""Config the ldap connection.
"""
config.ldap_setup(
ldap_settings['url'],
ldap_settings['bind'],
ldap_settings['passwd'],
)
config.ldap_set_login_query(
ldap_settings['base_dn'],
filter_tmpl='(login=%(login)s)',
scope=ldap.SCOPE_SUBTREE,
)
config.set_request_property(
get_user_from_request,
name='user',
reify=True
)
set_user_validator(config, ldap_user_validator)
json_renderer = JSON()
json_renderer.add_adapter(datetime.date, datetime_adapter)
json_renderer.add_adapter(datetime.datetime, datetime_adapter)
json_renderer.add_adapter(Decimal, decimal_adapter)
config.add_renderer('json', json_renderer)
mail_config = config.get_settings()['turbomail']
if mail_config:
interface.start(mail_config)
# scan view decorator for adding routes
config.scan()
# add the interfaces
add_interface(config, interface_type=INTERFACE_TYPE_NGEO_CATALOGUE)
config.add_route("echocsv", "/profile/echocsv", request_method="POST")
config.add_route('getuserinfo', '/getuserinfo')
config.add_route('wms', '/ogcproxywms')
config.add_route('https_proxy', '/httpsproxy')
config.add_route('download_sketch', '/downloadsketch')
config.add_route('download', '/download')
config.add_route('download_measurement', '/downloadmeasurement')
config.add_route('preview_measurement', '/previewmeasurement')
config.add_route('qr', '/qr')
config.add_route('getfeatureinfo', '/getfeatureinfo')
config.add_route('getpoitemplate', '/getpoitemplate')
config.add_route('getremotetemplate', '/getremotetemplate')
config.add_route('isthemeprivate', '/isthemeprivate')
config.add_route('download_resource', '/downloadresource')
# Appcache manifest
config.add_route(
'appcache',
'/geoportailv3.appcache'
)
return config.make_wsgi_app()
import sqlalchemy as sa
import sqlahelper
from sqlalchemy import orm
from sqlalchemy.dialects.postgresql import ARRAY, REAL
from sqlalchemy.ext.declarative import declarative_base
import geoalchemy2
SCHEMA = 'coastdat'
Base = declarative_base()
Base.metadata.bind = sqlahelper.get_engine('reiners_db')
class Timeseries(Base):
__tablename__ = 'timeseries'
__table_args__ = {'schema': SCHEMA}
id = sa.Column(sa.BIGINT, primary_key=True)
tsarray = sa.Column(ARRAY(REAL))
class Year(Base):
__tablename__ = 'year'
__table_args__ = {'schema': SCHEMA}
year = sa.Column(sa.SMALLINT, primary_key=True)
leap = sa.Column(sa.BOOLEAN)
timeseries = orm.relationship('Timeseries',
secondary=f'{SCHEMA}.scheduled',
backref='year')
def main(): # pragma: nocover
env = bootstrap("development.ini")
from geoportailv3.models import LuxGetfeatureDefinition
package = env["registry"].settings["package"]
directory = "%s/locale/" % package
destination = path.join(directory, "%s-tooltips.pot" % package)
if not os.path.isfile(destination):
po = polib.POFile()
po.metadata = {
'MIME-Version': '1.0',
'Content-Type': 'text/plain; charset=utf-8',
'Content-Transfer-Encoding': '8bit',
}
else:
po = polib.pofile(destination, encoding="utf-8")
dbsession = sqlahelper.get_session()
results = dbsession.query(LuxGetfeatureDefinition).\
filter(LuxGetfeatureDefinition.remote_template == False).filter(
LuxGetfeatureDefinition.template.in_
(['default.html', 'default_table.html', 'feedbackanf.html'])).all() # noqa
fields = []
for result in results:
engine = sqlahelper.get_engine(result.engine_gfi)
first_row = None
if result.query is not None and len(result.query) > 0:
if "SELECT" in result.query.upper():
first_row = engine.execute(result.query).first()
else:
first_row =\
engine.execute("SELECT * FROM " + result.query).first()
if result.rest_url is not None and len(result.rest_url) > 0:
first_row = _get_external_data(
result.rest_url,
'96958.90059551848,61965.61097091329,' +
'97454.77280739773,62463.21618929457', result.layer)
attributes = None
if first_row is not None:
attributes = dict(first_row)
attributes = remove_attributes(
attributes,
result.attributes_to_remove,
result.geometry_column)
if first_row is None and result.columns_order is not None and\
len(result.columns_order) > 0:
attributes = result.columns_order.split(",")
if attributes is not None:
for attribute in attributes:
if attribute not in fields:
fields.append(attribute)
if not is_in_po(po, u'f_%(name)s' % {'name': attribute}):
entry = polib.POEntry(
msgid=u'f_%(name)s' % {'name': attribute},
msgstr=u'',
comment=("engine:%(engine)s Layer:%(layer)s *"
"Role:%(role)s" % {
"engine": result.engine_gfi,
"layer": result.layer,
"role": result.role,
})
)
po.append(entry)
po.save(destination)
print("tooltips Pot file updated: %s" % destination)
# -*- coding: utf-8 -*-
import datetime
import sqlahelper
from c2cgeoportal.models import * # noqa
from formalchemy import Column
from sqlalchemy.types import Integer, String, DateTime
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine = sqlahelper.get_engine()
Base = declarative_base(bind=engine)
Session = sessionmaker(bind=engine)
PortailSession = Session()
class Connections(Base):
__table_args__ = ({'schema': 'geov3_stats', 'autoload': False})
__tablename__ = 'connections'
id = Column(Integer, primary_key=True)
ip = Column(String)
action = Column(String)
login = Column(String)
application = Column(String)
class MesurageDownload(Base):
__table_args__ = ({'schema': 'geov3_stats'})
__tablename__ = 'measurement_download'
id = Column(Integer, primary_key=True)
login = Column(String)
def _create_layer(self,
public=False,
none_area=False,
attr_list=False,
exclude_properties=False,
metadatas=None):
""" This function is central for this test class. It creates
a layer with two features, and associates a restriction area
to it. """
import transaction
import sqlahelper
from sqlalchemy import Column, Table, types, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from geoalchemy2 import Geometry, WKTElement
from c2cgeoportal.models import DBSession, management, LayerV1, RestrictionArea
if self._tables is None:
self._tables = []
self.__class__._table_index += 1
id = self.__class__._table_index
engine = sqlahelper.get_engine()
connection = engine.connect()
if not self.metadata:
self.metadata = declarative_base(bind=engine).metadata
tablename = "table_%d" % id
table1 = Table("%s_child" % tablename,
self.metadata,
Column("id", types.Integer, primary_key=True),
Column("name", types.Unicode),
schema="public")
self._tables.append(table1)
table2 = Table(tablename,
self.metadata,
Column("id", types.Integer, primary_key=True),
Column("child_id", types.Integer,
ForeignKey("public.%s_child.id" % tablename)),
Column("name", types.Unicode),
Column(
"geom",
Geometry("POINT", srid=21781,
management=management)),
schema="public")
self._tables.append(table2)
table2.drop(checkfirst=True)
table1.drop(checkfirst=True)
table1.create()
table2.create()
ins = table1.insert().values(name=u"c1é")
c1_id = connection.execute(ins).inserted_primary_key[0]
ins = table1.insert().values(name=u"c2é")
c2_id = connection.execute(ins).inserted_primary_key[0]
ins = table2.insert().values(child_id=c1_id,
name="foo",
geom=WKTElement("POINT(5 45)", 21781))
connection.execute(ins)
ins = table2.insert().values(child_id=c2_id,
name="bar",
geom=WKTElement("POINT(6 46)", 21781))
connection.execute(ins)
if attr_list:
ins = table2.insert().values(child_id=c2_id,
name="aaa,bbb,foo",
geom=WKTElement("POINT(6 46)", 21781))
connection.execute(ins)
layer = LayerV1()
layer.id = id
layer.name = str(id)
layer.geo_table = tablename
layer.public = public
layer.interface = [self.main]
if exclude_properties:
layer.exclude_properties = "name"
if metadatas:
layer.metadatas = metadatas
DBSession.add(layer)
if not public:
ra = RestrictionArea()
ra.name = u"__test_ra"
ra.layers = [layer]
ra.roles = [self.role]
ra.readwrite = True
if not none_area:
poly = "POLYGON((4 44, 4 46, 6 46, 6 44, 4 44))"
ra.area = WKTElement(poly, srid=21781)
DBSession.add(ra)
transaction.commit()
self.layer_ids.append(id)
return id
def _create_layer(
self, public=False, none_area=False, attr_list=False,
exclude_properties=False, metadatas=None):
""" This function is central for this test class. It creates
a layer with two features, and associates a restriction area
to it. """
import transaction
import sqlahelper
from sqlalchemy import Column, Table, types, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from geoalchemy2 import Geometry, WKTElement
from c2cgeoportal.models import DBSession, management, LayerV1, RestrictionArea
if self._tables is None:
self._tables = []
self.__class__._table_index += 1
id = self.__class__._table_index
engine = sqlahelper.get_engine()
connection = engine.connect()
if not self.metadata:
self.metadata = declarative_base(bind=engine).metadata
tablename = "table_%d" % id
table1 = Table(
"%s_child" % tablename, self.metadata,
Column("id", types.Integer, primary_key=True),
Column("name", types.Unicode),
schema="public"
)
self._tables.append(table1)
table2 = Table(
tablename, self.metadata,
Column("id", types.Integer, primary_key=True),
Column("child_id", types.Integer,
ForeignKey("public.%s_child.id" % tablename)),
Column("name", types.Unicode),
Column("geom", Geometry("POINT", srid=21781, management=management)),
schema="public"
)
self._tables.append(table2)
table2.drop(checkfirst=True)
table1.drop(checkfirst=True)
table1.create()
table2.create()
ins = table1.insert().values(name=u"c1é")
c1_id = connection.execute(ins).inserted_primary_key[0]
ins = table1.insert().values(name=u"c2é")
c2_id = connection.execute(ins).inserted_primary_key[0]
ins = table2.insert().values(
child_id=c1_id,
name="foo",
geom=WKTElement("POINT(5 45)", 21781)
)
connection.execute(ins)
ins = table2.insert().values(
child_id=c2_id,
name="bar",
geom=WKTElement("POINT(6 46)", 21781)
)
connection.execute(ins)
if attr_list:
ins = table2.insert().values(
child_id=c2_id,
name="aaa,bbb,foo",
geom=WKTElement("POINT(6 46)", 21781)
)
connection.execute(ins)
layer = LayerV1()
layer.id = id
layer.name = str(id)
layer.geo_table = tablename
layer.public = public
layer.interface = [self.main]
if exclude_properties:
layer.exclude_properties = "name"
if metadatas:
layer.metadatas = metadatas
DBSession.add(layer)
if not public:
ra = RestrictionArea()
ra.name = u"__test_ra"
ra.layers = [layer]
ra.roles = [self.role]
ra.readwrite = True
if not none_area:
poly = "POLYGON((4 44, 4 46, 6 46, 6 44, 4 44))"
ra.area = WKTElement(poly, srid=21781)
DBSession.add(ra)
transaction.commit()
self.layer_ids.append(id)
return id
def test_one_engine(self):
e = sa.create_engine(self.db1.url)
sqlahelper.add_engine(e)
retrieved = sqlahelper.get_engine()
self.assertIs(retrieved, e)
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from shapely import wkb
from shapely.geometry import asShape
from shapely.geometry.multipoint import asMultiPoint
from shapely.geometry.multilinestring import asMultiLineString
from shapely.geometry.multipolygon import MultiPolygonAdapter
from geoalchemy2 import Geometry, func
from geoalchemy2.shape import from_shape
import geojson
engine = sqlahelper.get_engine('mymaps')
Base = declarative_base(bind=engine)
Session = sessionmaker(bind=engine)
DBSession = Session()
class Map(Base):
__tablename__ = 'map'
uuid = Column(Unicode, primary_key=True,
default=lambda: unicode(uuid.uuid4().hex))
user_login = Column(Unicode(50))
title = Column(Unicode(50))
description = Column(Unicode)
public = Column(Boolean)
create_date = Column(DateTime, default=datetime.datetime.now)
update_date = Column(DateTime, onupdate=datetime.datetime.now)
import sqlahelper as sah
import saio
saio.register_schema("supply", sah.get_engine('oep_engine'))
from saio.supply import openfred_feedin_wind_2016 as Timeseries
from saio.supply import openfred_powerplants as Powerplants
def get_feature_info(self):
layers = self.request.params.get('layers', None)
if layers is None:
return HTTPBadRequest()
big_box = self.request.params.get('box1', None)
small_box = self.request.params.get('box2', None)
if big_box is None or small_box is None:
return HTTPBadRequest()
luxgetfeaturedefinitions = self.get_lux_feature_definition(layers)
coordinates_big_box = big_box.split(',')
coordinates_small_box = small_box.split(',')
results = []
for luxgetfeaturedefinition in luxgetfeaturedefinitions:
if (luxgetfeaturedefinition is not None and
luxgetfeaturedefinition.engine is not None and
luxgetfeaturedefinition.query is not None and
len(luxgetfeaturedefinition.query) > 0):
engine = sqlahelper.get_engine(luxgetfeaturedefinition.engine)
query_1 = luxgetfeaturedefinition.query
if "WHERE" in query_1.upper():
query_1 = query_1 + " AND "
else:
query_1 = query_1 + " WHERE "
if "SELECT" in query_1.upper():
query_1 = query_1.replace(
"SELECT",
"SELECT ST_AsGeoJSON (%(geom)s), "
% {'geom': luxgetfeaturedefinition.geometry_column}, 1)
else:
query_1 = "SELECT *,ST_AsGeoJSON(%(geom)s) FROM "\
% {'geom': luxgetfeaturedefinition.geometry_column} +\
query_1
query_point = query_1 + "ST_Intersects( %(geom)s, "\
"ST_MakeEnvelope(%(left)s, %(bottom)s, %(right)s,"\
"%(top)s, 2169) ) AND ST_NRings(%(geom)s) = 0"\
% {'left': coordinates_big_box[0],
'bottom': coordinates_big_box[1],
'right': coordinates_big_box[2],
'top': coordinates_big_box[3],
'geom': luxgetfeaturedefinition.geometry_column}
query_others = query_1 + "ST_Intersects( %(geom)s,"\
" ST_MakeEnvelope (%(left)s, %(bottom)s, %(right)s,"\
" %(top)s, 2169) ) AND ST_NRings(%(geom)s) > 0"\
% {'left': coordinates_small_box[0],
'bottom': coordinates_small_box[1],
'right': coordinates_small_box[2],
'top': coordinates_small_box[3],
'geom': luxgetfeaturedefinition.geometry_column}
query = query_point + " UNION ALL " + query_others +\
" LIMIT 20"
res = engine.execute(query)
rows = res.fetchall()
if (luxgetfeaturedefinition.additional_info_function
is not None and
len(luxgetfeaturedefinition.additional_info_function)
> 0):
features = eval(luxgetfeaturedefinition.
additional_info_function)
if len(features) > 0:
results.append(
self.to_featureinfo(
features,
luxgetfeaturedefinition.layer,
luxgetfeaturedefinition.template,
luxgetfeaturedefinition.remote_template))
else:
features = []
for row in rows:
geometry = geojson_loads(row['st_asgeojson'])
attributes = dict(row)
f = self.to_feature(
geometry,
attributes,
luxgetfeaturedefinition.attributes_to_remove,
luxgetfeaturedefinition.geometry_column)
features.append(f)
if len(features) > 0:
results.append(
self.to_featureinfo(
self.remove_features_outside_tolerance(
features, coordinates_small_box),
luxgetfeaturedefinition.layer,
luxgetfeaturedefinition.template,
luxgetfeaturedefinition.remote_template))
if (luxgetfeaturedefinition is not None and
luxgetfeaturedefinition.rest_url is not None and
len(luxgetfeaturedefinition.rest_url) > 0):
features = self._get_external_data(
luxgetfeaturedefinition.rest_url,
big_box, None, None,
luxgetfeaturedefinition.attributes_to_remove)
if len(features) > 0:
results.append(
self.to_featureinfo(
self.remove_features_outside_tolerance(
features, coordinates_small_box),
luxgetfeaturedefinition.layer,
luxgetfeaturedefinition.template,
luxgetfeaturedefinition.remote_template))
return results
# -*- coding: utf-8 -*-
import sqlahelper
from sqlalchemy import Column, Unicode
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from geoalchemy2 import Geometry
engine = sqlahelper.get_engine("ecadastre")
Base = declarative_base(bind=engine)
Session = sessionmaker(bind=engine)
DBSession = Session()
class Address(Base):
__table_args__ = {"schema": "diffdata", "autoload": False}
__tablename__ = "v_pcn_addresspoints"
id = Column("gid", Unicode, primary_key=True)
rue = Column(Unicode)
numero = Column(Unicode)
localite = Column(Unicode)
code_postal = Column(Unicode)
id_caclr_rue = Column(Unicode)
id_caclr_bat = Column(Unicode)
geom = Column(Geometry(srid=2169))
from sqlalchemy.types import Unicode, Boolean, DateTime, Integer, Float, Binary
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from shapely import wkb
from shapely.geometry import asShape
from shapely.geometry.multipoint import asMultiPoint
from shapely.geometry.multilinestring import asMultiLineString
from shapely.geometry.multipolygon import MultiPolygonAdapter
from geoalchemy2 import Geometry, func
from geoalchemy2.shape import from_shape
import geojson
engine = sqlahelper.get_engine('mymaps')
Base = declarative_base(bind=engine)
Session = sessionmaker(bind=engine)
DBSession = Session()
class Map(Base):
__tablename__ = 'map'
uuid = Column(Unicode,
primary_key=True,
default=lambda: unicode(uuid.uuid4().hex))
user_login = Column(Unicode(50))
title = Column(Unicode(50))
description = Column(Unicode)
public = Column(Boolean)
create_date = Column(DateTime, default=datetime.datetime.now)
# -*- coding: utf-8 -*-
import datetime
import sqlahelper
from c2cgeoportal.models import * # noqa
from formalchemy import Column
from sqlalchemy.types import Integer, String, DateTime
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine = sqlahelper.get_engine()
Base = declarative_base(bind=engine)
Session = sessionmaker(bind=engine)
PortailSession = Session()
class Connections(Base):
__table_args__ = ({'schema': 'geov3_stats', 'autoload': False})
__tablename__ = 'connections'
id = Column(Integer, primary_key=True)
ip = Column(String)
action = Column(String)
login = Column(String)
application = Column(String)
class MesurageDownload(Base):
__table_args__ = ({'schema': 'geov3_stats'})
__tablename__ = 'measurement_download'
id = Column(Integer, primary_key=True)
login = Column(String)
def _create_layer(self,
public=False,
none_area=False,
attr_list=False,
exclude_properties=False):
""" This function is central for this test class. It creates
a layer with two features, and associates a restriction area
to it. """
import transaction
import sqlahelper
from sqlalchemy import func
from sqlalchemy import Column, Table, types, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from geoalchemy import (GeometryDDL, GeometryExtensionColumn, Point,
WKTSpatialElement)
from c2cgeoportal.models import DBSession, Layer, RestrictionArea
self.__class__._table_index = self.__class__._table_index + 1
id = self.__class__._table_index
engine = sqlahelper.get_engine()
if not self.metadata:
self.metadata = declarative_base(bind=engine).metadata
tablename = "table_%d" % id
table = Table('%s_child' % tablename,
self.metadata,
Column('id', types.Integer, primary_key=True),
Column('name', types.Unicode),
schema='public')
table.create()
ins = table.insert().values(name=u'c1é')
c1_id = engine.connect().execute(ins).inserted_primary_key[0]
ins = table.insert().values(name=u'c2é')
c2_id = engine.connect().execute(ins).inserted_primary_key[0]
table = Table(tablename,
self.metadata,
Column('id', types.Integer, primary_key=True),
Column('child_id', types.Integer,
ForeignKey('public.%s_child.id' % tablename)),
Column('name', types.Unicode),
GeometryExtensionColumn('geom', Point(srid=21781)),
schema='public')
GeometryDDL(table)
table.create()
ins = table.insert().values(child_id=c1_id,
name='foo',
geom=func.ST_GeomFromText(
'POINT(5 45)', 21781))
engine.connect().execute(ins).inserted_primary_key[0]
ins = table.insert().values(child_id=c2_id,
name='bar',
geom=func.ST_GeomFromText(
'POINT(6 46)', 21781))
engine.connect().execute(ins).inserted_primary_key[0]
if attr_list:
ins = table.insert().values(child_id=c2_id,
name='aaa,bbb,foo',
geom=func.ST_GeomFromText(
'POINT(6 46)', 21781))
engine.connect().execute(ins).inserted_primary_key[0]
layer = Layer()
layer.id = id
layer.geoTable = tablename
layer.public = public
if exclude_properties:
layer.excludeProperties = 'name'
DBSession.add(layer)
if not public:
ra = RestrictionArea()
ra.name = u'__test_ra'
ra.layers = [layer]
ra.roles = [self.role]
ra.readwrite = True
if not none_area:
poly = 'POLYGON((4 44, 4 46, 6 46, 6 44, 4 44))'
ra.area = WKTSpatialElement(poly, srid=21781)
DBSession.add(ra)
self.layer_ids.append(self.__class__._table_index)
transaction.commit()
return id
MetaData, String as Str, Text, UniqueConstraint as UC,
Table, Numeric)
from sqlalchemy.dialects.postgresql import ARRAY
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
import sqlahelper as sah
from geoalchemy2 import types as geotypes
# import WAM_APP_FRED.app_settings
# from geoalchemy2.types import Geometry
# from WAM_APP_FRED.cli.openFRED import mapped_classes, db_session
# ##########################################SQLAlchemy setup########################################
SCHEMA_1 = 'climate'
SCHEMA_2 = 'supply'
engine = sah.get_engine('oep_engine')
metadata_1 = MetaData(schema=SCHEMA_1, bind=engine)
metadata_2 = MetaData(schema=SCHEMA_2, bind=engine)
# ##########################################TABLE DEFINITION########################################
# included function from github: https://github.com/open-fred/cli/blob/master/openFRED.py
# copied function to avoid dependencies
def mapped_classes(metadata):
""" Returns classes mapped to the openFRED database via SQLAlchemy.
The classes are dynamically created and stored in a dictionary keyed by
class names. The dictionary also contains the special entry `__Base__`,
which an SQLAlchemy `declarative_base` instance used as the base class from
which all mapped classes inherit.
"""
def test_one_engine(self):
e = sa.create_engine(self.db1.url)
sqlahelper.add_engine(e)
retrieved = sqlahelper.get_engine()
self.assertIs(retrieved, e)
# -*- coding: utf-8 -*-
import sqlahelper
from sqlalchemy import Column, Unicode
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from geoalchemy2 import Geometry
engine = sqlahelper.get_engine('ecadastre')
Base = declarative_base(bind=engine)
Session = sessionmaker(bind=engine)
DBSession = Session()
class Address(Base):
__table_args__ = ({'schema': 'diffdata', 'autoload': False})
__tablename__ = 'v_pcn_addresspoints'
id = Column('gid', Unicode, primary_key=True)
rue = Column(Unicode)
numero = Column(Unicode)
localite = Column(Unicode)
code_postal = Column(Unicode)
id_caclr_rue = Column(Unicode)
id_caclr_bat = Column(Unicode)
geom = Column(Geometry(srid=2169))
def get_feature_info(self):
fid = self.request.params.get('fid', None)
if fid is not None:
layers, fid = fid.split('_', 1)
if layers is None or fid is None:
return HTTPBadRequest()
else:
layers = self.request.params.get('layers', None)
if layers is None:
return HTTPBadRequest()
big_box = self.request.params.get('box1', None)
small_box = self.request.params.get('box2', None)
if big_box is None or small_box is None:
return HTTPBadRequest()
luxgetfeaturedefinitions = self.get_lux_feature_definition(layers)
if fid is None:
coordinates_big_box = big_box.split(',')
coordinates_small_box = small_box.split(',')
results = []
for luxgetfeaturedefinition in luxgetfeaturedefinitions:
if (luxgetfeaturedefinition is not None
and luxgetfeaturedefinition.engine is not None
and luxgetfeaturedefinition.query is not None
and len(luxgetfeaturedefinition.query) > 0):
engine = sqlahelper.get_engine(luxgetfeaturedefinition.engine)
is_ordered = luxgetfeaturedefinition.columns_order is not None\
and len(luxgetfeaturedefinition.columns_order) > 0
query_1 = luxgetfeaturedefinition.query
if "WHERE" in query_1.upper():
query_1 = query_1 + " AND "
else:
query_1 = query_1 + " WHERE "
if "SELECT" in query_1.upper():
query_1 = query_1.replace(
"SELECT", "SELECT ST_AsGeoJSON (%(geom)s), " %
{'geom': luxgetfeaturedefinition.geometry_column}, 1)
else:
query_1 = "SELECT *,ST_AsGeoJSON(%(geom)s) FROM "\
% {'geom': luxgetfeaturedefinition.geometry_column} +\
query_1
if fid is None:
query_point = query_1 + "ST_Intersects( %(geom)s, "\
"ST_MakeEnvelope(%(left)s, %(bottom)s, %(right)s,"\
"%(top)s, 2169) ) AND ST_NRings(%(geom)s) = 0"\
% {'left': coordinates_big_box[0],
'bottom': coordinates_big_box[1],
'right': coordinates_big_box[2],
'top': coordinates_big_box[3],
'geom': luxgetfeaturedefinition.geometry_column}
query_others = query_1 + "ST_Intersects( %(geom)s,"\
" ST_MakeEnvelope (%(left)s, %(bottom)s, %(right)s,"\
" %(top)s, 2169) ) AND ST_NRings(%(geom)s) > 0"\
% {'left': coordinates_small_box[0],
'bottom': coordinates_small_box[1],
'right': coordinates_small_box[2],
'top': coordinates_small_box[3],
'geom': luxgetfeaturedefinition.geometry_column}
query = query_point + " UNION ALL " + query_others +\
" LIMIT 20"
else:
if luxgetfeaturedefinition.id_column is not None:
query = query_1 + luxgetfeaturedefinition.id_column +\
" = '" + fid + "'"
else:
query = query_1 + " id = '" + fid + "'"
res = engine.execute(query)
rows = res.fetchall()
if (luxgetfeaturedefinition.additional_info_function
is not None and
len(luxgetfeaturedefinition.additional_info_function) >
0):
features = eval(
luxgetfeaturedefinition.additional_info_function)
if len(features) > 0:
results.append(
self.to_featureinfo(
features, luxgetfeaturedefinition.layer,
luxgetfeaturedefinition.template, is_ordered,
luxgetfeaturedefinition.remote_template))
else:
features = []
for row in rows:
geometry = geojson_loads(row['st_asgeojson'])
attributes = dict(row)
if luxgetfeaturedefinition.id_column in row:
featureid = row[luxgetfeaturedefinition.id_column]
else:
if 'id' in row:
featureid = row['id']
else:
featureid = None
f = self.to_feature(
luxgetfeaturedefinition.layer, featureid, geometry,
attributes,
luxgetfeaturedefinition.attributes_to_remove,
luxgetfeaturedefinition.columns_order,
luxgetfeaturedefinition.geometry_column)
features.append(f)
if len(features) > 0:
if fid is None:
results.append(
self.to_featureinfo(
self.remove_features_outside_tolerance(
features, coordinates_small_box),
luxgetfeaturedefinition.layer,
luxgetfeaturedefinition.template,
is_ordered,
luxgetfeaturedefinition.remote_template))
else:
results.append(
self.to_featureinfo(
features, luxgetfeaturedefinition.layer,
luxgetfeaturedefinition.template,
is_ordered,
luxgetfeaturedefinition.remote_template))
if (luxgetfeaturedefinition is not None
and luxgetfeaturedefinition.rest_url is not None
and len(luxgetfeaturedefinition.rest_url) > 0):
if fid is None:
features = self._get_external_data(
luxgetfeaturedefinition.layer,
luxgetfeaturedefinition.rest_url,
luxgetfeaturedefinition.id_column, big_box, None, None,
luxgetfeaturedefinition.attributes_to_remove,
luxgetfeaturedefinition.columns_order)
else:
features = self._get_external_data(
luxgetfeaturedefinition.layer,
luxgetfeaturedefinition.rest_url,
luxgetfeaturedefinition.id_column, None, fid, None,
luxgetfeaturedefinition.attributes_to_remove,
luxgetfeaturedefinition.columns_order)
if len(features) > 0:
if (luxgetfeaturedefinition.additional_info_function
is not None and len(luxgetfeaturedefinition.
additional_info_function) > 0):
features = eval(
luxgetfeaturedefinition.additional_info_function)
is_ordered =\
luxgetfeaturedefinition.columns_order is not None\
and len(luxgetfeaturedefinition.columns_order) > 0
if fid is None:
results.append(
self.to_featureinfo(
self.remove_features_outside_tolerance(
features, coordinates_small_box),
luxgetfeaturedefinition.layer,
luxgetfeaturedefinition.template, is_ordered,
luxgetfeaturedefinition.remote_template))
else:
results.append(
self.to_featureinfo(
features, luxgetfeaturedefinition.layer,
luxgetfeaturedefinition.template, is_ordered,
luxgetfeaturedefinition.remote_template))
return results
