def __init__(self,
database,
context,
app_root=None,
table='records',
repo_filter=None):
''' Initialize repository '''
self.context = context
self.filter = repo_filter
self.fts = False
# Don't use relative paths, this is hack to get around
# most wsgi restriction...
if (app_root and database.startswith('sqlite:///')
and not database.startswith('sqlite:////')):
database = database.replace('sqlite:///',
'sqlite:///%s%s' % (app_root, os.sep))
self.engine = Repository.create_engine('%s' % database)
base = declarative_base(bind=self.engine)
LOGGER.debug('binding ORM to existing database')
self.postgis_geometry_column = None
schema, table = util.sniff_table(table)
self.dataset = type(
'dataset', (base, ),
dict(__tablename__=table,
__table_args__={
'autoload': True,
'schema': schema
}))
self.dbtype = self.engine.name
self.session = create_session(self.engine)
temp_dbtype = None
if self.dbtype == 'postgresql':
# check if PostgreSQL is enabled with PostGIS 1.x
try:
self.session.execute(select([func.postgis_version()]))
temp_dbtype = 'postgresql+postgis+wkt'
LOGGER.debug('PostgreSQL+PostGIS1+WKT detected')
except Exception, err:
LOGGER.debug('PostgreSQL+PostGIS1+WKT detection failed')
# check if PostgreSQL is enabled with PostGIS 2.x
try:
self.session.execute('select(postgis_version())')
temp_dbtype = 'postgresql+postgis+wkt'
LOGGER.debug('PostgreSQL+PostGIS2+WKT detected')
except Exception, err:
LOGGER.debug('PostgreSQL+PostGIS2+WKT detection failed')
def __init__(self, database, context, app_root=None, table='records', repo_filter=None):
''' Initialize repository '''
self.context = context
self.filter = repo_filter
self.fts = False
# Don't use relative paths, this is hack to get around
# most wsgi restriction...
if (app_root and database.startswith('sqlite:///') and
not database.startswith('sqlite:////')):
database = database.replace('sqlite:///',
'sqlite:///%s%s' % (app_root, os.sep))
self.engine = Repository.create_engine('%s' % database)
base = declarative_base(bind=self.engine)
LOGGER.debug('binding ORM to existing database')
self.postgis_geometry_column = None
schema, table = util.sniff_table(table)
self.dataset = type('dataset', (base,),
dict(__tablename__=table,__table_args__={'autoload': True,
'schema': schema}))
self.dbtype = self.engine.name
self.session = create_session(self.engine)
temp_dbtype = None
if self.dbtype == 'postgresql':
# check if PostgreSQL is enabled with PostGIS 1.x
try:
self.session.execute(select([func.postgis_version()]))
temp_dbtype = 'postgresql+postgis+wkt'
LOGGER.debug('PostgreSQL+PostGIS1+WKT detected')
except Exception, err:
LOGGER.debug('PostgreSQL+PostGIS1+WKT detection failed')
# check if PostgreSQL is enabled with PostGIS 2.x
try:
self.session.execute('select(postgis_version())')
temp_dbtype = 'postgresql+postgis+wkt'
LOGGER.debug('PostgreSQL+PostGIS2+WKT detected')
except Exception, err:
LOGGER.debug('PostgreSQL+PostGIS2+WKT detection failed')
def __init__(self, database, context, app_root=None, table='records', repo_filter=None):
''' Initialize repository '''
self.context = context
self.filter = repo_filter
self.fts = False
# Don't use relative paths, this is hack to get around
# most wsgi restriction...
if (app_root and database.startswith('sqlite:///') and
not database.startswith('sqlite:////')):
database = database.replace('sqlite:///',
'sqlite:///%s%s' % (app_root, os.sep))
self.engine = Repository.create_engine('%s' % database)
base = declarative_base(bind=self.engine)
LOGGER.debug('binding ORM to existing database')
self.postgis_geometry_column = None
schema, table = util.sniff_table(table)
self.dataset = type('dataset', (base,),
dict(__tablename__=table,__table_args__={'autoload': True,
'schema': schema}))
self.dbtype = self.engine.name
self.session = create_session(self.engine)
temp_dbtype = None
if self.dbtype == 'postgresql':
# check if PostgreSQL is enabled with PostGIS 1.x
try:
self.session.execute(select([func.postgis_version()]))
temp_dbtype = 'postgresql+postgis+wkt'
LOGGER.debug('PostgreSQL+PostGIS1+WKT detected')
except Exception as err:
LOGGER.debug('PostgreSQL+PostGIS1+WKT detection failed')
# check if PostgreSQL is enabled with PostGIS 2.x
try:
self.session.execute('select(postgis_version())')
temp_dbtype = 'postgresql+postgis+wkt'
LOGGER.debug('PostgreSQL+PostGIS2+WKT detected')
except Exception as err:
LOGGER.debug('PostgreSQL+PostGIS2+WKT detection failed')
# check if a native PostGIS geometry column exists
try:
result = self.session.execute("select f_geometry_column from geometry_columns where f_table_name = '%s' and f_geometry_column != 'wkt_geometry' limit 1;" % table)
row = result.fetchone()
self.postgis_geometry_column = str(row['f_geometry_column'])
temp_dbtype = 'postgresql+postgis+native'
LOGGER.debug('PostgreSQL+PostGIS+Native detected')
except Exception as err:
LOGGER.debug('PostgreSQL+PostGIS+Native not picked up: %s', str(err))
# check if a native PostgreSQL FTS GIN index exists
result = self.session.execute("select relname from pg_class where relname='fts_gin_idx'").scalar()
self.fts = bool(result)
LOGGER.debug('PostgreSQL FTS enabled: %r', self.fts)
if temp_dbtype is not None:
LOGGER.debug('%s support detected' % temp_dbtype)
self.dbtype = temp_dbtype
if self.dbtype in ['sqlite', 'sqlite3']: # load SQLite query bindings
# <= 0.6 behaviour
if not __version__ >= '0.7':
self.connection = self.engine.raw_connection()
self.connection.create_function(
'query_spatial', 4, util.query_spatial)
self.connection.create_function(
'update_xpath', 3, util.update_xpath)
self.connection.create_function('get_anytext', 1,
util.get_anytext)
self.connection.create_function('get_geometry_area', 1,
util.get_geometry_area)
self.connection.create_function('get_spatial_overlay_rank', 2,
util.get_spatial_overlay_rank)
LOGGER.debug('setting repository queryables')
# generate core queryables db and obj bindings
self.queryables = {}
for tname in self.context.model['typenames']:
for qname in self.context.model['typenames'][tname]['queryables']:
self.queryables[qname] = {}
for qkey, qvalue in \
self.context.model['typenames'][tname]['queryables'][qname].iteritems():
self.queryables[qname][qkey] = qvalue
# flatten all queryables
# TODO smarter way of doing this
self.queryables['_all'] = {}
for qbl in self.queryables:
self.queryables['_all'].update(self.queryables[qbl])
self.queryables['_all'].update(self.context.md_core_model['mappings'])
def setup_db(database, table, home, create_sfsql_tables=True, create_plpythonu_functions=True, postgis_geometry_column='wkb_geometry', extra_columns=[], language='english'):
"""Setup database tables and indexes"""
from sqlalchemy import Column, create_engine, Integer, MetaData, \
Table, Text
from sqlalchemy.orm import create_session
LOGGER.info('Creating database %s', database)
if database.startswith('sqlite'):
dbtype, filepath = database.split('sqlite:///')
dirname = os.path.dirname(filepath)
if not os.path.exists(dirname):
raise RuntimeError('SQLite directory %s does not exist' % dirname)
dbase = create_engine(database)
schema, table = util.sniff_table(table)
mdata = MetaData(dbase, schema=schema)
create_postgis_geometry = False
# If PostGIS 2.x detected, do not create sfsql tables.
if dbase.name == 'postgresql':
try:
dbsession = create_session(dbase)
for row in dbsession.execute('select(postgis_lib_version())'):
postgis_lib_version = row[0]
create_sfsql_tables=False
create_postgis_geometry = True
LOGGER.info('PostGIS %s detected: Skipping SFSQL tables creation' % postgis_lib_version)
except:
pass
if create_sfsql_tables:
LOGGER.info('Creating table spatial_ref_sys')
srs = Table(
'spatial_ref_sys', mdata,
Column('srid', Integer, nullable=False, primary_key=True),
Column('auth_name', Text),
Column('auth_srid', Integer),
Column('srtext', Text)
)
srs.create()
i = srs.insert()
i.execute(srid=4326, auth_name='EPSG', auth_srid=4326, srtext='GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]')
LOGGER.info('Creating table geometry_columns')
geom = Table(
'geometry_columns', mdata,
Column('f_table_catalog', Text, nullable=False),
Column('f_table_schema', Text, nullable=False),
Column('f_table_name', Text, nullable=False),
Column('f_geometry_column', Text, nullable=False),
Column('geometry_type', Integer),
Column('coord_dimension', Integer),
Column('srid', Integer, nullable=False),
Column('geometry_format', Text, nullable=False),
)
geom.create()
i = geom.insert()
i.execute(f_table_catalog='public', f_table_schema='public',
f_table_name=table, f_geometry_column='wkt_geometry',
geometry_type=3, coord_dimension=2,
srid=4326, geometry_format='WKT')
# abstract metadata information model
LOGGER.info('Creating table %s', table)
records = Table(
table, mdata,
# core; nothing happens without these
Column('identifier', Text, primary_key=True),
Column('typename', Text,
default='csw:Record', nullable=False, index=True),
Column('schema', Text,
default='http://www.opengis.net/cat/csw/2.0.2', nullable=False,
index=True),
Column('mdsource', Text, default='local', nullable=False,
index=True),
Column('insert_date', Text, nullable=False, index=True),
Column('xml', Text, nullable=False),
Column('anytext', Text, nullable=False),
Column('language', Text, index=True),
# identification
Column('type', Text, index=True),
Column('title', Text, index=True),
Column('title_alternate', Text, index=True),
Column('abstract', Text, index=True),
Column('keywords', Text, index=True),
Column('keywordstype', Text, index=True),
Column('parentidentifier', Text, index=True),
Column('relation', Text, index=True),
Column('time_begin', Text, index=True),
Column('time_end', Text, index=True),
Column('topicategory', Text, index=True),
Column('resourcelanguage', Text, index=True),
# attribution
Column('creator', Text, index=True),
Column('publisher', Text, index=True),
Column('contributor', Text, index=True),
Column('organization', Text, index=True),
# security
Column('securityconstraints', Text, index=True),
Column('accessconstraints', Text, index=True),
Column('otherconstraints', Text, index=True),
# date
Column('date', Text, index=True),
Column('date_revision', Text, index=True),
Column('date_creation', Text, index=True),
Column('date_publication', Text, index=True),
Column('date_modified', Text, index=True),
Column('format', Text, index=True),
Column('source', Text, index=True),
# geospatial
Column('crs', Text, index=True),
Column('geodescode', Text, index=True),
Column('denominator', Integer, index=True),
Column('distancevalue', Integer, index=True),
Column('distanceuom', Text, index=True),
Column('wkt_geometry', Text),
# service
Column('servicetype', Text, index=True),
Column('servicetypeversion', Text, index=True),
Column('operation', Text, index=True),
Column('couplingtype', Text, index=True),
Column('operateson', Text, index=True),
Column('operatesonidentifier', Text, index=True),
Column('operatesoname', Text, index=True),
# additional
Column('degree', Text, index=True),
Column('classification', Text, index=True),
Column('conditionapplyingtoaccessanduse', Text, index=True),
Column('lineage', Text, index=True),
Column('responsiblepartyrole', Text, index=True),
Column('specificationtitle', Text, index=True),
Column('specificationdate', Text, index=True),
Column('specificationdatetype', Text, index=True),
# distribution
# links: format "name,description,protocol,url[^,,,[^,,,]]"
Column('links', Text, index=True),
)
# add extra columns that may have been passed via extra_columns
# extra_columns is a list of sqlalchemy.Column objects
if extra_columns:
LOGGER.info('Extra column definitions detected')
for extra_column in extra_columns:
LOGGER.info('Adding extra column: %s', extra_column)
records.append_column(extra_column)
records.create()
conn = dbase.connect()
if create_plpythonu_functions and not create_postgis_geometry:
if dbase.name == 'postgresql': # create plpythonu functions within db
LOGGER.info('Setting plpythonu functions')
pycsw_home = home
function_get_anytext = '''
CREATE OR REPLACE FUNCTION get_anytext(xml text)
RETURNS text
AS $$
import sys
sys.path.append('%s')
from pycsw import util
return util.get_anytext(xml)
$$ LANGUAGE plpythonu;
''' % pycsw_home
function_query_spatial = '''
CREATE OR REPLACE FUNCTION query_spatial(bbox_data_wkt text, bbox_input_wkt text, predicate text, distance text)
RETURNS text
AS $$
import sys
sys.path.append('%s')
from pycsw import util
return util.query_spatial(bbox_data_wkt, bbox_input_wkt, predicate, distance)
$$ LANGUAGE plpythonu;
''' % pycsw_home
function_update_xpath = '''
CREATE OR REPLACE FUNCTION update_xpath(nsmap text, xml text, recprops text)
RETURNS text
AS $$
import sys
sys.path.append('%s')
from pycsw import util
return util.update_xpath(nsmap, xml, recprops)
$$ LANGUAGE plpythonu;
''' % pycsw_home
function_get_geometry_area = '''
CREATE OR REPLACE FUNCTION get_geometry_area(geom text)
RETURNS text
AS $$
import sys
sys.path.append('%s')
from pycsw import util
return util.get_geometry_area(geom)
$$ LANGUAGE plpythonu;
''' % pycsw_home
function_get_spatial_overlay_rank = '''
CREATE OR REPLACE FUNCTION get_spatial_overlay_rank(target_geom text, query_geom text)
RETURNS text
AS $$
import sys
sys.path.append('%s')
from pycsw import util
return util.get_spatial_overlay_rank(target_geom, query_geom)
$$ LANGUAGE plpythonu;
''' % pycsw_home
conn.execute(function_get_anytext)
conn.execute(function_query_spatial)
conn.execute(function_update_xpath)
conn.execute(function_get_geometry_area)
conn.execute(function_get_spatial_overlay_rank)
if dbase.name == 'postgresql':
LOGGER.info('Creating PostgreSQL Free Text Search (FTS) GIN index')
tsvector_fts = "alter table %s add column anytext_tsvector tsvector" % table
conn.execute(tsvector_fts)
index_fts = "create index fts_gin_idx on %s using gin(anytext_vector)" % table
conn.execute(index_fts)
# This needs to run if records exist "UPDATE records SET anytext_tsvector = to_tsvector('english', anytext)"
trigger_fts = "create trigger ftsupdate after insert or update on %s for each row execute procedure tsvector_update_trigger('anytext_tsvector', 'pg_catalog.%s', 'anytext')" % (table, language)
conn.execute(trigger_fts)
if dbase.name == 'postgresql' and create_postgis_geometry:
# create native geometry column within db
LOGGER.info('Creating native PostGIS geometry column')
if postgis_lib_version < '2':
create_column_sql = "SELECT AddGeometryColumn('%s', '%s', 4326, 'POLYGON', 2)" % (table, postgis_geometry_column)
else:
create_column_sql = "ALTER TABLE %s ADD COLUMN %s geometry(Geometry,4326);" % (table, postgis_geometry_column)
create_insert_update_trigger_sql = '''
DROP TRIGGER IF EXISTS %(table)s_update_geometry ON %(table)s;
DROP FUNCTION IF EXISTS %(table)s_update_geometry();
CREATE FUNCTION %(table)s_update_geometry() RETURNS trigger AS $%(table)s_update_geometry$
BEGIN
IF NEW.wkt_geometry IS NULL THEN
RETURN NEW;
END IF;
NEW.%(geometry)s := ST_GeomFromText(NEW.wkt_geometry,4326);
RETURN NEW;
END;
$%(table)s_update_geometry$ LANGUAGE plpgsql;
CREATE TRIGGER %(table)s_update_geometry BEFORE INSERT OR UPDATE ON %(table)s
FOR EACH ROW EXECUTE PROCEDURE %(table)s_update_geometry();
''' % {'table': table, 'geometry': postgis_geometry_column}
create_spatial_index_sql = 'CREATE INDEX %(geometry)s_idx ON %(table)s USING GIST (%(geometry)s);' \
% {'table': table, 'geometry': postgis_geometry_column}
conn.execute(create_column_sql)
conn.execute(create_insert_update_trigger_sql)
conn.execute(create_spatial_index_sql)
def setup_db(database,
table,
home,
create_sfsql_tables=True,
create_plpythonu_functions=True,
postgis_geometry_column='wkb_geometry',
extra_columns=[],
language='english'):
"""Setup database tables and indexes"""
from sqlalchemy import Column, create_engine, Integer, MetaData, \
Table, Text
from sqlalchemy.orm import create_session
LOGGER.info('Creating database %s', database)
if database.startswith('sqlite'):
dbtype, filepath = database.split('sqlite:///')
dirname = os.path.dirname(filepath)
if not os.path.exists(dirname):
raise RuntimeError('SQLite directory %s does not exist' % dirname)
dbase = create_engine(database)
schema, table = util.sniff_table(table)
mdata = MetaData(dbase, schema=schema)
create_postgis_geometry = False
# If PostGIS 2.x detected, do not create sfsql tables.
if dbase.name == 'postgresql':
try:
dbsession = create_session(dbase)
for row in dbsession.execute('select(postgis_lib_version())'):
postgis_lib_version = row[0]
create_sfsql_tables = False
create_postgis_geometry = True
LOGGER.info('PostGIS %s detected: Skipping SFSQL tables creation' %
postgis_lib_version)
except:
pass
if create_sfsql_tables:
LOGGER.info('Creating table spatial_ref_sys')
srs = Table('spatial_ref_sys', mdata,
Column('srid', Integer, nullable=False, primary_key=True),
Column('auth_name', Text), Column('auth_srid', Integer),
Column('srtext', Text))
srs.create()
i = srs.insert()
i.execute(
srid=4326,
auth_name='EPSG',
auth_srid=4326,
srtext=
'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]'
)
LOGGER.info('Creating table geometry_columns')
geom = Table(
'geometry_columns',
mdata,
Column('f_table_catalog', Text, nullable=False),
Column('f_table_schema', Text, nullable=False),
Column('f_table_name', Text, nullable=False),
Column('f_geometry_column', Text, nullable=False),
Column('geometry_type', Integer),
Column('coord_dimension', Integer),
Column('srid', Integer, nullable=False),
Column('geometry_format', Text, nullable=False),
)
geom.create()
i = geom.insert()
i.execute(f_table_catalog='public',
f_table_schema='public',
f_table_name=table,
f_geometry_column='wkt_geometry',
geometry_type=3,
coord_dimension=2,
srid=4326,
geometry_format='WKT')
# abstract metadata information model
LOGGER.info('Creating table %s', table)
records = Table(
table,
mdata,
# core; nothing happens without these
Column('identifier', Text, primary_key=True),
Column('typename',
Text,
default='csw:Record',
nullable=False,
index=True),
Column('schema',
Text,
default='http://www.opengis.net/cat/csw/2.0.2',
nullable=False,
index=True),
Column('mdsource', Text, default='local', nullable=False, index=True),
Column('insert_date', Text, nullable=False, index=True),
Column('xml', Text, nullable=False),
Column('anytext', Text, nullable=False),
Column('language', Text, index=True),
# identification
Column('type', Text, index=True),
Column('title', Text, index=True),
Column('title_alternate', Text, index=True),
Column('abstract', Text, index=True),
Column('keywords', Text, index=True),
Column('keywordstype', Text, index=True),
Column('parentidentifier', Text, index=True),
Column('relation', Text, index=True),
Column('time_begin', Text, index=True),
Column('time_end', Text, index=True),
Column('topicategory', Text, index=True),
Column('resourcelanguage', Text, index=True),
# attribution
Column('creator', Text, index=True),
Column('publisher', Text, index=True),
Column('contributor', Text, index=True),
Column('organization', Text, index=True),
# security
Column('securityconstraints', Text, index=True),
Column('accessconstraints', Text, index=True),
Column('otherconstraints', Text, index=True),
# date
Column('date', Text, index=True),
Column('date_revision', Text, index=True),
Column('date_creation', Text, index=True),
Column('date_publication', Text, index=True),
Column('date_modified', Text, index=True),
Column('format', Text, index=True),
Column('source', Text, index=True),
# geospatial
Column('crs', Text, index=True),
Column('geodescode', Text, index=True),
Column('denominator', Text, index=True),
Column('distancevalue', Text, index=True),
Column('distanceuom', Text, index=True),
Column('wkt_geometry', Text),
# service
Column('servicetype', Text, index=True),
Column('servicetypeversion', Text, index=True),
Column('operation', Text, index=True),
Column('couplingtype', Text, index=True),
Column('operateson', Text, index=True),
Column('operatesonidentifier', Text, index=True),
Column('operatesoname', Text, index=True),
# additional
Column('degree', Text, index=True),
Column('classification', Text, index=True),
Column('conditionapplyingtoaccessanduse', Text, index=True),
Column('lineage', Text, index=True),
Column('responsiblepartyrole', Text, index=True),
Column('specificationtitle', Text, index=True),
Column('specificationdate', Text, index=True),
Column('specificationdatetype', Text, index=True),
# distribution
# links: format "name,description,protocol,url[^,,,[^,,,]]"
Column('links', Text, index=True),
)
# add extra columns that may have been passed via extra_columns
# extra_columns is a list of sqlalchemy.Column objects
if extra_columns:
LOGGER.info('Extra column definitions detected')
for extra_column in extra_columns:
LOGGER.info('Adding extra column: %s', extra_column)
records.append_column(extra_column)
records.create()
conn = dbase.connect()
if create_plpythonu_functions and not create_postgis_geometry:
if dbase.name == 'postgresql': # create plpythonu functions within db
LOGGER.info('Setting plpythonu functions')
pycsw_home = home
function_get_anytext = '''
CREATE OR REPLACE FUNCTION get_anytext(xml text)
RETURNS text
AS $$
import sys
sys.path.append('%s')
from pycsw import util
return util.get_anytext(xml)
$$ LANGUAGE plpythonu;
''' % pycsw_home
function_query_spatial = '''
CREATE OR REPLACE FUNCTION query_spatial(bbox_data_wkt text, bbox_input_wkt text, predicate text, distance text)
RETURNS text
AS $$
import sys
sys.path.append('%s')
from pycsw import util
return util.query_spatial(bbox_data_wkt, bbox_input_wkt, predicate, distance)
$$ LANGUAGE plpythonu;
''' % pycsw_home
function_update_xpath = '''
CREATE OR REPLACE FUNCTION update_xpath(nsmap text, xml text, recprops text)
RETURNS text
AS $$
import sys
sys.path.append('%s')
from pycsw import util
return util.update_xpath(nsmap, xml, recprops)
$$ LANGUAGE plpythonu;
''' % pycsw_home
function_get_geometry_area = '''
CREATE OR REPLACE FUNCTION get_geometry_area(geom text)
RETURNS text
AS $$
import sys
sys.path.append('%s')
from pycsw import util
return util.get_geometry_area(geom)
$$ LANGUAGE plpythonu;
''' % pycsw_home
function_get_spatial_overlay_rank = '''
CREATE OR REPLACE FUNCTION get_spatial_overlay_rank(target_geom text, query_geom text)
RETURNS text
AS $$
import sys
sys.path.append('%s')
from pycsw import util
return util.get_spatial_overlay_rank(target_geom, query_geom)
$$ LANGUAGE plpythonu;
''' % pycsw_home
conn.execute(function_get_anytext)
conn.execute(function_query_spatial)
conn.execute(function_update_xpath)
conn.execute(function_get_geometry_area)
conn.execute(function_get_spatial_overlay_rank)
if dbase.name == 'postgresql':
LOGGER.info('Creating PostgreSQL Free Text Search (FTS) GIN index')
tsvector_fts = "alter table %s add column anytext_tsvector tsvector" % table
conn.execute(tsvector_fts)
index_fts = "create index fts_gin_idx on %s using gin(anytext_tsvector)" % table
conn.execute(index_fts)
# This needs to run if records exist "UPDATE records SET anytext_tsvector = to_tsvector('english', anytext)"
trigger_fts = "create trigger ftsupdate before insert or update on %s for each row execute procedure tsvector_update_trigger('anytext_tsvector', 'pg_catalog.%s', 'anytext')" % (
table, language)
conn.execute(trigger_fts)
if dbase.name == 'postgresql' and create_postgis_geometry:
# create native geometry column within db
LOGGER.info('Creating native PostGIS geometry column')
if postgis_lib_version < '2':
create_column_sql = "SELECT AddGeometryColumn('%s', '%s', 4326, 'POLYGON', 2)" % (
table, postgis_geometry_column)
else:
create_column_sql = "ALTER TABLE %s ADD COLUMN %s geometry(Geometry,4326);" % (
table, postgis_geometry_column)
create_insert_update_trigger_sql = '''
DROP TRIGGER IF EXISTS %(table)s_update_geometry ON %(table)s;
DROP FUNCTION IF EXISTS %(table)s_update_geometry();
CREATE FUNCTION %(table)s_update_geometry() RETURNS trigger AS $%(table)s_update_geometry$
BEGIN
IF NEW.wkt_geometry IS NULL THEN
RETURN NEW;
END IF;
NEW.%(geometry)s := ST_GeomFromText(NEW.wkt_geometry,4326);
RETURN NEW;
END;
$%(table)s_update_geometry$ LANGUAGE plpgsql;
CREATE TRIGGER %(table)s_update_geometry BEFORE INSERT OR UPDATE ON %(table)s
FOR EACH ROW EXECUTE PROCEDURE %(table)s_update_geometry();
''' % {
'table': table,
'geometry': postgis_geometry_column
}
create_spatial_index_sql = 'CREATE INDEX %(geometry)s_idx ON %(table)s USING GIST (%(geometry)s);' \
% {'table': table, 'geometry': postgis_geometry_column}
conn.execute(create_column_sql)
conn.execute(create_insert_update_trigger_sql)
conn.execute(create_spatial_index_sql)