InnoDB

Era ora che colmassi le mie lacune in campo database.

E quando dico database, intendo MySql. Ovviamente.

Indipercui! Mi sono messo di buzzo buono a pensare come fare un sistema di gestione degli utenti & relativi permessi, che fosse flessibile e soprattutto facesse lo sporco lavoro di contorno in maniera totalmente autonoma.

Ergo, cari signori miei, i requisiti!

  1. Un utente deve avere un ruolo assegnato;
  2. I ruoli collegano un utente ai suoi permessi;
  3. Un utente deve avere meta-caratteristiche da aggiungere in futuro senza dover modificare le tabelle;
  4. Utf-8. E basta con ‘sto cazzo di iso-8859-1. Utf-8 per tutti i popoli rivoluzionari del mondo!
  5. Quando faccio una ricerca, questa deve essere veloce. In una parola, Index. In realtà la maggior parte delle query viene messa in cache, quindi solo in caso di cache miss la query viene realmente eseguita… Ma ciò non può essere una giustificazione valida per la nostra pigrizia!
  6. Quando cancello un utente/ruolo/permesso, la modifica deve essere autonomamente propagata alle altre tabelle. Non posso fare tutto io in php/perl/python/RoR/c++. In due parole: Foreign Index. Indice clandestino. È reato, sappiatelo.

Ecco il codice di creazione di un siffatto database:

/*
Creazione del DB: MyDatabase
o charset utf8 per supporto a tutte le lingue e caratteri strani.
o consigli:
- impostare utf8 come 'locale' del server
- impostare utf8 come codifica delle pagine
*/

CREATE DATABASE IF NOT EXISTS babel DEFAULT CHARACTER SET='utf8';
USE babel;

/*
Creazione utenti MySQL:
o Utente di amministrazione: master (solo su localhost)
o Utente normalmente usato dal webserver: normal (solo su localhost)
Per vedere gli utenti del database: lanciare il comando
SELECT Host,User FROM mysql.user;
*/

CREATE USER 'normal'@'localhost' IDENTIFIED BY 'p4$sw0R|)';
CREATE USER 'master'@'localhost' IDENTIFIED BY 's3cUr3-p4$sw0R|)';

GRANT SELECT,INSERT,UPDATE,DELETE ON MyDatabase.* TO 'normal'@'localhost';
GRANT ALL PRIVILEGES ON MyDatabase.* TO 'master'@'localhost';

/*
Creazione tabella users:
o max elementi: umediumint = 16.777.215
o indici: nick (unique).
o foreign keys: roles(id).
- cancellare un ruolo la' significa
impostare a null il solo campo qua.
- cambiare l'id di ruolo la' significa
cambiare il role_id qua.
*/

CREATE TABLE IF NOT EXISTS users(
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
role_id TINYINT UNSIGNED DEFAULT NULL,
status TINYINT NOT NULL,
password VARCHAR(20) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
nick VARCHAR(25) NOT NULL UNIQUE,
created DATETIME NOT NULL,
birth_date DATETIME NOT NULL,
nicename VARCHAR(200) UNIQUE,

PRIMARY KEY (id),

FOREIGN KEY (role_id)
REFERENCES roles(id)
ON DELETE SET NULL
ON UPDATE CASCADE,

UNIQUE INDEX (nick)
)
ENGINE=InnoDB
COMMENT='Users table, contains main user information.';

/*
Creazione tabella user_metas:
o max elementi: uint = 4.294.967.295
o indici: mkey (non unique)
o foreign keys: users(id)
- cancellare un utente la' significa
cancellare tutti i suoi metadati qua
- cambiare l'id utente la' significa
cambiare l'user_id qua.
*/

CREATE TABLE IF NOT EXISTS user_metas(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id MEDIUMINT UNSIGNED NOT NULL,
mkey CHAR(15) NOT NULL,
mvalue LONGTEXT NOT NULL,

PRIMARY KEY (id),

FOREIGN KEY (user_id)
REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE CASCADE,

INDEX (mkey)
)
ENGINE=InnoDB
COMMENT='Users meta table, contains more (trivial?) user information.';

/*
Creazione tabella roles:
o max elementi: utinyint = 255
o indici: name (unique).
*/

CREATE TABLE IF NOT EXISTS roles(
id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
descr TEXT,

PRIMARY KEY (id),

UNIQUE INDEX (name)
)
ENGINE=InnoDB
COMMENT='Roles assigned to users';

/*
Creazione tabella permissions:
o max elementi: usmallint = 65.535
o indici: name (unique)
*/

CREATE TABLE IF NOT EXISTS permissions(
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(15) NOT NULL,
descr TEXT,

PRIMARY KEY (id),

UNIQUE INDEX (name)
)
ENGINE=InnoDB
COMMENT='Every Permission that could be bound to a Role';

/*
Creazione tabella role_permission_binds:
o max elementi: utinyint * usmallint = 255 * 65.535 = 16.711.425 < umediumint
o indici: /
o foreign key: roles(id)
- cancellare un ruolo la' significa
cancellare tutte le associazioni a quel ruolo qua
- modificare l'id di un ruolo la' significa
modificare tutte le associazioni a quel ruolo qua
o foreign key: permissions(id)
- cancellare un permesso la' significa
cancellare tutte le associazioni a quel permesso qua
- modificare l'id di un permesso la' significa
modificare tutti i perm_id di quel permesso qua
*/

CREATE TABLE IF NOT EXISTS role_permission_binds(
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
role_id TINYINT UNSIGNED NOT NULL,
perm_id SMALLINT UNSIGNED NOT NULL,

PRIMARY KEY (id),

FOREIGN KEY (role_id)
REFERENCES roles(id)
ON DELETE CASCADE
ON UPDATE CASCADE,

FOREIGN KEY (perm_id)
REFERENCES permissions(id)
ON DELETE CASCADE
ON UPDATE CASCADE
)
ENGINE=InnoDB
COMMENT='Binds between Roles and Permissions';

Che dire? Ho fatto tutti i test del caso e funziona tutto egregiamente per quanto riguarda la sincronizzazione tra le varie tabelle!

Si è aperto un mondo nuovo davanti ai miei occhi… happy

Con questa base per gli utenti e i permessi, è molto più facile ora:

  • Implementare nuovi oggetti nel database (tutti con una loro brava tabellina *-metas);
  • Utilizzare framework che implementino il paradigma MVC (Model-View-Controllerlinkgo) senza preoccuparmi eccessivamente di quello che capita alle mie tabelle ormai autonome;

The Next Big Thing: CakePHPlinkgo!

Come al solito, sentitevi in dovere di fare delle segnalazioni se ho scritto vaccate.

Alla prossima!

Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

%d blogger cliccano Mi Piace per questo: