· 7 years ago · Dec 07, 2018, 01:52 AM
1-- MySQL Script generated by MySQL Workbench
2-- Fri Dec 7 09:47:44 2018
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
9
10-- -----------------------------------------------------
11-- Schema mydb
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema mydb
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
18USE `mydb` ;
19
20-- -----------------------------------------------------
21-- Table `mydb`.`service`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`service` (
24 `serviceID` INT(11) NOT NULL,
25 `OS` VARCHAR(64) NULL,
26 `application` VARCHAR(64) NOT NULL,
27 `protocol` VARCHAR(64) NOT NULL,
28 `description` TEXT NOT NULL,
29 PRIMARY KEY (`serviceID`))
30ENGINE = InnoDB
31DEFAULT CHARACTER SET = latin1;
32
33
34-- -----------------------------------------------------
35-- Table `mydb`.`tocrawl`
36-- -----------------------------------------------------
37CREATE TABLE IF NOT EXISTS `mydb`.`tocrawl` (
38 `crawlID` INT(11) NOT NULL,
39 `priority` INT(1) NOT NULL,
40 `ptc_portNumber` INT(5) NOT NULL,
41 `ptc_ipAddress` VARCHAR(38) NOT NULL,
42 `status` VARCHAR(16) NOT NULL,
43 `dateCrawled` DATETIME NOT NULL,
44 PRIMARY KEY (`crawlID`))
45ENGINE = InnoDB
46DEFAULT CHARACTER SET = latin1;
47
48
49-- -----------------------------------------------------
50-- Table `mydb`.`site`
51-- -----------------------------------------------------
52CREATE TABLE IF NOT EXISTS `mydb`.`site` (
53 `siteID` INT(11) NOT NULL,
54 `serviceID` INT(11) NOT NULL,
55 `crawlDate` VARCHAR(45) NOT NULL,
56 `endDate` DATE NULL,
57 PRIMARY KEY (`siteID`),
58 INDEX `serviceID_idx` (`serviceID` ASC) VISIBLE,
59 CONSTRAINT `serviceID`
60 FOREIGN KEY (`serviceID`)
61 REFERENCES `mydb`.`service` (`serviceID`)
62 ON DELETE NO ACTION
63 ON UPDATE NO ACTION)
64ENGINE = InnoDB
65DEFAULT CHARACTER SET = latin1;
66
67
68-- -----------------------------------------------------
69-- Table `mydb`.`CMS`
70-- -----------------------------------------------------
71CREATE TABLE IF NOT EXISTS `mydb`.`CMS` (
72 `cmsID` INT(11) NOT NULL,
73 `siteID` INT(11) NOT NULL,
74 `name` VARCHAR(16) NOT NULL,
75 `version` VARCHAR(16) NOT NULL,
76 `crawldate` DATE NOT NULL,
77 `enddate` DATE NULL,
78 PRIMARY KEY (`cmsID`),
79 INDEX `siteID_idx` (`siteID` ASC) VISIBLE,
80 CONSTRAINT `siteID`
81 FOREIGN KEY (`siteID`)
82 REFERENCES `mydb`.`site` (`siteID`)
83 ON DELETE NO ACTION
84 ON UPDATE NO ACTION)
85ENGINE = InnoDB;
86
87
88-- -----------------------------------------------------
89-- Table `mydb`.`portID`
90-- -----------------------------------------------------
91CREATE TABLE IF NOT EXISTS `mydb`.`portID` (
92 `portID` INT(11) NOT NULL,
93 `serviceID` INT(11) NOT NULL,
94 `portNumber` INT(11) NOT NULL,
95 `status` VARCHAR(16) NOT NULL,
96 `crawlDate` DATE NOT NULL,
97 `endDate` DATE NULL,
98 PRIMARY KEY (`portID`),
99 INDEX `serviceID_idx` (`serviceID` ASC) VISIBLE,
100 CONSTRAINT `serviceID`
101 FOREIGN KEY (`serviceID`)
102 REFERENCES `mydb`.`service` (`serviceID`)
103 ON DELETE NO ACTION
104 ON UPDATE NO ACTION)
105ENGINE = InnoDB;
106
107
108-- -----------------------------------------------------
109-- Table `mydb`.`ipAddressID`
110-- -----------------------------------------------------
111CREATE TABLE IF NOT EXISTS `mydb`.`ipAddressID` (
112 `ipAddressID` INT(11) NOT NULL,
113 `serviceID` INT(11) NOT NULL,
114 `ipAddress` VARCHAR(39) NOT NULL,
115 `crawlDate` DATE NOT NULL,
116 `endDate` DATE NULL,
117 PRIMARY KEY (`ipAddressID`),
118 INDEX `existenceID_idx` (`serviceID` ASC) VISIBLE,
119 CONSTRAINT `serviceID`
120 FOREIGN KEY (`serviceID`)
121 REFERENCES `mydb`.`service` (`serviceID`)
122 ON DELETE NO ACTION
123 ON UPDATE NO ACTION)
124ENGINE = InnoDB;
125
126
127-- -----------------------------------------------------
128-- Table `mydb`.`subdomain`
129-- -----------------------------------------------------
130CREATE TABLE IF NOT EXISTS `mydb`.`subdomain` (
131 `subdomainID` INT(11) NOT NULL,
132 `siteID` INT(11) NOT NULL,
133 `subdomain` VARCHAR(256) NOT NULL,
134 `crawldate` DATE NOT NULL,
135 `enddate` DATE NULL,
136 PRIMARY KEY (`subdomainID`),
137 INDEX `siteID_idx` (`siteID` ASC) VISIBLE,
138 CONSTRAINT `siteID0`
139 FOREIGN KEY (`siteID`)
140 REFERENCES `mydb`.`site` (`siteID`)
141 ON DELETE NO ACTION
142 ON UPDATE NO ACTION)
143ENGINE = InnoDB;
144
145
146-- -----------------------------------------------------
147-- Table `mydb`.`domain`
148-- -----------------------------------------------------
149CREATE TABLE IF NOT EXISTS `mydb`.`domain` (
150 `domainID` INT(11) NOT NULL,
151 `siteID` INT(11) NOT NULL,
152 `domain` VARCHAR(256) NOT NULL,
153 `crawldate` DATE NOT NULL,
154 `enddate` DATE NULL,
155 PRIMARY KEY (`domainID`),
156 INDEX `siteID_idx` (`siteID` ASC) VISIBLE,
157 CONSTRAINT `siteID00`
158 FOREIGN KEY (`siteID`)
159 REFERENCES `mydb`.`site` (`siteID`)
160 ON DELETE NO ACTION
161 ON UPDATE NO ACTION)
162ENGINE = InnoDB;
163
164
165-- -----------------------------------------------------
166-- Table `mydb`.`tld`
167-- -----------------------------------------------------
168CREATE TABLE IF NOT EXISTS `mydb`.`tld` (
169 `tldID` INT(11) NOT NULL,
170 `siteID` INT(11) NOT NULL,
171 `tld` VARCHAR(256) NOT NULL,
172 `crawldate` DATE NOT NULL,
173 `enddate` DATE NULL,
174 PRIMARY KEY (`tldID`),
175 INDEX `siteID_idx` (`siteID` ASC) VISIBLE,
176 CONSTRAINT `siteID01`
177 FOREIGN KEY (`siteID`)
178 REFERENCES `mydb`.`site` (`siteID`)
179 ON DELETE NO ACTION
180 ON UPDATE NO ACTION)
181ENGINE = InnoDB;
182
183
184SET SQL_MODE=@OLD_SQL_MODE;
185SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
186SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;