[yunqa.de] Re: cache and blob
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Tue, 23 Dec 2008 12:54:21 +0100
>Please, any example of using class TDIAbstractSQLite3Cache for BLOB caching?
{ DISQLite3 example about how to use TDIAbstractSQLite3Cache with blob data.
Visit the DISQLite3 Internet site for latest information and updates:
http://www.yunqa.de/delphi/
Copyright (c) 2008 Ralf Junker, The Delphi Inspiration <delphi@xxxxxxxx>
------------------------------------------------------------------------------ }
program DISQLite3_Blob_Cache;
{$APPTYPE CONSOLE}
{$I DI.inc}
uses
{$IFDEF FastMM}FastMM4, {$ENDIF}SysUtils,
DISQLite3Api, DISQLite3Database, DISQLite3Cache;
type
{ Type to store the data as returned from one table row. }
TRowData = record
Blob: Pointer;
BlobSize: Cardinal;
end;
PRowData = ^TRowData;
TBlobCache = class(TDIAbstractSQLite3Cache)
protected
procedure DoFinalizeItem(const AItem: Pointer); override;
procedure DoInitializeItem(const AItem: Pointer); override;
end;
procedure TBlobCache.DoFinalizeItem(const AItem: Pointer);
var
p: PRowData;
begin
p := PRowData(AItem);
FreeMem(p^.Blob);
end;
//------------------------------------------------------------------------------
procedure TBlobCache.DoInitializeItem(const AItem: Pointer);
begin
FillChar(AItem^, ItemSize, 0);
end;
//------------------------------------------------------------------------------
var
Blob: packed array[0..255] of Byte;
c: TBlobCache;
Count: Cardinal;
DB: TDISQLite3Database;
i: Integer;
p: PRowData;
Stmt: TDISQLite3Statement;
begin
try
try
{ Create database. }
DB := TDISQLite3Database.Create(nil);
DB.DatabaseName := 'test.db3';
DB.CreateDatabase;
DB.Execute('CREATE TABLE t(idx INTEGER PRIMARY KEY, b BLOB)');
{ Create blob table and fill with data. }
DB.StartTransaction;
try
Stmt := DB.Prepare('INSERT INTO t (idx, b) VALUES (?, ?)');
try
for i := 0 to 255 do
begin
Stmt.Bind_Int(1, i);
FillChar(Blob, i, i);
Stmt.Bind_Blob(2, @Blob, i, SQLITE_STATIC);
Stmt.StepAndReset;
end;
finally
Stmt.Free;
end;
finally
DB.Commit;
end;
{ Set up cache and read random value. }
c := TBlobCache.Create(SizeOf(TRowData), 0);
c.MaxCount := 64;
Stmt := DB.Prepare('SELECT b from t WHERE idx=?');
try
for Count := 0 to 1024 do
begin
i := Random(256);
{ Try to get row from cache. }
p := c.GetItem(i);
if not Assigned(p) then
begin
{ If row is not in the cache,
try to get it from the database. }
Stmt.Bind_Int(1, i);
if Stmt.Step = SQLITE_ROW then
begin
{ Add row from database to the cache. }
p := c.AddItem(i);
p^.BlobSize := Stmt.Column_Bytes(0);
{ If the blob contains data,
allocate memory and copy it there. }
if p^.BlobSize > 0 then
begin
GetMem(p^.Blob, p^.BlobSize);
Move(Stmt.column_blob(0)^, p^.Blob^, p^.BlobSize);
end;
end;
Stmt.Reset;
end
else
begin
Write('Cache ');
end;
if Assigned(p) then
WriteLn('Found ', i, ', Size: ', p^.BlobSize)
else
WriteLn('Not Found: ', i);
end;
finally
Stmt.Free;
c.Free;
end;
DB.Free;
except
on e: Exception do
WriteLn(e.Message);
end;
finally
WriteLn('Done - Press ENTER to exit.');
ReadLn;
end;
end. { DISQLite3 example about how to use TDIAbstractSQLite3Cache with blob data.
Visit the DISQLite3 Internet site for latest information and updates:
http://www.yunqa.de/delphi/
Copyright (c) 2008 Ralf Junker, The Delphi Inspiration <delphi@xxxxxxxx>
------------------------------------------------------------------------------ }
program DISQLite3_Blob_Cache;
{$APPTYPE CONSOLE}
{$I DI.inc}
uses
{$IFDEF FastMM}FastMM4, {$ENDIF}SysUtils,
DISQLite3Api, DISQLite3Database, DISQLite3Cache;
type
{ Type to store the data as returned from one table row. }
TRowData = record
Blob: Pointer;
BlobSize: Cardinal;
end;
PRowData = ^TRowData;
TBlobCache = class(TDIAbstractSQLite3Cache)
protected
procedure DoFinalizeItem(const AItem: Pointer); override;
procedure DoInitializeItem(const AItem: Pointer); override;
end;
procedure TBlobCache.DoFinalizeItem(const AItem: Pointer);
var
p: PRowData;
begin
p := PRowData(AItem);
FreeMem(p^.Blob);
end;
//------------------------------------------------------------------------------
procedure TBlobCache.DoInitializeItem(const AItem: Pointer);
begin
FillChar(AItem^, ItemSize, 0);
end;
//------------------------------------------------------------------------------
var
Blob: packed array[0..255] of Byte;
c: TBlobCache;
Count: Cardinal;
DB: TDISQLite3Database;
i: Integer;
p: PRowData;
Stmt: TDISQLite3Statement;
begin
try
try
{ Create database. }
DB := TDISQLite3Database.Create(nil);
DB.DatabaseName := 'test.db3';
DB.CreateDatabase;
DB.Execute('CREATE TABLE t(idx INTEGER PRIMARY KEY, b BLOB)');
{ Create blob table and fill with data. }
DB.StartTransaction;
try
Stmt := DB.Prepare('INSERT INTO t (idx, b) VALUES (?, ?)');
try
for i := 0 to 255 do
begin
Stmt.Bind_Int(1, i);
FillChar(Blob, i, i);
Stmt.Bind_Blob(2, @Blob, i, SQLITE_STATIC);
Stmt.StepAndReset;
end;
finally
Stmt.Free;
end;
finally
DB.Commit;
end;
{ Set up cache and read random value. }
c := TBlobCache.Create(SizeOf(TRowData), 0);
c.MaxCount := 64;
Stmt := DB.Prepare('SELECT b from t WHERE idx=?');
try
for Count := 0 to 1024 do
begin
i := Random(256);
{ Try to get row from cache. }
p := c.GetItem(i);
if not Assigned(p) then
begin
{ If row is not in the cache,
try to get it from the database. }
Stmt.Bind_Int(1, i);
if Stmt.Step = SQLITE_ROW then
begin
{ Add row from database to the cache. }
p := c.AddItem(i);
p^.BlobSize := Stmt.Column_Bytes(0);
{ If the blob contains data,
allocate memory and copy it there. }
if p^.BlobSize > 0 then
begin
GetMem(p^.Blob, p^.BlobSize);
Move(Stmt.column_blob(0)^, p^.Blob^, p^.BlobSize);
end;
end;
Stmt.Reset;
end
else
begin
Write('Cache ');
end;
if Assigned(p) then
WriteLn('Found ', i, ', Size: ', p^.BlobSize)
else
WriteLn('Not Found: ', i);
end;
finally
Stmt.Free;
c.Free;
end;
DB.Free;
except
on e: Exception do
WriteLn(e.Message);
end;
finally
WriteLn('Done - Press ENTER to exit.');
ReadLn;
end;
end.
Other related posts:
