def test_force_schema_quoted_name_w_dot_case_sensitive(self):
metadata = MetaData()
tbl = Table(
"test",
metadata,
Column("id", Integer, primary_key=True),
schema=quoted_name("Foo.dbo", True),
)
self.assert_compile(select([tbl]),
"SELECT [Foo.dbo].test.id FROM [Foo.dbo].test")
def upgrade_2(session):
"""Version 2 upgrade
This upgrade adds indices for the metadata.tags, metadata.requirements fields in the
results/runs tables.
It is equivalent to running the following SQL statements:
CREATE INDEX IF NOT EXISTS ix_runs_tags ON runs USING gin ((data->'tags'));
CREATE INDEX IF NOT EXISTS ix_results_tags ON results USING gin ((data->'tags'));
CREATE INDEX IF NOT EXISTS ix_results_requirements ON results
USING gin ((data->'requirements'));
"""
TABLES = ["runs", "results"]
engine = session.get_bind()
op = get_upgrade_op(session)
metadata = MetaData()
metadata.reflect(bind=engine)
if engine.url.get_dialect().name == "postgresql":
for table_name in TABLES:
tags_index_name = f"ix_{table_name}_tags"
reqs_index_name = f"ix_{table_name}_requirements"
table = metadata.tables.get(table_name)
if (table_name in metadata.tables and table is not None
and tags_index_name
not in [idx.name for idx in table.indexes]):
op.create_index(
tags_index_name,
table_name,
[quoted_name("(data->'tags')", False)],
postgresql_using="gin",
)
if table_name == "results" and reqs_index_name not in [
idx.name for idx in table.indexes
]:
op.create_index(
reqs_index_name,
table_name,
[quoted_name("(data->'requirements')", False)],
postgresql_using="gin",
)
def test_force_schema_quoted_name_w_dot_case_sensitive(self):
metadata = MetaData()
tbl = Table(
'test', metadata,
Column('id', Integer, primary_key=True),
schema=quoted_name("Foo.dbo", True)
)
self.assert_compile(
select([tbl]),
"SELECT [Foo.dbo].test.id FROM [Foo.dbo].test"
)
def test_force_schema_quoted_w_dot_case_insensitive(self):
metadata = MetaData()
tbl = Table(
"test",
metadata,
Column("id", Integer, primary_key=True),
schema=quoted_name("foo.dbo", True),
)
self.assert_compile(
select([tbl]), "SELECT [foo.dbo].test.id FROM [foo.dbo].test"
)
def test_literal_column_label_embedded_select_diffname_explcit_quote(self):
col = sql.literal_column("NEEDS QUOTES").label(
quoted_name("NEEDS QUOTES_", True))
with testing.expect_deprecated(
r"The SelectBase.select\(\) method is deprecated"):
self.assert_compile(
select([col]).select(),
'SELECT anon_1."NEEDS QUOTES_" FROM '
'(SELECT NEEDS QUOTES AS "NEEDS QUOTES_") AS anon_1',
)
def test_force_schema_quoted_w_dot_case_insensitive(self):
metadata = MetaData()
tbl = Table(
'test', metadata,
Column('id', Integer, primary_key=True),
schema=quoted_name("foo.dbo", True)
)
self.assert_compile(
select([tbl]),
"SELECT [foo.dbo].test.id FROM [foo.dbo].test"
)
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 _build_select(self, query: Query) -> sa.select:
selects = []
if not query.selects and not query.joins:
# No explicit selects, no joins, return entire table of columns
return sa.select([query.table])
elif not query.selects and query.joins:
selects.extend(query.table.columns)
for join in query.joins:
for column in join.table.columns:
selects.append(column.label(quoted_name(join.alias + '__' + column.name, True)))
elif query.selects:
# Explicit selects (can be on main table or joined relations)
for select in query.selects:
column = self._column(select, query)
if column.alias:
selects.append(column.sacol.label(quoted_name(column.alias, True)))
else:
selects.append(column.sacol)
# Return SQLAlchemy .select() statment with above columns
return sa.select(selects)
def make_label_compatible(cls, label):
"""
Conditionally mutate and/or quote a sql column/expression label. If
force_column_alias_quotes is set to True, return the label as a
sqlalchemy.sql.elements.quoted_name object to ensure that the select query
and query results have same case. Otherwise return the mutated label as a
regular string. If maxmimum supported column name length is exceeded,
generate a truncated label by calling truncate_label().
"""
label_mutated = cls.mutate_label(label)
if cls.max_column_name_length and len(label_mutated) > cls.max_column_name_length:
label_mutated = cls.truncate_label(label)
if cls.force_column_alias_quotes:
label_mutated = quoted_name(label_mutated, True)
return label_mutated
def upgrade():
conn = op.get_bind()
conn.execute("PRAGMA legacy_alter_table=ON")
# Schema migration
op.rename_table("sources", "sources_tmp")
# Add UUID column.
op.add_column("sources_tmp", sa.Column("uuid", sa.String(length=36)))
# Add UUIDs to sources_tmp table.
sources = conn.execute(sa.text("SELECT * FROM sources_tmp")).fetchall()
for source in sources:
conn.execute(
sa.text("""UPDATE sources_tmp SET uuid=:source_uuid WHERE
id=:id""").bindparams(source_uuid=str(uuid.uuid4()),
id=source.id))
# Now create new table with unique constraint applied.
op.create_table(
quoted_name("sources", quote=False),
sa.Column("id", sa.Integer(), nullable=False),
sa.Column("uuid", sa.String(length=36), nullable=False),
sa.Column("filesystem_id", sa.String(length=96), nullable=True),
sa.Column("journalist_designation",
sa.String(length=255),
nullable=False),
sa.Column("flagged", sa.Boolean(), nullable=True),
sa.Column("last_updated", sa.DateTime(), nullable=True),
sa.Column("pending", sa.Boolean(), nullable=True),
sa.Column("interaction_count", sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("uuid"),
sa.UniqueConstraint("filesystem_id"),
)
# Data Migration: move all sources into the new table.
conn.execute("""
INSERT INTO sources
SELECT id, uuid, filesystem_id, journalist_designation, flagged,
last_updated, pending, interaction_count
FROM sources_tmp
""")
# Now delete the old table.
op.drop_table("sources_tmp")
def test_quoted_name_label(engine_testaccount):
test_cases = [
# quote name
{"label": quoted_name('alias', True),
"output": 'SELECT colname AS "alias" \nFROM abc GROUP BY colname'},
# not quote label
{"label": 'alias',
"output": 'SELECT colname AS alias \nFROM abc GROUP BY colname'},
# not quote mixed case label
{"label": 'Alias',
"output": 'SELECT colname AS "Alias" \nFROM abc GROUP BY colname'},
]
for t in test_cases:
col = column('colname').label(t["label"])
sel_from_tbl = select([col]).group_by(col).select_from(table('abc'))
compiled_result = sel_from_tbl.compile(engine_testaccount)
assert str(compiled_result) == t["output"]
class myfunc(GenericFunction):
name = quoted_name("NotMyFunc", quote=True)
def qname(self):
return sql.quoted_name(self.schema, True) + '.' + sql.quoted_name(
self.table, True)
def _build_orm_queries(self, method: str) -> List:
# Different than the single _build_query in the DB Builder
# This one is for ORM only and build multiple DB queries from one ORM query.
queries = []
# First query
query = self.query.copy()
self._build_orm_relations(query)
# Add all columns from main model
query.selects = self.entity.selectable_columns(show_writeonly=self.query.show_writeonly)
# Add all selects where any nested relation is NOT a *Many
relation: _Relation
for relation in query.relations.values():
if not relation.contains_many(query.relations):
# Don't use the relation.entity table to get columns, use the join aliased table
table = self._get_join_table(query, alias=relation.name)
columns = relation.entity.selectable_columns(table, show_writeonly=self.query.show_writeonly)
for column in columns:
query.selects.append(column.label(quoted_name(relation.name + '__' + column.name, True)))
# Build first query
saquery = None
if query.table is not None:
query, saquery = self._build_query(method, query)
queries.append({
'name': 'main',
'query': query,
'saquery': saquery,
'sql': str(saquery) if saquery is not None else '',
})
# So we have our first query perfect
# Now we need to build a second or more queries for all *Many relations
relation: Relation
for relation in query.relations.values():
# Only handle *Many relations
if not relation.is_many(): continue
# Relation __ name converted to dot name
rel_dot = relation.name.replace('__', '.')
# New secondary relation query
query2 = self.query.copy()
# Build ORM Relations but force HasMany joins to INNER JOIN
self._build_orm_relations(query2)
# Only if Many-To-Many add in the main tables pivot ID
if type(relation) == BelongsToMany or type(relation) == MorphToMany:
# Bug found while grabbing the table by name. What if we join the same table twice (though different tables of course)
# There will be 2 joins with the same table name and _get_join_table will grab the first one in error.
# Instead we need to use the table alias name + __pivot to grab the proper unique table
#join_table = self._get_join_table(query2, relation.join_tablename) # No, using table name will clash if joining multiples of the same table,
join_table = self._get_join_table(query2, alias=relation.name + '__pivot') # Relation.name is the alias name which should match the unique join table even if multiples
query2.selects.append(
getattr(join_table.c, relation.left_key).label(
quoted_name(relation.name + '__' + relation.left_key, True)
)
)
# Set selects to only those in the related table
table = self._get_join_table(query2, alias=relation.name)
columns = relation.entity.selectable_columns(table, show_writeonly=self.query.show_writeonly)
for column in columns:
query2.selects.append(column.label(quoted_name(relation.name + '__' + column.name, True)))
# Add in selects for any *One sub_relations
for sub_relation in query2.relations.values():
if relation.name + '__' not in sub_relation.name: continue
if sub_relation.contains_many(query2.relations, skip=relation.name.split('__')): continue
table = self._get_join_table(query2, alias=sub_relation.name)
columns = sub_relation.entity.selectable_columns(table, show_writeonly=self.query.show_writeonly)
for column in columns:
query2.selects.append(column.label(quoted_name(sub_relation.name + '__' + column.name, True)))
# Remove any wheres for this relation. Why? Because the relation
# where is applied to the main query not to relations. We still
# show all relations for any main query item
# If you want to filter relations, use .filter() instead
new_wheres = []
for where in query2.wheres:
# This also matches any sub-relations uder the where and removes those too
# BROKEN if using SQLAlchemy binary expressions
# .where(section.name, 'Production')
if '.' in where[0]:
# Perhaps the ORM should not be "hybrid" and always use string dotnotation?
where_entity = '.'.join(where[0].split('.')[0:-1]).lower()
if rel_dot[0:len(where_entity)] == where_entity:
# Found where for this relation or a sub-relation
# Notice I continue, therefore I am removing this relations where
continue
new_wheres.append(where)
query2.wheres = new_wheres
# Add .filter() as .where()
query2.wheres.extend(query2.filters)
# Add .or_filter() as .or_where()
query2.or_wheres.extend(query2.or_filters)
# Add where to show only joined record that were found
# Cant use INNER JOIN instead because it would limit further sub many-to-many
query2.wheres.append((relation.name + '.' + relation.entity.pk, '!=', None))
# Swap .sort() that apply to this relation as an order_by
# This WIPES out any .order_by as they do not apply to relation queries
new_sorts = []
for sort in query2.sort:
(sort_column, sort_order) = sort
if '.'.join(sort_column.split('.')[0:-1]).lower() == rel_dot.lower():
# Found sort for this relation
new_sorts.append(sort)
query2.sort = new_sorts
query2.order_by = query2.sort
# Build secondary relation query
query2, saquery2 = self._build_query(method, query2)
queries.append({
'name': relation.name,
'query': query2,
'saquery': saquery2,
'sql': str(saquery2),
})
# Return all queries
return queries
class email_local_part(GenericFunction):
type = Text
name = quoted_name(QUALIFIED_EMAIL_LOCAL_PART, False)
identifier = EMAIL_LOCAL_PART
class email_domain_name(GenericFunction):
type = Text
name = quoted_name(QUALIFIED_EMAIL_DOMAIN_NAME, False)
identifier = EMAIL_DOMAIN_NAME
