PostgreSQL – 0.0.10

Whenever a new project is started, one has to consider, how to get persistence of your objects. One typical way is to use relational databases and VA has support for ODBC, DB2 and Oracle databases.

In commercial projects Oracle and DB2 can be seen very often. The license issues are not really a problem any more. When considering DB2 you may download the Express-C version and you get a full qualified database with only a few limits (2-core CPU, but no space limit), but one could live with that.

Oracle also offers a “free” database, but their limits are much tighter and therefore I would only consider DB2 as a good solution for a single user or smaller workgroup project.

The support for these both database under VASmalltalk can be considered as solid. It offers base support, but enhanced features should not be expected. You might not find XML support – therefore the database drivers need definitely enhancements here.

The problem with these database are quite simple: they are *very* large software tools and far from being easily used. Even the connect software for DB2 (the client software) might be much larger than your own software you want to develop with. Therefore its a reasonable approach to look for other relational databases.

For single user solutions one should consider SQLite – and therefore I realized the wrapper for that database. The biggest point is the easy way of using this library – very simple to use, simple to distribute, simple to install – but its a single user software system.

For workgroup solutions you may consider the free PostgreSQL database, which is much smaller than the DB2 database (about 1/10 of the distribution size of the DB2). And instead of using the ODBC driver, you might think about using the native connection library directly (“pglib.dll”). Then you have to write a wrapper for that database.

The main part of the PostgreSQL wrapper is located in the configuration maps MSKPostgreSQL. Tests for that library are located in the map MSKPostgreSQLWithTests, which loads the tests and the library. The tests are also using the map MSKSystemExtension, but mainly for the fact, that some file oriented helper methods are located in that map. The wrapper itselfs does only need the apps MSKPlatformExtension and MSKGeneralDatabase.

MSKPlatformExtension contains code for interfacing low-level c libraries. All my interfaces (Lua, OpenGL, FreeImage, Rexx) for external c libraries are using (or will use in the future) this map.

MSKGeneralDatabase contains general definitions for the PostgreSQL wrapper and perhaps also later my SQLite wrapper. Mainly exceptions are defined here and a special subclass for errors are also defined here.

When creating wrappers for external libraries, several questions have to been asked and answered:

* calling convention
* threadsafe

Calling conventions under Windows are either cdecl or stdcall. VASmalltalk only supports stdcall. The problems with different calling conventions may be discussed elsewhere – I simply do not know, if VASmalltalk looses memory or not, when calling cdecl external functions.

Threadsafe is another questions and this was a topic for the SQLite library some years ago, but this has been solved now. PostgreSQL is threadsafe – the only thing you have to look for is, that the connection structure is only used in ONE active calling of the database. The calls itselfs may be done using different threads.

This approach has been used in the PostgreSQL wrapper library. All calls, which may result into network traffic are called in a way, that they are done either in synchronous way (main thread of Smalltalk) or by using a static-future call (blocking only the Smalltalk thread doing the call). All other calls (e.g. preparing the result for the API caller, asking for status information …) are generally done in a synchronous way.

Preparing and Installation

The wrapper has been programmed against the 8.3.6 database version of PostgreSQL. VASmalltalk will not find the libraries “pgsql.dll” due to the fact, that the PostgreSQL installation path is not part of the search path. Adding the “bin”-directory to the PATH environment produces a library problem: VASmalltalk and PostgreSQL are both using the OpenSLL library – but they distribute different versions. The wrapper will not work, because Windows will not be able to load the “pgsql.dll” library – due to mismatched dynamic libraries.

Therefore I decided to copy all libraries from PostgreSQL to the VASmalltalk directories – replacing the OpenSLL libraries delivered with VA. Then the whole database stuff is working.

Introducing the code

In earlier wrapper I concentrated all libraries call in one single class, which was a subclass of MSKExternalLibraryInterface. I changed this layout and you may find the API calls in the classes MSKExternalLibraryInterface, PGConn and PGresult.

All API calls using the PGConn pointer are defined in the PGConn class, all API calls using the PGResult pointer data structure are defined in the PGResult class.

All method should have the same structure – similiar to the methods in the SST framework:

connectToDBVia: connectionString
	"
	ACTION
		initiate a connection to a database location defined by connectionString

	PARAMETERS
		connectionString: 

	EXCEPTIONS
		ExMSKDBNCException

	RETURN VALUE
		
	"

	| address |

	address :=
		self lowLevelExecute: PQconnectdb parameters: (Array with: connectionString asParameter).

	address ~= 0 ifTrue: [^PGConn address: address api: self].

	ExMSKDBNCException signalWith: 'apiConnect' with: connectionString

The first comment within the method contain lots of method signature information.

The begin the programming with this wrapper you should get an instance of the main interface class and define, how to handle the calls to the external library.

api1 := MSKPSQLInterface startWithFutureAndSynchronized.

After this step you have a connection to the library itselfs and you may now connect to the database via:

dbC := api1 connectToDBVia: 'host=localhost port=5432 dbname=mskpsqltest user=postgres password=postgres'.

and you get an connection handler to a database. If it is not possible to connect to this database you get one of the three defined exceptions.

Now you may create a transaction and import a blob object:

 dbC beginTransaction.
 oid := dbC blobImportFromFile: cfsPathToLargeFile asString.
 ...
 dbC commitTransaction.

When finishing your work, you may close the connection and – do NOT forget – return the static future to the Smalltalk system. Actually this can not be done automatically:

  "close the connection to the database ...."
 dbC finish
  "return the static future ..."
 api1 destroyFuture.

Putting all together you may get a method, which creates several execution threads and stores blob’s multiple thimes and even with Smalltal you may get 75% CPU time busy …

The whole action is triggered via several Smalltalk background threads. From these Smalltalk threads the most time consuming API calls are done via external OS threads. The computer is getting under heavy load – but still then your Smalltalk system is very responsive.

methodForTestingHeavyLoad
  | cfsPathToLargeFile maxNumberOfThreads |
  "change to a number as you like ... e.g. number of processors"
  maxNumberOfThreads := 8.

  "change to file you have on your machine"
  cfsPathToLargeFile := CfsPath named: 'e:\test.bmp'.

  1 to: maxNumberOfThreads do: [:pid |
    | aProcess |

    aProcess :=
        [:myPID | | api1 dbC |
          api1 := MSKPSQLInterface startWithFutureAndSynchronized.
          [
            dbC := api1 connectToDBVia: self dbCorrectConnectionString.
            100 timesRepeat: [| oid |
              dbC beginTransaction.
              oid := dbC blobImportFromFile: cfsPathToLargeFile asString.
              (oid ~= 0)
                ifTrue: [  Transcript cr;  show: 'Inserted from process ' , myPID asString , ': ' ,oid asString ].
              dbC commitTransaction
            ].
            dbC finish
          ]
          when: ExMSKDBNCException
          do: [:sig | Transcript cr; show: 'No connection to database possible !'. sig exitWith: nil ]
          when: ExMSKDBCException
          do: [ :sig | sig exitWith: nil ].

         "to release the static future again ..."
         api1 destroyFuture.
         Transcript cr; show: 'Process ' , myPID asString , ' finished'
      ]
      newProcessWith: (Array with: pid).
      aProcess
       priority: Processor userBackgroundPriority;
      resume
  ]

The PostgreSQL wrapper is not adapted to the VA database framework – this step would take much more time to do, than this native wrapper.

Here is the latest – work in progress – code:

mskpsqlwrapper-0-0-10

You may also look at vastgoodies, where I also publish my code.

This entry was posted in Smalltalk and tagged . Bookmark the permalink.