Subject Re: Storing headings and paragraphs in a Firebird database
Author certfb
Thank you very much for taking the time to explain this in such clear detail.

> This virtually means I have one query sent to the FB server for each hierarchy level, constrained to paragraphs having the current paragraph as parent (WHERE IDParent = :IDThisParagraph...).

Can I ask, do you have a separate 'sort' field so the paragraphs come out in the right order or are the ID's ordered to support this?

> The TreeItems/Nodes have a custom property "IDParagraph", and so in the adjacent "Details" screen I can load all details for one paragraph, move it up and down the hierarchy, delete, modify it, add new paragraphs etc.

Great, this nicely keeps a light structure separate from the heavy payload. I would like to do this too.

> I don't exactly know, by the way, what a CTE is.

Its a Common Table Expression and they support recursion inside a query(so server side). I am only just learning about them, but they have been in Firebird since 2.1 (I think)and were covered in the Version 2.1.3 supplement to Helen's old book. I haven't got the new book yet (as I am hoping for a Kindle version) but I am sure they will be in there.

The problem with CTE (according to this presentation is that although the hierarchy is maintained, siblings in the result set can be in any order.

I really appreciate you sharing your experiences of this!