using lobs system types to manually clear data

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

using lobs system types to manually clear data

Brian Young-4
Hello, in our usage of hsqldb, it is sometimes necessary to permanently delete blobs such that someone "in the know" couldn't recover said blob from the .lobs file.

I am considering reading the system tables and zero out the blocks that are identified as having no active references.  Obviously this would be done while the database is "offline", meaning the cleanup process is the owner and single user of the database.

I am assuming LOB_IDS.LOB_LENGTH is in bytes, but how do I determine the offset a given LOB within the .lobs file?

Are there any gotchas here? 

Thanks

------------------------------------------------------------------------------
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
Instantly run your Selenium tests across 300+ browser/OS combos.
Get unparalleled scalability from the best Selenium testing platform available
Simple to use. Nothing to install. Get started now for free."
http://p.sf.net/sfu/SauceLabs
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: using lobs system types to manually clear data

Fred Toussi-2
It is easier to delete the rows that contain the lobs. The spaces then become available for new lobs after a checkpoint. Create a a table with a lob column and insert random data in multiples of 32 GB. This will fill up the space. Then delete rows from this table and finally perform a checkpoint to release the space again.
 
The LOB_IDS table is for the currently existing lobs that have a reference, not deleted ones.
 
Fred
 
On Wed, May 21, 2014, at 18:38, Brian Young wrote:
Hello, in our usage of hsqldb, it is sometimes necessary to permanently delete blobs such that someone "in the know" couldn't recover said blob from the .lobs file.
 
I am considering reading the system tables and zero out the blocks that are identified as having no active references.  Obviously this would be done while the database is "offline", meaning the cleanup process is the owner and single user of the database.
 
I am assuming LOB_IDS.LOB_LENGTH is in bytes, but how do I determine the offset a given LOB within the .lobs file?
 
Are there any gotchas here? 
 
Thanks
------------------------------------------------------------------------------
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
Instantly run your Selenium tests across 300+ browser/OS combos.
Get unparalleled scalability from the best Selenium testing platform available
Simple to use. Nothing to install. Get started now for free."
_______________________________________________
Hsqldb-user mailing list

------------------------------------------------------------------------------
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
Instantly run your Selenium tests across 300+ browser/OS combos.
Get unparalleled scalability from the best Selenium testing platform available
Simple to use. Nothing to install. Get started now for free."
http://p.sf.net/sfu/SauceLabs
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: using lobs system types to manually clear data

Brian Young-4
My only concern is that writing a lot of data would "stretch" the .lobs file even larger as it grows to accommodate an artificially large quantity of random data.   File size is a major concern since this is part of a desktop application.  Is there some way to calculate exactly how much random data I'd need to write to fill the unused areas of a given .lobs file so I can eliminate or at least minimize further enlarging the .lobs file?

Thanks for the help.

On Wed, May 21, 2014 at 2:22 PM, Fred Toussi <[hidden email]> wrote:
It is easier to delete the rows that contain the lobs. The spaces then become available for new lobs after a checkpoint. Create a a table with a lob column and insert random data in multiples of 32 GB. This will fill up the space. Then delete rows from this table and finally perform a checkpoint to release the space again.
 
The LOB_IDS table is for the currently existing lobs that have a reference, not deleted ones.
 
Fred
 
On Wed, May 21, 2014, at 18:38, Brian Young wrote:
Hello, in our usage of hsqldb, it is sometimes necessary to permanently delete blobs such that someone "in the know" couldn't recover said blob from the .lobs file.
 
I am considering reading the system tables and zero out the blocks that are identified as having no active references.  Obviously this would be done while the database is "offline", meaning the cleanup process is the owner and single user of the database.
 
I am assuming LOB_IDS.LOB_LENGTH is in bytes, but how do I determine the offset a given LOB within the .lobs file?
 
Are there any gotchas here? 
 
Thanks
------------------------------------------------------------------------------
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
Instantly run your Selenium tests across 300+ browser/OS combos.
Get unparalleled scalability from the best Selenium testing platform available
Simple to use. Nothing to install. Get started now for free."
_______________________________________________
Hsqldb-user mailing list

------------------------------------------------------------------------------
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
Instantly run your Selenium tests across 300+ browser/OS combos.
Get unparalleled scalability from the best Selenium testing platform available
Simple to use. Nothing to install. Get started now for free."
http://p.sf.net/sfu/SauceLabs
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user



------------------------------------------------------------------------------
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
Instantly run your Selenium tests across 300+ browser/OS combos.
Get unparalleled scalability from the best Selenium testing platform available
Simple to use. Nothing to install. Get started now for free."
http://p.sf.net/sfu/SauceLabs
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: using lobs system types to manually clear data

Fred Toussi-2
The empty space sizes are in the second column of SYSTEM_LOBS.BLOCKS table. Each unit represents one unit of LOB SCALE which is 32K by default.
 
In the .script file there are entries like this:
 
INSERT INTO BLOCKS VALUES(25391,1,0)
INSERT INTO BLOCKS VALUES(25392,1,0)
 
In the above list there are two blocks each with one empty unit. The last entry has a very large value and indicates the space beyond the current file
 
INSERT INTO BLOCKS VALUES(25918,2147457729,0)
 
Therefore you can use something like this to calculate the number of free blocks within the file:
 
SELECT SUM(BLOCK_COUNT) FROM SYSTEM_LOBS.BLOCKS WHERE BLOCK_COUNT < 10000000
 
Fred
 
On Thu, May 22, 2014, at 2:28, Brian Young wrote:
My only concern is that writing a lot of data would "stretch" the .lobs file even larger as it grows to accommodate an artificially large quantity of random data.   File size is a major concern since this is part of a desktop application.  Is there some way to calculate exactly how much random data I'd need to write to fill the unused areas of a given .lobs file so I can eliminate or at least minimize further enlarging the .lobs file?
 
Thanks for the help.
 
On Wed, May 21, 2014 at 2:22 PM, Fred Toussi <[hidden email]> wrote:
It is easier to delete the rows that contain the lobs. The spaces then become available for new lobs after a checkpoint. Create a a table with a lob column and insert random data in multiples of 32 GB. This will fill up the space. Then delete rows from this table and finally perform a checkpoint to release the space again.
 
The LOB_IDS table is for the currently existing lobs that have a reference, not deleted ones.
 
Fred
 
On Wed, May 21, 2014, at 18:38, Brian Young wrote:
Hello, in our usage of hsqldb, it is sometimes necessary to permanently delete blobs such that someone "in the know" couldn't recover said blob from the .lobs file.
 
I am considering reading the system tables and zero out the blocks that are identified as having no active references.  Obviously this would be done while the database is "offline", meaning the cleanup process is the owner and single user of the database.
 
I am assuming LOB_IDS.LOB_LENGTH is in bytes, but how do I determine the offset a given LOB within the .lobs file?
 
Are there any gotchas here? 
 
Thanks
------------------------------------------------------------------------------
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
Instantly run your Selenium tests across 300+ browser/OS combos.
Get unparalleled scalability from the best Selenium testing platform available
Simple to use. Nothing to install. Get started now for free."
_______________________________________________
Hsqldb-user mailing list

------------------------------------------------------------------------------
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
Instantly run your Selenium tests across 300+ browser/OS combos.
Get unparalleled scalability from the best Selenium testing platform available
Simple to use. Nothing to install. Get started now for free."
http://p.sf.net/sfu/SauceLabs
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
 
------------------------------------------------------------------------------
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
Instantly run your Selenium tests across 300+ browser/OS combos.
Get unparalleled scalability from the best Selenium testing platform available
Simple to use. Nothing to install. Get started now for free."
_______________________________________________
Hsqldb-user mailing list

------------------------------------------------------------------------------
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
Instantly run your Selenium tests across 300+ browser/OS combos.
Get unparalleled scalability from the best Selenium testing platform available
Simple to use. Nothing to install. Get started now for free."
http://p.sf.net/sfu/SauceLabs
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user