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 -> Paragraph bla bla bla
Record 5 -> Paragraph bla bla bla
Record 6 -> 1.1.2 Heading 3
Record 7 -> Paragraph bla bla bla
Record 8 -> 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:

- 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...

- IDParagraph (BigInt)
- TXParagraph (BLOB Type 1/80)

- 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.

Like this post? Share on: TwitterFacebookEmail

Related Articles


Firebird Community



Gems from Firebird Support list