Re: Core Data with ODBC databases?
Re: Core Data with ODBC databases?
- Subject: Re: Core Data with ODBC databases?
- From: Andrew Satori <email@hidden>
- Date: Wed, 16 Oct 2013 12:11:57 -0400
Well... This is why I LOVE Objective-C and Cocoa for this work. What follows is a little complex, and honestly still in very raw form as I've had limited time to really finish all the work for something that is a line of business bit of work, but wasn't really built to be made generally available.
This is NOT built on ODBC, but is direct to PostgreSQL, but it could be reworked if needed.
The project in question needed to deal with a very modern architecture. Clients run on Windows (RT), Windows Phone, iOS, OS X, Web and though I did create an Android version, it is not in deployment. They communicate with the backend via HTTPS using a RESTful design with XML. The backend is written in ObjectiveC and runs as a CGI app within either Apache or in our case, LigHTTPd and uses the C libraries from FastCGI as a foundation. That code accesses the PostgreSQL database in a fairly abstract and generic manner.
I admit that since most of the code is boilerplate work, I use a code generator for many things, but that's more because I'm lazy than anything else. If I was to take these ideas to a more general use solution, I would certainly move to JSON, as it is far easier to consume at the client level these days. It was not when I did most of this work almost 18 months ago.
So, about the work...
At the heart of the matter is PGSQLKit, which provides an ADO like interface for a PostgreSQL database. Connecitions, Recordsets and Fields oh my. I wrote most of it, so it was what I was comfortable with. The problem was, I didn't really want to deal with creating SQL in code all the time, so I added a class to the PGSQLKit framework simply called PGSQLDataObject, it's purpose? to abstract a record into an NSObject derived object that I could easily inherit and ignore the boilerplate grunt work. It is simple, but effective ( the code is in the PGSLKit SVN on SourceForge ) and there is room for improvement. There is an associates List object for the entire table/recordset.
The interface is fairly simple:
// PGSQLDataObject.h
// PGSQLKit
//
// Created by Dru Satori on 1/30/12.
// Copyright (c) 2012 Druware Software Designs. All rights reserved.
/*!
@header PGSQLDataObject
@abstract A simple data object class that encapsulates a single record in
a recordset, and manages all of the CRUD methods needed to
create, read, update, delete, serialize to xml and reconstitute
from xml.
@discussion The PGSQLDataObject class was born mostly out of the drudgery
of the boilerplate code that is so many simple data classes. In
this initial pass, it
License
Copyright (c) 2005-2012, Druware Software Designs
All rights reserved.
Redistribution and use in binary forms, with or without
modification, are permitted provided that the following
conditions are met:
1. Redistributions in binary form must reproduce the above
copyright notice, this list of conditions and the following
disclaimer in the documentation and/or other materials
provided with the distribution.
2. Neither the name of the Druware Software Designs nor the
names of its contributors may be used to endorse or promote
products derived from this software without specific prior
written permission.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND
CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES,
INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR
CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE,
EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
#import <Foundation/Foundation.h>
#import "PGSQLConnection.h"
@interface PGSQLDataObject : NSObject
{
NSNumber *refId;
NSString *table;
NSMutableDictionary *properties;
NSString *primaryKey;
BOOL isNew;
BOOL isDirty;
NSString *lastError;
NSMutableArray *omittedFields;
PGSQLConnection *connection;
}
#pragma mark customer initializers
- (id)initWithConnection:(PGSQLConnection *)pgConn;
- (id)initWithConnection:(PGSQLConnection *)pgConn
forId:(NSNumber *)referenceId;
- (id)initWithConnection:(PGSQLConnection *)pgConn
forRecord:(PGSQLRecordset *)rs;
- (id)initWithConnection:(PGSQLConnection *)pgConn
forTable:(NSString *)tableName
withPrimaryKey:(NSString *)keyName;
- (id)initWithConnection:(PGSQLConnection *)pgConn
forTable:(NSString *)tableName
withPrimaryKey:(NSString *)keyName
forRecord:(PGSQLRecordset *)rs;
- (id)initWithConnection:(PGSQLConnection *)pgConn
forTable:(NSString *)tableName
withPrimaryKey:(NSString *)keyName
forId:(NSNumber *)referenceId;
- (id)initWithConnection:(PGSQLConnection *)pgConn
forTable:(NSString *)tableName
withPrimaryKey:(NSString *)primaryKeyName
lookupKey:(NSString *)keyName
lookupValue:(NSString *)keyValue;
#pragma mark utility methods
- (NSString *)stringForBit:(NSString *)value;
- (NSString *)stringForBool:(BOOL)value;
- (NSString *)stringForAbsTime:(NSDate *)value;
- (NSString *)stringForDate:(NSDate *)value;
- (NSString *)stringForTime:(NSDate *)value;
- (NSString *)stringForTimeStamp:(NSDate *)value;
- (NSString *)stringForTimeTZ:(NSDate *)value;
- (NSString *)stringForTimeStampTZ:(NSDate *)value;
- (NSString *)stringForData:(NSData *)value;
- (NSString *)stringForLongNumber:(NSNumber *)value;
- (NSString *)stringForRealNumber:(NSNumber *)value;
- (NSString *)stringForMoney:(NSNumber *)value;
- (NSString *)stringForString:(NSString *)value;
- (NSString *)sqlEncodeString:(NSString *)value;
#pragma mark mata management methods (RDBMS & Xml)
- (BOOL)save;
- (BOOL)remove;
- (NSXMLElement *)xmlForObject;
- (BOOL)loadFromXml:(NSXMLElement *)xmlElement;
- (void)setLastError:(NSString *)value;
- (BOOL)addOmittedField:(NSString *)fieldName;
#pragma mark standard data connection properties
- (void)setValue:(id)value forProperty:(NSString *)property;
- (id)valueForProperty:(NSString *)property;
- (long)sizeOfProperty:(NSString *)property;
- (BOOL)propertyIsNull:(NSString *)property;
@property (assign,readonly) BOOL isNew;
@property (assign,readonly) BOOL isDirty;
@property (assign,readonly, nonatomic) NSString *lastError;
@property (assign,readonly) PGSQLConnection *connection;
@property (assign,readonly) NSString *table;
@property (assign,readonly) NSString *primaryKey;
@property (assign,readonly) NSNumber *refId;
@end
From there you can then implement a record specific class from there to extend do validation and provide additional behaviors like child/related objects:
#import <PGSQLKit/PGSQLKit.h>
#import <PGSQLKit/PGSQLDataObject.h>
#import "TWJContact.h"
@interface TWJUser : PGSQLDataObject
{
TWJContact *contact;
}
#pragma mark customer initializers
- (id)initWithConnection:(PGSQLConnection *)pgConn;
- (id)initWithConnection:(PGSQLConnection *)pgConn
forId:(NSNumber *)referenceId;
- (id)initWithConnection:(PGSQLConnection *)pgConn
forRecord:(PGSQLRecordset *)rs;
#pragma mark persistance methods (rdbms, xml)
- (BOOL)save;
- (NSXMLElement *)xmlForObject;
- (BOOL)loadFromXml:(NSXMLElement *)xmlElement;
#pragma mark custom properties
@property (copy,readonly) NSNumber *userId;
@property (copy,nonatomic) NSString *login;
@property (copy,nonatomic) NSString *password;
@property (copy,nonatomic) NSNumber *status;
@property (copy,nonatomic) NSNumber *contactId;
@property (copy, readonly) TWJContact *contact;
#pragma mark custom accessors / property overrides
- (NSNumber *)userId;
- (NSString *)login;
- (void)setLogin:(NSString *)value;
- (NSString *)password;
- (void)setPassword:(NSString *)value;
- (NSNumber *)status;
- (void)setStatus:(NSNumber *)value;
- (NSNumber *)contactId;
- (void)setContactId:(NSNumber *)value;
- (TWJContact *)contact;
@end
Which you ultimately serve up to the client via CGI using a simple RESTful server object:
#import <Foundation/Foundation.h>
#import <PGSQLKit/PGSQLKit.h>
#import <DruwareWebKit/DruwareWebKit.h>
#import <TWJServerCore/TWJServerCore.h>
@interface DWSession : NSObject {
OWWebRequest *request;
PGSQLConnection *connection;
NSNumber *errorCode;
NSString *errorCondition;
TWJSession *session;
}
#pragma mark custom initializers
- (id)initWithRequest:(OWWebRequest *)thisRequest;
#pragma mark custom methods
- (void)restfulGet;
- (void)restfulPut;
- (void)restfulPost;
- (void)restfulDelete;
#pragma mark custom properties
@property (copy,readonly) OWWebRequest *request;
-(NSString *) exitStatus;
#pragma mark custom accessors / property overrides
#pragma mark standard web request properties
@end
But we have really migrated away from the concepts of an ORM at this point, and are looking at something far more valuable to the community. The entire framework could be consolidated today, and integrated quite well into the current OS X Server platform.
At the root of things, what I think we are really discussing is a model not about CoreData, but one about CoreWebServices. Could we build a framework that let's you model your data in a modeler, and it publishes the model to a RESTful web service via JSON? Absolutely, but ODBC probably isn't the right vehicle. In this instance, you could in fact even do it in CoreData (I wouldn't but you certainly could).
I know I've wandered a bit far afield here, but this is a subject near and dear :-)
Dru
On Oct 16, 2013, at 11:24 AM, Flavio Donadio <email@hidden> wrote:
> Dru,
>
>
>> I'd like to take this a step further. CoreData is a really nice tool, but CoreData really isn't the tool for using a multi-user RDMS since it skips over some of the frequently forgotten concepts like locking and data concurrency.
>
> So, that's why we need another tier: an application server between the client and database server, to manage locking and concurrency (among other things).
>
> This is really annoying, as we have to deal with things like web services (or, even worse, custom protocols), data encoding into structures meant only for client/server communication (stuff like XML, JSON or whatever).
>
> It always requires us to write a lot of code to decode/encode from/to these intermediate data formats, even when using one of the many parsers or a framework like RESTKit. It's hard to use bindings, which CoreData can do easily -- and which, I believe, is the reason most developers love CoreData.
>
> In cases where a partially connected application is desired, even if you want to use CoreData in the client, it's still very hard to do. You have to have two identical models (one for the server, one for the client -- often in different formats) and keep them in sync as you add/change features to/in the app. You also have to generate NSManagedObjects from the data you receive from the app server and save them back when the objects modified. AFIncrementalStore helps, but it could be even easier.
>
>> Most of the time when people talk about CoreData and ODBC, they are really looking for one of two things, an easy way to use bindings for getting data to and from the view, or the ability to use the XCModel tools to manage and create databases.
>
> I look for both. ;)
>
> Still waiting for the day I won't have to re-invent the wheel everytime I develop a new remote-data-driven app.
>
>
> Best regards,
> Flavio Donadio
_______________________________________________
Cocoa-dev mailing list (email@hidden)
Please do not post admin requests or moderator comments to the list.
Contact the moderators at cocoa-dev-admins(at)lists.apple.com
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden