Fun SQL Tricks II

Last time, I outlined some project requirements and a solution to one of the requirements, that of object IDs unique across first class objects in the system. This involved foreign key constraints, a stored function, character sets, collations, and a trigger, and also showed a use for SELECT…RETURNING. This time, I’m going to look at the other requirements specific to user objects.

Continue reading “Fun SQL Tricks II”

Fun SQL Tricks

So I recently started a new project that has a few requirements that are a little more complex than a simple web site. Exactly why these requirements are part of the project doesn’t really matter, nor does the specific nature of the project. However, along the way to implementing it, I learned a few things that will probably make the overall coding for the project simpler. These include the use of SQL triggers, stored routines, and foreign key constraints. Read on for a discussion of the first level of the fun.

Continue reading “Fun SQL Tricks”

Mr. Kenney Followup

I talked about the United Conservative Party leadership review in Alberta in my previous post. It seems Mr. Kenney probably did a similar analysis to mine. Even though he technically won the review with 51.4% of the vote, he has decided to resign. It is almost certainly the right choice since there is no possible way the in-fighting within the party will end if he stays on. I do wonder if the approval number would have been higher if the whole pandemic thing hadn’t happened.

Now the real question is who ends up becoming the new leader. Hopefully, it will be someone who the party can unite behind, but also someone who will not be off-putting to the voters at large. On the balance, the province really cannot afford an NDP repeat performance. I also hope it won’t be any of the ring leaders of the dissent over the past couple of years since I don’t want them to be rewarded. In particular, I really hope it isn’t Brian Jean who has just been chucking a wobbly since he didn’t win the party’s inaugural leadership election.

Well, whatever happens will happen. At least there should be time for the party to get organized before the next provincial election, and for the new leader to have some experience in office (assuming the winner is an actual MLA, though technically the premier doesn’t strictly have to be an MLA).

Also, hopefully whoever wins continues the good stuff that Kenney’s regime has been doing.

What I do know for certain is that whoever wins is going to find out that whatever it is they wanted to do that Kenney wasn’t doing is going to be loads harder than they think it is, if it’s even possible.

How Democracy Falls

(A followup after the leadership review results is over here.

Some people reading this will be aware of the leadership review for Jason Kenney, currently premier of Alberta and leader of the United Convervative Party in the province. This situation underscores how our democratic institutions have been failing. Even with the result of the leadership review vote due to be announced within the next few hours, it’s clear that this will not be the end of the matter, no matter what the result is.

The problem as I see it is that what appears to be a fringe element of the party led by the person who lost the original leadership election to Mr. Kenney is hell bent on defeating their perceived enemy at all costs, no matter the consequences. Indeed, reports quote sources as saying that even if the result is a landslide in Mr. Kenney’s favour, they will not accept the results. Indeed, it seems from their statements that any result that does not match with their desired outcome is untrustworthy and not to be accepted. Yet they’ll happily accept that the same process is completely trustworthy if they get the result they want. (Does that sound familiar at all? Feels like a re-run of some big democratic event from a year or so ago, but I can’t quite put my finger on it….)

Anyway, they can’t have it both ways. Either the process is trustworthy and the results are to be trusted and accepted, or the process is untrustworthy and the results are not to be trusted or accepted. It’s the same process either way. You can’t cherry pick the votes that go your way as the only trustworthy ones. At least not if you want to claim that you truly believe in the democratic will of the people being honoured.

Here’s the thing, though. Mr. Kenney has said he will accept the result of the vote. The party constitution (or rules or whatever it’s called) requires 50% plus one to pass the review. He has said he will say on if he gets 50% plus one, as he is allowed to do. He has said he expects everyone to accept the results and stop the infighting no matter which way they go. His opponents, however, seem to only be using this process as a tool to stage a coup. They’ll crow about how they won if it goes their way, but if not, they’ll crow about how it was rigged. And, to make matters worse, the mainstream media will continue to amplify their message and largely ignore or deride Kenny’s as they have been doing all along. After all, confict makes for better news, doesn’t it?

So here’s how I see things going. There are exactly two possibilities.

Kenney wins. If Kenney wins, the infighting will continue. I don’t like meme images, but if I did, I would insert one that has a caption along the lines of “infighting intensifies”. Kenney will attempt to reconcile the party, which will fail. He will likely have to kick the more disruptive members out of caucus because there’s now way to govern otherwise. He will attempt to hold on until the next regularly scheduled election and put his actual track record up against the opposition parties in the general election. I give it no better than even odds that he isn’t forced out by some other means, likely extremely shady and probably involving a frame job, within months following the review.

Kenney loses. In this case, Kenney will step aside and there will be a party leadership election. He may stay on as leader until the results of the leadership election are known. You would think that would be acceptable for his opponents since they would be getting what they want, but if it chooses this path, they will scream about him being antidemocratic by staying on while the multi-month leadership election takes place. I doubt Kenney will stand for re-election, though as I understand it, he would have the right to do so. Then, regardless of the outcome of the leadership election, the rebellious element of the party will not be united. Currently, they’re united with the goal of getting Kenney out, but beyond that, they don’t have much in common at all. The party will continue to be anything but united, and will have a very high probability of losing the next election, or at least being knocked down to minority status. (There are allegations that a vast number of new memberships were purchased with the specific goal of achieving just this result, but those haven’t been proven. Even if true, those members are probably going to be continuing to destabilize from within rather than let things settle down.)

Anyway, I expect Kenney to win the leadership review by a relatively small margin, but enough that it’s unlikely a counting error. Then, his oponents will grouse and gripe in the media for a short time and demand his resignation on various grounds, including not having met the “threshold” for continuing (he only needs 50% plus one according to the rules remember so this will all be self-serving nonsense). Then, eventually, law suits will be filed. The courts will not be amused. Nor will anyone else. Odds are they will be dismissed with prejudice after a great deal of legal brangling. Regardless of all of that, Kenney will eventually be forced to resign if there is to be any chance at actually governing or winning the next election.

On the other hand, if he loses, he has said he will step aside and we can skip all the mess in the previous paragraph. However, I don’t think his opponents have as much real support as they think they do which is why I think he will eke out a win.

The subsequent leadership election is going to be unpleasant as candidates from three camps vie for leadership: the rebels, the status quo-ers, and various “time for a change” new guys.

All of this shows neatly how a democratic system can fall. It usually takes a run of this type of behaviour to cause a fall, but if it doesn’t fall apart completely on its own, eventually someone with the charisma and resources will come along and game the chaos to their own benefit. (See many cases in history, including Julius (and, due to existence failure of the former, Augustus) a couple thousand years ago

I should be clear that this doesn’t mean we shouldn’t strive to keep our democratic systems functioning. Instead, we need to be wary of this sort of trap, which has become all too much easier to cause with the advent of algorithmic (anti)social media and such things as cancel culture.

Multiplayer Games and Floating Point

Multiplayer games are quite popular. That statement is not likely to be controversial. What might be controversial is my assertion that game developers are implementing network based multiplayer incorrectly. The sheer number of bugs related to desynchronization, especially between players on different platforms, on some games I’ve been familiar with over the years leads me to believe this is a much harder problem for many developers than it would seem on the surface. Here, I’m going to discuss one major source of problems: floating point numbers.

Continue reading “Multiplayer Games and Floating Point”

CPU Operations: Registers

Now that I have a clock source of some sort, I can move on to making components of the processor. Of course, there will be other bits required as well. Notably some sort of memory module and some sort of I/O system. However, those are not useful until I can actually do something with them so I’m going to focus on the stuff that does things, which is, unsurprisingly, the processor.

The processor will have multiple parts. These include components that handle addition, subtraction, boolean operations, and others, collectively known as an ALU or Arithmetic Logic Unit. It also includes little bits of storage where numbers are stored temporarily in order to do these operations. (There are a few other bits that will be needed but I’ll get to those later when they’re actually relevant.) Those bits of storage are called registers and that is exactly what I am going to look at next.

At the most basic, a register is just a small bit of memory, say 8 bits. Each bit of a the register can be built from four NAND gates taking a “set enable” signal and a data input signal, and providing a data output signal. Putting eight of those together with the “set enable” signals tied together gives an 8 bit register. This simple register has a level triggered set input. More complex logic can provide an edge triggered set signal. Conveniently, this simple design allows creating each bit from the four NAND gates on a 74LS00 chip so the 8 bit register can be created from 8 74LS00 chips. A schematic of such a design is below.

Simple NAND Gate Register

That’s rather frightening, isn’t it? It’s also more complication than I’m willing to put up with, however, given that there is a handy 74LS173 chip that provides a 4 bit register in a much more compact package which also supports edge triggered setting. Two of these chips can easily be combined to make an 8 bit register.

But the storage of bits is not the whole story. I also need to get data into the register. And also feed the data from the register to the ALU for operations to be accomplished. To figure out how to do that, a brief description of an ALU is needed.

At its most generic, which is all that is important at this stage, an ALU takes one or two inputs, performs an operation on them, and generates an output. Unary operations like shifts only need a single operand. Binary operations like addition require two operands. To avoid conflicts, three sets of data lines need to be connected to the ALU: two inputs and one output. You may be tempted to try to combine the output with one of the inputs. That way lies madness.

Ben Eater’s design uses two registers called A and B which feed directly into the ALU inputs and has the ALU output directly to the system data bus. (A bus is just a collection of lines, say 8 data lines, that are all related and run together through a portion of a system.) In his design, the ALU output is always showing the result of the selected operation based on the contents of his A and B registers. In that design, the A register connects to one input of the ALU and the B register to the other. That is a perfectly serviceable design, of course. However, I am looking for something slightly more flexible. (While I want something with similar capabilities to the 6809, I also want some additional capabilities.)

Instead of the above design, I want to be able to feed the contents of any register into the ALU on either input. That means I’m going to need *two* data buses, one for each ALU input, that the registers can all be selectively connected to. That is in addition to the bus that will connect to the output side of the ALU which will need to also connect to the input side of each register so the results of calculations can be stored back in a register. What this means is that we need a way to feed two operands into the ALU.

There are two ways we can do this. We can have a single bus that connects all the registers. In this case, we would need some sort of input operand storage in the ALU so that both operands of a binary operand can be fed into it. The first operand would get stored in, say, a latch, and then the operation would be done on the value in the latch and the value on the bus. This method has the advantage that it only needs a single data bus between the output of the registers and the ALU. On the other hand, it means an extra clock cycle to do binary operations since the first operand has to be sent down to the ALU first, taking one clock cycle, and then the operation can be done on the next clock cycle.

The other option is to have two data buses connecting the registers and the ALU. This requires a lot more support electronics including a second bus driver for each register. On the other hand, it also means that binary operations can be done in a single clock cycle since both operands will be available immediately.

In the interest of minimizing the amount of wiring and breadboard space requirements for everything, I have opted for a single input bus to the ALU. The disadvantage of requiring two clock cycles to transfer the operands into the ALU can be avoided with a higher clock speed. (Indeed, it is likely that the CPU will end up running an internal clock at a multiple of the external bus clock.)

Now that I’ve arrived at the design requirements for a register in this processor, I can take the 74LS173 chips and add some additional hardware to make the whole thing work. Because I want to have readouts of the current register value, the register outputs have to be permanently enabled. That means I can’t rely on the ‘173s tristate output capability (tristate logic has a third state which is “high impedence”, essentially meaning that the line is disconnected from the circuit; this is useful for shared bus situations). Instead, the outputs can be permanently enabled and fed into a 74LS245 octal bus transceiver which is also connected to the ALU input bus. It may also make sense to put another 74LS245 on the input side of the register to reduce the number of devices directly connected to that bus. However, that is not included in this initial design.

I will create the register contents redout using an LED bar graph chip (10 segment, all independent) because I have some and the bar graph chips fit nicely onto a breadboard and take minimal space, unlike the complications of placing individual LEDs. This is easy enough since it will just have 8 LEDs connected to the 8 output lines of the ‘173s which will be active all the time anyway. These will, of course, be connected to ground with the usual current limiting resistors. The readout is, of course, optional, but it’s useful for debugging and also, who doesn’t like a bunch of blinkenlights. If you want to leave out the readout, you can also leave out the 74LS245 and instead connect the output enable input to the ‘173s directly.

With all that sorted, I now have a design for each general register. There will be several special purpose registers that I will get to later. That gives the schematic shown below.

Register Schematic

Constructed on a breadboard, that gives the rat’s nest of wires in the picture below. Missing from the photo are the bus connections. The input connection will be on the left and the output connection on the right. You can see there is space on the left to add a ‘245 bus transceiver should it prove necessary. In the photo, the register has been loaded with decimal 255 (all bits set). As a variance from the schematic, I have left a space in the middle of the LEDs to group the bits in groups of four which makes it visually easier to read.

Register on Breadboard

Exciting, isn’t it? Now I just need to build at least one more of those.

The Clock Module

As noted previously, I’m working on a project similar to Ben Eater’s 8 bit breadboard project (see This time around, time keeping.

While it’s probably possible to build a computer without a clock and have it work, it’s quite a lot easier to keep things from going off the rails with some sort of time keeping. To do that, I need a clock module. The design of this one is essentially Ben’s design with some adjustments. (Edit: do take a look at MiaM’s comment below for a rather significant pitfall depending on your use case.)

Basically, it has four main parts. First is a push button with a 555 timer used to debounce it. Second is a variable rate automatic clock run by, get this, a 555 timer. Then there is a slide switch to select between manual and automatic modes running through another 555 timer. This is pretty much exactly Ben’s design. Finally, there is some glue logic that leads to two outputs: CLK (positive clock) and !CLK (negative clock). There is also an input, HALT, which when high will stop the clock.

The Clock Module

In the image above, the pushbutton and associated 555 timer is on the left. The adjustable oscilator and its associated timer are next to it. Then the mode slide switch and its 555 timer. The remaining three chips are a 74LS04 Hex Inverter, of which 3 gates are used, a 74LS08 Quad And gate which is fully used, and a 74LS32 Quad Or gate of which 1 gate is used. You can see the decoupling capacitors for each chip overtop of the chips themselves. The blue LED will show the state of the CLK output.

The CLK output is fed through an And gate with both inputs tied together. This is a quick way to create a buffer for the output which should ensure a clean output signal to the bus. It also adds a gate delay similar to the inverter on the !CLK output, which should also ensure a clean output signal. The two clock outputs are the jumpers that are not connected. The jumper connected across to ground is the HALT input. CLK and !CLK will be fed to the bus which HALT is a control signal that will ultimately be provided by the control logic when it is created. There is no significance to the colour of the wires.

I have included a schematic of the module below (assuming I didn’t make any errors creating the schematic). It’s in SVG format so you should be able to scale it up to read it. As far as I can determine, it works though I haven’t put a scope on CLK output to see how clean it is.

Embarking on a Breadboard Adventure

A couple of years ago, I encountered the breadboard computer project by Ben Eater over at I found the idea interesting so I purchased his kits for the 8 bit breadboard computer project. I then embarked on the project and put together all the modules. It “worked” for some value of “work” but there were clearly some issues with it. Given the complexity of it and the number of bits where it violates good design practices (it’s a hobby learning project), I was unable to definitively trace the problems. I have some theories, however.

I have now decided to embark on a new project. I’m still going to build a breadboard computer with an 8 bit processor. However, I’m going to build my own design rather than Ben’s. His was fun, but it’s my hobby so why not?

So, I have a couple of rules for this project:

  • All ICs get decoupling capacitors. In the worst case, that does nothing but it should help a lot with instability.
  • All unused inputs on logic gates get tied high or low. A similar situation for other types of chips where it makes sense. Basically, avoid floating inputs.
  • Lines should be buffered between a module and a bus to avoid feedback and other complications. Where possible and practicable, high impedence (tri-state) should be used. This is to minimize the load for anything driving the bus.
  • Components are mostly limited to basic parts (resistors, capacitors, diodes), 74-series logic (and other) chips, and items where appropriate (RAM for instance). Components used should not be listed as “obsolete”.

The overall goal is to create a computer that has roughly the capabilities of a MC6809 CPU. That’s a long way down the road, however. What that means, however, is that there will be an 8 bit data bus and a 16 bit address bus between the processor and the memory and any memory mapped I/O devices. What the internal connections within the processor look like is undetermined at this time.

My general plan is to add entries here as I work through the project. As we all know, however, I’m likely to forget or get bored with entries so if they stop, well, maybe poke me and see what’s going on.

The next post will be about the actual build.