COmmandes
unknown
sql
5 months ago
2.0 kB
8
Indexable
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 );
Editor is loading...
Leave a Comment