Re: Patch to 2.2.9 for named checkpoints

Thanks Rob,

This feature is certainly useful for large tests.

As you say, the restore is the difficult part. You seem to have paid
attention to session states.

I haven't looked at the patch yet and may or may not use your code, But
I will review it and include the required functionality for the next
version, which also extends the ORA compatibility mode.

Regards

Fred Toussi

On Fri, Feb 8, 2013, at 17:18, Rob David wrote:

Hi,


I have posted a patch on the tracker for a change against 2.2.9 to
allow for named checkpoints. This was a much needed feature that we are
currently using in our organisation for automated Web UI testing. Our
UI test cases typically unavoidably commit transactions, and we are
using this feature to quickly wind the database back to a known state
between each test case in order to isolate them from each other. We’re
currently finding that we are now able to do this with something like
sub-second performance, which is a massive improvement over the only
previous solution we had under Oracle of using scripted backup and
restore, which was something more like a minute. We think this is very
useful addition for our use case, which must be a fairly typical one,
for which alternative solutions appear to be very hard to find (or are
very expensive).


The extensions work like this:


The checkpoint command has been extended:


CHECKPOINT [ ‘name’ ] [ DEFRAG ]


and two new commands added:


RESTORE [FROM] CHECKPOINT ‘name’;

DROP CHECKPOINT ‘name’;


Each named checkpoint exists as a saved copy of the database files
stored in a <database>.checkpoints directory stored in the same
directory as the main database. The implementation of the extended
checkpoint command is fairly straightforward. Once the data is flushed
to database files, and the files are closed, a suitably named copy is
made to the checkpoint directory before re-opening the database again.


The drop command is likewise not much more than a glorified file
delete.


The hard part is the implementation of RESTORE, which must shutdown the
database engine, restore the database files from the checkpoint backup
and re-open them all again, whilst keeping existing database sessions
open. It does this by trying to lock out the session manager, and all
the individual sessions, rolling back any transactions that may be in
progress as it goes. It then reloads the database from the checkpoint,
and then refreshes the user information in the sessions from the
restored user database, and kills off any session for any users that
don’t now exist in the database.


I’m not sure if my solution is 100% sound. Implementing the above
required having to reorganise some of the session locking, which is
always a risky proposition. However, It  has been running completely
reliably (or so I’m told) in our test system for the last few months,
so I’m led to believe there’s at least some merit to it. Therefore I’m
submitting the patch in the hope it will be useful to someone.


Regards


Rob D



  Rob David
  Sword Apak
  [1]www.sword-apak.com

  t   +44 1454 871000
  f   +44 1454 871199
  e   [email protected]

  Apak House
  Badminton Court
  Station Road
  Yate, Bristol
  BS37 5HZ.  United Kingdom
  www.sword-group.com

  Apak Group Limited, Registered in England No. 01451033.
  Registered Office: 1000 Great West Road, Brentford, Middlesex, TW8
  9DW.
    _______________________________________________________________

  The information contained in this message or any of its attachments
  may be privileged and confidential and intended for the exclusive
  use of the addressee.  The views expressed may not be Apak policy
  but the personal views of the originator.  If you have received this
  message in error, please advise the sender immediately by reply
  e-mail and delete this message and any attachments without retaining
  a copy.  This footnote also confirms that this email has been swept
  for the presence of known computer viruses before being sent.

-----------------------------------------------------------------------
-------

Free Next-Gen Firewall Hardware Offer

Buy your Sophos next-gen firewall before the end March 2013

and get the hardware for free! Learn more.

[2]http://p.sf.net/sfu/sophos-d2d-feb

_______________________________________________

Hsqldb-user mailing list

[3][email protected]

[4]https://lists.sourceforge.net/lists/listinfo/hsqldb-user

References

1. http://www.sword-apak.com/
2. http://p.sf.net/sfu/sophos-d2d-feb
3. mailto:[email protected]
4. https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Free Next-Gen Firewall Hardware Offer
Buy your Sophos next-gen firewall before the end March 2013 
and get the hardware for free! Learn more.
http://p.sf.net/sfu/sophos-d2d-feb

_______________________________________________
Hsqldb-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user