[yunqa.de] Re: In Memory Database Documentation
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx,<yunqa@xxxxxxxxxxxxx>
- Date: Wed, 13 May 2009 12:29:57 +0200
At 20:21 12.05.2009, Yu Luo wrote:
>I would like to include a SQLite database as a resource file and compile that
>into an executable. Would a DISQLite3Database then be able to load it? If so,
>should I use the In Memory of DISQLite3Database?
DISQLite3 can not by default access a databases directly from memory. However,
it can do so with the help of a memory virtual file system (VFS).
The VFS layer controls all file operations. I have attached a TStream VFS
implementation (requires DISQLite3 Pro) which I use for some library testing. I
believe that it should serve you well to access your resource file database
directly via TResourceStream.
First you must register the new VFS using:
sqlite3_vfs_register_stream;
Then you can open your database stream by simply calling:
sqlite3_open_stream(@DB, Stream);
Now you can use the usual sqlite3_... API calls on your TStream database. The
stream VFS layer implementation gives read and write access, but does not
create journal files. Therefore, ROLLBACK is unavailable.
For TResourceStream, databases are of course read-only.
Ralf
// {$DEFINE VFS_Debug}
unit DISQLite3VfsStream;
{$I DI.inc}
{$I DISQLite3.inc}
interface
uses
DISystemCompat, Classes, DISQLite3Api;
function sqlite3_open_stream(
const ADb: sqlite3_ptr;
const AStream: TStream): Integer;
function sqlite3_vfs_register_stream: Integer;
implementation
uses
Windows, SysUtils;
function isNt: Boolean;
var
sInfo: OSVERSIONINFO;
begin
sInfo.dwOSVersionInfoSize := SizeOf(sInfo);
GetVersionEx(sInfo);
Result := sInfo.dwPlatformId = VER_PLATFORM_WIN32_NT;
end;
//------------------------------------------------------------------------------
// IO Methods
//------------------------------------------------------------------------------
type
stream_file = packed record
pMethods: sqlite3_io_methods_ptr;
Stream: TStream;
Name: PUtf8Char;
end;
stream_file_ptr = ^stream_file;
//------------------------------------------------------------------------------
{ Close a file. }
function xClose(
ID: sqlite3_file_ptr): Integer;
{$IFDEF VFS_Debug}
var
f: stream_file_ptr;
{$ENDIF VFS_Debug}
begin
{$IFDEF VFS_Debug}
f := stream_file_ptr(ID);
WriteLn('xClose ' + f^.Name);
{$ENDIF VFS_Debug}
Result := SQLITE_OK;
end;
//------------------------------------------------------------------------------
{ Read data from a file into a buffer. Return SQLITE_OK if all
bytes were read successfully and SQLITE_IOERR if anything goes
wrong. }
function xRead(
ID: sqlite3_file_ptr; // File to read from.
pBuf: Pointer; // Write content into this buffer.
iAmount: Integer; // Number of bytes to read.
iOffset: Int64): Integer; // Begin reading at this offset.
var
{$IFDEF VFS_Debug}
f: stream_file_ptr;
{$ENDIF VFS_Debug}
iRead: Integer;
Stream: TStream;
begin
{$IFDEF VFS_Debug}
f := stream_file_ptr(ID);
WriteLn('xRead ' + f^.Name);
{$ENDIF VFS_Debug}
Stream := stream_file_ptr(ID)^.Stream;
if Assigned(Stream) then
try
Stream.Seek(iOffset, soBeginning);
iRead := Stream.Read(pBuf^, iAmount);
if iRead = iAmount then
Result := SQLITE_OK
else
begin
{ Unread parts of the buffer must be zero-filled. }
FillChar(PAnsiChar(pBuf)[iRead], iAmount - iRead, 0);
Result := SQLITE_IOERR_SHORT_READ;
end;
except
Result := SQLITE_IOERR_READ;
end
else
begin
Result := SQLITE_IOERR;
end;
end;
//------------------------------------------------------------------------------
function xWrite(
ID: sqlite3_file_ptr;
const pBuf: Pointer;
iAmount: Integer;
iOffset: Int64): Integer;
var
f: stream_file_ptr;
iWritten: Integer;
begin
{$IFDEF VFS_Debug}
f := stream_file_ptr(ID);
WriteLn('xWrite ' + f^.Name);
{$ENDIF VFS_Debug}
f := stream_file_ptr(ID);
if Assigned(f^.Stream) then
try
f^.Stream.Seek(iOffset, soBeginning);
iWritten := f^.Stream.Write(pBuf^, iAmount);
if iWritten = iAmount then
Result := SQLITE_OK
else
Result := SQLITE_ERROR;
except
Result := SQLITE_ERROR;
end
else
begin
Result := SQLITE_OK;
end;
end;
//------------------------------------------------------------------------------
function xTruncate(
ID: sqlite3_file_ptr;
Size: Int64): Integer;
var
Stream: TStream;
begin
{$IFDEF VFS_Debug}WriteLn('xTruncate'); {$ENDIF}
Stream := stream_file_ptr(ID)^.Stream;
try
Stream.Size := Size;
Result := SQLITE_OK
except
Result := SQLITE_ERROR;
end;
end;
//------------------------------------------------------------------------------
{ Make sure all writes to a particular file are committed to disk. }
function xSync(
ID: sqlite3_file_ptr;
Flags: Integer): Integer;
{$IFDEF VFS_Debug}
var
f: stream_file_ptr;
{$ENDIF VFS_Debug}
begin
{$IFDEF VFS_Debug}
f := stream_file_ptr(ID);
WriteLn('xSync ' + f^.Name);
{$ENDIF VFS_Debug}
Result := SQLITE_OK;
end;
//------------------------------------------------------------------------------
{ Determine the current size of a file in bytes.
Return SQLITE_OK on success. }
function xFileSize(
ID: sqlite3_file_ptr;
pSize: PInt64): Integer;
var
{$IFDEF VFS_Debug}
f: stream_file_ptr;
{$ENDIF VFS_Debug}
Stream: TStream;
begin
{$IFDEF VFS_Debug}
f := stream_file_ptr(ID);
WriteLn('xFileSize ' + f^.Name);
{$ENDIF VFS_Debug}
Stream := stream_file_ptr(ID)^.Stream;
if Assigned(Stream) then
try
pSize^ := Stream.Size;
Result := SQLITE_OK;
except
Result := SQLITE_ERROR;
end
else
begin
pSize^ := 0;
Result := SQLITE_OK;
end;
end;
//------------------------------------------------------------------------------
function xLock(
ID: sqlite3_file_ptr;
lockType: Integer): Integer;
{$IFDEF VFS_Debug}
var
f: stream_file_ptr;
{$ENDIF VFS_Debug}
begin
{$IFDEF VFS_Debug}
f := stream_file_ptr(ID);
WriteLn('xLock ' + f^.Name);
{$ENDIF VFS_Debug}
Result := SQLITE_OK;
end;
//------------------------------------------------------------------------------
{ Lower the locking level on file descriptor id to locktype. locktype
must be either NO_LOCK or SHARED_LOCK.
If the locking level of the file descriptor is already at or below
the requested locking level, this routine is a no-op.
It is not possible for this routine to fail if the second argument
is NO_LOCK. If the second argument is SHARED_LOCK then this routine
might return SQLITE_IOERR. }
function xUnlock(
ID: sqlite3_file_ptr;
lockType: Integer): Integer;
{$IFDEF VFS_Debug}
var
f: stream_file_ptr;
{$ENDIF VFS_Debug}
begin
{$IFDEF VFS_Debug}
f := stream_file_ptr(ID);
WriteLn('xUnlock ' + f^.Name);
{$ENDIF VFS_Debug}
Result := SQLITE_OK;
end;
//------------------------------------------------------------------------------
{ This routine checks if there is a RESERVED lock held on the specified
file by this or any other process. If such a lock is held, return
non-zero, otherwise zero. }
function xCheckReservedLock(
ID: sqlite3_file_ptr;
pResOut: PInteger): Integer;
{$IFDEF VFS_Debug}
var
f: stream_file_ptr;
{$ENDIF VFS_Debug}
begin
{$IFDEF VFS_Debug}
f := stream_file_ptr(ID);
WriteLn('xCheckReservedLock ' + f^.Name);
{$ENDIF VFS_Debug}
pResOut^ := 0;
Result := SQLITE_OK;
end;
//------------------------------------------------------------------------------
function xFileControl(
ID: sqlite3_file_ptr;
op: Integer;
pArg: Pointer): Integer;
{$IFDEF VFS_Debug}
var
f: stream_file_ptr;
{$ENDIF VFS_Debug}
begin
{$IFDEF VFS_Debug}
f := stream_file_ptr(ID);
WriteLn('xFileControl ' + f^.Name);
{$ENDIF VFS_Debug}
Result := SQLITE_OK;
end;
//------------------------------------------------------------------------------
{ Not used. }
function xLockState(
ID: sqlite3_file_ptr): Integer;
begin
{$IFDEF VFS_Debug}WriteLn('xLockedState'); {$ENDIF}
Result := SQLITE_LOCK_NONE;
end;
//------------------------------------------------------------------------------
function xSectorSize(
ID: sqlite3_file_ptr): Integer;
{$IFDEF VFS_Debug}
var
f: stream_file_ptr;
{$ENDIF VFS_Debug}
begin
{$IFDEF VFS_Debug}
f := stream_file_ptr(ID);
WriteLn('xSectorSize ' + f^.Name);
{$ENDIF VFS_Debug}
Result := $200;
end;
//------------------------------------------------------------------------------
function xDeviceCharacteristics(
ID: sqlite3_file_ptr): Integer;
begin
Result := SQLITE_OK;
end;
const
stream_methods: sqlite3_io_methods = (
iVersion: 1;
xClose: xClose;
xRead: xRead;
xWrite: xWrite;
xTruncate: xTruncate;
xSync: xSync;
xFileSize: xFileSize;
xLock: xLock;
xUnlock: xUnlock;
xCheckReservedLock: xCheckReservedLock;
xFileControl: xFileControl;
xLockState: nil; // xLockState;
xSectorSize: xSectorSize;
xDeviceCharacteristics: xDeviceCharacteristics);
//------------------------------------------------------------------------------
// VFS Stream
//------------------------------------------------------------------------------
{ xOpen is used to open files on the underlying storage media. The result is an
sqlite3_file object. There are additional methods, defined by the sqlite3_file
object itself that are used to read and write and close the file. The
additional methods are detailed below. The filename is in UTF-8. SQLite will
guarantee that the zFilename string passed to xOpen() is a full pathname as
generated by xFullPathname() and that the string will be valid and unchanged
until xClose() is called. So the sqlite3_file can store a pointer to the
filename if it needs to remember the filename for some reason. The flags
argument to xOpen() is a copy of the flags argument to sqlite3_open_v2().
If sqlite3_open() or sqlite3_open16() is used, then flags is
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE. If xOpen() opens a file read-only
then it sets *pOutFlags to include SQLITE_OPEN_READONLY. Other bits in
*pOutFlags may be set. SQLite will also add one of the following flags to
the xOpen() call, depending on the object being opened:
* SQLITE_OPEN_MAIN_DB
* SQLITE_OPEN_MAIN_JOURNAL
* SQLITE_OPEN_TEMP_DB
* SQLITE_OPEN_TEMP_JOURNAL
* SQLITE_OPEN_SUBJOURNAL
* SQLITE_OPEN_MASTER_JOURNAL
The file I/O implementation can use the object type flags to changes the way
it deals with files. For example, an application that does not care about
crash recovery or rollback, might make the open of a journal file a no-op.
Writes to this journal are also a no-op. Any attempt to read the journal
return SQLITE_IOERR. Or the implementation might recognize that a database
file will be doing page-aligned sector reads and writes in a random order and
set up its I/O subsystem accordingly. SQLite might also add one of the
following flags to the xOpen method:
* SQLITE_OPEN_DELETEONCLOSE
* SQLITE_OPEN_EXCLUSIVE
The SQLITE_OPEN_DELETEONCLOSE flag means the file should be deleted when it is
closed. This will always be set for TEMP databases and journals and for
subjournals. The SQLITE_OPEN_EXCLUSIVE flag means the file should be opened
for exclusive access. This flag is set for all files except for the main
database file. The sqlite3_file structure passed as the third argument to
xOpen is allocated by the caller. xOpen just fills it in. The caller allocates
a minimum of szOsFile bytes for the sqlite3_file structure. }
function xOpen(
pVfs: sqlite3_vfs_ptr;
const zName: PAnsiChar;
pFile: sqlite3_file_ptr;
Flags: Integer;
pOutFlags: PInteger): Integer;
var
f: stream_file_ptr;
begin
{$IFDEF VFS_Debug}
WriteLn('xOpen ', zName); {$ENDIF}
f := stream_file_ptr(pFile);
f^.pMethods := @stream_methods;
f^.Name := zName;
{ Assigned stream for main DB only, not for journal files. }
if Flags and SQLITE_OPEN_MAIN_DB <> 0 then
begin
pVfs.pAppData := zName;
f^.Stream := TStream(StrToInt(zName));
end
else
begin
f^.Stream := nil;
end;
Result := SQLITE_OK;
end;
//------------------------------------------------------------------------------
{ The xDelete method is used delete a file. The name of the file is given in the
second parameter. The filename will be in UTF-8. The VFS must convert the
filename into whatever character representation the underlying operating
system expects. If the syncDir parameter is true, then the xDelete method
should not return until the change to the directory contents for the directory
containing the deleted file have been synced to disk in order to insure that
the file does not "reappear" if a power failure occurs soon after. }
function xDelete(
pVfs: sqlite3_vfs_ptr;
const zName: PAnsiChar;
syncDir: Integer): Integer;
begin
{$IFDEF VFS_Debug}
WriteLn('xDelete ' + zName);
{$ENDIF VFS_Debug}
Result := SQLITE_OK;
end;
//------------------------------------------------------------------------------
{ The xAccess method is used to check for access permissions on a file. The
filename will be UTF-8 encoded. The flags argument will be
SQLITE_ACCESS_EXISTS to check for the existence of the file,
SQLITE_ACCESS_READWRITE to check to see if the file is both readable and
writable, or
SQLITE_ACCESS_READ to check to see if the file is at least readable.
The "file" named by the second parameter might be a directory or folder name.
Store True (1) if access is allowed, False (0) if not to pResOut^.
Return SQLITE_OK or an error code. Not that SQLITE_IOERR_ACCESS has been
specially added for this purpose. }
function xAccess(
pVfs: sqlite3_vfs_ptr;
const zName: PUtf8Char;
Flags: Integer;
pResOut: PInteger): Integer;
begin
{$IFDEF VFS_Debug}
WriteLn('xAccess ', zName, ' - Flags: ', Flags);
{$ENDIF VFS_Debug}
{ Give access to the main DB stream only, not to journal files. }
if StrComp(pVfs.pAppData, zName) = 0 then
begin
pResOut^ := 1;
Result := SQLITE_OK;
end
else
begin
pResOut^ := 0;
Result := SQLITE_OK;
end;
end;
//------------------------------------------------------------------------------
{ Turn a relative pathname into a full pathname. Write the full pathname into
zOut. zOut will be at least pVfs->mxPathname bytes in size. }
function xFullPathname(
pVfs: sqlite3_vfs_ptr; // Pointer to vfs object.
const zName: PUtf8Char; // Possibly relative input path.
nOut: Integer; // Size of output buffer in bytes.
zOut: PUtf8Char): Integer; // Output bufferd.
begin
{$IFDEF VFS_Debug}
WriteLn('xFullPathname ', zName);
{$ENDIF VFS_Debug}
StrCopy(zOut, zName);
Result := SQLITE_OK;
end;
//------------------------------------------------------------------------------
{$IFNDEF SQLITE_OMIT_LOAD_EXTENSION}
{ The xDlOpen, xDlError, xDlSym, and xDlClose methods are all used for accessing
shared libraries at run-time. These methods may be omitted (and their pointers
set to zero) if the library is compiled with SQLITE_OMIT_LOAD_EXTENSION or if
the sqlite3_enable_load_extension() interface is never used to enable dynamic
extension loading. The xDlOpen method opens a shared library or DLL and
returns a pointer to a handle. NULL is returned if the open fails. If the open
fails, the xDlError method can be used to obtain a text error message. The
message is written into the zErrMsg buffer of the third parameter which is at
least nByte bytes in length. The xDlSym returns a pointer to a symbol in the
shared library. The name of the symbol is given by the second parameter.
UTF-8 encoding is assumed. If the symbol is not found a NULL pointer is
returned. The xDlClose routine closes the shared library. }
function xDlOpen(
pVfs: sqlite3_vfs_ptr;
const zFilename: PUtf8Char): Pointer;
var
h: THandle;
s: UnicodeString;
begin
{$IFDEF VFS_Debug}WriteLn('xDlOpen'); {$ENDIF}
s := sqlite3_decodeutf8(zFilename);
if isNt then
h := LoadLibraryA(s);
else
h := LoadLibraryW(s);
Result := Pointer(h);
end;
procedure xDlError(
pVfs: sqlite3_vfs_ptr;
nBuf: Integer;
zBufOut: PUtf8Char);
begin
{$IFDEF VFS_Debug}WriteLn('xDlError'); {$ENDIF}
getLastErrorMsg(nBuf, zBufOut);
end;
function xDlSym(
pVfs: sqlite3_vfs_ptr;
PHandle: Pointer;
const zSymbol: PAnsiChar): Pointer;
begin
{$IFDEF VFS_Debug}WriteLn('xDlSym'); {$ENDIF}
Result := GetProcAddress(THandle(PHandle), zSymbol);
end;
procedure xDlClose(
pVfs: sqlite3_vfs_ptr;
PHandle: Pointer);
begin
{$IFDEF VFS_Debug}WriteLn('xDlClose'); {$ENDIF}
FreeLibrary(THandle(PHandle));
end;
{$ENDIF !SQLITE_OMIT_LOAD_EXTENSION}
//------------------------------------------------------------------------------
{ The xRandomness method is used once by the first database connection that is
opened. xRandomness should return high-quality randomness that SQLite will
used to seeds its internal pseudo-random number generator (PRNG). The routine
requests that nByte bytes of randomness be written into zOut. The routine
returns the actual number of bytes of randomness obtained. The quality of the
randomness so obtained will determine the quality of the randomness generated
by built-in SQLite functions such as random() and randomblob(). }
function xRandomness(
pVfs: sqlite3_vfs_ptr;
nByte: Integer;
zOut: PAnsiChar): Integer;
begin
{$IFDEF VFS_Debug}WriteLn('xRandomness'); {$ENDIF}
if SizeOf(TSystemTime) <= nByte then
begin
GetSystemTime(psystemtime(zOut)^);
Result := SizeOf(TSystemTime);
end
else
begin
Result := 0;
end;
end;
//------------------------------------------------------------------------------
{ The xSleep method is used to suspend the calling thread for at least the
number of microseconds given. This method is used to implement the
sqlite3_sleep() and sqlite3_busy_timeout() APIs. In the case of
sqlite3_sleep() the xSleep method of the default VFS is always used. If the
underlying system does not have a microsecond resolution sleep capability,
then the sleep time should be rounded up. xSleep returns this rounded-up
value. }
function xSleep(
pVfs: sqlite3_vfs_ptr;
microseconds: Integer): Integer;
var
m: Integer;
begin
{$IFDEF VFS_Debug}WriteLn('xSleep'); {$ENDIF}
m := (microseconds + 999) div 1000;
Sleep(m);
Result := m * 1000;
end;
//------------------------------------------------------------------------------
{ The xCurrentTime method finds the current time and date and writes the result
as double-precision floating point value into pointer provided by the second
parameter. The time and date is in coordinated universal time (UTC) and is a
fractional julian day number. }
function xCurrentTime(
pVfs: sqlite3_vfs_ptr;
pTimeOut: PDouble): Integer;
var
ft: TFileTime;
begin
{$IFDEF VFS_Debug}WriteLn('xCurrentTime'); {$ENDIF}
GetSystemTimeAsFileTime(ft);
pTimeOut^ := FileTimeToJulianDate(ft);
Result := SQLITE_OK;
end;
//------------------------------------------------------------------------------
{ The idea is that this function works like a combination of
GetLastError() and FormatMessage() on windows (or errno and
strerror_r() on unix). After an error is returned by an OS
function, SQLite calls this function with zBuf pointing to
a buffer of nBuf bytes. The OS layer should populate the
buffer with a nul-terminated UTF-8 encoded error message
describing the last IO error to have occurred within the calling
thread.
<p>If the error message is too large for the supplied buffer,
it should be truncated. The return value of xGetLastError
is zero if the error message fits in the buffer, or non-zero
otherwise (if the message was truncated). If non-zero is returned,
then it is not necessary to include the nul-terminator character
in the output buffer.
<p>Not supplying an error message will have no adverse effect
on SQLite. It is fine to have an implementation that never
returns an error message.
<p>However if an error message is supplied, it will be incorporated
by sqlite into the error message available to the user using
sqlite3_errmsg(), possibly making IO errors easier to debug. }
function xGetLastError(
pVfs: sqlite3_vfs_ptr;
nBuf: Integer;
zBuf: PUtf8Char): Integer;
begin
{$IFDEF VFS_Debug}WriteLn('xGetLastError'); {$ENDIF}
zBuf[0] := #0;
Result := SQLITE_OK;
end;
var
stream_vfs: sqlite3_vfs = (
iVersion: 1;
szOsFile: SizeOf(stream_file);
mxPathname: $20;
pNext: nil;
zName: 'stream';
pAppData: nil;
xOpen: xOpen;
xDelete: xDelete;
xAccess: xAccess;
xFullPathname: xFullPathname;
xDlOpen: {$IFDEF SQLITE_OMIT_LOAD_EXTENSION}nil{$ELSE}xDlOpen{$ENDIF};
xDlError: {$IFDEF SQLITE_OMIT_LOAD_EXTENSION}nil{$ELSE}xDlError{$ENDIF};
xDlSym: {$IFDEF SQLITE_OMIT_LOAD_EXTENSION}nil{$ELSE}xDlSym{$ENDIF};
xDlClose: {$IFDEF SQLITE_OMIT_LOAD_EXTENSION}nil{$ELSE}xDlClose{$ENDIF};
xRandomness: xRandomness;
xSleep: xSleep;
xCurrentTime: xCurrentTime
// xGetLastError: xGetLastError
);
//------------------------------------------------------------------------------
// Interfaced Functions
//------------------------------------------------------------------------------
{ Register the VFS stream interface. }
function sqlite3_vfs_register_stream: Integer;
begin
Result := sqlite3_vfs_register(@stream_vfs, 0);
end;
//------------------------------------------------------------------------------
function sqlite3_open_stream(
const ADb: sqlite3_ptr;
const AStream: TStream): Integer;
begin
Result := sqlite3_open_v2(
PAnsiChar(IntToStr(Integer(AStream))),
ADb,
0,
'stream');
end;
end.
Other related posts: