COmmandes

CREATE TABLE role (
  id TINYINT unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
  code VARCHAR(4) NOT NULL,
  icon VARCHAR(1) NOT NULL,
  mask BIT(8) NOT NULL
);
 
INSERT INTO role (code, icon, mask) VALUES
('USER', '👤', b'00000000'), # user: can log to web sites
('APPU', '👷', b'00000001'), # app user
('TADM', '👮', b'00000010'), # tenant administrator
('ADMI', '🔑', b'00000100'), # administrator: can connect to admin app
('AUTH', '📝', b'00001000'); # news author
 
CREATE TABLE user (
  id MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,  #+ pk id, max 16,777,215
  pseudo VARCHAR(32) DEFAULT NULL,                            #~ user pseudo
  firstname VARCHAR(32) DEFAULT NULL,                         #~ user firstname
  lastname VARCHAR(32) DEFAULT NULL,                          #~ user lastname
  email VARCHAR(64) NOT NULL,			              #~ user email
  hash VARCHAR(255) DEFAULT NULL,                             #~ pwd hash
  checked BOOLEAN DEFAULT false,			      #~ user checked
  roles BIT(4) DEFAULT b'00000000',			      #~ role bitfield
  creation DATE DEFAULT (CURRENT_DATE)                        #~ creation date
);
 
CREATE TABLE client (
 
  id MEDIUMINT unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, #+ pk id, max 16,777,215
  user_id MEDIUMINT unsigned NOT NULL,                       #~ user ref
  phone VARCHAR(13) DEFAULT "",		                     #~ client phone
  creation DATE DEFAULT (CURRENT_DATE),                      #~ creation date
  FOREIGN KEY (user_id) REFERENCES user(id)                  #~ fk user ref
);
 
CREATE TABLE contact (
 
  id MEDIUMINT unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, #+ pk id, max 16,777,215
  fullname VARCHAR(64) NOT NULL,                             #+ fullname
  client_id MEDIUMINT unsigned DEFAULT 0,                    #~ client ref
  content VARCHAR(512) NOT NULL,                             #+ content
  creation DATE DEFAULT (CURRENT_DATE)                       #~ creation date
);