clean up relation construction

[dataders]

we should try and standardize on a convention of using {{ relation }} wherever possible over these variants:

• {{ relation.schema }}.{{ relation.identifier }}
• to_relation.schema ~ '.' ~ to_relation.identifier

@jtcohen6, how do we disable by default database being included when calling {{ relation }}? Do we have create a child of the Relation class?

dbt-sqlserver/dbt/include/sqlserver/macros/adapters.sql

Line 49 in 4b3f7bc

EXEC('CREATE SCHEMA {{ relation.without_identifier().schema }}')

dbt-sqlserver/dbt/include/sqlserver/macros/adapters.sql

Line 111 in 4b3f7bc

create view {{ relation.schema }}.{{ relation.identifier }} as

dbt-sqlserver/dbt/include/sqlserver/macros/adapters.sql

Line 118 in 4b3f7bc

EXEC sp_rename '{{ from_relation.schema }}.{{ from_relation.identifier }}', '{{ to_relation.identifier }}'

dbt-sqlserver/dbt/include/sqlserver/macros/adapters.sql

Line 130 in 4b3f7bc

{%- set full_relation = relation.schema ~ '.' ~ relation.identifier -%}

dbt-sqlserver/dbt/include/sqlserver/macros/adapters.sql

Lines 152 to 154 in 4b3f7bc

	EXEC('create view {{ tmp_relation.schema }}.{{ tmp_relation.identifier }} as
	{{ temp_view_sql }}
	');

dbt-sqlserver/dbt/include/sqlserver/macros/adapters.sql

Lines 168 to 169 in 4b3f7bc

	{%- set full_to_relation = to_relation.schema ~ '.' ~ to_relation.identifier -%}
	{%- set full_from_relation = from_relation.schema ~ '.' ~ from_relation.identifier -%}

[jtcohen6]

+1 to using {{ relation }} instead of concatenating.

Check out how we did this same thing in dbt-spark:

• SparkIncludePolicy, which turns off database
• That policy is then set within SparkRelation
• Set Relation = SparkRelation within SparkAdapter

[dataders]

beautiful. I'll open a PR.

maybe being extra here... but how about concatenating a relation by _ instead of .? We do this for index naming. Looking for a cleaner way than the below.

dbt-sqlserver/dbt/include/sqlserver/macros/adapters.sql

Lines 116 to 139 in 4b3f7bc

	{% macro sqlserver__rename_relation(from_relation, to_relation) -%}
	{% call statement('rename_relation') -%}
	EXEC sp_rename '{{ from_relation.schema }}.{{ from_relation.identifier }}', '{{ to_relation.identifier }}'
	IF EXISTS(
	SELECT *
	FROM sys.indexes
	WHERE name='{{ from_relation.schema }}_{{ from_relation.identifier }}_cci' and object_id = OBJECT_ID('{{ from_relation.schema }}.{{ to_relation.identifier }}'))
	EXEC sp_rename N'{{ from_relation.schema }}.{{ to_relation.identifier }}.{{ from_relation.schema }}_{{ from_relation.identifier }}_cci', N'{{ from_relation.schema }}_{{ to_relation.identifier }}_cci', N'INDEX'
	{%- endcall %}
	{% endmacro %}
	{% macro sqlserver__create_clustered_columnstore_index(relation) -%}
	{%- set cci_name = relation.schema ~ '_' ~ relation.identifier ~ '_cci' -%}
	{%- set relation_name = relation.schema ~ '_' ~ relation.identifier -%}
	{%- set full_relation = relation.schema ~ '.' ~ relation.identifier -%}
	if EXISTS (
	SELECT * FROM
	sys.indexes WHERE name = '{{cci_name}}'
	AND object_id=object_id('{{relation_name}}')
	)
	DROP index {{full_relation}}.{{cci_name}}
	CREATE CLUSTERED COLUMNSTORE INDEX {{cci_name}}
	ON {{full_relation}}
	{% endmacro %}

potential solutions

1. duplicate BaseRelation.render(), but with '_'.join() instead of '.'.join(). Seems like overkill to me
2. just quote the rendered relation with square brackets so that the . is ignored? @mikaelene, can you look up an index name that has a . in it? I'm pretty sure you can
3. just do something like below. not sure if you can call .replace() on relation directly like that though.

   {% set cci_name = relation.replace(".", "_") ~ "_cci" %}

[jtcohen6]

Option 1 doesn't sound crazy to me. You could define a special method on SQLServerRelation, something like index_render, and then call {{ full_relation.index_render() }} as needed.

It's fewer moving pieces if you handle in Jinja or SQL, of course.

[sdebruyn]

Should happen together with #325