The Great Page Refactor

The old database

To understand why the changes were made, we must understand the previous configuration. And so, let's travel back in time.

When I first created this site, it was bad. But over the years, I've made it a little better each time. First starting with putting the posts in the database, then the categories.

But there was always something that bugged me. Here's the definitions of my post and post categories:

^{To be honest, "security through obscurity" is big, but I've already linked my GitHub here. This is nothing you can't find out already.}

Post definition

Post category definition

So! Couple of things to notice. First of all, every post has a parent id that points to a category's id. Each category id also has a parent id that points to its parent. So, say you go to the... Blog category, then the Media Recommendations one. You'll see up at the top the header has index -> blog ->, then Media Recommendations below it. If you go into a post inside Media Recommendations (there aren't any at the time of writing), you'll see Media Recommendations up there too.

This is the point of the parent id on the post category page. My post and category service traversed up the parent through the category until it reached the top (parent id being null), then sent it out as the parents.

Second thing is the "slug" column. Slugs on my site are unique, styled after qntm's site. See here for an in-depth dive on that. I personally enjoy the simplicity of that: if I want to go to Runic, I just type "runic" into the URL bar and boom, I'm there. Makes the URLs simple to remember, but has the downsides listed in qntm's blog post.

Thirdly! There are five duplicated properties on each table. Each post and category needs to be shown as a page, and so they have the normal things a page needs: keywords, a description, a name, a title (for the tab's name), and the HTML body. This has bugged me for a while, but I've never gotten around to... you know, doing anything about it.

Until three days ago. Three days ago, I stood on the hill and said I will either tear it down with my bare hands, or die on it. And so I started.

The new database

Here's the thing with database work. It sucks. I hate writing SQL. But I already have a system in place (see: here) and so I stuck with it. I wrote three scripts.

• RemoveNullableOptionsFromMostDatabases.sql
• This script is to remove the null option from most of the columns. Almost every single table in my database was made with nullable as the default (thanks, MariaDB) and so I needed to remove this for my own sanity and ease of code. If any of the values are null (aside from parent id), I'd like my server to be very loud about this and refuse entry into the hallowed halls of the database, thanks!
• ConvertPostsToUseIntegerId.sql
• This one wasn't bad, but it was tedious. Please direct your attention back to the tables: Slug was the primary key on the posts table, but not on the post categories table. This was a problem, as I wanted to have the slug be a foreign key later on, and I really didn't want to even try to have a foreign key and a primary key be the same column. So now, the posts category has an id column, and the slug is relegated to a normal column.
• ExtractCommonPageDataToPagesTable.sql
• This sucked. This really, really sucked. It needed to be done, but god, it sucked. Writing the script wasn't that hard. All it was is creating a new table, uploading the post and category page data to it, creating the slug foreign key link, and then boom, you're good to go. The main problem was making the new classes to access the data.
• Actually, just realizing while I was writing this that I'm a fucking moron. That's awesome. I need to redo the PageRepository in my database access layer. ughhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
• So, what I had to do was pretty complicated. Previously, I was just returning the database objects from the database to my API controllers, and then converting that into a class that my views would be able to use. Instead, I moved all that converting into the database access layer. Now, I have two repositories: a page management repository, which handles what I'm using to write and post this post right now, and the page repository, which handles general browsing requests.

So, after I ran those three, I had a functional site. Woo!

The automated tests

Look. I'm not gonna say I write the best software in the world. I definitely do not. That's why, when I rewrote my database access layer, I wanted to have some damn good unit tests to make sure I did it right.

And you know what? I did. I made some damn fucking good unit tests that caught several errors in my repository logic that would've taken me ages to debug! I'm glad I took the time to write them.

(Now, if only I could convince my work to go for them... damn legacy software that was coded terribly!)

What's next?

There are two major things on the docket.

First, I want to make my story editor interface a lot easier to use. It's not great right now. This'll involve a lot of work, but it should make the process of beta reading a lot easier and marking up areas where I've written poorly.

Second, I want to add a comments system. I mentioned this in a blog post ages and ages back, but it just never happened. It'd be nice to see some reader feedback.

After that? I dunno. I think I will have reached the point where the custom content management system for my website is decent, and needs no further work besides maintenance. That's a weird feeling. I guess I'll need to patch it for security vulnerabilities, but... wow.

Thanks for reading. Happy Martin Luther King day.