Gb6b1haJ

1CREATE TABLE IF NOT EXISTS `visit` (
2  `id` BIGINT(6) NOT NULL AUTO_INCREMENT,
3  `siteId` BIGINT(6) NOT NULL,
4  `clientId` BIGINT(6) NULL,
5  `guest` TINYINT(1) NULL DEFAULT 1,
6  `ip` LONGTEXT  NULL,
7  `os` LONGTEXT NULL,
8  `screen` LONGTEXT  NULL,
9  `page` LONGTEXT NULL,
10  `referer` LONGTEXT NULL,
11  `device` LONGTEXT  NULL,
12  `location` LONGTEXT  NULL,
13  `created` TIMESTAMP(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
14  PRIMARY KEY (`id`, `siteId`),
15  INDEX `fk_visit_site1_idx` (`siteId` ASC),
16  CONSTRAINT `fk_visit_site1`
17    FOREIGN KEY (`siteId`)
18    REFERENCES `site` (`id`)
19    ON DELETE NO ACTION
20    ON UPDATE NO ACTION)
21ENGINE = InnoDB;
22	
23SELECT v.id AS id, v.siteId, v.clientId, v.guest, MAX(v.ip) AS ip, MAX(v.os) AS os, MAX(v.screen) AS screen, MAX(v.device) AS device, MAX(v.location) AS location, MAX(v.date) AS date, MAX(v.time) AS time, MAX(v.created) AS created FROM visit AS v WHERE siteId = ? GROUP BY v.id, v.siteId, v.clientId ORDER BY MAX(v.created) DESC;
24	
25CREATE TABLE IF NOT EXISTS `visit` (
26  `id` BIGINT(6) NOT NULL AUTO_INCREMENT,
27  `siteId` BIGINT(6) NOT NULL,
28  `clientId` BIGINT(6) NULL,
29  `guest` TINYINT(1) NULL DEFAULT 1,
30  `created` TIMESTAMP(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
31  PRIMARY KEY (`id`, `siteId`),
32  INDEX `fk_visit_site1_idx` (`siteId` ASC),
33  CONSTRAINT `fk_visit_site1`
34    FOREIGN KEY (`siteId`)
35    REFERENCES `site` (`id`)
36    ON DELETE NO ACTION
37    ON UPDATE NO ACTION)
38ENGINE = InnoDB;
39
40CREATE TABLE IF NOT EXISTS `visitinfo` (
41  `id` BIGINT(6) NOT NULL AUTO_INCREMENT,
42  `visitId` BIGINT(6) NOT NULL,
43  `ip` LONGTEXT NULL,
44  `os` LONGTEXT NULL,
45  `screen` LONGTEXT NULL,
46  `page` LONGTEXT NULL,
47  `referer` LONGTEXT NULL,
48  `device` LONGTEXT NULL,
49  `created` TIMESTAMP(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
50  PRIMARY KEY (`id`, `visitId`),
51  INDEX `fk_visitinfo_visit2_idx` (`visitId` ASC),
52  CONSTRAINT `fk_visitinfo_visit2`
53    FOREIGN KEY (`visitId`)
54    REFERENCES `visit` (`id`)
55    ON DELETE NO ACTION
56    ON UPDATE NO ACTION)
57ENGINE = InnoDB;