I last wrote about football.db in a Jul 2014 blog post. Four years have gone by, and the World Cup is here again. This time around, I've started building a Glorp descriptor system for the World Cup data.
football.db's data is described in YAML files which are assembled into SQLite databases using tools written in Ruby. From the constructed 2014 and 2018 World Cup databases, I've created SQL dumps and placed them in the repo. To get an SQLite database from the 2018 SQL dump file:
In its current state, footballdb-Glorp allows querying the initial World Cup first round group membership.
This Glorp descriptor may not be completed in time for this World Cup which is starting in a few days, but will be in time for the next one for sure! :-) Load it thusly:
From its documentation, SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).
I've added Boolean handling to UDBC-SQLite. When writing to an SQLite database from Pharo, true is written as integer 1 and false as integer 0. SQLite uses dynamic typing, and any column in an SQLite database, except an INTEGER PRIMARY KEY column, may be used to store a value of any type, irrespective of the column's type declaration. As such, when writing Boolean values to a database, UDBC-SQLite does not check the database's type declaration.
When reading an SQLite database, UDBC-SQLite does check a column's type declaration: If a column is Boolean, UDBC-SQLite reads 1 as true, 0 as false, NULL as nil, and any other integer values raises an exception. I've encountered real world data where the string "t" means true and "f" means false for a Boolean column, so UDBC-SQLite handles these cases too.
Glorp has been similarly updated. Loading GlorpSQLite, from my development fork for now, installs both UDBC-SQLite and Glorp:
All Glorp unit tests should pass. Tested on Linux using fresh 32- and 64-bit 60540 images.
I have started a booklet on Pharo, hopefully the first of, um, more than one. It is entitled RedditSt20, on my fork and extension of Sven Van Caekenberghe's excellent "Reddit.st in 10 elegant classes", to cover the following in another 10 or so classes:
The book is being written using Pillar, of course. Note that the Pharo 5 version of Pillar that I downloaded from InriaCI doesn't work - the supporting makefiles aren't able to obtain the output of "./pillar introspect <something>". Use the Pharo 6 version.
I've updated ConfigurationOfGlorp for Pharo 6 and added catalog methods to ConfigurationOfGlorpSQLite.
Take a fresh Pharo 60365 image, the latest as of yesterday's download. Launch it, open the Catalog Browser, and install GlorpSQLite from there.
Run the Glorp tests in TestRunner. The result should be green, with all 889 tests passed and 12 tests skipped. The database file is sodbxtestu.db in your image directory.
(On Pharo 5, Glorp runs and passes total of 953 tests. Something to look into.)
I've created ConfigurationOfGlorpSQLite on STH.
Take a fresh Pharo 5 image. Make a script like the following and run it on the image:
When done, fire up the image again, and run the Glorp tests in TestRunner. The result should be green, with all 953 tests passed and 12 tests skipped. The database file is sodbxtestu.db in your image directory.
I'm pleased to announce the release of Glorp-SQLite3 for Pharo 5.
Developed and tested on Linux. Known working on Windows 7. Your Pharo 5 VM needs to be able to find libsqlite3.so or the Windows equivalent.
Take a fresh Pharo 5 image. Run the following:
Run the Glorp tests in TestRunner. All tests should pass, with 12 tests skipped. The database file is sodbxtestu.db in your image directory.
Using the Pharo v40592 image with which I had verified NBSQLite3 for Glorp, in this blog post I go through doing the same with the PostgresV2 pure-Smalltalk database driver.
Outside of Smalltalk, create the database 'sodbxtest', user 'sodbxtest' with password 'sodbxtest':
In Smalltalk, firstly, install PostgresV2:
Open Test Runner and runs the PostgresV2 tests. On my Linux Mint machine, using a vanilla PostgreSQL 9.3 installation, 23 of 24 tests passed, and TestPGConnection>>#testNotify2 erred.
Now that we know the PostgresV2 driver can talk to our database, using the Monticello browser, open the PostgresV2 repository and load the package GlorpDriverPostgreSQL. Here I had to edit NativePostgresDriver>>connectionArgsFromCurrentLogin: to comment out the second last line:
This is because GlorpDatabaseLoginResource class>defaultPostgreSQLLocalLogin does not specify encodingStrategy, meaning it is nil and will respond to #asSymbol with DNU.
Next, in a playground, execute the following:
Open Test Runner and run the Glorp tests.
Tested on Linux Mint 17.
I've integrated NBSQLite3 into Glorp on the current Pharo 4.0 v40592 beta image.
Firstly, install NBSQLite3 (TorstenBergmann.7) and then Glorp (TorstenBergmann.42) from the config browser.
Then, using the Monticello browser, open the NBSQLite3 repository and load the packages NBSQLite3-Glorp and NBSQLite3-Test-Glorp. Next, open the Glorp repository and load the packages Glorp-PierceNg.97.mcz and GlorpTests-PierceNg.44.mcz.
In a workspace/playground, execute the following:
Open Test Runner and run the Glorp tests.
Tested on Linux Mint 17 and OSX Mavericks. 2 fewer tests passed on Linux.
Curiously, #testLargeBlob, #testLargeClob and #testLargeText passed on the Pharo 3 image that I wrote this code on.
The database file created by the tests is sodbxtest.db.
Made really good progress with NBSQLite3 for Glorp.
On the failed tests:
#testReadTimestamp - SQLite3 does not support date/time/timestamp types natively. More investigation needed. #testDateCast - The test itself says, "This test will fail on SQLite which has no DB Date type; see test comment." #testDeleteAndReinsertTwiceWhenRemovingFromAnExclusiveCollection - This test is expected to result in a primary key conflict. Without studying the code, my conjecture is that the test's failing has to do with SQLite's primary key handling. #testInt8 - Integer overflow on 32-bit SQLite, I think. #testPreparedStatementsAreReused - PostgresV2 doesn't support prepared statements. NBSQLite3 does. Mayhaps this is why? More investigation needed. #testUpdatingComment - Test passes on OSX Mavericks but fails on Linux Mint 17. No idea why. #testUpdatingOtherThing - Test passes on OSX Mavericks but fails on Linux Mint 17. No idea why.
The entire GlorpOptimisticLockingTest and GlorpTimestampTest suites are skipped, because some of the tests fail, and foobars the Pharo-SQLite interface, causing many subsequent tests to fail, requiring restart of the Pharo image. Still need to look into these.
Making good progress with NBSQLite3 for Glorp.
Sven van Caekenberghe has written a very nice tutorial implementing a Reddit clone in Pharo using Seaside, Glorp and PostgreSQL. Sven also makes available a prebuilt image containing the application.
Seeing that the image contains Glorp working with the PostgresV2 driver, I set about integrating NBSQLite3 with Glorp. After about an afternoon's work, I now have Reddit.st working with Glorp+NBSQLite3.
There is still much to be done to get Glorp fully working with NBSQLite3: Some tests apparently expect Glorp proxies, but are getting OrderedCollections, and one particular test rendered my Linux X session non-responsive to mouse and keyboard, except for screen brightness key chords!