Postgres Provider

Feb 12, 2012 at 7:26 PM

I've implemented a postgres client for the IQToolkit. It passes all but 2 unit tests. As it stands i'm still not really convinced that everything will work correctly (what happened to cross apply from the blogs?) Some of the type mappings from Sql were just not provided in the postgres driver so it will throw a not-implemented exception for some of the more odd types (image??). There are also likely some differences in driver or server behavior compared to sql server that will yield strange behavior.

The postgres database does not have quite the same notion of auto-generated ID's that sql / mysql has. However it does support them. I had to change the GetGeneratedIdExpression() function signature to pull the autogenerated id's out of the database but it was a natural API change in this case. 

I had to move GetTableName() from BasicMapping into QueryMapping so that the PostgresClient could implement the GetGeneratedIDExpression(). The default implementation works in both places so it seems like a natural move too.

The GetRowsAffectedExpression() / IsRowsAffectedExpression() is still kind of a mystery to me. IQToolkit seems to only use these two methods to inject a custom expression into the syntax tree and then remove it again later. This could have been accomplished just as easily with a custom DbExpression. The syntax returned by the PostgresClient (inherited from the base) would not be valid if sent to the database, and i haven’t found any ‘identifier’ that can pull back this information. The multi-table unit tests do not work correctly with regard to ‘rows affected’ but i believe this is more the fault of IQToolkit when ‘allow multiple commands’ is disabled than the postgres implementation.

The current postgres driver has a bug that results in exceptions being thrown during the program termination / cleanup phase. The exceptions can be prevented by doing some extra 'cleanup' when disposing the connection. Because of this i went ahead and made QueryProvider implement IDisposable. You can now using(your linq provider here) and it will be cleaned up. As long as you dispose your postgres provider like this your program wont throw an exception during shutdown. The other providers clean themselves up just as nicely.

Case sensitivity is an interesting problem for implementing the postgres client. Postgres converts all identifiers (table names / column names / etc...) to lower case unless the identifier is enclosed in quotes. If a create statement is run with quotes, the table is forever created with that case. If the create statement is run without quotes, that table is forever lower case. IQToolkit quotes everything by default (yay) so it's entirely possible to use whatever mapping you want, but you have to operate in case-sensitive mode all the time. You must define your mappings with properly cased strings.

The LIKE operator in MSSQL is case insensitive (with the default configuration?) so there is at least one particular unit test written in such a way that it failed in postgres. I’ve tweaked this unit test (creating two copies) one that is intentionally case-insensitive and the other that is intentionally case-sensitive. MSSQL will fail the case sensitive test.

Postgres needed different SQL syntax in the unit test setup / teardown scripts.

The MultiTableTests pass except for 2  row-updated assertions. The row-updated assertions only seem to work correctly when multi-statement queries are supported. The single-statement code could be tweaked to get the correct row affected counts but that isn’t a postgres specific change.

Implementing multi-statement support for postgres will be a larger challenge, mostly because of the variable declarations. Local variables would have to be declared in a DO-DECLARE-BEGIN-END block for postgres and so far (even assuming you do get that working) i haven’t found a way to pull back any data from code that runs within one. Clever use of ado command parameters with output or input-output direction might be a smoother way to go about implementing. This will have to stand as a separate project for the time being.

My patch includes a postgres Northwind sql script found in the DBLinq unit tests. I modified it slightly to include the multi table test tables.

My patch also includes a copy of the NpgSql driver version 2.0.11.92. As IQToolkit is only set to compile against .NET 3.5 the .net 3.5 version of the driver is included This means you don't need to install it in the GAC. Just fire and forget. I updated the unit test pre-build steps to copy this dll over too.

Generating a patch was difficult (mostly because i have submitted several patches now and they are all in my local source branch). The submitted patch seemed to have some conflicts because of those other patches. I would suggest applying all of those previous patches however resolving the conflicts manually should also be possible.

The patch file can be found in the issue tracker ticket i made:  http://iqtoolkit.codeplex.com/workitem/17408