Business Machine

Technology, business
and innovation.

And, not least, about
the Mac.

Weblog Archive Cutedge

by: Bernard Teo

Creative Commons License

Copyright © 2003-2012
Bernard Teo
Some Rights Reserved.

Mon 16 Oct 2006

PostgreSQL and Luca

Category : Technology/Luca_PostgreSQL.txt

I've built a database connector framework, over the last few days, to allow Luca to store its data into a PostgreSQL database.

Luca already works with SQLite and MySQL. And we did build also a database framework for OpenBase, and went as far as being able to show the Chart of Accounts and Trial Balance on OpenBase, but I realised (it seems) that OpenBase doesn't support the SQL command, Union, which we used quite a bit - particularly in being able to generate a Statement of Accounts or an Ageing Report reasonably quickly. So I've left that aside for the moment to work on PostgreSQL which does support Unions.

I've been able to populate a Postgres database with data needed by Luca, and that was rather satisfying - that our framework's structure works across four database types now.

But I've hit a curious snag.

I've named my database columns using mixed case because "currencyCode" is much easier to read than "currencycode". But PostgreSQL "folds" all column and table names to lower case, by default. So my column is named "currencycode" in Postgres.

When the data is read back into Luca, they're packed into dictionaries so that it's easier to access each field value using Objective-Cs "valueForKey" construct.

But C, on which Objective-C is based, is of course case-sensitive and so 'valueForKey:@"currencyCode"' which I use all over the code does not work now that the key, that is returned from Postgres, is called "currencycode".

There are various ways of solving this, with a variety of trade-offs. So I'm mulling it over.

But, if you're management, how do you assess this situation? Should you allocate resources to explore the options and produce a solution? First question, "why bother?".

Well, PostgreSQL is free, and there are no contraints on its use, whether for commercial or open source development. It's easy to install, or at least the Mac OS X installation went like a breeze (use Marc Liyanage's installer). And PostgreSQL has been known to break new grounds that later became standard features on other databases. So this is a great tool to have in your toolset.

There are some questions whether MySQL's licensing will allow its C API to be embedded inside a commercial application, with no restrictions. Sometimes, I read, yes; sometimes I'm not too sure.

But there are no such ambiguities with SQLite and, I think, also with PostgreSQL. And so, with SQLite forming the core of a light-weight embedded database that is shrinkwrapped into Luca, and PostgreSQL for people who need multi-user, concurrent, client-server access with transaction locking, that should cover all the bases.

Posted at 3:43AM UTC | permalink

Put your Mac to Work Now how would you do something like that?

Weblogs. Download and start a weblog of your own.

A Mac Business Toolbox
A survey of the possibilities

A Business Scenario
How we could use Macs in businesses

VPN Enabler for Mavericks

MailServe for Mavericks

DNS Enabler for Mavericks

DNS Agent for Mavericks

WebMon for Mavericks

Luca for Mavericks

Liya for Mountain Lion & Mavericks

Postfix Enabler for Tiger and Panther

Sendmail Enabler for Jaguar

Services running on this server, a Mac Mini running Mac OS X 10.9.2 Mavericks:

  • Apache 2 Web Server
  • Postfix Mail Server
  • Dovecot IMAP Server
  • Fetchmail
  • SpamBayes Spam Filter
  • Procmail
  • BIND DNS Server
  • DNS Agent
  • WebDAV Server
  • VPN Server
  • PHP-based weblog
  • MySQL database
  • PostgreSQL database

all set up using MailServe, WebMon, DNS Enabler, DNS Agent, VPN Enabler, Liya and our SQL installers, all on Mavericks.