#pgsqlkit

Getting started with the PGSQLKit and Swift is a remarkably painless process. Wether you are a veteran of many database toolkits and languages, or brand new to developing database applications, PGSQLKit is intended to make getting to your data relatively easy. Keep in mind, that getting to and from a database is only part of the puzzle though. Part of what makes using SQL databases so interesting is that in order to be truly effective, a developer needs a grasp of both the language they are working in, and the dialect of SQL that their server implements. In the scope of this document, we are looking at a fairly small subset of information, designed to showcase the very basics needed to build a project.

PostgreSQL Server

Before you can use the database from Swift, you need to make sure you have a running database that you have access too. Hopefully, if you are here looking for getting to the data, you already have a database. If you do not, you can download and install a PostgreSQL Server for OS X from PostgreSQL for Mac or one of the distributions from EnterpriseDB that can be found at the official PostgreSQL site.

Once you have the server installed, you will need to create a user that can connect. For all of our examples here, that user will be pgexamples with a password of examples. The user will need rights to create a database.

PGSQLKit

With a server in place, you need the tools to get connected. For this purposes, we are going to use PGSQLKit from Druware Software Designs. It is an open source ( BSD Licensed ) framework that is free for pretty much any usage. It is available from either the PostgreSQL for Mac site above or direct from Druware. It is worth noting, that there is a companion project that is PGSQLTouch that is the same source and framework but built for iOS 8 or newer.

Once downloaded, place the .framework into either the /Library/Frameworks folder, or ~/Library/Frameworks. We suggest ~/Library/Frameworks, but this location means that any program written using the framework will only run as the current user. This is great during development. Once a product is ready to deploy, the framework can be embedded into the application bundle.

Hello Database

Hello World has become such a standard starting point, that is almost a cliche in the programming world, but for brevity sake, we are going to do it one more time. This is a little more complex than a typical 'Hello World' in that in order for it to work, we have to connect to the database first. So, let is get the basics down. First things first, a project is needed. In Xcode, create a new Mac OS X Command Line project. We are calling it HelloDatabase, and make sure you select Swift as the language.

The program starts off as the classic Hello World in Swift. For our needs, just a little more code is required. In order to do anything with the database, we must first establish a connection to the database. That connection will allow for running a command against the database, and in this instance return a result.

Create a Connection

In the world of PGSQLKit, the PGSQLConnection and it's generic form the GenDBConnection, are the root of all access. It marshalls the queries and commands to the database and returns the results in a consumable fashion. The code for our connection is pretty simple.

import Foundation import PGSQLKit var connectionString: String connectionString = "host=" + "localhost" connectionString += " port=" + "5432" connectionString += " dbname=" + "postgres" connectionString += " user=" + "pgexample" connectionString += " password=" + "example" var conn: PGSQLConnection conn = PGSQLConnection(); conn.ConnectionString = connectionString if (conn.connect()) { ... conn.close(); }

Before we go on to do something with the connection, it is worth looking at the parts that are used to set up the connection. Being Swift, the first item to note is telling the compiler to include the PGSQLKit. If you just typed this in, you are probably getting an error that PGSQLKit cannot be found. That is because you have not told the target to link to the PGSQLKit.framework. With the framework linked, Xcode can now process the rest of the logic.

The next groups of lines are just building up the connection string. This can also be done by setting the individual properties on the Connection object, but for this simple example, either method will do. If you look at the connection string, you see the definition of the connection, where, to what, and as whom. What do we do with that information? That is when it is time to establish the connection. With the command "var conn: PGSQLConnection" we are saying hey, compiler I would like a variable named conn that is of type PGSQLConnection. Immediately following we ask the compiler to please create an instance of a PGSQLConnection and assign it to our previously defined conn variable. Next up, we tell the give the connection our connectionstring to describe how to connect, and finally, we ask the connection to connect. If it succeeds, we can do something with it.

That little bit of work handles the connection. Now we want to do something with the connection. For this example, let us work with something simple. What we want is to run a query that returns Hello Database! in field named hello. The SQL for this is simple:

select 'Hello Database!' as hello;

Now, how do we send that to PostgreSQL from Swift? Why via the PGSQLConnection we just created (You knew that already didn't you?). Replacing the ... in our above sample, let us do something.

var rs = conn.open("select 'Hello Detabase!' as hello") while (!rs!.IsEOF) { var hello = rs!.fieldByName("hello").asString() println("Database says: \(hello)") rs!.moveNext() } rs!.close()

Again, we define a variable, this time rs, and we set it the result of our request from the conn.open() method. Rephrasing that into simpler terms, we are asking the compiler to tell the variable we created named conn to open a result for the SQL command "select 'Hello Detabase!' as hello" placing the result into the variable named rs. That is alot of work in a single command. Next we setup a little loop to fetch results for as many results as there may be. In this instance, we only have one result, so it will loop one time. Inside that loop, we read the field from the result. Asking for the field by the name of hello, telling it to return it as a string, and putting into the variable hello. Print what the database says, and finally, move to the next record in the results. The very last thing we do is a little bit of clean up, by closing up our result to free up any resources.

At this point, you have a complete little program that you can build and run that wil connect to the database, run a query and display the results.

Believe it or not, this tiny example provides you with the foundation that a full data access program can be built upon. This code would work nearly unchanged in an iOS 8 application using PGSQLTouch instead, and could be adpated with just a few minor changes to use and ODBC data source instead of PostgreSQL using ODBCKit.

I often get questions about PGSLKit ( and ODBCKit ) from people who inexplicably cannot receive emails from me. This week, I got one of these from Bruno.

Q: Can you tell us how transform a PGSQLRecordset to NSArray or NSMutableArray?

A: Yes, I Can.

The quickest way is to do an iterative loop, adding NSDictionary records to an NSMutableArray via the dictionaryFromRecord methodology. Internally, this is what the relatively new PGSLDataObjectList in SVN does. The following code is pulled directly from that code. If you look at the code below, you will see the loop. It does however use the PGSQLDataObject, but you could simply replace that with an NSMutableDicationary to get what you want.

/* initWithConnection * description * implements an init that takes an active PGSQLConnection for use in data * operations withing the object list. If this generic base operation is * used generic base operation is used. It makes several assumptions: * 1. the table in question has a primary key * 2. the primary key is serial * 3. the table supports * arguments * (PGSQLConnection *) as the connection to be used for the current object. * note that this connection cannot have multiple result sets open at a * time. * returns * (id) as the current object reference initialized as the referenced * data record * history * who date change * --- -------- ----------------------------------------------------------- ******************************************************************************/ - (id)initWithConnection:(PGSQLConnection *)conn forTable:(NSString *)tableName withPrimaryKey:(NSString *)keyName { self = [super init]; if (self != nil) { connection = [conn retain]; table = [[tableName copy] retain]; primaryKey = [[keyName copy] retain]; items = [[NSMutableArray alloc] init]; NSString *cmd = [NSString stringWithFormat:@"select * from %@", table]; PGSQLRecordset *rs = (PGSQLRecordset *)[connection open:cmd]; while (![rs isEOF]) { // do the load here PGSQLDataObject *dataObject = [[PGSQLDataObject alloc] initWithConnection:connection forTable:table withPrimaryKey:keyName forRecord:rs]; [items addObject:dataObject]; [rs moveNext]; [dataObject release]; } [rs close]; } return self; }

So one of our major pushes has been to reduce the burden of multiple codebases. With that in mind, we are pulling both the Query Tool programs (PostgreSQL and ODBC) into a single tool that can go either way, both ways and in the future will also include out newly started TDSKit work.