db.sql

CREATE TABLE `access_list` (
	`id` INT(10) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	`num` TINYTEXT NULL,
	`date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`duration` INT(10) UNSIGNED ZEROFILL NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=0
;
CREATE TABLE `ip_list` (
	`id` INT(10) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	`ip` TEXT NOT NULL,
	`mac_id` INT(10) UNSIGNED ZEROFILL NOT NULL,
	`reg_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	INDEX `mac` (`mac_id`),
	CONSTRAINT `mac` FOREIGN KEY (`mac_id`) REFERENCES `mac_list` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=0
;

CREATE TABLE `journal` (
	`id` INT(10) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	`user_number` TEXT NULL,
	`date` DATE NULL DEFAULT NULL,
	`time_activate` TIME NULL DEFAULT NULL,
	`comp_number` INT(10) UNSIGNED ZEROFILL NULL DEFAULT NULL,
	`duration` INT(11) NULL DEFAULT '-1',
	`busy` BINARY(1) NULL DEFAULT '0',
	PRIMARY KEY (`id`),
	INDEX `PC` (`comp_number`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
CREATE TABLE `last_check` (
	`id` INT(10) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	`ip_id` INT(10) UNSIGNED ZEROFILL NULL DEFAULT NULL,
	`checked` TIMESTAMP NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	INDEX `ipid_id` (`ip_id`),
	CONSTRAINT `ipid_id` FOREIGN KEY (`ip_id`) REFERENCES `ip_list` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=0
;
CREATE TABLE `mac_list` (
	`id` INT(10) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	`mac` TEXT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=0
;
CREATE TABLE `session` (
	`id` INT(10) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	`list_id` INT(10) UNSIGNED ZEROFILL NULL DEFAULT NULL,
	`start_at` TIME NULL DEFAULT NULL,
	`pc` INT(10) UNSIGNED ZEROFILL NULL DEFAULT NULL,
	`stamp` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	INDEX `mac_id` (`pc`),
	INDEX `list_id` (`list_id`),
	CONSTRAINT `list_id` FOREIGN KEY (`list_id`) REFERENCES `access_list` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT `mac_id` FOREIGN KEY (`pc`) REFERENCES `mac_list` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=0
;