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.
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:
If cut out part is wrapped by double dollar sign it is considered an 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:
$a$ + $b$ = $c$
the parse result would be:
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:
$a$$ + $b$ = $c$
the parse result would be:
||☐||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 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:
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:
just a mexican...
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.
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.
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;
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
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);
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:
-- 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)'); 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.
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.
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;
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:
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:
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.
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;
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;
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)');
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)');
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;
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:
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)'); 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;
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.
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!
Placeholder "LocalizeWeb2016" failed