GoNetNinja: Day 3

Day 3

(Project is published to github ytjohn/gonetninja)

To start off, I made an activity table. This has an id, a net foreign key id, an action, name, and description. The
time_at column is the time the net control operator says the activity happened. This is important since my most
anticipated use case is for recording the information after the fact. Live during the net recording is also a
goal, but will require a lot of focus on the frontend ui.

My original plan was to have a callsign column, but decided at the last minute to put
name here to make it a bit more flexible. But an FCC or equivalent issued callsign is
the expected value.

CREATE TABLE activity
(
    id          uuid      not null
        constraint activity_pk
            primary key,
    created_at  timestamp not null,
    updated_at  timestamp not null,
    net         uuid      not null
        constraint activity_nets_id_fk
            references nets,
    action      text      not null,
    time_at     timestamp not null,
    name        text      not null,
    description text
);
CREATE UNIQUE INDEX activity_id_uindex
    on activity (id);
CREATE INDEX activity_name_index
    on activity (name);

Actions will be an enumerated list in code. Currently this set is planned as:

• open (open the net)
• close (close the net)
• netcontrol - assume net control
• checkin check an operator into the net
• checkout check an operator out of the net
• comment - add a comment to the net

I created some data by hand using Jetbrains/GoLand's database IDE. It showed the
iso format, but saves it as integer. I don't know if pop is going to read that properly,
but I do agree that integer based timestamps will make for better performance and when
I get around to fixing the timezone, I'll want to set the rest up to write as timestamps
instead of iso formatted strings.

sqlite> select net, action, time_at, name, description from activity order by time_at desc;
786829ca-f1c3-11ec-a9d5-ce5390833a0f|close|1655894167000|n0call|close net
786829ca-f1c3-11ec-a9d5-ce5390833a0f|checkin|1655894047000|n3call|regular checkin
786829ca-f1c3-11ec-a9d5-ce5390833a0f|checkin|1655894047000|n4call|regular checkin
786829ca-f1c3-11ec-a9d5-ce5390833a0f|open|1655893987000|n0call|open net
786829ca-f1c3-11ec-a9d5-ce5390833a0f|checkin|1655893927000|n1call|early checkin
786829ca-f1c3-11ec-a9d5-ce5390833a0f|checkin|1655893927000|n2call|early checkin
786829ca-f1c3-11ec-a9d5-ce5390833a0f|netcontrol|1655893867000|n0call|assume net control

Now, to create the model, I'll do the generate and add my other columns like I did with nets.

buffalo pop generate model activity
rm migrations/*.fizz

And the go model.

// Activity is used by pop to map your activities database table to your go code.
type Activity struct {
    ID          uuid.UUID `json:"id" db:"id"`
    CreatedAt   time.Time `json:"created_at" db:"created_at"`
    UpdatedAt   time.Time `json:"updated_at" db:"updated_at"`
    Net         uuid.UUID `json:"net" db:"net"`
    Action      string    `json:"action"  db:"action"`
    Name        string    `json:"name" db:"name"`
    TimeAt      time.Time `json:"time_at" db:"time_at"`
    Description string    `json:"description" db:"description"`
}

// TableName overrides the table name used by Pop.
func (a Activity) TableName() string {
    return "activity"
}

Next steps for Net Activity

First is to display a net with the activity above. This will require a change to the single net handler to query all the activity for the net id, and display these in chronological order. I'll do this the lazy way as two queries, but for optimization, a database view would be the way to go long term.

Second step is I want to make my "easy" form. This is easy for the user, not for the development.

There will be fields for a net name, net control, opened time, closed time, followed by "early checkins" and "regular checkins". The handler will read these fields, create a net with the planned open/start times, then create activity for netcontrol, open, and checkins. It will need to use the open time smartly. The activity for netcontrol and "early checkins" will take place one minute before the net start time. The "open" action will be created at net time, as will checkin actions for "regular checkins".

Once all the data is saved, it should redirect back to the single net form.

Future step is to start working on a net editor, where one can edit the invidivual net information and activity.

View Net Activity

To query the activities, we update our NetHandler. After retrieving the net, we then fetch the Activities order by time_at, and add that to our context.

func NetHandler(c buffalo.Context) error {
    //tx := c.Value("tx").(*pop.Connection)
    net := models.Net{}

    if err := models.DB.Find(&net, c.Param("id")); err != nil {
        return errors.WithStack(err)
    }

    activities := models.Activities{}
    query := models.DB.Where("net = (?)", net.ID)
    if err := query.Order("time_at").All(&activities); err != nil {
        return errors.WithStack(err)
    }
    c.Set("net", net)
    c.Set("activities", activities)
    return c.Render(http.StatusOK, r.HTML("home/netview.plush.html"))
}

Then update the template to display this fanciness.

    <table class="table table-striped">
          <thead>
          <tr text-align="left">
            <th>Action</th>
            <th>Callsign/Name</th>
            <th>Description</th>
            <th>At</th>
          </tr>
          </thead>
          <tbody>
          <%= for (activity) in activities { %>
          <tr>
            <td>
              <%= activity.Action %>
            </td>
            <td class="left">
              <%= activity.Name %>
            </td>
            <td>
              <%= activity.Description %>
            </td>
            <td>
              <%= activity.TimeAt %>
            </td>
          </tr>
          <% } %>
          </tbody>
        </table>

This all amazingly worked right off the bat. The timestamp ints read from the database get converted into human readable strings, and activity is displayed in order. If there is no activity, no rows are displayed.

Now we're going to update the handler to find the list of netcontrols, the first net open time, and the last net closed. There's no validation yet decided on adding activity. Ie, someone could checkin before the net opens, add comments after it closes, close but never open the net, or open and close it several times. Our plan is to attempt to guide users through the most likely scenarios, but otherwise try to make the best of whatever is passed in.

I went ahead and created a function to find the first open, by time_at. I left error handling out.

func GetOpen(id uuid.UUID) time.Time {
    activity := models.Activity{}
    query := models.DB.Where("net = (?) AND action = (?)", id, "open")
    query.Order("time_at asc").First(&activity)
    // SELECT activity.action, activity.created_at, activity.description, activity.id, activity.name,
    //  activity.net, activity.time_at, activity.updated_at FROM activity AS activity
    // WHERE net = (?) AND action = (?)
    // ORDER BY time_at asc LIMIT 1 $1=786829ca-f1c3-11ec-a9d5-ce5390833a0f $2=open
    return activity.TimeAt
}

Then in my NetHandler function, I add this result to the context.

    c.Set("opened", GetOpen(net.ID))

I created a GetClose that is nearly identical, but with action = close and time_at desc. Creating a few extra open and close records shows the desired behavior. Alternatively, I could do time_at asc and choose Last record.

    query := models.DB.Where("net = (?) AND action = (?)", id, "close")
    query.Order("time_at desc").First(&activity)

That works, though if no action with "open" or "clsoe" is found, the time.Time defaults to Jan 1st, 0001. This is correctable in the html template, but I wonder if there's a way to have a None/Null time.Time.

      <p>
        Actual Open: <%= if (!opened.IsZero()) { %>  <b><%= opened %></b> <% } else { %> - <% } %>
        Closed: <%= if (!closed.IsZero()) { %> <b><%= closed %></b>  <% } else { %> - <% } %>
      </p>

While still working on presenting the data, we get into some fun bits with golang and queries. I wanted a list of the net control operators, as well as all the participants. This is identically the same thing, just whether you query on "netcontrol" action or not. My first stab was to make a slice of strings and then reduce that to a unique set. But various Q&A sites suggest that I make a map instead. Once I did that, I realized my sorting was out the window. So I worked out how to do a map, then throw the keys into a slice, and sort that.

I'm iterating over things twice, so I don't believe it's really performant. With a raw database query, I would do a DISTINCT select, but I'm not yet sure if that's a feature of buffalo's "pop".

func NetControls(id uuid.UUID) []string {
    //err = models.DB.Select("name").All(&users)
    set := make(map[string]struct{})

    activities := models.Activities{}
    query := models.DB.Where("net = (?) AND action = (?)", id, "netcontrol")
    query.Order("name desc").All(&activities)

    for _, a := range activities {
        logrus.Info("checking participant ?", a.Name)
        _, isPresent := set[a.Name]
        if !isPresent {
            logrus.Info("found new participant ?", a.Name)
            set[a.Name] = struct{}{}
        }
    }
    keys := make([]string, 0, len(set))
    for k := range set {
        keys = append(keys, k)
    }
    sort.Strings(keys)

    logrus.Info(keys)
    return keys
}

Then, in the template, I add in some if/else logic.

    <p><b>Net Control Operator<%= if (len(netcontrols) > 1) { %>s (<%= len(netcontrols) %>)<% } %>:
      <%= for (n) in netcontrols { %>
        <%= n %>
      <% } %>
      </b>
    <br />
    <b>Net Participant<%= if (len(participants) > 1) { %>s (<%= len(participants) %>)<% } %>:
        <%= for (p) in participants { %> <%= p %><% } %>
      </b>
    </p>

Day 3 Wrap-Up

At this point, my timer is reading 7hrs, 4 minutes. I went ahead and added 3 hours for my Monday/Tuesday work, so total time in this project is around 10 hours.

I did some further playing with the templates, adding activity-{name} classes to the rows (mainly for colors), putting some logic to create an "early" or "late" flag for checkins that happen before the net was opened or after the net was closed.

I didn't get to a point of making any forms, and I may work on this some more this evening. But the more I work on how the net details page will look, the more it helps me in thinking how the form will look. Ultimately, my net details page will also be the editable form. There would be a button to switch from readonly to an editable view. I've used htmx in the past to simplify the live ajax style of page updating, and I think it will fit here. Basically buttons to assume net control, open and close the net, and an in-line form to add activity (mainly checkins or comments). I can see a relatively seamless UI path for the "quick entry" and individual action entry, though I know there will be lots more to learn on the backend.