Squeak and SQlite Database
David Simmons
David.Simmons at smallscript.com
Fri Jul 26 04:11:11 UTC 2002
Torsten,
I've taken the last 2 and 1/2 hours to download SqLite and build a
SmallScript wrapper library and test program script. Thanks for
promoting this DB and sending me e-mail asking how to hook SmallScript
up to it.
---
The enclosed script contains a library "SQL" that wrappers the basic
services of SqLite. It also has a simple "Eval" at the end of the script
which creates a database called "Testing", and a table called "tb_1",
with
two columns. It then adds two rows, and selects the results back.
This eval example is almost identical to that on page 1 of the "sqlite"
admin tool for SQLite guide.
You can directly obtain the "sqlite.dll" and other tools directly from
the
SQLite site http://www.hwaci.com/sw/sqlite/download.html. Once you have
downloaded the "sqlite.dll", you can place it in any standard dll
location,
including the %AOSPath%common directory where SmallScript's runtime
"AOS.DLL" is located.
Once you've placed the DLL appropriately, you can just run the enclosed
script.
Enjoy!
-- Dave S. [SmallScript Corp]
SmallScript for the AOS & .NET Platforms
David.Simmons at SmallScript.com | http://www.smallscript.org
==========
Library name: SQL imports: SqLite.
Namespace name: SqLite dll: SqLite
{
Function [<$extern>sqlite_exec2(<Object>session, sqlExpr,
callbackFn,
self, errMsgPtr)]
Function[errorMsgFromCode(code)
^sqlite_error_string(code).__cStringAt(0)]
Function [version ^sqlite_libversion().__cStringAt(0)]
Function [encoding ^sqlite_libencoding().__cStringAt(0)]
}
Exception name: SqException fields: auto id;
{
Function [<::throw(<>,<>)>
throwMsg: msg id: id
^__new() id(id); throw(msg)
]
}
Enum {kPtrSize := 4}
Enum {
#define SQLITE_OK 0 /* Successful result */
#define SQLITE_ERROR 1 /* SQL error or missing database */
#define SQLITE_INTERNAL 2 /* An internal logic error in SQLite
*/
#define SQLITE_PERM 3 /* Access permission denied */
#define SQLITE_ABORT 4 /* Callback routine requested an
abort
*/
#define SQLITE_BUSY 5 /* The database file is locked */
#define SQLITE_LOCKED 6 /* A table in the database is locked
*/
#define SQLITE_NOMEM 7 /* A malloc() failed */
#define SQLITE_READONLY 8 /* Attempt to write a readonly
database
*/
#define SQLITE_INTERRUPT 9 /* Operation terminated by
sqlite_interrupt() */
#define SQLITE_IOERR 10 /* Some kind of disk I/O error
occurred
*/
#define SQLITE_CORRUPT 11 /* The database disk image is
malformed
*/
#define SQLITE_NOTFOUND 12 /* (Internal Only) Table or record
not
found */
#define SQLITE_FULL 13 /* Insertion failed because database
is
full */
#define SQLITE_CANTOPEN 14 /* Unable to open the database file
*/
#define SQLITE_PROTOCOL 15 /* Database lock protocol error */
#define SQLITE_EMPTY 16 /* (Internal Only) Database table is
empty */
#define SQLITE_SCHEMA 17 /* The database schema changed */
#define SQLITE_TOOBIG 18 /* Too much data for one row of a
table
*/
#define SQLITE_CONSTRAINT 19 /* Abort due to contraint violation
*/
#define SQLITE_MISMATCH 20 /* Data type mismatch */
#define SQLITE_MISUSE 21 /* Library used incorrectly */
}
Class name: SqSession
fields: auto property ixHandle, defTrans;
{
"" Class methods
Function [<::(<>)>
open: dbPath
^self(dbPath, false)
]
Function [<::(<>,<>)>
open: dbPath readOnly: isReadOnly
|<String*> errMsgPtr|
^(sqlite_open(dbPath, isReadOnly, <out>errMsgPtr)) ifZero:
[|errMsg|
if (errMsgPtr) [errMsg := errMsgPtr.__cStringAt(0).
sqlite_freemem(errMsgPtr)]
else [errMsg := errorMsgFromCode(SQLITE_CANTOPEN)].
SqException.throw(errMsg,SQLITE_CANTOPEN).
] ifNotZero: [:hSqLite|
basicNew().ixHandle(hSqLite)
]
]
"" Instance methods
Property [
defTrans
^defTrans ? [defTrans := SqTransaction(self)]
]
"" DB Engine Services
Method [isValidSQLExpr(sql) ^sqlite_complete(sql)]
""
Method [version ^::version()]
Method [encoding ^::encoding()]
Method [isValidSQLExpr(sql) ^class().isValidSQLExpr]
""
Method [lastInsertRowId ^sqlite_last_insert_rowid(self)]
Method [changes ^sqlite_changes(self)]
""
Method [setTimeoutTo(nMilliseconds)
sqlite_timeout(self,nMilliseconds)]
""
Method [interrupt sqlite_interrupt(self)]
"" Support behavior
Method [<::close>ReleaseExternalStructures ixHandle := 0]
Property [
ixHandle: hSqLite
"" Close any current session
ixHandle ifNotZero: [if(isXStructGCed())
sqlite_close(ixHandle)].
"" Configure for the new session
(ixHandle := hSqLite) ifZero: [
self
isExternalized(false);
receivesReferenceNotification(false).
] ifNotZero: [
self
receivesReferenceNotification(true);
isExternalized(true).
].
]
Method scope: AutoType [<'ToDo: Review'>
ffiMarshallToFFV
^ixHandle.__asFFV
]
}
Class name: SqTransaction
fields: auto session, nColumns, nRows;
auto private columnData_vector,
columnName_vector,
columnName_cache;
virtual property columnNames, columnValues;
auto virtual[session] lastInsertRowId,
interrupt,
changes,
version,
encoding;
{
"" Class methods
Function [<::(<>)>
newOn: <SqSession> session
^basicNew() session(session)
]
"" Instance methods
Method [at: key ^self at: key ifAbsent: nil]
Method [
at: <Integer> index ifAbsent: absentHandler
unless(index between: 1 and: nColumns)
^absentHandler(self,index).
"" <columnData_vector> may be <nil> if the
EMPTY_RESULT_CALLBACKS
"" pragma/mode is set to ON. In which case, we return a <nil>.
^((columnData_vector ? [^nil]).uint32At((index-1)*kPtrSize))
ifZero: [nil] ifNotZero: [:addr| addr.__cStringAt(0)].
]
Method [
at: <String> key ifAbsent: absentHandler
^columnName_cache.equivIndexOf(key)
ifNil: [absentHandler(self,key)]
ifNotNil: [:i| self[i]]
]
Property [
columnNames
columnName_cache !? [columnName_cache].
unless(nColumns and: [columnName_vector]) [^columnName_cache :=
#()].
columnName_cache := List(nColumns).
1 to: nColumns do: [:i|
columnName_cache[i] :=
(columnName_vector.uint32At((i-1)*kPtrSize))
ifZero: [nil] ifNotZero: [:addr| addr.__cStringAt(0)].
].
^columnName_cache
]
Property [
columnValues
nColumns ifZero: [^#()].
|columnValues| := List(nColumns).
columnData_vector !? [
1 to: nColumns do: [:i|
columnValues[i] :=
(columnData_vector.uint32At((i-1)*kPtrSize))
ifZero: [nil] ifNotZero: [:addr|
addr.__cStringAt(0)].
].
].
^columnValues
]
"" Query Services
Method [
exec: sqlExpr
|errMsgPtr| |callbackFn| := [<$callback=cdecl>
:<Object>ref :argc :argv :columnNames|
"" #onQueryCallback event returns false if it wants no more
values
"" this logic is the opposite of SqLite so we use #not.
(ref.asObjectFromWeakId)
nColumns(argc);
columnData_vector(argv ifZero: []);
columnName_vector(columnNames);
onQueryCallback.not.
].
try [
nRows := 0.
(sqlite_exec(session, sqlExpr, callbackFn, self,
<out>errMsgPtr)) ifNotZero:
[:iErr||errMsg|
if (errMsgPtr) [errMsg := errMsgPtr.__cStringAt(0).
sqlite_freemem(errMsgPtr)]
else [errMsg := errorMsgFromCode(iErr)].
SqException.throw(errMsg, iErr).
].
] finally: [nColumns := 0. columnName_cache := nil].
]
Event [
onQueryCallback
"" As a default event handler, display the query results
"" Display the column headers (once)
nRows ifZero: [stdout cr<< columnNames].
"" Display the column values each time
stdout.print('`n[%s] %s',nRows:+=1, columnValues).
"" Or alternately, access the columns by name...
""stdout cr << {nRows:+=1, self['two'], self['one']}.
]
"" Support behavior
Method scope: AutoType [<'ToDo: Review'>
ffiMarshallToFFV
^weakIdFFV().__asFFV
]
}
[
|session| := SqSession('Testing').
try [
|trans := session.defTrans|
stdout.print('`nSqLite v%s [%s] Simple Test',trans.version,
trans.encoding).
stdout.print('`n---------------------------').
"*
trans.exec: 'create table tb_1(one varchar(1), two smallint);'.
trans.exec: 'insert into tb_1 values(''hello!'', 10);'
'insert into tb_1 values(''goodbye'', 20);'.
*"
trans.exec: 'select * from tb_1;'.
]
catch(SqException) [
stderr cr << thisSignal.asLogString.
thisSignal.resume.
];
finally: [session.close].
]
> -----Original Message-----
> From: squeak-dev-admin at lists.squeakfoundation.org [mailto:squeak-dev-
> admin at lists.squeakfoundation.org] On Behalf Of
> Torsten.Bergmann at phaidros.com
> Sent: Thursday, July 25, 2002 12:03 AM
> To: squeak-dev at lists.squeakfoundation.org
> Subject: Squeak and SQlite Database
>
> Is anybody using or planning to use the SQLite
> database with Squeak ? (http://www.hwaci.com/sw/sqlite/)
>
> From the page:
>
> SQlite is a C database engine in a small C library.
> Programs that link with the SQLite library can have SQL database
> access without running a separate RDBMS process.
>
> Features:
> - Implements a large subset of SQL92.
> - A complete database (with multiple tables and indices) is stored in
> a single disk file.
> - Atomic commit and rollback protect data integrity.
> - Small memory footprint: less than 20K lines of C code.
> - Four times faster than PostgreSQL
> - very simple C/C++ Interface requieres the use of only three
functions
> and an opaque structure
> - Build and tested under Linux and Win2000
> - Source are uncopyrighted. Use for any purpose.
>
> Could this be the base for platform independent common squeak database
?
>
> Bye
> Torsten
-------------- next part --------------
A non-text attachment was scrubbed...
Name: SqLite_SQL.sts
Type: application/stsc
Size: 8429 bytes
Desc: not available
Url : http://lists.squeakfoundation.org/pipermail/squeak-dev/attachments/20020725/f7e3d17a/SqLite_SQL.bin
More information about the Squeak-dev
mailing list
|