SQL Templates Internals


SQL Template engine can be looked at as being with a small brain an a big heart. Where brain is sfnc_Database_SqlTemplate_FragmentParse and heart is sstp_Database_SqlTemplate_ExpandFragment. Brain is simple yet powerful. It is a text parser. Heart on the other hand is where all the pumping is done. It is recursive and it uses sp_executesql inside. Also let us not forget sfnc_Database_SqlTemplate_GetFragment, kidney of SQL Templates. It filters out unnecessary SQL fragments through a matching mechanism.

Fragment Parse

As mentioned simple text parsing is done inside of FragmentParse. Dollar sign ($) is used to mark parts of text to cut out and processed by SQL Template engine. Since we are not interested in what is between the items, parsing algorithm just uses CHARINDEX function to get next location of dollar in order to cut the item out. If cut out part is wrapped with single dollar sign then it is considered simple item:
$simple$
If cut out part is wrapped by double dollar sign it is considered an instruction:
$$instruction$$
FragmentParse function does not know how instruction is different from regular item. It just returns tabular data about term names parsed out and positions in text. So if you had simple SQL fragment text like:
$$nothing todo$$
$a$ + $b$ = $c$
the parse result would be:

term_name is_error offset original is_instruction args
nothing 1 $$nothing todo$$ todo
a 17 $a$
b 22 $b$
c 28 $c$

Parsing is smart enough to tell that there is an error if you missed a dollar. So if you had SQL fragment text like this:
$$nothing todo$$
$a$$ + $b$ = $c$

the parse result would be:

term_name is_error offset original is_instruction args
nothing 1 $$nothing todo$$ todo
a 17 $a$

22 $ + $ +

27 $ = $ =
error 33 $... Error at position 34. Item started but never ended

Now we have an unintended result where $ + $ and $ = $ were recognized as items and the last dollar became redundant. sfnc_Database_SqlTemplate_FragmentParse works in a simple predictable way. It involves no recursion so execution time is linear. Since the function is a table valued function it can and is used inside views. most notable is sviw_Database_SqlTemplate_FragmentTerm. You can parse all your fragments in no time. No benchmark was done but it is fast. Parsing results are then used inside ExpandFragment procedure. But next we are going to talk about kidney of SQL Templates. sfnc_Database_SqlTemplate_GetFragment function.

Get Fragment

Get Fragment can actually be considered a fitting function. It evaluates all possible candidates by name and picks the one that fits the most based on the cotext. This function takes the context xml and extracts matching patterns as a tabular data. So if context comes in like:

<context>
  <project name="TestProject" />
  <match pattern="table.name.stbl_Code_File" />
  <match pattern="table.usesyscolnames.1" />
  <match pattern="table.column.PrimKey" />
  <match pattern="table.column.FileData" />
  <match pattern="table.column.IsCompressed" />
  <fragment name="tablename" value="stbl_Code_File" />
</context>

Matching table @_context_match will look like this:

pattern
table.name.stbl_Code_File
table.usesyscolnames.1
table.column.PrimKey
table.column.FileData
table.column.IsCompressed

As you can see context matching is used in a very straight forward way. When it is in a tabular form it is then used in simple join. Similar thing is being done with projects specified, so each project node will result in more projects included. In context specified above we include only TestProject. As you can see all the logic of matching is contained in a matching_scripts common table expression. Let us look at it closer:

select f.ID, f.Status, COUNT(*) as matches
	from dbo.sviw_Database_SqlTemplate_FragmentTerm as f
	inner join @_porjects as p
		on p.Name = f.Name
	left join @_context_match as m
		on m.pattern LIKE f.args
	where f.FragmentName = @name        
        and f.term_name = 'match'		
        and f.is_instruction = 1		
        and f.is_error = 0
	group by f.ID, f.Status
	having COUNT(*) = COUNT(pattern)

Remember the fragment parsing? here it is being used in a form of view mentioned before. This is the first place parsing becomes usefull, by giving information about all match instructions in fragments of included projects. Note that good old LIKE is being used here. What is not to like. Then it is all about match counting and picking the one that has the biggest match count. So if you had 2 SQL fragments with identical name like:
$$match mexican$$
just a mexican...

and:
$$match zoro$$
Zoro!

and if you had a context like this:

<context> 
  <match pattern="zoro" />
</context>

The second fragmnt would be picked wining by 1 point. Zoro! would be the result. Here in our SQL fragments we use simple phrazes. But actually nothing is stopping form fully employing LIKE pattern matching. z%, zo%, zor% and even %o_o% would also match supplied context.

Expand Fragment

All the serious pumping is done in Expand Fragment procedure. Bellow is a diagram showing what is happening inside of this procedure. To the left are all temporary tables (shown in blue). On top are input arguments of a procedure: context and fragment (shown in green). Bellow is the output: result script (shown in green). Trace and error handling is not shown to keep the diagram simple.

As mentioned before it is recursive. procedure calls itself in step 6 and 12. Now lets walk throug. Once you understand didtinct parts it is easy to understand the full picture.

0. copy

This operation is straight forward, yet critical. In SQL templates we are writing SQL and punching holes to insert another SQL. In other words we are writing dynamic SQL. So here we start with original fragment text and as mentioned in the comment, it is going to be processed in a loop later on.

--NOTE: will be filled up in a while loop
SELECT @result_script = @fragment;

1. xml nodes

XML nodes call is very expensive. But SQL templates currently relly on context being XML. As we have seen plain text parsing can be extremely fast and it for sure could rival XML processing speed, specifically when spliting it. But here it is, the most expensive part of SQL template Engine.

-- NOTE: Huge performance hit here. XML is tremendously expensive. 
-- TODO: evaluate option to use structured string and parse it instead.
INSERT INTO @_context_fragments 
	(name, value, is_local)
SELECT c.f.value('@name', 'nvarchar(128)'),
	c.f.value('@value', 'nvarchar(max)'),
	CAST(0 as bit) as is_local
	FROM @context.nodes('/context/fragment') as c(f);

Here we extract fragment values as a tabular data. Filling table based on attribute values in xml

2. parse

In parsing step we get all the places in the fragments that needs to be replaced. We use 'brain' function described before to get all the terms.

INSERT INTO @_parsed_terms
SELECT term_name, is_instruction, args, offset, original
	FROM dbo.sfnc_Database_SqlTemplate_FragmentParse(@fragment);

3. imports

In this step first signs of primitive syntax implementation is unveiled. If you have used instruction with a term name - import, it is going to be evaluated as a project import. Example:
$$import Appframe365.Common$$

-- NOTE: must append context with imports prior to evaluating set instructions.
if exists (
SELECT null
	FROM @_parsed_terms
	WHERE term_name = 'import' 
		and is_instruction = 1
)
begin
select @_parsed_imports = (
select distinct args as name
	FROM @_parsed_terms
	WHERE term_name = 'import' 
		and is_instruction = 1
	FOR XML RAW('project'));

SET @context.modify('
insert sql:variable("@_parsed_imports")           
as last into (/context)[1]');
end;

Here parsed import statements are injected back into full xml context. XML modify is not at all expensive compared to XML nodes function. Appending XML in SQL server is actually fast.

4. '=' instruction parse

Another possible instruction is uncovered. 'set' instruction is used if you want to set some fragment value to specific text or another external fragment.

INSERT INTO @_set_instructions
SELECT SUBSTRING(args, 0, CHARINDEX('=', args)) as name,
	SUBSTRING(args, CHARINDEX('=', args) + 1, LEN(args) - CHARINDEX('=', args)) as value,
	CASE	WHEN CHARINDEX('=', args) < 2
			THEN N'Invalid set instruction. "=" sign must be present in arguments in at least second position.'
			ELSE NULL
			END	AS error,
	offset,
	original,
	NULL AS flags
	FROM @_parsed_terms
	WHERE term_name = 'set'
		AND is_instruction = 1;

--NOTE: removing optional semicolon (';') delimiter
UPDATE @_set_instructions
SET value = SUBSTRING(value, 0, CHARINDEX(';', value)),
	flags = SUBSTRING(value, CHARINDEX(';', value), LEN(value))
WHERE CHARINDEX(';', value) > 0;

Here we get all terms that are instructions and are named 'set'. Additionaly we split set instruction argument list by equals sign. That way we extract name value pairs in each set instruction.

5. copy

Copying is used here so that we can preserve the original list. And we want to have a looping constuct where we process each item and delete it from the list on the go.

--NOTE: evaluate all instruction values as fragments
--enabling $$set some=$one$another$two$;$$ syntax
insert into @_set_value_fragments
select value, offset
	from @_set_instructions;

6. set instruction expand

As you can see recursion starts here. We are still not processing all the terms parsed. We are actually processing insides of set instructions, so we can use other SQL fragments inside of set instruction. example:
$$set colname=$filename$;$$
Here colname value will be assigned after $filename$ is expanded. Trace and error handling code was removed to simplify reading (it does not contribute to core understanding).

while exists (select null from @_set_value_fragments)
begin
	select top 1 @_current_set_fragment = value
		from @_set_value_fragments
		order by offset asc;
	
	select @_current_set_fragment_filled = NULL;
	
	exec dbo.sstp_Database_SqlTemplate_ExpandFragment
		@fragment = @_current_set_fragment,
		@context = @context,
		@Status = @Status,
		@result_script = @_current_set_fragment_filled OUTPUT,
		@errors = @_current_errors OUTPUT,
		@collect_trace = @collect_trace,
		@trace = @_inner_trace OUTPUT;

	if charindex('xquery:', @_current_set_fragment_filled) = 1
	begin
		declare @_dynamic nvarchar(max),
			@_res xml,
			@_xquery nvarchar(max);

		set @_xquery = substring(@_current_set_fragment_filled, 8, len(@_current_set_fragment_filled))

		set @_dynamic  ='select @r = @c.query('''+ @_xquery + ''');';
	
		begin try
			exec sys.sp_executesql
				@_dynamic, N'@c xml, @r xml out',
				@c = @context,
				@r = @_res out;
		end try
		begin catch
			set @_res = '';
		end catch;
		
		set @_current_set_fragment_filled = CAST(@_res as nvarchar(max));
	end;

	update @_set_instructions
	set value = @_current_set_fragment_filled
	where value = @_current_set_fragment;

	delete from @_set_value_fragments where value = @_current_set_fragment;
end;

As you can see set instruction supports xquery postprocessing. XML query() is being used to extract fragment value out of freeform xml passed. So here you could extract information out of unstructured xml passed in case you needed that. Example:
$$set outer_context=xquery:context$$
This specific instruction just captures context passed to your SQL fragment. it is usefull in case you need to use it inside of your SQL fragment. Check aggregate SQL fragment inside Appframe365.Common SQL template to see it in action.

7. copy

After set instructions are processed we are pushing computed values in to main value bucket - @_context_fragments.

INSERT INTO @_context_fragments
SELECT name, value,
	CASE	WHEN CHARINDEX('local', ISNULL(flags, '')) > 0
			THEN CAST(1 as bit)
			ELSE CAST(0 as bit)
			END AS is_local
	FROM @_set_instructions
	WHERE error is null; 

8. match instruction insert

Another instuction uncovered. Here we get all instructions with term name 'match' to use it in matching mechanism down the road.

-- NOTE: appending parsed additional match statements
INSERT INTO @_parsed_context_matches
SELECT args
	FROM @_parsed_terms
	WHERE term_name = 'match'
		AND is_instruction = 1;

9. append match

Because matching function expects it as xml we need to write it back into context XML.

SELECT @_parsed_context_match = (
SELECT pattern
	FROM @_parsed_context_matches	
	FOR XML RAW('match'));

SET @context.modify('
insert sql:variable("@_parsed_context_match")           
as first into (/context)[1]');

10. append fragments

Further we need to also append all computed fragments back to context XML. So it is usable when using recursion.

-- NOTE: appending parsed and possibly evaluated fragments
SELECT @_parsed_set_fragments = (
SELECT name, value
	FROM @_set_instructions
	WHERE CHARINDEX('local', ISNULL(flags, '')) = 0
		AND error IS NULL
	FOR XML RAW('fragment'));

SET @context.modify('
insert sql:variable("@_parsed_set_fragments")           
as last into (/context)[1]');

11. copy

Here we prepare for a loop. Similar to how it was done in step 5.

insert into @_replacer_terms
select term_name, is_instruction, ISNULL(original, '$$null$$'), ISNULL(args, '$$null$$'), offset
	from @_parsed_terms;

12. expand and replace

Again to keep it simple trace and error handling was removed below. This step is core part of this procedure. Here additional funcion is being used: sfnc_Database_SqlTemplate_TermArgsParse. But is not used anywhere else and can be considered as a part of this procedure. This function introduces additional syntax keywords 'inline' and 'suspend'. Also it introduces neat feature where you can use values from existing context by using '&' sybmol in a term. Example:
$get_data cols=&cols_get$
Here get data would be evalueated with cols being set to cols_get context fragment value. As you can see below mechanism is simple. it just parses current term arguments, then gets the fragment based on the term name and expands it again. Rinse and repeate recrsively until there is nothing to expand.

while exists (select null from @_replacer_terms)
begin
	select top 1 @_current_term_name = term_name,
		@_current_term_original = original,
		@_current_term_is_instruction = is_instruction,
		@_current_term_args = args
		from @_replacer_terms
		order by offset asc;

	if @_current_term_is_instruction = 0
	begin

		select @_current_ready_fragment = value
			from @_context_fragments
			where name = @_current_term_name;

		select	@_suspend = suspend, 
				@_inline= inline,
				@_context_suplement = context_suplement,
				@_frag_args_errors = errors
			from dbo.sfnc_Database_SqlTemplate_TermArgsParse(@_current_term_args, @context);
		
		set @_inner_context = @context;

		if @_context_suplement is not null
		set @_inner_context.modify('
		insert sql:variable("@_context_suplement")           
		as last into (/context)[1]');

		set @_id = null;

		if @_current_ready_fragment IS NULL
		select top 1 @_current_ready_fragment = fragment_text, @_id = id
			from dbo.sfnc_Database_SqlTemplate_GetFragment(@_current_term_name, @_inner_context)
			where Status >= @Status -- limiting first
			order by Status asc; -- dev -> test -> prod

		if @_current_ready_fragment is not null and @_suspend = 0
		exec dbo.sstp_Database_SqlTemplate_ExpandFragment
			@fragment = @_current_ready_fragment,
			@context = @_inner_context,
			@Status = @Status,
			@result_script = @_current_ready_fragment output,
			@fragment_name = @_current_term_name,
			@fragment_id = @_id,
			@errors = @_current_errors output,
			@collect_trace = @collect_trace,
			@trace = @_inner_trace output;

		--NOTE: postprocessing with args
		if @_inline = 1
		set @_current_ready_fragment = replace(@_current_ready_fragment, '''', '''''');
	end;

	if @_current_term_is_instruction = 1
	set @_current_ready_fragment = '';

	if @_current_ready_fragment IS NULL
	begin
		insert into @_errors
		select 'Fragment "' + @_current_term_original + '" is not defined in current context of fragment "' + isnull(@fragment_name, 'undefined') + '";';

		set @_current_ready_fragment = '/*fragment "' + ltrim(rtrim(@_current_term_original)) + '" is missing*/';
	END;

	set @result_script = replace(@result_script, @_current_term_original, @_current_ready_fragment);

	set @_current_ready_fragment = NULL;

	delete from @_replacer_terms
		where term_name = @_current_term_name
			AND original = @_current_term_original
			AND is_instruction = @_current_term_is_instruction
			AND	args = @_current_term_args;
end;

13. value instruction execute

The last and very important instruction uncovered here. Value instruction allows special handling of SQL fragment. The engine will execute it using sp_executesql. Again Error handling was removed to simplify reading.

IF EXISTS (
SELECT NULL 
	FROM @_parsed_terms 
	WHERE term_name = 'value' 
		AND is_instruction = 1
)
BEGIN
	BEGIN TRY
	EXEC sys.sp_executesql @result_script
		, N'@value nvarchar(max) OUTPUT'
		, @value = @_dynamic_value OUTPUT;
	END TRY
	BEGIN CATCH
		SELECT @_dynamic_value = '';
	END CATCH;

	SELECT @result_script = @_dynamic_value;
END;

As you can see in SQL fragments containing value instruction it is expected to do assignments to @value variable.

Conclusion

When thinking about SQL templates it is usefull to think about data flows. Specifically how context XML gets appended and gets passed recursively into deeper calls. Also extremely important part of SQL template engine is a concept of Project and Version. It supports deployment and allows separating distinct features into separate collections of SQL fragments. But do not forget that the primary purpose of SQL templates is writing dynamic SQL. If you are not trying to write something dynamic or something SQL then you most probably do not need to use SQL templates. Happy dynamic coding!


Related articles

Placeholder "LocalizeWeb2016" failed