DEV Community: Michael Chaney

Handling X -> Y -> X Relationships in Rails

Michael Chaney — Wed, 28 May 2025 02:18:28 +0000

I'm creating another publishing database, and this time I have to get the relationships between publishers correct. Up until now, I've been able to cheat a bit as the publishers that I work with tend to be in the film and TV music business and have pretty simple publishing needs. But it's time to really model this properly.

Publishers can make deals with other publishers. There are two deals that we're specifically interested in:

Subpublishing
Administration

There's no need to get too far into the specifics of these deals, but in general one publisher is the "assignor" and the other is the "assignee" (or "acquiror"). The agreement itself has information as well: minimally an agreement number (assigned by a performing rights organization), some sort of royalty share percentage, and a territory(s) covered by this agreement.

So, the basic form of this is Publisher -> SubpublishingAgreement -> Publisher, or Publisher -> AdministrationAgreement -> Publisher. Data-wise, these agreements are practically the same so I'll simply talk about SubpublishingAgreement.

In the world of Ruby on Rails, we might use models like these:

class Publisher < ApplicationRecord
  has_many :subpublishing_agreements_as_assignor,
    class_name: "SubpublishingAgreement",
    foreign_key: :assignor_id,
    inverse_of: :assignor,
    dependent: :destroy
  has_many :subpublishing_agreements_as_assignee,
    class_name: "SubpublishingAgreement",
    foreign_key: :assignee_id,
    inverse_of: :assignee,
    dependent: :destroy
end

class SubpublishingAgreement < ApplicationRecord
  belongs_to :assignor,
    class_name: 'Publisher',
    foreign_key: :assignor_id,
    inverse_of: :subpublishing_agreements_as_assignor
  belongs_to :assignee,
    class_name: 'Publisher',
    foreign_key: :assignee_id,
    inverse_of: :subpublishing_agreements_as_assignee
end

There are a couple of important aspects of this to keep in mind as we program it:

This isn't a general graph where there might be loops. Every relationship should be part of a tree with the base being the original publisher.
There can be many such relationships. It's quite possible for a publisher to have an administrator or set of administrators as well as a few subpublishers, and the subpublishers themselves may have administrators. Each administrator and subpublisher would have a territory or set of territories that they handle.

This picture taken from the Common Works Registration functional specifications gives an idea of how this works in practice:

This can be modeled pretty easily as shown above. If I want to know what subpublishers a publisher has, for instance, I can simply pull them from the SubpublishingAgreement records or use a "has_many...through" relationship.

From a RESTful standpoint, we have a problem. Let's say I'm looking at a publisher record and want to know what subpublishing agreements the publisher is a party to. The issue is that the publisher may be the assignor or the assignee.

Normally, I'd build a RESTful resource path like this:

/publishers/1/subpublishing_agreements

But that path doesn't really capture the complete picture. Are we looking for subpublishing agreements where Publisher 1 is the assignor, assignee, or perhaps either?

There are a couple of options. The first is to add the role into the path part for agreements:

/publishers/1/subpublishing_agreements_as_assignor
/publishers/2/subpublishing_agreements_as_assignee

That's readable and makes sense, but it's a bear to add to Rails routing.

How about this?

/assignors/1/subpublishing_agreements
/assignees/2/subpublishing_agreements

This puts the role into the path up one level. But, it's also a bear to add to Rails routing.

Let's talk about that first one. It's nice that the nomenclature matches that from the model. But, this is a bit ugly in terms of routing. My goal is to have a basic resource-based route. But here's the issue: the later part of the path dictates what the earlier part references. Let me explain that. In this route:

/publishers/1/subpublishing_agreements_as_assignor

I can see that Publisher 1 is the assignor.

/publishers/2/subpublishing_agreements_as_assignee

And in this route, Publisher 2 is the assignee. But I can't know that from the first part of the route.

Ugh.

Let's go down this path and see where it leads.

First, how do we make routes for this? I want for my "publisher_id" to be called "assignor_id" or "assignee_id" in the params. I'm not proud of this, but it works:

    # Nested routes under publishers for role-specific agreements
    scope "/publishers" do
      # Agreements where the publisher is the assignor
      scope ":assignor_id", as: :publisher do
        resources :subpublishing_agreements,
                  controller: "subpublishing_agreements",
                  as: :subpublishing_agreements_as_assignor,
                  path: :subpublishing_agreements_as_assignor
      end

      # Agreements where the publisher is the assignee
      scope ":assignee_id", as: :publisher do
        resources :subpublishing_agreements,
                  controller: "subpublishing_agreements",
                  as: :subpublishing_agreements_as_assignee,
                  path: :subpublishing_agreements_as_assignee
      end
    end

It's difficult to really tell what this is doing, but it creates the routes shown above.

There is one major problem, though. Let's say I want to put this inside another resource. I do, because this is a multi-tenanted application and the full path will be something like this:

/libraries/1/publishers/1/subpublishing_agreements_as_assignor/1

(note that I personally have no problem with long paths like this that give details of the entire relationship)

Normally, we would have a path like this:

/libraries/1/publishers/1/subpublishing_agreements/1

Again, the issue here is that "subpublishing_agreements" is ambiguous. But let's consider it for a minute. If I go this route, then in the controller I need to handle the fact that the publisher might be the assignor or the assignee:

def get_publisher
  @publisher = Publisher.find(params.expect(:publisher_id))
end

def index
  @subpublishing_agreements = @publisher.subpublishing_agreements_as_assignor + @publisher.subpublishing_agreements_as_assignee
end

Well, that's one way to do it, but note that @subpublishing_agreements is an array in this case, not an ActiveRecord relation. There's no way to build on it, sort it, whatever you might want to do in the database. It's possible to do whatever you want in Ruby, but if you try to add .order(:starts_on) or something like that it'll fail.

You can make it into a regular relationship like this:

  @subpublishing_agreements = SubpublishingAgreement.where("? in (assignor_id, assignee_id)", @publisher.id)

That's possible, but really not normal.

Related to that:

  @subpublishing_agreements = SubpublishingAgreement.where(assignor_id: @publisher.id).or(SubpublishingAgreement.where(assignee_id: @publisher.id))

Technically, you can turn that into a single "where" and put your "or" inside the SQL snippet:

  @subpublishing_agreements = SubpublishingAgreement.where("assignor_id = :publisher_id or assignee_id = :publisher_id", publisher_id: @publisher.id)

Now, this works, kind of. When I create multi-tenanted applications, I always restrict my queries back to the main organization model - library in this case. I can still do that, just not in the way that I'd like. One great thing about this is that I have a relation that I can build on:

  @subpublishing_agreement = @subpublishing_agreements.find(params.expect(:subpublishing_agreement_id))

The other issue arises in displaying this data. Normally, I'd show this list of subpublishing agreements in a simple table, but there would be no need to put the parent datum in this table since it's the same for all and can be shown above. But in this scenario the parent datum may be in one of two different places. In that case, I can either create two tables - agreements as assignor and agreements as assignee, sort them based on this type, or at least put both data fields on each row.

To get back to our way of handling it, we change the routes to show which role the "publisher" in the path is taking on. This allows us to use some of the Rails tools at our disposal for creating routes, but it also breaks a lot of it.

A big problem that I face is that I want to be able to have the library at the front of the path. Each account may have multiple libraries, so that's important. But with the scopes in the routes as above, putting that code within a resources :libraries do...end block ends up with paths that we don't want:

publisher_library_subpublishing_agreements_as_assignee
/publishers/:assignee_id/libraries/:library_id/subpublishing_agreements_as_assignee/:id(.:format)

That's right - the library ends up after the scoping. Argh.

But, let's explore this, anyway.

When I create controllers like this, I like to use a helper that I wrote called filled_path. It allows me to pretty easily reuse a lot of code across different controller end points and at different paths.

For instance, just looking at this particular sort of data model as an administrator I might want to view all of the publishers or just the publishers for a particular library. It's also the case that an ordinary user may access their libraries in a similar manner, but in a path prefixed with "/my".

/admin/publishers/1
/admin/libraries/1/publishers/1
/my/libraries/1/publishers/1

In this case, there's no reason for a regular user to access a publisher outside of their library-specific path. But, for an administrator, that might be optional.

  namespace :my do
    resources :libraries do
      resources :publishers
    end
  end

  namespace :admin do
    resources :publishers
    resources :libraries do
      resources :publishers
    end
  end

This allows an administrator to access publishers either directly or under the libraries path. This works out well because Rails path helpers accept an array with the path items. For instance:

[:admin, @library, @publisher]
[:my, @library, @publisher]

The only difference in code is the first piece. There are a few tricks to know here. If you want to get the "edit" path, for instance, then the action must be prepended to the array:

[:edit, :my, @library, @publisher]

And if you want to see the subpublishing_agreements for a publisher, you would append the relevant path item:

[:my, @library, @publisher, :subpublishing_agreements]

My filled_path helper constructs those arrays, and you can use such an array for any path in Rails, including in links or in form actions. I'll talk a little more about filled_path later.

  form_for([:my, @library, @publisher]) do
    ...
  end

That works, until you break it with scopes.

The fundamental issue is that scopes will allow you to construct a path, but instead of an object for subpublishing_agreement you have to use the plain id. Let me show you a bit of the controller. Again, this is for administrative access so tying this to a particular library doesn't matter:

class Admin::SubpublishingAgreementsController < Admin::ApplicationController
  before_action :set_library
  before_action :set_assignor_or_assignee
  before_action :figure_out_path_filling
  before_action :set_collection
  before_action :set_subpublishing_agreement, only: %i[ show edit update destroy ]

...

  private
    # This sets @publisher as well as either @assignor or @assignee
    def set_assignor_or_assignee
      @publisher =
        if params[:assignor_id].present?
          @assignor = Publisher.find(params[:assignor_id])
        elsif params[:assignee_id].present?
          @assignee = Publisher.find(params[:assignee_id])
        end
    end

    def set_collection
      @collection =
        if @assignor.present?
          @assignor.subpublishing_agreements_as_assignor
        elsif @assignee.present?
          @assignee.subpublishing_agreements_as_assignee
        else
          SubpublishingAgreement
        end
    end

    def set_subpublishing_agreement
      @subpublishing_agreement = @collection.find(params.expect(:id))
    end

    def figure_out_path_filling
      if params[:assignor_id].present?
        @subpub_agreement_path_piece = :subpublishing_agreements_as_assignor
      elsif params[:assignee_id].present?
        @subpub_agreement_path_piece = :subpublishing_agreements_as_assignee
      else
        @subpub_agreement_path_piece = :subpublishing_agreements
      end
    end

    def sub_pub_path_helper(action, subpub_agreement=nil)
      if @publisher.present?
        general_path = [:admin, @publisher, @subpub_agreement_path_piece]
        case action.to_sym
        when :new
          polymorphic_path([:new] + general_path)
        when :edit
          polymorphic_path([:edit] + general_path, id: subpub_agreement.id)
        when :show, :update, :destroy
          polymorphic_path(general_path, id: subpub_agreement.id)
        when :index, :create
          polymorphic_path(general_path + [:index])
        else
          raise "Unknown action #{action}"
        end
      end
    end
end

What a mess. The real action here is in sub_pub_path_helper. Imagine that I have this path:

/admin/publishers/1/subpublishing_agreements_as_assignor/2

In this case, the params will have the assignor_id key. This means that @publisher and @assignor will be set to Publisher 1 and the SubpublishingAgreement will be 2. @subpub_agreement_path_piece will be set to :subpublishing_agreements_as_assignor.

Now, in sub_pub_path_helper the general_path will be:

[:admin, @publisher, subpublishing_agreements_as_assignor]

If you wish to create the "edit" path for this item, the helper will prepend :edit to the array, and also pass id: 2 to polymorphic_path. In the end, you'll get:

/admin/publishers/1/subpublishing_agreements_as_assignor/2/edit

That's a lot of work to basically add "/edit" to the path. The form looks like this:

form_with(model: subpublishing_agreement, url: sub_pub_path_helper(subpublishing_agreement.new_record? ? :create : :update, subpublishing_agreement)) do |form|

Yes, we have to decide which URL to provide based on the record status. That sub_pub_path_helper ends up everywhere. Simply put, every URL has to use that helper or include all of that logic. It's a bit of a mess. As stated earlier, it's also difficult if I want to include another resource (e.g. "library") at the front of the path.

Let's try something else.

Here's a piece of a route file to attempt another approach:

  namespace :admin do
    resources :libraries do
      resources :publishers

      resources :publishers, path: 'assignors', as: :assignors, only: [] do
        resources :subpublishing_agreements
      end

      resources :publishers, path: 'assignees', as: :assignees, only: [] do
        resources :subpublishing_agreements
      end
    end
  end

This has potential. Just showing the paths for the generic show method:

     admin_library_assignor_subpublishing_agreement GET    /admin/libraries/:library_id/assignors/:assignor_id/subpublishing_agreements/:id(.:format)        admin/subpublishing_agreements#show
     admin_library_assignee_subpublishing_agreement GET    /admin/libraries/:library_id/assignees/:assignee_id/subpublishing_agreements/:id(.:format)        admin/subpublishing_agreements#show
              admin_library_subpublishing_agreement GET    /admin/libraries/:library_id/subpublishing_agreements/:id(.:format)                               admin/subpublishing_agreements#show

There's some potential here. This allows us to make a path like this:

/admin/libraries/1/assignors/5/subpublishing_agreements/2

This would be Library 1, Publisher 5, and SubpublishingAgreement 2 with the assumption that Publisher 5 would be the assignor for that particular agreement.

But, how do we specify such a path using helpers?

It turns out that polymorphic_path can handle these with a little care. Take the above as an example:

  polymorphic_path([:admin, Library.find(1), :assignor, SubpublishingAgreement.find(2)], assignor_id: 5)

That's not optimal, but it's pretty easy to get it to work. This is my filled_path helper:

module FilledPathHelper
  def set_path_filling(*params)
    @path_filling = params.compact
  end

  def set_path_options(options = {})
    @path_options = options.compact
  end

  def final_filled_path(pre, post, **options)
    path = Array(pre || []) + (@path_filling || []) + Array(post || [])
    merged_options = (@path_options || {}).merge(options)
    merged_options.empty? ? path : polymorphic_path(path, merged_options)
  end

  def filled_path(*params, **options)
    normalize = ->(arg) {
      case arg
      when String then arg.to_sym
      when Array  then arg.map { |s| normalize.call(s) }
      else arg
      end
    }

    params = normalize.call(params)

    case params
    in [ Array ]
      final_filled_path(nil, params.flatten, **options)
    in [ ApplicationRecord, * ]
      final_filled_path(nil, params, **options)
    in [ Symbol => post ]
      final_filled_path(nil, post, **options)
    in [ Symbol => pre, *post ]
      final_filled_path(pre, post.flatten, **options)
    in []
      final_filled_path(nil, nil, **options)
    else
      raise ArgumentError, "Invalid arguments to filled_path"
    end
  end

  def filled_url(*params, **options)
    polymorphic_url(filled_path(*params), **options)
  end
end

Normally, you use it as such:

class Admin::BooksController < Admin::ApplicationController
  before_action :set_library
  before_action -> { set_path_filling :admin, @library }
  before_action :set_collection
  before_action :set_book, only: [ :show, :edit, :update, :destroy ]

  ....

  private

  def set_library
   @library = Library.find(params[:library_id])
  end

  def set_collection
    @collection = @library.books
  end

  def set_book
    @book = @collection.find(params[:id])
  end
end

This is assuming that a Book belongs to a Library. A typical URL might be:

/admin/libraries/2/books/5

To link to a particular book:

link_to @book.title, filled_path(@book)

Is the equivalent of:

link_to @book.title, [:admin, @library, @book]
# or
link_to @book, admin_library_book_path(@library, @book)

The nice thing about filled_path is that you can use your mostly same code in a different path by simply changing the initial path filling.

But it requires a little more fun when using it with these paths, as we have to get the assignor_id or assignee_id into the path. And to do that we have to also set some path options.

class Admin::SubpublishingAgreementsController < Admin::ApplicationController
  before_action :set_library
  before_action :set_publisher_context
  before_action :set_subpublishing_agreement, only: [:show, :edit, :update, :destroy]

  ....

  private

    def set_library
      @library = Library.find(params[:library_id])
    end

    def set_publisher_context
      if params[:assignor_id]
        @assignor = @library.publishers.find(params[:assignor_id])
        set_path_filling(:admin, @library, :assignor)
        set_path_options(assignor_id: @assignor.id)
      elsif params[:assignee_id]
        @assignee = @library.publishers.find(params[:assignee_id])
        set_path_filling(:admin, @library, :assignee)
        set_path_options(assignee_id: @assignee.id)
      end
    end

    def set_subpublishing_agreement
      @subpublishing_agreement =
        if @assignor
          @assignor.subpublishing_agreements_as_assignor.find(params[:id])
        else
          @assignee.subpublishing_agreements_as_assignee.find(params[:id])
        end
    end
end

With this in place, I can use filled_path to handle all path generation to automatically include the assignor or assignee id.

This allows me to create paths easily for the subpublishers functionality, and it's pretty easy to dip in from the publishers' "show" page:

  <%= link_to "Subpublishing Agreements as Assignor", polymorphic_path([:admin, @publisher.library, :assignor, :subpublishing_agreements], :assignor_id => @publisher.id) %> |
  <%= link_to "Subpublishing Agreements as Assignee", polymorphic_path([:admin, @publisher.library, :assignee, :subpublishing_agreements], :assignee_id => @publisher.id) %> |
  <%= link_to "Edit", filled_path(:edit, @publisher) %> |
  <%= link_to "Back to publishers", filled_path(:publishers) %>

The reason I go to these lengths is to keep the context. If I ask for the subpublishing agreements where a certain publisher is the assignor, I can see those agreements and see that the publisher in the path is the assignor. If I add an agreement, the assignor is already set, so I just have to choose an assignee on the form.

  <div class="row">
    <div class="col-md-6">
      <div class="mb-3">
        <%= form.label :assignor_id, class: "form-label" %>
        <% if @assignor.present? %>
          <%= form.text_field :assignor_id, value: @assignor.name, class: "form-control", disabled: true %>
        <% else -%>
          <%= form.collection_select :assignor_id, @library.publishers.order(:name), :id, :name, { prompt: true }, { class: "form-select" }  %>
        <% end -%>
      </div>
    </div>

    <div class="col-md-6">
      <div class="mb-3">
        <%= form.label :assignee_id, class: "form-label" %>
        <% if @assignee.present? %>
          <%= form.text_field :assignee_id, value: @assignee.name, class: "form-control", disabled: true %>
        <% else -%>
          <%= form.collection_select :assignee_id, @library.publishers.order(:name), :id, :name, { prompt: true }, { class: "form-select" }  %>
        <% end -%>
      </div>
    </div>
  </div>

This context allows you to put together user workflows that are intuitive:

Choose a publisher
View subpublishing agreements as the assignor
Create a new agreement
Automatically get redirected back to subpublishing agreements as assignor
Return to the publisher's main page

Wrap Up

This particular pattern isn't very common, but knowing how to put it together can definitely make an easy to maintain application that's also easy for your users.

Feel free to ask questions in the comments.

COBOL, Dates, May 20, 1875, and Disinformation

Michael Chaney — Wed, 19 Feb 2025 22:33:00 +0000

Let me start at the end for those who don't want to read the whole thing. COBOL does not have a "default date" or any kind of default value for a missing date. COBOL doesn't even have a "date" data type. ISO8601 is an interchange format for dates. "May 20, 1875" has no prominence in that standard or any other date standard.

That's the short version. How did we get here?

Elon Musk mentioned in a press briefing that there were many names in the Social Security database of people who weren't marked as dead but were 150 years old. Clearly, this is bad data. At the time, he provided no other information, so speculation abounded on the internet.

In the middle of all of it, this appeared on threads:

https://www.threads.net/@ashmore_glenn/post/DGDfmj6TsZS

It's interesting that he starts off making the claim that "in COBOL, if a date is missing the program defaults to 1875. e.g. 2025-1875=150". This claim isn't just false, it's false in multiple ways:

There is no "date" data type in COBOL. Dates are stored however the programmer wants, but usually numeric character strings
There's no "default" date, even if there were such a data type
Even if there were a default, 1875 would be a bizarre choice

His initial post has over 20,000 "likes" and numerous shares as of this writing. The followup - included in the picture above - has him backpedaling hard and pointing out that they were "taught to never leave a null date" and "I assume some group at IBM or elsewhere decided that May 20,1875, the date of the Convention on the Meter, would be the standard filler." That got 10 likes. Welcome to the internet.

So, later on in the same thread he gets a lot of push back from other programmers, and then we find this with 1000+ likes:

I'll put the whole thing here:

Correct. The original ISO standard for a default reference date was May 20, 1875 - the date the international standards and metrics treaty was signed. Geeky to be sure but that is coders for you. This standard was changed in 2019.

As you've probably guessed, that's not correct, either.

But this was added to the lore and we had some disinformation with running shoes on.

ISO8601

In the ISO8601:2004 standard, "May 20, 1875" is mentioned as a "reference date" in a section discussing the Gregorian calendar.

For those of you unaware, the Gregorian calendar is almost certainly the calendar that you're using. The "Julian calendar" is still used in some contexts in the west, mostly among Orthodox churches. You most likely see this as "Orthodox Christmas" and "Orthodox Easter" on your (Gregorian) calendar.

Without getting into all the specifics, Julius Caesar adopted what's now known as the "Julian calendar" in 46 BC. It has 365.25 days in a year, so every four years a leap year is added. That's pretty close.

Unfortunately, "pretty close" doesn't work over the course of centuries, and people determined that the real number of days in a year is slightly less than 365.25. So, in 1582 Pope Gregory XIII established a new calendar system that's pretty similar, but to get closer to the number of days in the year the leap year is skipped every hundred years, unless the year is a multiple of "400".

In order to "reset" the calendar, October 4, 1582 (Julian) was followed by October 15, 1582 (Gregorian), which was October 5, 1582 (Julian).

The Julian calendar will continue to drift apart from the Gregorian calendar. The starting offset in 1582 was 10 days. The Gregorian calendar skipped leap years in 1700, 1800, and 1900, but the Julian didn't skip those. As of 2025, there are 13 days between the two.

So, Christmas is December 25 in both calendar systems. But those aren't the same day. "December 25, 2024" in the Julian calendar is "January 7, 2025" Gregorian. That's why "Orthodox Christmas" is celebrated in what most of us call "January". Inside the Julian calendar, it's still December.

In the ISO8601 specification, 2004 edition, section 3.2.1 discusses the Gregorian calendar as it is considered the "standard" calendar and the one that ISO8601 assumes. In this discussion of the Gregorian calendar, it is mentioned:

The Gregorian calendar has a reference point that assigns 20 May 1875 to the calendar day that the "Convention du Mètre" was signed in Paris.

This is a statement about the Gregorian calendar, not ISO8601. It is saying that the date that the "Meter Convention" was signed in Paris is "May 20, 1875" on the Gregorian calendar, as an example.

Equally valid statements:

"The Julian calendar has a reference point that assigns 8 May 1875 to the calendar day that the 'Convention du Mètre' was signed in Paris."

Even more:

"The Gregorian calendar has a reference point that assigned 29 Aug 1958 to the calendar day that Michael Joseph Jackson was born."

That's not a statement about ISO8601 - it's a statement about a calendar system. It was removed from the 2019 update to ISO8601.

https://www.loc.gov/standards/datetime/iso-tc154-wg5_n0038_iso_wd_8601-1_2016-02-16.pdf

Due to what was shown on threads, this morphed into a full-scale disinformation campaign that usually disparaged Elon Musk or his "DOGE" team as hapless idiots who don't know about COBOL and thus don't know that "May 20, 1875 is the epoch in COBOL".

Ah, the epoch. What's an "epoch"?

Let's back up a bit. The ISO8601 standard for exact dates is pretty simple:

yyyy-mm-dd (dashes optional)

That's the entire standard for an exact date. Four-digit year, two-digit month, and two-digit day of month, each with leading zeroes as applicable. So, "May 20, 1875" is "1875-05-20". "May 19, 1875" is "1875-05-19". "October 15, 1582" is "1582-10-15". "October 10, 1582" - ha! fooled you there, that date technically doesn't exist. The Gregorian calendar didn't exist then, so we use the "proleptic" version which just numbers the days as if they did exist. So, "October 10, 1582" Gregorian would be "September 30, 1582" Julian, and we would still use "1582-10-10". Anyway....

I use ISO8601 all the time. It's the format used by HTML form fields for dates and times. It's a date/time interchange format, and it's useful because a date/time value includes all information to accurately specify the exact date and time of an event, even if someone in a different time zone consumes the data being produced or if daylight savings time is different. It's also great for dates as they can be sorted easily.

Epochs

An "epoch" in computer lore is basically when a system of timekeeping starts. Year "1" is the first year in our Gregorian calendar system. Times before that are just "BC". There is no year "0", which is a bit odd, but exact dates that long ago typically don't matter.

In the Unix operating system, the "epoch" is January 1, 1970 at 12:00:00 AM. Later, that was determined to be "UTC". A standard Unix timestamp is 32 bits, but it's considered to be a "signed" number so 31 bits are accessible. That means that we can count up to 2,147,483,647 seconds past January 1, 1970 in this system, which puts us at 2038 when the number "overflows" and resets to a negative value.

Put another way, with this system we can specify a date and time within about 68 years either way from January 1, 1970. Here, I show what this means using the Ruby programming language:

3.3.6 :001 > Time.at(0).utc
 => 1970-01-01 00:00:00 UTC
3.3.6 :002 > Time.at(2**31-1).utc
 => 2038-01-19 03:14:07 UTC
3.3.6 :003 > Time.at(-2**31).utc
 => 1901-12-13 20:45:52 UTC

As I type this, we are at 1,740,000,100 seconds since the epoch.

The system that I just described has a couple of features that we can see as relevant:

It only works within a fairly narrow range of years
A timestamp of "0" will be obvious since it's always the same value

When I'm working with systems and I see the date/time is "January 1, 1970 12:00:00am UTC" or "December 31, 1969 6:00:00pm CST" I know right away that I have a timestamp that is 0. This is a pretty common bug form - we forget to initialize a value, it ends up being 0, so the date that shows up is January 1, 1970 or December 31, 1969.

There are other epochs. A bunch of others. One example is a timestamp for a file in MS-DOS, which starts in 1980.

Now, in ISO8601 the specification for an exact date is just the four-digit year, two-digit month, and two-digit day of month. There's no "epoch" other than, basically, year "1" or something like that. In this system, May 20, 1875 has no significance. That's why the line was removed from the standard in the 2019 edition.

Disinformation Multiplies

Musk countered the disinformation by showing the counts by age bracket:

https://x.com/elonmusk/status/1891350795452654076

By this point, the disinformation was "if Musk and company were competent they would have noticed the huge spike at 150 years old and known that was important." As the data shows, there is no spike at 150 but rather the downward slope past 70 that one would expect with this data. And it ends not long after 150, not surprising since those are the first Social Security recipients. There are a couple of outliers on there as well.

It was too late to stop the disinformation campaign. It was repeated by Rachel Maddow on MSNBC, and shows up in other places. For instance, it's repeated by this "Politifact" "fact-check":

https://www.politifact.com/article/2025/feb/17/are-150-year-old-americans-receiving-social-securi/

This guy repeated it on X and got quite a few views:

https://x.com/toshiHQ/status/1889928670887739902

It showed up in this Wired article:

https://t.co/79FN6dSvBO

Note that the guy who wrote this - David Gilbert - supposedly covers "disinformation". Yeah, he covered it. By repeating it.

Here it shows up in a yahoo!news article:

https://www.yahoo.com/news/trump-press-secretary-hit-embarrassing-191941730.html

And this article from someone on Daily Kos:

https://www.dailykos.com/stories/2025/2/14/2303889/-Nope-There-are-no-150-year-olds-on-Social-Security-It-s-COBOL

This disinformation is showing up almost exclusively on left-wing media and "fact checkers", and usually includes digs at Elon Musk and the "young" DOGE employees.

COBOL

So, how does COBOL store dates? That's up to the programmer, and I'm sure there are many, many different ways that dates have been stored.

I pulled out my "Structured COBOL, Pseudocode Edition" by Shelly, Cashman, and Forsythe, 1986 edition. ISBN 0-87835-196-5. This is pre-Y2K, and it's mind-blowing to me that they weren't planning for Y2K yet.

On page 6.32, "Defining the Date Work Area":

On most computers, the current date is stored in computer memory as a 6 character field (Year, Month, Day). In the sample program, the date, which will be printed in the report heading, is obtained from computer memory and is placed in a work area within the Working Storage Section of the Data Division. The work area and the method used in the Procedure Division of the sample program to place the date in the work area are shown in Figure 6-48.

In Figure 6-48, the DATE-WORK field is defined on line 006080. The DATE-WORK field is then subdivided into three 2-digit numeric fields - the YEAR-WORK field, the MONTH-WORK field, and the DAY-WORK field. The date is six characters in length and is stored in YYMMDD format (i.e. - January 25, 1987 would be stored as 870125).

The date is obtained from computer memory using the Accept statement. When the Accept statement is executed, the reserved work DATE identifies that the current date is to be copied from the area in main computer memory where it is stored by the operating system to the field DATE-WORK which has been defined in the program.

The Accept statement can also be used to retrieve the day of the year and the time of day. The day of the year is returned in a Julian date format. The first two numeric characters are the year and the next three numeric characters are the day of the year. Thus, the value returned for January 25, 1987 would be 87025. The time is returned as a two-digit numeric hour, a two-digit numeric minute, and a two-digit numeric second, and a two-digit numeric hundredths of a second. Thus, the time 9:15 a.m. would be returned as 09150000.

Figure 6-48, Data Division:

006070 01  WORK-AREAS
006080     05  DATE-WORK
006090         10  YEAR-WORK       PIC 99
006100         10  MONTH-WORK      PIC 99
006110         10  DAY-WORK        PIC 99

Procedure Division:

014070     ACCEPT DATE-WORK FROM DATE
014080     MOVE MONTH-WORK TO MONTH-HEADING
014090     MOVE DAY-WORK TO DAY-HEADING
014100     MOVE YEAR-WORK TO YEAR-HEADING

For those of you wondering how we had the Y2K bug, there it is in all its glory. This was written 14 years before 2000, and in the world of COBOL nobody apparently thought to point out that this was going to cause problems very, very soon.

The part of the book shown above is the entire treatise on handling dates (times aren't even mentioned except in that section) out of hundreds of pages. They show two possible date formats: "YYMMDD" or "YYDDD". COBOL generally stored data like this as characters, so the standard date format would be six separate characters.

Contrast that with a Unix timestamp which is a binary format that uses the space of four characters total and is able to specify more than 130 years of dates and times. The YYMMDD format can specify only 100 years of dates, and uses six character spaces. In modern terms, that's 48 bits to store around 36,525 days, which handily fits into 16 bits.

But the nice thing about YYMMDD is that there is no endianness to worry about, no epoch, no real calculation to determine the date. Just add a couple more Ys and you have ISO8601. That's why it's a great interchange format.

Summary

But, "May 20, 1875" has nothing to do with ISO8601, COBOL, or basically anything else other than the day that the Meter Convention was signed in Paris.

It was interesting to see a disinformation campaign take off like this, and I think at this point those involved have moved on to something else. But this will live on because of the internet and all the newly-minted COBOL date experts expounding on how Musk and company don't know something that's just so obvious. Sigh.

Never change, internet. Never change.

Understanding Hexadecimal Numbers

Michael Chaney — Thu, 30 Jan 2025 21:24:20 +0000

I'm going old-school here, but some of these basics are overlooked nowadays. This is not a post for my Ruby on Rails friends, but rather for folks who may be learning low-level programming.

Why Hex?

Hexadecimal ("hex" from now on) is just a shorthand binary notation. We don't use normal decimal numbers because they don't line up with their binary equivalents.

All modern computers have a word size that is 8 times a power of 2. Simply stated, it will be 8, 16, 32, or 64 bits. There have been computers with larger word sizes, and there have been computers with weird word sizes.

The DEC PDP-6 was a 36-bit computer. I have worked on a CDC 830 which used a 60-bit word size. Each of these had the capability of storing 6-bit characters in their words.

There's something interesting about those word sizes - they also have "3" as a factor.

Back when I was a kid, octal was more popular than hex when handling binary data. For a 36-bit computer, each word is 12 octal digits and those line up nicely with the bits. Things get messy when dealing with modern word sizes and octal. Why?

Consider what "all bits on" looks like:

Binary: 1111 1111
Octal: 377
Binary: 1111 1111 1111 1111
Octal: 177777
Binary: 1111 1111 1111 1111 1111 1111 1111 1111
Octal: 37777777777

Hopefully you can see the issue here. It's not obvious what octal digits line up to what binary digits.

You'll notice that I spaced out the bits into groups of four bits each. 8 bits is a byte, and 4 bits is a nybble (pronounced as "nibble"). This is common in any literature to make things more readable. And, luckily, each nybble corresponds to a hex digit.

Hex to the Rescue

Hexadecimal is just a base-16 number system. We have our normal 10 digits in base 10 - 0 through 9 - so for hex we just add a-f to represent the numbers 10-15 in decimal. Hex is really easy:

Decimal	Hex	Binary
0	0	0000
1	1	0001
2	2	0010
3	3	0011
4	4	0100
5	5	0101
6	6	0110
7	7	0111
8	8	1000
9	9	1001
10	A	1010
11	B	1011
12	C	1100
13	D	1101
14	E	1110
15	F	1111

It's identical to decimal until we get to "10", and we start using those letters. "F" is all four bits on, "0" is all four bits off.

So, again, hex is just a way to write binary that's shorter and often more readable.

Memorizing Bit Patterns

You can think of each hex digit as the corresponding bit pattern, and if you're going to do any low-level programming you really want to be able to have the above table memorized. Here's how I did it.

Comments	Binary	Hex	Decimal
No bits	0000	0	0
All bits	1111	F	15
One bit	0001	1	1
	0010	2	2
	0100	4	4
	1000	8	8
Two bits - outer	1001	9	9
Two bits - inner	0110	6	6
Two bits - ends	1100	C	12
	0011	3	3
Two bits - spaced	1010	A	10
	0101	5	5
Three bits - ends	1110	E	14
	0111	7	7
Three bits - space	1011	B	11
	1101	D	13

That's it. Remember those patterns and you'll be able to "see" the bits and vice versa.

Advanced Hex

One other thing - now and then you need to know certain numbers in decimal. You should have powers of two memorized up to 65,536, along with the corresponding 2^x-1. You should also know the factors of 16 up to 16*16. For instance, if I see "C0" I know that's 192 decimal because "12 * 16" is 192 in decimal.

This isn't for everybody. If you're a web programmer it's unlikely that you'll need to care about any of this.

On the other hand, if you're reading spec sheets for a piece of hardware, you'll need every bit of this.

Enjoy, and ask questions below.

Matching Fuzzy Data

Michael Chaney — Mon, 27 Jan 2025 21:55:21 +0000

I do a lot of work in the music industry. One thing about the music industry - and really every industry - is that there's a lot of junk data out there.

Now, there are various ways that this manifests in the music industry. The obvious one is what I'll cover today - composition titles and associated filenames. Then we have composer names. And publisher names. This isn't even getting into the realm of metadata.

There are lots of places where we need to match up pieces of data that might not match with a simple string compare. There are also issues of missing data - audio files might not be present or lines may be missing from the sheet.

Managing and ingesting data of this kind requires fuzzy matching as part of a process that alerts the user to all of the ways that the data is problematic.

In Ruby, I use a gem called "fuzzy-string-match" which implements what's known as the Jaro-Winkler algorithm. (An alternative is the Levenshtein distance algorithm.)

As I said before, I'll cover the concept of composition titles and filenames. It's very common that I'll receive a group of files along with a spreadsheet of metadata. The trick is to match the lines in the spreadsheet with the associated file path.

Right now, I'm dealing with a dataset where the file path looks like this:

country/{sometimes genre/}album/filename.[mp3|wav]

It turns out that the "genre" - if present - may or may not match what's in the spreadsheet, so it'll have to be ignored.

The album column contains an "album number" consisting of a few letters to identify the publisher along with a serial number plus the title, so it's guaranteed to have some uniqueness.

The filename, though. May God help me. The filename might be any number of things:

The composition title
The album number/name plus the composition title
The album number/name, the track number, and the composition title
The track number and composition title
(I'm not making this up) The album number/name, the track number, the track number again, and the composition title

For #5, it looks like the composition title came from somewhere that had the track number already tacked on. This belief is supported by some of those also have an extension of ".mp3.mp3".

I would also point out that the various components of this filename have delimiters between them, typically some combination of dashes, spaces, and underscores.

I'm not going to embarrass my client if for no other reason than their data is normal. It's no worse than any number of other datasets that I've encountered. So consider this filepath which has some stuff changed:

Canada/ZZZ 001_Very Nice Music Vol. 1/ZZZ 001_Very Nice Music Vol. 1 - 10 - 10_Fighter.mp3.mp3

So,
country: Canada
album: ZZZ 001_Very Nice Music Vol. 1
track number: 10
composition title: Fighter

Here's another one:
Mexico/Rock/YYY 001_Rock Vol. 1/YYY 001_Rock_9_9_ Love You Much.mp3

Note that in this one the album designation is missing "Vol. 1" in the filename. Plus we have an extraneous space. What can you trust here?

If you're thinking I should just dig out the album name and track number - good idea. Except, records still might not match up. It's important to me to have a bit of a double-check to make sure the titles are correct(ish) as well.

The general way to accomplish this:

Create likely possible file paths for each row in the spreadsheet.
Use the fuzzy matcher to determine if it's really close to one of the file paths
If so, match it up and take that file path out of the pool
Output a new sheet with a new "full file path" column
Output separately a list of unmatched file paths

In this list, the second item is the meat of the algorithm. It's also where we can get the most gains from a correct implementation.

The Jaro-Winkler Algorithm

Let's talk about the Jaro-Winkler algorithm. To make it easy, let's look at examples using irb.

The implementation of the Jaro-Winkler algorithm in Ruby is a simple function call.

$ irb
3.3.6 :001 > require 'fuzzystringmatch'
 => true
3.3.6 :002 > fuzzy = FuzzyStringMatch::JaroWinkler.create(:pure)
 => #<FuzzyStringMatch::JaroWinklerPure:0x000000011d0c0120>
3.3.6 :003 > fuzzy.getDistance('string 1', 'string 2')
 => 0.975
3.3.6 :004 > fuzzy.getDistance('the same thing', 'the same thing')
 => 1.0
3.3.6 :005 > fuzzy.getDistance('a string like this', 'something different')
 => 0.5467836257309941

You can see here that the value "1.0" means equality, and that number diminishes to "0.0" as the strings diverge. I would note that the linked article shows the opposite, which makes sense given that it's a "distance", but as long as it's consistent we can work with whatever we get.

3.3.6 :006 > fuzzy.getDistance('monday', 'MONDAY')
 => 0.0

Ack. It's case-sensitive.

3.3.6 :007 > fuzzy.getDistance('a new day', 'a_new_day')
 => 0.8666666666666666
3.3.6 :008 > fuzzy.getDistance('a new day', 'a  new  day')
 => 0.8898071625344353
3.3.6 :009 > fuzzy.getDistance('a new day', 'a   new   day')
 => 0.8505369274600044

It cares about whitespace and non-alphanumeric characters as well. But there's something to be noticed here - adding more spaces doesn't make the score drop by much.

Just from what I've shown here, we can come up with a few ideas to make the filename matches more likely:

Remove all file extensions from the filenames, including duplicate extensions
Force the file path to lowercase
Remove all non-alphanumeric characters
Compress multiple spaces down to a single space

Let's then consider this file path:

Mexico/Rock/YYY 001_Rock Vol. 1/YYY 001_Rock_9_9_ Love You Much.mp3

First, I can cut it up into four pieces:

Mexico
Rock
YYY 001_Rock Vol. 1
YYY 001_Rock_9_9_ Love You Much.mp3

I'll ignore the genre. Let's munge the album name:

yyy 001 rock vol 1

And let's do the same for the filename, removing the extension as well:

yyy 001 rock 9 9 love you much

Now, the row in the spreadsheet will show that the album name is:

YYY 001_Rock Vol. 1

That'll then turn into the same thing we see above:

yyy 001 rock vol 1

There are two issues in the filename. First, the "Vol. 1" is missing from the album designation. Second, the track number is duplicated.

Let's see how those things affect the scoring. The ideal filename from the data would be YYY 001_Rock Vol. 1_9_Love You Much.mp3 given the established pattern. Let's munge that (yyy 001 rock vol 1 9 love you much) and compare:

3.3.6 :010 > fuzzy.getDistance('yyy 001 rock vol 1 9 love you much', 'yyy 001 rock 9 9 love you much')
 => 0.9202640894085828

So, it comes in at 0.92, which is close. Is it close enough?

A naive algorithm might have a cutoff value that is considered "close enough". But if we do a complete cartesian product and sort by the fuzzy distance (descending) we'll get all of the best matches at the top. This can be an expensive operation, and it might make sense to look for exact matches before running the fuzzy matcher. I won't optimize it here, but you get the idea.

Munging Strings For Better Matching

Taking our example, we can run a set of mungers against the two strings, compare all possible pairs, and extract the highest score as the official score for those two strings. Let's look at how to do this in Ruby.

First, what are our "two strings". On one side, we have the file path. On the other side, we have the expected file path that will be generated from the spreadsheet data. In reality, there will be a few different "expected" file paths since there are a few ways those are generated.

Let's create a simple set of mungers. We will munge the country, album name, and filename items separately. All will go through the basic munging:

Force to lowercase
Change all non-alphanumeric characters to spaces
Collapse multiple contiguous spaces to one single space

Filenames will have to be treated differently. Just looking at the examples shown:

Remove "vol x", where "x" is a number
Change " x x " to " x " - in other words, if a number is duplicated remove one of them.

There's a potential pitfall here. If we have "vol. 1" and track "1" there'll likely be a duplicated number. For those mungers, though, we'll try all combinations - including none, so the original filename will still be in the list.

Let's start with our simple mungers (I'm going to skip the new Ruby syntaxes to make this work back into the Ruby 2 series):

downcase_munger = lambda { |x| x.downcase }
alphanumeric_filter_munger = lambda { |x| x.gsub(/\P{Alnum}/, ' ') }
spaces_munger = lambda { |x| x.gsub(/ +/, ' ').strip }

And our specific filename mungers:

remove_vol_munger = lambda { |x| x.sub(/ vol \d+ /, ' ') }
remove_dup_track_number_munger = lambda { |x| x.sub(/ (\d+) (\1) /, ' \\1 ') }

Finally, we also want to remove the extensions from the filenames, knowing that they might be duplicated (or replicated):

remove_ext_munger = lambda { |x| x.sub(/(\.(mp3|wav))+$/i, '') }

We're going to use a fun trick to apply every combination of mungers to our strings. Note that downcase_munger, alphanumeric_filter_munger, and spaces_munger are always applied to our strings at the start, with spaces_munger applied again at the end. But our other two mungers for filenames need to be applied in all combinations. Since there are only two, this is very easy: none, both, and each individually. I have code that uses up to seven mungers, so generalizing this makes sense.

Ruby has an awesomely simple way to do this with the combination method for arrays:

3.3.6 :001 > [1,2,3,4].combination(3).to_a
 => [[1, 2, 3], [1, 2, 4], [1, 3, 4], [2, 3, 4]]
3.3.6 :002 > [1,2,3,4].combination(2).to_a
 => [[1, 2], [1, 3], [1, 4], [2, 3], [2, 4], [3, 4]]
3.3.6 :003 > [1,2].combination(2).to_a
 => [[1, 2]]
3.3.6 :004 > [1,2].combination(1).to_a
 => [[1], [2]]

As you can see, given an array it returns all possible combinations of "n" elements.

What if those elements are mungers?

In Ruby, starting in version 2.5 of the language, it's possible to easily compose functions using the ">>" or "<<" operator.

As an aside, I'm going to assume that my mungers can be called in any order, meaning we only need to worry about "combinations" and not "permutations". In reality order does matter, but it doesn't matter enough for me to bother with it.

Here are our steps so far:

For the spreadsheet: come up with a list of all expected file paths given the country, album name, track number, and composition title.
For the file paths: remove the genre if applicable, apply the general mungers to the country and album name, then run all mungers against the filename. Reassemble the paths.
Run the cartesian product of expected file paths and file paths. Since there will be multiple items for each spreadsheet line and each file path, we'll again run a cartesian product and take only the closest match.

We will create an array that has these elements:

spreadsheet line
full file path
Jaro-Winkler score

Scores below a certain threshold will have to be ignored. We'll figure that out later.

The General Algorithm

So, let's talk about the spreadsheet lines. Here is the data for the file paths that we've referenced above:

country	album	track_number	title
Canada	ZZZ 001_Very Nice Music Vol. 1	10	Fighter
Mexico	YYY 001_Rock Vol. 1	9	Love You Much

Here are all of our combinations of these data items:

title - "%s"
The album plus the title - "%s - %s"
The album, track_number, and title - "%s - %d - %s"
The track_number and title - "%d - %s"

I can ignore the duplicated track number as the mungers will handle that. Let's see how this works.

downcase_munger = lambda { |x| x.downcase }
alphanumeric_filter_munger = lambda { |x| x.gsub(/\P{Alnum}/, ' ') }
spaces_munger = lambda { |x| x.gsub(/ +/, ' ').strip }
general_munger = downcase_munger >> alphanumeric_filter_munger >> spaces_munger

filename_patterns = [
  "%<title>s",
  "%<album>s - %<title>s",
  "%<album>s - %<track_number>d - %<title>s",
  "%<track_number>d - %<title>s"
]
# Note that the keys need to be symbols
csv_rows = [
  { country: "Canada", album: "ZZZ 001_Very Nice Music Vol. 1",
    track_number: 10, title: "Fighter"
  },
  { country: "Mexico", album: "YYY 001_Rock Vol. 1",
    track_number: 9, title: "Love You Much"
  }
]

With all of that in place, we can get a group of possible (expected) filenames for each csv row:

add_possible_filenames = lambda do |csv_rows|
  csv_rows.each do |csv_row|
    country = general_munger.call csv_row[:country]
    album = general_munger.call csv_row[:album]
    path_prefix = "#{country}/#{album}/"
    csv_row[:possible_filenames] = filename_patterns.map { |pattern|
      general_munger.call sprintf(pattern, csv_row)
    }.map { |filename| path_prefix + filename }
  end
end

3.3.6 :067 > add_possible_filenames.call csv_row
 =>
[{:country=>"Canada",
  :album=>"001_Very Nice Music Vol. 1",
  :track_number=>10,
  :title=>"Fighter",
  :possible_filenames=>
   ["canada/zzz 001 very nice music vol 1/fighter",
    "canada/zzz 001 very nice music vol 1/zzz 001 very nice music vol 1 fighter",
    "canada/zzz 001 very nice music vol 1/zzz 001 very nice music vol 1 10 fighter",
    "canada/zzz 001 very nice music vol 1/10 fighter"]},
 {:country=>"Mexico",
  :album=>"YYY 001_Rock Vol. 1",
  :track_number=>9,
  :title=>"Love You Much",
  :possible_filenames=>
   ["mexico/yyy 001 rock vol 1/love you much",
    "mexico/yyy 001 rock vol 1/yyy 001 rock vol 1 love you much",
    "mexico/yyy 001 rock vol 1/yyy 001 rock vol 1 9 love you much",
    "mexico/yyy 001 rock vol 1/9 love you much"]}]

I placed the possible filenames in the csv_rows under "possible_filenames", and they're already generally munged.

Now, let's look at the filepaths:

filename_list = [
  "Mexico/Rock/YYY 001_Rock Vol. 1/YYY 001_Rock_9_9_ Love You Much.mp3",
  "Canada/ZZZ 001_Very Nice Music Vol. 1/ZZZ 001_Very Nice Music Vol. 1 - 10 - 10_Fighter.mp3.mp3"
]
filename_mungers = [ remove_vol_munger, remove_dup_track_number_munger ]

apply_filename_mungers = lambda do |filename|
  munged_filenames = [ filename ] +
  1.upto(filename_mungers.length).map do |i|
    filename_mungers.combination(i).to_a.map do |munger_combo|
      munger_combo.inject(:>>).call(filename)
    end
  end.flatten
  munged_filenames.map { |x| spaces_munger.call x }.sort.uniq
end

create_filename_lists = lambda do |filename_list|
  filename_list.map do |filepath|
    path_pieces = filepath.split("/")
    path_pieces.delete_at(1) if path_pieces.size == 4
    country = general_munger.call path_pieces[0]
    album = general_munger.call path_pieces[1]
    filename = (remove_ext_munger >> general_munger).call path_pieces[2]
    filenames = apply_filename_mungers.call filename
    [ filepath, filenames.map { |fn| "#{country}/#{album}/#{fn}" } ]
  end
end

Now, to apply this:

3.3.6 :075 > file_path_variations = create_filename_lists.call filename_list
 =>
[["Mexico/Rock/YYY 001_Rock Vol. 1/YYY 001_Rock_9_9_ Love You Much.mp3",
  ["mexico/yyy 001 rock vol 1/yyy 001 rock 9 9 love you much",
   "mexico/yyy 001 rock vol 1/yyy 001 rock 9 love you much"]],
 ["Canada/ZZZ 001_Very Nice Music Vol. 1/ZZZ 001_Very Nice Music Vol. 1 - 10 - 10_Fighter.mp3.mp3",
  ["canada/zzz 001 very nice music vol 1/zzz 001 very nice music 10 10 fighter",
   "canada/zzz 001 very nice music vol 1/zzz 001 very nice music 10 fighter",
   "canada/zzz 001 very nice music vol 1/zzz 001 very nice music vol 1 10 10 fighter",
   "canada/zzz 001 very nice music vol 1/zzz 001 very nice music vol 1 10 fighter"]]]

Here we have an array where the first element is the file path on disk and the second element is a subarray with a few different variations, based on the mungers. Again, there may be any number of mungers depending on the data.

At this point, we want to combine everything. For each spreadsheet row and each file path we want to get the Jaro-Winkler distance between all variations and capture the closest (highest, in our case).

If you're using a language other than Ruby or JavaScript you might want to have a slightly different data structure than what I'm doing here. These languages use references, meaning 1) data isn't copied and 2) equality can be guaranteed by checking the actual object reference.

However, note that this will grow exponentially as the data set grows, so it works great for hundreds and maybe thousands of records. Beyond that you'll likely need to start working on optimization.

compute_cartesian_product_array = lambda do |csv_rows, file_path_variations|
  csv_rows.map do |csv_row|
    file_path_variations.map do |filepath, variations|
      closest = csv_row[:possible_filenames].map do |possible_filename|
        variations.map do |variation|
          fuzzy.getDistance(possible_filename, variation)
        end
      end.flatten.max
      [csv_row, filepath, closest]
    end
  end.flatten(1)
end

Just with two items we have a four element array:

[[{:country=>"Canada",
   :album=>"ZZZ 001_Very Nice Music Vol. 1",
   :track_number=>10,
   :title=>"Fighter",
   :possible_filenames=>
    ["canada/zzz 001 very nice music vol 1/fighter",
     "canada/zzz 001 very nice music vol 1/zzz 001 very nice music vol 1 fighter",
     "canada/zzz 001 very nice music vol 1/zzz 001 very nice music vol 1 10 fighter",
     "canada/zzz 001 very nice music vol 1/10 fighter"]},
  "Mexico/Rock/YYY 001_Rock Vol. 1/YYY 001_Rock_9_9_ Love You Much.mp3",
  0.6296653796653796],
 [{:country=>"Canada",
   :album=>"ZZZ 001_Very Nice Music Vol. 1",
   :track_number=>10,
   :title=>"Fighter",
   :possible_filenames=>
    ["canada/zzz 001 very nice music vol 1/fighter",
     "canada/zzz 001 very nice music vol 1/zzz 001 very nice music vol 1 fighter",
     "canada/zzz 001 very nice music vol 1/zzz 001 very nice music vol 1 10 fighter",
     "canada/zzz 001 very nice music vol 1/10 fighter"]},
  "Canada/ZZZ 001_Very Nice Music Vol. 1/ZZZ 001_Very Nice Music Vol. 1 - 10 - 10_Fighter.mp3.mp3",
  1.0],
 [{:country=>"Mexico",
   :album=>"YYY 001_Rock Vol. 1",
   :track_number=>9,
   :title=>"Love You Much",
   :possible_filenames=>
    ["mexico/yyy 001 rock vol 1/love you much",
     "mexico/yyy 001 rock vol 1/yyy 001 rock vol 1 love you much",
     "mexico/yyy 001 rock vol 1/yyy 001 rock vol 1 9 love you much",
     "mexico/yyy 001 rock vol 1/9 love you much"]},
  "Mexico/Rock/YYY 001_Rock Vol. 1/YYY 001_Rock_9_9_ Love You Much.mp3",
  0.9804808984852584],
 [{:country=>"Mexico",
   :album=>"YYY 001_Rock Vol. 1",
   :track_number=>9,
   :title=>"Love You Much",
   :possible_filenames=>
    ["mexico/yyy 001 rock vol 1/love you much",
     "mexico/yyy 001 rock vol 1/yyy 001 rock vol 1 love you much",
     "mexico/yyy 001 rock vol 1/yyy 001 rock vol 1 9 love you much",
     "mexico/yyy 001 rock vol 1/9 love you much"]},
  "Canada/ZZZ 001_Very Nice Music Vol. 1/ZZZ 001_Very Nice Music Vol. 1 - 10 - 10_Fighter.mp3.mp3",
  0.6403194506642782]]

Now, we can process this array to get the likely matches, starting with the most likely and descending. At each step, we will remove "used" items from the cartesian_product_array - based on both the "csv_row" and the "filepath". The array will quickly shrink as this action is destructive. Again, this is unoptimized and there are many obvious optimizations to speed this up.

As I've written this, everything will be "consumed". What that means is that when we finish whichever set is smaller (csv rows or file paths) will have a match for every element. Some of those matches may be incorrect simply because there was no match and it grabbed the closest. You can see above that absolutely wrong items still scored above 0.6. We probably need to program in a cutoff of .8 or .9, which we can work on next.

The Naive Algorithm

Let's do the naive algorithm first.

cartesian_product_array.sort_by!(&:last)

This will put the most likely matches at the end. We can grab the last element in this array and assume it's a match.

3.3.6 :313 > cartesian_product_array.last
 =>
[{:country=>"Canada",
  :album=>"ZZZ 001_Very Nice Music Vol. 1",
  :track_number=>10,
  :title=>"Fighter",
  :possible_filenames=>
   ["canada/zzz 001 very nice music vol 1/fighter",
    "canada/zzz 001 very nice music vol 1/zzz 001 very nice music vol 1 fighter",
    "canada/zzz 001 very nice music vol 1/zzz 001 very nice music vol 1 10 fighter",
    "canada/zzz 001 very nice music vol 1/10 fighter"]},
 "Canada/ZZZ 001_Very Nice Music Vol. 1/ZZZ 001_Very Nice Music Vol. 1 - 10 - 10_Fighter.mp3.mp3",
 1.0]

That is a nice match - the score is "1.0" meaning it was an exact match. We need to remove every instance of this csv_row and every instance of this file path from our array.

3.3.6 :317 > current_match = cartesian_product_array.last
3.3.6 :318 > cartesian_product_array.reject! { |row|
3.3.6 :319 >   row[0] == current_match[0] || row[1] == current_match[1]
3.3.6 :320 > }
 =>
[[{:country=>"Mexico",
   :album=>"YYY 001_Rock Vol. 1",
   :track_number=>9,
   :title=>"Love You Much",
   :possible_filenames=>
    ["mexico/yyy 001 rock vol 1/love you much",
     "mexico/yyy 001 rock vol 1/yyy 001 rock vol 1 love you much",
     "mexico/yyy 001 rock vol 1/yyy 001 rock vol 1 9 love you much",
     "mexico/yyy 001 rock vol 1/9 love you much"]},
  "Mexico/Rock/YYY 001_Rock Vol. 1/YYY 001_Rock_9_9_ Love You Much.mp3",
  0.9804808984852584]]

That knocked out three lines! This algorithm continues to speed up as it goes along. At this point, we save the current_match and do this again, and we'll have both matches.

3.3.6 :321 > current_match = cartesian_product_array.last
 =>
[{:country=>"Mexico",
...
3.3.6 :322 > cartesian_product_array.reject! { |row|
3.3.6 :323 >   row[0] == current_match[0] || row[1] == current_match[1]
3.3.6 :324 > }
 => []

Let's turn that into a function. I'll just add the matched file path to the csv_row.

add_file_path_to_csv_rows = lambda do |cartesian_product_array|
  cartesian_product_array.sort_by!(&:last)
  while cartesian_product_array.any?
    current_match = cartesian_product_array.last
    cartesian_product_array.reject! { |row|
      row[0] == current_match[0] || row[1] == current_match[1]
    }
    current_match[0][:matched_file_path] = current_match[1]
  end
end

Run that, and it'll add the "matched_file_path" to the csv_rows:

[{:country=>"Canada",
  :album=>"ZZZ 001_Very Nice Music Vol. 1",
  :track_number=>10,
  :title=>"Fighter",
  :possible_filenames=>
   ["canada/zzz 001 very nice music vol 1/fighter",
    "canada/zzz 001 very nice music vol 1/zzz 001 very nice music vol 1 fighter",
    "canada/zzz 001 very nice music vol 1/zzz 001 very nice music vol 1 10 fighter",
    "canada/zzz 001 very nice music vol 1/10 fighter"],
  :matched_file_path=>
   "Canada/ZZZ 001_Very Nice Music Vol. 1/ZZZ 001_Very Nice Music Vol. 1 - 10 - 10_Fighter.mp3.mp3"},
 {:country=>"Mexico",
  :album=>"YYY 001_Rock Vol. 1",
  :track_number=>9,
  :title=>"Love You Much",
  :possible_filenames=>
   ["mexico/yyy 001 rock vol 1/love you much",
    "mexico/yyy 001 rock vol 1/yyy 001 rock vol 1 love you much",
    "mexico/yyy 001 rock vol 1/yyy 001 rock vol 1 9 love you much",
    "mexico/yyy 001 rock vol 1/9 love you much"],
  :matched_file_path=>
   "Mexico/Rock/YYY 001_Rock Vol. 1/YYY 001_Rock_9_9_ Love You Much.mp3"}]

More Complicated Data

Okay, let's be frank. That example is a little sterile given that we knew up front that our two files would match the two lines in the sheet. Real life is never so nice. I mean, it just isn't. So given that, how can we make this work when stuff doesn't match? Let's add some crap data and see what happens.

Let's add an extraneous file path first:

filename_list = [
  "Mexico/Rock/YYY 001_Rock Vol. 1/YYY 001_Rock_9_9_ Love You Much.mp3",
  "Canada/ZZZ 001_Very Nice Music Vol. 1/ZZZ 001_Very Nice Music Vol. 1 - 10 - 10_Fighter.mp3.mp3",
  "USA/AAA 001_I Rock, You Don't/AAA 001_I Rock, You Don't - 1 - I Rock, You Don't.mp3"
]

With that, we can compute a new set of file path variations, and I'll also reset my csv_rows.

Let's get a new cartesian product array:

[[{:country=>"Canada",
   :album=>"ZZZ 001_Very Nice Music Vol. 1",
   :track_number=>10,
   :title=>"Fighter",
   :possible_filenames=>
    ["canada/zzz 001 very nice music vol 1/fighter",
     "canada/zzz 001 very nice music vol 1/zzz 001 very nice music vol 1 fighter",
     "canada/zzz 001 very nice music vol 1/zzz 001 very nice music vol 1 10 fighter",
     "canada/zzz 001 very nice music vol 1/10 fighter"]},
  "Mexico/Rock/YYY 001_Rock Vol. 1/YYY 001_Rock_9_9_ Love You Much.mp3",
  0.6296653796653796],
 [{:country=>"Canada",
   :album=>"ZZZ 001_Very Nice Music Vol. 1",
   :track_number=>10,
   :title=>"Fighter",
   :possible_filenames=>
    ["canada/zzz 001 very nice music vol 1/fighter",
     "canada/zzz 001 very nice music vol 1/zzz 001 very nice music vol 1 fighter",
     "canada/zzz 001 very nice music vol 1/zzz 001 very nice music vol 1 10 fighter",
     "canada/zzz 001 very nice music vol 1/10 fighter"]},
  "Canada/ZZZ 001_Very Nice Music Vol. 1/ZZZ 001_Very Nice Music Vol. 1 - 10 - 10_Fighter.mp3.mp3",
  1.0],
 [{:country=>"Canada",
   :album=>"ZZZ 001_Very Nice Music Vol. 1",
   :track_number=>10,
   :title=>"Fighter",
   :possible_filenames=>
    ["canada/zzz 001 very nice music vol 1/fighter",
     "canada/zzz 001 very nice music vol 1/zzz 001 very nice music vol 1 fighter",
     "canada/zzz 001 very nice music vol 1/zzz 001 very nice music vol 1 10 fighter",
     "canada/zzz 001 very nice music vol 1/10 fighter"]},
  "USA/AAA 001_I Rock You Don't/AAA 001_I Rock You Don't - 1 - I Rock You Don't.mp3",
  0.6125451577579237],
 [{:country=>"Mexico",
   :album=>"YYY 001_Rock Vol. 1",
   :track_number=>9,
   :title=>"Love You Much",
   :possible_filenames=>
    ["mexico/yyy 001 rock vol 1/love you much",
     "mexico/yyy 001 rock vol 1/yyy 001 rock vol 1 love you much",
     "mexico/yyy 001 rock vol 1/yyy 001 rock vol 1 9 love you much",
     "mexico/yyy 001 rock vol 1/9 love you much"]},
  "Mexico/Rock/YYY 001_Rock Vol. 1/YYY 001_Rock_9_9_ Love You Much.mp3",
  0.9804808984852584],
 [{:country=>"Mexico",
   :album=>"YYY 001_Rock Vol. 1",
   :track_number=>9,
   :title=>"Love You Much",
   :possible_filenames=>
    ["mexico/yyy 001 rock vol 1/love you much",
     "mexico/yyy 001 rock vol 1/yyy 001 rock vol 1 love you much",
     "mexico/yyy 001 rock vol 1/yyy 001 rock vol 1 9 love you much",
     "mexico/yyy 001 rock vol 1/9 love you much"]},
  "Canada/ZZZ 001_Very Nice Music Vol. 1/ZZZ 001_Very Nice Music Vol. 1 - 10 - 10_Fighter.mp3.mp3",
  0.6403194506642782],
 [{:country=>"Mexico",
   :album=>"YYY 001_Rock Vol. 1",
   :track_number=>9,
   :title=>"Love You Much",
   :possible_filenames=>
    ["mexico/yyy 001 rock vol 1/love you much",
     "mexico/yyy 001 rock vol 1/yyy 001 rock vol 1 love you much",
     "mexico/yyy 001 rock vol 1/yyy 001 rock vol 1 9 love you much",
     "mexico/yyy 001 rock vol 1/9 love you much"]},
  "USA/AAA 001_I Rock You Don't/AAA 001_I Rock You Don't - 1 - I Rock You Don't.mp3",
  0.6289747064137309]]

The "I Rock, You Don't" file isn't matching anything. If I now run add_file_path_to_csv_rows, I get the same result. Now, we can find missing file paths:

find_missing_file_paths = lambda do |csv_rows, filename_list|
  filename_list.sort - csv_rows.map { |x| x[:matched_file_path] }.compact.sort
end

3.3.6 :428 > find_missing_file_paths.call(csv_rows, filename_list)
 => ["USA/AAA 001_I Rock You Don't/AAA 001_I Rock You Don't - 1 - I Rock You Don't.mp3"]

Now, what happens if we have an extra row in the CSV file? Let's start out with the same number of rows, but items that clearly don't match. I'll leave "I Rock, You Don't" in the list of files, and we'll add another line to the CSV file:

country	album	track_number	title
Canada	ZZZ 001_Very Nice Music Vol. 1	10	Fighter
Mexico	YYY 001_Rock Vol. 1	9	Love You Much
UK	UUU 003_Folk Vol. 3	1	Serious Sunday

The first two will match up, and we'll see "Serious Sunday" get matched with "I Rock, You Don't". Let's modify the matcher to store the JW distance in csv_rows as well:

add_file_path_to_csv_rows = lambda do |cartesian_product_array|
  cartesian_product_array.sort_by!(&:last)
  while cartesian_product_array.any?
    current_match = cartesian_product_array.last
    cartesian_product_array.reject! { |row|
      row[0] == current_match[0] || row[1] == current_match[1]
    }
    current_match[0][:matched_file_path] = current_match[1]
    current_match[0][:matched_file_score] = current_match[2]
  end
end

With that, I'll run everything:

3.3.6 :451 > add_possible_filenames.call csv_rows;
3.3.6 :452 > cartesian_product_array = compute_cartesian_product_array.call(csv_rows, file_path_variations);
3.3.6 :453 > add_file_path_to_csv_rows.call cartesian_product_array;

And the output (note that "possible_filenames" have been removed):

[{:country=>"Canada",
  :album=>"ZZZ 001_Very Nice Music Vol. 1",
  :track_number=>10,
  :title=>"Fighter",
  :matched_file_path=>
   "Canada/ZZZ 001_Very Nice Music Vol. 1/ZZZ 001_Very Nice Music Vol. 1 - 10 - 10_Fighter.mp3.mp3",
  :matched_file_score=>1.0},
 {:country=>"Mexico",
  :album=>"YYY 001_Rock Vol. 1",
  :track_number=>9,
  :title=>"Love You Much",
  :matched_file_path=>
   "Mexico/Rock/YYY 001_Rock Vol. 1/YYY 001_Rock_9_9_ Love You Much.mp3",
  :matched_file_score=>0.9804808984852584},
 {:country=>"UK",
  :album=>"UUU 003_Folk Vol. 3",
  :track_number=>1,
  :title=>"Serious Sunday",
  :matched_file_path=>
   "USA/AAA 001_I Rock You Don't/AAA 001_I Rock You Don't - 1 - I Rock You Don't.mp3",
  :matched_file_score=>0.5716374269005847}]

So, you can see that "I Rock, You Don't" and "Serious Sunday" matched, but the score is 0.57. We can get around this by ignoring low scores, we just have to define what "low" is. Ultimately, that's something to decide with a trial and error approach. For now, I think 0.9 is reasonable.

Let's take this one step further and see what happens if we add another CSV row:

country	album	track_number	title
Canada	ZZZ 001_Very Nice Music Vol. 1	10	Fighter
Mexico	YYY 001_Rock Vol. 1	9	Love You Much
UK	UUU 003_Folk Vol. 3	1	Serious Sunday
USA	AAA 002_Country	2	Let's Go Out

At this point, we now have two items in the CSV file and one file which are orphans.

[{:country=>"Canada",
  :album=>"ZZZ 001_Very Nice Music Vol. 1",
  :track_number=>10,
  :title=>"Fighter",
  :matched_file_path=>
   "Canada/ZZZ 001_Very Nice Music Vol. 1/ZZZ 001_Very Nice Music Vol. 1 - 10 - 10_Fighter.mp3.mp3",
  :matched_file_score=>1.0},
 {:country=>"Mexico",
  :album=>"YYY 001_Rock Vol. 1",
  :track_number=>9,
  :title=>"Love You Much",
  :matched_file_path=>
   "Mexico/Rock/YYY 001_Rock Vol. 1/YYY 001_Rock_9_9_ Love You Much.mp3",
  :matched_file_score=>0.9804808984852584},
 {:country=>"UK",
  :album=>"UUU 003_Folk Vol. 3",
  :track_number=>1,
  :title=>"Serious Sunday"},
 {:country=>"USA",
  :album=>"AAA 002_Country",
  :track_number=>2,
  :title=>"Let's Go Out",
  :matched_file_path=>
   "USA/AAA 001_I Rock You Don't/AAA 001_I Rock You Don't - 1 - I Rock You Don't.mp3",
  :matched_file_score=>0.6940492321589883}]

You can see that "Serious Sunday" didn't get matched, and "Let's Go Out" got matched to "I Rock, You Don't" with a score of 0.69.

A More Sophisticated Algorithm

We have a couple of places to cut out the low scores, but earlier in the process is better since there's no reason to even drag those around. I will note here that if you have every reason to believe that there will be 1:1 matching you might want to leave the low scores in. But for what we're doing here - we won't miss them.

compute_cartesian_product_array = lambda do |csv_rows, file_path_variations|
  csv_rows.map do |csv_row|
    file_path_variations.map do |filepath, variations|
      closest = csv_row[:possible_filenames].map do |possible_filename|
        variations.map do |variation|
          fuzzy.getDistance(possible_filename, variation)
        end
      end.flatten.max
      closest >= 0.9 ? [csv_row, filepath, closest] : nil
    end.compact
  end.flatten(1)
end

And the output:

[{:country=>"Canada",
  :album=>"ZZZ 001_Very Nice Music Vol. 1",
  :track_number=>10,
  :title=>"Fighter",
  :matched_file_path=>
   "Canada/ZZZ 001_Very Nice Music Vol. 1/ZZZ 001_Very Nice Music Vol. 1 - 10 - 10_Fighter.mp3.mp3",
  :matched_file_score=>1.0},
 {:country=>"Mexico",
  :album=>"YYY 001_Rock Vol. 1",
  :track_number=>9,
  :title=>"Love You Much",
  :matched_file_path=>
   "Mexico/Rock/YYY 001_Rock Vol. 1/YYY 001_Rock_9_9_ Love You Much.mp3",
  :matched_file_score=>0.9804808984852584},
 {:country=>"UK",
  :album=>"UUU 003_Folk Vol. 3",
  :track_number=>1,
  :title=>"Serious Sunday"},
 {:country=>"USA",
  :album=>"AAA 002_Country",
  :track_number=>2,
  :title=>"Let's Go Out"}]

And, of course, we now have a missing file path (really, a missing CSV entry):

["USA/AAA 001_I Rock You Don't/AAA 001_I Rock You Don't - 1 - I Rock You Don't.mp3"]

Wrap Up

There are a lot of options of how to handle this in your UI/UX. I like to show the matches and make anything that's of lower quality red to draw attention to it. It's often the case that I'll do this with a DropBox or Box directory, so the user has to find the directory and then be shown a matching list of files. If things don't match properly, they can rename them, add the missing files, whatever is appropriate, and then try matching again.

While this algorithm time increases exponentially as data is added, cutting off low-quality matches up front eases a lot of that pain. It still should be fine for even thousands of rows as long as you have enough memory.

Feel free to ask questions below.

Addendum

I wanted to make an addendum on this since I didn't really go into detail about why I do the matching in this manner. I'm doing a cartesian product of two sets, getting the distance, and then matching individually starting at the best matches and working my way down.

Why not just determine what's "close enough" and assume those are matches?

A lot of the data that I work with has really close names. Even the data set referenced above has this issue, but some other data sets even more so.

It's common for me to get a set of files with names like these (note that this is real data except for the title):

I Rock, You Don't - full.wav
I Rock, You Don't - full no drums.wav
I Rock, You Don't - under.wav
I Rock, You Don't - under no drums.wav
I Rock, You Don't - bass stem.wav
I Rock, You Don't - brass stem.wav
I Rock, You Don't - drums stem.wav
I Rock, You Don't - fx stem.wav
I Rock, You Don't - keys stem.wav
I Rock, You Don't - strings stem.wav
I Rock, You Don't - synth drones stem.wav
I Rock, You Don't - synth pulse stem.wav
I Rock, You Don't - sting.wav
I Rock, You Don't - 30.wav
I Rock, You Don't - 60.wav

I'll direct your attention to the "bass" and "brass" stems. Check this out:

3.3.6 :003 > fuzzy.getDistance("I Rock, You Don't - bass stem.wav", "I Rock, You Don't - brass stem.wav")
 => 0.9962514417531718

Those are really close, and if you're doing a really simple "take anything over .95" type algorithm you'll have problems.

I also can't just take exact matches, because it's so common with these filenames to have "drum stem" instead of "drums stem", etc. Plus typos.

The idea is to find the closest match first, and then mark those items on both sides as "matched" and remove them from the pool. By doing that, it's extremely unlikely to have issues such as "bass stem" and "brass stem" getting mixed up.

Replacing Cron Jobs With Active Jobs in Rails

Michael Chaney — Wed, 18 Dec 2024 22:11:01 +0000

For years - decades, actually - I've been using the rails runner to handle recurring jobs via cron. This works, and the output of the job is emailed to me so I can keep track of what's going on.

But with Rails 8 out and Solid Queue being a contender, it's time to change things up a bit and move the cron jobs to Solid Queue as recurring jobs. But, I don't want to lose the ability to view the output easily. I can capture the output and email it to myself.

Hmm, even better, I can capture the output and keep a log. And with AI tools I can create an agent to watch those logs for me and alert me to something that looks like trouble.

This is going to rock.

This particular project has a rather robust crontab with 16 entries. most of them look something like this:

# new autocomplete stuff at 7:02AM each morning
2 7 * * *   /bin/bash -l -c 'rvm 3.2.3 --quiet && cd $APP_HOME && bundle exec rails runner batch/autocomplete_load.rb'

# Daily CWR ack processing
57 15 * * * /bin/bash -l -c 'rvm 3.2.3 --quiet && cd $APP_HOME && bundle exec rails runner CwrDestination.download_and_process_all_ack_files'

# Weekly batch scripts
15 0 * * 1  /bin/bash -l -c 'rvm 3.2.3 --quiet && cd $APP_HOME && bundle exec rails runner Track.make_renewals'

As you can see, these aren't implemented as ActiveJobs. Instead, most are class methods on my models which implement effectively procedural job code, while others with no obvious home are simply scripts. I can move them to jobs, and likely will at some point. But, for now, I can make these work in Solid Queue as recurring jobs, anyway. The script will be moved to a Job, but the others will simply run as "commands".

Getting Started With Solid Queue

We have to start by adding the Solid Queue gem to our bundle, installing it, then running the installer to set up the initial config files for our application:

bundle add solid_queue
bin/rails solid_queue:install

There are two config files generated - config/queue.yml and config/recurring.yml. Additionally, db/queue_schema.rb is generated, along with the bin/jobs script.

(See the install_generator.rb file for more information)

It also changes your config/environments/production.rb file in two ways. First, it sets config.active_job.queue_adapter to ":solid_queue", and it adds this line as well:

  config.solid_queue.connects_to = { database: { writing: :queue } }

That line assumes that Solid Queue will use a separate database referenced in your config/databases.yml as "queue". The Solid Queue docs show how to modify your file to accommodate this.

I'm using Postgres and it's all on one server, so setting up a separate database doesn't make sense. I simply commented that line out, causing Solid Queue to use the primary database.

An Aside - The Issue When Using SQL Schema Format

As an aside, there's an issue that will arise here if you're going to have a separate database for Solid Queue and you're using the sql schema format. Solid Queue creates the "queue_schema.rb", which is the reference for the database in the config/database.yml file ("queue") plus "_schema.rb".

As the example:

production:
  primary:
    <<: *default
    database: storage/production.sqlite3
  queue:
    <<: *default
    database: storage/production_queue.sqlite3
    migrations_paths: db/queue_migrate

I'm taking this aside to warn you that if you are using the sql schema format the queue_schema.rb file will be ignored when you try to set up the queue database. In that case, you need to turn it into a migration and let the db:setup task create the SQL schema file for you.

Back To Setup

Since I'm using the same database, I won't have a separate schema file and migration directory for Solid Queue. So, I created a migration called add_solid_queue, and for its change method I added all the code from the db/queue_schema.rb file, which I then removed.

At this point, I just have to run bin/rails db:migrate to create the Solid Queue tables.

The queue.yml Config File

The runner allows for a very robust configuration, but I have a fairly simple application. I need three workers:

A general worker for sending email and doing quick jobs in the "default" queue
A worker for handling audio encoding and importing - I just want to handle one of these at a time
A worker to handle the old crontab stuff - again, one at a time

My polling interval is set to 1 second for most of these as there's no reason to beat on the database for stuff that's going to take more time to run.

Because the config/queue.yml and config/recurring.yml files are sectioned by env, I add them to the git repo. Not necessary for what I'm doing, but makes life easier.

Here's my queue.yml file:

default: &default
  dispatchers:
    - polling_interval: 1
      batch_size: 500
  workers:
    - queues: "*"
      threads: 3
      processes: 1
      polling_interval: 0.1
    - queues: [ "encoding", "import" ]
      threads: 1
      processes: 1
      polling_interval: 1
    - queues: [ "cron" ]
      threads: 1
      processes: 1
      polling_interval: 1

development:
  <<: *default

test:
  <<: *default

production:
  <<: *default

This is probably not useful for you, but I wanted you to see what I've done.

The recurring.yml Config File

Here's where the real fun is: the recurring.yml file. Taking the three items shown from the crontab, here's what I have in recurring.yml:

production:
  autocomplete_load:
    class: AutocompleteLoadJob
    queue: cron
    schedule: 'every day at 7:02am'
  cwr_process_ack_files:
    command: "CwrDestination.download_and_process_all_ack_files"
    queue: cron
    schedule: 'every day at 7:10am'
  make_renewals:
    command: "Track.make_renewals"
    queue: cron
    schedule: 'every monday at 12:15am'

For some items, the "schedule" is straight from the crontab. For instance, I have one that runs at 5:15AM on the first day of each quarter:

15 5 1 1,4,7,10 *   /bin/bash ...

I just put that crontab day/time string directly in the "schedule" key:

  quarterly_term_emails:
    command: "Track.quarterly_term_emails"
    queue: cron
    schedule: '15 5 1 1,4,7,10 *'

Logging

All of my crontab entries just log to stdout, but that means the output will end up in a log file somewhere. That's fine, but ultimately I want something like I had before where each one was emailed to me.

Better than email is to simply log to a database table so I can look back over these later, plus keep track of errors easily.

To do this, I first create a new database table and associated model called JobLog:

class CreateJobLogs < ActiveRecord::Migration[8.0]
  def change
    create_table :job_logs do |t|
      t.string :job_class, null: false, index: true
      t.uuid :job_id, null: false, index: true
      t.text :output
      t.datetime :started_at, null: false
      t.datetime :finished_at, null: false
      t.boolean :success, null: false, default: false, index: true
      t.boolean :needs_attention, null: false, default: false, index: true
      t.text :error_details

      t.timestamps
    end

    add_index :job_logs, :created_at
  end
end

And, the model:

class JobLog < ApplicationRecord
  scope :recent, -> { order(created_at: :desc) }
  scope :for_job, ->(job_class) { where(job_class: job_class) }
  scope :needs_attention, -> { where(needs_attention: true) }
  scope :problematic, -> { where("needs_attention = ? OR success = ?", true, false) }

  def failed?
    !success
  end

  def duration
    (finished_at - started_at).round(2)
  end

  validates :job_class, :job_id, presence: true
end

There are two flags here of interest: success and needs_attention. If the script fails, we can set success to "false" and log the error information. But it's also possible that the script won't "fail" but will find a problem. In that case, we can flag this in code so that it'll be called out to the administrator later. It might make sense to also send an email in these cases, and doing so would be an easy addition.

Of course, I also created a simple view for my admin dashboard where I can see these as they complete.

To do all of this, I created a concern which I can include in my jobs. It lives in app/jobs/concerns/job_logging.rb:

module JobLogging
  extend ActiveSupport::Concern

  included do
    around_perform :capture_job_output
    attr_accessor :current_job_log
    attr_accessor :job_class
  end

  def flag_for_attention(message = nil)
    if message
      puts "\n[NEEDS ATTENTION] #{message}"
    end
    @needs_attention = true
  end

  def alt_job_name(job_name)
    @job_class = job_name
  end

  private

  def capture_job_output
    original_stdout = $stdout
    captured_output = StringIO.new
    start_time = Time.current
    success = true
    error_details = nil
    @needs_attention = false

    $stdout = captured_output
    puts "Job started at: #{start_time}"

    begin
      yield
    rescue StandardError => e
      success = false
      error_details = generate_error_details(e)
      puts error_details
      raise e
    ensure
      end_time = Time.current
      duration = (end_time - start_time).round(2)
      puts "\nJob finished at: #{end_time}"
      puts "Total duration: #{duration} seconds"

      $stdout = original_stdout
      captured_output.close
      output = captured_output.string

      Rails.logger.info(output)

      # Save to database
      self.current_job_log = JobLog.create!(
        job_class: @job_class || self.class.name,
        job_id: job_id,
        output: output,
        started_at: start_time,
        finished_at: end_time,
        success: success,
        error_details: error_details,
        needs_attention: @needs_attention
      )
    end
  end

  def generate_error_details(error)
    <<-ERROR_DETAILS

ERROR DETAILS
============
Error class: #{error.class}
Error message: #{error.message}
Backtrace:
#{error.backtrace.take(50).join("\n")}
    ERROR_DETAILS
  end
end

(note that I simplified this from an earlier version)

I use the CustomWriter class in here because I want the output to go to the database log as well as the Rails logger. With this, I can create a superclass called "LoggedJob":

class LoggedJob < ApplicationJob
  include JobLogging
end

And then simply use it as the superclass for any jobs to be logged:

class AutocompleteLoadJob < LoggedJob
  queue_as :cron
  ...
end

Handling Commands

We still need to handle the recurring jobs that are "commands". The issue is that they won't be logged to our job_logs table automatically, but it's pretty easy to hook them up.

First, we need to create a new superclass just for those, and it'll in turn be based on the class already used for recurring commands:

class RecurringLoggedJob < SolidQueue::RecurringJob
  include JobLogging

  def perform(*args)
    alt_job_name args.first
    super
  end
end

Note that I'm using alt_job_name - that was added specifically for these jobs. Since the job logger uses the job name by default, all of these end up showing up as "RecurringJob" unless we do something different. In this case, we just take the command that's passed in and log it.

Administration

I'm going to copy in the controller and views here.

class Admin::JobLogsController < Admin::ApplicationController
  def index
    @job_logs = JobLog.recent

    if params[:job_class].present?
      @job_logs = @job_logs.for_job(params[:job_class])
    end

    case params[:filter]
    when 'attention'
      @job_logs = @job_logs.needs_attention
    when 'problematic'
      @job_logs = @job_logs.problematic
    end

    @job_logs = add_pagination(@job_logs)
  end

  def show
    @job_log = JobLog.find(params[:id])
  end
end

<h1>Job Logs</h1>

<%= form_tag admin_job_logs_path, method: :get, class: 'mb-4' do %>
  <div class="row">
    <div class="col">
      <%= select_tag :job_class, 
                     options_for_select(JobLog.distinct.pluck(:job_class), params[:job_class]), 
                     prompt: "All Jobs",
                     class: 'form-select' %>
    </div>
    <div class="col">
      <%= select_tag :filter,
                     options_for_select([
                       ['All', ''],
                       ['Needs Attention', 'attention'],
                       ['Problematic (Failed or Needs Attention)', 'problematic']
                     ], params[:filter]),
                     class: 'form-select' %>
    </div>
  </div>
  <%= submit_tag "Filter", class: 'btn btn-primary my-2' %>
<% end %>

<table class="table table-striped">
  <thead>
    <tr>
      <th>Job</th>
      <th>Started</th>
      <th>Duration</th>
      <th>Status</th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <% @job_logs.each do |log| %>
      <tr class="<%= 'table-warning' if log.needs_attention && log.success %>">
        <td><%= log.job_class %></td>
        <td><%= log.started_at.strftime('%Y-%m-%d %l:%M:%S%p %Z') %></td>
        <td class="text-end"><%= log.duration %></td>
        <td>
          <% if !log.success %>
            <span class="badge bg-danger">Failed</span>
          <% elsif log.needs_attention %>
            <span class="badge bg-warning text-dark">Needs Attention</span>
          <% else %>
            <span class="badge bg-success">Success</span>
          <% end %>
        </td>
        <td>
          <%= link_to "View", admin_job_log_path(log), class: 'btn btn-sm btn-primary' %>
        </td>
      </tr>
    <% end %>
  </tbody>
</table>

<%= will_paginate @job_logs, renderer: WillPaginate::ActionView::Bootstrap4LinkRenderer if @job_logs.respond_to?(:total_pages) -%>

<div class="job-log">
  <h1>Job Log: <%= @job_log.job_class %></h1>

  <div class="card mb-4">
    <div class="card-body">
      <dl>
        <dt>Job ID</dt>
        <dd><%= @job_log.job_id %></dd>

        <dt>Started At</dt>
        <dd><%= @job_log.started_at.strftime('%Y-%m-%d %l:%M:%S%p %Z') %></dd>

        <dt>Duration</dt>
        <dd><%= @job_log.duration %> seconds</dd>

        <dt>Status</dt>
        <dd>
          <% if @job_log.success %>
            <span class="badge bg-success">Success</span>
          <% else %>
            <span class="badge bg-danger">Failed</span>
          <% end %>
        </dd>
      </dl>
    </div>
  </div>

  <div class="card">
    <div class="card-header">
      Output
    </div>
    <div class="card-body">
      <pre><code><%= @job_log.output %></code></pre>
    </div>
  </div>

  <% if @job_log.error_details.present? %>
    <div class="card mt-4">
      <div class="card-header text-white bg-danger">
        Error Details
      </div>
      <div class="card-body">
        <pre><code><%= @job_log.error_details %></code></pre>
      </div>
    </div>
  <% end %>
</div>

<%= link_to 'Back', admin_job_logs_path %>

In Production

You'll also have to make this work in production. I'm using Capistrano to deploy, and this simple tutorial from Rob Zolkos shows how to set it up:

https://www.zolkos.com/2024/02/21/how-i-deploy-solid-queue-with-capistrano

If you're using kamal or another deployment method it will be different.

Summary

Moving from old style cron to recurring jobs with Solid Queue is a great way to keep your entire application in one place. I used to have to worry about the crontab when moving servers and such, and I got too many emails every day. This takes care of them. I have also created a simple job to check every day and email me any jobs where "success" is false or "needs_attention" is true.

After I get a few weeks of these I'll set up a simple agent using an LLM that'll view the logs and alert me when something seems off.

Solid Queue also helped me remove the redis dependency, which is important moving forward.

Let me know below if you have questions, or reach out on X.

Moving From auto_strip_attributes to normalizes

Michael Chaney — Sat, 31 Aug 2024 16:05:44 +0000

I ran across the excellent "auto_strip_attributes" gem years ago, and it's been a staple gem that gets added to my Gemfile right off the bat when making a new project. I even have a standard initializer that adds some extra functionality to it:

AutoStripAttributes::Config.setup do
  set_filter(fix_curly_quotes: false) do |value|
    !value.blank? && value.respond_to?(:gsub) ? value.gsub(/[\u201c\u201d]/, '"').gsub(/[\u2018\u2019]/, '\'') : value
  end
  set_filter(downcase: false) do |value|
    !value.blank? && value.respond_to?(:downcase) ? value.downcase : value
  end
  set_filter(unicode_normalize: false) do |value|
    !value.blank? && value.respond_to?(:unicode_normalize) ? value.unicode_normalize : value
  end
end

These should be self-explanatory, but just in case:

fix_curly_quotes - Changes Unicode curly double quotes “” to a standard ASCII quotation mark ", and changes Unicode curly single quotes ‘’ to a standard ASCII apostrophe.

downcase - Force text to lowercase.

unicode_normalize - Normalizes Unicode characters to the "nfc" form.

This has been an awesome tool in the tool belt for years now. With the advent of Rails 7.1, we now have "normalizes" built in to ActiveModel. It takes the place of auto_strip_attributes, but there are some differences that require your attention if you're changing from one to the other.

Here's a standard setup for auto_strip_attributes that is pulled from my code:

class Track < ApplicationRecord
  auto_strip_attributes :title, :original_title, :artist, squish: true, nullify: false, fix_curly_quotes: true

  auto_strip_attributes :lyric_notes, nullify: true, fix_curly_quotes: true, squish: true

  auto_strip_attributes :lyrics, nullify: true
  ...
end

This is all normal stuff. In general, you don't want to "squish" multi-line text blocks and if a field can be nullified then you add "nullify: true" to leave it as "null" if there is no value.

One serious "gotcha" here is that there is no checking for bad filter names. If you misspell a filter name it will be silently ignored.

The code for this gem is really simple, and you can view it here. It creates a "before_validation" callback to apply the filters right before validation.

Keep that in mind.

Starting in Rails 7.1 we have "normalizes" as a declaration to make in our models. It provides the same functionality, but in a different manner.

From a different project, also with a "Track" model:

class Track < ApplicationRecord
  normalizes :title, with: -> (value) { value.strip.unicode_normalize }
  normalizes :description, with: -> (value) { value.strip.unicode_normalize }
  normalizes :lyrics, with: -> (value) { value.strip.unicode_normalize }
  normalizes :lyrics_chorus_only, with: -> (value) { value.strip.unicode_normalize }
  ...
end

This is pretty similar, but there are differences:

There are no "built-in" normalizers. You can munge the string with any methods on string or create a lambda/proc that'll do what you need.
There are no "default" normalizers. auto_strip_attributes strips by default, "normalizes" has no defaults.
By default, nils are ignored, so you don't have to worry about getting something other than a string. However, you can use the "apply_to_nil" option to force your normalizer to be called even with nil values.
Each attribute has its own declaration. This is interesting because the model class has a method "normalize_value_for" which accepts an attribute name plus a value, allowing you to "normalize" a random string as if it were the named attribute.
Big one: the normalizer is applied when the attribute is assigned a value.

5 has an interesting side effect which is useful. Consider this code:

class Artist < ApplicationRecord
  normalizes :name, with: -> name { name.strip.downcase.unicode_normalize }
end

Artist.create(name: "Beyoncé")

At this point, we have a record where the name is "beyoncé". What does this code do?

beyonce = Artist.find_or_initialize_by(name: " Beyoncé ")

This is the big difference between the two. If you're using "auto_strip_attributes" here you'll get a new record. Using "normalizes" - the existing record will be found instead as it'll run " Beyoncé " through the normalizer before doing the find.

This isn't a gotcha, though. It's very desired functionality, as it allows you to perform this function without duplicating somehow the normalization code.

These are both great ways to accomplish normalizing your attributes, which is a very important aspect of bringing data from outside into your database. I am moving to "normalizes" for all new code simply because it's one less gem, but I still have lots of code using "auto_strip_attributes".

Adding A Confirmation Interstitial On Create in REST

Michael Chaney — Wed, 24 Jul 2024 04:31:23 +0000

On X, Tom Rossi asked about handling a confirmation page on a form while conforming to RESTful routes.

// Detect dark theme var iframe = document.getElementById('tweet-1814001018923004250-819'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1814001018923004250&theme=dark" }

This is an excellent question and one which I've answered in a couple of different ways in various projects. But, I'm a bit of a REST snob these days and I want any solution to be as RESTy as possible.

What's UnRESTy About It?

Well, that's a good question. Let's take the kind of straightforward method of doing this and consider it in the context of REST and Ruby on Rails, with a simple "todo list" application.

The TodoList application has a single model called "Todo". Each todo record has a varchar field called "body" and a boolean field called "finished". Very simple.

class CreateTodos < ActiveRecord::Migration[7.1]
  def change
    create_table :todos do |t|
      t.string :body, limit: 100, null: false
      t.boolean :finished, null: false, default: false

      t.timestamps
    end
  end
end

The model:

class Todo < ApplicationRecord
  normalizes :body, with: ->(value) { value.strip }

  validates :body, presence: true, length: { maximum: 100 }
end

With the standard scaffold, we can now create, list, examine, edit, and delete todos.

If we hit the route /todos/new, we'll get a form which we can fill in to create a new item. When we submit the form - which is a POST request to the /todos route, the item is checked for validity and created if it's valid. This is standard REST.

How do you add a confirmation page in there?

This is where it gets unRESTy. Let's consider the flow with the interstitial in place:

User hits the "new" route, gets the form
User fills out the form, hits "Save"
If information doesn't pass validation, the user is shown the form again along with error information so they can correct it.
If the information passes validation, the user is shown the information again, and given the choice to a) Really Save or b) Make Changes
If "Make Changes", go back to the form with the information filled in to allow changes
If "Really Save", save the information to the database

One aspect of this to consider is that the user wants to see any validation errors at step 3. This brings up an issue because we then have to validate the data somehow after step 2 but before it's actually saved. With RoR, this requires a bit of thought because it's easier to wait until step 6. But users will complain that your application is moronic if they don't see errors until that late in the game. I might agree with them.

There are multiple ways to handle this. I have created a simple Rails app to demonstrate all of them:

mdchaney / todo_with_confirmation

This is a sample rails app for a todo app with confirmation

TODO - Basic

This ia a simple todo application which is discussed in this article:

https://dev.to/mdchaney/adding-a-confirmation-interstitial-on-create-in-rest-4an0

View on GitHub

The "please" parameter

Add a "please" parameter to the form, and only perform the "create" action if it is present. This allows you to show the information again and stick it all in hidden fields along with your "please" parameter. Then, the user can hit "Really Save" and it'll hit the same create route and actually save it.

This works (I know, I did it 20 something years ago) but it's unRESTy and makes me want to shower after I write it. The "create" route may or may not create the item depending on the presence of the "please" parameter.

A challenge with this method is that after you have the data payload it's required to use the HTTP post method to make sure your data is moved forward continuously. That means that if they hit "Make Changes" you need to either a) allow the "new" route to accept a post with some data or b) add a different route. In the sample app I use a "redo" route that accepts a post.

If you think about it in terms of an API, the whole thing is kind of silly because the client can simply send the "please" parameter the first time and not worry about confirmation, anyway. More on this later.

Hit A Different Route For the Interstitial

This is RESTier than the former solution. The idea here is that the form doesn't just POST to the /todos route (which is "create" in RESTland). Instead, it posts to another route - such as /todos/confirm - which validates the data and handles steps 3 and 4. Then, if the user is happy with the data, they can hit "Really Save" and the data is posted to the real "create" route. Note that this still requires the extra "redo" route as well.

This is definitely the RESTier way to do it. But one issue that this and the former method have in common is that the data is changeable and the user still has the capability of doing silly stuff. They're not constrained by the interface.

Hit A Different Route For the Interstitial, and Sign the Data

This fixes the problems with the second method. In this method, the data from the form is encrypted and signed, then stored in a hidden field. The data is shown to the user, but the only data that is transmitted back to the server on the "create" route is an encrypted (or just signed) blob that can be used to recreate the data.

This strikes me as less RESTy, and the reason is that the I feel (yes, this is purely subjective) that the data that is sent to the "create" route should be the data for the record in form data or JSON format.

There is another way that gets around that issue, and that is to simply create a cryptographic signature on the back end in step 4 which is sent back to the confirmation form along with the field data, then verified before the item is created. That's a little more work as you have to make sure to create the signature in a repeatable manner.

So, this is a reasonable way to do it, but it's still not as RESTy as I'd like. But I think it's RESTy enough.

I know I sound a bit anal about making sure the data wasn't changed, but this really is a UI issue that we're handling between the front end and the back end. The back end ultimately decides whether to accept a set of data or not based on its validations, and I personally believe that the UI part that makes this tricky is handling the fact that after the back end vouches for a set of data and says "it validated" we want to make sure that's the set of data that is later sent to it to be saved. It'll help prevent some weird errors.

Going back to the API - if you're creating something like this using React then the front end has to really handle everything. In that case, you can think about the discrete components easier:

The front end sends a set of data to the back end for validation
If it's not valid, a set of errors are returned
If it is valid, a signature is sent to the front end that can be sent along with the final "create" to make it save
When the user hits "Really Save", the signature is sent along with the form data and the database saves the information.

Alternately, this also works if the server just sends the binary blob representing the marshalled/encrypted/signed data.

But, isn't this just the "please" parameter all over again? No. The reason it isn't is that the "please" parameter can be easily faked and sent to the server at any time. This method forces the front end to first ask the back end "is this data valid?", and then send the proof of validity (the signature) back along with the data so the server can trust that it validated it before.

How To Accomplish This

Again, for me the easiest way to handle this is the binary blob. I use this pattern in multiple places, and sometimes it's not for confirmation directly. I have some multi-step forms that require some data to be entered, and then saved across the next page which might do something completely different. It's again important to ensure that after the server has vouched for the data that it get the exact same data next time.

Another common place to use this is in ordering. I don't want to create the order in the database until payment is secured. And I don't want the user to be able to edit the form and get free items or something like that.

Using MessageEncryptor

Rails provides ActiveSupport::MessageEncryptor to help encrypt and sign. The basic framework is this:

Generate a key based on your secret key base
Generate a secret based on a salt value that you provide
Use this to encrypt and sign your value

Later:

Use the same "secret" to decrypt and verify the signature

class TodoWithBlob < ApplicationRecord
  normalizes :body, with: ->(value) { value.strip }

  def self.encryptor
    @encryptor ||=
      begin
        salt = "very salty salt"
        key_gen = ActiveSupport::KeyGenerator.new(Rails.application.credentials.secret_key_base, iterations: 1000)
        secret = key_gen.generate_key(salt,32)
        @encryptor = ActiveSupport::MessageEncryptor.new(secret)
      end
  end

  def as_json_string
    attributes.except("id", "created_at", "updated_at").to_json
  end

  def as_encrypted_blob
    self.class.encryptor.encrypt_and_sign(self.as_json_string)
  end

  def self.encrypted_blob_to_json(encrypted_blob)
    JSON.parse(encryptor.decrypt_and_verify(encrypted_blob))
  end

  def self.from_encrypted_blob(encrypted_blob)
    new(encrypted_blob_to_json(encrypted_blob))
  end

  validates :body, presence: true, length: { maximum: 100 }
end

I also use this basic pattern when handling orders that need to be validated but not saved in the database until the payment has processed.

The Universal ID gem will be usable here as well when signing is added to it.

Summary

We've looked at some various ways to try to keep our app as RESTy as possible while providing a decent UI to the user. The UI shows the user any errors immediately at form submission and assumes that when they confirm their input on the next page it will be valid. I strongly recommend one of the latter two options (signature or signed blob) to ensure that the data isn't tampered with intentionally or accidentally between validation on the server and ultimately saving it.

When I get time I'll use a similar technique to make a multi-page form.

Considerations for Unicode and Searching

Michael Chaney — Thu, 04 Jul 2024 23:06:41 +0000

In previous posts here and on Twitter (now X) I've written about using Unicode and UTF-8 in general application development. In this article I'm going to expand a bit on how to handle searching when Unicode is present.

To quickly summarize using Unicode in modern application development - it's probably something you won't even have to think about if you're using a modern high-level language. Strings are almost universally assumed to be in UTF-8 format, so everything just works. The biggest problem is when importing data from outside - there's still a ton of code out there that is writing using older 8-bit encodings (generally Latin-1/ISO-8859-1 in the western world) so it's best to check your data and force it to UTF-8 before processing.

With that out of the way, let's consider "Beyoncé". Why? For purposes of this discussion simply because that name has a Latin Small Letter E with Acute character - also known as "eacute" - in it.

michael-chaneys-computer-2:~ mdchaney$ hexdump -C
Beyoncé
00000000  42 65 79 6f 6e 63 c3 a9  0a                       |Beyonc...|

In this hex dump, "c3 a9" is the two-byte UTF-8 encoding for unicode character E9. In a text file, on this web site's database, etc. that will be stored as the two bytes that you see above.

Here's the issue, though. If I'm searching for "Beyoncé" - how do I even get a funky accent thing above the "e"? I'll tell you how if you're on a Mac - press "option e", let them both up, then press the vowel over which you wish to place the acute symbol. In other words, option-e, then e. Beyoncé.

So, that's it, right?

Of course not. Let's say I have a music database (I really do have a bunch of them) and Beyoncé is in there. I want people to be able to search for her name and find her music. The problem? I'll tell you what the problem is - a bunch of Neanderthals using the internet that don't know that little Mac trick that I learned 2 minutes ago about typing an e-with-an-acute-mark from my keyboard.

Okay, I'll confess. Even I would just type in "beyonce" if I was searching for her music. Not even a capital B, much less the funky acute mark over the e. Google has been so good for so long that we expect that search to just work because it always has at Google.

So, how do we handle this when we write the search code?

Well, what are we using for search?

I want to spend the brunt of this article talking about how to do this in Postgres, partly because it's a little more difficult there. But let me start in Apache Solr, which is where I first worked on these issues.

Apache Solr Configuration

Solr is based on the Lucene library, as is Elastic. I mention this because these two search engines provide most private web site search capabilities on the internet. I started using Lucene around 25 years ago in Perl - it works well.

In Apache Solr, there's a filter that can be added to the config for a search index called the "ASCIIFoldingFilter". The code is a sight to behold, as it contains the logic for turning "Beyoncé" into "Beyonce". There's another case folder to further turn that into "beyonce".

Go ahead and check out the source code linked above. I would direct your attention specifically to line 434 which matches "é", aka "latin small letter e with acute". If you scroll all the way to line 474 you'll find that the "é" along with 40 other friends gets mapped to a plain old "e".

You've probably caught on even though I haven't explicitly stated it yet: for purposes of searching we have to get our text into the simplest form possible both for storage as well as for searching. After we munge both sets of data in the same way, we end up with this being stored:

beyonce

And any of these terms matching:

Beyoncé
beyoncé
Beyonce
beyonce

Oddly enough, a bunch of things map to the plain old "e", so:

Beyoncǝ

also works. If you want to really get nuts, Imagine all the things that map to any of those letters.

ⓑǝẙǾɳꜾᶔ

If you run that string through the solr.ASCIIFoldingFilter, you'll get "beyonce" out the other side.

I'm not saying that so that you can do it, although nobody's stopping you. The point is that there are many characters that map to any single ASCII character in this scenario. They will be stored in plain ASCII where possible.

There are plenty of Unicode characters that won't be changed to anything else, by the way. Emojis are a common group of characters that'll go straight through. Chinese characters (regardless of what the multi-lingual genius at the tattoo parlor tells you) don't actually map to ASCII characters and vice versa. Same with Korean, Thai, Arabic, etc. In fact, the vast majority of Unicode characters are untouched by ASCIIFoldingFilter.

But, for standard text from the Western World (The Americas and Europe) the characters will map down to the closest ASCII equivalent. Accents, umlauts, little circles - all those things get dropped for indexing.

Apache Solr is a great search engine and I've used it extensively for well over a decade. It handles all of this text munging extremely well. But I also use PostgreSQL and it has a very robust full-text search engine built right in that might be good for your project, especially if you're already using it as your RDBMS data store.

Configuring PostgreSQL For Search

I love Postgres and use it just about everywhere. But the full-text search functionality requires a little bit of work to handle text in the manner that you want it to. Thankfully, their documentation is great, AI assistants such as ChatGPT and Claude seem to know everything about this subject, and Postgres itself comes with some awesome tools to make testing a breeze.

Solr comes with various filters that you can use to munge your input text into the output text. Postgres has fewer available options, but the default setup is pretty good for English text. This includes a standard "stopword" dictionary to keep common words out of your index, implement case folding, and word stemming.

Relevant to our discussion today, it also comes with the "unaccent" dictionary to handle fixing "Beyoncé", and "mañana", and whatever else you might come up with. But it's not included by default, so you'll have to do a little work.

Let's first see how it handles all of this by default.

Postgres includes the excellent "ts_debug" function which allows you to see exactly what it thinks about your text.

img3_dev=> select * from ts_debug('Beyoncé is coming mañana');
   alias   |    description    |  token  |  dictionaries  |  dictionary  |  lexemes  
-----------+-------------------+---------+----------------+--------------+-----------
 word      | Word, all letters | Beyoncé | {english_stem} | english_stem | {beyoncé}
 blank     | Space symbols     |         | {}             |              | 
 asciiword | Word, all ASCII   | is      | {english_stem} | english_stem | {}
 blank     | Space symbols     |         | {}             |              | 
 asciiword | Word, all ASCII   | coming  | {english_stem} | english_stem | {come}
 blank     | Space symbols     |         | {}             |              | 
 word      | Word, all letters | mañana  | {english_stem} | english_stem | {mañana}

This shows the default parser in action. "is" is a stop word, so it gets kicked out. "coming" is run through the default stemmer and is normalized as "come". But "Beyoncé" and "mañana" pass through unchanged save for being downcased.

Now, one can make the case that letters like "é" and "ñ" don't occur in regular English so this is the proper functionality. I'm handling searches where names are a common element in the search, and accented characters are much more common in names. Even if we restrict ourselves to the United States where English is the most common language, clearly Spanish is the second most common and French is quite common as well.

It's not just Beyoncé - Michael Bublé has an e-with-acute in his name as well. And many others.

Just searching Google for "singers with accents in their names" brings up a litany of issues from programmers handling this situation. Look at this one, which also seems to be based on lack of Unicode normalization:

https://www.discogs.com/forum/thread/826212

Here's someone talking about this problem in 2009:

https://musicmachinery.com/2009/04/10/removing-accents-in-artist-names/

Anyway, the point is that even if you're handling straight English text, removing diacritics is important for name matching. And since the English language doesn't have diacritics, it won't really hurt to turn those letters into their straight ASCII equivalent since they won't exist outside of names or some non-English words.

We have to create the "unaccent" extension in our database, create the new text search configuration, and finally modify it to add "unaccent" to the mix.


img3_dev=> CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION
img3_dev=> CREATE TEXT SEARCH CONFIGURATION music_search ( COPY = english );
CREATE TEXT SEARCH CONFIGURATION
img3_dev=> ALTER TEXT SEARCH CONFIGURATION music_search
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, english_stem;
ALTER TEXT SEARCH CONFIGURATION
img3_dev=> select * from ts_debug('music_search', 'Beyoncé is coming mañana');
   alias   |    description    |  token  |      dictionaries       |  dictionary  |  lexemes  
-----------+-------------------+---------+-------------------------+--------------+-----------
 word      | Word, all letters | Beyoncé | {unaccent,english_stem} | unaccent     | {Beyonce}
 blank     | Space symbols     |         | {}                      |              | 
 asciiword | Word, all ASCII   | is      | {english_stem}          | english_stem | {}
 blank     | Space symbols     |         | {}                      |              | 
 asciiword | Word, all ASCII   | coming  | {english_stem}          | english_stem | {come}
 blank     | Space symbols     |         | {}                      |              | 
 word      | Word, all letters | mañana  | {unaccent,english_stem} | unaccent     | {manana}

Note that ts_debug isn't perfect. If you run this through to_tsvector, you get a slightly different result:

img3_dev=> select to_tsvector('music_search', 'Beyoncé is coming mañana');
          to_tsvector           
--------------------------------
 'beyonc':1 'come':3 'manana':4

Note that the stemming algorithm turns "beyonce" into "beyonc", and that's fine. The words will match.

To show you how this works, consider the following queries where I use the standard built-in "english" search configuration:

img3_dev=> select to_tsvector('english', 'Beyoncé is coming mañana') @@  
    to_tsquery('english', 'beyoncé');
 ?column? 
----------
 t
(1 row)

img3_dev=> select to_tsvector('english', 'Beyoncé is coming mañana') @@  
    to_tsquery('english', 'beyonce');
 ?column? 
----------
 f
(1 row)

If we search for "beyoncé", it's a match, but if we search for "beyonce" there's no match.

Switching to our new "music_search" configuration:

img3_dev=> select to_tsvector('music_search', 'Beyoncé is coming mañana') @@  
    to_tsquery('music_search', 'beyonce');
 ?column? 
----------
 t
(1 row)

img3_dev=> select to_tsvector('music_search', 'Beyoncé is coming mañana') @@  
    to_tsquery('music_search', 'beyoncé');
 ?column? 
----------
 t
(1 row)

There it is - works either way.

I would also note that the "unaccent" dictionary in Postgres has fewer mappings than the ASCIIFoldingFactory in Solr. But, quite a few are still there:

img3_dev=> select to_tsvector('music_search', 'Beyoncé is coming mañana') @@ 
    to_tsquery('music_search', 'Ƃⅇẙỗɳcᶔ');
 ?column? 
----------
 t
(1 row)

If you want to see the dictionary, Postgres has these stored under your "share" directory, basically ".../share/postgresqlxx/tsearch_data", where "xx" is the version number. Depending on the installation, this will likely be under "/usr", but it might be under "/usr/local" or wherever Postgres is installed.

You can view the "unaccent.rules" file to see the list.

As an aside, if you want to gꝏf around (see what I did there?) with it and come up with weird ways to spell "Beyoncé" so it'll match, use "grep":

michael-chaneys-computer-2:dl5 mdchaney$ grep 'o$' /opt/local/share/postgresql16/tsearch_data/unaccent.rules 
ò  o
ó  o
ô  o
õ  o
ö  o
ø  o
ō  o
ŏ  o
ő  o
ơ  o
ǒ  o
ǫ  o
ǭ  o
ȍ  o
ȏ  o
ȫ  o
ȭ  o
ȯ  o
ȱ  o
ṍ o
ṏ o
ṑ o
ṓ o
ọ o
ỏ o
ố o
ồ o
ổ o
ỗ o
ộ o
ớ o
ờ o
ở o
ỡ o
ợ o
℅ c/o
№ No
ℴ o
ⱺ o
ꜵ ao
ꝋ o
ꝍ o
ꝏ oo
ｏ o

That shows you all the characters that will map to "o". Do that for all the letters, choose your favs, copy/paste, you get the idea.

Outtro

There's a lot to configuring a full-text search application on Postgres, but if you're going to search names this is pretty much required. And it's not difficult.

Feel free to hit me up in comments here or on X (@MichaelDChaney) for any further ideas that you might need help with.

Making Common Table Expression SQL More Railsy

Michael Chaney — Sun, 30 Jun 2024 22:53:39 +0000

In our last episode, I talked about using the common table expression syntax to make a query run much faster and allow me to insert and query the new records at the same time.

Starting in Rails 7.1, it's now possible to add common table expressions to ActiveRecord relations. I can rewrite the query to use some of the good parts of ActiveRecord and hopefully make the code a little more readable.

As a reminder, I'm working with these three tables:

class RawRoyaltyRecord < ApplicationRecord
  belongs_to :royalty_input_batch_partial
  belongs_to :track
  has_many :raw_royalty_records_sales
end

class RoyaltyInputBatchPartial < ApplicationRecord
  belongs_to :pro
  has_many :raw_royalty_records
end

class RawRoyaltyRecordsSale < ApplicationRecord
  belongs_to :raw_royalty_record
  belongs_to :sale
end

Ultimately, this is the big SQL query that inserts the new records and returns them:

WITH eligible_records AS ( -- This gets a list of existing track_id, customer, and sale_ids
    SELECT DISTINCT rrr.track_id, LOWER(rrr.customer) AS lower_customer, rrrs.sale_id
    FROM raw_royalty_records rrr
    INNER JOIN royalty_input_batch_partials ribp ON ribp.id = rrr.royalty_input_batch_partial_id
    INNER JOIN raw_royalty_records_sales rrrs ON rrrs.raw_royalty_record_id = rrr.id
    WHERE ribp.pro_id = 960
      AND rrr.track_id IS NOT NULL
),
inserted_records AS (
    INSERT INTO raw_royalty_records_sales (raw_royalty_record_id, sale_id, created_at, updated_at)
    SELECT DISTINCT rr.id, er.sale_id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
    FROM raw_royalty_records rr
      INNER JOIN royalty_input_batch_partials ribp ON rr.royalty_input_batch_partial_id = ribp.id
      LEFT OUTER JOIN raw_royalty_records_sales rrs ON rrs.raw_royalty_record_id = rr.id
      INNER JOIN eligible_records er ON er.track_id = rr.track_id and er.lower_customer = LOWER(rr.customer)
    WHERE ribp.pro_id = 960
      AND rrs.id IS NULL
      AND rr.track_id IS NOT NULL
    RETURNING *
)
SELECT ir.raw_royalty_record_id, ir.sale_id, rrr.track_id, rrr.customer
  FROM inserted_records ir INNER JOIN raw_royalty_records rrr
         ON rrr.id=ir.raw_royalty_record_id

The only piece of information that I need to pass in there is the pro_id, which is "960" in the example. It would be easy to use to use select_all in my Ruby code to accomplish this, but using ActiveRecord will be fun.

There is, however, one interesting limitation with ActiveRecord. I have to somehow base this entire query on a table if I want to be able to use the various methods to build the query. It's not a problem here because our actual query pulls from raw_royalty_records. I just need to add a join with the CTE inserted_records from above.

One other issue is that the CTEs need to be somehow built. The first one is pretty straightforward. But the second one is a weird insert and all that, I'm going to be creating some hand-coded SQL there or something.

Putting together a query like this requires starting from the end. Here's the final query:

SELECT ir.raw_royalty_record_id, ir.sale_id, rrr.track_id, rrr.customer
  FROM inserted_records ir INNER JOIN raw_royalty_records rrr
         ON rrr.id=ir.raw_royalty_record_id

Let's change that around so that we're pulling from raw_royalty_records:

SELECT rrr.id, ir.sale_id, rrr.track_id, rrr.customer
FROM raw_royalty_records rrr INNER JOIN inserted_records it
  ON rrr.id=ir.raw_royalty_record_id

Now, we can use ActiveRecord to build this:

query = RawRoyaltyRecord.joins("INNER JOIN inserted_records on inserted_records.raw_royalty_record_id = raw_royalty_records.id")

Of course, that's invalid as-is because "inserted_records" isn't a table. But if I look at the generated SQL using the to_sql method, we're on the right track:

SELECT "raw_royalty_records".* FROM "raw_royalty_records" INNER JOIN inserted_records on inserted_records.raw_royalty_record_id = raw_royalty_records.id

So, we need to add our CTEs to this query, and we do that using the with method. We can add them both at the same time, but I'm going to do one at a time because they're different and we need to look at those differences.

Here's the first CTE:

WITH eligible_records AS (
    SELECT DISTINCT rrr.track_id, LOWER(rrr.customer) AS lower_customer, rrrs.sale_id
    FROM raw_royalty_records rrr
    INNER JOIN royalty_input_batch_partials ribp ON ribp.id = rrr.royalty_input_batch_partial_id
    INNER JOIN raw_royalty_records_sales rrrs ON rrrs.raw_royalty_record_id = rrr.id
    WHERE ribp.pro_id = 960
      AND rrr.track_id IS NOT NULL
)

Turns out, we can use standard ActiveRecord to put this together.

   eligible_records_query =
     RawRoyaltyRecord
       .joins(:royalty_input_batch_partial)
       .where("royalty_input_batch_partials.pro_id": 960)
       .joins(:raw_royalty_records_sales)
       .where("raw_royalty_records.track_id is not null")
       .select(:track_id, "LOWER(raw_royalty_records.customer) AS lower_customer", "raw_royalty_records_sales.sale_id")
       .distinct

With that, we can add it as a CTE to our query:

  query = query.with(eligible_records: eligible_records_query)

It's actually really cool to be able to see this work in parts, and common table expressions built this way allow us to easily break the query up and test the individual parts.

The query above stands on its own. But how can we test it as a CTE?

Our original giant query had two CTEs as you may recall, with the second one being the insert. But we can pull it apart and use the select part of it to see this query working as a CTE.

But, first, let's make it really simple. Here's the simplest way you can test a CTE:

WITH eligible_records AS ( -- This gets a list of existing track_id, customer, and sale_ids
    SELECT DISTINCT rrr.track_id, LOWER(rrr.customer) AS lower_customer, rrrs.sale_id
    FROM raw_royalty_records rrr
    INNER JOIN royalty_input_batch_partials ribp ON ribp.id = rrr.royalty_input_batch_partial_id
    INNER JOIN raw_royalty_records_sales rrrs ON rrrs.raw_royalty_record_id = rrr.id
    WHERE ribp.pro_id = 960
      AND rrr.track_id IS NOT NULL
)
SELECT * FROM eligible_records;

How do we rubyize this? Not easily, because eligible_records doesn't exist outside of this query. But let's revisit the original giant query, specifically the second CTE:

inserted_records AS (
    INSERT INTO raw_royalty_records_sales (raw_royalty_record_id, sale_id, created_at, updated_at)
    SELECT DISTINCT rr.id, er.sale_id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
    FROM raw_royalty_records rr
      INNER JOIN royalty_input_batch_partials ribp ON rr.royalty_input_batch_partial_id = ribp.id
      LEFT OUTER JOIN raw_royalty_records_sales rrs ON rrs.raw_royalty_record_id = rr.id
      INNER JOIN eligible_records er ON er.track_id = rr.track_id and er.lower_customer = LOWER(rr.customer)
    WHERE ribp.pro_id = 960
      AND rrs.id IS NULL
      AND rr.track_id IS NOT NULL
    RETURNING *
)

Let's chop out the select statement and use it as the primary query to find some raw_royalty_records:

WITH eligible_records AS ( -- This gets a list of existing track_id, customer, and sale_ids
    SELECT DISTINCT rrr.track_id, LOWER(rrr.customer) AS lower_customer, rrrs.sale_id
    FROM raw_royalty_records rrr
    INNER JOIN royalty_input_batch_partials ribp ON ribp.id = rrr.royalty_input_batch_partial_id
    INNER JOIN raw_royalty_records_sales rrrs ON rrrs.raw_royalty_record_id = rrr.id
    WHERE ribp.pro_id = 960
      AND rrr.track_id IS NOT NULL
)
SELECT DISTINCT rrr.id, er.sale_id, rrr.customer
    FROM raw_royalty_records rrr
      INNER JOIN royalty_input_batch_partials ribp ON rrr.royalty_input_batch_partial_id = ribp.id
      LEFT OUTER JOIN raw_royalty_records_sales rrs ON rrs.raw_royalty_record_id = rrr.id
      INNER JOIN eligible_records er ON er.track_id = rrr.track_id and er.lower_customer = LOWER(rrr.customer)
    WHERE ribp.pro_id = 960
      AND rrs.id IS NULL
      AND rrr.track_id IS NOT NULL

That's a simplified version of the original giant query which will give us a list of raw_royalty_record ids along with sales ids. While we're at it, we'll grab the customer as well so we can see what's matching up.

Now, with the query in this shape, it's time to Rubyize it. We already have the CTE Rubyized, let's work on the main query.

With ActiveRecord, we have to start from a model. For this, we're pulling in ids from two different tables, but raw_royalty_records is the primary. I'll base this on RawRoyaltyRecord:

# See above for "eligible_records_query"
query = RawRoyaltyRecord
.joins(:royalty_input_batch_partial)
.where("royalty_input_batch_partials.pro_id": 960)
.joins("INNER JOIN eligible_records
  ON eligible_records.track_id = raw_royalty_records.track_id
    AND eligible_records.lower_customer = LOWER(raw_royalty_records.customer)")
.left_joins(:raw_royalty_records_sales)
.where("raw_royalty_records_sales.id is null")
.where("raw_royalty_records.track_id is not null")
.with(eligible_records: eligible_records_query)
.select("raw_royalty_records.id", "eligible_records.sale_id", "raw_royalty_records.customer")
.distinct

At this point, there's an argument that can be made that the Ruby code is more complicated than the SQL. And, in a way, it is. But it also is safe since we're bringing in a possibly dangerous parameter (the pro_id of "960) and still takes care of a lot of the grunt work of joining tables and all that. I think that it's easier to read. But those are opinions, nothing more.

Back to the original issue - How do I automatically add these records in a CTE and then get them back out? Is it worth the trouble?

Let's figure out how to do it first. The issue that we have is that the main query above needs to be changed:

remove "customer" as it won't be needed
add created_at and updated_at
remove the now-extraneous with as I'll keep them both at top level

At that point, it'll be ready to insert into raw_royalty_records_sales. The problem is that it's difficult to turn the select into an insert using standard ActiveRecord.

Again, here's the part of the query that performs the insert and select as a common table expression:

    INSERT INTO raw_royalty_records_sales (raw_royalty_record_id, sale_id, created_at, updated_at)
    SELECT DISTINCT rr.id, er.sale_id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
    FROM raw_royalty_records rr
      INNER JOIN royalty_input_batch_partials ribp ON rr.royalty_input_batch_partial_id = ribp.id
      LEFT OUTER JOIN raw_royalty_records_sales rrs ON rrs.raw_royalty_record_id = rr.id
      INNER JOIN eligible_records er ON er.track_id = rr.track_id and er.lower_customer = LOWER(rr.customer)
    WHERE ribp.pro_id = 960
      AND rrs.id IS NULL
      AND rr.track_id IS NOT NULL
    RETURNING *

This is basically the same select as above, only it's in the CTE. We can make this work.

Let's start with the final query. Ultimately, I'm going to pull in this information, which I'm just using to log the transaction:

SELECT rrr.id, ir.sale_id, rrr.track_id, rrr.customer
FROM raw_royalty_records rrr INNER JOIN inserted_records it
  ON rrr.id=ir.raw_royalty_record_id

This is a denormalized list of sales, tracks, and customers from the raw_royalty_records and friends tables (it's possible for one raw_royalty_record to be tied to multiple sales because the reporting doesn't give enough information sometimes).

select_for_insert_query = RawRoyaltyRecord
.joins(:royalty_input_batch_partial)
.where("royalty_input_batch_partials.pro_id": 960)
.joins("INNER JOIN eligible_records
  ON eligible_records.track_id = raw_royalty_records.track_id
    AND eligible_records.lower_customer = LOWER(raw_royalty_records.customer)")
.left_joins(:raw_royalty_records_sales)
.where("raw_royalty_records_sales.id is null")
.where("raw_royalty_records.track_id is not null")
.select("raw_royalty_records.id AS raw_royalty_record_id", "eligible_records.sale_id AS sale_id", "CURRENT_TIMESTAMP AS created_at", "CURRENT_TIMESTAMP AS updated_at")
.distinct

This is maybe a little ugly, but I can take the SQL from that, wrap it with INSERT INTO...RETURNING *, and I've got a CTE.

One note here - the CTE cannot just be a string. In this case, we'll just have to wrap the string in Arel.sql:

insert_query = Arel.sql(
  "INSERT INTO raw_royalty_records_sales (raw_royalty_record_id, sale_id, created_at, updated_at)\n" +
  select_for_insert_query.to_sql +
  "\nRETURNING *"
)

With that, I can add both CTEs to a basic query. I'll call that one "inserted_records".

query = RawRoyaltyRecord.joins("
  INNER JOIN inserted_records
    ON inserted_records.raw_royalty_record_id = raw_royalty_records.id
  ")
      .select("raw_royalty_records.id",
              "inserted_records.sale_id",
              "raw_royalty_records.track_id",
              "raw_royalty_records.customer")
      .distinct
      .with(
         eligible_records: eligible_records_query,
         inserted_records: insert_query
      )

And with that, this monstrosity works.

WITH "eligible_records" AS (SELECT DISTINCT "raw_royalty_records"."track_id", LOWER(raw_royalty_records.customer) AS lower_customer, "raw_royalty_records_sales"."sale_id" FROM "raw_royalty_records" INNER JOIN "royalty_input_batch_partials" ON "royalty_input_batch_partials"."id" = "raw_royalty_records"."royalty_input_batch_partial_id" INNER JOIN "raw_royalty_records_sales" ON "raw_royalty_records_sales"."raw_royalty_record_id" = "raw_royalty_records"."id" WHERE "royalty_input_batch_partials"."pro_id" = 960 AND (raw_royalty_records.track_id is not null)), "inserted_records" AS (INSERT INTO raw_royalty_records_sales (raw_royalty_record_id, sale_id, created_at, updated_at)
WITH "eligible_records" AS (SELECT DISTINCT "raw_royalty_records"."track_id", LOWER(raw_royalty_records.customer) AS lower_customer, "raw_royalty_records_sales"."sale_id" FROM "raw_royalty_records" INNER JOIN "royalty_input_batch_partials" ON "royalty_input_batch_partials"."id" = "raw_royalty_records"."royalty_input_batch_partial_id" INNER JOIN "raw_royalty_records_sales" ON "raw_royalty_records_sales"."raw_royalty_record_id" = "raw_royalty_records"."id" WHERE "royalty_input_batch_partials"."pro_id" = 960 AND (raw_royalty_records.track_id is not null)) SELECT DISTINCT raw_royalty_records.id AS raw_royalty_record_id, eligible_records.sale_id AS sale_id, CURRENT_TIMESTAMP AS created_at, CURRENT_TIMESTAMP AS updated_at FROM "raw_royalty_records" INNER JOIN "royalty_input_batch_partials" ON "royalty_input_batch_partials"."id" = "raw_royalty_records"."royalty_input_batch_partial_id" LEFT OUTER JOIN "raw_royalty_records_sales" ON "raw_royalty_records_sales"."raw_royalty_record_id" = "raw_royalty_records"."id" INNER JOIN eligible_records
  ON eligible_records.track_id = raw_royalty_records.track_id
    AND eligible_records.lower_customer = LOWER(raw_royalty_records.customer) WHERE "royalty_input_batch_partials"."pro_id" = 960 AND (raw_royalty_records_sales.id is null) AND (raw_royalty_records.track_id is not null)
RETURNING *) SELECT DISTINCT "raw_royalty_records"."id", "inserted_records"."sale_id", "raw_royalty_records"."track_id", "raw_royalty_records"."customer" FROM "raw_royalty_records" INNER JOIN inserted_records
    ON inserted_records.raw_royalty_record_id = raw_royalty_records.id

But is it worth it? I can short-cut this and return the raw_royalty_records_sales, or I can do it the way I've done it above. All I want the records for is to log them, and my preference is to log them in the format:

track id (and maybe title)
customer
list of new raw_royalty_record ids
list of sale ids (and maybe the customer info attached to the sales for confirmation)

Given the denormalized nature of the data, I'm still going to have to do some magic in Ruby to get this log format. I could simply use pluck to pull the somewhat raw data out and handle it myself.

The cool thing about using the railsy way of building these queries is that I can swap raw_royalty_records out for raw_royalty_records_sales easily:

query = RawRoyaltyRecordsSale
      .from("inserted_records")
      .joins("INNER JOIN raw_royalty_records ON raw_royalty_records.id=inserted_records.raw_royalty_record_id")
      .select("inserted_records.id",
              "inserted_records.raw_royalty_record_id",
              "inserted_records.sale_id",
              "raw_royalty_records.track_id",
              "raw_royalty_records.customer")
      .distinct
      .with(
         eligible_records: eligible_records_query,
         inserted_records: insert_query
      )

One issue with this is that - regardless of how I do it - find_each is not going to work here. So I have to be realistic about the amount of data that I'm loading in. With the PRO that I'm testing with I have around 4000 created records. Not too many, definitely more than I want to risk an N+1 with. Plus, it's difficult to say how this may progress in the future as the database grows.

Anyway, that's how to use a common table expression to insert new records while simultaneously instantiating objects based on them.

Inserting and Selecting New Records - One Query

Michael Chaney — Fri, 28 Jun 2024 23:16:44 +0000

Just found something very interesting in PostgreSQL, thanks to Claude.

I have a really nasty query on some huge tables. Here's the basic layout that we're concerned with:

class RawRoyaltyRecord < ApplicationRecord
  belongs_to :royalty_input_batch_partial
  belongs_to :track
  has_many :raw_royalty_records_sales
end

class RoyaltyInputBatchPartial < ApplicationRecord
  belongs_to :pro
  has_many :raw_royalty_records
end

class RawRoyaltyRecordsSale < ApplicationRecord
  belongs_to :raw_royalty_record
  belongs_to :sale
end

Don't worry about "Pro" and "Sale" - we just need their IDs.

The concept is this. We get spreadsheets in every quarter or so with new raw_royalty_records. I have to match them to sales, which can be a bit tricky. Because of this trickiness, the simplest way to match them is to look at previously matched raw_royalty_records from the same Pro (through the royalty_input_batch_partials table) that have matching track_id and customer values.

Note that the customer value is stripped of extraneous leading/trailing spaces before being saved the in the database, and we have an index on lower(customer) to allow us to search quickly for them.

This thing was dog slow. It started out fine, but as we've added millions of records it got slower.

The way this worked was that we'd go get a list of prior matched raw_royalty_records and create a hash to look up the sale_ids based on the customer and track_id:

    @pro = Pro.find(params[:pro_id])

    @raw_royalty_records = @pro.raw_royalty_records
      .sans_sales
      .where("track_id is not null")
      .includes(:raw_royalty_records_sales, track: :library)

    @previously_matched_raw_royalty_records =
      @pro.raw_royalty_records.with_sales.where("track_id is not null")
        .select(:customer, :track_id, :id).distinct.includes(:sales).each_with_object({}) do |rrr, hash|
      hash[rrr.track_id] ||= {}
      hash[rrr.track_id][rrr.customer.strip.downcase] = rrr.sales
    end

    # Later on, looping through @raw_royalty_records
        @matches[track_id] ||= {}
        previously_matched = @previously_matched_raw_royalty_records.dig(track_id, customer_key)
        if previously_matched
          @matches[track_id][customer_key] = { match_type: 'previously_matched', sales: previously_matched }
          #break
        end

Not the most beautiful code I've written, but you get the idea.

That worked great when there were a couple hundred thousand records, but now we have a hundred and something thousand unmatched records, and many matched records. Looking in the database, for one particular PRO that I'm testing with has around 287,000 records total, with 259,000 not connected to a sale. So, our query there was pulling in 259,000 records total along with some associated records. That requires CPU and memory. Way too much.

But I realized quickly that I can match up the records in an SQL query. It's ugly, but using a common table expression takes a lot of the complexity out and works well because it needs to compute certain expressions only a single time.

WITH eligible_records AS (
    SELECT DISTINCT rrr.track_id, LOWER(rrr.customer) AS lower_customer
    FROM raw_royalty_records rrr
    INNER JOIN royalty_input_batch_partials ribp ON ribp.id = rrr.royalty_input_batch_partial_id
    INNER JOIN raw_royalty_records_sales rrrs ON rrrs.raw_royalty_record_id = rrr.id
    WHERE ribp.pro_id = 960
      AND rrr.track_id IS NOT NULL
)
SELECT rr.*
FROM raw_royalty_records rr
INNER JOIN royalty_input_batch_partials ribp ON rr.royalty_input_batch_partial_id = ribp.id
LEFT OUTER JOIN raw_royalty_records_sales rrs ON rrs.raw_royalty_record_id = rr.id
WHERE ribp.pro_id = 960
  AND rrs.id IS NULL
  AND rr.track_id IS NOT NULL
  AND EXISTS (
      SELECT 1
      FROM eligible_records er
      WHERE er.track_id = rr.track_id
        AND er.lower_customer = LOWER(rr.customer)
  );

That's awesome, but what I really want to do is just create the new records in raw_royalty_records_sales. But I also want to get a list of what was just created. Technically, I could do that by saving the highest id in raw_royalty_records_sales, then looking at the row count. This thing shouldn't run overlapping (famous last words), but I'm not going to rely on such silliness.

This is where common table expressions shine.

WITH eligible_records AS ( -- This gets a list of existing track_id, customer, and sale_ids
    SELECT DISTINCT rrr.track_id, LOWER(rrr.customer) AS lower_customer, rrrs.sale_id
    FROM raw_royalty_records rrr
    INNER JOIN royalty_input_batch_partials ribp ON ribp.id = rrr.royalty_input_batch_partial_id
    INNER JOIN raw_royalty_records_sales rrrs ON rrrs.raw_royalty_record_id = rrr.id
    WHERE ribp.pro_id = 960
      AND rrr.track_id IS NOT NULL
),
inserted_records AS (
INSERT INTO raw_royalty_records_sales (raw_royalty_record_id, sale_id, created_at, updated_at)
SELECT DISTINCT rr.id, er.sale_id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
FROM raw_royalty_records rr
INNER JOIN royalty_input_batch_partials ribp ON rr.royalty_input_batch_partial_id = ribp.id
LEFT OUTER JOIN raw_royalty_records_sales rrs ON rrs.raw_royalty_record_id = rr.id
INNER JOIN eligible_records er ON er.track_id = rr.track_id and er.lower_customer = LOWER(rr.customer)
WHERE ribp.pro_id = 960
  AND rrs.id IS NULL
  AND rr.track_id IS NOT NULL
RETURNING *
)
SELECT ir.raw_royalty_record_id, ir.sale_id, rrr.track_id, rrr.customer
  FROM inserted_records ir INNER JOIN raw_royalty_records rrr
         ON rrr.id=ir.raw_royalty_record_id

That's a very similar query, but now it's using INSERT INTO...RETURNING * - in a CTE. So the actual query that is run selects from the items that were just inserted, joins them with raw_royalty_records record to get the track_id and customer, and returns the records in a normalized fashion.

I have 3448 raw_royalty_records records that can match up like this. Queries that took multiple seconds now take around a second, including the insert. This is a huge win.

Unfortunately, I'm not really using a lot of the cool Railsy ActiveRecord goodies to make this more readable, but that's what good documentation covers. I've sped this up by a couple of orders of magnitude and made it use 1% of the memory of the original. This is a huge win. I can handle an ugly query with those numbers.

Refactoring fix_encoding

Michael Chaney — Tue, 25 Jun 2024 16:10:21 +0000

I've been writing about Unicode on Twitter over the last week, and specifically handling Unicode in Ruby. Ruby has robust Unicode support, along with robust support for the older code pages.

In my work in the music publishing industry I have to write code to process all manner of spreadsheets, typically in the form of CSV files. CSV files can be a crap shoot in terms of encoding. Thankfully, everything I've had to deal with up to now has been either Unicode or Latin-1 (ISO-8859-1) or the Windows-1252 variant.

I created a piece of code some years back to handle the issue of determining the encoding of a file and coercing the bytes into a standard Unicode format, specifically UTF-8.

module FixEncoding
  def FixEncoding.fix_encoding(str)
    # The "b" method returns a copied string with encoding ASCII-8BIT
    str = str.b
    # Strip UTF-8 BOM if it's at start of file
    if str =~ /\A\xEF\xBB\xBF/n
      str = str.gsub(/\A\xEF\xBB\xBF/n, '')
    end
    if str =~ /([\xc0-\xff][\x80-\xbf]{1,3})+/n
      # String has actual UTF-8 characters
      str.force_encoding('UTF-8')
    elsif str =~ /[\x80-\xff]/n
      # Get rid of Microsoft stupid quotes
      if str =~ /[\x82\x8b\x91\x92\x9b\xb4\x84\x93\x94]/n
        str = str.tr("\x82\x8b\x91\x92\x9b\xb4\x84\x93\x94".b, "''''''\"\"\"")
      end
      # There was no UTF-8, but there are high characters.  Assume to
      # be Latin-1, and then convert to UTF-8
      str.force_encoding('ISO-8859-1').encode('UTF-8')
    else
      # No high characters, just mark as UTF-8
      str.force_encoding('UTF-8')
    end
  end
end

There it is in all its glory. I realized after looking at it that it's in not great shape. I'm going to refactor it and talk about my decisions.

There are a few things that stick out:

I'm making extensive use of =~ instead of using the String#match. In Ruby, =~ causes a performance hit due to the fact that it sets various globals (a la Perl) after the match.
I'm using regular expressions where I don't need to - specifically when checking for the BOM (Unicode byte order mark) at the start of the string. Some of these strings are many megabytes, so there can be performance gains.
I realized that I'm using a regular expression to check for high (128 and above) characters. Ruby has String#ascii_only? to do that.
The logic can be changed around to handle the faster cases first.

So, let's first talk about what this does.

    # The "b" method returns a copied string with encoding ASCII-8BIT
    str = str.b

I'm telling you right there - this gets a copy of the string with the encoding set to ASCII-8BIT. That's basically "no encoding", which is what we want. The string is mostly a string of boring bytes, where the collation order is the character code and there are 26 upper and lowercase letters. This is what Ruby essentially used in version 1.8.

With the string in this encoding, we can look at individual bytes regardless of whether they're part of a UTF-8 set.

    # Strip UTF-8 BOM if it's at start of file
    if str =~ /\A\xEF\xBB\xBF/n
      str = str.gsub(/\A\xEF\xBB\xBF/n, '')
    end

(note that the "n" flag on the regular expression is a Rubyism that makes the regular expression have the ASCII-8BIT encoding)

The Unicode Byte Order Mark can optionally occur at the start of a file. Microsoft software adds these, and some other software has no idea what they are. If you understand UTF-8 encoding you can see that this BOM is really character FEFF, which oddly is a zero-width non-breaking space.

The BOM isn't needed, and you'll notice that I do nothing but remove it. That's because I've received files that have a BOM at the start, and Latin-1 characters later on in the same file. There's no reason to "believe" the BOM.

    if str =~ /([\xc0-\xff][\x80-\xbf]{1,3})+/n
      # String has actual UTF-8 characters
      str.force_encoding('UTF-8')

Now we're getting to the meat of it. That regexp will find real UTF-8 characters in the binary byte stream. It'll really find the first one, but that's all I care about. I could make that regexp more precise, although it's of limited value to do so.

In this tweet I cover the format of a UTF-8 character in-depth. Here are the basics, though. a UTF-8 character will be of one of these forms:

0xxxxxxx
110xxxxx 10xxxxxx
1110xxxx 10xxxxxx 10xxxxxx
11110xxx 10xxxxxx 10xxxxxx 10xxxxxx

The first form is just ASCII. Any ordinal value above 127 will always occupy two, three, or four bytes in UTF-8 and will be one of the forms shown above. The first character will always be in the ranges (in hex) C0-DF, E0-EF, or F0-F7. The subsequent characters will always be in the range 80-BF. Having a character in the first range that's not followed by a character in the 80-BF range would be invalid, and having a character in the 80-BF range that's not preceded by one of the first characters or another 80-BF is also not valid.

In the referenced tweet I include a large regexp that will determine if a given string is fully valid as UTF-8:

str =~ /\A(?:\xef\xbb\xbf)?
  (?:
  (?:[\x00-\x7f]) |
  (?:[\xc0-\xdf][\x80-\xbf]) |
  (?:[\xe0-\xef][\x80-\xbf]{2}) |
  (?:[\xf0-\xf7][\x80-\xbf]{3})
  )*
\z/nx

That's a beauty, but it's overkill for what I'm doing here. I'll assume that if there's a single valid UTF-8 character then the string is UTF-8. I'm willing to take that risk.

The only change that I see is that the first character should match [\xc0-\xf7] instead of [\xc0-\xff] (note the final "7" in the former). I can also use "match" here to speed it up.

    elsif str =~ /[\x80-\xff]/n
      # Get rid of Microsoft stupid quotes
      if str =~ /[\x82\x8b\x91\x92\x9b\xb4\x84\x93\x94]/n
        str = str.tr("\x82\x8b\x91\x92\x9b\xb4\x84\x93\x94".b, "''''''\"\"\"")
      end
      # There was no UTF-8, but there are high characters.  Assume to
      # be Latin-1, and then convert to UTF-8
      str.force_encoding('ISO-8859-1').encode('UTF-8')

Okay, lots going on here. We first check if the string has any "high characters", defined as a character code greater than 127. Put another way - the high bit is set. Standard ASCII goes from 0 to 127. When I was a kid the high bit was often used as a parity bit, which isn't needed now. For most applications. I'm sure someone's still using a parity bit.

We've already ruled out this being a UTF-8 string, so if there are high characters we're in either Latin-1 or its inbred cousin Windows-1252.

In Latin-1 the character codes 80-9F were reserved as extended "control codes", kind of mirroring the control code concept in the first 32 ASCII characters. I'm not sure they were ever used as such, and interestingly the character table in Wikipedia simply shows them as "undefined".

Microsoft and Apple both had an idea of what to put in that range, and this caused calamity 20+ years ago as a text file that looked great on Windows or Mac would be full of weird question marks when viewed elsewhere.

Microsoft referred to this "feature" as "smart quotes", so we usually referred to them as "stupid quotes" (as a side note, if you think I'm the only one who refers to them as such Github Copilot knew what to do when I created the "has_stupid_quotes?" method). There are a few other characters in there as well which don't have Latin-1 equivalents, including the Euro sign "€".

Anyway, the next chunk replaces the fancy quote characters with the standard ASCII equivalents.

One possible change to this piece of code would be to force the encoding to Windows-1252 and then transcode to UTF-8, which would preserve the fancy quotation marks and apostrophes. I don't do that simply because I prefer to standardize quote marks to the ASCII versions. In some other contexts that might be a less preferred choice.

Here, I use a regular expression to find them and, if found, use the "tr" method to replace them.

        str = str.tr("\x82\x8b\x91\x92\x9b\xb4\x84\x93\x94".b, "''''''\"\"\"")

Finally, I force the string to Latin-1 encoding, then transcode to UTF-8:

      str.force_encoding('ISO-8859-1').encode('UTF-8')

A better way to do this would be to check for the presence of characters in the 80-9F range and use Windows-1252 instead of ISO-8859-1 (aka "Latin-1"). That would pick up Euro signs and such.

In the last part, there were no high characters at all, so we force the encoding to be UTF-8. A regular ASCII string is also a standard UTF-8 or Latin-1 string as well.

    else
      # No high characters, just mark as UTF-8
      str.force_encoding('UTF-8')

So, let's turn this on its head. First, we need to start out with the check for high characters, and there's good reason for that. Ruby has a built-in method "String#ascii_only?". I'm going to give some high praise here - this method is written how I would write it. Go ahead, have a look:

https://github.com/ruby/ruby/blob/bed34b3a52afde6d98fcef19e199d0af293577be/string.c#L618

That's actually the opposite - "search_nonascii", but that's ultimately what "ascii_only?" uses. Why do I like it? It is as fast as the CPU can perform this check. It looks at each word and sees if any of the high bits are set. Instead of looking byte by byte, it looks at entire 32 or 64-bit words.

So, that's way preferable to using a regular expression. Better yet, if the string has no high characters there's no reason to even continue with the rest of this.

module FixEncoding
  def FixEncoding.fix_encoding(str)
    if str.ascii_only?
      return str.force_encoding('UTF-8')
    else
      str = str.b
      # Rest of code
    end
  end
end

Putting that check first will short-circuit the rest of our checks if there are no high characters. And since that's the fastest check we have, it should speed this up dramatically in that case. Note that this also precludes the string copy, so there's an even bigger win.

Next, we need to strip the BOM. This can be done without a regular expression to speed it up. Here's the old way again:

    if str =~ /\A\xEF\xBB\xBF/n
      str = str.gsub(/\A\xEF\xBB\xBF/n, '')
    end

We can use String#byteslice in both places to make this faster:

  def FixEncoding.remove_bom(str)
    if str.byteslice(0..2) == "\xEF\xBB\xBF".b
      return str.byteslice(3..-1)
    else
      return str
    end
  end

So, this is very different. First, we're slicing the first 3 bytes off and comparing to the BOM (also as a binary string). If they match, we replace str with all but the first three bytes of str. Both parts of this are much faster than the original, and String#byteslice is the fastest way to handle it.

Next, we check for UTF-8 characters:

  def FixEncoding.has_utf8?(str)
    str.match(/[\xc0-\xf7][\x80-\xbf]/n)
  end

This is mostly the same, but I've simplified the regexp by removing the extraneous capture and repetition.

Next, we can check for stupid quotes:

  def FixEncoding.has_stupid_quotes?(str)
    str.match(/[\x82\x8b\x91\x92\x9b\xb4\x84\x93\x94]/n)
  end

and replace them if we find them:

  def FixEncoding.replace_stupid_quotes(str)
    str.tr("\x82\x8b\x91\x92\x9b\xb4\x84\x93\x94".b, "''''''\"\"\"")
  end

This remains unchanged, save for moving it to its own function.

The final piece of the puzzle is to determine what the likely encoding is, force it to that encoding, then transcode to UTF-8.

  def FixEncoding.has_win1252?(str)
    str.match(/[\x80-\x9f]/n)
  end

  def FixEncoding.likely_8bit_encoding(str)
    if str.ascii_only?
      "ASCII-8BIT"
    elsif has_win1252?(str)
      "WINDOWS-1252"
    else
      "ISO-8859-1"
    end
  end

Note that we again do the "ascii_only?" check. Why? I've replaced the high quote marks with standard ASCII equivalents, so we may well have an ASCII string again. That's faster than the regular expression check, so we're again looking for a short-circuit.

With that, we can write our final helper:

  def FixEncoding.transcode_to_utf8(str)
    str.encode("UTF-8", likely_8bit_encoding(str))
  end

Note that using the encode method like that is the equivalent of using force_encoding with the second argument followed by encode with the first argument:

   str.force_encoding(likely_8bit_encoding(str)).encode("UTF-8")

At this point, our fix_encoding function is simpler, fully testable, and pretty much all acting at the same semantic level:

  def FixEncoding.fix_encoding(str)
    if str.ascii_only?
      return str.force_encoding('UTF-8')
    else
      str = str.b

      str = remove_bom(str)

      if has_utf8?(str)
        return str.force_encoding('UTF-8')
      else
        if has_stupid_quotes?(str)
          str = replace_stupid_quotes(str)
        end

        return transcode_to_utf8(str)
      end
    end
  end

The entire thing is longer now, but in reality there's no more code that before and what code there is will run faster. While I don't normally worry too much about the speed of Ruby code this is often used in processing multi-megabyte files where any speed improvement is appreciated.

The complete code is available here:

https://gist.github.com/mdchaney/e2b05eafab81cbdc4dfed6dd2f8e69a6

That's not tested, though. Next time, I'll create some tests and find out how I did.