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 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 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)
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. """
# -*- 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