EPANET Input Files in Django, Part 2 (#26)

In the last newsletter we considered our first Django classes for our modelling of EPANET input files. This time we look at the rest of the classes that we need to replicate the tables defined in our earlier newsletters (#18, #19, #20 and #21).

Table RESERVOIR

Column name	Data type	Nullable?	Description
NETWORKID	Foreign key	No	ID of the network in the NETWORK table
ID	Character(31)	No	ID label (up to 31 characters (letters and digits))
HEAD	Floating point	No	Head (m or ft)
PATTERNID	Character(31)	Yes	Head pattern ID (optional)

This is simple to model, but some changes are worthwhile and are highlighted in the class definition below:

class RESERVOIR(models.Model):
    NETWORKID = models.ForeignKey('NETWORK', related_name='NETWORK_RESERVOIRS', db_column='NETWORKID')
    NAME = models.CharField(max_length=31)
    HEAD = models.FloatField()
    PATTERNNAME = models.CharField(blank=True, null=True, max_length=31)

In this case, we have changed the name of one field from ID to NAME. This is done because Django does not always cope very well with columns called “ID” that are not unique ID columns, particularly if you create them by accident and try to fix the problem using Django migrations. It is quite simple to make this work by defining a primary key column and specifying its name, but if you want to do so, make sure you do it properly from the start.

Using a different column name for our non-unique key is more in step with Django’s design philosophy, so I have made this choice throughout. In the same way, the PATTERNID column name has been changed to PATTERNNAME to make it clear that it is not a true foreign key column, but instead stores the user-specified ID (really a name) of the pattern.

From this class definition, Django gives us an ID column which has no meaning for us, but is not a problem. If you like completeness, our true uniqueness constraint on this table would be that the NETWORKID and NAME columns be unique together across all of the “node” tables (for reservoirs, junctions and tanks). Since our data has been checked before we receive it, we do not need to concern ourselves with this.

Our table is defined in SQLite as follows:

CREATE TABLE IF NOT EXISTS "pots_reservoir" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "NAME" varchar(31) NOT NULL,
    "HEAD" real NOT NULL,
    "PATTERNNAME" varchar(31) NULL,
    "NETWORKID" integer NOT NULL REFERENCES "pots_network" ("NETWORKID")
);

It is probably worth pointing out here that we could also specify the table name if we wanted to using the db_table meta option. Since we have had to make other changes to our table definition, this is not worthwhile, however the method is included below for completeness. The class definition would be:

class RESERVOIR(models.Model):
    NETWORKID = models.ForeignKey('NETWORK', related_name='NETWORK_RESERVOIRS', db_column='NETWORKID')
    NAME = models.CharField(max_length=31)
    HEAD = models.FloatField()
    PATTERNNAME = models.CharField(blank=True, null=True, max_length=31)

    class Meta:
        db_table = 'reservoir'

Table JUNCTION

Column name	Data type	Nullable?	Description
NETWORKID	Foreign key	No	ID of the network in the NETWORK table
ID	Character(31)	No	ID label (up to 31 characters (letters and digits))
ELEVATION	Floating point	No	Elevation (m or ft)
BASEDEMAND	Floating point	Yes	Base demand flow (flow units) (optional)
DEMANDID	Character(31)	Yes	Demand pattern ID (optional)

Replicating this table to contain each of the data fields required by EPANET is easy, and we have made the same “ID” to “NAME” changes as we made for the RESERVOIR table.

class JUNCTION(models.Model):
    NETWORKID = models.ForeignKey('NETWORK', related_name='NETWORK_JUNCTIONS', db_column='NETWORKID')
    NAME = models.CharField(max_length=31)
    ELEVATION = models.FloatField()
    BASEDEMAND = models.FloatField(blank=True, null=True)
    DEMANDNAME = models.CharField(blank=True, null=True, max_length=31)

The resulting table definition in SQLite is:

CREATE TABLE IF NOT EXISTS "pots_junction" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "NAME" varchar(31) NOT NULL,
    "ELEVATION" real NOT NULL,
    "BASEDEMAND" real NULL,
    "DEMANDNAME" varchar(31) NULL,
    "NETWORKID" integer NOT NULL REFERENCES "pots_network" ("NETWORKID")
);

Table PIPE

Column name	Data type	Nullable?	Description
NETWORKID	Foreign key	No	ID of the network in the NETWORK table
ID	Character(31)	No	ID label (up to 31 characters (letters and digits))
STARTID	Character(31)	No	ID of start node
ENDID	Character(31)	No	ID of end node
LENGTH	Floating point	No	Length (m or feet)
DIAMETER	Floating point	No	Diameter (mm or feet)
ROUGHNESS	Floating point	No	Roughness coefficient
MINORLOSS	Floating point	No	Minor loss coefficient
STATUS	Character(6)	No	Status (Open, Closed or CV)

Simple again. Same basic changes again:

class PIPE(models.Model):
    NETWORKID = models.ForeignKey('NETWORK', related_name='NETWORK_PIPES', db_column='NETWORKID')
    NAME = models.CharField(max_length=31)
    STARTNAME = models.CharField(max_length=31)
    ENDNAME = models.CharField(max_length=31)
    LENGTH = models.FloatField()
    DIAMETER = models.FloatField()
    ROUGHNESS = models.FloatField()
    MINORLOSS = models.FloatField()
    STATUS = models.CharField(max_length=6)

The resulting table definition in SQLite is:

CREATE TABLE IF NOT EXISTS "pots_pipe" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "NAME" varchar(31) NOT NULL,
    "STARTNAME" varchar(31) NOT NULL,
    "ENDNAME" varchar(31) NOT NULL,
    "LENGTH" real NOT NULL,
    "DIAMETER" real NOT NULL,
    "ROUGHNESS" real NOT NULL,
    "MINORLOSS" real NOT NULL,
    "STATUS" varchar(6) NOT NULL,
    "NETWORKID" integer NOT NULL REFERENCES "pots_network" ("NETWORKID")
);

Table OPTION

Column name	Data type	Nullable?	Description
NETWORKID	Foreign key	No	ID of the network in the NETWORK table
NAME	Character(17)	No	Name of option (up to 17 characters since the longest possible option name is “DEMAND MULTIPLIER”)
VALUE	Character(251)	No	Value of the option (up to 251 characters, since a line can be only 255 characters long, the shortest option name has three letters, and a space is required to separate the name and its value)

In replicating this table with Django, there is no need to change anything:

class OPTION(models.Model):
    NETWORKID = models.ForeignKey( 'NETWORK', related_name='NETWORK_OPTIONS', db_column='NETWORKID')
    NAME = models.CharField(max_length=17)
    VALUE = models.CharField(max_length=251)

The resulting table definition in SQLite is:

CREATE TABLE IF NOT EXISTS "pots_option" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "NAME" varchar(17) NOT NULL,
    "VALUE" varchar(251) NOT NULL,
    "NETWORKID" integer NOT NULL REFERENCES "pots_network" ("NETWORKID")
);

Table TIME

Column name	Data Type	Description
NETWORKID	Foreign key	ID of project
LINE	Character(255)	Line read from input file

Nothing to change in modelling this table either:

class TIME(models.Model):
    NETWORKID = models.ForeignKey( 'NETWORK', related_name='NETWORK_TIMES', db_column='NETWORKID')
    LINE = models.CharField(max_length=255)

The resulting table definition in SQLite is:

CREATE TABLE IF NOT EXISTS "pots_time" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "LINE" varchar(255) NOT NULL,
    "NETWORKID" integer NOT NULL REFERENCES "pots_network" ("NETWORKID")
);

Phew! Quite a bit of plodding through some rather mechanical porting of tables to Django. But next week, we start to look at some of the real advantages of doing this work in Django.