Creating the Temporary "WPC" (Water Pipes Current) Table in the AMDB

I wanted it clear in the database structure, that when a replacement chopped up a previous pipe, what the original situation had been. This 20-year-old slide illustrates the solution:

The trick is to have one table for the pipe description, and a separate one for the network-locations of assets. Multiple records on that table - multiple chunks of pipe - could all join to the one pipe record, as it was chopped up.

The pipe at left is one of the few situations where a pipe has been chopped into three remaining pieces, by two short PVC replacements. In 2015, there were breaks where the two short green sections are, and we decided to replace as much pipe as you could in one night, so there wouldn't be repeats on 14th street. (They must have done the job between 8PM and 6AM.)

The table in the diagram shows the list of all pipes, current (feat_id=901) or replaced (feat_id=905), and sorted north to south. That's the network-locations table, WTR_FEAT_ITEMS.

There are a total of seven "901" records, current pipes. The first two are an original CI put in 7 years later in 1957 (Calgary may have kind of ended at 16th avenue in 1950); and the 1986 PVC installed under 16th avenue so that we wouldn't have to worry about ever digging up the LRT.

Then the third, "100291", is the first of the three pipes that used to be just one, numbered "10029".

Below that: Circled, in magenta, are the two pairs of replacement 905 pipes,that show the original 10029 as their "asset_table_mslink" back to the WTR_PIPE table.

You can see the pairing plainly: both the 905 pipes have a 901 pipe, their replacment, right beside them, at he same "arc_dist" location, and the same length.

A primary job of the AMDB, when it started, was making an analysis table used so often it was abbreviated to WPC, water_pipes_current. It's just the join of the location table (WTR_FEAT_ITEMS) with all the current pipe pieces, and the WTR_PIPE table, for all the pipe attributes. But then you need some added record ID numbers, the Microstation MSLINK.

Normally, after the WPC_CREATE.SQL script, the "901", current pipes, have "asset_table_mslink" values that end in a zero, because in AMDB, they don't point at the WTR_PIPES table anymore, as they do in the production Infranet database. They've been changed to point at the new, AMDB-only, temporary-each-day, "WPC" table, which has one record for every current pipe. That means when WPC is created, multiple records into it are copied by this statement, which just duplicates the 'asset_table_mslink' if multiple 901 records use it.

insert into wpc
    (mslink, vintage, pipe_zone, year, material, diameter, line_assignment, 
     microfilm, fm_segment_num, fm_name, pipe_table_link, arcs_mslink, arc_dist, length
    )
    (select asset_table_mslink*10,vintage,zone pipe_zone,year,material,
            diameter, line_assignment, microfilm, fm_segment_num, fm_name,
            asset_table_mslink, arcs_mslink, arc_dist, length
        from wtr_pipe          P,
             wtr_feat_items    F
        where feat_id=901
          and arcs_mslink>0
          and P.mslink=F.asset_table_mslink
    );

English translation: the SELECT picks out every current pipe in the network (feat_id=901) and joins the WTR_FEAT_ITEMS table containing them, to the WTR_PIPE table to get their material, diameter, year, and other pipe attributes.

But it inserts this into the new WPC table with "asset_table_mslink*10" as the MSLINK (see boldfaced code). That's why all the 901 records on the list end in zero.

For over 99% of the network arcs in the water network, that's all there is to it; most pipes are completely replaced, and the whole pipe record becomes a "905" replaced, not-current pipe.

For about 141 arcs, as of 2018, we have the "chopped in two" pipe from the original presentation in 2002. For those arcs, the records in the WPC table have two records with the same "MSLINK" column value, which can't be allowed.

So the "wpc_create.sql" file adds one onto one of their two MSLINK numbers with this trick:

 update wpc set mslink=mslink+1 where rowid in
    (select min(rowid) from wpc where mslink in
        (select mslink from wpc group by mslink having count(*)>1)
        group by mslink
    );

English Translation: find the duplicate MSLINK values in the WPC table (having count(*)>1) and get the member out of each set of duplicates with the minimum "ROWID" value. ROWID is the invisible, always-there Oracle column in every Oracle table that is always unique. And add one to the MSLINK for that row.

The two copies of the original single pipe record, then go into WPC with MSLINK of the [pipe-mslink]*10 and [pipe-mslink]*10+1.

That would handle the 140-odd arcs that are chopped up once. But then, not this arc, which would have a record with "100291" instead of "100290", but would still have two "100290" records. Very simple. I won't bother repeating the code, but you run the exact same statment again, except with "MSLINK=MSLINK+2" in the top line. That finds the second one and makes the MSLINK "100292".

So in the example here, we see the third pipe listed, 79.9983 metres long, with 100291, and the one in between the two replacements, 44.6618 metres long, is 100292.

So, that's how WPC is created, since just before 2010. There is one vulnerability in the algorithm.

Eventually, somebody will chop up a pipe three times, then there will be an error when it tries to uniquely index the MSLINK column of the WPC table that night. A third statement, like the one above, should be added, except with "MSLINK=MSLINK+3" in the first line.

When that is added, nothing will happen, except there will be a third statement with a "zero" at the end in the nightly log:

10  |04:36 |update wpc set mslink=mslink+1 where rowid in (sel |111
11  |04:36 |update wpc set mslink=mslink+2 where rowid in (sel |5
  

12  |04:36 |update wpc set mslink=mslink+3 where rowid in (sel |0

If that zero on the end is ever "1" one morning, it's because a draftsman chopped up a pipe with a third short replacement, the day before.

This third statement will probably never be needed before Infranet is replaced. But you'll be covered, and not surprised, should it happen. It's the kind of thing that would take ages to figure out and debug, without this reminder of how the whole "WPC" trick works. We got it working so long ago (over 10 years) and haven't had to look at it since.