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.