miker169 wrote:
This is probably a question for Stack Overflow, but I would like a Firebird specific answer so asking here.
I would like to store simple documents in a Firebird database as an ordered collection of paragraphs as follows:
Document 1 Record 1 -> 1. Heading 1 Record 2 -> 1.1 Heading 2 Record 3 -> 1.1.1 Heading 3 Record 4 -> 1.1.1.1 Paragraph bla bla bla Record 5 -> 1.1.1.2 Paragraph bla bla bla Record 6 -> 1.1.2 Heading 3 Record 7 -> 1.1.2.1 Paragraph bla bla bla Record 8 -> 1.1.2.2 Paragraph bla bla bla Record 9 -> 2. Heading 1 Record 10 -> 2.1 Heading 2 ... Document 2 Record n -> 1. Heading 1 Record n + 1 -> 1.1 Heading 2
and so on.
I would like concurrent access to different paragraphs of the document and each record can link to paragraph(s) (records) in another 'document'. Therefore I want to move away from creating the document in XML and storing it in a BLOB.
I think CTE is out because I can't determine the order of siblings and nested sets are not good for doing a lot of inserts (as is likely when writing documents).
Probably the obvious way is to use path enumeration, but I guess this is a relatively common problem (although googling doesn't find many results) and wondered if anyone has a good solution?
André Knappstein answers:
I have realized a solution for a similar problem, but since I am more a power user than a database professional I can't tell if this is really a good solution. I can only tell that it works nice for our company (some 50 concurrent users, documents usually consist of some 25 Headings with some 1 - 15 sub paragraphs).
From scratch, basically I do have 3 tables:
T_Index: - IDParagraph (BigInt) - IDParent (BigInt) // Uplink to parent paragraph, NULL if on 1st level - IDDocument (BigInt) // The "document" as such - IDType (SmallInt) // e.g. Heading, sub heading, title... - IDStatus (SmallInt) // e.g. confirmed, pending... T_Content - IDParagraph (BigInt) - TXParagraph (BLOB Type 1/80) T_HyperLinks - IDHyperlink (BigInt) // For further annotations in other tables... - IDParagraph (BigInt) // Source paragraph for a hyper link - IDTarget (BigInt) // Target paragraph for a hyper link
I do - of course - have more tables in the system. For example I have comments on the hyperlinks, qualifications of hyperlinks, modification protocol (who changed what and when...), some tables for results of parsing (to find keywords in a range of documents) etc.
Also you can have keywords pointing to documents, documents to documents, paragraphs to documents...
Processing the hierarchy within one document is very easy using a recursion. I usually use a TreeView component to display to the user.
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 new book 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...).
I have an extra column for the position within one and the same hiararchy.
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.