ActiveRecord for Oracle won't update records

I needed a script to fix up some date data in an Oracle database that didn't lend itself to using a pl/sql script. Naturally I turned to Ruby and ActiveRecord to sort out the issue. I drafted the script against a local MySQL DB and it worked flawlessly, but when run against the Oracle instance it wouldn't save any data changes.

Here's what happened, and how to fix it...

So turns out this has happened before to other - see this post on Ruby-Forum.com for details. The OP didn't get any replies so I'm assuming that he figured it out on his own, unluckily for me he didn't share the fix so I had to do some leg work.

For starters I'm running Ruby v1.8.7, activerecord v2.3.8 and activerecord-oracle_enhanced-adapter v1.3.0

My schema is legacy with a primary key that isn't "id". Easy to sort out, I coded up something like this:

class Receipt < ActiveRecord::Base
  set_primary_key "rt_receipt_id"
end

So onto the issue. This worked fine with MySQL but not Oracle so my first stop is to check out the SQL that's being created:

UPDATE "RECEIPT"
SET "RT_RECEIVED" = TO_DATE('2008-01-03 23:58:19','YYYY-MM-DD HH24:MI:SS')
WHERE "RT_RECEIPT_ID" = NULL

NULL?

On any update, AR tries to sql encode the value of the AR object's "id" value as the primary key. Calling the id accessor on my Receipt objects was returning nil, and AR was translating that to a sql NULL. There's a simple fix, alias id to our new primary key accessor:

class Receipt < ActiveRecord::Base
  set_primary_key "rt_receipt_id"
  alias_attribute :id, :rt_receipt_id
end

Recursion in Ruby

I recently took part in the RPCFN. Being that this month's challenge was a Shortest Path type challenge and I've done enough discrete maths papers to know that the best graph traversal Algorithm for this is Dijkstra's least cost path one I went away and implemented it in a couple of hours. Many others did exactly the same thing, often much more elegantly that me. What struck me was that the markers were quite obviously looking for people to imagine up their own method of finding the shortest path, and had a massive bias towards recursive algorithms.

Now the winning submission is a very nice piece of code, it's concise, easy to read, simple, well tested, and it clearly lays out it's goals, to find a recursive solution to the problem with disregard of performance. It performed ok, I did wonder however how the recursion would impact the performance on larger problem sets.

So I took Todd's code and refactored mine to work with his data structures. I also changed up my code to match his module's interface and packaged both implementations into their own classes so that there wouldn't be clashes while trying to test them. (modules are for adding abilities to classes, not for providing whole feature sets, if you see yourself including a module into main, you're doing it wrong...)

And here it is.

I ran benchmarks on a low end computer (Pentium 2.8) against 3 graphs, one with 2 nodes and 2 edges, the RPCFN example with 7 nodes and 10 edges, and a larger, more complicated one of 7 nodes and 21 edges, all of this is in the code linked above. Here's the results over 10000 iterations:

Simple Graph:
Iterative Approach took 1.54700second(s).
Recursive Approach took 0.95300second(s).
RPCFN Graph:
Iterative Approach took 6.37500second(s).
Recursive Approach took 20.62500second(s).
Large Graph:
Iterative Approach took 10.70300second(s).
Recursive Approach took 472.96200second(s).

The numbers speak for themselves, this approach doesn't scale at all well. In fact at first I thought I'd broken some of Todd's code and created an infinite loop of some sort. It's more than likely that the recursive function could be tweaked to get better performance, but short version is that if I were ever to need to implement graph traversal in Ruby for any production system, recursion would be the last approach I'd look at.

Final word
Todd's decision to use a recursive solution in this case was totally justified, readability and simplicity were massive factors in this event. That said I believe that outside of the smaller, more contrived problem scopes, recursion brings a lot of headaches and scaling issues than the iterative approaches.

Rails with a legacy Oracle DB

It's a bit annoying...
It seems like every ROR tutorial assumes two things, firstly that you're writing your web application on Mac OSX with Textmate, and second that you're starting with a fresh database schema on MySQL. For a large percentage of people this is correct, but for the other half it's more than frustrating.

I am currently investigating using rails to replace a rather old (think 6 years+) and difficult (JSP, the kind that looks like PHP gone bad) reporting site. The site shows various metrics to our business partners by analyzing SMS traffic sent through our gateway. I need to replace it with something simpler and more maintainable, Rails seemed to fit the bill, however there are some hurdles.

• Firstly, Ruby likes UNIX. I program on a windows box and deploy to UNIX environments, there's nothing I can do to change that. Setting up a Rails environment on windows without resorting to the horribly outdated installers is a feat in itself, I'll cover that in a later post.
  


• Secondly, my company likes Oracle. For the moment. It wouldn't be my first choice on a new project, but our software (with high availability requirements, somewhere in the 99.9% up time region) has been running smoothly on Oracle databases since I was in first year university. There's nothing like a good track record to make loyal followers of upper management.
  


• Thirdly the schemas of the data I need to report on are sorely outdated. Everything is capitalized, there's no standard "id" column on any table, and there's a scattering of prefixes on half the columns. This makes for a slow start with ActiveRecord...
  

The Oracle to Rails "Stack"
Slow it may be but start we will. My "Secondly" was pretty easy to fix. Basically we need to plug 3 gaps to get ActiveRecord to talk to an Oracle Database, that can be summarised as: Your pc needs a connection to the Oracle Database Server, Ruby needs to see this connection, and ActiveRecord needs to see Ruby's connection. To make it a bit easier (maybe) here's a diagram, drawn in glorious MS Paint =>

Oracle Client: Since I already do a lot of work on Oracle databases I didn't need a client, but if you don't already have one, go find an oracle client preferable a thin (hah!) one. Google Oracle XE if you want to have a local oracle database, it's Oracle's attempt at an express edition, however at over a gigabyte to install on windows it's not for the netbook developer types...

Ruby-OCI8: go to your command line and get the Ruby-OCI8 gem via: gem install ruby-oci8 on windows you'll get the ruby-oci8-2.0.2-x86-mswin32 version. This gem lets Ruby talk to your Oracle client.

Oracle-Enhanced: This gem is an ActiveRecord adapter. Get it by running gem install activerecord-oracle_enhanced-adapter

These different parts will get you a path from ActiveRecord in Rails all the way to the Oracle database you need to work on, but there's a few things we need to tweak in our application's config to get it all the way, see here for a lot of info that is much better than I can give.

Schema woes...
Most things in rails "Just work", so long as you followed EVERY SINGLE CONVENTION. This is a bit much to ask of the developers that designed our legacy database 6 years ago. Luckily you can override and alias enough values to shoehorn your data into a valid ActiveRecord model, and once that (admittedly long and tedious) task is done you can develop like your database schema was never an issue.

I'm going to cover a few of the more common issues by rebuilding a hypothetical (cough) table called ORGANISATION. ORGANISATION has the following columns: OR_ID, FK_RE_ID, OR_NAME, OR_CODE, FK_PMP_ID. As you can see it's kind of well structured, but definitely not "Rails Safe".

Issue 1: Rails expects database tables to be pluralised.
ActiveRecord offers a method set_table_name that takes a string. We'll add set_table_name "organisation" to our model.

Issue 2: Rails wants a column called "id" for it's primary key.
ActiveRecord offers a method set_primary_key that takes a string. We'll add set_primary_key "OR_ID" to our model.

OK we're half way there. If we were building from a scaffold command our views might work (maybe) but I can think of a few places where they'll break.

Issue 3: By ERB code is breaking, what the hell?
There's a few issues here, first is that if we use the scaffold command we'll probably get what we asked for. If we asked for upper case column names (as you'd expect) then your views will break when they try to extract the info in "organisation.FK_RE_ID". Oracle SQL is very lenient on the casing of it's table and column names, while something in Rails or our adapters hates uppercase. Use the lowercase "organisation.fk_re_id".

Issue 4: By ERB code is still breaking, what the hell?
Shortcuts... The scaffolded views will invariably at some stage try to call a method that takes the organisation and try to guess at it's. In the index.html.erb for example (we're scaffolding here) it will try to do something like: "<%= link_to 'Edit', edit_organisation_path(organisation) %>" since the organisation has no "id" column it will just send everything else. Poof, your code broke.
To fix it you need to alias your foreign key as "id" in your model, add alias_attribute :id, :or_id

Final niggling issue 5: I don't like all the unintuitive column names in my views.
Since we've aliased OR_ID to id, we might as well do the same on all our columns. Just remember to alias to LOWERCASED versions of the column names, otherwise your views will throw errors again. Below is the code for the model I've described above, it works a treat.

class Organisation < ActiveRecord::Base
 set_table_name "organisation"
 set_primary_key "OR_ID" 
 
 alias_attribute :id, :or_id
 alias_attribute :region_id, :fk_re_id
 alias_attribute :org_name, :or_name
 alias_attribute :org_code, :or_code
 alias_attribute :provider, :fk_pmp_id 
end

Simple SOAP calls over SSL with Ruby

I deal with web services a lot in my day to day, some good, some nightmarish. Having scripts and example code to deal with them makes my job a lot easier. My example last week was a piece of python code that implemented CONNECT to allow you to make SSL encrypted requests across a proxy in Python. I had to write this code because the company I work for exposes it's web services to clients via SSL, and being able to offer example code to leverage our services in multiple languages is a good thing. Not being able to offer example code in a pretty mainstream (in web terms) language is a bad thing.

Therefore I wrote a similar script to last week's example that uses Ruby to post an XMP SOAP envelope request to an SSL secured site over a proxy. For enterprise use in apps expecting to make thousands of SOAP calls a day I would recommend users to build a full SOAP app using SOAP4R or some equivalent framework. However as this is often overkill for smaller apps that simply want to make a few calls to a web service, doing the request manually as a raw HTTP POST to send the SOAP envelope to the service is usually enough, and doesn't obscure the details of what SOAP really is, an HTTP POST request with a rigorously defined XML payload.

As an aside, to build the SOAP envelope for this example I heartily recommend SoapUI, it allows you to open a WSDL for a particular service and easily generate the XML for each SOAP action. It's really good, especially if you build SOAP web services in your day to day job. It's so good, in fact, that as you get more and more used to using it you might find it replacing the test harnesses you inevitably build to test the web services you build.

Back on topic, because we are building the XML as a string we need to inject any parameters for the SOAP call after we have built it. Luckily Ruby lets us replace substrings in strings pretty easily with the .sub! method. I use it to template my SOAP requests with {1} and {2} etc... Also to avoid being stung with content length issues make sure you finalize your XML before creating the headers dictionary so that your Content-Lenght ('Content-Length'=> soap_data.length) is correct. If you don't, the next 2 hours will be wasted while you go round in circles with HTTP errors that don't make too much sense.

Anyway: here's the code, you'll notice that it's a lot shorter that the Python code from last week, this is because the standard library gives you native SSL over Proxy support. Note the call 'http_session.use_ssl = true'.

require 'net/https'
require 'open-uri'

# Create the SOAP Envelope
soap_data = '''
This is where the SOAP xml would be drafted. I use {1} to template value fields.
'''
# normally I'd inject parameters into the SOAP Envelope using a call like:
# soap_data.sub!('{1}', "example string")

# Set Headers
headers = {
  'Content-type'=> 'text/xml; charset=utf-8',
  'SOAPAction'=> '""',
  'User-Agent'=> 'The useragent you wish to use, useful if you ever have to debug at the other end...',
  'Host'=>  'www.securedurl.com',
  'Content-Length'=> soap_data.length
}

#create session object
uri = URI.parse("https://www.securedurl.com")
path = '/WebServiceHome/services/'
proxy = Net::HTTP::Proxy("aproxyserver",8080)
http_session = proxy.new(uri.host, uri.port)
http_session.use_ssl = true

#start the http session
http_session.start { |http|
  # create the request
  req = Net::HTTP::Post.new(path)
  req.basic_auth mip_user, mip_password
  headers.each{|key, val| req.add_field(key, val)}
  # Post the request
  resp, data = http.request(req, soap_data)
  puts 'Code = ' + resp.code
  puts 'Message = ' + resp.message
  resp.each { |key, val| puts key + ' = ' + val }
  puts data
}

Python 3.0 SSL over Proxy

With the IT world the way it is now I'm certain the majority of enterprises put their users behind proxy servers, and I'm also certain that a lot of users behind said proxies need to access SSL secured sites programatically. With python touting itself as an enterprise worthy product and the batteries included philosophy of the core language libraries I'm surprised that there is no built in support for accessing SSL secured sites over a proxy. Admittedly there is a patch in the root bug report for this feature, however it's been around for years and it's only just getting to the implementation stage. Hopefully it's going to make it into 3.1.

The python HOW TO documentation points to a cookbook recipe that handles this issue, but it's still only available in 2.x version. So since I had to implement this for a little code snippet here it is, SSL over proxy, the Python 3.0 version.

import urllib, urllib.parse, ssl, http.client, socket
from urllib.request import Request, urlopen
from urllib.error import  URLError, HTTPError

class ProxyHTTPConnection(http.client.HTTPConnection):
    _ports = {'http' : 80, 'https' : 443}
    def request(self, method, url, body=None, headers={}):
        #request is called before connect, so can interpret url and get
        #real host/port to be used to make CONNECT request to proxy
        proto, rest = urllib.parse.splittype(url)
        if proto is None:
            raise ValueError("unknown URL type: %s" % url)
        #get host
        host, rest = urllib.parse.splithost(rest)
        #try to get port
        host, port = urllib.parse.splitport(host)
        #if port is not defined try to get from proto
        if port is None:
            try:
                port = self._ports[proto]
            except KeyError:
                raise ValueError("unknown protocol for: %s" % url)
        self._real_host = host
        self._real_port = port
        http.client.HTTPConnection.request(self, method, url, body, headers)

    def connect(self):
        http.client.HTTPConnection.connect(self)
        #send proxy CONNECT request
        connect_string="CONNECT {0}:{1} HTTP/1.0\r\n\r\n".format(self._real_host, self._real_port)
        self.send(connect_string.encode('utf-8'))
        #expect a HTTP/1.0 200 Connection established
        response = self.response_class(self.sock, strict=self.strict, method=self._method)
        (version, code, message) = response._read_status()
        #probably here we can handle auth requests...
        if code != 200:
            #proxy returned and error, abort connection, and raise exception
            self.close()
            raise socket.error("Proxy connection failed: %d %s" % (code, message.strip()))
        #eat up header block from proxy....
        while True:
            #should not use directly fp probablu
            line = response.fp.readline()
            print(line)
            if line == b'\r\n': break


class ProxyHTTPSConnection(ProxyHTTPConnection):
    default_port = 443
    def __init__(self, host, timeout = 10, port = None, key_file = None, cert_file = None, strict = None):
        ProxyHTTPConnection.__init__(self, host, port)
        self.key_file = key_file
        self.cert_file = cert_file

    def connect(self):
        ProxyHTTPConnection.connect(self)
        #make the sock ssl-aware
        self.sock = ssl.wrap_socket(self.sock, self.key_file, self.cert_file)


class ConnectHTTPHandler(urllib.request.HTTPHandler):
    def do_open(self, http_class, req):
        return urllib.request.HTTPHandler.do_open(self, ProxyHTTPConnection, req)

class ConnectHTTPSHandler(urllib.request.HTTPSHandler):

    def do_open(self, http_class, req):
        return urllib.request.HTTPSHandler.do_open(self, ProxyHTTPSConnection, req)


if __name__ == '__main__':
    import sys
    # build Proxy handler
    proxies = {'http': 'http://aproxyserver:8080/', 'https': 'http://aproxyserver:8080/'}
    proxy_handler = urllib.request.ProxyHandler(proxies)
    # build basic authentication handler
    password_mgr = urllib.request.HTTPPasswordMgrWithDefaultRealm()
    password_mgr.add_password(None, 'https://www.securedurl.com/', 'username', 'password')
    auth_handler = urllib.request.HTTPBasicAuthHandler(password_mgr)
    # create "opener" (OpenerDirector instance)
    opener = urllib.request.build_opener(ConnectHTTPHandler, ConnectHTTPSHandler, proxy_handler, auth_handler)
    urllib.request.install_opener(opener)
    request_url = "https://www.securedurl.com/default.html"
    req = Request(request_url)
    try:
        response = urlopen(req)
        print(response.read())
    except URLError as e:
        print(e.headers)
        print (e.code)