Tag Archives: sql

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.

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.

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

MD5 Hash in SQL Server 2000 and 2005, 32 bit and 64 bit

SQL Server 2000
xp_MD5 at http://www.codeproject.com/database/xp_md5.asp

Also works in SQL Server 2005 32 bit

SQL Server 2005

DECLARE @Data varchar(MAX)
SubString(master.dbo.fn_varbintohexstr(HashBytes('MD5', @Data)), 3, 32)

Works in SQL Server 2005 32 and 64 bit

Solution found at http://www.codeproject.com/database/xp_md5.asp?df=100&forumid=32460&select=1519990#xx1519990xx