Databáze ZoraData

Návrh jednoduché databáze

Ukazuje vytvoření základních tabulek, jejich logovacích triggerů a pohledů. Ukazuje využití uložených funkcí a procedur. Schéma databáze v PDF.
DELIMITER //

--
-- Vytvoření databáze
--
CREATE DATABASE `test` COLLATE 'utf8_general_ci'
//
USE test
//

--
-- Logovací tabulka
--
CREATE TABLE log
 ( id                       INT NOT NULL AUTO_INCREMENT, 
   date                     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   user_id                  INT COMMENT 'Id uživatele', 
   module                   VARCHAR(200) COMMENT 'Název modulu', 
   presenter                VARCHAR(200) COMMENT 'Název presenteru', 
   action                   VARCHAR(200) COMMENT 'Název akce',
   status                   INT NOT NULL DEFAULT 1 COMMENT 'Stav',
   remote_addr              VARCHAR(200) COMMENT 'Adresa odkud se přistupuje', 
   ip_addr                  VARCHAR(20) COMMENT 'IP adresa odkud se přistupuje',
   readonly                 BOOLEAN COMMENT 'Aplikace je v módu pouze čtení', 
   browser                  VARCHAR(2000) COMMENT 'Údaje o prohlížeči',
   description              VARCHAR(2000) COMMENT 'Podrobnější popis',
   data                     TEXT COMMENT 'Podrobnější data',
PRIMARY KEY (id), 
KEY log_i1 (date, user_id),
KEY log_i2 (user_id, date),
KEY log_i3 (date, module, presenter, action),  
KEY log_i4 (module, presenter, action, date),
KEY log_i5 (user_id, module, presenter, action, date) )
ENGINE=InnoDb MAX_ROWS=10000000000 COMMENT='Logovací tabulka'
//

--
-- Tabulka jazyků aplikace
--
CREATE TABLE language
 ( id                       INT NOT NULL COMMENT 'Id jayzka',
   code                     VARCHAR(20) NOT NULL COMMENT 'Kód jazyka (národního prostředí v PHP)',
   name                     VARCHAR(60) NOT NULL COMMENT 'Název jazyka',
   db_environment           VARCHAR(20) NOT NULL COMMENT 'Kód databázového národního prostředí',
   dbchanged                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY language_i1 (code),
UNIQUE KEY language_i2 (name) )
ENGINE=InnoDb MAX_ROWS=10 COMMENT='Jazyky aplikace'
//

--
-- Tabulka parametrů aplikace
--
CREATE TABLE param
 ( param                    VARCHAR(60) NOT NULL COMMENT 'Kód parametru',
   level                    ENUM ('B', 'S', 'U') NOT NULL DEFAULT 'S' COMMENT 'Úroveň parametru(B=Základ, S=System, U=uživatel', 
   type                     ENUM ('C', 'N', 'D', 'B', 'L') NOT NULL DEFAULT 'C' COMMENT 'Typ parametru (C=char, N=Number, D=Date, B=Boolean, L=List)',
   default_value            VARCHAR(2000) COMMENT 'Standardní hodnota parametru',
   value                    VARCHAR(2000) COMMENT 'Aktuální hodnota parametru',
   description              VARCHAR(2000) COMMENT 'Popis parametru',
   created_at               DATETIME NOT NULL COMMENT 'Datum a čas založení záznamu', 
   created_by               INT NOT NULL COMMENT 'Aplikační uživatel, který záznam založil', 
   created_by_db            VARCHAR(64) NOT NULL COMMENT 'Databázový uživatel, který záznam založil',
   updated_at               DATETIME COMMENT 'Datum a čas poslední změny záznamu', 
   updated_by               INT COMMENT 'Aplikační uživatel, který záznam poslední změnil', 
   updated_by_db            VARCHAR(64) COMMENT 'Databázový uživatel, který záznam poslední změnil',
   dbchanged                TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
PRIMARY KEY (param) )
ENGINE=InnoDb MAX_ROWS=500 COMMENT='Parametry aplikace'
//
CREATE TRIGGER param_bi BEFORE INSERT ON param
   FOR EACH ROW
BEGIN
   CALL set_stamp(NEW.created_at, NEW.created_by, NEW.created_by_db);
END;
//
CREATE TRIGGER param_bu BEFORE UPDATE ON param
   FOR EACH ROW
BEGIN
   CALL set_stamp(NEW.updated_at, NEW.updated_by, NEW.updated_by_db);
END;
//

--
-- Tabulka možných hodnot parametrů aplikace
--
CREATE TABLE param_list
 ( id                       INT NOT NULL AUTO_INCREMENT COMMENT 'Id záznamu',
   param                    VARCHAR(60) NOT NULL COMMENT 'Kód parametru',
   sentence                 INT NOT NULL DEFAULT 0 COMMENT 'Pořadí hodnoty', 
   value                    VARCHAR(200) NOT NULL COMMENT 'Hodnota', 
   description              VARCHAR(200) NOT NULL COMMENT 'Popis hodnoty', 
   created_at               DATETIME NOT NULL COMMENT 'Datum a čas založení záznamu', 
   created_by               INT NOT NULL COMMENT 'Aplikační uživatel, který záznam založil', 
   created_by_db            VARCHAR(64) NOT NULL COMMENT 'Databázový uživatel, který záznam založil',
   updated_at               DATETIME COMMENT 'Datum a čas poslední změny záznamu', 
   updated_by               INT COMMENT 'Aplikační uživatel, který záznam poslední změnil', 
   updated_by_db            VARCHAR(64) COMMENT 'Databázový uživatel, který záznam poslední změnil',
   dbchanged                TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
PRIMARY KEY (id),
UNIQUE KEY param_list_i1 (param, sentence, value),
UNIQUE KEY param_list_i2 (param, value),
UNIQUE KEY param_list_i3 (param, description),
CONSTRAINT param_list_c1 FOREIGN KEY (param) REFERENCES param (param) ON UPDATE CASCADE ON DELETE CASCADE )
ENGINE=InnoDb MAX_ROWS=500 COMMENT='Parametry aplikace'
//
CREATE TRIGGER param_list_bi BEFORE INSERT ON param_list
   FOR EACH ROW
BEGIN
   CALL set_stamp(NEW.created_at, NEW.created_by, NEW.created_by_db);
END;
//
CREATE TRIGGER param_list_bu BEFORE UPDATE ON param_list
   FOR EACH ROW
BEGIN
   CALL set_stamp(NEW.updated_at, NEW.updated_by, NEW.updated_by_db);
END;
//

--
-- Tabulka uživateslkých rolí
--
CREATE TABLE role
 ( id                       INT NOT NULL AUTO_INCREMENT COMMENT 'Id role', 
   name                     VARCHAR(30) NOT NULL COMMENT 'Jméno role', 
   description              VARCHAR(2000) COMMENT 'Popis role', 
   enabled                  BOOLEAN NOT NULL DEFAULT TRUE COMMENT 'Je role aktivní?',
   created_at               DATETIME NOT NULL COMMENT 'Datum a čas založení záznamu', 
   created_by               INT NOT NULL COMMENT 'Aplikační uživatel, který záznam založil', 
   created_by_db            VARCHAR(64) NOT NULL COMMENT 'Databázový uživatel, který záznam založil',
   updated_at               DATETIME COMMENT 'Datum a čas poslední změny záznamu', 
   updated_by               INT COMMENT 'Aplikační uživatel, který záznam poslední změnil', 
   updated_by_db            VARCHAR(64) COMMENT 'Databázový uživatel, který záznam poslední změnil',
   dbchanged                TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
PRIMARY KEY (id),  
UNIQUE KEY role_i1 (name), 
UNIQUE KEY role_i2 (enabled, name) )
ENGINE=InnoDb MAX_ROWS=10 COMMENT='Přístupové role pro uživatele'
//
CREATE TRIGGER role_bi BEFORE INSERT ON role
   FOR EACH ROW
BEGIN
   CALL set_stamp(NEW.created_at, NEW.created_by, NEW.created_by_db);
END;
//
CREATE TRIGGER role_bu BEFORE UPDATE ON role
   FOR EACH ROW
BEGIN
   CALL set_stamp(NEW.updated_at, NEW.updated_by, NEW.updated_by_db);
END;
//

--
-- Tabulka uživateslkých rolí
--
CREATE TABLE privilege
 ( id                       INT NOT NULL AUTO_INCREMENT COMMENT 'Id práva', 
   module                   VARCHAR(60) NOT NULL COMMENT 'Název modulu', 
   presenter                VARCHAR(60) NOT NULL COMMENT 'Název presenteru', 
   action                   VARCHAR(60) NOT NULL COMMENT 'Název akce', 
   description              VARCHAR(200) COMMENT 'Popis přístupu', 
   enabled                  BOOLEAN NOT NULL DEFAULT TRUE COMMENT 'Je přístup aktivní?',
   created_at               DATETIME NOT NULL COMMENT 'Datum a čas založení záznamu', 
   created_by               INT NOT NULL COMMENT 'Aplikační uživatel, který záznam založil', 
   created_by_db            VARCHAR(64) NOT NULL COMMENT 'Databázový uživatel, který záznam založil',
   updated_at               DATETIME COMMENT 'Datum a čas poslední změny záznamu', 
   updated_by               INT COMMENT 'Aplikační uživatel, který záznam poslední změnil', 
   updated_by_db            VARCHAR(64) COMMENT 'Databázový uživatel, který záznam poslední změnil',
   dbchanged                TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
PRIMARY KEY (id),  
UNIQUE KEY privilege_i1 (module, presenter, action) )
ENGINE=InnoDb MAX_ROWS=1000 COMMENT='Přístupová práva aplikace'
//
CREATE TRIGGER privilege_bi BEFORE INSERT ON privilege
   FOR EACH ROW
BEGIN
   CALL set_stamp(NEW.created_at, NEW.created_by, NEW.created_by_db);
END;
//
CREATE TRIGGER privilege_bu BEFORE UPDATE ON privilege
   FOR EACH ROW
BEGIN
   CALL set_stamp(NEW.updated_at, NEW.updated_by, NEW.updated_by_db);
END;
//

--
-- Tabulka přiřazení práv rolím
--
CREATE TABLE role_privilege
 ( id                       INT NOT NULL AUTO_INCREMENT COMMENT 'Id záznamu', 
   role_id                  INT NOT NULL COMMENT 'Id role uživatele',
   privilege_id             INT NOT NULL COMMENT 'Id přístupového práva',
   created_at               DATETIME NOT NULL COMMENT 'Datum a čas založení záznamu', 
   created_by               INT NOT NULL COMMENT 'Aplikační uživatel, který záznam založil', 
   created_by_db            VARCHAR(64) NOT NULL COMMENT 'Databázový uživatel, který záznam založil',
   updated_at               DATETIME COMMENT 'Datum a čas poslední změny záznamu', 
   updated_by               INT COMMENT 'Aplikační uživatel, který záznam poslední změnil', 
   updated_by_db            VARCHAR(64) COMMENT 'Databázový uživatel, který záznam poslední změnil',
   dbchanged                TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
PRIMARY KEY (id),  
UNIQUE KEY role_privilege_i1 (role_id, privilege_id), 
UNIQUE KEY role_privilege_i2 (privilege_id, role_id),
CONSTRAINT role_privilege_c1 FOREIGN KEY (role_id) REFERENCES role (id) ON UPDATE RESTRICT ON DELETE CASCADE,
CONSTRAINT role_privilege_c2 FOREIGN KEY (privilege_id) REFERENCES privilege (id) ON UPDATE RESTRICT ON DELETE CASCADE )
ENGINE=InnoDb MAX_ROWS=1000 COMMENT='Přiřazení práv rolím'
//
CREATE TRIGGER role_privilege_bi BEFORE INSERT ON role_privilege
   FOR EACH ROW
BEGIN
   CALL set_stamp(NEW.created_at, NEW.created_by, NEW.created_by_db);
END;
//
CREATE TRIGGER role_privilege_bu BEFORE UPDATE ON role_privilege
   FOR EACH ROW
BEGIN
   CALL set_stamp(NEW.updated_at, NEW.updated_by, NEW.updated_by_db);
END;
//

--
-- Tabulka počtů
--
CREATE TABLE sentence
 ( sentence                 INT NOT NULL,
PRIMARY KEY (sentence ) )
ENGINE=InnoDb MAX_ROWS=100000 COMMENT='Tabulka počtů'
//

--
-- Tabulka textů aplikace
--
CREATE TABLE text
 ( name                     VARCHAR(60) NOT NULL COMMENT 'Název textu', 
   description              VARCHAR(2000) COMMENT 'Popis textu', 
   created_at               DATETIME NOT NULL COMMENT 'Datum a čas založení záznamu', 
   created_by               INT NOT NULL COMMENT 'Aplikační uživatel, který záznam založil', 
   created_by_db            VARCHAR(64) NOT NULL COMMENT 'Databázový uživatel, který záznam založil',
   updated_at               DATETIME COMMENT 'Datum a čas poslední změny záznamu', 
   updated_by               INT COMMENT 'Aplikační uživatel, který záznam poslední změnil', 
   updated_by_db            VARCHAR(64) COMMENT 'Databázový uživatel, který záznam poslední změnil',
   dbchanged                TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
PRIMARY KEY (name))
ENGINE=InnoDb MAX_ROWS=100 COMMENT='Texty aplikace'
//
CREATE TRIGGER text_bi BEFORE INSERT ON text
   FOR EACH ROW
BEGIN
   CALL stamp_set(NEW.created_at, NEW.created_by, NEW.created_by_db);
END;
//
CREATE TRIGGER text_bu BEFORE UPDATE ON text
   FOR EACH ROW
BEGIN
   CALL stamp_set(NEW.updated_at, NEW.updated_by, NEW.updated_by_db);
END;
//

--
-- Tabulka jazykových variant textů
--
CREATE TABLE text_lang
 ( id                       INT NOT NULL AUTO_INCREMENT COMMENT 'Id záznamu', 
   name                     VARCHAR(60) NOT NULL COMMENT 'Název textu', 
   language_id              INT NOT NULL COMMENT 'Id jayzka', 
   content                  TEXT COMMENT 'Obsah textu',
   created_at               DATETIME NOT NULL COMMENT 'Datum a čas založení záznamu', 
   created_by               INT NOT NULL COMMENT 'Aplikační uživatel, který záznam založil', 
   created_by_db            VARCHAR(64) NOT NULL COMMENT 'Databázový uživatel, který záznam založil',
   updated_at               DATETIME COMMENT 'Datum a čas poslední změny záznamu', 
   updated_by               INT COMMENT 'Aplikační uživatel, který záznam poslední změnil', 
   updated_by_db            VARCHAR(64) COMMENT 'Databázový uživatel, který záznam poslední změnil',
   dbchanged                TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
PRIMARY KEY (id), 
UNIQUE KEY text_lang_i1 (name, language_id), 
UNIQUE KEY text_lang_i2 (language_id, name),
CONSTRAINT text_lang_c1 FOREIGN KEY (name) REFERENCES text (name) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT text_lang_c2 FOREIGN KEY (language_id) REFERENCES language (id) ON UPDATE RESTRICT ON DELETE CASCADE )
ENGINE=InnoDb MAX_ROWS=10000 COMMENT='Texty aplikace - Jazykové mutace'
//
CREATE TRIGGER text_lang_bi BEFORE INSERT ON text_lang
   FOR EACH ROW
BEGIN
   CALL stamp_set(NEW.created_at, NEW.created_by, NEW.created_by_db);
END;
//
CREATE TRIGGER text_lang_bu BEFORE UPDATE ON text_lang
   FOR EACH ROW
BEGIN
   CALL stamp_set(NEW.updated_at, NEW.updated_by, NEW.updated_by_db);
END;
//

--
-- Tabulka parametrů textů
--
CREATE TABLE text_param
 ( id                       INT NOT NULL AUTO_INCREMENT COMMENT 'Id záznamu',
   name                     VARCHAR(60) NOT NULL COMMENT 'Název parametru', 
   sentence                 INT NOT NULL COMMENT 'Pořadí parametru',
   code                     VARCHAR(60) NOT NULL COMMENT 'Kód parametru', 
   description              VARCHAR(200) COMMENT 'Význam (popis) parametru', 
   created_at               DATETIME NOT NULL COMMENT 'Datum a čas založení záznamu', 
   created_by               INT NOT NULL COMMENT 'Aplikační uživatel, který záznam založil', 
   created_by_db            VARCHAR(64) NOT NULL COMMENT 'Databázový uživatel, který záznam založil',
   updated_at               DATETIME COMMENT 'Datum a čas poslední změny záznamu', 
   updated_by               INT COMMENT 'Aplikační uživatel, který záznam poslední změnil', 
   updated_by_db            VARCHAR(64) COMMENT 'Databázový uživatel, který záznam poslední změnil',
   dbchanged                TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
PRIMARY KEY (id), 
UNIQUE KEY text_param_i1 (name, sentence, code),
UNIQUE KEY text_param_i2 (name, code),
CONSTRAINT text_param_c1 FOREIGN KEY (name) REFERENCES text (name) ON UPDATE CASCADE ON DELETE CASCADE )
ENGINE=InnoDb MAX_ROWS=10000 COMMENT='Jádro - Texty aplikace - Parametry textů'
//
CREATE TRIGGER text_param_bi BEFORE INSERT ON text_param
   FOR EACH ROW
BEGIN
   CALL stamp_set(NEW.created_at, NEW.created_by, NEW.created_by_db);
END;
//
CREATE TRIGGER text_param_bu BEFORE UPDATE ON text_param
   FOR EACH ROW
BEGIN
   CALL stamp_set(NEW.updated_at, NEW.updated_by, NEW.updated_by_db);
END;
//

--
-- Tabulka uživatelů
--
CREATE TABLE user
 ( id                       INT NOT NULL AUTO_INCREMENT COMMENT 'Id uživatele',
   email                    VARCHAR(255) NOT NULL COMMENT 'E-mail (login) uživatele', 
   name                     VARCHAR(60) NOT NULL COMMENT 'Jméno uživatele',
   password                 VARCHAR(255) NOT NULL COMMENT 'Heslo uživatele', 
   description              VARCHAR(2000) COMMENT 'Poznámka k uživateli', 
   enabled                  BOOLEAN NOT NULL DEFAULT TRUE COMMENT 'Je uživatel aktivní?',
   language_id              INT NOT NULL DEFAULT 1 COMMENT 'Id jazyka',
   token                    VARCHAR(255) NOT NULL COMMENT 'Token pro přihlášení do API',
   change_password          BOOLEAN NOT NULL DEFAULT TRUE COMMENT 'Vynucená změna hesla?',
   password_at              DATETIME COMMENT 'Datum a čas poslední změny hesla', 
   password_by              INT COMMENT 'Aplikační uživatel, který poslední změnil heslo', 
   password_by_db           VARCHAR(64) COMMENT 'Databázový uživatel, který poslední změnil heslo', 
   token_at                 DATETIME COMMENT 'Datum a čas poslední změny tokenu', 
   token_by                 INT COMMENT 'Aplikační uživatel, který poslední změnil token', 
   token_by_db              VARCHAR(64) COMMENT 'Databázový uživatel, který poslední změnil token', 
   created_at               DATETIME NOT NULL COMMENT 'Datum a čas založení záznamu', 
   created_by               INT NOT NULL COMMENT 'Aplikační uživatel, který záznam založil', 
   created_by_db            VARCHAR(64) NOT NULL COMMENT 'Databázový uživatel, který záznam založil',
   updated_at               DATETIME COMMENT 'Datum a čas poslední změny záznamu', 
   updated_by               INT COMMENT 'Aplikační uživatel, který záznam poslední změnil', 
   updated_by_db            VARCHAR(64) COMMENT 'Databázový uživatel, který záznam poslední změnil',
   dbchanged                TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
PRIMARY KEY (id), 
UNIQUE KEY user_i1 (email), 
UNIQUE KEY user_i2 (enabled, email), 
CONSTRAINT user_c2 FOREIGN KEY (language_id) REFERENCES language (id) ON UPDATE RESTRICT ON DELETE RESTRICT )
ENGINE=InnoDb AUTO_INCREMENT=127 MAX_ROWS=100000 COMMENT='Uživatelé'
//
CREATE TRIGGER user_bi BEFORE INSERT ON user
   FOR EACH ROW
BEGIN
   CALL set_stamp(NEW.created_at, NEW.created_by, NEW.created_by_db);
   SET NEW.password_at = now();
   SET NEW.password_by = get_appl_user();
   SET NEW.password_by_db = current_user();
   SET NEW.token = generate_token(NEW.email);
   SET NEW.token_at = now();
   SET NEW.token_by = get_appl_user();
   SET NEW.token_by_db = current_user();
END;
//
CREATE TRIGGER user_bu BEFORE UPDATE ON user
   FOR EACH ROW
BEGIN
   CALL set_stamp(NEW.updated_at, NEW.updated_by, NEW.updated_by_db);
   IF NEW.password != OLD.password
   THEN SET NEW.password_at = now();
        SET NEW.password_by = get_appl_user();
        SET NEW.password_by_db = current_user();
   END IF;
   IF NEW.token != OLD.token
   THEN SET NEW.token_at = now();
        SET NEW.token_by = get_appl_user();
        SET NEW.token_by_db = current_user();
   END IF;
END;
//

--
-- Tabulka přiřazení rolí uživatelům
--
CREATE TABLE user_role
 ( id                       INT NOT NULL AUTO_INCREMENT COMMENT 'Id záznamu', 
   user_id                  INT NOT NULL COMMENT 'Id uživatele',
   role_id                  INT NOT NULL COMMENT 'Id role uživatele',
   created_at               DATETIME NOT NULL COMMENT 'Datum a čas založení záznamu', 
   created_by               INT NOT NULL COMMENT 'Aplikační uživatel, který záznam založil', 
   created_by_db            VARCHAR(64) NOT NULL COMMENT 'Databázový uživatel, který záznam založil',
   updated_at               DATETIME COMMENT 'Datum a čas poslední změny záznamu', 
   updated_by               INT COMMENT 'Aplikační uživatel, který záznam poslední změnil', 
   updated_by_db            VARCHAR(64) COMMENT 'Databázový uživatel, který záznam poslední změnil',
   dbchanged                TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
PRIMARY KEY (id),  
UNIQUE KEY user_role_i1 (user_id, role_id), 
UNIQUE KEY user_role_i2 (role_id, user_id),
CONSTRAINT user_role_c1 FOREIGN KEY (user_id) REFERENCES user (id) ON UPDATE RESTRICT ON DELETE CASCADE,
CONSTRAINT user_role_c2 FOREIGN KEY (role_id) REFERENCES role (id) ON UPDATE RESTRICT ON DELETE CASCADE )
ENGINE=InnoDb MAX_ROWS=10000000 COMMENT='Přiřazení rolí uživatelům'
//
CREATE TRIGGER user_role_bi BEFORE INSERT ON user_role
   FOR EACH ROW
BEGIN
   CALL set_stamp(NEW.created_at, NEW.created_by, NEW.created_by_db);
END;
//
CREATE TRIGGER user_role_bu BEFORE UPDATE ON user_role
   FOR EACH ROW
BEGIN
   CALL set_stamp(NEW.updated_at, NEW.updated_by, NEW.updated_by_db);
END;
//

--
-- Tabulka uživatelských parametrů aplikace
--
--
-- Tabulka uživatelských parametrů aplikace
--
CREATE TABLE user_param
 ( id                       INT NOT NULL AUTO_INCREMENT COMMENT 'Id záznamu', 
   user_id                  INT NOT NULL COMMENT 'Id uživatele', 
   param                    VARCHAR(60) NOT NULL COMMENT 'Název parametru', 
   value                    VARCHAR(2000) COMMENT 'Hodnota parametru',
   created_at               DATETIME NOT NULL COMMENT 'Datum a čas založení záznamu', 
   created_by               INT NOT NULL COMMENT 'Aplikační uživatel, který záznam založil', 
   created_by_db            VARCHAR(64) NOT NULL COMMENT 'Databázový uživatel, který záznam založil',
   updated_at               DATETIME COMMENT 'Datum a čas poslední změny záznamu', 
   updated_by               INT COMMENT 'Aplikační uživatel, který záznam poslední změnil', 
   updated_by_db            VARCHAR(64) COMMENT 'Databázový uživatel, který záznam poslední změnil',
   dbchanged                TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
PRIMARY KEY (id), 
UNIQUE KEY user_param_i1 (user_id, param), 
UNIQUE KEY user_param_i2 (param, user_id ),
CONSTRAINT user_param_c1 FOREIGN KEY (user_id) REFERENCES user (id) ON UPDATE RESTRICT ON DELETE CASCADE,
CONSTRAINT user_param_c2 FOREIGN KEY (param) REFERENCES param (param) ON UPDATE CASCADE ON DELETE CASCADE )
ENGINE=InnoDb MAX_ROWS=100000 COMMENT='Parametry uživatelů'
//
CREATE TRIGGER user_param_bi BEFORE INSERT ON user_param
   FOR EACH ROW
BEGIN
   CALL set_stamp(NEW.created_at, NEW.created_by, NEW.created_by_db);
END;
//
CREATE TRIGGER user_param_bu BEFORE UPDATE ON user_param
   FOR EACH ROW
BEGIN
   CALL set_stamp(NEW.updated_at, NEW.updated_by, NEW.updated_by_db);
END;
//

--
-- Nastavení ID aplikačního uživatele do DB session
--
CREATE PROCEDURE set_appl_user ( i_user INT )
   COMMENT 'Nastaví id aktuálního aplikačního uživatele'
   SQL SECURITY INVOKER 
BEGIN
   SET @appl_user := i_user;
END;
//

--
-- Načtení ID aplikačního uživatele
--
CREATE FUNCTION get_appl_user () RETURNS INT
   NO SQL
   COMMENT 'Vrací id aktuálního aplikačního uživatele'
   SQL SECURITY INVOKER 
BEGIN
   RETURN IFNULL(@appl_user, 0);
END;
//

--
-- Procedura pro logovací triggery
--
CREATE PROCEDURE set_stamp ( INOUT o_date DATETIME, INOUT o_user_id INT, INOUT o_user_db VARCHAR(64) )
   COMMENT 'Pro logování přístupu k záznamu'
   SQL SECURITY INVOKER 
BEGIN
   SET o_date = now();
   SET o_user_id = get_appl_user();
   SET o_user_db = current_user();
END;
//

--
-- Nastavení tabulky počtů
--
CREATE PROCEDURE set_sentence
 ( p_from INT,
   p_to INT )
   COMMENT 'Nastavení počtů' 
   SQL SECURITY INVOKER
BEGIN 
DECLARE a_count INT;
  SET a_count = p_from;
  WHILE a_count <= p_to DO
     INSERT INTO sentence (sentence) VALUES (a_count);
     SET a_count = a_count + 1;
  END WHILE;
END;
//

--
-- Generování přístupového tokenu
--
CREATE FUNCTION generate_token
  ( p_user_email VARCHAR(255) ) RETURNS VARCHAR(255)
   COMMENT 'Vrací vygenerovanou hodnotu API tokenu'
   NO SQL
   SQL SECURITY INVOKER
BEGIN
  RETURN SHA1(CONCAT(p_user_email, now()));
END;
//

--
-- Načtení systémové hodnoty parametru aplikace
--
CREATE FUNCTION get_system_param
  ( p_param VARCHAR(60) ) RETURNS VARCHAR(2000)
   COMMENT 'Vrací hodnotu zadaného parametru'
   READS SQL DATA
   SQL SECURITY INVOKER
BEGIN
  DECLARE x_value VARCHAR(2000);
  SELECT value
  INTO   x_value
  FROM   param
  WHERE  param = p_param;
  RETURN x_value;
END;
//

--
-- Formátování datumu
--
CREATE FUNCTION date_output
  ( p_date DATETIME ) RETURNS VARCHAR(60)
   COMMENT 'Vrací formátované datum'
   READS SQL DATA
   SQL SECURITY INVOKER
BEGIN
  DECLARE x_format VARCHAR(200);
  SET x_format = IFNULL(get_user_param('dateFormatOutput'), '%e.%c.%Y');
  RETURN DATE_FORMAT(p_date, x_format);
END;
//

--
-- Formátování datumu a času
--
CREATE FUNCTION datetime_output
  ( p_date DATETIME ) RETURNS VARCHAR(60)
   COMMENT 'Vrací formátované datum a čas'
   READS SQL DATA
   SQL SECURITY INVOKER
BEGIN
  DECLARE x_format VARCHAR(200);
  SET x_format = IFNULL(get_user_param('datetimeFormatOutput'), '%e.%c.%Y %H:%i:%s');
  RETURN DATE_FORMAT(p_date, x_format);
END;
//

--
-- Formátování času
--
CREATE FUNCTION time_output
  ( p_date DATETIME ) RETURNS VARCHAR(60)
   COMMENT 'Vrací formátovaný čas'
   READS SQL DATA
   SQL SECURITY INVOKER
BEGIN
  DECLARE x_format VARCHAR(200);
  SET x_format = IFNULL(get_user_param('timeFormatOutput'), '%H:%i:%s');
  RETURN DATE_FORMAT(p_date, x_format);
END;
//

--
-- Pohled na uživatele aplikace
--
CREATE VIEW user_v AS
SELECT U.id id,
       U.email email, 
       U.name name, 
       U.password password, 
       U.description description,
       U.enabled, 
       U.change_password change_password,
       U.password_at password_at, 
       datetime_output(U.password_at) password_at_f, 
       U.password_by password_by, 
       U3.name password_by_name, 
       U.password_by_db password_by_db, 
       U.created_at created_at, 
       datetime_output(U.created_at) created_at_f, 
       U.created_by created_by, 
       U1.name created_by_name, 
       U.created_by_db created_by_db, 
       U.updated_at updated_at, 
       datetime_output(U.updated_at) updated_at_f, 
       U.updated_by updated_by, 
       U2.name updated_by_name, 
       U.updated_by_db updated_by_db, 
       CASE WHEN U.updated_at IS NULL THEN U.created_at ELSE U.updated_at END changed_at, 
       CASE WHEN U.updated_at IS NULL THEN datetime_output(U.created_at) ELSE datetime_output(U.updated_at) END changed_at_f, 
       CASE WHEN U.updated_at IS NULL THEN U.created_by ELSE U.updated_by END changed_by, 
       CASE WHEN U.updated_at IS NULL THEN U1.name ELSE U2.name END changed_by_name, 
       CASE WHEN U.updated_at IS NULL THEN U.created_by_db ELSE U.updated_by_db END changed_by_db, 
       U.dbchanged dbchanged
FROM user U
LEFT JOIN user U1 ON U1.id = U.created_by 
LEFT JOIN user U2 ON U2.id = U.updated_by 
LEFT JOIN user U3 ON U3.id = U.password_by
//

--
-- Pohled na aktuálního uživatele aplikace
--
CREATE VIEW user_u_v AS
SELECT U.id id,
       U.email email, 
       U.name name, 
       U.password password, 
       U.description description,
       U.enabled, 
       U.change_password change_password,
       U.password_at password_at, 
       datetime_output(U.password_at) password_at_f, 
       U.password_by password_by, 
       U3.name password_by_name, 
       U.password_by_db password_by_db, 
       U.created_at created_at, 
       datetime_output(U.created_at) created_at_f, 
       U.created_by created_by, 
       U1.name created_by_name, 
       U.created_by_db created_by_db, 
       U.updated_at updated_at, 
       datetime_output(U.updated_at) updated_at_f, 
       U.updated_by updated_by, 
       U2.name updated_by_name, 
       U.updated_by_db updated_by_db, 
       CASE WHEN U.updated_at IS NULL THEN U.created_at ELSE U.updated_at END changed_at, 
       CASE WHEN U.updated_at IS NULL THEN datetime_output(U.created_at) ELSE datetime_output(U.updated_at) END changed_at_f, 
       CASE WHEN U.updated_at IS NULL THEN U.created_by ELSE U.updated_by END changed_by, 
       CASE WHEN U.updated_at IS NULL THEN U1.name ELSE U2.name END changed_by_name, 
       CASE WHEN U.updated_at IS NULL THEN U.created_by_db ELSE U.updated_by_db END changed_by_db, 
       U.dbchanged dbchanged
FROM user U
LEFT JOIN user U1 ON U1.id = U.created_by 
LEFT JOIN user U2 ON U2.id = U.updated_by 
LEFT JOIN user U3 ON U3.id = U.password_by
WHERE U.id = get_appl_user()
//

--
-- Pohled na uživatele pro přihlášení do aplikace
--
CREATE VIEW user_login_v AS
SELECT A.id id, 
       A.email email,
       A.name name, 
       A.password password, 
       A.token token, 
       A.description description,
       A.change_password,
       now() actual_datetime, 
       datetime_output(now()) actual_datetime_f
FROM user A
WHERE A.enabled = TRUE
//

--
-- Pohled na role uživatelů
--
CREATE VIEW role_v AS
SELECT R.id id, 
       R.name name, 
       R.description description, 
       R.enabled enabled,
       R.created_at created_at, 
       datetime_output(R.created_at) created_at_f, 
       R.created_by created_by, 
       U1.name created_by_name, 
       R.created_by_db created_by_db, 
       R.updated_at updated_at, 
       datetime_output(R.updated_at) updated_at_f, 
       R.updated_by updated_by, 
       U2.name updated_by_name, 
       R.updated_by_db updated_by_db, 
       CASE WHEN R.updated_at IS NULL THEN R.created_at ELSE R.updated_at END changed_at, 
       CASE WHEN R.updated_at IS NULL THEN datetime_output(R.created_at) ELSE datetime_output(R.updated_at) END changed_at_f, 
       CASE WHEN R.updated_at IS NULL THEN R.created_by ELSE R.updated_by END changed_by, 
       CASE WHEN R.updated_at IS NULL THEN U1.name ELSE U2.name END changed_by_name, 
       CASE WHEN R.updated_at IS NULL THEN R.created_by_db ELSE R.updated_by_db END changed_by_db, 
       R.dbchanged dbchanged
FROM role R
LEFT JOIN user U1 ON U1.id = R.created_by
LEFT JOIN user U2 ON U2.id = R.updated_by
//

--
-- Pohled na přiřazení rolí uživatelům
--
CREATE VIEW user_role_v AS
SELECT X.id id, 
       X.user_id user_id,
       U.email user_email,
       U.name user_name,
       X.role_id role_id,
       R.name role_name,
       R.description role_description,
       X.created_at created_at, 
       datetime_output(X.created_at) created_at_f, 
       X.created_by created_by, 
       U1.name created_by_name, 
       X.created_by_db created_by_db, 
       X.updated_at updated_at, 
       datetime_output(X.updated_at) updated_at_f, 
       X.updated_by updated_by, 
       U2.name updated_by_name, 
       X.updated_by_db updated_by_db, 
       CASE WHEN X.updated_at IS NULL THEN X.created_at ELSE X.updated_at END changed_at, 
       CASE WHEN X.updated_at IS NULL THEN datetime_output(X.created_at) ELSE datetime_output(X.updated_at) END changed_at_f, 
       CASE WHEN X.updated_at IS NULL THEN X.created_by ELSE X.updated_by END changed_by, 
       CASE WHEN X.updated_at IS NULL THEN U1.name ELSE U2.name END changed_by_name, 
       CASE WHEN X.updated_at IS NULL THEN X.created_by_db ELSE X.updated_by_db END changed_by_db, 
       X.dbchanged dbchanged
FROM user U
JOIN user_role X ON X.user_id = U.id
JOIN role R ON R.id = X.role_id
LEFT JOIN user U1 ON U1.id = X.created_by
LEFT JOIN user U2 ON U2.id = X.updated_by
//

--
-- Pohled na přiřazení práv uživatelům
--
CREATE VIEW user_privilege_v AS
SELECT U.id user_id,
       U.name user_name,
       U.email user_email,
       U.enabled user_enabled,
       R.id role_id,
       R.name role_name,
       R.enabled role_enabled,
       V.id id, 
       V.module module,
       V.presenter presenter,
       V.action action
FROM user U
JOIN user_role X ON X.user_id = U.id
JOIN role R ON R.id = X.role_id
JOIN role_privilege Y ON Y.role_id = X.role_id
JOIN privilege V ON V.id = Y.privilege_id
//

--
-- Pohled na základní nastavení parametrů aplikace
--
CREATE VIEW param_v AS
SELECT P.param param, 
       P.level level, 
       P.type type, 
       P.value value, 
       IFNULL(L.description, P.value) value_show, 
       P.description description,
       P.created_at created_at, 
       datetime_output(P.created_at) created_at_f, 
       P.created_by created_by, 
       U1.name created_by_name, 
       P.created_by_db, 
       P.updated_at updated_at, 
       datetime_output(P.updated_at) updated_at_f, 
       P.updated_by updated_by, 
       U2.name updated_by_name, 
       P.updated_by_db updated_by_db, 
       CASE WHEN P.updated_at IS NULL THEN P.created_at ELSE P.updated_at END changed_at,
       CASE WHEN P.updated_at IS NULL THEN datetime_output(P.created_at) ELSE datetime_output(P.updated_at) END changed_at_f,
       CASE WHEN P.updated_at IS NULL THEN P.created_by ELSE P.updated_by END changed_by,
       CASE WHEN P.updated_at IS NULL THEN U1.name ELSE U2.name END changed_by_name,
       CASE WHEN P.updated_at IS NULL THEN P.created_by_db ELSE P.updated_by_db END changed_by_db,
       P.dbchanged
FROM param P
LEFT JOIN param_list L ON P.param = P.param AND P.value = P.value AND P.type = 'L'
LEFT JOIN user U1 ON U1.id = P.created_by
LEFT JOIN user U2 ON U2.id = P.updated_by
//

--
-- Pohled na uživatelské nastavení parametrů aplikace
--
CREATE VIEW user_param_v AS
SELECT P.param param, 
       P.level level, 
       P.type type, 
       P.value default_value, 
       IFNULL(L1.description, P.value) default_value_show, 
       P.description description, 
       U.id user_id, 
       U.email user_email, 
       U.name user_name, 
       R.value user_value,
       IFNULL(L2.description, R.value) user_value_show,
       CASE WHEN R.id IS NULL THEN FALSE ELSE TRUE END is_user_value,
       CASE WHEN R.id IS NULL THEN P.value ELSE R.value END value,
       CASE WHEN R.id IS NULL THEN IFNULL(L1.description, P.value) ELSE IFNULL(L2.description, R.value) END value_show
FROM user U
JOIN param P
LEFT JOIN user_param R ON R.user_id = U.id AND R.param = P.param
LEFT JOIN param_list L1 ON L1.param = P.param AND L1.value = P.value AND P.type = 'L'
LEFT JOIN param_list L2 ON L2.param = P.param AND L2.value = R.value AND P.type = 'L'
//

--
-- Načtení uživatelské hodnoty parametru aplikace
--
CREATE FUNCTION get_user_param
  ( p_param VARCHAR(60) ) RETURNS VARCHAR(2000)
  COMMENT 'Vrací hodnotu zadaného uživatelského parametru'
  READS SQL DATA
BEGIN
  DECLARE x_value VARCHAR(2000);
  SELECT value
  INTO   x_value
  FROM   user_param_v
  WHERE  param = p_param
  AND    user_id = get_appl_user();
  RETURN x_value;
END;
//
DELIMITER ;
Databázové služby

Příklad návrhu databáze pro MySQL

Návrh jednoduché databáze
