def __init__(self, desc, srid=4326, geometry_type='GEOMETRY'):
assert isinstance(desc, (basestring, buffer))
self.desc = desc
self.srid = srid
self.geometry_type = geometry_type
Function.__init__(self, "")
def __init__(self, desc, srid=4326, geometry_type='GEOMETRY'):
assert isinstance(desc, (basestring, buffer))
self.desc = desc
self.srid = srid
self.geometry_type = geometry_type
Function.__init__(self, "")
def __init__(self, desc, srid=4326, geometry_type='GEOMETRY'):
assert isinstance(
desc, (str, bytes)), "Expected str/bytes for {!r}, got {}".format(
desc, type(desc))
self.desc = desc
self.srid = srid
self.geometry_type = geometry_type
Function.__init__(self, "")
def _get_function(element, compiler, params, within_column_clause):
"""For elements of type BaseFunction, the database specific function data
is looked up and a executable sqlalchemy.sql.expression.Function object
is returned.
"""
from geoalchemy.dialect import DialectManager
database_dialect = DialectManager.get_spatial_dialect(compiler.dialect)
for kls in element.__class__.__mro__:
try:
function_data = database_dialect.get_function(kls)
except KeyError:
continue
if function_data is None:
raise Exception("Unsupported function for this dialect")
if isinstance(function_data, list):
"""if we have a list of function names, create cascaded Function objects
for all function names in the list::
['TO_CHAR', 'SDO_UTIL.TO_WKTGEOMETRY'] --> TO_CHAR(SDO_UTIL.TO_WKTGEOMETRY(..))
"""
function = None
for name in reversed(function_data):
packages = name.split('.')
if function is None:
"""for the innermost function use the passed-in parameters as argument,
otherwise use the prior created function
"""
args = params
else:
args = [function]
function = Function(packages.pop(-1), packagenames=packages, *args)
return function
elif isinstance(function_data, types.FunctionType):
"""if we have a function, call this function with the parameters and return the
created Function object
"""
if hasattr(element, 'flags'):
# when element is a BaseFunction
flags = element.flags
else:
flags = {}
return function_data(params, within_column_clause, **flags)
else:
packages = function_data.split('.')
return Function(packages.pop(-1), packagenames=packages, *params)
def sql_write(self, bindvalue):
# 2. Writing - SQL layer - wrap in call to ST_GeomFromWKB to convert WKB to MySQL binary.
return Function("ST_GeomFromWKB",
bindvalue,
self.crs_id,
self.AXIS_ORDER,
type_=self)
def search_for_seriesname(name, nid):
similarity = Function('similarity', AlternateNames.cleanname, (name))
query = select([
AlternateNames.series, AlternateNames.cleanname, AlternateNames.name,
similarity
],
from_obj=[AlternateNames],
order_by=desc(similarity)).where(
and_(
AlternateNames.cleanname.op("%%")(name),
AlternateNames.series != nid)).limit(10)
# print("Query:", query)
results = db.session.execute(query).fetchall()
data = {}
for mgid, mcleanname, mname, similarity in results:
if not mgid in data:
data[mgid] = []
distance = lv.distance(name, mcleanname)
data[mgid].append((mgid, mcleanname, mname, distance))
return data
def title_search(searchterm, page=1):
searchtermclean = bleach.clean(searchterm, strip=True)
searchterm = nt.prepFilenameForMatching(searchtermclean)
if not searchterm:
return render_template(
'not-implemented-yet.html',
message=
'No search term entered (or search term collapsed down to nothing).'
)
similarity = Function('similarity', AlternateNames.cleanname, (searchterm))
query = select([
AlternateNames.series, AlternateNames.cleanname, AlternateNames.name,
similarity
],
from_obj=[AlternateNames],
order_by=desc(similarity)).where(
or_(
AlternateNames.cleanname.op("%%")(searchterm),
AlternateNames.cleanname.like(searchterm +
"%%"))).limit(50)
results = db.session.execute(query).fetchall()
data = collections.OrderedDict()
uid = g.user.get_id()
for result in results:
dbid = result[0]
if not dbid in data:
data[dbid] = {}
data[dbid]['row'] = Series.query.filter(Series.id == dbid).one()
if uid:
data[dbid]['watch'] = Watches \
.query \
.filter(Watches.series_id==dbid) \
.filter(Watches.user_id==uid) \
.scalar()
else:
data[dbid]['watch'] = []
data[dbid]['results'] = []
# We only care about relative ordering, and
# since we're ordered when we iterate, if we
# just append here, things will stay in the correct
# order.
data[dbid]['results'].append(result)
# print(results)
# print(data)
return render_template(
'text-search.html',
results=data,
name_key="tag",
page_url_prefix='tag-id',
searchTarget="Titles",
searchValue=searchtermclean,
title='Search for \'{name}\''.format(name=searchtermclean))
def title_search(searchterm, page=1):
searchtermclean = bleach.clean(searchterm, strip=True)
# searchterm = nt.prepFilenameForMatching(searchtermclean)
searchterm = searchtermclean
if not searchterm:
return render_template(
'not-implemented-yet.html',
message=
'No search term entered (or search term collapsed down to nothing).'
)
similarity = Function('similarity', Story.title, (searchterm))
query = select([Story.id, Story.title, similarity],
from_obj=[Story],
order_by=desc(similarity)).where(
or_(
Story.title.op("%%")(searchterm),
Story.title.like(searchterm + "%%"))).limit(50)
results = db.session.execute(query).fetchall()
data = collections.OrderedDict()
return render_template(
'text-search.html',
results=data,
name_key="tag",
page_url_prefix='tag-id',
searchTarget="Titles",
searchValue=searchtermclean,
title='Search for \'{name}\''.format(name=searchtermclean))
def generate_similarity_query(searchterm, cols=None):
searchtermclean = bleach.clean(searchterm, strip=True)
searchtermprocessed = nt.prepFilenameForMatching(searchtermclean)
similarity = Function('similarity', AlternateNames.cleanname, (searchtermprocessed))
if cols is None:
cols = [AlternateNames.series, AlternateNames.cleanname, AlternateNames.name, similarity]
if not searchterm:
return None, None
query = select(
cols,
from_obj=[AlternateNames],
order_by=desc(similarity)
).where(
or_(
AlternateNames.cleanname.op("%%")(searchtermprocessed),
AlternateNames.cleanname.like(searchtermprocessed + "%%")
)
).limit(
50
)
return query, searchtermprocessed
def sql_write(self, bindvalue):
# 2. Writing - SQL layer - wrap in call to STGeomFromWKB to convert WKB to MS binary.
# POINT EMPTY is handled specially since it doesn't have a WKB value the SQL Server accepts.
return sa.case(
(
sa.cast(bindvalue, sa.VARBINARY)
== sa.literal_column(self.EMPTY_POINT_WKB),
Function(
quoted_name("geometry::STGeomFromText", False),
"POINT EMPTY",
self.crs_id,
type_=self,
),
),
else_=Function(
quoted_name("geometry::STGeomFromWKB", False),
bindvalue,
self.crs_id,
type_=self,
),
)
def search_for_tlname(name, nid, alts):
# print("Searching:", (nid, name))
similarity = Function('similarity', AlternateTranslatorNames.cleanname,
(name))
query = select([
AlternateTranslatorNames.group, AlternateTranslatorNames.cleanname,
AlternateTranslatorNames.name, similarity
],
from_obj=[AlternateTranslatorNames],
order_by=desc(similarity)).where(
and_(
AlternateTranslatorNames.cleanname.op("%%")(name),
AlternateTranslatorNames.group != nid)).limit(10)
results = db.session.execute(query).fetchall()
data = {}
for mgid, mcleanname, mname, similarity in results:
if not mgid in data:
data[mgid] = []
distance = lv.distance(name, mcleanname)
data[mgid].append((mgid, mcleanname, mname, distance))
for mgid, mcleanname, mname, similarity in results:
if not mgid in data:
data[mgid] = []
distance = lv.distance(mcleanname, name)
data[mgid].append((mgid, mcleanname, mname, distance))
for oid, altn, caltn in alts:
if not altn:
continue
if name.lower().startswith(altn.lower()) or altn.lower().startswith(
name.lower()) and altn and name:
if not oid in data:
data[oid] = []
# rows = get_rows(altn)
# for row in rows:
# data[oid].append(row)
return data
def add_similarity_query(searchterm, query):
searchtermclean = bleach.clean(searchterm, strip=True)
searchtermprocessed = nt.prepFilenameForMatching(searchtermclean)
similarity = Function('similarity', AlternateNames.cleanname, (searchtermprocessed))
if not searchterm:
return None, None
query = query.filter(
Series.id.in_(
db.session.query(AlternateNames.series).filter(
or_(
AlternateNames.cleanname.op("%%")(searchtermprocessed),
AlternateNames.cleanname.like(searchtermprocessed + "%%")
)
).order_by(
desc(similarity)
)
)
)
return query
def sql_read(self, column):
return Function("to_char", column, "HH24:MI:SS", type_=self)
def sql_read(self, column):
return Function("to_char", column, "YYYY-MM-DD", type_=self)
def sql_read(self, column):
return Function("FORMAT", column, "yyyy-MM-ddTHH:mm:ss", type_=self)
def sql_read(self, column):
return Function("FORMAT", column, r"hh\:mm\:ss", type_=self)
def get_term_query(self, column_alias, column_name, value, default_op=or_):
tq = AlchemyMixin.get_tsquery(value, default_op)
if column_name is None:
column_name = column_alias.name
return Function('to_tsvector', column_name).op('@@')(tq)
def sql_read(self, column):
# 3. Reading - SQL layer - wrap in call to ST_AsBinary() to convert MySQL binary to WKB.
return Function("ST_AsBinary", column, self.AXIS_ORDER, type_=self)
def __init__(self, desc):
self.desc = desc
Function.__init__(self, "", desc)
def __init__(self, *arguments, **kwargs):
self.arguments = arguments
self.flags = kwargs.copy()
Function.__init__(self, self.__class__.__name__, **kwargs)
def sql_read(self, column):
# Reading - SQL layer - convert date to string in ISO8601.
# https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
return Function("DATE_FORMAT", column, "%Y-%m-%d", type_=self)
def __init__(self, desc):
self.desc = desc
Function.__init__(self, "", desc)
def sql_read(self, column):
return Function("to_char",
column,
'YYYY-MM-DD"T"HH24:MI:SS',
type_=self)
def sql_read(self, col):
# 2. Reading - SQL layer - convert timestamp to string in ISO8601 with Z as the timezone specifier.
# https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
return Function("DATE_FORMAT", col, "%Y-%m-%dT%H:%i:%S", type_=self)
def sql_read(self, col):
# Reading - SQL layer - convert timestamp to string in ISO8601.
# https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
return Function("DATE_FORMAT", col, "%H:%i:%S", type_=self)
def __init__(self, desc):
assert isinstance(desc, basestring)
self.desc = desc
Function.__init__(self, "")
def __init__(self, *arguments, **kwargs):
self.arguments = arguments
self.flags = kwargs.copy()
Function.__init__(self, self.__class__.__name__, **kwargs)
