[firebird-support] Re: Storing headings and paragraphs in a Firebird database
> I have an extra column for the position within one and the same > hiararchy. > Thanks, I will try to do something similar. I will play around with this for a little while now!
Re: [firebird-support] Re: Storing headings and paragraphs in a Firebird database
I have an extra column for the position within one and the same hiararchy. > 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?
[firebird-support] Re: Storing headings and paragraphs in a Firebird database
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 http://www.firebirdsql.org/file/community/ppts/fbcon11/FBTrees2011.pdf 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!
Re: [firebird-support] Re: Storing headings and paragraphs in a Firebird database
> Could I ask how you have implemented the recursion - i.e. in the > client, using a CTE or with a stored procedure? In this particular case I am not using a recursion in SQL. I have another scenario where sales items are grouped and the groups' hierarchy can be changed dynamically by many concurrent users, and there I have used stored procedure in FB to find all items belonging to all n levels of subgroups of a certain group. Anyway I do favour fetching only those records the user currently can process, or is effectively looking at. Just received Helen's newbook and the first page I opened (464 in Vol. 2) says something about "who can process 200.000 rows at once anyway?". Exactly what I am trying to tell all my old dBase companions, but now that Helen writes it they'll probably start believing me :-) I am using nested "foreach..." loops in C#/ADO.net to open all sub-items of any given parent item. I am sure that Delphi/C++ et al will have the same options. 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...). 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. I keep a local storage for details already fetched from the database, so they won't be fetched again unless they have been changed by another user. I don't exactly know, by the way, what a CTE is. Something similar as a VIEW in Firebird, I guess. If you need to implement as much as possible in SQL, it is probably a good idea to check Global Temp Tables in FB 2.x upwards. > The reason for asking is that AFAIK the ordering in the result set > (from a CTE at least) is not defined. Therefore you could end up with:
[firebird-support] Re: Storing headings and paragraphs in a Firebird database
Thank you Andre, this is exactly what I was looking for! > Processing the hierarchy within one document is very easy using a > recursion. Could I ask how you have implemented the recursion - i.e. in the client, using a CTE or with a stored procedure? The reason for asking is that AFAIK the ordering in the result set (from a CTE at least) is not defined. Therefore you could end up with: Heading 1 Heading 1.3 Paragraph 1.3.2 Paragraph 1.3.1 Paragraph 1.3.3 Heading 1.2 ... etc. So my original idea was to use this kind of adjacency structure but with an "IDPredecessor" and/or "IDSuccessor" instead of an "IDParent" and a "Depth" field. But although this would be easy to access in a programming language such as 'C', in SQL I thought I would end up doing a fetch for every record to work out which is the next one. > I usually use a TreeView component to display to the user. Yes, I plan something similar or possibly Tree + Rich Text that is parsed for paragraph marks. > > I hope I did not misunderstand what your question was :-) > You understood it perfectly :)
