SQL Nested Sets for Sites/Subsites – Part 1

I am trying to create a table to manage parent/child relationships for a website.  This is not too difficult if it were for one site, however I would like to use this table for multiple sites, as well as subsites.

For example, I have a website ALPHA, a website BETA as well as a Website GAMMA, but the root of website GAMMA is also a page in website ALPHA.

ALPHA BETA GAMMA
A
|-B
|-|-C
|-|-D
|-|-|-E
|-|-F
|-G
|-H
M
|-N
|-O
|-P
|-|-Q
|-|-R
B
|-C
|-D
|-|-E
|-F

As you can see above, site GAMMA’s root of B is also a child of site ALPHA’s A.

This is my schema

IF OBJECT_ID (N'Sites_GetTreeFromPage') IS NOT NULL
	DROP FUNCTION Sites_GetTreeFromPage
GO

CREATE FUNCTION dbo.Sites_GetTreeFromPage (
	@Page_id uniqueidentifier
	, @Site_id uniqueidentifier = NULL
	, @StartDepth int = 0
	, @Depth int = NULL
) RETURNS @PageTree TABLE (
	Page_id uniqueidentifier PRIMARY KEY NOT NULL
	, L int NOT NULL
	, R int NOT NULL
	, D int NOT NULL
	, Site_id uniqueidentifier NOT NULL
) AS
BEGIN
	SELECT TOP 1 @Site_id = ISNULL(@Site_id, Site_id)
	FROM Sites_Pages
	WHERE Page_id = @Page_id
	ORDER BY D ASC

	INSERT INTO @PageTree (Page_id, L, R, D, Site_id)
	SELECT
		p.Page_id
		, tree.L
		, tree.R
		, tree.D
		, s.Site_id
	FROM
		Sites_Pages s_p
		JOIN Sites_Pages tree ON tree.Site_id = s_p.Site_id AND tree.L >= s_p.L AND tree.R <= s_p.R
		JOIN Pages p ON tree.Page_id = p.Page_id
		JOIN Sites s ON s_p.Site_id = s.Site_id
	WHERE
		s_p.Page_id = @Page_id
		AND s_p.Site_id = @Site_id
		AND tree.D >= s_p.D+@startDepth
		AND (@Depth IS NULL OR tree.D <= s_p.D+@Depth+@startDepth)

	RETURN
END
GO
IF OBJECT_ID (N'Sites_GetPageTree') IS NOT NULL
	DROP PROCEDURE Sites_GetPageTree
GO

CREATE PROCEDURE Sites_GetPageTree
	@Page_id uniqueidentifier
	, @Site_id uniqueidentifier = NULL
	, @StartDepth int = 0
	, @Depth int = NULL
AS
BEGIN
	SELECT
		p.Page_id
		, p.Title
		, REPLICATE('| - ', tree.D-@startDepth) + p.Title AS Tree
		, tree.L
		, tree.R
		, tree.D
		, s.Title AS Site
		, CASE WHEN tree.L = 1 THEN 1 ELSE 0 END AS IsRoot
		, CASE WHEN tree.R = tree.L + 1 THEN 1 ELSE 0 END AS IsLeaf
		, CASE WHEN tree.R > tree.L + 1 THEN 1 ELSE 0 END AS IsParent
	FROM
		dbo.Sites_GetTreeFromPage(@Page_id, @Site_id, @StartDepth, @Depth) tree
		JOIN Pages p ON tree.Page_id = p.Page_id
		JOIN Sites s ON tree.Site_id = s.Site_id
	ORDER BY
		tree.L
		, tree.R
END
GO
IF OBJECT_ID (N'Sites_GetPageChildren') IS NOT NULL
	DROP PROCEDURE Sites_GetPageChildren
GO

CREATE PROCEDURE Sites_GetPageChildren
	@Page_id uniqueidentifier
AS
BEGIN
	EXEC Sites_GetPageTree @Page_id = @Page_id, @StartDepth = 1, @Depth = 0
END
GO
IF OBJECT_ID (N'Sites_GetSiteTree') IS NOT NULL
	DROP PROCEDURE Sites_GetSiteTree
GO

CREATE PROCEDURE Sites_GetSiteTree
	@Site_id uniqueidentifier
AS
BEGIN
	DECLARE @Page_id uniqueidentifier
	SELECT
		@Page_id = RootPage_id
	FROM
		Sites
	WHERE
		Site_id = @Site_id

	EXEC Sites_GetPageTree @Page_id = @Page_id, @Site_id = @Site_id
END
GO

I am trying to write stored procedures to interact with the Sites_Pages table, but it has been hurting my brain.

The complication is that I would like to perform all operations without having to run different queries for different Sites. Also, I don’t want to use a temporary table to store rows if I want to move Pages from one parent to another.

CSS Columns expanding beyond constrints of parent positioned element

I am trying to use CSS Columns to show submenu items stacked vertically in horizontal stacks/columns. I would also like to have a fallback of a scrolling menu if the browser doesn’t support css columns. However it seems that CSS Columns are expanding beyond the containing positioned element.

CodePen : http://codepen.io/anon/pen/dKAJF

Stack Overflow : http://stackoverflow.com/questions/26244353/css-columns-blowing-out-of-positioned-container-element

Using Freemarker in Coldfusion

It was quite a pain trying to find a decent template system to use in Coldfusion.  I finally landed on Freemarker, because I had used it’s syntax before.  I had to jump through some hurdles to finally figure out how to effectively use it, so here I will share how I did it.

Download JavaLoader:
http://javaloader.riaforge.org/

Download Freemarker:
http://freemarker.sourceforge.net/freemarkerdownload.html

I use JavaLoader because I do not always have control over the Coldfusion environment that my applications will run on.

Here is the cfc

<cfcomponent name="FreeMarker" output="no">

<cfscript>
instance=StructNew();
instance.ID=CreateUUID();
instance.Out=createObject("java", "java.io.StringWriter").init();
instance.Map=StructNew();
instance.JavaLoader="";
</cfscript>

<cffunction name="init" hint="Constructor" access="public" returntype="FreeMarker" output="false">
 <cfargument name="JavaLoader" type="any" required="yes">

 <cfscript>
 instance.JavaLoader=arguments.JavaLoader;
 </cfscript>

 <cfreturn this />
</cffunction>

<cffunction name="setContent" access="public" returntype="FreeMarker" output="false">
 <cfargument name="Template" type="string" required="yes">
 <cfscript>
 instance.Content=createObject("java", "java.io.StringReader").init(arguments.Template);
 instance.Template=instance.JavaLoader.create("freemarker.template.Template").init("FMT_" & instance.ID, instance.Content);
 </cfscript>
 <cfreturn this />
</cffunction>

<cffunction name="process" access="public" returntype="string" output="false">
 <cfscript>
 instance.Template.process(instance.Map, instance.Out);
 instance.Out.flush();
 </cfscript>
 <cfreturn instance.Out.toString() />
</cffunction>

<cffunction name="putInt" access="public" returntype="FreeMarker" output="false">
 <cfargument name="name" type="string" required="true" />
 <cfargument name="in" type="numeric" required="true" />
 <cfscript>
 instance.Map[arguments.name]=JavaCast("int", arguments.in);
 </cfscript>
 <cfreturn this />
</cffunction>

<cffunction name="putString" access="public" returntype="FreeMarker" output="false">
 <cfargument name="name" type="string" required="true" />
 <cfargument name="in" type="string" required="true" />
 <cfscript>
 instance.Map[arguments.name]=JavaCast("string", arguments.in);
 </cfscript>
 <cfreturn this />
</cffunction>

<cffunction name="putArray" access="public" returntype="FreeMarker" output="false">
 <cfargument name="name" type="string" required="true" />
 <cfargument name="in" type="array" required="true" />
 <cfscript>
 instance.Map[arguments.name]=arguments.in;
 </cfscript>
 <cfreturn this />
</cffunction>

<cffunction name="putStruct" access="public" returntype="FreeMarker" output="false">
 <cfargument name="name" type="string" required="true" />
 <cfargument name="in" type="struct" required="true" />
 <cfscript>
 instance.Map[arguments.name]=arguments.in;
 </cfscript>
 <cfreturn this />
</cffunction>

<cffunction name="putQuery" access="public" returntype="FreeMarker" output="false">
 <cfargument name="name" type="string" required="true" />
 <cfargument name="in" type="query" required="true" />
 <cfscript>
 instance.Map[arguments.name]=QueryToArray(arguments.in);
 </cfscript>
 <cfreturn this />
</cffunction>

<cffunction name="getMap" access="public" returntype="struct" output="false">
 <cfreturn instance.Map />
</cffunction>

<!--- ===================================== --->

<!--- Got from Ben Nadel: http://www.bennadel.com/blog/124-Ask-Ben-Converting-a-Query-to-an-Array.htm --->
<cffunction name="QueryToArray" access="private" returntype="array" output="false"
 hint="This turns a query into an array of structures.">

 <!--- Define arguments. --->
 <cfargument name="Data" type="query" required="yes" />

 <cfscript>
 // Define the local scope.
 var LOCAL = StructNew();
 // Get the column names as an array.
 LOCAL.Columns = ListToArray( ARGUMENTS.Data.ColumnList );
 // Create an array that will hold the query equivalent.
 LOCAL.QueryArray = ArrayNew( 1 );
 // Loop over the query.
 for (LOCAL.RowIndex = 1 ; LOCAL.RowIndex LTE ARGUMENTS.Data.RecordCount ; LOCAL.RowIndex = (LOCAL.RowIndex + 1)){
 // Create a row structure.
 LOCAL.Row = StructNew();
 // Loop over the columns in this row.
 for (LOCAL.ColumnIndex = 1 ; LOCAL.ColumnIndex LTE ArrayLen( LOCAL.Columns ) ; LOCAL.ColumnIndex = (LOCAL.ColumnIndex + 1)){
 // Get a reference to the query column.
 LOCAL.ColumnName = LOCAL.Columns[ LOCAL.ColumnIndex ];
 // Store the query cell value into the struct by key.
 LOCAL.Row[ LOCAL.ColumnName ] = ARGUMENTS.Data[ LOCAL.ColumnName ][ LOCAL.RowIndex ];
 }
 // Add the structure to the query array.
 ArrayAppend( LOCAL.QueryArray, LOCAL.Row );
 }
 // Return the array equivalent.
 return( LOCAL.QueryArray );
 </cfscript>
</cffunction>

</cfcomponent>

Download the full example here

AlanClendenin.com Launched

Well, a much overdue update to my father’s website has been completed. AlanClendenin.com has been launched on GSL Solutions’ FrontRunner CMS.  Check it out, I’m rather proud of it.

It’s the perfect communication tool for my father, who is politically active and needed a website to publish not only blog posts, but also news items.

I’m pretty much using his website as my test bed for new features and ideas.  Plus who better to suggest features than a family member.

You know how family is with software.  They are always asking “Can I do …”, or “How do I …”.

I would like to thank Flip who designed the website.

Thoughts on CMS’s as Version Control Systems

I got myself to thinking about versioning content in a content management system.  It seems to me that when it comes down to it versioning of content is integral to the success of long term usability of a CMS.  As any website grows more and more users will need to have access to both create and maintain the content of a website.  But I was thinking that version control can server a better purpose then just helping with workflow.

Let’s apprise one of the weaknesses of the internet, permanence.  After I post this article on my website, the website would seem to be the same website, but looking at it from a different perspective you could consider it to be a “new edition”.  Much like a book, adding new information and reprinting it, you would have a new book as a different edition.  The problem with this analogy is that with books you can take the two editions and compare them side by side, but with a website the old edition is gone forever (kind of).  Comparing two editions of a book can show you exactly what changes have been made, while there is no way of knowing exactly what has changed on a website.

Now this is not entirely true, The Wayback Machine at the Internet Archive, continually scans the internet and saves old copies of information. RSS feeds have been adopted to allow subscribers to keep informed of changes made to the website.  And Wiki’s have sprung up in the attempt to allow content to be kept up to date and versioned.

What I would like to see is a standard for websites that allows the website itself to be versioned.  Make a website capable of being displayed as it was at a specific date and time.  Allow for milestones to be created for the content and the website as a whole.  Imagine having a draggable timeline above the website and being able to change the date you are viewing the website from.  This could even be extended to have a timeline per piece of content.

I am taking a lot of this idea from version control systems like CVS, SVN, or Git, but with one main difference.  Content should also be able to be predated to allow content to show up at future times as well.  Using version control as an embargo system is just one of the many advantages of thinking of a website as a viewpane on a timeline.

How long will it be until we create an internet of self archiving websites.  How long until we stop using technologies such as RSS and The Wayback Machine as stopgaps.  Let’s address this issue before it is too late, if it isn’t already.

SQL Server BULK INSERT Using a Data Field?

I have wondered for a while if there is a way to use SQL Server’s BULK INSERT command using BLOB or CLOB data Instead of a local file.

The reason for this is that when writing imports, using both Coldfusion and PHP, parsing and importing the data into the database is the weakest link.  It is relatively simple to write dynamic SQL to import data into my existing schema when copying from data already present in the database.  It is quite another matter to attempt to run thousands of INSERT statements from my web application.

I can’t ask a client to use SQL Management Studio to import their data into a table and then use a web app to run the import.  I don’t want to expose the database to the user and vice-versa.

At present I can only think of one good way to implement an import process and that is to allow the user to

  1. Upload the file.
  2. Create some sort of import plan (associate the content).
  3. Save that plan in some way.
  4. Run a scheduled task to run the import in batches.

I am dealing with imports of possibly hundreds of thousands of records.  Also note that I have limited access to the database as it is possible that the app will reside in a shared hosting environment.

Git for Windows Users

Here I am compiling information as a windows user that I needed or should have known while working with Git.

I still have issues trying to push using Git to a network server, as does a Mac using coworker.  I think there is some configuration issue or Git just doesn’t work well across networks.

MS SQL Server Time Dimension UDF

I use this function when writing reports.

Use:

SELECT
  time.Date
  , Count(tab.ID) AS [Count]
FROM
  dbo.TimeDimension('1/1/2007 00:00:00', '12/31/2007 23:59:59', 'month', 1) time
  LEFT OUTER JOIN
   TempTable tab
  ON tab.DateCreated BETWEEN time.Date AND time.DateEnd

Here is the function. I don’t know if there is a better way to do this, if there is can someone tell me. As far as I know I am the only person to post a function like this.

CREATE FUNCTION [dbo].[TimeDimension] (
 @DateStart datetime
 , @DateEnd datetime
 , @Type varchar(20)
 , @IncrementValue int
)
RETURNS @Dates TABLE ( Date datetime , DateEnd datetime )
AS
BEGIN
 DECLARE @DateCurrent datetime, @NextDate datetime
 SET @DateCurrent = @DateStart

 WHILE (@DateCurrent <= @DateEnd)
 BEGIN
 SET @NextDate = CASE (@Type)
 WHEN 'year' THEN DateAdd(year, @IncrementValue, @DateCurrent)
 WHEN 'quarter' THEN DateAdd(quarter, @IncrementValue, @DateCurrent)
 WHEN 'month' THEN DateAdd(month, @IncrementValue, @DateCurrent)
 WHEN 'dayofyear' THEN DateAdd(dayofyear, @IncrementValue, @DateCurrent)
 WHEN 'day' THEN DateAdd(day, @IncrementValue, @DateCurrent)
 WHEN 'week' THEN DateAdd(week, @IncrementValue, @DateCurrent)
 WHEN 'weekday' THEN DateAdd(weekday, @IncrementValue, @DateCurrent)
 WHEN 'hour' THEN DateAdd(hour, @IncrementValue, @DateCurrent)
 WHEN 'minute' THEN DateAdd(minute, @IncrementValue, @DateCurrent)
 WHEN 'second' THEN DateAdd(second, @IncrementValue, @DateCurrent)
 WHEN 'millisecond' THEN DateAdd(millisecond, @IncrementValue, @DateCurrent)
 END

 INSERT INTO @Dates ( Date , DateEnd )
 VALUES ( @DateCurrent , DateAdd(second, -1, @NextDate) )

 SET @DateCurrent = @NextDate
 END
 RETURN
END