On 2014/02/08 19:30, Raheel Gupta wrote:
@Simon, Sir I dont want to rearrange the data.
I will try to explain more.
All my rows have the exact same size. They will not differ in size.
My problem is due to the fact that I use 64kB page size.
My rows are exactly 8 Bytes + 4096 Bytes.
Now for the purpose of ease in calculation lets assume each row is exactly
4 KB.
So one page stores 16 rows.
Lets say 10 pages are in use and I have a total of 160 rows.
Sir,
We do understand exactly what you mean, no amount of re-explaining will improve a 100% comprehension, and because we do understand,
we know SQLite ain't doing it, and we are trying to offer other ways of achieving what you want to achieve, but this is not the road
you seem to want to go down... Which is OK.
The basic thing you need to understand is this: SQLite does not work the way you hope, it is not made to do the sort of work within
the sort of restrictions you prescribe. Please consider using an alternative, or, accept the space vs. usage parameters. Even if you
could adjust the code of SQLite to allow re-using pages with 1/3 free space (as opposed to 2/3 free space), then you are doomed
because the code will be untested (unless you can download and run the entire test suite without errors) and even then, you will
have to manually rebuild and repair and re-test your own version of the DB every time a new release happens and forever in future.
Is this really feasible? And even then... there is no guarantee SQLIte will re-use the exact rowids that fit inside a specific
page, not to mention it will only even consider reusing a key if you did not specify "AUTOINCREMENT" in the schema (which, at least,
is unlikely and fixable).
If you absolutely have to use SQLite, then maybe you can keep track of deleted rows, and in stead of deleting them, just mark them
as "not used" while keeping the rowid or whatever primary key is used - add this key to a list of available keys maybe (to be
faster), and when inserting new rows, first see if you have any items in your list of unused rows, then write them to that primary
key using REPLACE etc.
A typical Schema could be like this:
CREATE TABLE datablocks (ID INTEGER PRIMARY KEY, Used INT DEFAULT 1, Data BLOB);
CREATE TABLE availrows (ID INTEGER PRIMARY KEY);
Some Pseudo code...
when deleting a row/rows:
UPDATE datablocks SET Used=0 WHERE ID=somerowid;
REPLACE INTO availrows VALUES (somerowid);
when adding a row
availRowID = (SELECT ID FROM availrows LIMIT 1);
if (availRowID!=NULL) then if (DELETE FROM availrows WHERE ID=availRowID) != SQL_OK then availRowID = NULL; // Needed to ensure you
can never overwrite a datablock
if (availRowID != NULL) then {
REPLACE INTO datablocks (availRowID,1,blobValue);
} else {
INSERT INTO datablocks (Data) VALUES (blobValue);
}
Of course adding BLOBs have some more processing to do, but you get the idea.
This way, no row will ever go unused and inserts wont ever use up any space other than that which already exists, unless no space
exists, so the DB size will only grow if you have more actual data rows than before. Also, btw, this will have significant
performance improvements if row-deletion is common.
Queries that need to check through the lists of data can simply reference the "Used" column in the where clause to ensure they list
only rows that do contain valid data-blocks. (SELECT ... WHERE Used>0, etc.)
Of course, the caveat here is this other index-type table will consume a significant amount of diskspace on a DB the size you
describe. Maybe have that in another DB file with different page size parameters too.
If it was me though, I would save the blob streams in another bytestreamed file, and only save the other data about it with indexes
in the SQLite table, since you cannot really use a BLOB in a Where clause or for any other SQL-related function. Do the queries, get
the index.. read the stream from the other file... SQLIte file size will be negligibly small and the data file will only ever be as
big as is needed... easy!
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
