All posts in Uncategorized

Recent Small Jobs

A few smaller jobs that I was involved in recently and now it is time to give a shout-out to people looking for work or trying to run a taxi company in SF.


http://patrickyoutz.com/

patrick-youtz

A very talented designer, looking for a gig. I worked with him at Monster and he is a great guy. He needed some help debugging the JavaScript and responsive CSS stuff.



http://airporttaxiairport.com/

ata

A dependable, quality flat-rate taxi service in San Francisco with online reservations. This is a custom WordPress implementation. Originally he had his site in the truly awful VistaPrint web world. Run away from that system like the plague!!! I set ATA all up with WordPress. I created a plugin so that the data for the various fares is managed in a Google spreadsheet. This data is then used to run the entire shopping cart. The site sends the user to an Authorize.net simple checkout. Secure, clean, dependable, flexible and really awesome.

AngularJS, .less and other cool stuff

This last month I have been taking some time off, doing self-improvement, studying AngularJS, getting more familiar with GIT and .less. It is a fine time for front-end web development.

Learning AngularJS is pretty straight forward. If you about 12 years old you can sign up for the course at http://campus.codeschool.com/courses/shaping-up-with-angular-js/intro. It is basically a video course that even has a theme song all about “directives” and “services.” Pretty dreadful really. The best way to get started is to go to https://docs.angularjs.org/tutorial, fire up Git and you are on your way. The process also introduced me to Bower, which is “package manager for the web.” Once you get use to Bower, it seems silly to do things the old fashion way.

Outside of computer stuff, during the last month, I painted the kitchen and the bathroom. I also went to the San Francisco dump with my 15-year-old daughter and got rid of a lot of useless stuff. The dump is down on tunnel road. Now I have space to enjoy a few empty shelves.

If you need help with your web site, feel free to call or email.

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.

    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!