Denormalized Page Hierarchy

Callstack:
    at User:arnec/Denormalized_Page_Hierarchy

MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException: reference to undefined name 'math' Exception of type 'MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException' was thrown.
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptVar expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Expr.DekiScriptVar.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Evaluate (MindTouch.Deki.Script.Expr.DekiScriptAccess expr, DekiScriptExpressionEvaluationState state, Boolean evaluateProperties) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptAccess expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Expr.DekiScriptAccess.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptCall expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Expr.DekiScriptCall.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptReturnScope expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 

Data Retrieval

Selecting all children of any page is accomplished via:

select page_child_id 
  from page_children 
  where page_id = {page_id};

Retrieving the page parents (including depth of each ancestor) is accomplished via:

select pc.page_id, pl.level
  from page_children as pc
    left join page_level as pl
      on pc.page_id = pl.page_id
  where pc.page_child_id = {page_id}
  order by level;

Data Insertion (Page create)

Creating a new page requires knowning the parent_page_id and page_id:

insert into page_level
  select {page_id},[level]+1
    from page_level
    where page_id = {parent_page_id};
insert into page_children values({parent_page_id},{page_id});
insert into page_children
  select page_id,{page_id} as page_child_id
    from page_children where page_child_id = {parent_page_id};

Data Update (Page move)

coming soon

Data Deletion (Page deletion)

This example assumes that the page has no children:

delete from page_level where page_id = {page_id};
delete from page_children where page_child_id = {page_id};

For the complete scenario, all children of the page need to be selected, ordered by level and the above deletes cascaded for all children, deepest level first.