SevenDaysOfSchema.com – Building this Website

I always planned on having a website where I could post some of the huge number of tutorials and code snippets I’ve written over the years. I’ve been wanting to do this for so long that many of my tutorials are too outdated to be useful. I’ve written tutorials and code examples that are stored on my hard drive as .cs or .sql files and in code snippet management software with varying degrees of documentation. Plus, there are hundreds of them, so the project goes beyond simply copying and pasting into a blog.

SevenDaysOfSchema.com is an effort put my tutorials online and also provide a place to post my tech tips, tutorials, and book reviews. Add to that a rock’n new PC and a rekindled love affair with anything related to .NET development and the catalyst to develop SevenDaysOfSchema.com was born.

Using this project to update my skills meant that it took longer than expected. A lot longer. As simple as it looks, the development effort took 14 months and spanned multiple C# projects that were combined to live under a single solution in Visual Studio.

Only a very small portion of content has been published.

Project Goals

For this project to be considered a success, this website must have the ability to receive and process the T-SQL scripts, queries, and tutorials that make up my T-SQL script library.

I needed to grammatically generate as much of the web pages as possible because the number of snippets in my collection had become quite large. The scripts needed to be parsed from an XML file that my code snippet software uses and from sql scripts in the file system. I needed to be able to link every script to its database record and that link needed to persist, even after the title or content of the script has changed.

I also wanted to add value to the library by writing a program to parse each T-SQL script to find any occurrences of SQL Server system objects and T-SQL functions, keywords, and other constructs. That would allow me to add reference information for each of those constructs, right on the same page as the script.

At first, I was happy if it could just list the T-SQL language keywords and SQL Server system objects in the scripts so that I could link back to Microsoft docs and add WordPress tags that would make it easier for me to search the library for similar scripts.

For example, if a script queries the INFORMATION_SCHEMA.TABLE_CONSTRAINTS view, I wanted to list all the other INFORMATION_SCHEMA views that also include constraint information, like INFORMATION_SCHEMA.KEY_COLUMN_USAGE.  That way, I would have the information I’d need to modify the script for use in a future project.

The process of parsing the Microsoft Docs so that a database of SQL Server system objects, T-SQL constructs and their descriptions turned into a six-month project that has been split off into its own project.

I am an editor of Microsoft documentation and so you’ll see that I’ve included links to GitHub repository where editors can edit the documentation and the live version on Microsoft’s website .

I save and manage a lot of my T-SQL queries using a free utility called Snippet Manager. I also have a folder on my hard drive that’s full of example SQL files. Recently, I began moving all of my SQL scripts to Snippet Manager, because I’m tired of having to open every .SQL file in SQL Server Management Studio, just to remember what’s in it.

I am not impressed with the way SSMS and Visual Studio manages snippets at all. Only Microsoft would decide that you don’t need to actually see the code in the code snippet you’re about to insert into your script, forcing you to commit to memory, the name you gave a specific snippet and then also remember how that snippet differs from any variation you may have saved. This is no surprise considering .NET programmers had to learn the difference between the visual user interface controls that most programs have, like ListView, TreeView, ListBox, PictureBox, and CheckListBox,  described entirely as text.  There isn’t a single picture in all of that documentation.  I digress.

Challenges

This project consumed all of my free time for more than 10 months, but the time was not wasted.  I’m not interested in being a full-on developer again, but I love to learn, so I took every opportunity to study and improve my C# and T-SQL skills in case I change my mind.

Vetting the Scripts

The first scripts were added to my library years ago and I never intended to post them online. That made attempts at automating this process, untenable. Many of the scripts were applicable to only one project. Others wouldn’t make sense to anyone except me. For these reasons and more, I wound up with 200+ scripts to edit and gradually add to the archive.

Matching Web Pages to Content

Once a code snippet was posted online, how would I know which T-SQL script it came from?  How do I uniquely identify each script?  Snippet Manager stores scripts in an XML file, using the script Title as a key.  SQL files stored in the file system also use the script’s title, but it’s the title is a file name.  What if the file name changes?  How will it know that the script was already uploaded to the server?

At first, the title of the script seemed to be the natural choice for use as a Primary Key, but what would I do if the file name changes? I thought I could just add a file watcher to the folder where my SQL files are stored and have it update the file name in my database if any file names are changed.  That was problematic because titles are used in WordPress URLs.  Once indexed by search engines, they can’t be changed without adding a re-write script to prevent the link from returning File Not Found errors.

Updating the Library

Another big problem had to do with updating the scripts that I had already posted.  It’s not helpful to have simply posted the code.  If I edit the code because the original version was incorrect, incomplete or less efficient, I wanted there to be a way for me to update the script in Snippet Manager or in Management Studio and have that update go out the website and update the script that’s been posted online.

I knew for sure that I did not want to have to go out to my website, every time I made a change to a file name.

Solutions

All projects seem to have this moment where things come together.  For me, it was when I realized that the best way to track my  T-SQL script the entire through its life-cycle was to bite the bullet and just add a GUID identifier in a T-SQL comment, directly to the bottom of every script.

You won’t see the watermark in any of my scripts.  It’s only present in the original source file.  That means I was also able to use it to identify new scripts, which freed me from having to parse all of the files in the library, every time I wanted to add a script to my website.

I resisted the idea of marking up the actual script because of the problems that can make reading and writing to a file at the same time error prone.  For example, I could not ensure that each of my .sql files was written and therefore would be read using the same encoding as the default encoding that would be used for write operations on my PC.

I solved that problem by testing several file encoding detection methods I found on Stack Overflow and selecting the solution that was most often successful at appending to the file in that same encoding as the original file.  It’s obvious that you’ve got the wrong encoding when garbage shows up in your scripts.  Once, it incorrectly altered the encoding of all the scripts in my library in one foul swoop.  Encoding errors are unrecoverable errors, so be sure to have a backup of your files at all times.

More Information

If you want to learn more about this or any other project I’ve worked on, please submit a comment form below.