Re: [Pharo-users] Glorp / SQLite3 issue

June 10, 2016

      Hi Pierce,

On Thu, Jun 09, 2016 at 10:14:06PM +0800, Pierce Ng wrote:
...
On Wed, Jun 08, 2016 at 08:25:27PM +0200, Alistair Grant wrote:
...
...
asking the session to login results in the following:
UDBCSQLite3Connection(Object)>>doesNotUnderstand: #queryEncoding
PharoDatabaseAccessor>>encoding
GlorpSession>>loginIfError:
GlorpSession>>login
GlorpBookDescriptorSystem class>>getSession
UndefinedObject>>DoIt
Interesting. Meaning none of Glorp's ~900 tests sends #login to a session
object directly...?
Ok, GlorpDatabaseLoginResource>>setUp looks like it asks the accessor to login.
Transcript show: self class name asString, ' setUp'; cr.
    super setUp.
    self login: self class defaultLogin.
    accessor := DatabaseAccessor forLogin: login.
    accessor login.
Looking around using Finder, seems this is how Glorp's test suite does
the login.
Try the following. It works on my computer. :-) I don't have the example book
descriptor, so I'm reusing the test suite's.
| workingDir dbName login accessor session |
    workingDir := SmalltalkImage current imagePath asFileReference parent fullName, 
        FileSystem disk delimiter asString.
    dbName := 'sodbxtestu.db'.
    login := Login new
        database: UDBCSQLite3Platform new;
        host: workingDir;
        port: '';
        username: '';
        password: '';
        databaseName: dbName;
        yourself.
    PharoDatabaseAccessor DefaultDriver: GlorpSQLite3Driver.
    accessor := PharoDatabaseAccessor forLogin: login.
    accessor login.
    [   session := GlorpSession new.
        session system: (GlorpDemoDescriptorSystem forPlatform: login database).
        session accessor: accessor.
        session beginTransaction.
        session inUnitOfWorkDo: [  
            | table row |
            table := session system tableNamed: 'GR_ADDRESS'.
            row := DatabaseRow newForTable: table.
            row at: (table fieldNamed: 'ID') put: 1.
            row at: (table fieldNamed: 'STREET') put: 'Alpha'.
            row at: (table fieldNamed: 'HOUSE_NUM') put: '300'.		
            session writeRow: row ].
        session commitTransaction.
    ] ensure: [ accessor logout ]
After running the test suite, sodbxtestu.db contains the test schema but has no
data. After running the above snippet:
sqlite> select * from GR_ADDRESS;
    ID|STREET|HOUSE_NUM
    1|Alpha|300
    sqlite>
(The snippet is still low level, because it creates the DatabaseRow object
explicitly.)
Just for kicks, I ran the snippet a second time and got this:
GlorpDatabaseWriteError: UNIQUE constraint failed: GR_ADDRESS.ID
...
UDBCSQLite3BaseConnection>>queryEncoding
  ^#'utf-8'
If #queryEncoding is a Glorp-specific thing, then from an architectural purity
perspective I prefer to not have this in UDBCSQLiteBaseConnection which is
intended to be a thin layer over the SQLite C API.
Can you try the above method see if it works for you.
Yep, I get the same results (sodbxtestu.db is populated, and the
constraint error on 2nd attempt).

I've been able to define a couple of classes / tables with foreign keys
and it is all working fine.  I'm still to set up a many-to-many
relationship and the more complex queries, but it is all looking good so
far.

I have seen one other problem with UDBC-SQLite3: if a connection is open
when the image is saved, it must be manually closed and re-opened after
the image is opened.  Should this be handled automatically?  If you
think this should be working I'll put together some code to reproduce
it.

Thanks!
Alistair