Revision 8567
Added by Aaron Marcuse-Kubitza over 11 years ago
| schemas/VegCore/VegCore.my.sql | ||
|---|---|---|
| 257 | 257 |
-- ----------------------------------------------------- |
| 258 | 258 |
CREATE TABLE IF NOT EXISTS `coordinates` ( |
| 259 | 259 |
`id` TEXT NOT NULL , |
| 260 |
PRIMARY KEY (`id`) , |
|
| 261 |
CONSTRAINT `fk_coordinates_record1` |
|
| 262 |
FOREIGN KEY (`id` ) |
|
| 263 |
REFERENCES `record` (`id` ) |
|
| 264 |
ON DELETE CASCADE |
|
| 265 |
ON UPDATE CASCADE) |
|
| 260 |
PRIMARY KEY (`id`) ) |
|
| 266 | 261 |
ENGINE = InnoDB |
| 267 | 262 |
DEFAULT CHARACTER SET = latin1 |
| 268 | 263 |
COLLATE = latin1_swedish_ci; |
| 269 | 264 |
|
| 270 | 265 |
|
| 271 | 266 |
-- ----------------------------------------------------- |
| 267 |
-- Table `place_path` |
|
| 268 |
-- ----------------------------------------------------- |
|
| 269 |
CREATE TABLE IF NOT EXISTS `place_path` ( |
|
| 270 |
`id` TEXT NOT NULL , |
|
| 271 |
PRIMARY KEY (`id`) ) |
|
| 272 |
ENGINE = InnoDB |
|
| 273 |
DEFAULT CHARACTER SET = latin1 |
|
| 274 |
COLLATE = latin1_swedish_ci; |
|
| 275 |
|
|
| 276 |
|
|
| 277 |
-- ----------------------------------------------------- |
|
| 272 | 278 |
-- Table `place` |
| 273 | 279 |
-- ----------------------------------------------------- |
| 274 | 280 |
CREATE TABLE IF NOT EXISTS `place` ( |
| 275 | 281 |
`id` TEXT NOT NULL , |
| 276 | 282 |
`parent` TEXT NOT NULL , |
| 283 |
`path` TEXT NULL , |
|
| 277 | 284 |
`coordinates` TEXT NULL , |
| 278 | 285 |
PRIMARY KEY (`id`) , |
| 279 | 286 |
INDEX `fk_place_coordinates1` (`coordinates` ASC) , |
| 280 | 287 |
INDEX `fk_place1` (`parent` ASC) , |
| 288 |
INDEX `fk_place_place_path1` (`path` ASC) , |
|
| 281 | 289 |
CONSTRAINT `fk_place_record1` |
| 282 | 290 |
FOREIGN KEY (`id` ) |
| 283 | 291 |
REFERENCES `record` (`id` ) |
| ... | ... | |
| 292 | 300 |
FOREIGN KEY (`parent` ) |
| 293 | 301 |
REFERENCES `place` (`id` ) |
| 294 | 302 |
ON DELETE CASCADE |
| 303 |
ON UPDATE CASCADE, |
|
| 304 |
CONSTRAINT `fk_place_place_path1` |
|
| 305 |
FOREIGN KEY (`path` ) |
|
| 306 |
REFERENCES `place_path` (`id` ) |
|
| 307 |
ON DELETE CASCADE |
|
| 295 | 308 |
ON UPDATE CASCADE) |
| 296 | 309 |
ENGINE = InnoDB |
| 297 | 310 |
DEFAULT CHARACTER SET = latin1 |
| ... | ... | |
| 718 | 731 |
|
| 719 | 732 |
|
| 720 | 733 |
-- ----------------------------------------------------- |
| 721 |
-- Table `place_path`
|
|
| 734 |
-- Table `validatable_place`
|
|
| 722 | 735 |
-- ----------------------------------------------------- |
| 723 |
CREATE TABLE IF NOT EXISTS `place_path` (
|
|
| 736 |
CREATE TABLE IF NOT EXISTS `validatable_place` (
|
|
| 724 | 737 |
`id` TEXT NOT NULL , |
| 738 |
`path` TEXT NOT NULL , |
|
| 739 |
`coordinates` TEXT NOT NULL , |
|
| 725 | 740 |
PRIMARY KEY (`id`) , |
| 726 |
CONSTRAINT `fk_place_path_record1` |
|
| 727 |
FOREIGN KEY (`id` ) |
|
| 728 |
REFERENCES `record` (`id` ) |
|
| 741 |
INDEX `fk_geovalidation_place_path1` (`path` ASC) , |
|
| 742 |
INDEX `fk_geovalidation_coordinates1` (`coordinates` ASC) , |
|
| 743 |
CONSTRAINT `fk_geovalidation_place_path1` |
|
| 744 |
FOREIGN KEY (`path` ) |
|
| 745 |
REFERENCES `place_path` (`id` ) |
|
| 729 | 746 |
ON DELETE CASCADE |
| 730 | 747 |
ON UPDATE CASCADE, |
| 731 |
CONSTRAINT `fk_place_path_place1`
|
|
| 732 |
FOREIGN KEY (`id` )
|
|
| 733 |
REFERENCES `place` (`id` )
|
|
| 748 |
CONSTRAINT `fk_geovalidation_coordinates1`
|
|
| 749 |
FOREIGN KEY (`coordinates` )
|
|
| 750 |
REFERENCES `coordinates` (`id` )
|
|
| 734 | 751 |
ON DELETE CASCADE |
| 735 | 752 |
ON UPDATE CASCADE) |
| 736 | 753 |
ENGINE = InnoDB |
| ... | ... | |
| 739 | 756 |
|
| 740 | 757 |
|
| 741 | 758 |
-- ----------------------------------------------------- |
| 742 |
-- Table `geovalidation` |
|
| 743 |
-- ----------------------------------------------------- |
|
| 744 |
CREATE TABLE IF NOT EXISTS `geovalidation` ( |
|
| 745 |
`id` TEXT NOT NULL , |
|
| 746 |
PRIMARY KEY (`id`) , |
|
| 747 |
CONSTRAINT `fk_geovalidation_record1` |
|
| 748 |
FOREIGN KEY (`id` ) |
|
| 749 |
REFERENCES `record` (`id` ) |
|
| 750 |
ON DELETE CASCADE |
|
| 751 |
ON UPDATE CASCADE, |
|
| 752 |
CONSTRAINT `fk_geovalidation_place_path1` |
|
| 753 |
FOREIGN KEY (`id` ) |
|
| 754 |
REFERENCES `place_path` (`id` ) |
|
| 755 |
ON DELETE CASCADE |
|
| 756 |
ON UPDATE CASCADE) |
|
| 757 |
ENGINE = InnoDB |
|
| 758 |
DEFAULT CHARACTER SET = latin1 |
|
| 759 |
COLLATE = latin1_swedish_ci; |
|
| 760 |
|
|
| 761 |
|
|
| 762 |
-- ----------------------------------------------------- |
|
| 763 | 759 |
-- Table `project` |
| 764 | 760 |
-- ----------------------------------------------------- |
| 765 | 761 |
CREATE TABLE IF NOT EXISTS `project` ( |
| ... | ... | |
| 982 | 978 |
ENGINE = InnoDB; |
| 983 | 979 |
|
| 984 | 980 |
|
| 981 |
-- ----------------------------------------------------- |
|
| 982 |
-- Table `geovalidation` |
|
| 983 |
-- ----------------------------------------------------- |
|
| 984 |
CREATE TABLE IF NOT EXISTS `geovalidation` ( |
|
| 985 |
`id` TEXT NOT NULL , |
|
| 986 |
PRIMARY KEY (`id`) , |
|
| 987 |
CONSTRAINT `fk_geovalidation_validatable_place1` |
|
| 988 |
FOREIGN KEY (`id` ) |
|
| 989 |
REFERENCES `validatable_place` (`id` ) |
|
| 990 |
ON DELETE CASCADE |
|
| 991 |
ON UPDATE CASCADE) |
|
| 992 |
ENGINE = InnoDB |
|
| 993 |
DEFAULT CHARACTER SET = latin1 |
|
| 994 |
COLLATE = latin1_swedish_ci; |
|
| 985 | 995 |
|
| 996 |
|
|
| 997 |
|
|
| 986 | 998 |
SET SQL_MODE=@OLD_SQL_MODE; |
| 987 | 999 |
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; |
| 988 | 1000 |
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; |
Also available in: Unified diff
schemas/VegCore/VegCore.ERD.mwb: made place_path and coordinates globally-scoped rather than per-datasource, so that there is only one geovalidation row for each combination of them rather than one per datasource place. added globally-scoped validatable_place table that stores combinations of place_path and coordinates, and made geovalidation extend it (since geovalidation provides optional fields and is 1:1 with it).