Changeset 118 for trunk/install/schema.sql
- Timestamp:
- 12/14/2005 07:42:28 PM (6 years ago)
- File:
-
- 1 edited
-
trunk/install/schema.sql (modified) (1 diff)
Legend:
- Unmodified
- Added
- Removed
-
trunk/install/schema.sql
r86 r118 1 # 2 # Table structure for table 'areas' 3 # 4 5 CREATE TABLE areas ( 6 id int(10) unsigned NOT NULL default '0', 7 region_id int(10) unsigned NOT NULL default '0', 8 name varchar(40) NOT NULL default '', 9 ip_start int(10) unsigned NOT NULL default '0', 10 ip_end int(10) unsigned NOT NULL default '0', 11 info text, 12 PRIMARY KEY (id) 13 ) TYPE=MyISAM; 14 15 16 17 # 18 # Table structure for table 'dns_nameservers' 19 # 20 21 CREATE TABLE dns_nameservers ( 22 id int(10) unsigned NOT NULL auto_increment, 23 date_in datetime NOT NULL default '0000-00-00 00:00:00', 24 node_id int(10) unsigned NOT NULL default '0', 25 name enum('ns0','ns1','ns2','ns3') NOT NULL default 'ns0', 26 ip int(10) unsigned NOT NULL default '0', 27 status enum('active','pending','rejected','invalid') NOT NULL default 'pending', 28 delete_req enum('Y','N') NOT NULL default 'N', 29 PRIMARY KEY (id), 30 UNIQUE KEY unique_keys (name,node_id) 31 ) TYPE=MyISAM; 32 33 34 35 # 36 # Table structure for table 'dns_zones' 37 # 38 39 CREATE TABLE dns_zones ( 40 id int(10) unsigned NOT NULL auto_increment, 41 date_in datetime NOT NULL default '0000-00-00 00:00:00', 42 type enum('forward','reverse') NOT NULL default 'forward', 43 name varchar(30) NOT NULL default '', 44 node_id int(10) unsigned default '0', 45 status enum('active','pending','rejected','invalid') NOT NULL default 'pending', 46 info text, 47 delete_req enum('Y','N') NOT NULL default 'N', 48 PRIMARY KEY (id), 49 UNIQUE KEY unique_keys (name,type) 50 ) TYPE=MyISAM; 51 52 53 54 # 55 # Table structure for table 'dns_zones_nameservers' 56 # 57 58 CREATE TABLE dns_zones_nameservers ( 59 id int(10) unsigned NOT NULL auto_increment, 60 zone_id int(10) unsigned NOT NULL default '0', 61 nameserver_id int(10) unsigned NOT NULL default '0', 62 PRIMARY KEY (id), 63 UNIQUE KEY unique_keys (zone_id,nameserver_id) 64 ) TYPE=MyISAM; 65 66 67 68 # 69 # Table structure for table 'ip_addresses' 70 # 71 72 CREATE TABLE ip_addresses ( 73 id int(10) unsigned NOT NULL auto_increment, 74 date_in datetime NOT NULL default '0000-00-00 00:00:00', 75 hostname varchar(50) NOT NULL default '', 76 ip int(10) unsigned NOT NULL default '0', 77 mac varchar(17) default NULL, 78 node_id int(10) unsigned NOT NULL default '0', 79 type enum('router','server','pc','wireless-bridge','voip','camera','other') NOT NULL default 'pc', 80 always_on enum('Y','N') NOT NULL default 'N', 81 info text, 82 PRIMARY KEY (id) 83 ) TYPE=MyISAM; 84 85 86 87 # 88 # Table structure for table 'ip_ranges' 89 # 90 91 CREATE TABLE ip_ranges ( 92 id int(10) unsigned NOT NULL auto_increment, 93 date_in datetime NOT NULL default '0000-00-00 00:00:00', 94 node_id int(10) unsigned NOT NULL default '0', 95 ip_start int(10) unsigned NOT NULL default '0', 96 ip_end int(10) unsigned NOT NULL default '0', 97 status enum('active','pending','rejected','invalid') NOT NULL default 'pending', 98 info text, 99 delete_req enum('Y','N') NOT NULL default 'N', 100 PRIMARY KEY (id), 101 UNIQUE KEY unique_keys (node_id,ip_start,ip_end) 102 ) TYPE=MyISAM; 103 104 105 106 # 107 # Table structure for table 'links' 108 # 109 110 CREATE TABLE links ( 111 id int(10) unsigned NOT NULL auto_increment, 112 date_in datetime NOT NULL default '0000-00-00 00:00:00', 113 node_id int(10) unsigned NOT NULL default '0', 114 peer_node_id int(10) unsigned default NULL, 115 peer_ap_id int(10) unsigned default NULL, 116 type enum('p2p','ap','client') NOT NULL default 'p2p', 117 ssid varchar(50) default NULL, 118 protocol enum('IEEE 802.11b','IEEE 802.11g','IEEE 802.11a','other') default NULL, 119 channel varchar(50) default NULL, 120 status enum('active','inactive') NOT NULL default 'active', 121 equipment text, 122 info text, 123 PRIMARY KEY (id) 124 ) TYPE=MyISAM; 125 126 127 128 # 129 # Table structure for table 'nodes' 130 # 131 132 CREATE TABLE nodes ( 133 id int(10) unsigned NOT NULL auto_increment, 134 date_in datetime NOT NULL default '0000-00-00 00:00:00', 135 name varchar(50) NOT NULL default '', 136 name_ns varchar(50) NOT NULL default '', 137 area_id int(10) unsigned default '0', 138 latitude float default NULL, 139 longitude float default NULL, 140 elevation int(10) unsigned default NULL, 141 info text, 142 PRIMARY KEY (id), 143 UNIQUE KEY unique_keys (name_ns) 144 ) TYPE=MyISAM; 145 146 147 148 # 149 # Table structure for table 'photos' 150 # 151 152 CREATE TABLE photos ( 153 id int(10) unsigned NOT NULL auto_increment, 154 date_in datetime NOT NULL default '0000-00-00 00:00:00', 155 node_id int(10) unsigned NOT NULL default '0', 156 type enum('galery','view') NOT NULL default 'galery', 157 view_point enum('N','NE','E','SE','S','SW','W','NW','PANORAMIC') default NULL, 158 info text, 159 PRIMARY KEY (id) 160 ) TYPE=MyISAM; 161 162 163 164 # 165 # Table structure for table 'regions' 166 # 167 168 CREATE TABLE regions ( 169 id int(10) unsigned NOT NULL default '0', 170 name varchar(40) NOT NULL default '', 171 ip_start int(10) unsigned NOT NULL default '0', 172 ip_end int(10) unsigned NOT NULL default '0', 173 info text, 174 PRIMARY KEY (id) 175 ) TYPE=MyISAM; 176 177 178 179 # 180 # Table structure for table 'rights' 181 # 182 183 CREATE TABLE rights ( 184 id int(10) unsigned NOT NULL auto_increment, 185 user_id int(10) unsigned NOT NULL default '0', 186 type enum('blocked','admin','hostmaster') NOT NULL default 'blocked', 187 PRIMARY KEY (id), 188 UNIQUE KEY unique_keys (type,user_id) 189 ) TYPE=MyISAM; 190 191 192 193 # 194 # Table structure for table 'subnets' 195 # 196 197 CREATE TABLE subnets ( 198 id int(10) unsigned NOT NULL auto_increment, 199 date_in datetime NOT NULL default '0000-00-00 00:00:00', 200 node_id int(10) unsigned default NULL, 201 ip_start int(10) unsigned NOT NULL default '0', 202 ip_end int(10) unsigned NOT NULL default '0', 203 type enum('local','link','client') NOT NULL default 'local', 204 link_id int(10) unsigned default NULL, 205 client_node_id int(10) unsigned default NULL, 206 PRIMARY KEY (id) 207 ) TYPE=MyISAM; 208 209 210 211 # 212 # Table structure for table 'users' 213 # 214 215 CREATE TABLE users ( 216 id int(10) unsigned NOT NULL auto_increment, 217 date_in datetime NOT NULL default '0000-00-00 00:00:00', 218 username varchar(30) NOT NULL default '', 219 password varchar(40) default NULL, 220 surname varchar(30) default NULL, 221 name varchar(30) default NULL, 222 phone varchar(60) default NULL, 223 email varchar(50) NOT NULL default '', 224 info text, 225 last_session datetime default NULL, 226 last_visit datetime default NULL, 227 status enum('activated','pending') NOT NULL default 'pending', 228 account_code varchar(20) default NULL, 229 PRIMARY KEY (id), 230 UNIQUE KEY unique_keys (username), 231 UNIQUE KEY unique_keys_2 (email) 232 ) TYPE=MyISAM; 233 234 235 236 # 237 # Table structure for table 'users_nodes' 238 # 239 240 CREATE TABLE users_nodes ( 241 id int(10) unsigned NOT NULL auto_increment, 242 user_id int(10) unsigned NOT NULL default '0', 243 node_id int(10) unsigned NOT NULL default '0', 244 owner enum('Y','N') NOT NULL default 'N', 245 PRIMARY KEY (id), 246 UNIQUE KEY unique_keys (node_id,user_id) 247 ) TYPE=MyISAM; 248 1 2 CREATE TABLE `areas` ( 3 `id` int(10) unsigned NOT NULL default '0', 4 `region_id` int(10) unsigned NOT NULL default '0', 5 `name` varchar(40) NOT NULL default '', 6 `ip_start` int(10) unsigned NOT NULL default '0', 7 `ip_end` int(10) unsigned NOT NULL default '0', 8 `info` text, 9 PRIMARY KEY (`id`), 10 KEY `region_id` (`region_id`), 11 KEY `name` (`name`), 12 KEY `ip_start` (`ip_start`), 13 KEY `ip_end` (`ip_end`) 14 ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 15 16 CREATE TABLE `dns_nameservers` ( 17 `id` int(10) unsigned NOT NULL auto_increment, 18 `date_in` datetime NOT NULL default '0000-00-00 00:00:00', 19 `node_id` int(10) unsigned NOT NULL default '0', 20 `name` enum('ns0','ns1','ns2','ns3') NOT NULL default 'ns0', 21 `ip` int(10) unsigned NOT NULL default '0', 22 `status` enum('waiting','active','pending','rejected','invalid') NOT NULL default 'waiting', 23 `delete_req` enum('Y','N') NOT NULL default 'N', 24 PRIMARY KEY (`id`), 25 UNIQUE KEY `unique_keys` (`name`,`node_id`), 26 KEY `date_in` (`date_in`), 27 KEY `node_id` (`node_id`), 28 KEY `ip` (`ip`), 29 KEY `status` (`status`), 30 KEY `delete_req` (`delete_req`) 31 ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 32 33 CREATE TABLE `dns_zones` ( 34 `id` int(10) unsigned NOT NULL auto_increment, 35 `date_in` datetime NOT NULL default '0000-00-00 00:00:00', 36 `type` enum('forward','reverse') NOT NULL default 'forward', 37 `name` varchar(30) NOT NULL default '', 38 `node_id` int(10) unsigned default '0', 39 `status` enum('waiting','active','pending','rejected','invalid') NOT NULL default 'waiting', 40 `info` text, 41 `delete_req` enum('Y','N') NOT NULL default 'N', 42 PRIMARY KEY (`id`), 43 UNIQUE KEY `unique_keys` (`name`,`type`), 44 KEY `type` (`type`), 45 KEY `date_in` (`date_in`), 46 KEY `node_id` (`node_id`), 47 KEY `status` (`status`), 48 KEY `delete_req` (`delete_req`) 49 ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 50 51 CREATE TABLE `dns_zones_nameservers` ( 52 `id` int(10) unsigned NOT NULL auto_increment, 53 `zone_id` int(10) unsigned NOT NULL default '0', 54 `nameserver_id` int(10) unsigned NOT NULL default '0', 55 PRIMARY KEY (`id`), 56 UNIQUE KEY `unique_keys` (`zone_id`,`nameserver_id`), 57 KEY `nameserver_id` (`nameserver_id`) 58 ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 59 60 CREATE TABLE `ip_addresses` ( 61 `id` int(10) unsigned NOT NULL auto_increment, 62 `date_in` datetime NOT NULL default '0000-00-00 00:00:00', 63 `hostname` varchar(50) NOT NULL default '', 64 `ip` int(10) unsigned NOT NULL default '0', 65 `mac` varchar(17) default NULL, 66 `node_id` int(10) unsigned NOT NULL default '0', 67 `type` enum('router','server','pc','wireless-bridge','voip','camera','other') NOT NULL default 'pc', 68 `always_on` enum('Y','N') NOT NULL default 'N', 69 `info` text, 70 PRIMARY KEY (`id`), 71 KEY `ip` (`ip`), 72 KEY `node_id` (`node_id`), 73 KEY `hostname` (`hostname`), 74 KEY `type` (`type`) 75 ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 76 77 CREATE TABLE `ip_ranges` ( 78 `id` int(10) unsigned NOT NULL auto_increment, 79 `date_in` datetime NOT NULL default '0000-00-00 00:00:00', 80 `node_id` int(10) unsigned NOT NULL default '0', 81 `ip_start` int(10) unsigned NOT NULL default '0', 82 `ip_end` int(10) unsigned NOT NULL default '0', 83 `status` enum('waiting','active','pending','rejected','invalid') NOT NULL default 'waiting', 84 `info` text, 85 `delete_req` enum('Y','N') NOT NULL default 'N', 86 PRIMARY KEY (`id`), 87 UNIQUE KEY `unique_keys` (`node_id`,`ip_start`,`ip_end`), 88 KEY `date_in` (`date_in`), 89 KEY `ip_start` (`ip_start`), 90 KEY `ip_end` (`ip_end`), 91 KEY `status` (`status`), 92 KEY `delete_req` (`delete_req`) 93 ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 94 95 CREATE TABLE `links` ( 96 `id` int(10) unsigned NOT NULL auto_increment, 97 `date_in` datetime NOT NULL default '0000-00-00 00:00:00', 98 `node_id` int(10) unsigned NOT NULL default '0', 99 `peer_node_id` int(10) unsigned default NULL, 100 `peer_ap_id` int(10) unsigned default NULL, 101 `type` enum('p2p','ap','client') NOT NULL default 'p2p', 102 `ssid` varchar(50) default NULL, 103 `protocol` enum('IEEE 802.11b','IEEE 802.11g','IEEE 802.11a','other') default NULL, 104 `channel` varchar(50) default NULL, 105 `status` enum('active','inactive') NOT NULL default 'active', 106 `equipment` text, 107 `info` text, 108 PRIMARY KEY (`id`), 109 KEY `node_id` (`node_id`), 110 KEY `peer_node_id` (`peer_node_id`), 111 KEY `type` (`type`), 112 KEY `status` (`status`), 113 KEY `peer_ap_id` (`peer_ap_id`) 114 ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 115 116 CREATE TABLE `nodes` ( 117 `id` int(10) unsigned NOT NULL auto_increment, 118 `date_in` datetime NOT NULL default '0000-00-00 00:00:00', 119 `name` varchar(50) NOT NULL default '', 120 `name_ns` varchar(50) NOT NULL default '', 121 `area_id` int(10) unsigned default '0', 122 `latitude` float default NULL, 123 `longitude` float default NULL, 124 `elevation` int(10) unsigned default NULL, 125 `info` text, 126 PRIMARY KEY (`id`), 127 UNIQUE KEY `unique_keys` (`name_ns`), 128 KEY `date_in` (`date_in`), 129 KEY `name` (`name`), 130 KEY `area_id` (`area_id`), 131 KEY `latitude` (`latitude`), 132 KEY `longitude` (`longitude`) 133 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0; 134 135 CREATE TABLE `photos` ( 136 `id` int(10) unsigned NOT NULL auto_increment, 137 `date_in` datetime NOT NULL default '0000-00-00 00:00:00', 138 `node_id` int(10) unsigned NOT NULL default '0', 139 `type` enum('galery','view') NOT NULL default 'galery', 140 `view_point` enum('N','NE','E','SE','S','SW','W','NW','PANORAMIC') default NULL, 141 `info` text, 142 PRIMARY KEY (`id`), 143 KEY `date_in` (`date_in`), 144 KEY `node_id` (`node_id`), 145 KEY `type` (`type`), 146 KEY `view_point` (`view_point`) 147 ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 148 149 CREATE TABLE `regions` ( 150 `id` int(10) unsigned NOT NULL default '0', 151 `name` varchar(40) NOT NULL default '', 152 `ip_start` int(10) unsigned NOT NULL default '0', 153 `ip_end` int(10) unsigned NOT NULL default '0', 154 `info` text, 155 PRIMARY KEY (`id`), 156 KEY `name` (`name`), 157 KEY `ip_start` (`ip_start`), 158 KEY `ip_end` (`ip_end`) 159 ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 160 161 CREATE TABLE `rights` ( 162 `id` int(10) unsigned NOT NULL auto_increment, 163 `user_id` int(10) unsigned NOT NULL default '0', 164 `type` enum('blocked','admin','hostmaster') NOT NULL default 'blocked', 165 PRIMARY KEY (`id`), 166 UNIQUE KEY `unique_keys` (`type`,`user_id`), 167 KEY `user_id` (`user_id`) 168 ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 169 170 CREATE TABLE `subnets` ( 171 `id` int(10) unsigned NOT NULL auto_increment, 172 `date_in` datetime NOT NULL default '0000-00-00 00:00:00', 173 `node_id` int(10) unsigned default NULL, 174 `ip_start` int(10) unsigned NOT NULL default '0', 175 `ip_end` int(10) unsigned NOT NULL default '0', 176 `type` enum('local','link','client') NOT NULL default 'local', 177 `link_id` int(10) unsigned default NULL, 178 `client_node_id` int(10) unsigned default NULL, 179 PRIMARY KEY (`id`), 180 KEY `node_id` (`node_id`) 181 ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 182 183 CREATE TABLE `users` ( 184 `id` int(10) unsigned NOT NULL auto_increment, 185 `date_in` datetime NOT NULL default '0000-00-00 00:00:00', 186 `username` varchar(30) NOT NULL default '', 187 `password` varchar(40) default NULL, 188 `surname` varchar(30) default NULL, 189 `name` varchar(30) default NULL, 190 `phone` varchar(60) default NULL, 191 `email` varchar(50) NOT NULL default '', 192 `info` text, 193 `last_session` datetime default NULL, 194 `last_visit` datetime default NULL, 195 `status` enum('activated','pending') NOT NULL default 'pending', 196 `account_code` varchar(20) default NULL, 197 PRIMARY KEY (`id`), 198 UNIQUE KEY `username` (`username`), 199 UNIQUE KEY `email` (`email`), 200 KEY `date_in` (`date_in`), 201 KEY `password` (`password`), 202 KEY `surname` (`surname`), 203 KEY `name` (`name`), 204 KEY `status` (`status`) 205 ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 206 207 CREATE TABLE `users_nodes` ( 208 `id` int(10) unsigned NOT NULL auto_increment, 209 `user_id` int(10) unsigned NOT NULL default '0', 210 `node_id` int(10) unsigned NOT NULL default '0', 211 `owner` enum('Y','N') NOT NULL default 'N', 212 PRIMARY KEY (`id`), 213 UNIQUE KEY `unique_keys` (`node_id`,`user_id`), 214 KEY `user_id` (`user_id`) 215 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Note: See TracChangeset
for help on using the changeset viewer.
