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