db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Moving/copying BLOBs from one table to another
Date Mon, 09 Feb 2009 15:03:37 GMT
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
>
>


Mime
View raw message