In January 2019, I finished my first huge project as a developer - the first time that I really had to architect a system where the influence of each individual piece would extend far beyond its own individual scope. It was also a really difficult project. For the most part, I think it turned out great! I’ve been able to make tons of modifications to the code over the years, as LDL, LLA, and LGL continue to surprise me with the most random requirements like points carrying from one split to another, both teams forfeiting matches, fun new points systems, and point changes mid-split. And I’ve added new features like user predictions, streak displays, and an additional Cargo table storing computed standings.

And in some ways, every now and then, I’ve realized it’s been…not so great. This post discusses one of the ways I really, really, really chose some incorrect options and ended up with a giant mess, which I’m now finally cleaning up.

(Note: All code in this post was originally written by me for Leaguepedia and is licensed under CC BY-SA 3.0.)

Quick note: what’s a tab?

After my final revision of this article before publishing I’ve realized that I’ve used the word tab extensively in two different meanings. This is regrettably rather unavoidable, so I’m warning you here, and hopefully it won’t be too confusing.

1. tab (noun) - a navigational element, meaning a wiki page. One of a set of tabs in TabsHeader.
2. tab (noun) - a theoretical construct that I defined in my Cargo table which loosely correlates to meaning (1) but often is contrasted to it. This definition will be significantly expanded upon later.

Quick background: keys in SQL/Cargo

Before we start, I first have to tell you about keys in SQL/Cargo, in case you’re not familiar with these. (Pretty sure I haven’t talked about these in any post yet.) If you are, just skip this section.

Behind the scenes. Cargo has this field called row._ID in every table. This field is known as the “primary key” of the table, and it’s all sorts of important because it’s guaranteed to be unique for every table. The database really, really cares about that.

So every time I say I’m making a key, I’m actually just pretending I’m making a key; in reality only row._ID is a key. This is sort of terrible and scary and bad, but it’s the best we can do because we’re working with the software we have. So yeah.

Second point. I’m going to toss around the phrase “Foreign key” a lot. What does this mean? If I have a table called Teams and it has primary key TeamIdNumber, and then I have another table called Players and I want to put their team, I might store it by ID number, then I have Players.TeamIdNumber, now this column is called a “foreign key.” Because it’s “foreign” to the Players table.

Often (but not always!), when you join two tables together, you are joining on one table’s primary key to a copy of that primary key in the other table, where it’s a foreign key. So like if you’re joining a PickBanGame to a ScoreboardGame, the PickBan table has its primary key of PickBanGameKey and a foreign key of ScoreboardGameKey. And the Scoreboard table has its primary key of ScoreboardGameKey. And then we join them ON PickBan.ScoreboardGameKey = Scoreboard.ScoreboardGameKey. But, maybe BOTH of these have a foreign key which is the MatchScheduleGameKey, and then we can just join them on THAT key: PickBan.MatchScheduleGameKey = Scoreboard.MatchScheduleGameKey.

In fact, in this article, that final situation is the one we’d like to arrive at, where everything just uses the MatchScheduleGame key and everyone is happy and everything is simple.

If none of this made sense to you, maybe 50% of this post won’t make sense to you. If that’s ok with you, don’t worry about it. If that’s really upsetting, try searching like “SQL primary key” or “SQL joins” etc. Or complain to me and maybe I’ll make a longer post about this topic.

The problem

In short: I made a couple bad decisions related to primary / foreign keys that made my database design pretty disastrously annoying to work with. And unfortunately I was so familiar with my database design that I didn’t really notice until I took a couple months’ break from constant editing to recuperate after not really taking a day off for like 2.5 years straight (yikes that sounds so much worse when I write it out lol). The situation described above where everything has a foreign key of GameId was impossible to arrive at, everything had terrible names, and you had to constantly refer to documentation to figure out how to query anything at all in order to get anything done (or be me (or ask me for help)).

The current (post-fixing-everything) state of Module:MatchSchedule, where I’ve now added all of the old names, all of the new names, and everything that needs to exist for convenience tells the story:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

function h.setGameIds(thisgame, match)
	thisgame.GameId = ('%s_%s_%s_%s'):format(
		match.OverviewPage,
		match.Tab,
		match.N_MatchInTab,
		thisgame.N_GameInMatch
	)
	thisgame.GameID_Wiki = ('%s_%s_%s_%s'):format(
		match.OverviewPage,
		match.Tab,
		match.N_MatchInTab,
		thisgame.N_GameInMatch
	)
	thisgame.ScoreboardID_Wiki = ('%s_%s_%s_%s'):format(
		match.OverviewPage,
		match.Tab,
		match.N_MatchInTab,
		thisgame.N_GameInMatch
	)
	thisgame.MatchId = ('%s_%s_%s'):format(
		match.OverviewPage,
		match.Tab,
		match.N_MatchInTab
	)
end

Actually it doesn’t quite, because there are also fields UniqueMatch, UniqueLine, and ScoreboardID_Riot (unused). Maybe we should look at the Cargo declaration of MatchScheduleGame:

1
2
3
4
5
6
7
8
9

	{ field = "ScoreboardID_Wiki", type = "String", desc = "DEPRECATED" },
	{ field = "ScoreboardID_Riot", type = "String", desc = "(unused) DEPRECATED" },
	
	{ field = "GameID_Wiki", type = "String", desc = "OverviewPage_Tab_MatchInTab_GameInMatch -- DEPRECATED" },
	{ field = "GameId", type = "String", desc = "OverviewPage_Tab_MatchInTab_GameInMatch" },

	{ field = "UniqueMatch", type = "String", desc = "Can be joined to MatchSchedule.UniqueMatch -- DEPRECATED" },
	{ field = "MatchId", type = "String", desc = "Join to MS.MatchId" },
	{ field = "UniqueLine", type = "String", desc = "DEPRECATED" },

Excellent, great. You get the idea.

The road to Hell

Let’s talk about why everything was the worst. As I mentioned in the introduction, there were some really difficult problems to solve.

ID_Wiki vs ID_Riot

I was originally planning to store some identifier derived from Riot’s match histories as an identifier somehow superior to the wiki key. Especially if our API was public, I figured anything based on wiki article titles would be not a great key (it’s really not), but in retrospect nothing else is better because:

• LPL/LDL exist and use QQ, not Riot match history links
• Lots of stuff has just schedules and no pick-bans or scoreboards, and so no identifiers other than wiki ID
• Or just has pick-bans and schedules, so same thing

In other words, the entire reason for the wiki API being desirable in the first place is that we have more data than just what’s available by Riot’s API, because we track third-party stuff, older stuff, and Tencent stuff. So while I definitely DO want to have some way of looking up games by Riot match history available (the match history link is a column in both MatchScheduleGame and ScoreboardGames), it should not compromise the naming convention of my actual keys.

UniqueMatch & UniqueLine vs MatchId & GameId

So why was this shit called…this shit?

Once upon a time…not that long ago, really, we’re talking like mid-2018, there were a lot of issues with Cargo incorrectly storing duplicate rows of tables. In other words, Cargo would inexplicably have two copies of a row that I had only stored one copy of on a page. These issues were due to a bug in the software, and there were a few ways I could deal with them:

• Rebuild the tables, like, constantly
• Blank edit pages, like, constantly
• Put group by conditions in every single query so that the duplicate entries didn’t affect your output and you didn’t care about them

(Note, however, that the last one makes SUM, COUNT, etc, off-limits for you to use.)

Now, these duplicate-row-saving issues have since been fixed, but their impact on my naming conventions - and even some of my queries - still remains. Namely (haha), most tables have fields called UniqueLine in them that serve as a primary key. They’re usually just like {{PAGENAME}}_{SOME_INDEXING_VAR} (although it can get way more complicated than that). And they are still useful, for a few reasons:

• In error messages, they can lead a user to find out which line has bad data
• When writing metadata queries, or debugging “regular” queries, they can inform the user about the location of data
• In stark contrast to their original usage, now they make great arguments to COUNT(DISTINCT())
• They can be used to join two tables together, if they are used as foreign keys in a “child” table

The last one is the one of note here. A couple points arise:

1. I cannot have two columns both called UniqueLine in the same table; and even if I could, ew
2. Therefore, I will want to slightly depart from this naming convention when using a UniqueLine key as a foreign key, and call it instead something along the lines of UniqueMatch.
3. If I did this greedily, in MatchSchedule I might have UniqueLine. Then in MatchScheduleGame I also have UniqueLine, meaning “the game.” Plus I also add the foreign key UniqueMatch this arrives at the somewhat unpleasant situation of having MatchSchedule.UniqueLine & MatchScheduleGame.UniqueLine referring to two distinct entities.
4. So I should probably call the key of MatchSchedule by the name UniqueMatch in both tables.
5. However UniqueLine is unambiguous in the lowest-common-denominator table, which is MatchScheduleGame - there is no other table where it will appear. So it is safe to use this primary key there.

So now we know the (doomed) etymology of the field names UniqueMatch and UniqueLine. (I never made a field called UniqueGame, though arguably (definitely) MatchScheduleGame.UniqueLine should have been named such instead.)

An extra pitfall of UniqueMatch

By the way, there is one other sadness of UniqueMatch that arose because I wasn’t deliberately optimizing for ease of joining arbitrary sets of tables in this “ecosystem” and due to the history of the UniqueLine key.

The GameId (by whichever name it’s known) is by necessity agnostic to the page it’s physically stored on: that is, if the particular tournament happens to have its data broken up into 5 pages, or 1, or 2, the GameId key doesn’t change: It’s named after OverviewPage, TabName, etc. However, as I mentioned, the UniqueLine keys historically have always been named after the stored page name.

When I was creating the UniqueMatch key, I wasn’t considering that it should be joinable to other tables, so I didn’t bother to name it within the scheme that GameId uses (with the page part being relegated to OverviewPage only); instead I followed the UniqueLine scheme, where it’s based on physical page name (again, quite deliberately, because this is the easiest and safest way to guarantee key uniqueness with globally-consistent logic - and I haven’t mentioned this anywhere else but I had to do a lot of work to make sure that my OverviewPage was both well-defined and unique in all cases! Any situation where this got violated could be disastrous).

But because obviously there’s no way any other table (pick-bans, scoreboards) can know which page the data is stored on, there’s going to be no way for it to join to MatchSchedule without going through MatchScheduleGame, even if the GameId key is known, as is the case with the PicksAndBans table.

Note that this ONLY affected the (potential) join between PicksAndBansS7 and MatchSchedule, for reasons that we’ll see in the next section.

Fortunately, the fix is simple: just use OverviewPage instead of {{PAGENAME}} as the page part of the MatchId key, and so all tables now have a MatchId key.

ScoreboardId vs GameId

Now that we understand all of the purely-naming-and-nothing-else sins, what about the structural sin: Why on earth did ScoreboardGames need to be joined to everything else via MatchScheduleGame? Couldn’t that just use the same GameId or whatever you wanna call it as everything else?

It’s not “Just”

Ok first of all IT’S NEVER “JUST!” NOT EVER! DO NOT EVER SAY “CAN’T YOU JUST” TO ANY DEVELOPER EVER!

Whew, I’m glad we got that out of the way. Here’s what I mean.

Here’s an (extended) quote from my original “Tournament Cargo” post, which currently is just a user page on the wiki (but eventually I’ll import it to this blog):

So I need two things with different requirements:

• A way to associate games together that doesn’t involve the name of the page or the number of the game within the page.
• A way to order games that DOES involve both name of the page (and order within the “group” of pages) and also the number of the game within the page.

This is an INSANELY difficult problem by the way! Creating & maintaining internally-consistent primary/foreign-key indexing across disparate data sets when the maintainers of said data sets are volunteers with whom you have no means of communication, and who must never be aware that these keys exist, let alone what they are, is VERY HARD! And then add on top of the page-size limits that we have, and ohhhhhhh my god this is not an easy thing to do. I almost gave up entirely on this project multiple times because I thought it was literally not possible.

I also struggled with this for a while. I don’t remember any particular aha! moment but here’s what I ultimately decided on, probably sometime in September-October ish:

• Break up events into “Rounds” or “Phases” or some smaller unit, each given a distinct name. For example, “Week 1” or “Quarterfinals.”
• Do not permit these units to be split across pages.
• Denote the event name by the title of the overview page, for example “NA LCS/2018 Season/Spring Season” but not “NA LCS/2018 Season/Spring Season/Picks and Bans”
• Number matches/games by their number within the phase/round/whatever
• Join based on the overview page, the NAME of the phase/round/whatever, and the number of the match witihn that phase/round/whatever
• Order based on page number and number of phase/round/whatever within page and number of match within phase/round/whatever

Okay, there’s a lot to take in here. But the basic idea is that my MatchId object is going to look like {OverviewPage}_{TabName}_{MatchNumber} (note the specific callout in the third bullet point that we’re departing from my previous primary-key convention of {PageName} and using {OverviewPage} instead), and then if it’s a GameId I’ll also append _{GameNumber}. Let’s look at each of these requirements.

• OverviewPage - As I mentioned earlier, I did a lot of “magic” to make sure this is always available. It’s out of scope of this post, but maybe I’ll write another post about it some day. Just take it for granted this is always available.
• TabName - This is determined either by the TabHeader tab, or the show-hide button for the section, or the editor literally fills out a template called ScoreboardTab saying what the name of the tab is. (Yes, I am committing about 25 thousand sins here in coupling database design to graphical layout, but graphical layout is the only way that editors get direct feedback on whether what they did was correct. Or in other words, when you have user acceptance tests and no unit tests, you lean into the former, and this is how you do it. Yes, it sucks. Yes, it leads to a lot of inflexibility. Yes, things could be better. But this is the only way to manage this quantity of manual data, so this is what we do.)
• GameNumber - Easy arithmetic, each series is always a single unit

I skipped MatchNumber. If a tab’s data is always fully contained within one page (second bullet point), this is easy arithmetic, just like GameNumber. For data pages (MatchSchedule, MatchScheduleGame) and picks & bans (PicksAndBansS7), this is never a problem. But for scoreboards? This is simply impossible for a lot of events that have tabs with too many games in them; a single tab (a week) MUST be split across multiple pages. So, how can I deal with this without losing integrity?

Disallowed solutions

There’s a couple solutions that I could have done that I’ve always considered off-limits, for various reasons. Let’s quickly review them:

1. Shrink tab size - We could redefine a tab to be a day, say, rather than a week when needed. Off-limits because this affects display of the wiki too much; the Match List on the event page (screenshotted above) would then be divided into days, instead of weeks, which we definitely do not want (again, this is a downside of having display coupled so tightly to database design, but not much we can do about this). Also, then what happens if suddenly a single day has too many matches??
2. Introduce a new parameter called subtab - I could introduce yet another, optional, “named” division called “subtab.” (In reality it would be mandatory but defined by the software unless overwritten by an editor.) To be honest, this is reasonably appealing. However, it is still vulnerable to the last point - what happens if suddenly a single day (“subtab”) has too many matches?? I also semi don’t like this for the purely philosophical reason that a NAME inside of my primary key instead of a NUMBER just really doesn’t FEEL nice (because of their susceptibility to accidental duplication in the case of human error - what if a person accidentally wrote Day 1 twice?), and now I’d have TWO of them instead of just one - three instead of two if you include the title of the page.
3. Reduce the amount of information we display in scoreboards - Not gonna happen. Not even on the table. I’m only putting this here to remind you to think outside the box!
4. Convince Gamepedia to raise the limits of what I can transclude per page - Haha. Maybe if it was, like, by 5%. But this is, like, 700%.

Great, now let’s look at solutions that are allowed. As far as I know, there’s only two real options, and I chose the wrong one first. But I hope you can now see why this is a very complicated situation, and why it’s not unreasonable that I made a mistake at the time.

A “brief” explanation of allowed solutions

I need to do one of two things:

1. Import the scoreboard’s internal keying to the “main” index as a second set of primary keys, with an editor specifying the page/“tab” divisions of scoreboards in the “main” indexing table (note that this is sort of like setting up subtabs in option 2 above, except the subtabs only exist for the scoreboards, and are in a separate key, and only exist if these two keys were ever somehow interpolated).
2. On each subsequent scoreboard page within the tab, query all previous pages within the same tab and continue the indexing so that the same primary key can be used.

From a caching point of view, (1) is safe and (2) is unstable; (2) requires that blank edits happen in a certain order, or the very integrity of my foreign keys is at stake!!!!!

From an editor point of view, (1) is more work, while (2) happens “for free,” however the caching requirement means that nothing is really for free; and indeed, (2) may end up being more work in the end if scoreboards are particularly volatile.

At the time, I only considered these two points of view, and I decided the danger of key integrity was a larger worry than anything else. These are (at least some of) the miscalculations I made:

• My editors are EXTREMELY good at worrying about caching. Maybe they all secretly hate me for it but they all do a phenomenal job of blank editing everything properly, and usually know the rules better than I do.
• So are users of my API; often people in my support server can recognize when there are problems & resolve them with appropriate blank edits, and once I explain one time people are usually very willing to deal with issues that come up again.
• We don’t really organize scoreboards by group anymore; in particular, there’s never really going to be an occasion where we’re going back and constantly inserting extra scoreboards out-of-order chronologically (this was a huge factor, really).
• I had VASTLY underestimated the importance of a third requirement to balance:

From an API user’s point of view, having the irreconcilable keys of ScoreboardId & GameId is a giant, huge, enormous nightmare. The situation that MatchScheduleGame is a required join any time ScoreboardGames needs to be joined to PicksAndBansS7 is just terrible. So, I needed to change how this was set up.

In summary, I was over-optimizing for ease of data storage and under-optimizing for ease of data access, and the things I thought were important for data storage, in the end, weren’t even that important.

Now that I’ve said a lot about this topic, let’s go into even more detail about the implementation specifics!

Original solution - ScoreboardTab

Originally I had this concept of a “ScoreboardTab” - the same “Tab” entity as it existed in the MatchSchedule sense, but for the ScoreboardGames table instead. The ScoreboardTab was set by a template called, intuitively, SetScoreboardTab. Here’s its source:

<includeonly>{{#invoke:InstantiateClass|main|class=ScoreboardTabVariablesClass|method=set}}</includeonly><noinclude>{{documentation}}</noinclude>

I’m not going to talk too much about InstantiateClass - that’s an entirely separate post - but basically it does what it says - it instantiates an instance of the class returned by the ScoreboardTabVariablesClass module, and then calls the method set.

Woah….a class? Instantiates? What the hell? River, you are literally SETTING A VARIABLE. IT DOESN’T NEED TO BE THIS COMPLICATED!! YAGNI!!

Ok, ok, hold on. First of all, remember that I’m going to need to retrieve these variables in the MatchSchedule module, which is, well, a module. So I’m gonna need a single module to use as a single setter & getter so that I’m not putting a single random variable name all over the place - that’s how you forget to change it everywhere it’s written when you refactor later. This variable’s name should only be written on one page.

And in fact the ScoreboardTab isn’t a set-it-once-and-done: I need to keep incrementing it as I’m calling it in MatchSchedule; remember, I’m just resetting an index based on where the page divisions from the scoreboard pages are (or at least are told me they are). So even the set function needs to be available both in Lua and MediaWiki.

Second, I actually have a very similar use case: setting the patch. Similar to the name of a scoreboard tab, the patch is set once and then carries forward; then set once again and carries forward; then set once again, etc. So having polymorphism here may be useful.

Arguably this was still too premature to do this much “random crap” and I should’ve just gone for two separate standard modules with a set and a get method twice, and refactored into a class if I had a third application later, but to be honest this isn’t really all that complicated compared to some other things I do; and I was pretty certain at the time I’d have more applications of this (I didn’t though, oh well).

………………okay, yeah I really didn’t need any of this, and I shouldn’t have done this, and I was literally SETTING A VARIABLE. IT DOESN’T NEED TO BE THIS COMPLICATED LOL. (But it DID need to be in Lua.)

Anyway, so here’s the (now deleted) code of Module:ScoreboardTabVariablesClass):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

local util_args = require('Module:ArgsUtil')

local p = require('Module:SetVariablesClass'):extends()

p.prefix = 'ScoreboardTab'

function p:set(args)
	if not args[1] then return end
	self:_set('tab', args[1]:gsub('_', ' '))
	self:_set('index', 0)
end

function p:match(gameIndex)
	local last = self:get('index')
	if not last then return nil end
	if tonumber(gameIndex) ~= 1 then return last end
	local next = tonumber(last) + 1
	self:_set('index', next)
	return next
end

return p

And here’s Module:SetVariablesClass:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

local util_vars = require('Module:VarsUtil')
local LCS = require('Module:LuaClassSystem')

local p = LCS.class.abstract()

p.prefix = nil -- define in subclasses

function p:set(key, val)
	self:_set(key, val)
end

function p:get(var)
	return self:_get(var)
end

function p:_set(key, val)
	util_vars.setVar(self.prefix .. key, val or self:_get(key))
end

function p:_get(var)
	return util_vars.getVar(self.prefix .. var)
end

return p

I’m………probably going to refactor Module:PatchVariablesClass so that it doesn’t need this anymore & then delete it; this is really just, way too much, it’s just setting a variable….

Module:MatchSchedule looked like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

local ScoreboardTab = require('Module:ScoreboardTabVariablesClass')()
-- snip
function h.setGameIds(thisgame, match)
	thisgame.GameID_Wiki = ('%s_%s_%s_%s'):format(
		match.OverviewPage,
		match.Tab,
		thisgame.N_MatchInTab,
		thisgame.N_GameInMatch
	)
	thisgame.ScoreboardID_Wiki = ('%s_%s_%s_%s'):format(
		match.OverviewPage,
		ScoreboardTab:get('tab') or match.Tab,
		ScoreboardTab:match(thisgame.N_GameInMatch) or thisgame.N_MatchInTab,
		thisgame.N_GameInMatch
	)
end

If ScoreboardTab weren’t a class, it would be ScoreboardTab.get() and ScoreboardTab.match() instead of with a :. And the thing wouldn’t be instantiated on its import line, just imported.

So, that was how I kept track of both the ScoreboardId and the GameId_Wiki in the MatchScheduleGame table. If you wanted to join Scoreboards to Schedule, you used ScoreboardId. If you wanted to join Schedule to PickBans, you used GameId. And if you wanted to join PickBans to Scoreboards, well, that just sucked, didn’t it, you had to go through Schedule to do so, because that was the only table that knew both ways of indexing.

(It was a pretty neat way of doing things while being cache-safe though, I do have to say.)

New solution - continue

As I mentioned before, the new solution involves this continue parameter. When a tab is broken into multiple pages for scoreboards, we start the page somewhat like this:

{{Scoreboard/Tab|Week 1|continue=Yes}}
{{MatchRecap/Button}}

That documentation of the Scoreboard/Tab template actually explains a lot. To summarize, when continue is not set, all we really do is set a tab name. When continue IS set, however, we query all of the previous pages, count all of their N_MatchInTab, and then reset our global index of this variable to be this value.

Quick sidebar: How do we know what a “previous page” is? We save the current index of every focused tab (page) when we create the TabsHeader. That’s done automatically for us in the TabsAbstract module, and then stored in the ScoreboardGames table. Cool.

Additional sidebar: Both ScoreboardGames and PicksAndBansS7 have these page indices, in fact. It allows for a total ordering on matches without resorting to any sort of join; and it allowed me to order pick-bans even when a join to MatchScheduleGame was unavailable, so our pick-ban histories were originally created without that join in place. The join was only added later in order to supply patch! At the time of this post, the ordering (for tournaments queries) still comes from the internal ordering of the pick-ban table.

Ok, here’s the code for ScoreboardButtonVariables:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

local util_args = require('Module:ArgsUtil')
local util_cargo = require("Module:CargoUtil")
local util_esports = require("Module:EsportsUtil")
local util_html = require("Module:HtmlUtil")
local util_map = require('Module:MapUtil')
local util_table = require("Module:TableUtil")
local util_text = require("Module:TextUtil")
local util_vars = require("Module:VarsUtil")
local i18n = require("Module:I18nUtil")
local lang = mw.getLanguage('en')

-- this module resets counters of start-of-page stuff at a value other than 0
-- this makes the page query all previous pages with the same tab name
-- and accordingly offset the indices to match those in MSG
-- see Template:Scoreboard/Tab for usage documentation

local h = {}

local p = {}

function p.test(frame)
	local args = util_args.merge()
	return p.main(args, args.tab)
end

function p.main(args, tab)
	util_vars.setVar('sbTabName', tab)
	util_vars.setGlobalIndex('sb_N_TabInPage')
	if not util_args.castAsBool(args.continue) then
		return
	end
	local result = util_cargo.getOneRow(h.makeQuery(tab)) or {}
	util_vars.resetGlobalIndex('sb_N_MatchInTab', result.N_MatchInTab)
end

function h.makeQuery(tab)
	local query = {
		tables = { 'ScoreboardGames=SG', 'MatchScheduleGame=MSG', 'MatchSchedule=MS' },
		join = {
			'SG.ScoreboardID_Wiki=MSG.ScoreboardID_Wiki',
			'MSG.MatchId=MS.MatchId',
		},
		where = h.getWhere(tab),
		fields = {
			-- This was previously COUNT DISTINCT and i see no reason for that
			-- a number can be skipped if there is a forfeit etc
			'MAX(MSG.N_MatchInTab)=N_MatchInTab [number]',
		},
	}
	return query
end

function h.getWhere(tab)
	local where = {
		('SG.OverviewPage="%s"'):format(util_esports.getOverviewPage()),
		('MS.Tab="%s"'):format(tab),
		('SG.N_Page<"%s"'):format(util_vars.getVar('tabThis'))
	}
	return where
end

return p

The first comment exists because one time I opened this module and had NO IDEA what I was looking at it. The second comment exists because I fixed a bug and was so perplexed at how I managed to make the mistake that I felt a need to document it. I do that sometimes when I see an error so outrageous in my code - like, what was I THINKING??? There must have been SOME LOGIC, RIGHT??? And I just trust my past self to have had some reason that I leave a comment behind just in case, just in case it saves my future self from some horrible bug that my past self had actually been guarding against that my present self didn’t realize.

One day it will help. It hasn’t yet, though.

New situation

Now that I’ve spent 5000 words telling you how much I suck, let’s talk about how awesome things are going to be now!

• In MatchSchedule, we’ll have primary key MatchId
• In MatchScheduleGame, we’ll have primary key GameId and foreign key MatchId (many-to-one relation)
• In ScoreboardGames, we’ll have some primary key we don’t care about, and foreign keys GameId and MatchId (and since entries are 1:1 with MatchScheduleGame, we can effectively treat GameId as a primary key). Note OCCASIONALLY there will be some errors due to failed blank edits. But if you’re a user of my API, you now know how to fix this!
• In PicksAndBansS7, same situation as ScoreboardGames. However there should almost never be any errors here due to failed blank edits, because there are no multi-page issues with this table.
• In ScoreboardTeams, we’ll have some primary key we don’t care about, and foreign keys GameId and MatchId.
• In ScoreboardPlayers, same situation as ScoreboardTeams. It also has a self-referential foreign key to the row in the opposing ingame role (GameRoleId & GameRoleIdVs - I know these names are sort of awkward, but I really want to emphasize what this entity is, and it’s not a role ID, which would be just like TOP & nothing else, it’s a game ID). By the way, IngameRole is a new thing too; we now support the ability to manually specify the played role, when positional role disagrees with played role because the players switched positions ingame.

Wow, that’s so nice!

A quick note on capitalization

Maybe you noticed that previously I called things like ID and now I use Id. What’s up with that? Actually a very deliberate philosophy change. I used to believe in capitalizing acronyms and initialisms in all-capitals when they appear in module names, field names, etc. I no longer do, though, because once upon a time I had to write a macro in SublimeText that changed html to HtmlUtil, but at the time the module was called HTMLUtil, and that broke everything, and I was very sad. So I instantly renamed the module, ran an AWB script to update dozens of module imports, and everything was happy.

I guess that would make a good topic for another blog post sometime, huh.

Anyway, after that experience, I immediately changed my opinion on the matter: Always, always, always, capitalize the first letter only, and pretend that the all-caps acronym/initialism is otherwise a normal, lowercased word. So that’s what you are seeing here. the ID_Wiki fields were made before this experience, and MatchId / GameId were made afterwards (just now).

Migration plan

As of publishing this post, I’ve added all new keys. I won’t delete anything for a few months at least. Certainly not until the end of May at the very earliest. If I don’t hear that anyone is still using the old fields by the end of May, I might delete old fields at that point in time. (For readers who aren’t familiar with the League of Legends season, we are on mid-season break during May.) If I hear that anyone still needs extra migration time, and I will try as hard as I can to reach out to people using our API, I will push removal of old fields to August/September, the end of the summer split.

It is definitely a challenging thing to deal with when there is no separation between the internals of my code base and what people are using as our public API, and it makes a lot of things a lot harder for me to change. But I think everyone involved will be a lot happier with the new outcome!

Conclusion

Whew! This was a pretty long post, even for me, especially given how little code there was. I hope you enjoyed reading about one of the biggest mistakes I’ve made in the past couple years, and how I resolved it! I’m EXTREMELY pleased with the outcome, and changing over modules to use the new keys has been extremely satisfying. Yay!

Two weeks ago, I was asked to write a post about a “boring, typical” use case of Cargo on Leaguepedia. Well, these tables are in very high use on Leaguepedia, so this is technically “about a typical use case of Cargo.” And the word count is now about 5500, so maybe I made the post itself extremely boring. So…mission accomplished?