def get_engine(self):
comp = env.postgis
# Need to check connection params to see if
# they changed for each connection request
credhash = (self.hostname, self.port, self.database, self.username,
self.password)
if self.id in comp._engine:
engine = comp._engine[self.id]
if engine._credhash == credhash:
return engine
else:
del comp._engine[self.id]
connect_timeout = int(comp.options['connect_timeout'].total_seconds())
statement_timeout_ms = int(
comp.options['statement_timeout'].total_seconds()) * 1000
args = dict(client_encoding='utf-8',
connect_args=dict(connect_timeout=connect_timeout,
options='-c statement_timeout=%d' %
statement_timeout_ms))
engine_url = make_engine_url(
EngineURL.create('postgresql+psycopg2',
host=self.hostname,
port=self.port,
database=self.database,
username=self.username,
password=self.password))
engine = db.create_engine(engine_url, **args)
resid = self.id
@db.event.listens_for(engine, 'connect')
def _connect(dbapi, record):
logger.debug("Resource #%d, pool 0x%x, connection 0x%x created",
resid, id(dbapi), id(engine))
@db.event.listens_for(engine, 'checkout')
def _checkout(dbapi, record, proxy):
logger.debug("Resource #%d, pool 0x%x, connection 0x%x retrieved",
resid, id(dbapi), id(engine))
@db.event.listens_for(engine, 'checkin')
def _checkin(dbapi, record):
logger.debug("Resource #%d, pool 0x%x, connection 0x%x returned",
resid, id(dbapi), id(engine))
engine._credhash = credhash
comp._engine[self.id] = engine
return engine
def get_engine(self):
comp = env.postgis
# Need to check connection params to see if
# they changed for each connection request
credhash = (self.hostname, self.port, self.database, self.username,
self.password)
if self.id in comp._engine:
engine = comp._engine[self.id]
if engine._credhash == credhash:
return engine
else:
del comp._engine[self.id]
engine = db.create_engine(
make_engine_url(
EngineURL('postgresql+psycopg2',
host=self.hostname,
port=self.port,
database=self.database,
username=self.username,
password=self.password)))
resid = self.id
@db.event.listens_for(engine, 'connect')
def _connect(dbapi, record):
comp.logger.debug(
"Resource #%d, pool 0x%x, connection 0x%x created", resid,
id(dbapi), id(engine))
@db.event.listens_for(engine, 'checkout')
def _checkout(dbapi, record, proxy):
comp.logger.debug(
"Resource #%d, pool 0x%x, connection 0x%x retrieved", resid,
id(dbapi), id(engine))
@db.event.listens_for(engine, 'checkin')
def _checkin(dbapi, record):
comp.logger.debug(
"Resource #%d, pool 0x%x, connection 0x%x returned", resid,
id(dbapi), id(engine))
engine._credhash = credhash
comp._engine[self.id] = engine
return engine
def initialize(self):
Component.initialize(self)
self.locale_default = self._settings.get('locale.default', 'en')
self.locale_available = self._settings.get(
'locale.available', 'en ru').split(' ')
setting_debug = self._settings.get('debug', 'false').lower()
self.debug = setting_debug in ('true', 'yes', '1')
if 'support_url' not in self._settings:
self._settings['support_url'] = "http://nextgis.com/contact/"
sa_url = make_engine_url(EngineURL(
'postgresql+psycopg2',
host=self._settings.get('database.host', 'localhost'),
database=self._settings.get('database.name', 'nextgisweb'),
username=self._settings.get('database.user', 'nextgisweb'),
password=self._settings.get('database.password', '')
))
self.engine = create_engine(sa_url)
self._sa_engine = self.engine
setting_check_at_startup = self._settings.get(
'database.check_at_startup', 'false').lower()
if setting_check_at_startup in ('true', 'yes', '1'):
conn = self._sa_engine.connect()
conn.close()
DBSession.configure(bind=self._sa_engine)
self.DBSession = DBSession
self._backup_path = self.settings.get('backup.filename')
self._backup_filename = self.settings.get(
'backup.filename', '%Y%m%d-%H%M%S')
self._backup_upload_bucket = self.settings.get(
'backup_upload.bucket', 'ngwbackup')
self._backup_upload_server = self.settings.get('backup_upload.server')
self._backup_upload_access_key = self.settings.get(
'backup_upload.access_key')
self._backup_upload_secret_key = self.settings.get(
'backup_upload.secret_key')
def _engine_url(self, error_on_pwfile=False):
con_args = self._db_connection_args(error_on_pwfile=error_on_pwfile)
return make_engine_url(EngineURL('postgresql+psycopg2', **con_args))
def create_feature_layer(ogrlayer, parent_id, **kwargs):
opts_db = env.core.options.with_prefix('test.database')
for o in ('host', 'name', 'user'):
if o not in opts_db:
pytest.skip(f"Option test.database.{o} isn't set")
con_args = dict(host=opts_db['host'],
port=opts_db['port'],
database=opts_db['name'],
username=opts_db['user'],
password=opts_db['password'])
engine_url = make_engine_url(
EngineURL.create('postgresql+psycopg2', **con_args))
engine = sa.create_engine(engine_url)
meta = sa.MetaData()
column_id = 'id'
columns = [sa.Column(column_id, sa.Integer, primary_key=True)]
column_geom = 'the_geom'
geom_type = _GEOM_OGR_2_TYPE[ogrlayer.GetGeomType()]
dimension = 3 if geom_type in GEOM_TYPE.has_z else 2
geometry_type_db = _GEOM_TYPE_2_DB[geom_type]
osr_ = ogrlayer.GetSpatialRef()
assert osr_.GetAuthorityName(None) == 'EPSG'
srid = int(osr_.GetAuthorityCode(None))
columns.append(
sa.Column(
column_geom,
ga.Geometry(dimension=dimension,
srid=srid,
geometry_type=geometry_type_db)))
# Make columns different from keynames
def column_keyname(name):
return name[4:]
def keyname_column(keyname):
return 'fld_%s' % keyname
defn = ogrlayer.GetLayerDefn()
for i in range(defn.GetFieldCount()):
fld_defn = defn.GetFieldDefn(i)
fld_name = fld_defn.GetNameRef()
fld_type = _FIELD_TYPE_2_ENUM[fld_defn.GetType()]
columns.append(
sa.Column(keyname_column(fld_name), _FIELD_TYPE_2_DB[fld_type]))
table = sa.Table('test_' + uuid4().hex, meta, *columns)
meta.create_all(engine)
with engine.connect() as conn:
with conn.begin():
for i, feature in enumerate(ogrlayer, start=1):
values = dict(id=i)
geom = feature.GetGeometryRef()
geom_bytes = bytearray(geom.ExportToWkb(ogr.wkbNDR))
values[column_geom] = ga.elements.WKBElement(geom_bytes,
srid=srid)
for k in range(feature.GetFieldCount()):
if not feature.IsFieldSet(k) or feature.IsFieldNull(k):
continue
fld_defn = defn.GetFieldDefn(k)
fld_name = fld_defn.GetNameRef()
fld_get = FIELD_GETTER[fld_defn.GetType()]
values[keyname_column(fld_name)] = fld_get(feature, k)
conn.execute(table.insert().values(**values))
with transaction.manager:
res_common = dict(parent_id=parent_id,
owner_user=User.by_keyname('administrator'))
connection = PostgisConnection(**res_common,
display_name='PostGIS connection',
hostname=opts_db['host'],
port=opts_db['port'],
database=opts_db['name'],
username=opts_db['user'],
password=opts_db['password']).persist()
layer = PostgisLayer(
**res_common,
display_name='Feature layer (postgis)',
connection=connection,
srs=SRS.filter_by(id=3857).one(),
table=table.name,
schema='public',
column_id=column_id,
column_geom=column_geom,
geometry_type=geom_type,
geometry_srid=srid,
).persist()
DBSession.flush()
layer.setup()
for field in layer.fields:
field.keyname = field.display_name = column_keyname(
field.column_name)
try:
yield layer
finally:
meta.drop_all(engine)
engine.dispose()