EPANET Input Files in Django, Part 1 (#25)

In an earlier series of newsletters, we looked at input files for EPANET, a public domain water supply network modelling tool.  To minimise the modelling required, we took as an example the simplest water supply network that I could think of – Hezekiah’s tunnel in Jerusalem – and considered the tables we would need to store the information required in an EPANET input file for this network.  See newsletters #13 to #21.

Now, instead of defining tables, we are defining Django models.  Let’s first quote the table we defined and then show the equivalent Django model.

In an attempt to make the Django models match the table design intent and definitions, we will use character ID fields.  These fields will contain the user-assigned EPANET IDs, which are more like the names, but must be unique in their class or set of classes.

Table NETWORK

For this class, we can use a Django AutoField for our network ID.  All we need to do is specify that this field is a primary_key.

class NETWORK(models.Model):
    NETWORKID = models.AutoField(primary_key= True)
    FILENAME = models.CharField(max_length= 250) 

When we run ‘makemigrations’ and then ‘migrate’ for the pots app, Django will create a table in the SQLite database called pots_network, with the NETWORKID column being an auto-allocated primary key.  The table creation command generated will be:

CREATE TABLE IF NOT EXISTS "pots_network" (
    "NETWORKID" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "FILENAME" varchar(250) NOT NULL
); 

Table NETWORK_TITLE

To replicate this table, we create a Django class as follows:

class NETWORK_TITLE(models.Model):
    NETWORKID = models.ForeignKey('NETWORK',
                    related_name='NETWORK_TITLE_ LINES',
                    db_column='NETWORKID')
    LINENUM = models.IntegerField()
    TITLE = models.CharField(max_length= 255) 

This model class is a little different because it does not include a primary key.  Django, however, requires one field to be a single column primary key for each table.  Since we have not specified one, Django will create a column for an automatically allocated unique ID as shown in the following table creation command that Django generates (re-ordered so that the fields are in the same order as our previous example table).  For our use, the ID will have no practical meaning.

CREATE TABLE IF NOT EXISTS "pots_network_title" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "NETWORKID" integer NOT NULL REFERENCES
        "pots_network" ("NETWORKID”),
    "LINENUM" integer NOT NULL,
    "TITLE" varchar(255) NOT NULL
); 

The first time I ever created a serious Django model, I was “caught” by the automatic naming of foreign key columns.  Personally, I like to name foreign key columns so that they have “ID” or “_ID” at the end.  This means that the names of columns will remind me when unique IDs are being stored in a column.  Django has the same sort of idea, and so foreign key columns are given the name of the field with “_id” appended on the end automatically.  Thus, by default, the NETWORKID field would be stored in a column NETWORKID_id.  Fortunately, we can avoid the double suffix by specifying the column name using the db_column argument of the ForeignKey field.

In our original example, we did not have a primary key at all in this table because the data we are planning to store is already checked before it is passed to us.  If we were creating a network design or analysis tool, what we would really want would be to have NETWORKID and LINENUM as a two-column primary key, but Django does not support this.  What we would have to do instead would be to specify in the metadata options that these two columns are “unique_together”.  Let’s see how the class would look in this situation:

class NETWORK_TITLE(models.Model):
    NETWORKID = models.ForeignKey('NETWORK',
                    related_name='NETWORK_TITLE_ LINES',
                    db_column='NETWORKID')
    LINENUM = models.IntegerField()
    TITLE = models.CharField(max_length= 255)

    class Meta:
        unique_together = (("NETWORKID", "LINENUM"),) 

This constraint is reflected in the creation by Django of a unique index as shown below.

CREATE UNIQUE INDEX "pots_network_title_NETWORKID_ fcc47f7e_uniq"
    ON "pots_network_title" ("NETWORKID", "LINENUM"); 

We will review more model classes next week as we walk through Hezekiah’s tunnel (God willing).