Wikipedia kaže: SQL ili “sequel” (Structured Query Language) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e. data incorporating relations among entities and variables.

Znači radi o sigurnom/optimalnom načinu pohrane podataka u bazu. Baza sprema podatke na sličan način na koji C jezik pohranjuje podatke u memoriju. Spremanje podataka na filesystem je sporo, neučinkovito i ako dođe do oštećenja teško je povratiti podatke. S druge strane baza ima mehanizme povrata podataka ukoliko dođe do oštećenja. Pada mi napamet, da li bi moderni filesystem, poput ZFS-a, bio dovoljan za sigurno/optimalno spremanje podataka u filesystem umjesto baze podataka? ZFS ima sve što i ta baza podataka već ugrađeno u sebi. Izolacija podataka se može riješiti sa enkripcijom ili unix user/group postavkama… Ali nema veze ‘ajmo mi o SQL-u.

Prije par godina Oracle je kupio (uništio?) Sun Microsystems. Pa je s time postao vlasnik i MySQL-a baze podataka. Ukratko, Oracle je prije par godina počeo naplaćivati MySQL pa je FOSS otpor proizveo MariaDB koju ćemo mi koristiti u današnjem zadatku/tutorialu.

phpMyAdmin

phpMyAdmin je alatčić koji nam omogućava vizualni prikaz baze podataka. Naravno paket je u svakom ozbiljnijem distro repo-u.

Da bi ga otvorili bez servera možemo koristiti ovu bash skriptu (radi samo na archlinux-based distrama):

#!/bin/bash
cd /usr/share/webapps/phpMyAdmin/
php -S localhost:9876

S time dobivamo phpMyAdmin na ‘localhost:9876’. I ulazimo sa sql root/šifra tekstom naše baze podataka.

Ajd u terminal

U terminal unosimo:

mysql

Dobivamo odgovor:

ERROR 1045 (28000): Access denied for user 'damir'@'localhost' (using password: NO)

Da bi se spojili na bazu (bez sudo):

mysql -u root -p

Da izlistamo baze, unosimo:

show databases;

Da izaberemo bazu, koristimo komandu ‘use’:

use ci4;

A da izlistamo tablice:

show tables;

Sad možemo koristiti sql komande na koje smo se već navikli na našem backend programskom jeziku.

select * from migrations;

I za kraj ovog bloka, kako uvesti/izvesti sql datoteku?

mysql -u root -p myDatabase < my_backup.sql
mysqldump -uroot -pYOURPASSWORD myDatabase > /tmp/mydb.sql

Nova baza

Idemo napraviti novu bazu. Koristimo moj user/db generator. 

./createdb.sh iooxblog

I dobivamo:

DROP DATABASE IF EXISTS iooxblog;
CREATE DATABASE iooxblog;
DROP USER IF EXISTS 'iooxblog'@'localhost';
CREATE USER 'iooxblog'@'localhost' IDENTIFIED BY 'iooxblog';
GRANT ALL ON iooxblog.* TO 'iooxblog'@'localhost';
USE iooxblog;

Skripta generira kod koji:

  • briše bazu ako postoji
  • pravi bazu
  • briše korisnika ako postoji
  • pravi korisnika
  • daje korisniku pristup bazi
  • ime baze, korisnika i šifra je ‘iooxblog’

Kod možemo ubaciti u SQL tab phpMyAdmin-a ili u terminal. Ako želimo promjeniti šifru mjenjamo dio: 

IDENTIFIED BY 'moja-šifra';

Pa se opet spajamo na bazu (bez sudo):

mysql -u root -p

I nakon ubacivanja SQL koda dobivamo:

Modeliranje baze

Da pojednostavimo, bazu dijelimo na: bazu, tablica, stupac i red. Znači Sql sadržava baze, baze tablice, tablice stupce i stupci redove. Ispred ‘users’ sam stavio ‘dslogin_’. Tako da ne bude problema ako već postoji npr. tablica ‘tags’. Kod koji pravi ‘dslogin_users’ tablicu izgleda ovako:

DROP TABLE IF EXISTS `dslogin_users`;
CREATE TABLE `dslogin_users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(250) NOT NULL,
  `password` varchar(200) NOT NULL,
  `created` datetime DEFAULT NOW(),
  PRIMARY KEY (`id`),
  CONSTRAINT uc_username UNIQUE (`username`)
) ENGINE=InnoDB;
DROP TABLE IF EXISTS `dslogin_users`;

Briše tablicu ako već postoji.

CREATE TABLE `dslogin_users` (
) ENGINE=InnoDB;

Pravi tablicu i sadržava tipove/entitete tablice.

`id` int NOT NULL AUTO_INCREMENT,

Svaka tablica treba imati primarni kljuć(pk)/id ali moguće je stvoriti tablicu i bez primarnoga ključa. ‘id’ je naziv stupca. int (integer, cijeli broj) je datatype ili tip podataka. Znači mi ovdje određujemo da u ovaj stupac samo može ići cijeli broj. NOT NULL znači da ovaj podatak mora biti unesen. AUTO_INCREMENT je kao u programiranju varijabla++, odnosno svaki put kada unesete podatke, ovaj broj će se povećati za jedan. Osim autoincrement+integer kao id/pk možemo koristiti i unique+bilo-koji-tip kombinaciju.

‘username’ je ime korisnika za koje smo odredili mjesto za (250) znakova sa varchar tipom koji se koristi za pohranu znakova (string).

‘created’ datetime DEFAULT NOW() nam daje ovaj oblik ‘2019-03-10 02:55:05’ prikaza datuma/vremena. NOW() je SQL funkcija koja vraća trenutni datum/vrijeme.

Na kraju nama ostaje “tvikanje” tako da, PRIMARY KEY (id) znači da je id stupac postavljen kao primarni ključ. 

CONSTRAINT uc_username UNIQUE (`username`)

znači da određujemo ‘username’ stupac kao UNIQUE. Što znači da nije dozvoljeno u redovima imati istu vrijednost dvaput.

Tag

Druga tablica izgleda ovako i sadržava tagove. Na primjer možemo napraviti tag ‘admin’ i povezati ga sa user-om.

DROP TABLE IF EXISTS `dslogin_tags`;
CREATE TABLE `dslogin_tags` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tagname` varchar(48) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT uc_tagname UNIQUE (`tagname`)
) ENGINE=InnoDB;

Shortcuts, links i strani ključevi

Ako želimo povezati podatke između tablica onda moramo koristiti ‘foreign keys’. Stvar funkcionira slično kao ENUM tip u C programiranju.

U biti, da bi povezali podatke ID-e/PK-e, koristimo id iz vanjske tablice. Treća tablica služi upravo za to. Da veže/spaja user i tag tablice:

DROP TABLE IF EXISTS `dslogin_usertag_links`;
CREATE TABLE `dslogin_usertag_links` (
    `tags_rid` int NOT NULL,
    `users_rid` int NOT NULL,
	CONSTRAINT `fk_dslogin_usertag_links` FOREIGN KEY (users_rid) REFERENCES dslogin_users (id) ON DELETE CASCADE,
	CONSTRAINT `fk_tags_rid` FOREIGN KEY (tags_rid) REFERENCES dslogin_tags (id) ON DELETE CASCADE,
    PRIMARY KEY (`tags_rid`, `users_rid`)
) ENGINE=InnoDB;

‘tags_rid’ je id iz ‘dslogin_tags’ a ‘users_rid’ je id iz ‘dslogin_users’.

CONSTRAINT `fk_tags_rid` FOREIGN KEY (tags_rid) REFERENCES dslogin_tags (id) ON DELETE CASCADE,

Ovdje je definirano da je ‘tags_rid’ shortcut/link na id u ‘tags’ tablici. A ‘ON DELETE CASCADE’ znači, ako id u ‘tags’ ne postoji, da se automatski izbriše iz ‘dslogin_usertag_links’. ‘fk_tags_rid’ je CONSTRAINT ime, može biti bilo kakav string.

Nakon što smo unijeli sve tri tablice naša baza izgleda ovako:

Ovaj prikaz imamo u phpMyAdmin Designer tabu.

Tags

Tag tablice ćemo popuniti sa tagovima vezanim u korisnike. Id stupac sam dodao zato da kada izbrišem tablicu, pa ponovo stvorim, dobijem podatke sa istim id brojevima.

INSERT INTO `dslogin_tags` (`id`, `tagname`) VALUES
(1, "administrators"),
(2, "moderators"),
(3, "users"),
(4, "banned"),
(5, "inactive"),
(6, "erased");

…i korisnike:

INSERT INTO `dslogin_users` (`id`, `username`, `password`) VALUES
(1, "Alpha", "1234"),
(2, "Zardoz", "1234"),
(3, "Beta", "1234"),
(4, "Gamma", "1234"),
(5, "Delta", "1234"),
(6, "Theta", "1234"),
(7, "Epsylon", "1234"),
(8, "Zeta", "1234");

Ok, kako da sada na primjer povežemo tag ‘administrators’ sa korisnikom ‘Zardoz’?

Budući da znamo id brojeve, možemo na primjer jednostavno:

INSERT INTO `dslogin_usertag_links` (`users_rid`, `tags_rid`) VALUES ( 1 , 2 ) ;

ili komplicirano, gdje prvo tražimo id sa SELECT:

INSERT INTO `dslogin_usertag_links` (`users_rid`, `tags_rid`) 
SELECT (SELECT `id` FROM `dslogin_users` WHERE `username` = 'Zardoz'), 
(SELECT `id` FROM `dslogin_tags` WHERE `tagname` = 'administrators') ;

Sada da provjerimo da CASCADE dio radi, idemo obrisati korisnika:

DELETE FROM `dslogin_users` WHERE `username` = 'Zardoz';

‘dslogin_usertag_links’ je prazan. Kad smo obrisali korisnika, izbrisani su i linkovi sa korisnikom.

Update

Ako želimo promijeniti vrijednost u redu, koristimo UPDATE statement. Tako da ako želimo promijeniti password od korisnika ‘Delta’:

UPDATE `dslogin_users` SET `password` = "xxx" WHERE `username` = 'Delta';

LIKE

Like koristimo za pretrage. Na primjer, kada netko upiše ‘gam’ pojam u tražilicu i želimo dobiti sve korsinike sa ‘gam’ u imenu:

SELECT * FROM `dslogin_users` WHERE `username` LIKE '%gam%' ;

LIMIT

Limit je koristan za ‘pagination’. Odnosno kada želimo prikazati određen broj artikala na stranici. Limit (kao array) počinje od nule.

SELECT * FROM dslogin_users LIMIT 0 , 3;

Nastavlja se…