sql_create

CREATE TABLE `user` (
    `id` int NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
    `username` varchar(16) NOT NULL COMMENT 'Username',
    `token` varchar(255) NOT NULL COMMENT 'User token, user unique',
    `register_time` datetime NOT NULL COMMENT 'Register Time',
    `last_updated_time` datetime NOT NULL COMMENT 'This row last updated time',
    `last_login_time` datetime NOT NULL COMMENT 'Last login time',
    `type` varchar(16) NOT NULL COMMENT 'master, manager, menber, user, visiter',
    PRIMARY KEY (`id`),
    UNIQUE KEY `username` (`username`),
    UNIQUE KEY `token` (`token`)
) COMMENT '';

CREATE TABLE `user_config` (
    `id` int NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
    `user_id` int NOT NULL COMMENT 'User foreign key',
    `theme` varchar(16) DEFAULT NULL COMMENT 'Color theme',
    `web_worker_num` int DEFAULT 4,
    `filelist_config` TEXT DEFAULT NULL COMMENT 'map path to its config',
    PRIMARY KEY (`id`),
    KEY `user_id` (`user_id`),
    CONSTRAINT `user_config_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) COMMENT '';

CREATE TABLE `user_file_action` (
    `id` int NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
    `user_id` int NOT NULL COMMENT 'User foreign key',
    `filename` varchar(16) DEFAULT NULL COMMENT '',
    `filepath` varchar(255) DEFAULT NULL COMMENT '',
    `action` varchar(32) DEFAULT NULL COMMENT 'upload, download, delete, share',
    `create_time` datetime NOT NULL COMMENT 'action create time',
    PRIMARY KEY (`id`),
    KEY `user_id` (`user_id`),
    FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) COMMENT '';

DELIMITER $$
CREATE TRIGGER `before_user_insert` BEFORE INSERT ON `user`
FOR EACH ROW
BEGIN
    SET NEW.register_time = NOW();
    SET NEW.last_updated_time = NOW();
    SET NEW.last_login_time = NOW();
END$$

CREATE TRIGGER `before_user_update` BEFORE UPDATE ON `user`
FOR EACH ROW
BEGIN
    IF OLD.last_login_time != NEW.last_login_time THEN
        SET NEW.last_updated_time = NOW();
    ELSE
        SET NEW.last_updated_time = NOW();
    END IF;
END$$

CREATE TRIGGER `before_file_action_insert` BEFORE INSERT ON `user_file_action`
FOR EACH ROW
BEGIN
    SET NEW.create_time = NOW();
END$$

DELIMITER ;