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

ImplementationNotes

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

PGClientKit

Notify

Non-blocking query processing

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. All the connections will have the same attributes (port, hostname, database) as the main connection. When you perform a connection, disconnect or reset, the pool will be destroyed.

To execute a command using a connection in the pool, you need to grab a pooled connection. This will return an integer (0 being the main connection, 1 upwards being the pooled connections):

NSInteger thePooledConnection = [theConnection pooledConnection];
FLXPostgresResult* theResult = [theConnection execute:statement withConnection:thePooledConnection];
...etc...

This can then be mapped onto particular threads, so that [NSThread mainThread] is linked to connection 0, and then further threads can be registered / deregistered against connection numbers. What happens if a particular thread does not use it's connection in a while? I assume the connection gets dropped at the server end.

   NSInteger thePooledConnection = [theResult registerThread];
   ...etc...
    [theResult unregisterThread];

PostgresDataKit

The data kit will be an easy way to create persistent objects. There will be an FLXPostgresDataObject class which implements the storable object. You would inherit this class in your own class definition and define a few methods:

@interface Person : FLXPostgresDataObject {

}

+(NSString* )tableName {
  return @"people";
}

@end

In this example, all the columns of the table are mapped onto the"people" table. Then there is a central store (which is probably a shared object) which is used for creating new objects, etc:

  FLXPostgresDataCache* theCache = [FLXPostgresDataCache sharedCache];
  [theCache setDatabase:@"my_database"];
  [theCache connect];

  Person* newPerson = [theCache newInstanceOfClass:[Person class]];
  
  [newPerson setValue:@"David" forKey:@"firstname"];
  [newPerson setValue:@"Thorpe" forKey:@"surname"];

  [newPerson commit];

  NSNumber* theID = [newPerson primaryValue];

  NSLog(@"Saved new person with ID=%@",theID);

  [theCache disconnect];

You can fetch all the objects into an array, or a single object:

   NSNumber* theID = [NSNumber numberWithInteger:100];
   Person* thePerson = [theCache fetchObjectWithPrimaryValue:theID];

   FLXPostgresDataArray* people = [theCache fetchArrayForClass:[Person class]];

   for(Person* thePerson in people) {
     // do stuff
   }

You can commit and rollback objects to the database, or delete them:

   [thePerson commit]; // will save a Person object if modified or if new
   [thePerson rollback]; // will revert a Person object to previous state
   [thePerson delete]; // will delete a Person object (convert instance back to 'new' object)

   // for arrays:
   [people commit]; // will save all people objects which have been modified
   [people rollback]; // will return all people objects to previous state
   [people delete]; // will delete all objects in array (convert all instances back to 'new')

You can specify which table columns should be retrieved and the primary key used for a person object:

@interface Person : FLXPostgresDataObject {

}

+(NSString* )tableName {
  return @"people";
}

+(NSString* )primaryKey {
  return @"id";
}

+(NSArray* )tableColumns {
  return [NSArray array withObjects:@"firstname",@"surname",nil];
}


@end
  • Object-to-object relationships are supported through foreign-keys....let's work out how this works.
  • One-to-many?
  • Table joins - updatable?
  • Views - updatable?

Other

Will have some sort of data model system for creating & upgrading data models.

FLXPostgresDataModel* model = [FLXPostgresDataModel modelFromFile:(NSString* )theFile]

if([[self connection] schema:schemaName containsModelWithKey:[model key]]==NO) {
  [[self connection] schema:schemaName createModel:model toVersion:version];
} else {
  [[self connection] schema:schemaName updateModel:model toVersion:version];
}

The model files might be some sort of XML, which includes tables, indexes, views and triggers.

Each model will have a distinguishing 'key' and incremental version number.

When updating, we can simply replace views & triggers but might need to do some sort of altering for tables and indexes.

You can then map each model table or view onto an Objective-C class, somehow. There are methods to fetch objects and arrays from the database, or to save them back again.

Each object and array has an incrementing 'serial' which determines when an object has changed. In this way it's possible to determine when objects have changed in the database, which allows objects and arrays to be reloaded.

(These are currently notes on how this framework is going to be implemented)

This is a framework for implementation of persistent objects in Objective-C. The idea is that you rapidly prototype your data model in Objective-C, and then use this framework for loading and saving the data within the objects.

Storm (https://storm.canonical.com/) is probably the best example of how such a framework might work.

#import <PGDataKit/PGDataKit.h>

PGStore -> Inherits from PGConnection

PGStore* db = [PGStore connectWithURL:....];
// register objects we're going to use with PGStore
[db register:[Person class],nil];

@interface Person <PGObject>
@property NSString* name;
@property int telephoneNumber;
@end

@implementation Person
@synthesize name;
@synthesize telephoneNumber;

+(PGStorePK* )_key {
  return [PGStoreKey keyWithTable:@"person" column:@"id" schema:@"addressbook"];
}

@end

Person* me = [[Person alloc] init];
me.name = @"David Thorpe";
me.telephoneNumber = 5551234;

[db add:me];
[db flush];

PGArray* objs = [db fetch:[Person class] where:[PGPredicate predicate:@"name='David Thorpe'"]];
for(Person* p in objs) {
  NSLog(@"Person = %@",p);
}

PGConnectionPool* pool = [PGConnectionPool sharedObject];

[pool setDelegate:self]; [pool setMode:PGConnectionPoolSimple]; [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.

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://[email protected]/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://[email protected]/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.

###Extending PGResult

##Helper classes for UI

##Limitations

  • Support for multi-threading through connection pooling
  • Support for SSL connections and MD5 authentication
  • Support for listen and notify?
  • Asyncronous operation
  • Large object interface?
  • Unit tests
  • Examples
Clone this wiki locally