use eclipse;
CREATE TABLE `download_file_index` (   
	`file_id` int(10) unsigned NOT NULL auto_increment,   
	`file_name` varchar(255) NOT NULL default '',   
	`download_count` int(10) unsigned NOT NULL default '0',   
	`timestamp_disk` bigint(20) NOT NULL default '0',   
	`size_disk_bytes` int(10) unsigned default '0',   
	`md5sum` char(32) default NULL,   `sha1sum` char(40) default NULL,   `sha512sum` char(128) default NULL,   
	PRIMARY KEY  (`file_id`),   
	KEY `IDX_timestamp_disk` (`timestamp_disk`),   
	KEY `idx_file_name` (`file_name`(30)) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `downloads` (
	  `file_id` int(10) unsigned NOT NULL default '0',
	  `download_date` datetime default NULL,
	  `remote_host` varchar(100) default NULL,
	  `remote_addr` varchar(15) default NULL,
	  `mirror_id` int(10) unsigned default NULL,
	  `ccode` char(2) default NULL,
	  KEY `IDX_file_id` (`file_id`),
	  KEY `IDX_download_date` (`download_date`),
	  KEY `IDX_ccode` (`ccode`),
	  KEY `IDX_mirror_id` (`mirror_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=1000000000 AVG_ROW_LENGTH=56;

CREATE TABLE `mirrors` (
	  `organization` varchar(200) default NULL,
	  `ccode` char(2) default NULL,
	  `mirror_id` int(10) unsigned NOT NULL auto_increment,
	  `update_ip_allow` varchar(50) default NULL,
	  `email` varchar(50) default NULL,
	  `is_internal` tinyint(1) default '0',
	  `is_advertise` tinyint(1) default '1',
	  `date_enabled` date default NULL,
	  `create_status` enum('approve','wait','active','dropped') default NULL,
	  `contact` varchar(50) default NULL,
	  `internal_host_pattern` varchar(200) NOT NULL default '',
	  PRIMARY KEY  (`mirror_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `drops` (
	  `drop_id` varchar(30) NOT NULL DEFAULT '',
	  `project_id` varchar(100) NOT NULL DEFAULT '',
	  `rsync_stanza` varchar(50) NOT NULL DEFAULT '',
	  `description` varchar(200) NOT NULL DEFAULT '',
	  `is_active` tinyint(1) NOT NULL DEFAULT '1',
	  `our_path` varchar(200) NOT NULL DEFAULT '',
	  `size` double NOT NULL DEFAULT '0',
	  `size_unit` char(1) NOT NULL DEFAULT 'G',
	  `file_pattern` varchar(100) NOT NULL DEFAULT '',
	  `gen_timestamp` tinyint(1) DEFAULT '1',
	  `sort_order` int(11) DEFAULT '0',
	  `is_default_selection` tinyint(1) DEFAULT '0',
	  PRIMARY KEY (`drop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `mirror_protocols` (
	  `mirror_id` int(10) unsigned NOT NULL default '0',
	  `protocol` varchar(20) NOT NULL default '',
	  `base_path` varchar(200) NOT NULL default '',
	  PRIMARY KEY  (`protocol`,`mirror_id`),
	  KEY `FK_MP_mirror_id` (`mirror_id`),
	  CONSTRAINT `mirror_protocols_ibfk_1` FOREIGN KEY (`mirror_id`) REFERENCES `mirrors` (`mirror_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `mirror_drops` (
	  `mirror_id` int(10) unsigned NOT NULL default '0',
	  `drop_id` varchar(30) NOT NULL default '',
	  `rel_path_override` varchar(200) default '',
	  `protocol` varchar(20) NOT NULL default '',
	  `timestamp` bigint(20) default '0',
	  `timestamp_end` bigint(20) default '0',
	  `timestamp_last_complete` bigint(20) default '0',
	  PRIMARY KEY  (`mirror_id`,`drop_id`,`protocol`),
	  KEY `FK_MD_drop_id` (`drop_id`),
	  CONSTRAINT `mirror_drops_ibfk_1` FOREIGN KEY (`mirror_id`) REFERENCES `mirrors` (`mirror_id`) ON DELETE CASCADE ON UPDATE CASCADE,
	  CONSTRAINT `mirror_drops_ibfk_2` FOREIGN KEY (`drop_id`) REFERENCES `drops` (`drop_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `mirror_trends` (
	  `trend_id` int(10) unsigned NOT NULL auto_increment,
	  `mirror_id` int(10) unsigned NOT NULL,
	  `sample_date` datetime default NULL,
	  `http_status_code` mediumint(8) unsigned NOT NULL,
	  `download_speed_kbps` mediumint(8) unsigned default NULL,
	  `fetched_url` varchar(300) default NULL,
	  `fetched_size_bytes` int(10) unsigned default NULL,
	  PRIMARY KEY  (`trend_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `drops` VALUES ('EclipseFull','root','fullMirror','Full Polarsys Mirror',1,'/',600,'G','',1,10,1);
CREATE TABLE `SYS_countries` (
  `ccode` char(2) NOT NULL DEFAULT '',
  `en_description` varchar(200) NOT NULL DEFAULT '',
  `fr_description` varchar(200) DEFAULT NULL,
  `continent_code` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`ccode`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `SYS_countries`
--

LOCK TABLES `SYS_countries` WRITE;
/*!40000 ALTER TABLE `SYS_countries` DISABLE KEYS */;
INSERT INTO `SYS_countries` VALUES ('af','Afghanistan','','as'),('al','Albania','','eu'),('dz','Algeria','','af'),('as','American Samoa','','oc'),('ad','Andorra','','eu'),('ao','Angola','','af'),('ai','Anguilla','','sa'),('ag','Antigua and Barbuda','','sa'),('ar','Argentina','','sa'),('am','Armenia','','as'),('aw','Aruba','','sa'),('au','Australia','','oc'),('at','Austria','','eu'),('az','Azerbaijan','','as'),('bs','Bahamas','','sa'),('bh','Bahrain','','as'),('bd','Bangladesh','','as'),('bb','Barbados','','sa'),('by','Belarus','','eu'),('be','Belgium','','eu'),('bz','Belize','','sa'),('bj','Benin','','af'),('bm','Bermuda','','sa'),('bt','Bhutan','','as'),('bo','Bolivia','','sa'),('ba','Bosnia and Herzegowina','','eu'),('bw','Botswana','','af'),('br','Brazil','','sa'),('io','British Indian Ocean Territory','','as'),('bn','Brunei Darussalam','','as'),('bg','Bulgaria','','eu'),('bf','Burkina Faso','','af'),('bi','Burundi','','af'),('kh','Cambodia','','as'),('cm','Cameroon','','af'),('ca','Canada','','na'),('cv','Cape Verde','','af'),('ky','Cayman Islands','','sa'),('cf','Central African Republic','','af'),('td','Chad','','af'),('cl','Chile','','sa'),('cn','China','','as'),('cx','Christmas Island','','as'),('co','Colombia','','sa'),('km','Comoros','','af'),('cg','Congo','','af'),('ck','Cook Islands','','oc'),('cr','Costa Rica','','sa'),('ci','Cote D\'Ivoire','','af'),('hr','Croatia (Hrvatska)','','eu'),('cy','Cyprus','','as'),('cz','Czech Republic','','eu'),('dk','Denmark','','eu'),('dj','Djibouti','','af'),('dm','Dominica','','sa'),('do','Dominican Republic','','sa'),('ec','Ecuador','','sa'),('eg','Egypt','','af'),('sv','El Salvador','','sa'),('gq','Equatorial Guinea','','af'),('er','Eritrea','','af'),('ee','Estonia','','eu'),('et','Ethiopia','','af'),('fk','Falkland Islands (Malvinas)','','sa'),('fj','Fiji','','oc'),('fi','Finland','','eu'),('fr','France','','eu'),('gf','French Guiana','','sa'),('pf','French Polynesia','','oc'),('ga','Gabon','','af'),('gm','Gambia','','af'),('ge','Georgia','','as'),('de','Germany','','eu'),('gh','Ghana','','af'),('gi','Gibraltar','','eu'),('gr','Greece','','eu'),('gl','Greenland','','sa'),('gd','Grenada','','sa'),('gp','Guadeloupe','','sa'),('gu','Guam','','oc'),('gt','Guatemala','','sa'),('gn','Guinea','','af'),('gw','Guinea-Bissau','','af'),('gy','Guyana','','sa'),('ht','Haiti','','sa'),('hn','Honduras','','sa'),('hk','Hong Kong','','as'),('hu','Hungary','','eu'),('is','Iceland','','eu'),('in','India','','as'),('id','Indonesia','','as'),('ie','Ireland','','eu'),('il','Israel','','as'),('it','Italy','','eu'),('jm','Jamaica','','sa'),('jp','Japan','','as'),('jo','Jordan','','as'),('kz','Kazakhstan','','as'),('ke','Kenya','','af'),('ki','Kiribati','','oc'),('kr','Korea, Republic Of','','as'),('kw','Kuwait','','as'),('kg','Kyrgyzstan','','as'),('la','Lao People\'s Democratic Republic','','as'),('lv','Latvia','','eu'),('lb','Lebanon','','as'),('ls','Lesotho','','af'),('lr','Liberia','','af'),('li','Liechtenstein','','eu'),('lt','Lithuania','','eu'),('lu','Luxembourg','','eu'),('mo','Macau','','as'),('mk','Macedonia, The Former Yugoslav Republic Of','','eu'),('mg','Madagascar','','af'),('mw','Malawi','','af'),('my','Malaysia','','as'),('mv','Maldives','','as'),('ml','Mali','','af'),('mt','Malta','','eu'),('mh','Marshall Islands','','oc'),('mq','Martinique','','sa'),('mr','Mauritania','','af'),('mu','Mauritius','','af'),('mx','Mexico','','na'),('fm','Micronesia, Federated States Of','','oc'),('md','Moldova, Republic Of','','eu'),('mc','Monaco','','eu'),('mn','Mongolia','','as'),('ms','Montserrat','','sa'),('ma','Morocco','','af'),('mz','Mozambique','','af'),('mm','Myanmar','','as'),('na','Namibia','','af'),('nr','Nauru','','oc'),('np','Nepal','','as'),('nl','Netherlands','','eu'),('an','Netherlands Antilles','','sa'),('nc','New Caledonia','','oc'),('nz','New Zealand','','oc'),('ni','Nicaragua','','sa'),('ne','Niger','','af'),('ng','Nigeria','','af'),('nu','Niue','','oc'),('nf','Norfolk Island','','oc'),('mp','Northern Mariana Islands','','oc'),('no','Norway','','eu'),('om','Oman','','as'),('pk','Pakistan','','as'),('pw','Palau','','oc'),('pa','Panama','','sa'),('pg','Papua New Guinea','','oc'),('py','Paraguay','','sa'),('pe','Peru','','sa'),('ph','Philippines','','as'),('pn','Pitcairn','','oc'),('pl','Poland','','eu'),('pt','Portugal','','eu'),('pr','Puerto Rico','','sa'),('qa','Qatar','','as'),('ro','Romania','','eu'),('ru','Russian Federation','','eu'),('rw','Rwanda','','af'),('kn','Saint Kitts and Nevis','','sa'),('lc','Saint Lucia','','sa'),('vc','Saint Vincent and The Grenadines','','sa'),('ws','Samoa','','oc'),('sm','San Marino','','eu'),('st','Sao Tome and Principe','','af'),('sa','Saudi Arabia','','as'),('sn','Senegal','','af'),('yu','Serbia & Montenegro','','eu'),('sc','Seychelles','','af'),('sl','Sierra Leone','','af'),('sg','Singapore','','as'),('sk','Slovakia (Slovak Republic)','','eu'),('si','Slovenia','','eu'),('sb','Solomon Islands','','oc'),('so','Somalia','','af'),('za','South Africa','','af'),('es','Spain','','eu'),('lk','Sri Lanka','','as'),('sr','Suriname','','sa'),('sz','Swaziland','','af'),('se','Sweden','','eu'),('ch','Switzerland','','eu'),('tw','Taiwan','','as'),('tj','Tajikistan','','as'),('tz','Tanzania, United Republic Of','','af'),('th','Thailand','','as'),('tg','Togo','','af'),('tk','Tokelau','','oc'),('to','Tonga','','oc'),('tt','Trinidad and Tobago','','sa'),('tn','Tunisia','','af'),('tr','Turkey','','eu'),('tm','Turkmenistan','','as'),('tc','Turks and Caicos Islands','','sa'),('tv','Tuvalu','','oc'),('ug','Uganda','','af'),('ua','Ukraine','','eu'),('ae','United Arab Emirates','','as'),('gb','United Kingdom','','eu'),('us','United States','','na'),('uy','Uruguay','','sa'),('uz','Uzbekistan','','as'),('vu','Vanuatu','','oc'),('va','Vatican City State (Holy Sea)','','eu'),('ve','Venezuela','','sa'),('vn','Viet Nam','','as'),('vg','Virgin Islands (British)','','sa'),('vi','Virgin Islands (U.S.)','','sa'),('wf','Wallis and Futuna Islands','','oc'),('eh','Western Sahara','','af'),('ye','Yemen','','as'),('zm','Zambia','','af'),('zw','Zimbabwe','','af');

UNLOCK TABLES;

CREATE TABLE `SYS_continents` (
  `continent_code` char(2) NOT NULL DEFAULT '',
  `en_description` varchar(75) NOT NULL DEFAULT '',
  `sort_order` tinyint(3) unsigned DEFAULT '0',
  PRIMARY KEY (`continent_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

LOCK TABLES `SYS_continents` WRITE;
INSERT INTO `SYS_continents` VALUES ('na','North America',100),('sa','South America',0),('as','Asia',15),('eu','Europe',90),('af','Africa',10),('oc','Australia/Oceania',5);
UNLOCK TABLES;