Skip to content
This repository has been archived by the owner on Jan 22, 2024. It is now read-only.

ImplementationNotes

Jump to bottom
David Thorpe edited this page Mar 19, 2015 · 11 revisions

PGClientKit

Transactions

Here is how transactions will be implemented. A transaction is a kind of query, with an ordered array of queries.

  PGQueryTransaction* transaction = [PGQueryTransation new];
  PGQuery* q = [PGQuery query:@"SELECT * FROM $1"];
  [transaction add:q bindings:@[ @1 ]];
  [transaction add:q bindings:@[ @2 ]];
  if([connection execute:transaction error:&error]==NO) {
    // transactin will have been rolled back
  }

The transaction will only execute if there is no transaction already in progress, or else a state error is raised. If one of the statements fails, then rollback is made, or else commit. A transaction cannot be nested within another transaction.

Type conversions

global data structure contains type conversions

typedef struct {
  Oid pgtype;
  id fromdata(),
  NSData* todata(id),
  const char* typename
} PGResultTypeConversionType;

struct conversions[] = {
  { PG_TYPE, binary_to_nsobject, nsobject_to_binary, @"typename" },
  ...
  { nil, nil, nil, nil }
};

To add a conversion (or override) use the following:

void PGResultTypeRegisterCustom(.....);

Supported Data types

This section to be rewritten It supports conversion of data types from postgres types to NSObject types. The following types are currently supported (I think):

bool        NSNumber     Boolean value
bytea       NSData       Data up to 4GB in size
char        NSString     Fixed length character data in UTF8
int8        NSNumber     Long integers
int2        NSNumber     Short integers
int4        NSNumber     Integers
text        NSString     Text up to 4GB in size
float4      NSNumber     Floats
float8      NSNumber     Double Floats
varchar     NSString     Fixed length character data in UTF8
date        NSDate       Dates
timestamp   NSDate       Date and time in UTC 
timestamptz NSDate       Date and time with timezone

Any cell which is NULL will be converted to the [NSNull null] value. Any cell which has an unsupported data type is returned as an NSData object.

Connection Pooling

Connection pooling will be supported, so that it's possible to execute a command from a connection in a pool, rather than from the main connection.

  PGConnectionPool* pool = [PGConnectionPool sharedSimplePool]; // one connection per tag
  PGConnectionPool* pool = [PGConnectionPool sharedRoundRobinPool]; // multiple replicated databases per tag
  PGConnectionPool* pool = [PGConnectionPool sharedShardedPool]; // sharded data across several databases....
  [pool setDelegate:self];
  [pool setUseKeychain:YES];
  [pool addConnectionWithURL:url weight:weight tag:tag];
  [pool removeConnectionsForTag:tag];

  [pool query:query tag:tag callback:^(PGResult* result,NSError* error) {
  	  ....
  }];

PGConnectionPool tag => [ { connection, url, weight }, ... ] tag => [ { connection, url, weight }, ... ]

A tag refers to a set of connections. You add a connection into a tag using:

NSUInteger index = [pool addConnectionWithURL:url weight:weight tag:tag];

You can remove a connection using [pool removeConnectionWithTag:tag index:index];

The index returned will be used to modify each connection, it should start at 1. Once you've added in all your connections, you can [pool connectWithTag:tag] and [pool disconnectWithTag:tag] to set the state to "up" or "down" for each tag. Individual connections within each tag pool will also become enabled and disabled as things happen over time.

the execution methods are [pool query:query tag:tag] and [pool cancelForTag:tag] - these will send in a round-robin mechanism by picking the next best connection to use.

PGSelect PGUnion PGUpdate PGDelete PGPredicate

PGTableSource (schema,tablename) PGJoinSource PGTableSource/Alias,PGTableSource/Alias,PGPredicate,options PGUnion PGSelect,,.... PGUpdate PGTableSource,NSDictionary,PGPredicate PGDelete PGTableSource,PGPredicate

PGSelect* select = [PGSelect new];

[select setTableSource:@"table" schema:@"public" alias:@"T"]; [select setSourceCrossJoin:@"table" with:@"table"]; [select setSourceJoin:[PGTableSource table:@"table1"] with:[PGTableSource table:@"table2"] on:(PGPredicate* ) options:(int)options];

[select setColumnsAndAliases:@{ @"A": @"column1", @"B": @"column2", @"C": [PGPredicate numberWithInteger:1], @"D": [PGSelect xxxx] } order:@[ @"A",@"C",@"B" ]];

// methods to set output columns -(void)setColumns:(NSArray* )columns; -(void)setColumnsAndAliases:(NSDictionary* )columns order:(NSArray* )aliases;

+(PGPredicate* )predicateWithInteger: +(PGPredicate* )predicateWithBool: +(PGPredicate* )predicateWithString: +(PGPredicate* )predicateWithIdentifier: table: +(PGPredicate* )predicateWithFunction:(NSString* )function,...;

// DELETE FROM table WHERE ((TRUE AND TRUE) OR FALSE); PGDelete* delete = [PGDelete deleteFromTable:@"table"]; [delete where:[PGPredicate predicateWithBool:YES]]; [delete andWhere:[PGPredicate predicateWithBool:YES]]; [delete orWhere:[PGPredicate predicateWithBool:NO]];

a higher-level query mechanism for returning information which can be charted in some way:

PGQueryData = [PGQueryData dataWithSource:(PGQuerySource* )source dimensions:(NSArray* )dimensions metrics:(NSArray* )metrics filters:(NSArray* )filters];

PGResultData = [connection execute:query];

// output SVG graph [result svgWithGraphType:PGResultDataLineGraph];

Performing simple queries

To perform a simple query, it's possible to use an execute method. For example,

#import <PGClientKit/PGClientKit.h>

int main(int argc, char** argv) {
  @autoreleasepool {
    PGConnection* db = [[PGConnection alloc] init];
    NSURL dbi = [NSURL URLWithString:@"pgsql://user@localhost/database/"];
    if([db connectWithURL:dbi error:&error]==NO) {
      NSLog(@"Error: %@",error);
      return -1;
    }
    // assume connected here, perform actions
    PGResult* result = [db execute:@"SELECT * FROM users" error:nil];
    if([result dataReturned]) {
        NSLog(@"result = %@",result);
    }
  }
  return 0;
}

Where errors occur, several methods return descriptive errors as NSError objects and return nil or NO as a return value. If you prefer to use exceptions, you can implement the connectionError: method in a delegate and throw an exception there. See below on implementing a connection delegate.

A PGResult object is always returned when an SQL command has been successfully executed, but it may not always contain returned data. Where there is data, the property dataReturned will be YES. You can retrieve rows of data with the fetchRowAsArray method:

  PGResult* result = ...;
  NSArray* row;
  while(row=[result fetchRowAsArray]) {
    NSLog(@"ROW %d=%@",[row rowNumber],[row componentsSeparatedByString:@",");
  }

You can bind parameters when executing SQL statements. For example,

    PGConnection* db = ...;
    NSString* username = ...;
    PGResult* result = [db execute:@"SELECT * FROM users WHERE username=$1" values:username,nil error:nil];

TBD

##The PGConnection Class

###Connecting and disconnecting

To connect to a remote PostgreSQL database, you'll need to use a URL (ideally). The scheme for the URL is 'pqsql' although you can obtain the scheme as well using the method [FLXPostgresConnection scheme]. To connect to a database on the local computer using socket-based communication, use 'localhost'. In this instance, the 'port' is still important as it determines the name of the socket. Ie,

   pgsql://localhost:9000/postgres

Specifies a connection using socket-based communication, using port 9000 to database 'postgres' and:

   pgsql://postgres@server.local/test

Specifies a connection to a remote server 'server.local' with username 'postgres' to a database called 'test'. To create a connection object and then connect to the remote database:

  NSURL* theURL = [NSURL URLWithString:@"pgsql://postgres@server.local/test"];
  FLXPostgresConnection* theClient = [FLXPostgresConnection connectionWithURL:theURL];

  [theClient connectWithPassword:@"..."];

Passwords are not used when provided through the URL. They are only used as an argument to the connectWithPassword: method. The connect and connectWithPassword methods can throw exceptions if the connection to the remote database failed.

In Cocoa applications, it might be a good idea to connect to remote databases using a background thread, because of the time it can take to initiate the connection across the network. You can also set the timeout value (in seconds) using the following method:

   // five second timeout
   [theClient setTimeout:5];

It's possible to retrieve information about the current connection using the following properties:

  NSUInteger thePort = [theClient port];
  NSString* theHost = [theClient host];
  NSString* theUser = [theClient user];
  NSString* theDatabase = [theClient database];  
  NSUInteger theTimeout= [theClient timeout];

  BOOL isConnected = [theClient connected];

###Executing SQL commands

You can execute simple SQL commands using the execute method and transform NSObject objects into quoted strings using the quote method:

-(FLXPostgresResult* )execute:(NSString* )theQuery;
-(FLXPostgresResult* )executeWithFormat:(NSString* )theFormat,...;

You can use the quote method for each passed object:

   NSString* theName = ...;
   @try {
     [server execute:@"CREATE TABLE t (name VACHAR(80)"];
     [server executeWithFormat:@"INSERT INTO t VALUES (%@)",[server quote:@"Lisa"]];
   } @catch(NSException* theException) {
     // handle error
   }

You can also use pre-prepared statements, where you will be repeatedly calling the same statement:

   FLXPostgresStatement* theStatement = [server prepare:@"..."];

   [server executePrepared:theStatement];
   [server executePrepared:theStatement];
   [server executePrepared:theStatement];

Of course, the most effective way to use prepared statements is to use bindings. This allows you to prepare a statement once for execution and then bind values to the statement:

   FLXPostgresStatement* theStatement = [server prepare:@"INSERT INTO t VALUES ($1)"];

   [server executePrepared:theStatement value:@"Bob"];
   [server executePrepared:theStatement value:@"Jill"];
   [server executePrepared:theStatement value:@"Fred"];

Implementing a PGConnection delegate

The PGResult class

When executing statements, an object of class FLXPostgresResult is returned, which will usually contain rows of data. When performing updates or deletes however, no data will be returned. A result object is still returned, but isDataReturned method will return false:

  FLXPostgresResult* theResult = [server execute:@"UPDATE t SET name='Harry'"];
  if([theResult isDataReturned]==NO) {
    // no data returned
  }

The method affectedRows indicates number of rows returned from the server. In the case where no data is returned, this method will return the number of rows inserted, updated or deleted.

  FLXPostgresResult* theResult = [server execute:@"DELETE FROM t WHERE name='Harry'"];
  NSUInteger affectedRows = [server affectedRows];
  if([theResult isDataReturned]==NO) {
    // no data returned, but affectedRows were changed
  } else {
    // affectedRows number of rows were returned
  }

When data is returned, the number of columns and further information regarding the columns is provided:

  FLXPostgresResult* theResult = [server execute:@"SELECT * FROM t"];  
  NSUInteger numberOfColumns = [theResult numberOfColumns];
  NSArray* theColumns = [theResult columns];
  for(NSUInteger i = 0; i < numberOfColumns; i++) {
    NSString* theName = [theColumns objectAtIndex:i];
    FLXPostgresType theType = [theResult typeForColumn:i];
    // do something here....
  }

The 'type' is the native postgresql type, which can be used to interpret the data returned in each row if necessary. To iterate through the rows of data, use the fetchRowAsArray method:

  FLXPostgresResult* theResult = [server execute:@"SELECT * FROM t"];  
  NSArray* theRow = nil;
  while(theRow = [theResult fetchRowArray]) {
    // do something with the row here    
  }

It's also possible to wind the cursor to any particular row using the method dataSeek:

  FLXPostgresResult* theResult = [server execute:@"SELECT * FROM t"];  
  NSArray* theRow = nil;
  // skip first row
  [theResult dataSeek:1];
  // read data
  while(theRow = [theResult fetchRowArray]) {
    // do something with the row here    
  }

Data types

Conversion is performed between postgresql native types and NSObject-based types when a row is fetched from the server. The supported types are:

postgresql type NSObject class Notes
bytea NSData
char NSString
name NSString
boolean NSNumber bool
int8 NSNumber long long
int2 NSNumber short
int4 NSNumber integer
text NSString
oid NSNumber unsigned integer
float4 NSNumber float
float8 NSNumber double float
unknown NSString
varchar NSString

Unsupported types are converted into NSData objects. Any value which is NULL is converted into an [NSNull null] object. Arrays must be currently be one-dimensional, otherwise an exception is raised.

###Binding values

When binding values for sending to postgresql, the following types are sent:

NSObject class postgresql type
NSNumber (bool) bool
NSNumber (int, unsigned int, long or unsigned long) int4
NSNumber (short or unsigned short) int2
NSNumber (long long or unsigned long long) int8
NSNumber (float) float4
NSNumber (double) float8
NSString varchar
NSData bytea

An object with value [NSNull null] is converted into NULL. Arrays must be one dimensional, and contain only elements of one class, or [NSNull null] values.

PGResultTableView

This is an NSViewController which allows you to generate a PGResult into a table view:

  // create a view, with a data source
  PGResultTableView* view = [PGResultTableView viewWithDataSource:result];
  [view setDelegate:self];

Improvements

  • Large object interface
  • COPY commands
Clone this wiki locally