[vwnc] Using BOSS within SQLite3, but don't know how to update

Hi, folks,

I'm no good at SQL or any relational database so BOSS is easy for me to store objects on, update them and read back. The problem is I have to save to many files. So I try to insert boss into SQLite tables, one table for a class and each object has two fields one for id and one for byte array. (I'll describe in details below).

Now, I don't know how to update the database, when object changes.  I can not find an example in DatabaseAppDevGuide on this. Any advice please? I have know idea of SQL syntax.

Thanks in advance.

Here is the details in Quote format(so if you can't see it please click on the link). I add several extensions to Object class, which have taken me one day to debug out

bossByteArray

| byteArray |

(BinaryObjectStorage onNew: (byteArray := ByteArray new: 10000) writeStream)

nextPut: self;

close.

^byteArray

 

bossByteArray: bba 

"create an instance of this class read from boss array"

^(BinaryObjectStorage onOldNoScan: bba readStream) next

sqbObject

^self bossByteArray

 

sqbObjectID

^self subclassResponsibility 

 

sqbConnection: conn retrieve: ablock 

"conn := (ExternalDatabaseConnection new)

username: 'xxxx';

password: 'password';

environment:'SQLiteBoss.db';

yourself."

"self bdb: conn retrieve:[:strm|strm nextPutAll: 'select sql from sqlite_master where type= ''table''']"

| sssn |

^

[conn connect.

sssn := conn getSession.

conn inTransactionMode ifFalse: [conn begin].

sssn

prepare: (String streamContents: ablock);

blockFactor: 100;

execute.

sssn answer upToEnd] 

ensure: 

["conn commit."

conn disconnect]

 

sqbConnection: aConnection save: anObject 

"objects should understand #sqbObjectID and #sqbObject"

| sssn |

^

[aConnection connect.

sssn := aConnection getSession.

aConnection inTransactionMode ifFalse: [aConnection begin].

sssn 

prepare: (String streamContents: 

[:strm | 

strm 

nextPutAll: ('INSERT INTO <1s> ( SQBOID , SQBOBJ) VALUES (:sqbObjectID ,:sqbObject)' 

expandMacrosWith: self sqbTablename)]).

sssn bindInput: anObject.

sssn execute.

sssn answer] 

ensure: 

[aConnection commit.

aConnection disconnect]

sqbInitialize: aConnection 

| aSession |

aConnection

disconnect;

connect.

aSession := aConnection getSession.

aSession

prepare: 'PRAGMA synchronous = OFF;';

execute;

answer.

aConnection inTransactionMode ifFalse: [aConnection begin].

aSession

prepare: (String streamContents: 

[:strm | 

strm

nextPutAll: 'CREATE TABLE IF NOT EXISTS ';

nextPutAll: self sqbTablename;

nextPutAll: ' ';

nextPutAll: ' (SQBOID TEXT PRIMARY KEY ON CONFLICT IGNORE, SQBOBJ BLOB );']);

execute;

answer.

aConnection commit.

aConnection disconnect

 

sqbConnection: conn retrieveOID: sqbObjectID 

"The SQL should use 'ID' but not ID

   SELECT SQBOID, SQBOBJ FROM Table  WHERE SQBOID = 'ID' "

^self sqbConnection: conn

retrieve: 

[:strm | 

strm nextPutAll: ('SELECT SQBOID, SQBOBJ FROM <1s>  WHERE SQBOID = <2p>' 

expandMacrosWith: self sqbTablename

with: sqbObjectID)]

 

Best Regards,

empt