Our last article on data shaping introduced you to the basics, with a few applications. This time we'll talk about reshaping, and explore one topic more in depth - that of a hierarchical file system. Briefly, let's review what data shaping is.
Data Shaping
Data shaping is a little known feature of ADO 2.0 and above that allows you to create hierarchical recordsets, which can give you tremendous power over manipulating and displaying data. Unfortunately, data shaping has taken a back seat for many developers.
Data shaping lets you create parent-child relationships between recordsets, i.e. recordsets within recordsets, all with a single ADO object. This is a dream for a web developer, because it eliminates messy joins, complicated filtering, and the need for spaghetti-code in presentation logic. This in turn reduces the amount of traffic crossing a network and reduces overhead when interfacing with other tools, such as XML.
The Shape Command
Let's go over the syntax for the shape command for review:
SHAPE {parent-command} [[AS] table-alias]
APPEND {child-command}
RELATE(parent-column TO child-column)
And to tickle your syntax funny bone, here's a more complex syntax for shape structures with grandchildren:
Here we have a typical file system (albeit a small file system). Using data shaping would be an excellent way to gather and display this type of structure. Of course, if this were an actual file system, you'd be more likely to use the FileSystemObject to retrieve and display data (go here for a tutorial on the FSO). Meanwhile, let's assume the database structure for this system looks like this:
tblRoots
DID
Unique ID to keep track of records
Autonumber
Name
Directory name
String
tblSubDirectories
SID
Unique ID to keep track of records
Autonumber
DID
Which root does this belong too? Foreign key to tblRoots
Integer
Name
Directory name
String
tblFiles
FID
Unique ID to keep track of records
Autonumber
SID
Which subdirectory does this belong to? Foreign key to tblRoots
Integer
Name
File name
String
Each table then consists of a separate level in the hierarchy. The shape command to return the data in the manner presented above would look like:
SHAPE {SELECT DID, Name FROM tblRoot}
APPEND (
( SHAPE { SELECT SID, Name FROM tblSubDirectories } As SubDirectory
APPEND (
{SELECT FID, Name FROM tblFiles} AS Files RELATE SID TO SID) )
RELATE DID TO DID)
This shape command can be used to display the hierarchy as outlined above. However, what happens if you have an arbitrary depth of the hierarchy? Meaning, you don't know ahead of time how many levels you have. Data shaping is great, but even it can't handle arbitrary depths by itself.
Let's examine this command a bit further. If the maximum number of levels you had was three, then you could use this statement without a problem. The command would return data in the following manner:
For those directories or subdirectories that don't have anything beneath them, the reference to the sub-chapter would be an empty recordset. Therefore, you needn't perform any special tasks for hierarchy data of varying depths. So in essence, you simply have to know the lowest level you need to grab data from.
Note: The tables described above are not a very efficient method to store hierarchical file system information, and are used solely for example. For a more efficient method, try this article.
Data Looping
Let's say now that you don't know ahead of time how many levels there will be in the hierarchy, and for some reason, you can't just go with the lowest level available - if for example, you wish to stop at a different level for each row in the database depending on some outside conditions. You can easily set up a loop to tack on the shape commands as necessary.
<%
for i = 0 to n
strSQL = "SHAPE { sql statement } APPEND (" _
& strSQL & " RELATE id TO id )"
next
%>
Where n equals the number of times you would like to loop through, or, in other words, the depth of your shape command (for n = 1 you will have grandchildren, n = 2, great grandchildren, etc). For each iteration, you would fill in sql statement and id TO id. This of course requires some knowledge ahead of time - you would need to know what fields to relate the tables by, and how many levels you need to dive into.
This loop will assemble a shape command in the proper format to reach any arbitrary level in a hierarchy. The problem with this approach is if you would like multiple children for each level. Since the shape syntax is very strict, you would have to assemble your command very carefully, and each level would have to have the same format and database members.
Due to this level of complexity, if you ever reach this scenario, it would probably be best to take a step back and examine other ways of structuring your database, as this method is cumbersome and inflexible. Data shaping is always most valuable when not used in a generic setting, such as in a for loop.
Reshaping
Reshaping is a way to use data from an existing shaped recordset to return new or different information. Normally, you might have to re-execute the entire shape command, but this method allows you to use cached data that you've already requested instead, saving you time and overhead. Reshaping also allows you to get at child recordsets without having to get to them through the parents. Let's take a look at a simple example, taken from our first article:
<%
Dim strSQL, rst, rstChild, strConnectionString, rstChild
strConnectionString = _
"Provider=MSDataShape;dsn=MyDSN;database=MyDatabase"
' Original recordset
strSQL = "SHAPE { SELECT MID, Name, Address FROM tblManufacturer } " _
& "APPEND ({ SELECT Name, Instock_flag, Price, PID " _
& "FROM tblProducts } " _
& "AS Products RELATE MID to MID )"
' Open original recordset
Set rst = Server.CreateObject("ADODB.Recordset")
rst.Open strSQL, strConnectionString
' Reshaped recordset
strSQL = "SHAPE Products"
' Open reshaped recordset
Set rstChild = Server.CreateObject("ADODB.Recordset")
rstChild.Open strSQL, strConnectionString
%>
Here, we have essentially separated the filtered child recordset from its parent, by referencing the Products alias used in the original shape command. We are now free to do whatever we wish with this child recordset, including filtering and displaying data. This opens up a whole new area of possibilities for data shaping - not only can it do hierarchical recordsets, but regular recordsets as well!
We can also do more advanced queries with reshaping. The following example uses the compute command to return additional information:
Find out that you need another parent for your child recordset? Not a problem. Let's assume we have a new Customers table - we set the Customers tables as the new parent, and the existing cached Products table as the child:
<%
strSQL = "SHAPE {SELECT * FROM Customers} APPEND " _
& "(Products AS Products RELATE PID TO PID)"
' Open recordset
Set rstSecond = Server.CreateObject("ADODB.Recordset")
rstSecond.Open strSQL, strConnectionString
%>
Or even have the Products table as the parent:
<%
strSQL = "SHAPE Products APPEND " _
& "({SELECT * FROM Customers} AS Customers RELATE PID TO PID)"
%>
Reshaping saves a lot of headache since you can build off of your existing recordsets, without having to execute additional statements or send more information across a network.
File System Revisited
Now, in the situation where we have an unknown level of depth in a hierarchy, we have more options. It is no longer necessary to know ahead of time how many levels there are or which levels have children, or even what format the additional tables are. Using reshaping, we can start with one parent and its child, and later, if necessary, we can append another children or parents anywhere we wish. This could continue on ad infinitum. For example, here is a fictional scenario where we create a recordset with grandchildren, and then depending on some condition at the grandchild level, we may append another child onto the recordset and display further data:
<%
strConnectionString = _
"Provider=MSDataShape;dsn=MyDSN;database=MyDatabase"
strSQL = "SHAPE {SELECT DID, Name FROM tblRoot} APPEND ( " & _
"( SHAPE { SELECT SID, Name FROM tblSubDirectories } " & _
"As SubDirectory " & _
"APPEND ( {SELECT FID, Name FROM tblFiles} AS Files " & _
"RELATE SID TO SID) " & _
"RELATE DID TO DID)"
' Open original recordset
Set rst = Server.CreateObject("ADODB.Recordset")
rst.Open strSQL, strConnectionString
Do While Not rst.EOF
Response.Write(rst("Name") & "<br>")
' Set object to child recordset and iterate through
Set rstChild = rst("SubDirectory").Value
if not rstChild.EOF then
Do While Not rstChild.EOF
Response.Write(rstChild("Name") & "<br>")
if some condition then
strSQL = "SHAPE Files APPEND " _
& "( { SELECT * FROM tblMoreFiles } AS " _
& "MoreFiles RELATE FID TO FID)"
call AddChild(strSQL)
end if
rstChild.MoveNext
Loop
rst.MoveNext
end if
Loop
Function AddChild (strSQL)
dim blnChild, rst2
Set rst2 = Server.CreateObject("ADODB.Recordset")
rst2.open strSQL, strConnectionString
Response.write(rst2("Name") & "<br>")
blnChild = rst2("Child")
rst2.close
set rst2 = nothing
if blnChild then
AddChild(strSQL)
end if
End Function
%>
The above is a very simple example (which, by the way, should not be used without modification) of how we could use reshaping, but we can see how the recursive AddChild function can perform whatever tasks we need, without having to know anything ahead of time. Thus, we can start with a simple shape command, and delve as deep as we need to go at run time.
Conclusion
In the last article on data shaping, we showed you some great uses and how to implement data shaping effectively in your applications. In this article, hopefully, we have showed you how to be more creative with data shaping, and eventually see when it is inappropriate for the task at hand. Part of using data shaping effectively, or any technology for that matter, is knowing wisely when and where to use them.
Meanwhile, data shaping still applies to many different tasks - so the more you practice, the more prepared you will be.
