Annotation of embedaddon/strongswan/src/libimcv/imv/tables-mysql.sql, revision 1.1
1.1 ! misho 1:
! 2: DROP TABLE IF EXISTS `directories`;
! 3: CREATE TABLE `directories` (
! 4: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
! 5: `path` VARCHAR(2048) NOT NULL
! 6: );
! 7:
! 8: DROP TABLE IF EXISTS `files`;
! 9: CREATE TABLE `files` (
! 10: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
! 11: `dir` INTEGER DEFAULT 0 REFERENCES `directories`(`id`),
! 12: `name` VARCHAR(512) NOT NULL
! 13: );
! 14:
! 15: DROP TABLE IF EXISTS `products`;
! 16: CREATE TABLE `products` (
! 17: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
! 18: `name` VARCHAR(128) NOT NULL
! 19: );
! 20:
! 21: DROP TABLE IF EXISTS `algorithms`;
! 22: CREATE TABLE `algorithms` (
! 23: `id` INTEGER PRIMARY KEY,
! 24: `name` VARCHAR(20) NOT NULL
! 25: );
! 26:
! 27: DROP TABLE IF EXISTS `file_hashes`;
! 28: CREATE TABLE `file_hashes` (
! 29: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
! 30: `file` INTEGER NOT NULL REFERENCES `files`(`id`),
! 31: `product` INTEGER NOT NULL REFERENCES `products`(`id`),
! 32: `device` INTEGER DEFAULT 0,
! 33: `key` INTEGER DEFAULT 0 REFERENCES `keys`(id),
! 34: `algo` INTEGER NOT NULL REFERENCES `algorithms`(`id`),
! 35: `hash` VARBINARY(64) NOT NULL
! 36: );
! 37:
! 38: DROP TABLE IF EXISTS `keys`;
! 39: CREATE TABLE `keys` (
! 40: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
! 41: `keyid` VARBINARY(128) NOT NULL,
! 42: `owner` VARCHAR(128) NOT NULL
! 43: );
! 44:
! 45: DROP TABLE IF EXISTS `groups`;
! 46: CREATE TABLE `groups` (
! 47: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
! 48: `name` VARCHAR(50) NOT NULL UNIQUE,
! 49: `parent` INTEGER
! 50: );
! 51:
! 52: DROP TABLE IF EXISTS `groups_members`;
! 53: CREATE TABLE `groups_members` (
! 54: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
! 55: `group_id` INTEGER NOT NULL REFERENCES `groups`(`id`),
! 56: `device_id` INTEGER NOT NULL REFERENCES `devices`(`id`),
! 57: UNIQUE (`group_id`, `device_id`)
! 58: );
! 59:
! 60: DROP TABLE IF EXISTS `groups_product_defaults`;
! 61: CREATE TABLE `groups_product_defaults` (
! 62: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
! 63: `group_id` INTEGER NOT NULL REFERENCES `groups`(`id`),
! 64: `product_id` INTEGER NOT NULL REFERENCES `products`(`id`),
! 65: UNIQUE (`group_id`, `product_id`)
! 66: );
! 67:
! 68: DROP TABLE IF EXISTS `policies`;
! 69: CREATE TABLE `policies` (
! 70: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
! 71: `type` INTEGER NOT NULL,
! 72: `name` VARCHAR(100) NOT NULL UNIQUE,
! 73: `argument` VARCHAR(100) DEFAULT '' NOT NULL,
! 74: `rec_fail` INTEGER NOT NULL,
! 75: `rec_noresult` INTEGER NOT NULL,
! 76: `file` INTEGER DEFAULT 0 REFERENCES `files`(`id`),
! 77: `dir` INTEGER DEFAULT 0 REFERENCES `directories`(`id`)
! 78: );
! 79:
! 80: DROP TABLE IF EXISTS `enforcements`;
! 81: CREATE TABLE `enforcements` (
! 82: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
! 83: `policy` INTEGER NOT NULL REFERENCES `policies`(`id`),
! 84: `group_id` INTEGER NOT NULL REFERENCES `groups`(`id`),
! 85: `rec_fail` INTEGER,
! 86: `rec_noresult` INTEGER,
! 87: `max_age` INTEGER NOT NULL,
! 88: UNIQUE (`policy`, `group_id`)
! 89: );
! 90:
! 91: DROP TABLE IF EXISTS `sessions`;
! 92: CREATE TABLE `sessions` (
! 93: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
! 94: `time` INTEGER NOT NULL,
! 95: `connection` INTEGER NOT NULL,
! 96: `identity` INTEGER DEFAULT 0 REFERENCES `identities`(`id`),
! 97: `device` INTEGER DEFAULT 0 REFERENCES `devices`(`id`),
! 98: `product` INTEGER DEFAULT 0 REFERENCES `products`(`id`),
! 99: `rec` INTEGER DEFAULT 3
! 100: );
! 101:
! 102: DROP TABLE IF EXISTS `sessions_identities`;
! 103: CREATE TABLE `sessions_identities` (
! 104: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
! 105: `session_id` INTEGER NOT NULL REFERENCES `sessions`(`id`),
! 106: `identity_id` INTEGER NOT NULL REFERENCES `identities`(`id`),
! 107: UNIQUE (`session_id`, `identity_id`)
! 108: );
! 109:
! 110: DROP TABLE IF EXISTS `workitems`;
! 111: CREATE TABLE `workitems` (
! 112: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
! 113: `session` INTEGER NOT NULL REFERENCES `sessions`(`id`),
! 114: `enforcement` INTEGER NOT NULL REFERENCES `enforcements`(`id`),
! 115: `type` INTEGER NOT NULL,
! 116: `arg_str` VARCHAR(128),
! 117: `arg_int` INTEGER DEFAULT 0,
! 118: `rec_fail` INTEGER NOT NULL,
! 119: `rec_noresult` INTEGER NOT NULL,
! 120: `rec_final` INTEGER,
! 121: `result` VARCHAR(128)
! 122: );
! 123:
! 124: DROP TABLE IF EXISTS `results`;
! 125: CREATE TABLE `results` (
! 126: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
! 127: `session` INTEGER NOT NULL REFERENCES `measurements`(`id`),
! 128: `policy` INTEGER NOT NULL REFERENCES `policies`(`id`),
! 129: `rec` INTEGER NOT NULL,
! 130: `result` TEXT NOT NULL
! 131: );
! 132:
! 133: DROP TABLE IF EXISTS `components`;
! 134: CREATE TABLE `components` (
! 135: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
! 136: `vendor_id` INTEGER NOT NULL,
! 137: `name` INTEGER NOT NULL,
! 138: `qualifier` INTEGER DEFAULT 0
! 139: );
! 140:
! 141: DROP TABLE IF EXISTS `key_component`;
! 142: CREATE TABLE `key_component` (
! 143: `key` INTEGER NOT NULL,
! 144: `component` INTEGER NOT NULL,
! 145: `depth` INTEGER DEFAULT 0,
! 146: `seq_no` INTEGER DEFAULT 0,
! 147: PRIMARY KEY (`key`, `component`)
! 148: );
! 149:
! 150: DROP TABLE IF EXISTS `component_hashes`;
! 151: CREATE TABLE `component_hashes` (
! 152: `component` INTEGER NOT NULL,
! 153: `key` INTEGER NOT NULL,
! 154: `seq_no` INTEGER NOT NULL,
! 155: `pcr` INTEGER NOT NULL,
! 156: `algo` INTEGER NOT NULL,
! 157: `hash` VARBINARY(32) NOT NULL,
! 158: PRIMARY KEY(`component`, `key`, `seq_no`, `algo`)
! 159: );
! 160:
! 161: DROP TABLE IF EXISTS `packages`;
! 162: CREATE TABLE `packages` (
! 163: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
! 164: `name` VARCHAR(128) NOT NULL,
! 165: `blacklist` INTEGER DEFAULT 0
! 166: );
! 167:
! 168: DROP TABLE IF EXISTS versions;
! 169: CREATE TABLE versions (
! 170: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
! 171: `package` INTEGER NOT NULL REFERENCES packages(id),
! 172: `product` INTEGER NOT NULL REFERENCES products(id),
! 173: `release` VARCHAR(32) NOT NULL,
! 174: `security` INTEGER DEFAULT 0,
! 175: `blacklist` INTEGER DEFAULT 0,
! 176: `time` INTEGER DEFAULT 0
! 177: );
! 178:
! 179: DROP TABLE IF EXISTS `devices`;
! 180: CREATE TABLE `devices` (
! 181: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
! 182: `description` VARCHAR(100) DEFAULT "",
! 183: `value` VARCHAR(256) NOT NULL,
! 184: `product` INTEGER REFERENCES `products`(`id`),
! 185: `trusted` INTEGER DEFAULT 0,
! 186: `created` INTEGER,
! 187: `inactive` INTEGER DEFAULT 0
! 188: );
! 189:
! 190: DROP TABLE IF EXISTS `identities`;
! 191: CREATE TABLE `identities` (
! 192: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
! 193: `type` INTEGER NOT NULL,
! 194: `value` VARBINARY(128) NOT NULL,
! 195: UNIQUE (type, value)
! 196: );
! 197:
! 198: DROP TABLE IF EXISTS `regids`;
! 199: CREATE TABLE `regids` (
! 200: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
! 201: `name` TEXT NOT NULL
! 202: );
! 203:
! 204: DROP TABLE IF EXISTS `tags`;
! 205: CREATE TABLE `tags` (
! 206: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
! 207: `regid` INTEGER NOT NULL REFERENCES `regids`(`id`),
! 208: `unique_sw_id` VARCHAR(64) NOT NULL,
! 209: `value` VARCHAR(128)
! 210: );
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>