Moving/copying BLOBs from one table to another

[Daniel Noll <da...@nuix.com>]

Hi all.

This is probably a weird question.  I was considering implementing a 
database something like this...

CREATE TABLE Binaries {
    id INTEGER NOT NULL PRIMARY KEY,
    data BLOB(1G) NOT NULL
}

CREATE TABLE BinariesHistory {
    id INTEGER NOT NULL,
    data BLOB(1G) NOT NULL,
    version INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMP NOT NULL,
    PRIMARY KEY (integer, version)
}

In this fashion querying Binaries for an item will be quicker than doing 
it for BinariesHistory as the table size will be fairly large and 
Binaries has only a single id to look stuff up whereas with 
BinariesHistory you would need to return all items and select the one 
where version = MAX(version).

My concern is not knowing what happens to the BLOB.  If I insert into 
BinariesHistory and then do a second query which copies part of the row 
I just inserted into Binaries, will Derby create a second copy of the 
BLOB (undesirable) or will it create a reference to the *same* BLOB?

Daniel


-- 
Daniel Noll                            Forensic and eDiscovery Software
Senior Developer                              The world's most advanced
Nuix                                                email data analysis
http://nuix.com/                                and eDiscovery software

Re: Moving/copying BLOBs from one table to another

[Rick Hillegas <Ri...@Sun.COM>]

Hi Daniel,

Derby only supports value semantics, not pointer or reference semantics. 
In the situation you describe, you will get two intact copies of the blob.

Another solution might be a schema like this:

BinariesHistory( id, versionNumber, blob, userID, creationTimestamp, 
primary key( id, versionNumber ) )

Binaries( id primary key, newestVersionNumber, foreign key( id, 
newestVersionNumber ) references BinariesHistory( id, versionNumber ) )


Hope this helps,
-Rick




Daniel Noll wrote:
> Hi all.
>
> This is probably a weird question.  I was considering implementing a 
> database something like this...
>
> CREATE TABLE Binaries {
>    id INTEGER NOT NULL PRIMARY KEY,
>    data BLOB(1G) NOT NULL
> }
>
> CREATE TABLE BinariesHistory {
>    id INTEGER NOT NULL,
>    data BLOB(1G) NOT NULL,
>    version INTEGER NOT NULL,
>    user_id INTEGER NOT NULL,
>    created_at TIMESTAMP NOT NULL,
>    PRIMARY KEY (integer, version)
> }
>
> In this fashion querying Binaries for an item will be quicker than 
> doing it for BinariesHistory as the table size will be fairly large 
> and Binaries has only a single id to look stuff up whereas with 
> BinariesHistory you would need to return all items and select the one 
> where version = MAX(version).
>
> My concern is not knowing what happens to the BLOB.  If I insert into 
> BinariesHistory and then do a second query which copies part of the 
> row I just inserted into Binaries, will Derby create a second copy of 
> the BLOB (undesirable) or will it create a reference to the *same* BLOB?
>
> Daniel
>
>