MySQL - The Good Stuff...and New Hexo Home Page

Today we started to get into the good stuff with MySQL! And I made a big change to this blog, now it’s like a real live website 😄.

MySQL Good Stuff

We picked up with many-to-many relationship queries today which went much faster than I expected. Which was great because then we started on a big fun project! We will be building the database for an Instagram clone. The first task was to come up with a database schema to store and link users, photos, comments, likes, followers, followees, and hashtags. I built a scaffold to start, and over the next section we’ll build it as a code-along, so I’ll get to see how I can improve on what I came up with. It was fun (and complicated!) to come up with how the tables might be structured and how they would all fit together, but awesome practice for an app I think I’m going to start on pretty soon. Anyway I’m sure there are lots of improvements to be made but here’s what I came up with as a start:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(25) NOT NULL,
    user_photo VARCHAR(150) DEFAULT 'https://link.to/default.jpg'
);

CREATE TABLE photos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    photo_link VARCHAR(150) NOT NULL,
    uploaded TIMESTAMP DEFAULT NOW(),
    user_id INT NOT NULL,
    FOREIGN KEY(user_id) REFERENCES users(id)
);

CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    comment VARCHAR(500) NOT NULL,
    created TIMESTAMP DEFAULT NOW(),
    user_id INT NOT NULL,
    photo_id INT NOT NULL,
    FOREIGN KEY(user_id) REFERENCES users(id),
    FOREIGN KEY(photo_id) REFERENCES photos(id)
);

CREATE TABLE likes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    like BOOLEAN DEFAULT 0,
    created TIMESTAMP DEFAULT NOW(),
    user_id INT,
    photo_id INT,
    FOREIGN KEY(user_id) REFERENCES users(id),
    FOREIGN KEY(photo_id) REFERENCES photos(id)
);

CREATE TABLE hashtags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hashtag VARCHAR(50),
    photo_id INT,
    FOREIGN KEY(photo_id) REFERENCES photos(id)
);

CREATE TABLE followings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created TIMESTAMP DEFAULT NOW(),
    follower INT,
    followee INT,
    FOREIGN KEY(follower) REFERENCES users(id),
    FOREIGN KEY(followee) REFERENCES users(id)
);

And as always, notes from the course today have been added to this gist.

Hexo Website Update - New Home Page

I also put some work into this site today, and finally transformed it from just a blog into a full website. Yay! Now the blog is hosted in its own directory, and I have a landing page to introduce myself and all of the content. I also think it links better to the portfolio. And there are some other sections I expect to add in the future, so now all ready to go for that. I’m really liking working with this Hexo site because it’s so easy to maintain…so I can spend my time working on projects more than this site!

The ability to change your blog destination was only recently added to hexo-generator-index, the Hexo component that lists and paginates blog posts. But for some reason the newest version doesn’t come with the Hexo installation, so I had to update it manually:

npm install hexo-generator-index@0.2.1 --save

Then in the _config.yml (site, not theme) I updated the index generator path to blog:

index_generator:
  path: blog
  per_page: 10
  order_by: -date

Next was to create a new layout index.ejs for the index page, and then add an index.md file in the main source folder. The markdown file’s main purpose is to give Hexo a new index.html file to generate; I added the title and layout to the front matter, and filled in all of the content in the layout.

And voila! New home page and a blog under its own directory. This is long overdue but I’m glad to have it now!

Other Stuff

4 hours to braid my hair and I’m only half done 😫. I’m so glad I don’t have to do this too often!

Up Next

Plugging away at MySQL until I finish this week.