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

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

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