Glorp / SQLite3 issue

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Glorp / SQLite3 issue

alistairgrant
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

Reply | Threaded
Open this post in threaded view
|

Re: Glorp / SQLite3 issue

Pierce Ng-3
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


Reply | Threaded
Open this post in threaded view
|

Re: Glorp / SQLite3 issue

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


Thanks!
Alistair

Reply | Threaded
Open this post in threaded view
|

Re: Glorp / SQLite3 issue

alistairgrant
On Wed, Jun 08, 2016 at 07:49:13PM +0200, Alistair Grant wrote:

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

P.S.  If I add:

UDBCSQLite3BaseConnection>>queryEncoding

        ^#'utf-8'


I'm able to create tables and populate the database (which is as far as
I've got).  But of course, not sure if this is the appropriate solution.

Thanks!
Alistair


Reply | Threaded
Open this post in threaded view
|

Re: Glorp / SQLite3 issue

stepharo
In reply to this post by alistairgrant
Thanks Alistair

This is a great initiative. Documentation is our shared knowledge. I
spent time editing the document

but I do not have time and the need for glorp now.

Stef


Le 8/6/16 à 09:50, Alistair Grant a écrit :

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


Reply | Threaded
Open this post in threaded view
|

Re: Glorp / SQLite3 issue

Pierce Ng-3
In reply to this post by alistairgrant
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.

Pierce

Reply | Threaded
Open this post in threaded view
|

Re: Glorp / SQLite3 issue

alistairgrant
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


Reply | Threaded
Open this post in threaded view
|

Re: Glorp / SQLite3 issue

Offray Vladimir Luna Cárdenas-2
Hi,


On 09/06/16 23:39, Alistair Grant wrote:

[...]

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

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
     ]
===============

Cheers,

Offray

Reply | Threaded
Open this post in threaded view
|

Re: Glorp / SQLite3 issue

alistairgrant
Hi Offray,

On Fri, Jun 10, 2016 at 09:37:58AM -0500, Offray Vladimir Luna C??rdenas wrote:

> 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
>     ]
> ===============

Thanks for the suggestion.  I had tried this, however my application is
fairly interactive, and Glorp proxies require the session to be logged
in to operate correctly, so it doesn't work in my situation,
unfortunately.

I guess I'll write a startup script that closes and re-opens the session
(I don't want to have to close everything when saving an image).

Thanks again,
Alistair