Saturday, October 4, 2014

Django ORM - Composite Key Problem - Special Case Solution

Problem: Recently had an assignment to pull data out from client database and expose the data tables as dashboards to be previewed by the clients. So the solution was to expose the tables as Django models via admin interface, simple !  The problem was that the client had a legacy Oracle database with read only access to us, so any changes to table like adding one ID column was ruled out. Initial analysis of database showed that tables were simple flat data files with no foreign key relations but having composite key as Primary Key for each of the table. Therefore Django ORM could not map objects - one to one , meaning thereby objects.get(id =pk) will throw an exception. And hence django admin will not work as easily as I thought.

Solution: Django does not support composite key by default for the reason as mentioned in documentation but my case was - I wanted to have only a read only view , with not even selecting a  single object to edit or delete or update. For the case in hand I only wanted the django's select all view - default dashboard where all the objects of particular type gets listed ( rows of the database table ).  So here is the solution
  1. So made any one field as Primary key - irrespective of what the field is, as in case of django primary key is needed for selecting one object. 
  2. Remove the hyperlink for selecting the single django object 
  3. Remove the default add button that comes with django admin to avoid inserts in the database by a primary key
  4. Remove the Action tool bar to remove the delete action - In case if you want to have some custom action like -Export Data functionality on your read only view , you have to rely on raw django queries and not the ORM
  5. The whole attempt is to not to let user select one particular record / object from the dashboard and hence avoiding the need to django ORM to call objects.get(id=pk)
Sample Code Snippet:

Summary: The above technique is only applicable for cases where no direct selection of object is required via django ORM. This is quite a bit of constrain for anyone thinking of using django for legacy database with default ORM. Thinking of evaluating other ORMs and how well they integrate with django ecosystem.  Suggestions ?  SQLAlchemy ??

But for now we are good to go !