Hierarchical Data in SQL Part 2: Recursive Queries with an Adjacency List

In the first post of this series, we saw a simple table representing a real-world hierarchy and the simplest of queries on that data, and finished with the question: “What else might we do with this data?”

Maybe we need to know the chain of responsibility leading from a particular position up to the top of the organisation.
Or, perhaps we would like to report on a subsection of the tree – for example, listing everyone working directly or indirectly below the Financial Director.
And what if we want to see what this organisational structure looks like?

For all of these, we can use recursive queries.
Continue reading Hierarchical Data in SQL Part 2: Recursive Queries with an Adjacency List

Hierarchical Data in SQL Part 1: The Adjacency List

One of the most obvious examples of hierarchical data frequently stored in a database is the organisational hierarchy of employees and their managers.

Typically this follows a one-to-many parent-child relationship whereby each employee reports to a single manager above them, and may have zero or more employees below them who they manage.
This tree is likely to have a relatively shallow structure, perhaps no more than 10 levels deep even in an organisation of 100,000 employees.

The simplest method of storing and maintaining a tree of this type is for each record to include a reference to its parent.
You may see this referred to as an “Adjacency List Model”.
It is worth noting that an adjacency list is a type of Relationship Graph, and that our example is a special case in which we want the graph to form a tree structure.

Representing a tree in this way allows us to easily write simple queries to access the nodes immediately adjacent to a given node (above or below them in the hierarchy), but requires us to use recursion if we wish to query data spanning further levels of the tree.
Continue reading Hierarchical Data in SQL Part 1: The Adjacency List

Working With Hierarchical Data in SQL

Over a series of articles, I will be writing about and demonstrating some of the ways in which hierarchical data can be stored, queried and presented using SQL. The primary focus will be Microsoft SQL Server, followed by Oracle, but there may also be some sections dedicated to other platforms.

I will update this page with links to the subsequent posts as and when they are added.

Continue reading:

ORA-600 Error in a Partitioned Join

In a previous post, I wrote about partitioned outer joins in Oracle.
The simple schema and queries in that post are similar to those I used when first learning about this feature. At the time, I came upon an unexpected error which caused some Oracle instances to throw an ORA-600 error from a very simple query.

The original test environment I had been using was Oracle Database 11g Enterprise Edition running on Solaris.
I had created a simple schema to experiment with, and written a couple of queries which returned results designed to illustrate the effect of a partitioned join as compared with other types of joins.
Once I had them doing what I wanted, I decided to discuss them with a colleague, who tried running the on his local instance of Oracle Express on Windows.
Instead of the expected results, he received the following error:
ORA-00600: internal error code, arguments: [kkoipt:invalid aptyp], [1], [0], [], []

Continue reading ORA-600 Error in a Partitioned Join

C# Constructor Gotchas Part 2: Singletons and the Sequence of Initialization

One of my favourite questions to ask interviewees is, “What is the order of initialization in a C# class?”

This is part of a series of posts featuring simple but informative code samples.
You can find a bit of background information and a list of the other posts in the series here.

If the question is too ambiguous, then to be more specific: place the following items in a list according to the order in which they are executed during initialization of a class in C#: static and instance class constructors, and static and instance field initializers.

The simple answer, ignoring inheritance, is:

Continue reading C# Constructor Gotchas Part 2: Singletons and the Sequence of Initialization

C# Constructor Gotchas Part 1: Calling virtual methods from a constructor

What could go wrong if we call an overridable method from within a constructor?

If you’ve used static code analysis tools then you may have come across a warning instructing you not to call virtual methods in constructors.
This particular warning is one that should not be ignored, and nor should it be suppressed without good reason. It is of particular relevance to those moving to C# from some other object oriented languages which do not behave in quite the same way (more on that later). Continue reading C# Constructor Gotchas Part 1: Calling virtual methods from a constructor

iOS7 fonts used for Japanese text

Is your iPhone using the correct default font for Japanese text?

Today I want to tell you about a little quirk of the system which I encountered when playing with iOS7.

With successive releases of iOS, Apple occasionally add new fonts to the system. Aside from app developers, it is something to which most of us are probably oblivious. There aren’t that many apps where a user has the need or opportunity to select a font (iBooks being an exception). Probably the majority of developers opt to stick with the default system font, which offers a clean and simple look and ensures their UI looks and feels like it belongs on the platform.

I don’t know if this was the case in earlier iOS versions, but there are currently different default system fonts used for each out of Chinese, Japanese and Korean.
When an iPhone displays Chinese, Japanese or Korean text, it will begin by looking in the current system default font for the required character. If it doesn’t find it, then it seems to fall back to the default system font specific to the language with which that character is primarily associated.

The default system font is changed when switching between the three languages, and because all three fonts contain the Japanese Hiragana and Katakana characters, Japanese text will appear different when the iPhone system language is set to Chinese or Korean.

All pretty straightforward so far, but… if you happen to be using your phone to display Japanese text, while the system default language is set to anything other than Chinese, Japanese or Korean, which font does it choose?
Continue reading iOS7 fonts used for Japanese text

ORA-29284 File Read Error with UTL_FILE.GET_RAW

UTL_FILE.GET_RAW fails on long lines if the file is opened in text mode.

Today while debugging a previously developed PL/SQL package, I had to investigate an ORA-29284: file read error.
The cause was not immediately apparent, as the procedure had been reading in and parsing other files ok, and had read in this particular file just fine up until it reached a certain point.
The file being read was text rather than binary, but the code was using GET_RAW not GET_LINE since the process was not interested in line breaks. With a little investigation, it turned out that the place where the error occurred was when the procedure began trying to read from a line which was 175,000 characters long.

If you’ve used the functions in UTL_FILE, then you’ll know that functions like GET_LINE support a maximum line size and buffer size of 32767 bytes. However in this case, the call to GET_RAW was specifying a much smaller number of bytes to read. So why did it fail? Continue reading ORA-29284 File Read Error with UTL_FILE.GET_RAW

Software Development in C#, .NET and SQL