Archive for August, 2013

The Adjacency List Model and HTML List Creation Overview – SQL and Classic ASP

In the effort to create semantically robust websites, I am often dealing with hierarchical data from Content Management Systems. Sometimes these systems have methods for dealing with this categorization. Often these methods are either inefficient (making unnecessary calls to the database, or just extremely clumsy) Sometimes, the presentation level is custom. In .net there is the TreeView Control which has its own set of issues. Many times I get into the world of XLM and XSLT, which almost always makes programmers groan.

This essay will present a method for dealing with this and the various issues when presenting The Adjacency List Model data using T-SQL and Classic ASP.

The Adjacency List Model

A good online definition of The Adjacency List Model can be seen here: http://www.pure-performance.com/2009/03/managing-hierarchical-data-in-sql/ . In particular, we are looking at Trees and Hierarchies in SQL for Smarties
by Joe Celko.

The Data

Imagine your data looks like below. This has Devices Categories and DeviceCategories within the Device Categories. In theory, this hierarchy could go on indefinitely with categories being nested inside categories.

DeviceCategoryId ParentCatID DeviceCategoryName
1 NULL Apple
2 NULL Other Devices
3 1 iPhone
4 2 Smartphones
5 1 iPad
6 1 iPod
7 3 iPhone 5
8 3 iPhone 4/4S
9 3 iPhone 3/3GS
10 6 iPod Touch
11 6 iPod Nano
12 6 iPod Classic
13 6 iPod Shuffle
14 2 Tablets
15 4 Android
16 4 iPhone
17 4 Windows
18 14 iPad
19 14 Nexus
20 14 Kindle
21 14 Samsung

In the end, the goal is to represent the data above as a series of nested lists as seen below.

  • Apple
    • iPhone
      • iPhone 5
      • iPhone 4/4S
      • iPhone 3/3GS
    • iPad
      • iPad (Retina Display)
      • iPad 2
      • iPad Mini
    • iPod
      • iPod Touch
      • iPod Nano
      • iPod Classic
      • iPod Shuffle
  • Other Manufacturers
    • Tablets
      • Nexus
      • Kindle
      • Samsung
    • Smartphones
      • Android
      • Windows

Getting From Point A to Point B

The first thing is to form the SQL data correctly. You can do this with a temp table, but I use a CTE (Common Table Expression). Below, this is what is the stored procedure sp_DisplayDeviceCategories

WITH TempCategories AS (
SELECT DeviceCategoryId, ParentCatId, DeviceCategoryName, 
1 AS DeviceLevel,  Cast(DeviceCategoryId as varchar(30)) AS SortOrder
FROM DeviceCategories WHERE ParentCatId  IS NULL
	 
UNION ALL
	 
SELECT c.DeviceCategoryId, c.ParentCatId, c.DeviceCategoryName, 
c2.DeviceLevel + 1,  Cast(c2.SortOrder + '/' + CAST(c.DeviceCategoryId AS varchar) as varchar(30))
		FROM DeviceCategories AS  c 
		INNER JOIN TempCategories AS c2
		ON c.ParentCatId = c2.DeviceCategoryId
		WHERE c.ParentCatId IS NOT NULL 
	)
SELECT  * FROM TempCategories ORDER BY  SortOrder

This then will format the data as

DeviceCategoryId ParentCatId DeviceCategoryName DeviceLevel SortOrder
1 NULL Apple 1 1
3 1 iPhone 2 1/3
7 3 iPhone 5 3 1/3/7
8 3 iPhone 4/4S 3 1/3/8
9 3 iPhone 3/3GS 3 1/3/9
5 1 iPad 2 1/5
6 1 iPod 2 1/6
10 6 iPod Touch 3 1/6/10
11 6 iPod Nano 3 1/6/11
12 6 iPod Classic 3 1/6/12
13 6 iPod Shuffle 3 1/6/13
2 NULL Other Devices 1 2
14 2 Tablets 2 2/14
18 14 iPad 3 2/14/18
19 14 Nexus 3 2/14/19
20 14 Kindle 3 2/14/20
21 14 Samsung 3 2/14/21
4 2 Smartphones 2 2/4
15 4 Android 3 2/4/15
16 4 iPhone 3 2/4/16
17 4 Windows 3 2/4/17

The tricky part of the CTE above was creating the DeviceLevel and SortOrder fields so that we can order the data is such a way that then we can simply loop through the data and display the results in a user-friendly way.

Now the Hacky Part

Well this may not actually be a hacky part. I find the .net TreeView to be pretty hacky as it uses tables to display this list data. There are CSS workarounds with the cssadapters (http://www.asp.net/cssadapters/TreeView.aspx) but yuck…. Why not get it right in the first pass.

I have also often seen people then format this sort of hierarchy with non-breaking spaces ( ) and do clever HTML things to indent lists but this is bad form. In order for your navigation to work with responsive designs it needs to be semantically correct.

I recently worked on a project that still uses Classic ASP. The problem was, without .net Controls, how to display the data above as a list. It would have to be recursive to work. In the end though, it is just procedural programming. I am certain that this has been created in other languages and for various custom navigation components.

<%
Sub DisplayDevices
' this sub writes out a nested HTML list from a common table expression in an SQL Stored Procedure
' that uses Adjacency list model to display Devices Categories
    DeviceLevelTemp = 0
    WriteListItem = True
    
    sql = "EXEC sp_DisplayDeviceCategories"
    set rs=Server.CreateObject("ADODB.Recordset")
    rs.open sql,connect    
    
    If Not rs.eof Then 

        DO  UNTIL rs.eof 
         
        DeviceCategoryId = rs("DeviceCategoryId")
        ParentCatId = rs("ParentCatId")
        DeviceCategoryName = rs("DeviceCategoryName")                                
        DeviceLevel = rs("DeviceLevel")
        
        If DeviceLevelTemp = DeviceLevel Then
            response.write("")
            WriteListItem = True
        Else
            If DeviceLevel > DeviceLevelTemp Then
                c = DeviceLevel  -  DevicelevelTemp 
                For i = 1 To Cint(c)
                    response.write("
  • ") Next WriteListItem = False End If 'closure If DeviceLevelTemp > DeviceLevel Then c = DeviceLevelTemp - Devicelevel response.write("
  • ") For i = 1 To Cint(c) response.write("
") Next WriteListItem = True End If End If If WriteListItem = True Then response.write("
  • ") End If Select Case DeviceLevel Case 1 response.write("

    " & DeviceCategoryName & "

    ") Case 2 response.write("
    " & DeviceCategoryName & "
    ") Case Else response.write("" & DeviceCategoryName & "") End Select DeviceCategoryIdTemp = DeviceCategoryId ParentCatIdTemp = ParentCatId DeviceCategoryNameTemp = DeviceCategoryName DeviceLevelTemp = DeviceLevel rs.movenext LOOP 'clean up list For i = 1 To Cint(DeviceLevel) response.write("
  • ") Next End If rs.close set rs=nothing End Sub %>

    For starters, EXEC sp_DisplayDeviceCategories is simply the CTE query above. The basic concept is that the results are then put into nested lists. When the records are all listed, the subroutine then cleans up the HTML and closes the list. I have tested this with only one set of data. Let me know if the concepts are of use to you. It would be nice to make the Sub DisplayDevices a more universal routine, but the data may always be different depending on the field names.

    The Semantics of HTML and Web Sites – Why Markup Matters

    Creating websites for 13 years now, I have seen the evolution of websites from clunky table based layouts that were semantically just a bunch of noise to the current HTML5, semantically charged creations. Of course the leaders in Web Standards and semantic HTML have been people like Jeffery Zeldman and his disciples, great CSS exercise sites like Zen Garden and also the blog sites and open source world, probably most importantly WordPress. If you want to make a web site theme that then someone else can take and run with, the semantics of the markup have to be solid.

    I am amazed at how understanding semantics in both web design and programming gets overlooked. I still know many web designers who do not understand what a heading tag is. They are often so lost in the fonts and backgrounds and images that they forget what is going on in the hierarchy of information. I have witnessed decent designers never realize that <h1>What a Killer Page</h1> is what is essential. This is not only for SEO but also for making the web site accessible on a variety of platforms. I still know amazing developers who can talk your ear off about Object Oriented Design Patterns who when they get to the HTML will code a heading tag as <div class=”main-header”> What a Killer Page</div>. I am not sure if this is because they just read stuff too quickly and never really read chapter one and think they know everything, or maybe they just don’t get it. Struggling with the margins of a <p> tag does not mean you should then use a few <br /> tags. Strange.

    In HTML, if you take away things like the required html, body and title tags, the important semantic markup is pretty simple.

    • Headers h1,h2,h3,h4,h5
    • Paragraphs <p></p>
    • Lists <ul><li></li></ul>
    • Tables <table><tr><th></th></tr><tr><td></td></tr></table> (for tabular data)

    That is really it! Markup such as divs and spans are not semantic. They are presentational! Putting things like navigational lists into divs is just wrong. The sooner one learns this basic distinction, the faster you will write cleaner, easier to maintain code.

    In HTML5 the list of semantic markup grows longer. I will not enumerate them here but to me one of the main objectives of HTML5 is this semantic markup. <section></section>, <article><article>, <video></video>.

    So there you have it. Just think. If you are just starting to write HTML, you got to avoid the 7 year era of table based layouts and nested tables seven layers deep just to keep you debugging for and hour all for the crappy browsers of the day.

    originally posted: December 27, 2012

    Welcome to the New Website!

    I remember a few years back when a coworker said

    You know when a website is old, is when the main header says “Welcome.”

    At the time I completely agreed with him. I have nothing against “Welcome,” it is just that there is no point really. Perhaps, “Welcome” in that context was more like – “look here… I HAVE A WEBSITE” OK. Here is the new portfolio website. The last one was dreadful and getting cobwebs.

    Well now we are in 2013. There are lots of websites. In fact, there are websites within website to the point where the internet is one communal content management system…

    I intend to make this blog a place where I share my findings, knowledge and opinions in web development. Stop back. I am thinking up some really good stuff!