MS SQL Server: Problem with User's history in Admin interface

[Rockallite]

Using ado_mssql for db backend, I find that the django_admin_log table created by manage.py install admin command has a problem. The data type of object_id column is "text", which is one of the unsortable data types (text, ntext, image) in MS SQL Server. That makes the user history in Admin interface unusable, throwing expections.

Also it doesn't make much sense if an ID column is of "text" data type. Instead, the data type should be "integer". So the contrib/admin/models.py file has to be modified. I applied the change, and the user history page worked fine.

[Rockallite]

[patch]fix user history in admin interface

[Rockallite]

[patch] This is the correct patch! Sorry I don't have SVN so I had to hand-make the patch...

[Malcolm Tredinnick]

I don't think changing the type of object_id is going to work here. Because it needs to be able to contain the value of the primary key fields from any arbitrary model and because primary keys do not have to be integers, a text field is about the best we can do (storing the string form of the referenced primary key).

So this is still a problem that needs to be solved in some way for SQL Server, but this is not the right way.

Changing the title to reflect what the real problem is.

[Rockallite]

How about changing it to CharField type which is sortable in SQL Server?

[Rockallite]

[patch] Here's another scheme: use CharField type for storing referenced keys in string form. Is it possible for databases other than SQL Server?

[Malcolm Tredinnick]

So the obvious problem is that this won't work for the general case because now it fails for anybody with a primary key of CharField(maxlength = 200), for example. Yes, I'm playing Devil's Advocate here, but I'm trying to indicate that we shouldn't be going for lowest common denominator support if it breaks some legimate use cases.

The ultimate fix will change this to something that uses TextField for most databases and something more restrictive (like a CharField) for databases with restrictions like SQL Server. It means some third-party models (nothing in Django core, though) might not work with SQL Server because they require the extra functionality, but that's the one of the costs of making that database choice.

One idea is to make a new field that becomes VARCHAR or TEXT in databases that support sorting on those fields and maps to CHAR(n) on others. However, I'm not totally in love with that idea, either, since it means the programmer can't just use the natural choice (looking at the wider use of that new field): they need to be aware of database differences.

This needs more thinking, definitiely. It's not an easy problem to solve nicely. However, please keep coming up with ideas. They help the process.

[Adam Vandenberg]

Resolving as invalid, as the SQL Server backend has been removed. 3rd party SQL Server back-ends have solved this bug in various ways (such as using varchar(max) on SQL Server 2005.)