Re: Patch to 2.2.9 for named checkpoints

Hi Peter,

This won't go into 2.3.0, which is due soon. The likely target is 2.3.1
in a few months time.

I have looked at the code. It shouldn't bee too difficult to insert the
functionality into 2.3.0 code, as the classes have not changed a lot
between 2.2.9 and 2.3.0.

Fred


On Tue, Feb 26, 2013, at 9:46, Skörd, Peter wrote:

Hi Fred,

I read the conversation about named checkpoints, this is a feature that
we would be very useful for our acceptance tests, do you know if this
will be included in the 2.3 release or will it be 3.0?

Is it included in the snapshot released today 20130226?

BR, Peter


From: Fred Toussi [mailto:[email protected]]
Sent: den 8 februari 2013 19:49
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] 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


   [2][email protected]

  Apak House
  Badminton Court
  Station Road
  Yate, Bristol
  BS37 5HZ.  United Kingdom
  [3]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.

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

_______________________________________________

Hsqldb-user mailing list

[5][email protected]

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



              Click [7]here to report this email as spam.

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

Everyone hates slow websites. So do we.

Make your web apps faster with AppDynamics

Download AppDynamics Lite for free today:

[8]http://p.sf.net/sfu/appdyn_d2d_feb

_______________________________________________

Hsqldb-user mailing list

[9][email protected]

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

References

1. http://www.sword-apak.com/
2. mailto:[email protected]
3. http://www.sword-group.com/
4. http://p.sf.net/sfu/sophos-d2d-feb
5. mailto:[email protected]
6. https://lists.sourceforge.net/lists/listinfo/hsqldb-user
7. https://www.mailcontrol.com/sr/BMmFoNBTTkXGX2PQPOmvUonSGHjms6TH4WEXqxGsVpM6SJvmwrBjbzlYpUUXOH0PVgxvWqNtOJpSWoTyPktGDA==
8. http://p.sf.net/sfu/appdyn_d2d_feb
9. mailto:[email protected]
  10. https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb

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