def insert_missing_rows(source: ETLObjectBase, destination: ETLWritableObjectBase) -> int: """ Insert rows from source that do not exist in destination and return the number of rows inserted. """ # Destination columns that are in source or are overridden. insertable_columns = _get_shared_columns( source.columns + list(destination.insert_overrides), destination.columns) sql = """ insert into {destination_object_representation} ({insert_columns}) select {select_columns} from {source_object_representation} as s left outer join {destination_object_representation} as d on {join} where {excluder} """ sql = SQL(sql).format( destination_object_representation=destination.object_representation, insert_columns=primatives.make_column_list(insertable_columns), select_columns=primatives.make_column_list( insertable_columns, "s", destination.insert_overrides), source_object_representation=source.object_representation, join=primatives.make_join_conditional(destination.key_columns, "d", "s"), excluder=primatives.make_join_excluder_conditional( destination.key_columns, "d"), ) return sql_helpers.execute_dml_sql(sql)
def identify_new_or_updated(source: ETLObjectBase, destination: ETLWritableObjectBase, staging: ETLTemporaryTable) -> int: """ Create a temporary staging table containing keys of rows in source that are new or updated from destination and return the number of rows affected. """ # Destination columns that are in source that are not overridden and are not keys. changeable_columns = _get_changeable_columns(source, destination) sql = """ create temporary table {staging_object_representation} as select {select_columns} from {source_object_representation} as s left outer join {destination_object_representation} as d on {join} where ({excluder}) or ({detect_changes}) """ sql = SQL(sql).format( staging_object_representation=staging.object_representation, select_columns=primatives.make_column_list( [c.name for c in destination.key_columns], "s"), source_object_representation=source.object_representation, destination_object_representation=destination.object_representation, join=primatives.make_join_conditional(destination.key_columns, "d", "s"), excluder=primatives.make_join_excluder_conditional( destination.key_columns, "d"), detect_changes=primatives.make_change_detector_conditional( changeable_columns, "s", "d"), ) return sql_helpers.execute_dml_sql(sql)
def _get_object_representation(self) -> Composed: """ To help us treat a dblink table like any other object, let's wrap it in a subquery. """ remote_table = SQL("{}.{}").format(Identifier(self.schema_name), Identifier(self.table_name)) remote_sql = SQL("select {columns} from {remote_table}").format( columns=primatives.make_column_list(self.columns), remote_table=remote_table) return SQL("({})").format( primatives.wrap_dblink_query(self.dblink_name, remote_sql, "r", self.columns, self.data_types))
def upsert_records_with_predicate(source: ETLObjectBase, destination: ETLWritableObjectBase, predicate: list, primary_key: str) -> int: # Destination columns that are in source or are overridden. insertable_columns = _get_shared_columns( source.columns + list(destination.insert_overrides), destination.columns) excluded = SQL(", ").join([ SQL("{dest} = {source}").format(dest=Identifier(field), source=SQL("EXCLUDED.") + Identifier(field)) for field in insertable_columns ]) upsert_sql_template = """ INSERT INTO {destination_object_representation} ({insert_columns}) SELECT {select_columns} FROM {source_object} AS {alias} ON CONFLICT ({primary_key}) DO UPDATE SET {excluded} RETURNING {primary_key} """ alias = "s" sql = SQL(upsert_sql_template).format( primary_key=Identifier(primary_key), alias=Identifier(alias), destination_object_representation=destination.object_representation, insert_columns=primatives.make_column_list(insertable_columns), select_columns=primatives.make_column_list( insertable_columns, alias, destination.insert_overrides), source_object=source.object_representation_custom_predicate(predicate), excluded=excluded, ) return sql_helpers.execute_dml_sql(sql)
def stage_table(source: ETLObjectBase, destination: ETLWritableObjectBase, staging: ETLTemporaryTable) -> int: """ Copy source table contents to staging table and return the number of rows copied. """ shared_columns = _get_shared_columns(source.columns, destination.columns) sql = """ create temporary table {staging_object_representation} as select {select_columns} from {source_object_representation} as t """ sql = SQL(sql).format( staging_object_representation=staging.object_representation, select_columns=primatives.make_column_list(shared_columns), source_object_representation=source.object_representation, ) return sql_helpers.execute_dml_sql(sql)
def test_primitives(): data_types = OrderedDict([ ("test", primatives.ColumnDefinition(name="test", data_type="int", not_nullable=True)), ("tube", primatives.ColumnDefinition(name="tube", data_type="text", not_nullable=False)), ]) single_key_column = [data_types["test"]] double_key_column = [data_types["test"], data_types["tube"]] assert cc(primatives.make_cast_column_list([], {})) == "" assert cc(primatives.make_cast_column_list( ["test"], data_types)) == 'cast("test" as int) as "test"' assert cc(primatives.make_cast_column_list( ["test"], data_types, "t")) == 'cast("t"."test" as int) as "test"' assert ( cc(primatives.make_cast_column_list(["test", "tube"], data_types, "t")) == 'cast("t"."test" as int) as "test", cast("t"."tube" as text) as "tube"' ) assert cc(primatives.make_change_detector_conditional([], "a", "b")) == "" assert (cc(primatives.make_change_detector_conditional( ["test"], "a", "b")) == '"a"."test" is distinct from "b"."test"') assert ( cc( primatives.make_change_detector_conditional(["test", "tube"], "a", "b")) == '"a"."test" is distinct from "b"."test" or "a"."tube" is distinct from "b"."tube"' ) assert cc(primatives.make_column_list([])) == "" assert cc(primatives.make_column_list(["test"])) == '"test"' assert cc(primatives.make_column_list(["test"], "t")) == '"t"."test"' assert cc(primatives.make_column_list(["test", "tube"], "t")) == '"t"."test", "t"."tube"' assert cc( primatives.make_column_list( ["test", "tube"], "t", {"tube": SQL("now()")})) == '"t"."test", now()' assert cc(primatives.make_column_setter_list([], "t")) == "" assert cc(primatives.make_column_setter_list(["test"], "t")) == '"test" = "t"."test"' assert cc(primatives.make_column_setter_list( ["test", "tube"], "t")) == '"test" = "t"."test", "tube" = "t"."tube"' assert (cc( primatives.make_column_setter_list( ["test", "tube"], "t", {"tube": SQL("now()")})) == '"test" = "t"."test", "tube" = now()') assert cc( primatives.make_composed_qualified_table_name("test")) == '"test"' assert cc(primatives.make_composed_qualified_table_name( "test", "tube")) == '"tube"."test"' assert cc( primatives.make_composed_qualified_table_name( "test", "tube", "t")) == '"tube"."test" as "t"' assert cc(primatives.make_composed_qualified_table_name( "test", alias="t")) == '"test" as "t"' assert cc(primatives.make_join_conditional([], "a", "b")) == "" assert cc(primatives.make_join_conditional( single_key_column, "a", "b")) == '"a"."test" = "b"."test"' assert ( cc(primatives.make_join_conditional(double_key_column, "a", "b")) == '"a"."test" = "b"."test" and "a"."tube" is not distinct from "b"."tube"' ) assert cc(primatives.make_join_excluder_conditional([], "t")) == "" assert cc(primatives.make_join_excluder_conditional( single_key_column, "t")) == '"t"."test" is null' assert (cc( primatives.make_join_excluder_conditional( double_key_column, "t")) == '"t"."test" is null and "t"."tube" is null') table = SQL("{}").format(Identifier("my_table")) assert cc(primatives.make_join_to_table_conditional([], "t", table)) == "" assert (cc( primatives.make_join_to_table_conditional( single_key_column, "t", table)) == '"t"."test" = "my_table"."test"') assert ( cc( primatives.make_join_to_table_conditional(double_key_column, "t", table)) == '"t"."test" = "my_table"."test" and "t"."tube" is not distinct from "my_table"."tube"' ) assert cc(primatives.make_typed_column_list([], {})) == "" assert cc(primatives.make_typed_column_list(["test"], data_types)) == '"test" int' assert cc(primatives.make_typed_column_list( ["test", "tube"], data_types)) == '"test" int, "tube" text' assert ( standardize_whitespace( cc( primatives.wrap_dblink_query("testdblink", "select now()", "r", ["test"], data_types))) == 'select "r"."test" from dblink(\'testdblink\', \'select now()\') as "r" ("test" int)' )