Glorp / SQLite3 issue

> On Wed, Jun 08, 2016 at 09:50:41AM +0200, Alistair Grant wrote:
> > PharoDatabaseAccessor DefaultDriver: GlorpSQLite3Driver.
> > session := GlorpBookDescriptorSystem sessionForLogin: login.
>
> Listing 1.12 on page 9 of the PDF says your code is missing a line:
>
>     session login.
>
> The example I posted in the announcement thread also sends #login, but to the
> accessor.
>
>   | login accessor |
>   login := Login new
>     ...
>     yourself.
>   PharoDatabaseAccessor DefaultDriver: GlorpSQLite3Driver.
>   accessor := PharoDatabaseAccessor forLogin: login.
> "==>"  accessor login. "<=="
>   (accessor executeSQLString: 'select * from sqlite_master') explore.
>   accessor logout.  
>
> If you follow the implementation of GlorpSession>>login you will see that it
> eventually sends #login to the accessor.
>
> Pierce

> Hi Pierce,
>
> On Wed, Jun 08, 2016 at 09:22:10PM +0800, Pierce Ng wrote:
> > On Wed, Jun 08, 2016 at 09:50:41AM +0200, Alistair Grant wrote:
> > > PharoDatabaseAccessor DefaultDriver: GlorpSQLite3Driver.
> > > session := GlorpBookDescriptorSystem sessionForLogin: login.
> >
> > Listing 1.12 on page 9 of the PDF says your code is missing a line:
> >
> >     session login.
> >
> > The example I posted in the announcement thread also sends #login, but to the
> > accessor.
> >
> >   | login accessor |
> >   login := Login new
> >     ...
> >     yourself.
> >   PharoDatabaseAccessor DefaultDriver: GlorpSQLite3Driver.
> >   accessor := PharoDatabaseAccessor forLogin: login.
> > "==>"  accessor login. "<=="
> >   (accessor executeSQLString: 'select * from sqlite_master') explore.
> >   accessor logout.  
> >
> > If you follow the implementation of GlorpSession>>login you will see that it
> > eventually sends #login to the accessor.
> >
> > Pierce
>
>
> Thanks for your response.  You're right, and I had tried this, however
> asking the session to login results in the following:
>
> UDBCSQLite3Connection(Object)>>doesNotUnderstand: #queryEncoding
> PharoDatabaseAccessor>>encoding
> GlorpSession>>loginIfError:
> GlorpSession>>login
> GlorpBookDescriptorSystem class>>getSession
> UndefinedObject>>DoIt
>
> In my image at least, there are no implementers of #queryEncoding.

> Hi All,
>
> I'm working my way through some of the examples in
> PharoInProgress/Glorp.  Adapting Pierce's code from his announcement:
>
>
> | workingDir dbName login session |
>
> workingDir := SmalltalkImage current imagePath asFileReference parent fullName
> , FileSystem disk delimiter asString.
> dbName := 'glorpbook.db'.
> login := Login new
> database: UDBCSQLite3Platform new;
> host: workingDir;
> port: '';
> username: '';
> password: '';
> databaseName: dbName;
> yourself.
> PharoDatabaseAccessor DefaultDriver: GlorpSQLite3Driver.
> session := GlorpBookDescriptorSystem sessionForLogin: login.
> session createTables.
>
> However this fails with:
>
> MessageNotUnderstood: receiver of "basicExecuteSQLString:" is nil
>
> If I add a breakpoint in GlorpSession>>accessor: manually execute:
>
> accessor login
>
> and then proceed, everything works fine.
>
> Given that Glorp has been around for so long, I guess that this is more
> likely an issue with the new UDBC driver, however I don't yet understand
> the architecture well enough to propose a fix.
>
> Any suggestions?
>
>
> Thanks!
> Alistair
>
>

> 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.

>
> 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.
>

> On 09/06/16 23:39, Alistair Grant wrote:
>
> [...]
> >
> > 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.
> >
> [...]
>
> I experienced something similar with NBSQLite3 before. Now I'm always
> closing my connections explicitly, once I have the collected info of the
> query in a "report" object of Pharo. I don't know if this could work in your
> case. In mine, the Panama Papers project has this, for example:
>
> ===============
> OffshoresDB class>>totalOffshoresByCountryRaw
>     "I query for the offshores by country data from a SQLite database file"
>     | queryResults query |
>     query := 'SELECT countries AS "country_name", count(countries) AS
> "total_offshores"
>                  FROM Addresses
>                  GROUP BY countries'.
>     self dataLocation exists
>     ifFalse: [ self inform: 'Download database first by running: ', String
> cr,
>              '"OffshoreLeaks updateDatabase"'  ]
>     ifTrue: [
>         queryResults := (self database open execute: query) rows collect: [
> :each | each data ].
>         self database isOpen ifTrue: [ self database close ].
>         ^ queryResults
>     ]
> ===============