Ramblings of Narc

For the longest time, I’ve been a person willing — rather, excited about — writing my own renditions of various interesting applications. When I started writing PHP, this tendency didn’t change — only the projects did.

One of the first Web applications I wrote was a very simplistic blogging app. There was an “add post” page, there were posts on the main page, and some simple login screens, and that was more or less it (oh, I think it had comments, too. I’m not sure anymore). However, because I didn’t save any database dumps, or anything similar, the project doesn’t exist anymore – I still have the PHP, but going through it to rip out the SQL, and using that SQL to figure out the table structures, would be amazingly boring — and, I daresay, useless.

My next big project on the same note, building on the experience learned in the previous one, was CMBlog v1. It featured multiple users, and multiple blogs, and various other nice things inspired, to some extent, by LiveJournal, Blogger, and whatever other projects like those might exist. CMBlog has served me well, but two years, and a lack of general interest, have taken their toll. A few weeks after CMBlog had grown to a state where it was no longer a simple application, and was actually doing most of what I wanted it to do, I realized it really wasn’t anywhere near what I thought it would be. Sure, it was fast. Sure, it was simple, and clean. But that was only on the surface. The code behind it was a muddled mess, and making sense of it in order to add features (or fix the existing ones) was getting more and more taxing.

So I declared CMBlog finished and started work on its successor, learning from the failings of the past — as I always seem to do in these things. CMBlog2 never really took off. At some point down the line, I realized the architecture I had chosen was really unfit for building something like what I wanted. I let it get as far as being a read-only version of CMBlog before locking it, always thinking “Well, I’ll come back to it when I have a better design, or a better way to keep the code clean”. That day never came, and other projects (not all of which are featured at that URL) came and went, and I learned more and more about how to do things right.

In PHP, one of the best architectures you can choose when, like me, you have a lot of related websites, is one where most parts are stored in one central location, and included as needed in each project. Thus, NeoFW was born, and has now become the repository of all my useful and semi-useful functions that I’ve written over the years. NeoFW is my codebase.

However, since I got a job (and for quite a while before that), these projects have been advancing at a glacial pace, and at one point, I stopped working on most of them, maybe writing a quick little hack-job, but never something that took longer than a few hours to write, and usually just copy/pasting the base skeleton from a previous little project, and maybe making a modification or two. The latest such mini-project was JGH, which stands for “Just Got Home”, and which is a relatively simple personal email gateway, designed to keep me and my girlfriend in touch by serving as a simple way to write each other long long emails and then send them without worrying too much about losing our progress during the day.

These mini-projects have all had one great advantage: they have, through every iteration of the same basic design, taught me how to make it work just right, and I’m now reasonably confident I can make it work for another iteration of the blogging application.

And so, after that long and somewhat rambly introduction, I come, finally, to what I announced in the title: CMBlog3.

CMBlog3 will be taking NeoFW to its first stable format, as during the project I suspect I will be requiring a large number of things to be working properly, which will stabilize the design on those parts I haven’t had cause to lock down yet. Such things as application-specific preferences, and the like.

But, to start the whole thing off, CMBlog3 needs a database — because I don’t want to reuse the old one. The old one really is kinda crappy.

The original design used five tables, namely blog_acl, blogs, comments, posts, and prefs. blog_acl was for the access control lists: who had access to what blogs. blogs, posts, and comments are self-explanatory. Finally, prefs was a special table, holding each user’s preferences, albeit in a very ugly fashion. The user tables (along with the entire user-handling substructure) were handled by EPRU — a user database and set of API functions.

The new design will probably keep the blogs, posts, and comments tables — it’s pretty much a requirement — though I do expect the structure to change. NeoFW will handle the user API, including user preferences. Finally, the access control list will have to change dramatically, as I wish to provide a lot more granularity in the preferences — down to the comment level.

This post was initially only meant to help me design the database, though I suppose along the way it turned into a bit of a history lesson. Either way, here’s what I’ve got so far:

• CREATE TABLE blogs (
  • id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  • name VARCHAR(255) NOT NULL DEFAULT “”,
  • tagline VARCHAR(255) NULL DEFAULT NULL,
  • user_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
  • created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  • updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  • deleted TINYINT(1) NOT NULL DEFAULT 0,
  • deleted_at TIMESTAMP NULL DEFAULT NULL

  )

• CREATE TABLE posts (
  • id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  • blog_id INT UNSIGNED NOT NULL DEFAULT 0,
  • user_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
  • title VARCHAR(255) NOT NULL DEFAULT “”,
  • content LONGTEXT NOT NULL DEFAULT “”,
  • created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  • updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  • deleted TINYINT(1) NOT NULL DEFAULT 0,
  • deleted_at TIMESTAMP NULL DEFAULT NULL

  )

• CREATE TABLE comments (
  • id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  • parent_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
  • post_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
  • user_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
  • content LONGTEXT NOT NULL DEFAULT “”,
  • created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  • updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  • deleted TINYINT(1) NOT NULL DEFAULT 0,
  • deleted_at TIMESTAMP NULL DEFAULT NULL

  )

That all should cover the first parts, with the minimum of metadata. You’ll note comments include a parent_id, which allows for threaded comments.

With these basics in place, I can begin work on the engine itself. The metadata, such as access control and the like, will be included in separate tables I can create later. Hopefully, there should be good stuff to link from here later.

Update: I forgot to mention — these tables, when in MySQL, should be TYPE=InnoDB, to take advantage of that storage engine’s transaction support. Since it is very likely that multiple tables will be updated at the same time (consider the fact blogs have an updated_at timestamp, and that it should reflect when a post was last added to them), this particular bit of magic is very desirable.