[firebird-support] Storing headings and paragraphs in a Firebird database
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?
[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 :)
[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!
[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!
[firebird-support] Re: GUI version of FB Trace Manager (fbtracemgr)
The only problem that I have at the moment is that I am using the isc_info_svc_timeout command as part of a isc_info_svc_line isc_service_query() but instead of replying with isc_info_svc_timeout in the header the server replies with isc_info_svc_line but a zero length data block. IIRC, isc_info_svc_line (or isc_info_svc_to_eof) + zero data length means end of stream and service was stopped at server side. This might be my misunderstanding of the service was stopped at the server side but after receiving isc_info_svc_line + 0 I continue to receive isc_info_svc_line + valid data as soon some traceable event occurs so I don't think it has stopped? Then you should get isc_info_svc_timeout after isc_info_svc_line (with zero data). Do you process received buffer up to the isc_info_end ? Yes, but I never receive an isc_info_end, I only ever receive isc_info_svc_line. Would it help for me to post the code? But there is no formatting so it will probably difficult to read.
[firebird-support] Re: GUI version of FB Trace Manager (fbtracemgr)
--- In firebird-support@yahoogroups.com, certfb miker169@... wrote: The only problem that I have at the moment is that I am using the isc_info_svc_timeout command as part of a isc_info_svc_line isc_service_query() but instead of replying with isc_info_svc_timeout in the header the server replies with isc_info_svc_line but a zero length data block. IIRC, isc_info_svc_line (or isc_info_svc_to_eof) + zero data length means end of stream and service was stopped at server side. This might be my misunderstanding of the service was stopped at the server side but after receiving isc_info_svc_line + 0 I continue to receive isc_info_svc_line + valid data as soon some traceable event occurs so I don't think it has stopped? Then you should get isc_info_svc_timeout after isc_info_svc_line (with zero data). Do you process received buffer up to the isc_info_end ? Yes, but I never receive an isc_info_end, I only ever receive isc_info_svc_line. Would it help for me to post the code? But there is no formatting so it will probably difficult to read. *Oops*, please disregard the above, yes I do get an isc_info_svc_timeout, sorry and thank you for helping me track this down.
[firebird-support] GUI version of FB Trace Manager (fbtracemgr)
Hello list! I have implemented a very simple GUI version of the bundled trace tool fbtracemgr using wxWidgets. If anyone is interested I am more than happy to upload or send them the code. If I get the chance I will try to write a blog because some aspects of the trace API take a bit of digging to find the documentation ;) The only problem that I have at the moment is that I am using the isc_info_svc_timeout command as part of a isc_info_svc_line isc_service_query() but instead of replying with isc_info_svc_timeout in the header the server replies with isc_info_svc_line but a zero length data block. I looked through the source of fbtracemgr and although I think it expects isc_info_svc_timeout it probably doesn't give an error if it doesn't get it. It isn't particularly important because I can just detect 0 length lines (for a normal response even an empty line provides a '\n') but I wondered if anyone has any ideas about this?
[firebird-support] Re: GUI version of FB Trace Manager (fbtracemgr)
The only problem that I have at the moment is that I am using the isc_info_svc_timeout command as part of a isc_info_svc_line isc_service_query() but instead of replying with isc_info_svc_timeout in the header the server replies with isc_info_svc_line but a zero length data block. IIRC, isc_info_svc_line (or isc_info_svc_to_eof) + zero data length means end of stream and service was stopped at server side. This might be my misunderstanding of the service was stopped at the server side but after receiving isc_info_svc_line + 0 I continue to receive isc_info_svc_line + valid data as soon some traceable event occurs so I don't think it has stopped? Although I am running the query in a thread I need the timeout because isc_service_query() blocks until it is ready to send some data otherwise. BTW, fbtracemgr used isc_info_svc_to_eof query, not isc_info_svc_line. The latter will produce very low performance. Thanks for the tip, I will try to re-write to use isc_info_svc_to_eof. Because it is not an error. Errors reported via status-vector :) Got it! Re-read TraceSvcUtil::runService() carefully... Thanks I will do, it might take me some time because I am still learning, but I wanted to thank you for your reply quickly first.
[firebird-support] Re: fbtracemgr and embedded
since v2.5 it is possible to use Firebird embedded without install of full CS package. All you need is to create the almost the same folders layout as with full Firebird package, including bin, intl, lib, plugin, UDF folders and its contents. Create simlink lib/libfbembed.so.2.5 pointing to the lib/libfbembed.so.2.5.2 Remove not needed binaries from bin folder, if necessary Set FIREBIRD to the root folder. Set LD_LIBRARY_PATH to the lib folder (not to the root !) Thank you Vlad!! I have followed these steps and it is working perfectly. Actually the symlink was already present. The only other thing I had to do was to delete the /tmp/firebird directory because this did not have user write privileges having been created with the install.sh script.
[firebird-support] Re: fbtracemgr and embedded
Sorry for the (possibly) silly question, but is it possible to use fbtracemgr with the embedded server? Yes Thank you for your answers, that is great news. By the way my purpose for using tracing is to be able to see a how parametrised selects created with IBPP are resolved. ...what would I replace the 'remote_host:service_mgr' with? If you want to trace local database, just use service_mgr and ensure fbtracemgr used fbembed[.dll|.so] instead of fbclient. Sorry I should have said that I am using CS 2.5.2 on Ubuntu 12.10. I have set up fbtracemgr to use fbemded by setting the following environment variables: export FIREBIRD=/home/myhome/experiments/firebird export LD_LIBRARY_PATH=/home/myhome/experiments/firebird I have tried putting libfbtrace.so in $FIREBIRD, $FIREBIRD/plugins and even tried /opt/firebird/plugins but fbtracemgr cannot find the plugin, Can not start trace session. There are no trace plugins loaded Thanks again!
[firebird-support] Re: fbtracemgr and embedded
--- In firebird-support@yahoogroups.com, Thomas Steinmaurer ts@... wrote: Sorry for the (possibly) silly question, but is it possible to use fbtracemgr with the embedded server? Yes Thank you for your answers, that is great news. By the way my purpose for using tracing is to be able to see a how parametrised selects created with IBPP are resolved. ...what would I replace the 'remote_host:service_mgr' with? If you want to trace local database, just use service_mgr and ensure fbtracemgr used fbembed[.dll|.so] instead of fbclient. Sorry I should have said that I am using CS 2.5.2 on Ubuntu 12.10. I have set up fbtracemgr to use fbemded by setting the following environment variables: export FIREBIRD=/home/myhome/experiments/firebird export LD_LIBRARY_PATH=/home/myhome/experiments/firebird I have tried putting libfbtrace.so in $FIREBIRD, $FIREBIRD/plugins and even tried /opt/firebird/plugins but fbtracemgr cannot find the plugin, Can not start trace session. There are no trace plugins loaded Is there a particular reason of using Embedded instead of a regular server for your tracing scenario? -- With regards, Thomas Steinmaurer http://www.upscene.com/ Thomas, thanks for your comment. I am making a very basic piece of software using wxWidgets and IBPP that I want to be easily deployable without having to install the server, check firewalls etc. You might think that sqlite or similar is more appropriate but I am happy with Firebird and don't want to preclude scaling things to client server at some point down the road. I have successfully used fbtracemgr before with the normal server, but this is my first attempt at using it with the embedded server which has been setup following the instructions at http://www.firebirdfaq.org/Firebird-Embedded-Linux-HOWTO.html Kind regards
