Wednesday, October 31, 2012

Only EBFs for the latest fully-supported versions of SQL Anywhere are shown here: 11.0.1 and 12.0.1.

Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new EBFs released.

Monday, October 29, 2012

Only EBFs for the latest fully-supported versions of SQL Anywhere are shown here: 11.0.1 and 12.0.1.

Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new EBFs released.

Wednesday, October 17, 2012

Only EBFs for the latest fully-supported versions of SQL Anywhere are shown here: 11.0.1 and 12.0.1.

Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new EBFs released.

Monday, October 15, 2012

Once upon a time, sorting was a basic building block for many computer programs. Both RAM and disk drives were limited and expensive... sequential I/O with magnetic tape was used for almost everything, plus the ubiquitous sort/merge utility.

And so it came to pass that many techniques were developed to exploit the sorting process, techniques that are sometimes useful today when it comes time to exploit the ORDER BY clause.

The previous article about breadth-first and depth-first tree traversals told of one such technique: building strings for the sole purpose of sorting.

This article tells the story of another such technique: inverting values before building the sort string.

Question: How do I perform breadth-first and depth-first traversals of a hierarchical table when the left-to-right order is given by column values that are descending rather than ascending?

Here's the tree structure table for "Jordan's Company" from the previous article about breadth-first and depth-first tree traversals:

CREATE FUNCTION invert_name

The solution is to "invert" the characters in the employee.name column so that each value will be appear in descending order when the lineage column is sorted in ascending order... this has a profoundly different effect than just sorting the lineage string in descending order.

The invert_name() function changes A to z, B to y, C to x and so on for upper case characters, and a to Z, b to Y, c to X and so on for lower case characters. For example, invert_name ( 'Jordan' ) returns 'qLIWZM', and invert_name ( 'qLIWZM' ) returns 'Jordan'.

Note: Different "invert_xxx" functions may be required depending on the semantics of the column used for left-to-right ordering. For example, the following code assumes that employee.name contains only mixed-case English letters with no punctuation or special characters at all. Other data types (e.g., numeric, timestamp, etc.) may need different logic altogether; an example is shown later in this article.

Salary Descending Left-to-Right

Here's the same data as before, with one change; the employee.salary values have been modified so their descending values determine the left-to-right ordering.
In other words, the left-to-right ordering of rows with the same parent is determined by the descending values of salary:

Friday, October 12, 2012

Question: How do I perform a breadth-first traversal of a hierarchical table? In other words, how do I write a SQL Anywhere query that returns all the rows of a tree-structured table in left-to-right, top-to-bottom order?

Next Question: How do I perform a depth-first traversal (top-to-bottom, left-to-right) of the same table?

Here's a diagram showing the primary keys for a tree-structured table:

SQL queries don't have any natural order other than what you specify in an ORDER BY clause, and clearly the numbers 1, 2, 3 aren't enough to determine either vertical (1 - 93 - 11) or horizontal ( 27 - 26 - 25) ordering. In other words, the table needs more than just a primary key:

node_id is a random primary key... not really "random" in this example because that would make the diagram hard to read, but just a "little" random: 93 appears below 1 but above 11, and while 18 appears to the left of 19, 21 comes before 20.

parent_id specifies top-to-bottom ordering; it contains the node_id of the row above this one, except for the top row which has parent_id = node_id.

sibling_number specifies the left-to-right ordering of rows with the same parent_id.

Columns like node_id and parent_id often appear in hierarchical tables, but sibling_id is something else. If left-to-right ordering is important in a hierarchical table (it often isn't) there must be one or more columns that can be used in an ORDER BY to get that result. Sometimes rows are sorted left-to-right in alphabetic order, sometimes in date/time order, maybe a line number, maybe even an autoincrement... in this example sibling_number is used to make the column's purpose absolutely clear.

The point is, if top-to-bottom and left-to-right ordering is to make any sense, there has to be something in the data (like parent_id and sibling_number) to represent it.

Here's what the data looks like for this table, with the diagram modified to show the node_id,sibling_number pairs for each row:

Note: The sibling_number for the root node is set to 9 instead of the "more natural value" 1 to show that sibling_number has no role to play in determining top-to-bottom ordering, only left-to-right.

Now, you can take my word...

...that no simple queries exist for either breadth-first or depth-first result sets, nothing in the form

SELECT name
FROM employee
ORDER BY [column-name-list];

Or, you can take a moment to experiment... go ahead, I'll wait :)

Starting Point: Top-Down Traversal

The first step is to write a building block for future efforts: a query that does a top-to-bottom traversal without regard to the left-to-right ordering.

The following RECURSIVE UNION query uses parent_id to calculat a new column, level, which contains the vertical level number 1, 2, 3, 4.

The first SELECT on lines 6 through 11 starts the ball rolling by returning the single root row:

level, node_id, parent_id, sibling_number
1, 1, 1, 9

The second SELECT on lines 13 through 20 recursively traverses all the rows underneath Jordan:

The FROM clause on lines 17 through 19 joins the employee table with all the rows that have already been selected for the top_down_traversal result; this is the "recursive" part, a reference inside a query to the outer query itself.

the ON clause on line 19 makes sure each new row selected from employee is exactly one level down from its parent row in top_down_traversal.

the expression top_down_traversal.level + 1 on line 13 calculates the level number (one level down) for the new row selected from employee, and

the WHERE clause on line 20 excludes the root "1" row because it was added by the first SELECT.

Answer 1: Breadth-First Traversal

ORDER BY level gives the top-to-bottom order for all the rows, and ORDER BY sibling_number gives the left-to-right order for rows with the same parent, but what about traversing all the rows in breadth-first order?

What's needed is a kind of "variable ORDER BY", one that includes the sibling_number for all the rows in the current row's lineage, all the way from the top of the hierarchy down to the current row: ORDER BY level, sibling_number, sibling_number, sibling_number, ...

It would look like ORDER BY 1, 9 for the root row, ORDER BY 2, 9, 1 for row 2, ORDER BY 2, 9, 2 for row 93 and so on.

Here's what the variable ORDER BY would look like for all the rows, if it was possible to code such a thing in SQL:

Variable ORDER BY clauses...

...aren't possible in SQL Anywhere, but variable strings are.

The following query adds one more column to the result set: lineage contains a formatted string consisting of the sibling_numbers from all the ancestors to this one. Each sibling_number is padded on the left with zeros to deal with the fact that strings are sorted from left to right, and dashes "-" are used as separators for clarity.

Test Case: Jordan's Company

Ainslie's company is not a good example for testing and debugging because the data is unrealistic; i.e., both of these simple queries work as breadth-first traversals without all the fuss and muss of a RECURSIVE UNION:

SELECT *
FROM employee
ORDER BY employee_id;
SELECT *
FROM employee
ORDER BY name;

Here's a better example; same names but shuffled around, and random values used for employee_id:

Friday, October 5, 2012

Only EBFs for the latest fully-supported versions of SQL Anywhere are shown here: 11.0.1 and 12.0.1.

Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new EBFs released.