def get_missing_attributes(attribute_tuples, conn_cur=None):
_, cur = conn_cur or db_util.get_connection_cursor()
# Find all triples which do not already exist
query = f"""select attribs.*
from (""" + "\n union all\n".join([f"select '{username}' username, '{layername}' layername, '{attrname}' attrname" for username, layername, attrname in attribute_tuples]) + """) attribs left join
information_schema.columns c on c.table_schema = attribs.username
and c.table_name = attribs.layername
and c.column_name = attribs.attrname
where c.column_name is null"""
try:
if attribute_tuples:
cur.execute(query)
except BaseException as exc:
logger.error(f'get_missing_attributes ERROR')
raise LaymanError(7) from exc
missing_attributes = set()
rows = cur.fetchall()
for row in rows:
missing_attributes.add((row[0],
row[1],
row[2]))
return missing_attributes
def get_text_data(username, layername, conn_cur=None):
_, cur = conn_cur or db_util.get_connection_cursor()
col_names = get_text_column_names(username, layername, conn_cur=conn_cur)
if len(col_names) == 0:
return [], 0
num_features = get_number_of_features(username, layername, conn_cur=conn_cur)
if num_features == 0:
return [], 0
limit = max(100, num_features // 10)
try:
cur.execute(f"""
select {', '.join(col_names)}
from {username}.{layername}
order by ogc_fid
limit {limit}
""")
except BaseException as exc:
logger.error(f'get_text_data ERROR')
raise LaymanError(7) from exc
rows = cur.fetchall()
col_texts = defaultdict(list)
for row in rows:
for idx, col_name in enumerate(col_names):
val = row[idx]
if val is not None and len(val) > 0:
col_texts[col_name].append(val)
col_texts = [
' '.join(texts)
for _, texts in col_texts.items()
]
# print(f"result col_texts={col_texts}")
return col_texts, limit
def create_string_attributes(attribute_tuples, conn_cur=None):
_, cur = conn_cur or db_util.get_connection_cursor()
query = "\n".join([f"""ALTER TABLE {username}.{layername} ADD COLUMN {attrname} VARCHAR(1024);""" for username, layername, attrname in attribute_tuples]) + "\n COMMIT;"
try:
cur.execute(query)
except BaseException as exc:
logger.error(f'create_string_attributes ERROR')
raise LaymanError(7) from exc
def delete_workspace(workspace, conn_cur=None):
if conn_cur is None:
conn_cur = db_util.get_connection_cursor()
conn, cur = conn_cur
try:
cur.execute(f"""DROP SCHEMA IF EXISTS "{workspace}" RESTRICT""")
conn.commit()
except BaseException as exc:
logger.error(f'delete_workspace ERROR')
raise LaymanError(7) from exc
def ensure_workspace(workspace, conn_cur=None):
if conn_cur is None:
conn_cur = db_util.get_connection_cursor()
conn, cur = conn_cur
try:
cur.execute(
f"""CREATE SCHEMA IF NOT EXISTS "{workspace}" AUTHORIZATION {settings.LAYMAN_PG_USER}""")
conn.commit()
except BaseException as exc:
logger.error(f'ensure_workspace ERROR')
raise LaymanError(7) from exc
def delete_layer(workspace, layername, conn_cur=None):
if conn_cur is None:
conn_cur = db_util.get_connection_cursor()
conn, cur = conn_cur
query = f"""
DROP TABLE IF EXISTS "{workspace}"."{layername}" CASCADE
"""
try:
cur.execute(query)
conn.commit()
except BaseException as exc:
raise LaymanError(7)from exc
def get_number_of_features(username, layername, conn_cur=None):
_, cur = conn_cur or db_util.get_connection_cursor()
try:
cur.execute(f"""
select count(*)
from {username}.{layername}
""")
except BaseException as exc:
logger.error(f'get_number_of_features ERROR')
raise LaymanError(7) from exc
rows = cur.fetchall()
return rows[0][0]
def get_geometry_types(username, layername, conn_cur=None):
conn, cur = conn_cur or db_util.get_connection_cursor()
try:
sql = f"""
select distinct ST_GeometryType(wkb_geometry) as geometry_type_name
from {username}.{layername}
"""
cur.execute(sql)
except BaseException as exc:
logger.error(f'get_geometry_types ERROR')
raise LaymanError(7) from exc
rows = cur.fetchall()
conn.commit()
result = [row[0] for row in rows]
return result
def get_all_column_infos(username, layername, conn_cur=None):
_, cur = conn_cur or db_util.get_connection_cursor()
try:
cur.execute(f"""
SELECT QUOTE_IDENT(column_name) AS column_name, data_type
FROM information_schema.columns
WHERE table_schema = '{username}'
AND table_name = '{layername}'
""")
except BaseException as exc:
logger.error(f'get_all_column_names ERROR')
raise LaymanError(7) from exc
rows = cur.fetchall()
return [ColumnInfo(name=r[0], data_type=r[1]) for r in rows]
def get_workspaces(conn_cur=None):
if conn_cur is None:
conn_cur = db_util.get_connection_cursor()
_, cur = conn_cur
try:
cur.execute(f"""select schema_name
from information_schema.schemata
where schema_name NOT IN ('{"', '".join(settings.PG_NON_USER_SCHEMAS)}\
') AND schema_owner = '{settings.LAYMAN_PG_USER}'""")
except BaseException as exc:
logger.error(f'get_workspaces ERROR')
raise LaymanError(7) from exc
rows = cur.fetchall()
return [r[0] for r in rows]
def get_text_column_names(username, layername, conn_cur=None):
_, cur = conn_cur or db_util.get_connection_cursor()
try:
cur.execute(f"""
SELECT QUOTE_IDENT(column_name) AS column_name
FROM information_schema.columns
WHERE table_schema = '{username}'
AND table_name = '{layername}'
AND data_type IN ('character varying', 'varchar', 'character', 'char', 'text')
""")
except BaseException as exc:
logger.error(f'get_text_column_names ERROR')
raise LaymanError(7) from exc
rows = cur.fetchall()
return [r[0] for r in rows]
def check_new_layername(workspace, layername, conn_cur=None):
if conn_cur is None:
conn_cur = db_util.get_connection_cursor()
_, cur = conn_cur
# DB table name conflicts
try:
cur.execute(f"""SELECT n.nspname AS schemaname, c.relname, c.relkind
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname IN ('{workspace}', '{settings.PG_POSTGIS_SCHEMA}') AND c.relname='{layername}'""")
except BaseException as exc:
logger.error(f'check_new_layername ERROR')
raise LaymanError(7) from exc
rows = cur.fetchall()
if len(rows) > 0:
raise LaymanError(9, {'db_object_name': layername})
def get_layer_info(workspace, layername, conn_cur=None):
if conn_cur is None:
conn_cur = db_util.get_connection_cursor()
_, cur = conn_cur
try:
cur.execute(f"""
SELECT schemaname, tablename, tableowner
FROM pg_tables
WHERE schemaname = '{workspace}'
AND tablename = '{layername}'
AND tableowner = '{settings.LAYMAN_PG_USER}'
""")
except BaseException as exc:
raise LaymanError(7) from exc
rows = cur.fetchall()
result = {}
if len(rows) > 0:
result = {
'db_table': {
'name': layername,
},
}
return result
def get_most_frequent_lower_distance2(workspace, layername, conn_cur=None):
_, cur = conn_cur or db_util.get_connection_cursor()
query = get_most_frequent_lower_distance_query(workspace, layername,
['st_area', 'Box2D'])
# print(f"\nget_most_frequent_lower_distance v2\nusername={username}, layername={layername}")
# print(query)
try:
cur.execute(query)
except BaseException as exc:
logger.error(f'get_most_frequent_lower_distance2 ERROR')
raise LaymanError(7) from exc
rows = cur.fetchall()
# for row in rows:
# print(f"row={row}")
result = None
if len(rows) > 0:
distance, freq, num_distances = rows[0]
if freq / num_distances > 0.03:
result = distance
return result
def ensure_attributes(attribute_tuples):
conn_cur = db_util.get_connection_cursor()
missing_attributes = get_missing_attributes(attribute_tuples, conn_cur)
if missing_attributes:
create_string_attributes(missing_attributes, conn_cur)
return missing_attributes
