Getting at Oracle ROWID

[rahajiyev]

There's an existing Oracle database I want to hook Django up with. The
problem is, the table I need lacks single-key primary keys. Oracle
does provide its unique table-wide ROWID which can normally be used as
a PK with a few restrictions. But it's a LOB, so a cast to string is
normally required to fetch it: SELECT ROWID || ''
Writing this expression a piece of cake for CakePHP and its
virtualFields. Can Django do the same?

Well, you guessed it, I've actually learned CakePHP and finished
writing a simple operator interface for remote Oracle + local Postgres
+ auth roles. Now I have some spare time to rewrite it in Django,
learning it at the same time (got some background learning it a year
or two ago).

[Ian]

On Thursday, June 7, 2012 3:55:37 AM UTC-6, rahajiyev wrote:

There's an existing Oracle database I want to hook Django up with. The
problem is, the table I need lacks single-key primary keys. Oracle
does provide its unique table-wide ROWID which can normally be used as
a PK with a few restrictions. But it's a LOB, so a cast to string is
normally required to fetch it: SELECT ROWID || ''
Writing this expression a piece of cake for CakePHP and its
virtualFields. Can Django do the same?

MyModel.objects.extra(select={'rowid': "rowid || ''"})

Cheers,
Ian

 

[rahajiyev]

> MyModel.objects.extra(select={'rowid': "rowid || ''"})
>
Thanks. I don't think extra() takes primary_key=True, does it?
I wouldn't want Django to auto-add column id otherwise.

Assuming I have:
MyModel.objects.extra(select={'id': "rowid || ''"})
OK, that's fine for fetching the ID itself, suitable for creating
forms. But what if I want to fetch an item by this virtual PK, or
update by PK with or without prior fetching?

[Ian]

I haven't tried it, but for those you should be able to use an extra where, similar to the extra select above.

MyModel.objects.extra(where=["rowid = whatever_syntax_you_need"]).update(cake="lie")

If using .extra() on all your queries isn't acceptable, you should be able to write a custom Field class for rowids and add that to your models with primary_key=True.  It will probably break syncdb (but I assume this is a pre-existing table anyway if you need to use rowids).

https://docs.djangoproject.com/en/dev/howto/custom-model-fields/

Cheers,
Ian

[rahajiyev]

Thanks, here's what I came up with:

In the model:
class RowidField(models.CharField):
        def __init__(self, *args, **kwargs):
                kwargs['name'] = 'rowid'
                kwargs['max_length'] = 18
                kwargs['primary_key'] = True

                super(RowidField, self).__init__(*args, **kwargs)

class CrefTab(models.Model):
        id = RowidField()
        cref_no = models.CharField(max_length=24)
        ...
        class Meta(SvistaMeta):
             db_table = 'cref_tab'
             managed = False


Then in the view:
        cref_tab_list = CrefTab.objects.all().using('svfe')[:5]
        output = ', '.join([t.cref_no for t in cref_tab_list])
        return HttpResponse(output)

DatabaseError at /

ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.

Unfortunately Django doesn't show the precise statement that caused the error.

[Ian]

On Wednesday, June 13, 2012 5:48:07 AM UTC-6, rahajiyev wrote:

DatabaseError at /

ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.

Unfortunately Django doesn't show the precise statement that caused the error.

The query it generates is:

SELECT * FROM (SELECT ROWNUM AS "_RN", "_SUB".* FROM (SELECT "CREF_TAB"."ROWID", "CREF_TAB"."CREF_NO" FROM "CREF_TAB") "_SUB" WHERE ROWNUM <= 5) WHERE "_RN" > 0

(You can find this using str(queryset.query) in a Django shell.)

The error appears to be caused by the outermost SELECT *, since the query runs all right if you remove it.  It also works if you add an alias to the rowid column, e.g. "SELECT CREF_TAB.ROWID AS FOOBEAR".  You might be able to finagle the Field into adding a column alias, but then you will likely break something else.  I think we're back to using extra().