Friday, December 31, 2010

One year closer to 2012

So happy new year everyone!!!

I got a feeling this will be a interesting year. For everyone.

Wednesday, December 29, 2010

A little misadventure with django and database

** this could be long**

It is almost the end of my project now, along the way, found a misadventure with quite number of stuff. Which included Django ORM. What is not to love for a ORM, it make a nicer abstraction to wring program that need to talk to db. Making us, to worry less about sql. Using django ORM with south, it make refactoring database a lot easier.

Well in my current job. The concept of using ORM is pretty new in the company(from what I see). So the projects they done is done like pre-ORM days. Writing SQL, doing select etc. For a person that is used to ORM, I am thinking like "Really?". So the way they do things is obviously very different, and this with Django's ORM, not a pretty sight.

For example, when integrating with other project. The expectation is, from a temp table that stores information from the other DB then write a INSERT statement, and push it to the target table. I was like again thinking "Really? They really did that?", especially the target table is huge.

And of course, in Django, I pretty used to have foreign key to reference to another models(table), which I have a few. And because I use django south, so that I can refactor the database from time to time. The order of the column is weird. Not to mention that the idea of a ORM is to make it look like a object, in my case python object, so I used to use boolean type, which from other table, uses char. So I imagine create a INSERT statement with a few SELECT, and map the variable properly. Pretty scary to me. I ends up writing a python script that uses the ORM to do it.

For the temp table, that contains info from the program i need to integrate with. It tend to be created by hand, because it make the existing script to export the table to sane. So have to create a unmanaged table. So django don't create the same table. Which I found, that for that particular case, I can't create a django fixtures for that models without losing data. Which I still investigating. In the end of the day, I just do a sql dump, just in case.

Also, as we build the application, the team is expecting a totally different behavior for Django queryset API. When in SQL, when you can't just delete a table that have another table reference to it. Django queryset don't seems to care, and will delete it anyway. So that is causing issue in a way that, we have to add checking before the user can delete anything in the application.

Talk about deletion, django ORM added constraints on column that refer to another table as they create the table. So manually delete and insert via SQL is a pain. But it is always faster to do that!!! Because the queryset API can be slow. And sometime they have quite a number of magic that scares me sometime and sometime bites.(or maybe I just need to understand it more).

After all these misadventure. I just realized, you can't just drop a database guy can get them to use ORM. because the workflow is totally different. The technology is different. Everybody need agree that approach, or properly planned it. Actually I am not sure. But what is sure, mix of ORM and non-ORM approach can be a mess.