Re: [Pharo-users] Glorp / SQLite3 issue

2016-06-11 Thread Alistair Grant 
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

Re: [Pharo-users] Glorp / SQLite3 issue

2016-06-09 Thread Alistair Grant 
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

Re: [Pharo-users] Glorp / SQLite3 issue

2016-06-09 Thread Pierce Ng 
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

Re: [Pharo-users] Glorp / SQLite3 issue

2016-06-08 Thread stepharo 

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

Re: [Pharo-users] Glorp / SQLite3 issue

2016-06-08 Thread Alistair Grant 
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

Re: [Pharo-users] Glorp / SQLite3 issue

2016-06-08 Thread Alistair Grant 
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

Re: [Pharo-users] Glorp / SQLite3 issue

2016-06-08 Thread Pierce Ng 
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