Re: Patch to 2.2.9 for named checkpoints

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
www.sword-apak.com <http://www.sword-apak.com/>
t



+44 1454 871000

f



+44 1454 871199

e



[email protected]<mailto:[email protected]>


Apak House
Badminton Court
Station Road
Yate, Bristol
BS37 5HZ.  United Kingdom
www.sword-group.com<http://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.
http://p.sf.net/sfu/sophos-d2d-feb
_______________________________________________
Hsqldb-user mailing list
[email protected]<mailto:[email protected]>
https://lists.sourceforge.net/lists/listinfo/hsqldb-user



Click here<https://www.mailcontrol.com/sr/BMmFoNBTTkXGX2PQPOmvUonSGHjms6TH4WEXqxGsVpM6SJvmwrBjbzlYpUUXOH0PVgxvWqNtOJpSWoTyPktGDA==> 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:
http://p.sf.net/sfu/appdyn_d2d_feb

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