How would you implement a record-level versioning system with Derby and Java?
Hi Guys, I'm completely new to Derby and I evaluating it as a possibile embedded RDBMS for a Java/Swing application that I'm goig to develop. I'm currently following the tutorials at IBM DeveloperWorks (http://tinyurl.com/6mlwpj) and I have ordered the IBM book at Amazon's already (http://tinyurl.com/ln3cas). I have a stimulating question for you all (I apologize if this topic has been discussed already): how would you implement a system to keep track of any old version of a record (row) of your database? I'm asking this because I need a system that will allow the user to see any old version of a record and to undo/redo any change with just a couple of mouse clicks. Any idea? Any suggestion? PS: No, I'm not planning to use any kind of ORM (Hibernate or similar). Should I? -- Alessandro Bottoni Website: http://www.alessandrobottoni.it/ Education is an admirable thing, but it is well to remember from time to time that nothing that is worth knowing can be taught. -- Oscar Wilde
Re: How would you implement a record-level versioning system with Derby and Java?
On Thu, Jul 9, 2009 at 9:03 AM, Alessandro Bottonialexbott...@yahoo.it wrote: Hi Guys, I'm completely new to Derby and I evaluating it as a possibile embedded RDBMS for a Java/Swing application that I'm goig to develop. I'm currently following the tutorials at IBM DeveloperWorks (http://tinyurl.com/6mlwpj) and I have ordered the IBM book at Amazon's already (http://tinyurl.com/ln3cas). I have a stimulating question for you all (I apologize if this topic has been discussed already): how would you implement a system to keep track of any old version of a record (row) of your database? I'm asking this because I need a system that will allow the user to see any old version of a record and to undo/redo any change with just a couple of mouse clicks. Any idea? Any suggestion? Actually, this is a question that I have previously considered for a project that I am starting. My thought was to have an identity column (value assigned by the DBMS) and a separate ID column. From here, you can do several different things: 1. Have a version column. Use the max function to find the current version, or make the current version -1 or something else easy to find. 2. Use the max function on the identity column to find the latest version. 3. Have previous and next columns with the identity values of the appropriate objects in the chain. The current version would have a null next. There are probably other approaches that I haven't thought of. PS: No, I'm not planning to use any kind of ORM (Hibernate or similar). Should I? I have used and recommend Hibernate. It simplifies things immensely IMO, though it does have its quirks (some of which are not properly explained by the documentation). It also works well with a number of other DBMS (if changing is something that might happen later). - Family photographs are a critical legacy for ourselves and our descendants. Protect that legacy with a digital backup and recovery plan.
Re: How would you implement a record-level versioning system with Derby and Java?
Actually I would store the history values outside the main table for performance and storage overhead reasons (history table may have additional columns, e.g. When-who-etc for auditing information). Peter 2009/7/9, Donald McLean dmclea...@gmail.com: On Thu, Jul 9, 2009 at 9:03 AM, Alessandro Bottonialexbott...@yahoo.it wrote: Hi Guys, I'm completely new to Derby and I evaluating it as a possibile embedded RDBMS for a Java/Swing application that I'm goig to develop. I'm currently following the tutorials at IBM DeveloperWorks (http://tinyurl.com/6mlwpj) and I have ordered the IBM book at Amazon's already (http://tinyurl.com/ln3cas). I have a stimulating question for you all (I apologize if this topic has been discussed already): how would you implement a system to keep track of any old version of a record (row) of your database? I'm asking this because I need a system that will allow the user to see any old version of a record and to undo/redo any change with just a couple of mouse clicks. Any idea? Any suggestion? Actually, this is a question that I have previously considered for a project that I am starting. My thought was to have an identity column (value assigned by the DBMS) and a separate ID column. From here, you can do several different things: 1. Have a version column. Use the max function to find the current version, or make the current version -1 or something else easy to find. 2. Use the max function on the identity column to find the latest version. 3. Have previous and next columns with the identity values of the appropriate objects in the chain. The current version would have a null next. There are probably other approaches that I haven't thought of. PS: No, I'm not planning to use any kind of ORM (Hibernate or similar). Should I? I have used and recommend Hibernate. It simplifies things immensely IMO, though it does have its quirks (some of which are not properly explained by the documentation). It also works well with a number of other DBMS (if changing is something that might happen later). - Family photographs are a critical legacy for ourselves and our descendants. Protect that legacy with a digital backup and recovery plan.
Re: How would you implement a record-level versioning system with Derby and Java?
Donald McLean ha scritto: Actually, this is a question that I have previously considered for a project that I am starting. My thought was to have an identity column (value assigned by the DBMS) and a separate ID column. From here, you can do several different things: 1. Have a version column. Use the max function to find the current version, or make the current version -1 or something else easy to find. 2. Use the max function on the identity column to find the latest version. I thought about this mechanism but I'm not completely convinced it would be the right one. I mean, there are two different user views to take into account: 1) The user view onto the whole database while browsing/searching for a specific record. 2) The single-record view used to examine/edit a specific record. While it is easy to use a SELECT with MAX to get the latest version of a specific record, it is not clear how I could get only the most recent version of every single record to build up the browse view. It looks like I should use a nested SELECT: first, select all matching records and, second, select the latest version of each of them. A little bit messy... 3. Have previous and next columns with the identity values of the appropriate objects in the chain. The current version would have a null next. Nice approach! Actually, I did not think about this possibility. Many thanks for this suggestion. This would simplify a lot the SELECT operation used to generate the browse view (just select all of the records that have a NULL next-version field) while maintaining the possibility to go back and forth between versions (and build up a list of them, if needed). I think I will experiment with this one. There are probably other approaches that I haven't thought of. I thought about triggers/procedures, as well, but I had the feeling they would have the same problem as the SELECT + MAX approach. I have used and recommend Hibernate. It simplifies things immensely IMO, though it does have its quirks (some of which are not properly explained by the documentation). It also works well with a number of other DBMS (if changing is something that might happen later). Thanks. I'm new to Hibernate, as well, so I'm going to ask you (all) which book is best for learning Hibernate. I browsed a little bit on Amazon's but I did not find anything to be enthusiastic about. What would you suggest me as a good starting point/introductory book? Any good web article/tutorial to signal, as well? Thanks again. -- Alessandro Bottoni Website: http://www.alessandrobottoni.it/ In mathematics you don't understand things. You just get used to them. -- John von Neumann
Re: How would you implement a record-level versioning system with Derby and Java?
Peter Ondruška ha scritto: Actually I would store the history values outside the main table for performance and storage overhead reasons (history table may have additional columns, e.g. When-who-etc for auditing information). Peter Yep... This has to be kept into account, actually, and it makes the whole thing a little bit more complicated... Thanks for this suggestion, Peter. -- Alessandro Bottoni Website: http://www.alessandrobottoni.it/ Who wants to remember that escape-x-alt-control-left shift-b puts you into super-edit-debug-compile mode? -- (Discussion in comp.os.linux.misc on the intuitiveness of commands, especially Emacs.)
RE: How would you implement a record-level versioning system with Derby and Java?
Hi Alessandro, We've implemented an approach similar to this for storing and tracking changes to XML documents - because the values we are storing could be large (base 64-encoded binary objects), we decided to have a 'attribute-value' table that would only allow inserts, plus an 'audit-record' table that tracks when/where/who changed the value. This approach works reasonably well for moderate sized documents (1MB / 100,000 values/changes), but it starts to slow down as the number of records/changes exceed 1,000,000. In Oracle and SQL Server, we implemented a VIEW on the attribute-value/audit-record table to only show the most recent value -- makes it easier/faster to get the latest value. Value history is easily retrieved by selecting all values from the attribute-value table with the same unique attribute-id. We haven't optimized our code for Derby, so haven't tried a similar approach here. Best Regards, Thomas Taylor -Original Message- From: Alessandro Bottoni [mailto:alexbott...@yahoo.it] Sent: Thursday, July 09, 2009 10:48 AM To: Derby Discussion Subject: Re: How would you implement a record-level versioning system with Derby and Java? Peter Ondru�ka ha scritto: Actually I would store the history values outside the main table for performance and storage overhead reasons (history table may have additional columns, e.g. When-who-etc for auditing information). Peter Yep... This has to be kept into account, actually, and it makes the whole thing a little bit more complicated... Thanks for this suggestion, Peter. -- Alessandro Bottoni Website: http://www.alessandrobottoni.it/ Who wants to remember that escape-x-alt-control-left shift-b puts you into super-edit-debug-compile mode? -- (Discussion in comp.os.linux.misc on the intuitiveness of commands, especially Emacs.)
Re: How would you implement a record-level versioning system with Derby and Java?
I would recommend also looking at some data warehousing articles as versioning is related to that. Though I do not have any concrete to recommend :-(. Peter 2009/7/9, Alessandro Bottoni alexbott...@yahoo.it: Donald McLean ha scritto: Actually, this is a question that I have previously considered for a project that I am starting. My thought was to have an identity column (value assigned by the DBMS) and a separate ID column. From here, you can do several different things: 1. Have a version column. Use the max function to find the current version, or make the current version -1 or something else easy to find. 2. Use the max function on the identity column to find the latest version. I thought about this mechanism but I'm not completely convinced it would be the right one. I mean, there are two different user views to take into account: 1) The user view onto the whole database while browsing/searching for a specific record. 2) The single-record view used to examine/edit a specific record. While it is easy to use a SELECT with MAX to get the latest version of a specific record, it is not clear how I could get only the most recent version of every single record to build up the browse view. It looks like I should use a nested SELECT: first, select all matching records and, second, select the latest version of each of them. A little bit messy... 3. Have previous and next columns with the identity values of the appropriate objects in the chain. The current version would have a null next. Nice approach! Actually, I did not think about this possibility. Many thanks for this suggestion. This would simplify a lot the SELECT operation used to generate the browse view (just select all of the records that have a NULL next-version field) while maintaining the possibility to go back and forth between versions (and build up a list of them, if needed). I think I will experiment with this one. There are probably other approaches that I haven't thought of. I thought about triggers/procedures, as well, but I had the feeling they would have the same problem as the SELECT + MAX approach. I have used and recommend Hibernate. It simplifies things immensely IMO, though it does have its quirks (some of which are not properly explained by the documentation). It also works well with a number of other DBMS (if changing is something that might happen later). Thanks. I'm new to Hibernate, as well, so I'm going to ask you (all) which book is best for learning Hibernate. I browsed a little bit on Amazon's but I did not find anything to be enthusiastic about. What would you suggest me as a good starting point/introductory book? Any good web article/tutorial to signal, as well? Thanks again. -- Alessandro Bottoni Website: http://www.alessandrobottoni.it/ In mathematics you don't understand things. You just get used to them. -- John von Neumann
Re: How would you implement a record-level versioning system with Derby and Java?
On Thu, Jul 9, 2009 at 10:44 AM, Alessandro Bottonialexbott...@yahoo.it wrote: Donald McLean ha scritto: I have used and recommend Hibernate. It simplifies things immensely IMO, though it does have its quirks (some of which are not properly explained by the documentation). It also works well with a number of other DBMS (if changing is something that might happen later). Thanks. I'm new to Hibernate, as well, so I'm going to ask you (all) which book is best for learning Hibernate. I browsed a little bit on Amazon's but I did not find anything to be enthusiastic about. What would you suggest me as a good starting point/introductory book? Any good web article/tutorial to signal, as well? I used only the included reference and experimented a bit. - Family photographs are a critical legacy for ourselves and our descendants. Protect that legacy with a digital backup and recovery plan.
