T-SQL to WordPress HTML

T-SQL to WordPress HTML is a program I created to discover, load and parse into HTML, the T-SQL scripts and queries that make up the T-SQL portion of this website.  The scripts originated from a directory of SQL files on my development machine and from the code snippet management software I use, called Snippet Manager.

This program is also responsible for adding the reference information that appears after each of my example T-SQL queries and scripts.

Challenges

Uniquely Identifying T-SQL Scripts

One of the first challenges I ran into dealt with finding a way to uniquely identify a T-SQL script so that the link between each T-SQL script and its database record would not be lost if the script is renamed or moved to another folder.

I needed to be able to apply this technique to sql files on my hard drive and scripts in my code snippet management software. I didn’t want this process to interfere with how I do my development work and wanted it to be as user-friendly as possible.

The solution to this problem was to embed a GUID in an SQL comment and insert it into the bottom of every T-SQL script.  This lets me rename the file and change virtually anything else in the script.  As long as I don’t remove the GUID from the file, the link won’t be broken between the script and its database record.  Sinc the GUID is also displayed on the web page, I can use it to link the WordPress blog post to it’s originating client-side SQL Server database record.

Multi-line Comment Parsing

I tend to start my T-SQL scripts with a multi-line comment that contains information about the script. My program pulls those multi-line comments out of the script and converts them into HTML paragraph text and displays each comment above the T-SQL script it’s referring to.

Of course, not all comments are substantial enough to justify breaking up the T-SQL into smaller parts, so the program ignores inline comments starting with “–“.

 
/* 

This comment will be broken out into an HTML paragraph. Although 
there are hard returns in the script, this will all appear on one 
line. Two consecutive newlines cause the parser to start a new 
paragraph. 

*/ 

UPDATE MyTable SET MyColumn=NULL; 

---------------------------------- 
-- Inline comments like this will not 
-- be parsed into HTML comments. 
---------------------------------- 

SELECT * FROM MyTable WHERE Something IS NULL; 

Syntax Highlighting

To be useful, my T-SQL scripts and queries need to easy to read, so syntax highlighting was required. Remember that I began this application by testing and working with my free blog on WordPress.com.  For me to be able to test the solution in my sandbox (i.e. my free WordPress.com blog) and deploy to production (i.e. my paid, BlueHost WordPress blog), I would need a solution that works in both environments.

Fortunately, WordPress comes with source code formatting built in, thanks to Alex Gorbatchev’s SyntaxHighlighter. This is currently your only option on free WordPress.com blogs and is available as a plug-in in the BlueHost C-Panel if you go with them.

It does have its shortcomings. Chief among them is the lack of a copy button. Still, it works with all of the languages I program and so I am happy to use it.

Adding Reference Information

If I had time, I could write a lot more about this part, but it will be covered in depth in my Microsoft Docs Parser project, which was a mammoth project that should have been done in Data Tools (i.e. SSIS).  That project left me with a database of information about every keyword in the T-SQL language even more information about the SQL Server system objects.

You’ll notice that each script in the T-SQL section of this website has a vast amount of supportive information. I wanted it to be present on the same page as the script. Including this much information without drowning out the script itself, was a challenge.

One big reason I’ve been slow to get scripts posted is that I’m still working on ways to trim down this information.

Discovering Search Keywords

The T-SQL scripts in my archive each have a title that represents the functional goal of the script and contains keywords and SQL Server system object names that provide excellent food for search engine optimization.

In WordPress, keywords are saved as tags.  WordPress tags can’t have a hierarchy like WordPress Categories can.  Categories play an important role in the formation of the URLs that lead to the pages of this site.

On each post, T-SQL to WordPress HTML performs the following tasks associated with keywords:

1. Parse each T-SQL script for the presence of any keyword
2. Add each keyword as a WordPress Tag
3. Add reference info about each keyword