PGClientKit

This framework is use for communication with a PostgreSQL server from your Cocoa or iOS application. It uses the PostgreSQL library libpq to do most of the work, but also provides a bridge between PostgreSQL data types and NSObject plus other native types (see below for what is supported).

In addition, there are two main areas which the framework covers:

• The PGResult set of classes provides methods to read data and status returned from the server once commands have been executed.
• The PGQuery set of classes provides the ability to construct commands and queries to send to the server in a programmatic way. There are a variety of subclasses which allow you to create different types of queries; for example, PGQuerySelect is used to create SELECT statements, and PGQueryUpdate can be used to programmatically create UPDATE statements, and so forth.

Table of contents

• Obtaining and/or building the framework for iOS and Macintosh
  • Building the frameworks from source
  • Using the frameworks within your application
• Connecting and disconnecting to remote servers
  • Connection URL's
  • Passwords and connection challenges
  • Ping and reset methods
• Implementing a delegate
  • Retrieving passwords
  • Notifications
• Building query statements
  • The PGQuery set of classes
  • Extending PGQuery
• Executing statements
  • Text and binary modes
  • Binding parameters
  • Cancelling execution
  • Preparing statements
  • Transations and rollback
• Data returned from the server
  • Using PGResult
  • Implementing custom data types for PGResult
  • Outputting data in text, XML, HTML and CSV formats
• Unit tests
• Implementing connection pools

Obtaining and/or building the frameworks

To obtain the latest version of the frameworks, you can navigate to the releases section of the project, where you'll find compiled versions of the frameworks for both iOS and Macintosh in a single archive file.

Building the frameworks from source

Alternatively, you can build the frameworks from source. There are two targets for the framework, one for the Mac OS platform and one for the iOS platform. The simplest way is to use the build-frameworks.sh shell script:

cd postgresql-kit
etc/build-frameworks.sh

This builds all the frameworks. In order to build only the single framework for both iOS and Macintosh, use the following:

cd postgresql-kit
CONFIGURATION=Release
xcodebuild -target "PGClientKit_ios" -configuration ${CONFIGURATION} || exit -1
xcodebuild -target "PGClientKit_mac" -configuration ${CONFIGURATION} || exit -1

Using the frameworks within your application

The two target frameworks are PGClientKit_ios.framework and PGClientKit.framework. In general these will be placed in the build/Release folder within the project folder. Add these to your linked frameworks. For Macintosh, you'll need to copy the framework into your Application as part of the build phases: Simply add a "Copy Files" build phase and use "Frameworks" as the destination.

To use the framework within your source code, include the header file and create a PGConnection object. For example:

#if TARGET_OS_IPHONE
#import <PGClientKit_ios/PGClientKit.h>
#else
#import <PGClientKit/PGClientKit.h>
#endif

int main(int argc, char** argv) {
  @autoreleasepool {
    PGConnection* db = [PGConnection new];
	...
  }
}

Due to the asyncronous nature of the framework, you'll need to ensure there is a runloop operating when using the framework, since data is processed by callback mechanisms triggered by the runloop. If you're using the framework as a Foundation tool, this isn't a problem however. For more information, see the sample code PGFoundationClient & PGFoundationServer which explains patterns you can use.

Connecting and disconnecting to remote servers

There are several methods to connect and disconnect to remote PostgreSQL servers, and do a variety of tasks related to these connections. There are in general syncronous and asyncronous versions of these methods. The latter would be used when you don't wish to block foreground activities.

• The connectForURL:whenDone: method connects to the remote server asyncronously, and returns the result of that connection attempt.
• The connectForURL:usedPassword:error: method connections to the remote server syncronously.
• The disconnect method will disconnect from the remote server syncronously.
• The pingForURL:whenDone: method attempts to determine if the remote server is available for connecting asyncronously.
• The pingForURL:error: method performs the same function, but syncronously.
• The resetWhenDone method attempts to reconnect to the remote server with the same URL asyncronously.
• The resetReturningError: method attempts the same syncronously.

Examples of these methods are provided in the following section.

Connection URL's

To connect to a PostgreSQL server, you'll need to construct an NSURL object which describes the endpoint. Connection URL's can use any form described within the libpq documentation

In general, the parameters which determine how the connection is made are as follows:

• The connection can either be by file socket (where the PostgreSQL server is on the same computer or network socket (where communication takes place through TCP/IP).
• Where the communication takes place on a network socket, the connection can be encrypted using SSL or unencrypred.
• On a file socket the folder in which the socket is located is required as the hostname argument.
• On a network socket, the remote server is addressed via a name, IPv4 address or IPv6 address.
• A username and database name are always required, but a password is optional.
• Additional parameters can be specified to modify the communication in some way. For example, the encoding used to communicate, the network timeout parameter, etc.

The URL is constructed in the normal manner. The method or scheme of the URL can be set as:

• postgresql:// and pgsql:// sets sslmode as prefer
• postgresqls:// and pgsqls:// sets sslmode as require

Where the sslmode is require, communication can't continue unless the communication is encrypted. Here are some example connection URL's:

postgresql://user@:56/database
pgsqls://other@localhost/otherdb?connect_timeout=10

The framework helper category NSURL+PGAdditions provides you with some convenience methods in order to construct URLs:

@interface NSURL (PGAdditions)
+(id)URLWithLocalDatabase:(NSString* )database username:(NSString* )username params:(NSDictionary* )params;
+(id)URLWithSocketPath:(NSString* )path port:(NSUInteger)port database:(NSString* )database username:(NSString* )username params:(NSDictionary* )params;
+(id)URLWithHost:(NSString* )host ssl:(BOOL)ssl username:(NSString* )username database:(NSString* )database  params:(NSDictionary* )params;
+(id)URLWithHost:(NSString* )host port:(NSUInteger)port ssl:(BOOL)ssl username:(NSString* )username database:(NSString* )database params:(NSDictionary* )params;
@end

For example, you may want to construct and connect to a local database:

	PGConnection* db = [PGConnection new];
	NSURL* dsn = [NSURL URLWithLocalDatabase:@"postgresql" username:@"postgresql" params:nil];
	if([db connectWithURL usedPassword:nil error:nil]==NO) {
		...
	}

Alternatively, connect to a remote server and force encrypted connections:

	PGConnection* db = [PGConnection new];
	NSURL* dsn = [NSURL URLWithHost:@"remote.database" ssl:YES database:@"postgresql" username:@"postgresql" params:@{ @"connect_timeout": @"20" }];
	NSError* error = nil;
	BOOL usedPassword = NO;
	if([db connectWithURL usedPassword:nil error:nil]==NO) {
		...
	}

You can create a URL from a dictionary of parameters using the `initWithPostgresqlParams' instance method. For example:

	NSURL* url = [[NSURL alloc] initWithPostgresqlParams:@{
		@"host": @"localhost",
		@"database": @"postgresl",
		@"username": @"username"
	}];

The parameters you can use are explained in the libpq documentation. If you wish to modify the parameters before they are used for opening the connection, you can pass these as part of the URL, or you can modify the dictionary of parameters during the connection process by the delegate method connection:willOpenWithParameters. For example, you will need to add a password into the parameters dictionary. More information about implementing a delegate is provided below.

There are three parameters which are added silently into the connection parameters if they are not already specified:

• connect_timeout is set from the timeout property of PGConnection if it it set. You can modify this before connection if necessary, or implement the delegate.
• application_name is set to the name of the currently running process. This is useful for debugging.
• client_encoding is set to the default PGConnectionDefaultEncoding string if not already set.

There are a couple of additional NSURL extension methods worth mentioning:

-(NSDictionary* )postgresqlParameters;
-(BOOL)isSocketPathURL;
-(BOOL)isRemoteHostURL;

The postgresqlParameters method will return the dictionary of parameters, or it will return nil if the URL is not a valid connection URL. The last two methods return YES if the connection will be made through a file-based socket or a TCP/IP socket respectively.

Connecting syncronously and asyncronously

You can use blocking or non-blocking versions of the connect, ping and reset operation methods. There is only a blocking version of disconnect. You would generally want to use the blocking version when you want your execution to be suspended whilst the operation is in progress. However, for most Cocoa and Cocoa Touch applications, you'll want to use the non-blocking versions.

Regardless of which you use, you'll need to have a run loop running. This comes for free with Cocoa and Cocoa Touch development, or you can use the examples PGFoundationClient & PGFoundationServer if you want to build a Foundation tool.

The blocking versions will generally return a boolean value indicating success (YES) or failure, and set an error object on failure. The non-blocking versions require you to provide a callback, which can be used to process the results of the operation on completion. In this case, if the error parameter is nil, the operation was successful.

Here are the signatures of the blocking versions:

-(BOOL)connectWithURL:(NSURL* )url usedPassword:(BOOL* )usedPassword error:(NSError** )error;
-(BOOL)pingWithURL:(NSURL* )url error:(NSError** )error;
-(BOOL)resetReturningError:(NSError** )error;
-(void)disconnect;

And here are the signatures of the non-blocking versions:

-(void)connectWithURL:(NSURL* )url whenDone:(void(^)(BOOL usedPassword,NSError* error)) callback;
-(void)pingWithURL:(NSURL* )url whenDone:(void(^)(NSError* error)) callback;
-(void)resetWhenDone:(void(^)(NSError* error)) callback;

Connection goes through several phases, which can be monitored by implementing the delegate method connection:statusChange:description:. The following status code values might be set:

• PGConnectionStatusDisconnected The connection is not yet connected
• PGConnectionStatusConnected The connection is established, and idle
• PGConnectionStatusRejected The connection has been rejected
• PGConnectionStatusConnecting The connection is being established
• PGConnectionStatusBusy The connection is established, but busy

For the connection operation, the errors returned will be one of the following error codes:

• PGClientErrorState The connection is in an invalid state for this operation
• PGClientErrorParameters The parameters provided are invalid
• PGClientErrorNeedsPassword The connection operation could not continue without a password
• PGClientErrorInvalidPassword The password provided was invalid
• PGClientErrorRejected There was some other issue with the connection credentials
• PGClientErrorUnknown An unknown error occurred

The error domain will be set to the constant PGClientErrorDomain and a description will be provided in the NSError user information dictionary. More information on ping and reset are provided below.

Passwords and connection challenges

Although it is accepted, you should not encode the password within the URL itself, but store and retrieve it (ideally) in the Apple Keychain mechanism. There is a helper class PGPasswordStore which can be used in order to do this.

In order to pass through your password, implement the delegate method connection:willOpenWithParameters and set the password there. For example,

-(PGPasswordStore* )passwordstore {
	static PGPasswordStore* passwordstore = nil;
	if(passwordstore==nil) {
		passwordstore = [PGPasswordStore new];
	}
	return passwordstore;
}

-(void)connection:(PGConnection* )connection willOpenWithParameters:(NSMutableDictionary* )dictionary {
	NSString* password = [[self passwordstore] passwordForURL:[self url]];
	if(password) {
		[dictionary setObject:password forKey:@"password"];
	}
}

The connection operation will return in one of three states:

• The connection was accepted. The usedPassword parameter which is returned by the connection method can be used to determine if the password should be saved in the password store (and hence the keychain).
• The connection was rejected due to an invalid password. You might want to remove the password from the password store, and ask the user again to enter their password before attempting to reconnect.
• The connection was rejected due for another reason. You would likely want to report the error to the user and ask them to modify their connection parameters.

Here is some code which demonstrates these possibilities:

   TODO: Sample Code

Ping and reset methods

Implementing a delegate

Retrieving passwords

Notifications

Building query statements

The PGQuery set of classes

Extending PGQuery

Executing statements

Text and binary modes

Binding parameters

Cancelling execution

Preparing statements

Transations and rollback