[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 :)
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 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
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
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: Rename database command
Em 5/6/2013 20:01, sir_wally_lewis escreveu: Hi Sean, While I don't pretend to understand Firebird at the atomic level. I am just trying to cope with database slowdowns. We find the only bullet proof methodology to solve database slowdowns is a backup restore. So we are searching for a method to be able to resolve database slowdowns, while keeping the database online. I am not concerned with whether theoretically firebird should or should not require a backup/restore. It seems that in practice under large database conditions to be a requirement. Our networks guy is going to spend some time seeing if he can give evidence of this requirement. Of course we will try any method to attempt to resolve this. In the past we have not found sweeping the database to help, but we will continue to do everything we can to resolve this for our customers sake. Kind Regards, Robert Robert, Maybe you (or the person responsible for the application code) needs to understand the Firebird principles (MGA) this way you could understand why the system become slow and fix the reason. I don't think this is understand Firebird at atomic level, but to understand why some coding mistakes could lead to a system slow down and how to fix it. Look at the cause not at the symptom... If you manage your transactions properly I doubt you will need a back-up/restore cycle every week to keep the system responsive. You said that sweep did not fix the problem, but in what circustances was the sweep run ? If your application keeps a transaction open for a long time (what I think is the case) running sweep could not help that much, but if you run sweep when no one is connected I think the result will be the same (as I said previously the restore process will do more than a sweep, but I think it's not necessary) as running a back-up/restore cycle. The transaction counter is a 32bit integer and when will get close to this limit you will need to perform a restore to reset it (as the restore creates a new database, the transaction counter will start from 0 again). To remove garbage and move forward the OIT the sweep is all you need, as long when you run sweep there is no transaction open for hours. see you !
Re: [firebird-support] TRACE
pologies if it was in that took the message, the Firebird version is 2.5, model is needed to determine the relationship of the database, you have an application that uses firebird but there is no technical information about this, we need to determine which tables and fields involved in a given process, thanks. 2013/6/6 Leyne, Sean s...@broadviewsoftware.com ** Marco, HOW TO TRACE FIREBIRD, THANK. 1- First, no need to SHOUT! 2- What Firebird version are you running? 3- What are you looking to trace? 4- Have you reviewed the release notes for details of the new Trace API? Sean -- *A.C.T.I.V.A. Business Consulting and TIC.* Sucuzhañay Lema Marco Vinicio Móvil: 09 2 432 945 / 08 3 015 499 Home: 02 2 611 885 Quito - Ecuador [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
[firebird-support] Many Items in Many Locations
I am building an image database and want to know locations of all copies of images. An image can be displayed in one or more locations: a web page, a show, a book, a commercial gallery, someone's residence, etc. Any of these locations can have multiple images. How is this best tracked, recorded and returned as a query result? One solution is to create a table called ImageLocs, having foreign key references to each image and to each image's location. I cannot get past the idea of a separate table for each location type: gallery, show, book, magazine, web page, residence (the latter with a FK link to the contacts table), etc. Each location table contains a location name (show or book title, etc) and foreign keys to the image table and to the location table(s). But then, ImageLocs will have lots of blank, foreign key cells, because only one or very few locations will contain any given image. For example, if I publish a book with 100 images, ImageLocs will have records containing that book's primary key (PK) 100 times, alongside the PKs of each of its images, etc. Seems redundant, or is this the standard practice? Or is there a better way?
Re: [firebird-support] Many Items in Many Locations
Em 7/6/2013 01:37, jwcane2003 escreveu: I am building an image database and want to know locations of all copies of images. An image can be displayed in one or more locations: a web page, a show, a book, a commercial gallery, someone's residence, etc. Any of these locations can have multiple images. How is this best tracked, recorded and returned as a query result? One solution is to create a table called ImageLocs, having foreign key references to each image and to each image's location. I cannot get past the idea of a separate table for each location type: gallery, show, book, magazine, web page, residence (the latter with a FK link to the contacts table), etc. Each location table contains a location name (show or book title, etc) and foreign keys to the image table and to the location table(s). But then, ImageLocs will have lots of blank, foreign key cells, because only one or very few locations will contain any given image. For example, if I publish a book with 100 images, ImageLocs will have records containing that book's primary key (PK) 100 times, alongside the PKs of each of its images, etc. Seems redundant, or is this the standard practice? Or is there a better way? Hi ! Perhaps I misunderstand your message, but I will answer as I understood it. I think you could have something like: Images (Table) ImageID integer (PK) Image BLOB Name varchar Locations (Table) LocationID integer (PK) Description varchar ImageLocation (Table) ImageLocationID integer (PK) ImageID integer (FK to Images) LocationID integer (FK to Locations) AdditionalInfo varchar If you want to show all the locations you have an image you will need a query like this: select I.ImageID, I.Name, L.Description, IL.AdditionalInfo from Images I join ImageLocations IL on (IL.ImageID = I.ImageID) join Locations L on (L.LocationID = IL.LocationID) where I.ImageID = ? If you want to show all the images for a desired location you will need a query like this: select I.ImageID, I.Name, L.Description, IL.AdditionalInfo from Images I join ImageLocations IL on (IL.ImageID = I.ImageID) join Locations L on (L.LocationID = IL.LocationID) where L.LocationID = ? It's a classic many to many relationship. Did I missed something ? Of course you could have additional colluns like Type to indicate if it is a Book, a WebSite and so on. It could be a typed column (Integer) or you could have another table. I would go with another table... see you !
