Second part of this project. You can read the first part here.
Today I have started with the work of replicating existing admin functions on the new site. During this process I have been checking the new screens as I go through them with the existing screens. A few things I have noticed.
Firstly, text fields are coming over as mandatory, even though on the postgres database they are not. So far this has been for comments fields which are usually blank on this system. Simple to change.
[cc lang=”python”]
comments = models.TextField()
[/cc]
becomes
[cc lang=”python”]
comments = models.TextField(null=True, blank=True)
[/cc]
I’ve also needed to set the verbose name that appears on columns and fields. For example, I tend to use the field name ‘code’ as a standard field name for the short code of an item, but on the admin it needs to apppear as ‘Signal Code’. To make the change amend the model definition to read:
[cc lang=”python”]
code = models.CharField(max_length=1, verbose_name=’Signal Code’)
[/cc]
So far so good, then I came across a bigger issue and remembered some of the ‘quirks’ of this system. Take the following SQL that creates the display for one of the maintenance screens in the old system.
[cc lang=”sql”]
select distinct
1 as tc_id,
t_pn_assm.partno as pn_assmpartno,
t_valid_groundtyre.*,
t_wheel.description as wheeldescription,
t_tyre.description as tyredescription,
t_wheel.code as wheelcode,
t_tyre.code as tyrecode,
coalesce(t_valid_vehicletype.description, ‘unknown’) as vehicle_description
from
t_valid_groundtyre
left join t_wheel on t_wheel.id = t_valid_groundtyre.wheelid
left join t_tyre on t_tyre.id = t_valid_groundtyre.tyreid
left join t_valid_vehicletype on t_valid_vehicletype.code = t_wheel.valid_vehicletypecode
left join t_pn_assm on t_pn_assm.valid_groundtyreid = t_valid_groundtyre.id
order by
t_wheel.code, t_tyre.code
[/cc]
We get our wheel from our wheelid, but we get the vehicle type by using the vehicle type’s code, left joining to the wheel’s vehicletypecode. We need to model the relationship between our wheel and our vehicle type. Simple to do.
On our wheel model we have
[cc code=”python”]
vehicle = models.ForeignKey(‘ValidVehicleTypeCode’,
to_field=’code’,
db_column=’valid_vehicletypecode’)
[/cc]
The key thing here is the ‘to_field’. If you are not linking to the related models primary key, you will need to specify that. In order to make that work however, you have to reassure Django that the ‘to_field’ you are pointing to is unique. So on our ValidVehicleTypeCode model we have.
[cc code=”python”]
code = models.CharField(max_length=1,
verbose_name=’Signal Code’,
unique=True)
[/cc]
This t_valid_groundtyre table has become more and more of a bug bear. It was obvious that the original database design was flawed and the system had to be slightly bent to allow for the flexibility required. From memory I believe that this happened because a core part of the system (EDI) was hastily tacked on once the main production part of the system was in place and live. The relationship between this table and the edi part number tables means that one production part could in theory have multiple part numbers in EDI! This is of course wrong. I can’t change the structure of the system during this exercise (we are doing front end only), but I can at least allow for the displays to flag up any such issues and I have done this. The new system will show multiple EDI part numbers against one production part and make it a lot easier to track down should this happen.
It was at this point I discovered a big problem. I couldn’t create any new records as any record would not have a new id assigned automatically. The first part to fix this is simple. The inspectdb command created the following fields for each model:
[cc code=”python”]
id = models.IntegerField(primary_key=True)
[/cc]
Remove that line and you can now add them. However, if you choose ‘Save and continue editing’ it doesn’t display the newly added item the admin redirects you to to (for example):
http://localhost:8000/admin/alc/validairpressurecode/None/
and tells you that “object with primary key u’None’ does not exist”. It seems to be because my tables were created in an old version of postgres and the Django postgres code doesn’t know what the postgres sequence is and can’t therefore query it to find what was just inserted. From some delving around I discovered this issue logged but no fix. Some after some thought and false starts I wrote some middleware to fix the issue by monkey patching Django’s postgres code that pulls the last number. You can find more info about there here.
That was one of those unexpected items you hope don’t happen too often in a project. Now back to the fray.
This was followed by more trudging through the old system screens and recreating them in Django. Tedious though it was it was SO less painful than any other web system I know. And each maintenance screen had 100 times the functionality of what it was replacing. Along the way I did a bit more splitting up of the models. Incidentally, I don’t think I mentioned that I tend to mirror my admin code to my model code. So if I have a models/edi.py I will also have an admin/edi.py. At this point in time the current structure of the entire project is like this:
(ttas-django)ian@vm:~/projects/ttas-django$ tree .
.
├── alc_schema.sql
├── alc.sql
├── bin
│ └── django
├── buildout.cfg
├── develop-eggs
├── parts
└── project
├── alc
│ ├── admin
│ │ ├── edi.py
│ │ ├── __init__.py
│ │ ├── main.py
│ │ ├── maintenance.py
│ ├── __init__.py
│ ├── middleware.py
│ ├── models
│ │ ├── edi.py
│ │ ├── __init__.py
│ │ ├── legacy.py
│ │ ├── main.py
│ │ ├── maintenance.py
│ ├── tests.py
│ └── views.py
├── development.py
├── __init__.py
├── local_settings.py
├── media
├── production.py
├── settings.py
├── templates
├── urls.py
Note the middleware.py which is handling our code to fix that problem I described earlier with the missing next numbers. Obviously I don’t have a legacy.py in admin as the whole reason for that file in models is that I probably don’t need to use it.
Another thing I noticed, which I hadn’t before is that I was missing some records on certain screens. On one screen it was only showing 24 records. It should have been showing 27 records and the bottom of the screen said there were 27 records in total. I tracked this down to the fact that I had a foreign key which wasn’t flagged as allowing null. When I modified the field definition the three hidden fields (which has a null for that field) appeared again.
I’ve had to make a couple of compromises in order to move on. Once again I make the point that I am not changing the structure of the system in any way and that limits some of the things I want to do. The structure of the database has meant that on a few of the display lists I want to show a column such a model1__model2__code. But Django will not let you do this. Instead you can create a method on model1 that returns the code from model2 but that makes the column unsearchable, unsortable and unfilterable. A little disappointing but little I can do. I discovered also that (since the database has no referential integrity) there are a few records which point to other records, but those records don’t exist – throwing a Django error. To get around this I’ve had to write a few helper methods that look for a record, catch any record not found exceptions thrown and returns None instead. This increases the number of queries going on as I check for these things on a record by record basis but we’re talking about maintenance tables with small numbers of records in them.
By the end of the day I have created all the main maintenance screens, bar one – which I know is going to be a little more of a pain and was intending to leave for another day. The last item I completed was a web version of the factory works instruction print. One sheet has six instructions (we term them requests on it). The real prints are generated by a python twisted process as pdfs outside of the web interface, but we do (for emergency use) allow a web representation to be created if required. The trouble with this is the old report was generated within Zope using dtml and a horrendous sql query. And by horrendous I mean like this:
[cc code=”sql”]
select
t_request.vehicle_type as vehicle_type,
t_request.sequence_number as sequence_number,
t_request.ground_tyre_type as ground_tyre_type,
t_request.ground_tyre_brand as ground_tyre_brand,
t_request.spare_tyre_type as spare_tyre_type,
t_request.spare_tyre_brand as spare_tyre_brand,
t_request.promotion as promotion,
t_wheel.description as wheel_description,
t_tyre.description as tyre_description,
t_request.air_pressure as air_pressure,
t_request.created_at as created_at,
t_valid_matching_wheels.description as matching_wheels_description,
t_valid_airpressure.description as air_pressure_description,
t_request.error_code as error_code,
t_request.error_text as dataerror_code,
t_request.tmuk_line as tmuk_line_no,
v_valid_groundtyre.barcode as ground_tyre_type_barcode,
ground_brand.barcode as ground_brand_barcode,
t_valid_matching_wheels.barcode as matching_wheels_barcode,
t_valid_sparetyre.barcode as spare_tyre_barcode,
spare_brand.barcode as spare_brand_barcode,
t_valid_airpressure.barcode as air_pressure_barcode,
t_valid_vehicletype.barcode as vehicle_type_barcode,
coalesce(t_request.original_signal,'(None)’) as original_signal
from
t_request
left join t_wheel on t_wheel.code = substr(t_request.ground_tyre_type, 1, 1)
left join t_tyre on t_tyre.code = substr(t_request.ground_tyre_type, 2, 1)
left join v_valid_groundtyre on text(v_valid_groundtyre.code) = t_request.ground_tyre_type
left join t_valid_matching_wheels on text(t_valid_matching_wheels.code) = t_request.spare_tyre_type
left join t_valid_sparetyre on text(t_valid_sparetyre.code) = t_request.spare_tyre_type
left join t_valid_airpressure on text(t_valid_airpressure.code) = t_request.air_pressure
left join t_valid_vehicletype on text(t_valid_vehicletype.code) = t_request.vehicle_type
left join t_valid_tyrebrand as ground_brand on text(ground_brand.code) = t_request.ground_tyre_brand
left join t_valid_tyrebrand as spare_brand on text(spare_brand.code) = t_request.spare_tyre_brand
left join t_dataerror on t_request.error_code = t_dataerror.id
where
sisid =
[/cc]
I really didn’t want to spend time coding the logic for that into our models as for the web side it’s only used here. Really all I wanted to do was take the zope dtml document (zope’s old templating language) and the query and move them straight to Django with as little pain as possible. Although it took me a while to work out how to do it – it was quite painless when I had worked out how to do it.
On my works instruction sheet model I created a method called wisheet_data. This contained the sql statement above (with a few small mods which I will detail in a moment) which was fed into a raw SQL query. Raw sql queries are documented here. This method would bring me back a bunch of requests with the additional fields the above sql query details. So I could refer to (for example) ‘ground_tyre_type_barcode’ as if it was a field on my request model.
The bit I didn’t quite get was passing the id of my works instruction sheet into it. You must do the following. In the query you’ll have your conditional statement as:
where sisid = %s
Not %d, not anything else even though you are passing it a number. Don’t do that. Then the actual call that brings the requests back is as follows.
[cc lang=”python”]
return TMUKRequest.objects.raw(sql, (self.id,))
[/cc]
That’s a tuple. Do NOT say ‘self.id’ – it will crash when you try and slice it. Do NOT say [self.id] as it just won’t work. Give it a tuple. And voila you’ll get back a list of records with all the new fields the sql query defines.
Then it was a case of copying over the zope dtml document and replacing the zope syntax with django syntax. So stuff like:
[cc lang=”html”]
[/cc]
becomes in Django
[cc lang=”html”]
{% for request in record.wisheet_data %}
{% endfor %}
[/cc]
There was obviously a lot more of that, but it was mostly just a case of one to one swapping with a few extras (the format strings were very different as you can see). And all the field names I had in the original were exactly the same for the new version.
Now I am half way through. The next big items of work are a couple of complicated forms and screens which need to communicate with the existing python backends. That should be another day which will leave half a day to make some cosmetic enhancements.