User - Apply SQL

Preface

Goal: Starting from Requirement.

Before making prototype. We need to make sure that this initial SQL is valid.

Prerequisite

We need MmySQL compatible in CLI or PHPMyAdmin. For this two to work, we need a LAMP stack, DAMP stack (docker) or XAMPP. I don’t know about Mac. I never have one. I have made a note on how to setup LAMP or DAMP. You might find them interesting.

• Docker Setup
• LAMP Overview

I’m using MariaDB for backend. You may use other.

Using MariaDB CLI

First we need to log in as root to create database. For a better solution, we should set privileges for specific user. But this time I’m going to make this simple.

Login

We need to log in as root to create database.

❯ sudo mariadb -u root -p
[sudo] password for epsi: 
Enter password: 

MariaDB [(none)]> \! reset

CREATE DATABASE and USE

We should create the database first.

MariaDB [(none)]> CREATE DATABASE Blewah;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE Blewah;
Database changed
MariaDB [Blewah]>

CREATE TABLE and INSERT

We can continue, creating table.

MariaDB [Blewah]> CREATE TABLE User (
    ->     id INT PRIMARY KEY,
    ->     username VARCHAR(255) NOT NULL,
    ->     password VARCHAR(255) NOT NULL,
    ->     email VARCHAR(255) NOT NULL
    -> );
Query OK, 0 rows affected (0.011 sec)

MariaDB [Blewah]> 

We can continue, creating table, also insert table.

MariaDB [Blewah]> INSERT INTO User (id, username, password, email)
    -> VALUES
    ->     (1, 'alice', '[hashed password]', 'alice@example.com'),
    ->     (2, 'bob', '[hashed password]', 'bob@example.com'),
    ->     (3, 'charlie', '[hashed password]', 'charlie@example.com');
Query OK, 3 rows affected (0.002 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [Blewah]> 

Using PHPMyAdmin

We can continue inserting fixture:

Insert Role

Insert The Rest

CREATE INDEX

We can also add index to each tables. For example for user in MariaDB CLI.

MariaDB [Blewah]> CREATE INDEX idx_user_username ON User (username);
Query OK, 0 rows affected (0.021 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [Blewah]> CREATE INDEX idx_user_email ON User (email);
Query OK, 0 rows affected (0.020 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [Blewah]>

Then we can continue to create index for role and permission in PHPMyAdmin.

And also the rest:

All Tables

Your MariaDB should have all these tables:

Your PHPMyAdmin should also have all these tables:

It is fun to see the relationship in Designer.

We can also check the console in PHPMyAdmin

I guess all SQL statements are valid.

Conclusion

Thank you for reading. We are not done. That is not all. We are not finished yet.

Maybe another article, another time. What do you think?