KISS my blog! (part 1)
Tags: keepitsimplestupid, php, sql, blog
Published: 2019-10-26 14:15:41

Sometimes, you get tired enough that you might as well do it yourself.

I want my own blog software.  I want something light and simple, but able to be updated on the road without too much hassle.  I want something that's secure and relatively bug free.  Something that doesn't get a ton of press for being repeatedly buggy (*I see you, WordPress!*).

Why not make it open source (GPLv2) and develop it here on dev.to?  It'll be fun.  We'll name it "Nothing" (Seinfeld reference).  I'll throw the code up on Github at https://github.com/STrRedWolf/Nothing .

# Philosophy

Before we get too gun-ho about things, I want to bring in the Navy mindset:

**Keep it simple,** ***stupid***.

We should not add anything that makes it more complicated and bloated.  The more that there is, the more that can go wrong.  *Maxim 61: Don't bring big grenades into small rooms.*  

BTW, you should read *The Seventy Maxims of Maximally Effective Mercenaries* ether [online](https://schlockmercenary.fandom.com/wiki/The_Seventy_Maxims_of_Maximally_Effective_Mercenaries) or [get the book itself](https://shop.schlockmercenary.com/collections/planet-mercenary/products/70momem-defaced).  I'll be dropping related maxims here and there because they do relate to what we're doing.

With that in mind...

# Requirements.

* Use Github-flavored Markdown for posts.  It seems to be the best way of writing stuff out.
* Use only what is necessary to do the job.  That means being light on the libraries on the server, and eschewing Javascript frameworks.  *Maxim 30:  A little trust goes a long way. The less you use, the further you'll go.*
* Use a database to keep things tidy, but don't overload it.  Using views is fine.
* Have users with privilege levels, icons, etc.
* *Optional:* Have "attachments" that can be inlined, so you don't have to SSH-copy stuff onto the server.

The project will be inspired by LiveJournal, because those avatar icons can be switched to indicate various moods and such of the user.  

For fun, I turned a Raspberry Pi 4 into a work server with lighttpd, PHP, and MariaDB.  Just a 1GB version will do, and a Pi 3B or 3B+ will do the job as well.  For development work, you don't need much.

# First stab at the SQL

```sql
CREATE TABLE posts (
        post_id         INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
        title           VARCHAR(255) NOT NULL,
        content         TEXT NOT NULL,
        author_id       INTEGER UNSIGNED NOT NULL,
        dtstamp         TIMESTAMP,
        has_attach      BOOLEAN NOT NULL
);

CREATE TABLE authors (
        author_id       INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
        username        VARCHAR(64) NOT NULL,
        fullname        VARCHAR(255) NOT NULL,
        passhash        VARCHAR(1024) NOT NULL,
        saltine         VARCHAR(1024) NOT NULL,
        blurb           TEXT NOT NULL
);

CREATE TABLE attachments (
        attachment_id   INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
        title                   VARCHAR(255) NOT NULL,
        disk_location   VARCHAR(255) NOT NULL,
        link_location   VARCHAR(255) NOT NULL
);

CREATE TABLE post_attach (
        post_id                 INTEGER UNSIGNED NOT NULL KEY AUTO_INCREMENT,
        attachment_id   INTEGER UNSIGNED NOT NULL
);

CREATE VIEW postview AS
        SELECT P.post_id, P.title, P.content, P.dtstamp, A.username, A.fullname
        FROM posts P
        INNER JOIN authors A ON P.author_id=A.author_id;

CREATE VIEW postattach AS
        SELECT F.attachment_id, F.title, F.link_location
        FROM attachments F
        INNER JOIN post_attach PA ON F.attachment_id=PA.attachment_id;
```

I wanted to start out simple and small, although I did get a bit ahead than intended.  Still, if we can make it easier for ourselves to program it and it's cheap to do, do it.

Note the authors table.  We'll use that to indicate posting users.  I haven't thought about commenting users at this time.  Yes, we're going to salt the hashes.

Comments and suggestions are welcome.  I intend on posting more as I work on this.  However, remember to heed your moderators.  *Maxim 8: Mockery and derision have their place. Usually, it's on the far side of the airlock.*

The next post?  The main index view.