Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Fetching contributors…

Cannot retrieve contributors at this time

1496 lines (1301 sloc) 51.435 kB
-- Ushahidi Engine
-- version 92
-- http://www.ushahidi.com
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
-- --------------------------------------------------------
/**
* Table structure for table `actions`
*
*/
CREATE TABLE IF NOT EXISTS `actions` (
`action_id` int(11) NOT NULL AUTO_INCREMENT,
`action` varchar(75) NOT NULL,
`qualifiers` text NOT NULL,
`response` varchar(75) NOT NULL,
`response_vars` text NOT NULL,
`active` tinyint(4) NOT NULL,
PRIMARY KEY (`action_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores user defined actions triggered by certain events' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `actions_log`
*
*/
CREATE TABLE IF NOT EXISTS `actions_log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`action_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`time` int(10) NOT NULL,
PRIMARY KEY (`id`),
KEY `action_id` (`action_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores a log of triggered actions' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `alert`
*
*/
CREATE TABLE IF NOT EXISTS `alert` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) unsigned DEFAULT '0',
`alert_type` tinyint(4) NOT NULL COMMENT '1 - MOBILE, 2 - EMAIL',
`alert_recipient` varchar(200) DEFAULT NULL,
`alert_code` varchar(30) DEFAULT NULL,
`alert_confirmed` tinyint(4) NOT NULL DEFAULT '0',
`alert_lat` varchar(150) DEFAULT NULL,
`alert_lon` varchar(150) DEFAULT NULL,
`alert_radius` tinyint(4) NOT NULL DEFAULT '20',
`alert_ip` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_alert_code` (`alert_code`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores alerts subscribers information' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `alert_category`
*
*/
CREATE TABLE IF NOT EXISTS `alert_category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`alert_id` bigint(20) unsigned DEFAULT NULL,
`category_id` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `alert_id` (`alert_id`),
KEY `category_id` (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores subscriber alert categories' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `alert_sent`
*
*/
CREATE TABLE IF NOT EXISTS `alert_sent` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`incident_id` bigint(20) unsigned NOT NULL,
`alert_id` bigint(20) unsigned NOT NULL,
`alert_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `incident_id` (`incident_id`),
KEY `alert_id` (`alert_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores a log of alerts sent out to subscribers' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `api_banned`
*
*/
CREATE TABLE IF NOT EXISTS `api_banned` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`banned_ipaddress` varchar(50) NOT NULL,
`banned_date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='For logging banned API IP addresses' AUTO_INCREMENT=8 ;
-- --------------------------------------------------------
/**
* Table structure for table `api_log`
*
*/
CREATE TABLE IF NOT EXISTS `api_log` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`api_task` varchar(10) NOT NULL,
`api_parameters` varchar(100) NOT NULL,
`api_records` tinyint(11) NOT NULL,
`api_ipaddress` varchar(50) NOT NULL,
`api_date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='For logging API activities' AUTO_INCREMENT=19 ;
-- --------------------------------------------------------
/**
* Table structure for table `api_settings`
*
*/
CREATE TABLE IF NOT EXISTS `api_settings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`default_record_limit` int(11) NOT NULL DEFAULT '20',
`max_record_limit` int(11) DEFAULT NULL,
`max_requests_per_ip_address` int(11) DEFAULT NULL,
`max_requests_quota_basis` int(11) DEFAULT NULL,
`modification_date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='For storing API logging settings' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `badge`
*
*/
CREATE TABLE IF NOT EXISTS `badge` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(250) NOT NULL,
`description` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores description of badges to be assigned' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `badge_users`
*
*/
CREATE TABLE IF NOT EXISTS `badge_users` (
`user_id` int(11) unsigned NOT NULL,
`badge_id` int(11) NOT NULL,
PRIMARY KEY (`user_id`,`badge_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores assigned badge information';
-- --------------------------------------------------------
/**
* Table structure for table `category`
*
*/
CREATE TABLE IF NOT EXISTS `category` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` int(11) NOT NULL DEFAULT '0',
`locale` varchar(10) NOT NULL DEFAULT 'en_US',
`category_position` tinyint(4) NOT NULL DEFAULT '0',
`category_title` varchar(255) DEFAULT NULL,
`category_description` text DEFAULT NULL,
`category_color` varchar(20) DEFAULT NULL,
`category_image` varchar(255) DEFAULT NULL,
`category_image_thumb` varchar(255) DEFAULT NULL,
`category_visible` tinyint(4) NOT NULL DEFAULT '1',
`category_trusted` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `category_visible` (`category_visible`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Holds information about categories defined for a deployment' AUTO_INCREMENT=6 ;
--
-- Dumping data for table `category`
--
INSERT INTO `category` (`id`,`category_title`, `category_description`, `category_color`, `category_visible`, `category_trusted`, `category_position`) VALUES
(1, 'Category 1', 'Category 1', '9900CC', 1, 0, 0),
(2, 'Category 2', 'Category 2', '3300FF', 1, 0, 1),
(3, 'Category 3', 'Category 3', '663300', 1, 0, 2),
(4, 'Trusted Reports', 'Reports from trusted reporters', '339900', 1, 1, 3),
(5, 'NONE', 'Holds uncategorized reports', '009887', 1, 1, 4);
-- --------------------------------------------------------
/**
* Table structure for table `category_lang`
*
*/
CREATE TABLE IF NOT EXISTS `category_lang` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`category_id` int(11) unsigned NOT NULL,
`locale` varchar(10) DEFAULT NULL,
`category_title` varchar(255) DEFAULT NULL,
`category_description` text DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `category_id` (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Holds translations for category titles and descriptions' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `checkin`
*
*/
CREATE TABLE IF NOT EXISTS `checkin` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) unsigned NOT NULL,
`location_id` bigint(20) unsigned NOT NULL,
`incident_id` bigint(20) unsigned DEFAULT '0',
`checkin_description` varchar(255),
`checkin_date` datetime NOT NULL,
`checkin_auto` enum('0','1') DEFAULT '0',
PRIMARY KEY (`id`),
KEY `incident_id` (`incident_id`),
KEY `user_id` (`user_id`),
KEY `location_id` (`location_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores checkin information' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `city`
*
*/
CREATE TABLE IF NOT EXISTS `city` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`country_id` int(11) DEFAULT NULL,
`city` varchar(200) DEFAULT NULL,
`city_lat` varchar(150) DEFAULT NULL,
`city_lon` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `country_id` (`country_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores cities of countries retrieved by user.' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `cluster`
*
*/
CREATE TABLE IF NOT EXISTS `cluster` (
`id` int(11) NOT NULL,
`location_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`latitude` double NOT NULL,
`longitude` double NOT NULL,
`latitude_min` double NOT NULL,
`longitude_min` double NOT NULL,
`latitude_max` double NOT NULL,
`longitude_max` double NOT NULL,
`child_count` int(11) NOT NULL,
`parent_id` int(11) NOT NULL,
`left_side` int(11) NOT NULL,
`right_side` int(11) NOT NULL,
`level` int(11) NOT NULL,
`incident_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`category_id` int(11) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `location_id` (`location_id`),
KEY `incident_id` (`incident_id`),
KEY `category_id` (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores information used for clustering of reports on the map.';
-- --------------------------------------------------------
/**
* Table structure for table `comment`
*
*/
CREATE TABLE IF NOT EXISTS `comment` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`incident_id` bigint(20) unsigned DEFAULT NULL,
`checkin_id` bigint(20) unsigned DEFAULT NULL,
`user_id` int(11) unsigned DEFAULT '0',
`comment_author` varchar(100) DEFAULT NULL,
`comment_email` varchar(120) DEFAULT NULL,
`comment_description` text,
`comment_ip` varchar(100) DEFAULT NULL,
`comment_spam` tinyint(4) NOT NULL DEFAULT '0',
`comment_active` tinyint(4) NOT NULL DEFAULT '0',
`comment_date` datetime DEFAULT NULL,
`comment_date_gmt` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `incident_id` (`incident_id`),
KEY `checkin_id` (`checkin_id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores comments made on reports/checkins' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `country`
*
*/
CREATE TABLE IF NOT EXISTS `country` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`iso` varchar(10) DEFAULT NULL,
`country` varchar(100) DEFAULT NULL,
`capital` varchar(100) DEFAULT NULL,
`cities` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores a list of all countries and their capital cities' AUTO_INCREMENT=248 ;
--
-- Dumping data for table `country`
--
INSERT INTO `country` (`id`, `iso`, `country`, `capital`, `cities`) VALUES
(1, 'AD', 'Andorra', 'Andorra la Vella', 0),
(2, 'AE', 'United Arab Emirates', 'Abu Dhabi', 0),
(3, 'AF', 'Afghanistan', 'Kabul', 0),
(4, 'AG', 'Antigua and Barbuda', 'St. John''s', 0),
(5, 'AI', 'Anguilla', 'The Valley', 0),
(6, 'AL', 'Albania', 'Tirana', 0),
(7, 'AM', 'Armenia', 'Yerevan', 0),
(8, 'AN', 'Netherlands Antilles', 'Willemstad', 0),
(9, 'AO', 'Angola', 'Luanda', 0),
(10, 'AQ', 'Antarctica', '', 0),
(11, 'AR', 'Argentina', 'Buenos Aires', 0),
(12, 'AS', 'American Samoa', 'Pago Pago', 0),
(13, 'AT', 'Austria', 'Vienna', 0),
(14, 'AU', 'Australia', 'Canberra', 0),
(15, 'AW', 'Aruba', 'Oranjestad', 0),
(16, 'AX', 'Aland Islands', 'Mariehamn', 0),
(17, 'AZ', 'Azerbaijan', 'Baku', 0),
(18, 'BA', 'Bosnia and Herzegovina', 'Sarajevo', 0),
(19, 'BB', 'Barbados', 'Bridgetown', 0),
(20, 'BD', 'Bangladesh', 'Dhaka', 0),
(21, 'BE', 'Belgium', 'Brussels', 0),
(22, 'BF', 'Burkina Faso', 'Ouagadougou', 0),
(23, 'BG', 'Bulgaria', 'Sofia', 0),
(24, 'BH', 'Bahrain', 'Manama', 0),
(25, 'BI', 'Burundi', 'Bujumbura', 0),
(26, 'BJ', 'Benin', 'Porto-Novo', 0),
(27, 'BL', 'Saint Barthélemy', 'Gustavia', 0),
(28, 'BM', 'Bermuda', 'Hamilton', 0),
(29, 'BN', 'Brunei', 'Bandar Seri Begawan', 0),
(30, 'BO', 'Bolivia', 'La Paz', 0),
(31, 'BR', 'Brazil', 'Brasília', 0),
(32, 'BS', 'Bahamas', 'Nassau', 0),
(33, 'BT', 'Bhutan', 'Thimphu', 0),
(34, 'BV', 'Bouvet Island', '', 0),
(35, 'BW', 'Botswana', 'Gaborone', 0),
(36, 'BY', 'Belarus', 'Minsk', 0),
(37, 'BZ', 'Belize', 'Belmopan', 0),
(38, 'CA', 'Canada', 'Ottawa', 0),
(39, 'CC', 'Cocos Islands', 'West Island', 0),
(40, 'CD', 'Democratic Republic of the Congo', 'Kinshasa', 0),
(41, 'CF', 'Central African Republic', 'Bangui', 0),
(42, 'CG', 'Congo Brazzavile', 'Brazzaville', 0),
(43, 'CH', 'Switzerland', 'Berne', 0),
(44, 'CI', 'Ivory Coast', 'Yamoussoukro', 0),
(45, 'CK', 'Cook Islands', 'Avarua', 0),
(46, 'CL', 'Chile', 'Santiago', 0),
(47, 'CM', 'Cameroon', 'Yaoundé', 0),
(48, 'CN', 'China', 'Beijing', 0),
(49, 'CO', 'Colombia', 'Bogotá', 0),
(50, 'CR', 'Costa Rica', 'San José', 0),
(51, 'CS', 'Serbia and Montenegro', 'Belgrade', 0),
(52, 'CU', 'Cuba', 'Havana', 0),
(53, 'CV', 'Cape Verde', 'Praia', 0),
(54, 'CX', 'Christmas Island', 'Flying Fish Cove', 0),
(55, 'CY', 'Cyprus', 'Nicosia', 0),
(56, 'CZ', 'Czech Republic', 'Prague', 0),
(57, 'DE', 'Germany', 'Berlin', 0),
(58, 'DJ', 'Djibouti', 'Djibouti', 0),
(59, 'DK', 'Denmark', 'Copenhagen', 0),
(60, 'DM', 'Dominica', 'Roseau', 0),
(61, 'DO', 'Dominican Republic', 'Santo Domingo', 0),
(62, 'DZ', 'Algeria', 'Algiers', 0),
(63, 'EC', 'Ecuador', 'Quito', 0),
(64, 'EE', 'Estonia', 'Tallinn', 0),
(65, 'EG', 'Egypt', 'Cairo', 0),
(66, 'EH', 'Western Sahara', 'El-Aaiun', 0),
(67, 'ER', 'Eritrea', 'Asmara', 0),
(68, 'ES', 'Spain', 'Madrid', 0),
(69, 'ET', 'Ethiopia', 'Addis Ababa', 0),
(70, 'FI', 'Finland', 'Helsinki', 0),
(71, 'FJ', 'Fiji', 'Suva', 0),
(72, 'FK', 'Falkland Islands', 'Stanley', 0),
(73, 'FM', 'Micronesia', 'Palikir', 0),
(74, 'FO', 'Faroe Islands', 'Tórshavn', 0),
(75, 'FR', 'France', 'Paris', 0),
(76, 'GA', 'Gabon', 'Libreville', 0),
(77, 'GB', 'United Kingdom', 'London', 0),
(78, 'GD', 'Grenada', 'St. George''s', 0),
(79, 'GE', 'Georgia', 'Tbilisi', 0),
(80, 'GF', 'French Guiana', 'Cayenne', 0),
(81, 'GG', 'Guernsey', 'St Peter Port', 0),
(82, 'GH', 'Ghana', 'Accra', 0),
(83, 'GI', 'Gibraltar', 'Gibraltar', 0),
(84, 'GL', 'Greenland', 'Nuuk', 0),
(85, 'GM', 'Gambia', 'Banjul', 0),
(86, 'GN', 'Guinea', 'Conakry', 0),
(87, 'GP', 'Guadeloupe', 'Basse-Terre', 0),
(88, 'GQ', 'Equatorial Guinea', 'Malabo', 0),
(89, 'GR', 'Greece', 'Athens', 0),
(90, 'GS', 'South Georgia and the South Sandwich Islands', 'Grytviken', 0),
(91, 'GT', 'Guatemala', 'Guatemala City', 0),
(92, 'GU', 'Guam', 'Hagåtña', 0),
(93, 'GW', 'Guinea-Bissau', 'Bissau', 0),
(94, 'GY', 'Guyana', 'Georgetown', 0),
(95, 'HK', 'Hong Kong', 'Hong Kong', 0),
(96, 'HM', 'Heard Island and McDonald Islands', '', 0),
(97, 'HN', 'Honduras', 'Tegucigalpa', 0),
(98, 'HR', 'Croatia', 'Zagreb', 0),
(99, 'HT', 'Haiti', 'Port-au-Prince', 0),
(100, 'HU', 'Hungary', 'Budapest', 0),
(101, 'ID', 'Indonesia', 'Jakarta', 0),
(102, 'IE', 'Ireland', 'Dublin', 0),
(103, 'IL', 'Israel', 'Jerusalem', 0),
(104, 'IM', 'Isle of Man', 'Douglas, Isle of Man', 0),
(105, 'IN', 'India', 'New Delhi', 0),
(106, 'IO', 'British Indian Ocean Territory', 'Diego Garcia', 0),
(107, 'IQ', 'Iraq', 'Baghdad', 0),
(108, 'IR', 'Iran', 'Tehran', 0),
(109, 'IS', 'Iceland', 'Reykjavík', 0),
(110, 'IT', 'Italy', 'Rome', 0),
(111, 'JE', 'Jersey', 'Saint Helier', 0),
(112, 'JM', 'Jamaica', 'Kingston', 0),
(113, 'JO', 'Jordan', 'Amman', 0),
(114, 'JP', 'Japan', 'Tokyo', 0),
(115, 'KE', 'Kenya', 'Nairobi', 0),
(116, 'KG', 'Kyrgyzstan', 'Bishkek', 0),
(117, 'KH', 'Cambodia', 'Phnom Penh', 0),
(118, 'KI', 'Kiribati', 'South Tarawa', 0),
(119, 'KM', 'Comoros', 'Moroni', 0),
(120, 'KN', 'Saint Kitts and Nevis', 'Basseterre', 0),
(121, 'KP', 'North Korea', 'Pyongyang', 0),
(122, 'KR', 'South Korea', 'Seoul', 0),
(123, 'KW', 'Kuwait', 'Kuwait City', 0),
(124, 'KY', 'Cayman Islands', 'George Town', 0),
(125, 'KZ', 'Kazakhstan', 'Astana', 0),
(126, 'LA', 'Laos', 'Vientiane', 0),
(127, 'LB', 'Lebanon', 'Beirut', 0),
(128, 'LC', 'Saint Lucia', 'Castries', 0),
(129, 'LI', 'Liechtenstein', 'Vaduz', 0),
(130, 'LK', 'Sri Lanka', 'Colombo', 0),
(131, 'LR', 'Liberia', 'Monrovia', 0),
(132, 'LS', 'Lesotho', 'Maseru', 0),
(133, 'LT', 'Lithuania', 'Vilnius', 0),
(134, 'LU', 'Luxembourg', 'Luxembourg', 0),
(135, 'LV', 'Latvia', 'Riga', 0),
(136, 'LY', 'Libya', 'Tripolis', 0),
(137, 'MA', 'Morocco', 'Rabat', 0),
(138, 'MC', 'Monaco', 'Monaco', 0),
(139, 'MD', 'Moldova', 'Chi_in_u', 0),
(140, 'ME', 'Montenegro', 'Podgorica', 0),
(141, 'MF', 'Saint Martin', 'Marigot', 0),
(142, 'MG', 'Madagascar', 'Antananarivo', 0),
(143, 'MH', 'Marshall Islands', 'Uliga', 0),
(144, 'MK', 'Macedonia', 'Skopje', 0),
(145, 'ML', 'Mali', 'Bamako', 0),
(146, 'MM', 'Myanmar', 'Yangon', 0),
(147, 'MN', 'Mongolia', 'Ulan Bator', 0),
(148, 'MO', 'Macao', 'Macao', 0),
(149, 'MP', 'Northern Mariana Islands', 'Saipan', 0),
(150, 'MQ', 'Martinique', 'Fort-de-France', 0),
(151, 'MR', 'Mauritania', 'Nouakchott', 0),
(152, 'MS', 'Montserrat', 'Plymouth', 0),
(153, 'MT', 'Malta', 'Valletta', 0),
(154, 'MU', 'Mauritius', 'Port Louis', 0),
(155, 'MV', 'Maldives', 'Malé', 0),
(156, 'MW', 'Malawi', 'Lilongwe', 0),
(157, 'MX', 'Mexico', 'Mexico City', 0),
(158, 'MY', 'Malaysia', 'Kuala Lumpur', 0),
(159, 'MZ', 'Mozambique', 'Maputo', 0),
(160, 'NA', 'Namibia', 'Windhoek', 0),
(161, 'NC', 'New Caledonia', 'Nouméa', 0),
(162, 'NE', 'Niger', 'Niamey', 0),
(163, 'NF', 'Norfolk Island', 'Kingston', 0),
(164, 'NG', 'Nigeria', 'Abuja', 0),
(165, 'NI', 'Nicaragua', 'Managua', 0),
(166, 'NL', 'Netherlands', 'Amsterdam', 0),
(167, 'NO', 'Norway', 'Oslo', 0),
(168, 'NP', 'Nepal', 'Kathmandu', 0),
(169, 'NR', 'Nauru', 'Yaren', 0),
(170, 'NU', 'Niue', 'Alofi', 0),
(171, 'NZ', 'New Zealand', 'Wellington', 0),
(172, 'OM', 'Oman', 'Muscat', 0),
(173, 'PA', 'Panama', 'Panama City', 0),
(174, 'PE', 'Peru', 'Lima', 0),
(175, 'PF', 'French Polynesia', 'Papeete', 0),
(176, 'PG', 'Papua New Guinea', 'Port Moresby', 0),
(177, 'PH', 'Philippines', 'Manila', 0),
(178, 'PK', 'Pakistan', 'Islamabad', 0),
(179, 'PL', 'Poland', 'Warsaw', 0),
(180, 'PM', 'Saint Pierre and Miquelon', 'Saint-Pierre', 0),
(181, 'PN', 'Pitcairn', 'Adamstown', 0),
(182, 'PR', 'Puerto Rico', 'San Juan', 0),
(183, 'PS', 'Palestinian Territory', 'East Jerusalem', 0),
(184, 'PT', 'Portugal', 'Lisbon', 0),
(185, 'PW', 'Palau', 'Koror', 0),
(186, 'PY', 'Paraguay', 'Asunción', 0),
(187, 'QA', 'Qatar', 'Doha', 0),
(188, 'RE', 'Reunion', 'Saint-Denis', 0),
(189, 'RO', 'Romania', 'Bucharest', 0),
(190, 'RS', 'Serbia', 'Belgrade', 0),
(191, 'RU', 'Russia', 'Moscow', 0),
(192, 'RW', 'Rwanda', 'Kigali', 0),
(193, 'SA', 'Saudi Arabia', 'Riyadh', 0),
(194, 'SB', 'Solomon Islands', 'Honiara', 0),
(195, 'SC', 'Seychelles', 'Victoria', 0),
(196, 'SD', 'Sudan', 'Khartoum', 0),
(197, 'SE', 'Sweden', 'Stockholm', 0),
(198, 'SG', 'Singapore', 'Singapur', 0),
(199, 'SH', 'Saint Helena', 'Jamestown', 0),
(200, 'SI', 'Slovenia', 'Ljubljana', 0),
(201, 'SJ', 'Svalbard and Jan Mayen', 'Longyearbyen', 0),
(202, 'SK', 'Slovakia', 'Bratislava', 0),
(203, 'SL', 'Sierra Leone', 'Freetown', 0),
(204, 'SM', 'San Marino', 'San Marino', 0),
(205, 'SN', 'Senegal', 'Dakar', 0),
(206, 'SO', 'Somalia', 'Mogadishu', 0),
(207, 'SR', 'Suriname', 'Paramaribo', 0),
(208, 'ST', 'Sao Tome and Principe', 'São Tomé', 0),
(209, 'SV', 'El Salvador', 'San Salvador', 0),
(210, 'SY', 'Syria', 'Damascus', 0),
(211, 'SZ', 'Swaziland', 'Mbabane', 0),
(212, 'TC', 'Turks and Caicos Islands', 'Cockburn Town', 0),
(213, 'TD', 'Chad', 'N''Djamena', 0),
(214, 'TF', 'French Southern Territories', 'Martin-de-Viviès', 0),
(215, 'TG', 'Togo', 'Lomé', 0),
(216, 'TH', 'Thailand', 'Bangkok', 0),
(217, 'TJ', 'Tajikistan', 'Dushanbe', 0),
(218, 'TK', 'Tokelau', '', 0),
(219, 'TL', 'East Timor', 'Dili', 0),
(220, 'TM', 'Turkmenistan', 'Ashgabat', 0),
(221, 'TN', 'Tunisia', 'Tunis', 0),
(222, 'TO', 'Tonga', 'Nuku''alofa', 0),
(223, 'TR', 'Turkey', 'Ankara', 0),
(224, 'TT', 'Trinidad and Tobago', 'Port of Spain', 0),
(225, 'TV', 'Tuvalu', 'Vaiaku', 0),
(226, 'TW', 'Taiwan', 'Taipei', 0),
(227, 'TZ', 'Tanzania', 'Dar es Salaam', 0),
(228, 'UA', 'Ukraine', 'Kiev', 0),
(229, 'UG', 'Uganda', 'Kampala', 0),
(230, 'UM', 'United States Minor Outlying Islands', '', 0),
(231, 'US', 'United States', 'Washington', 0),
(232, 'UY', 'Uruguay', 'Montevideo', 0),
(233, 'UZ', 'Uzbekistan', 'Tashkent', 0),
(234, 'VA', 'Vatican', 'Vatican City', 0),
(235, 'VC', 'Saint Vincent and the Grenadines', 'Kingstown', 0),
(236, 'VE', 'Venezuela', 'Caracas', 0),
(237, 'VG', 'British Virgin Islands', 'Road Town', 0),
(238, 'VI', 'U.S. Virgin Islands', 'Charlotte Amalie', 0),
(239, 'VN', 'Vietnam', 'Hanoi', 0),
(240, 'VU', 'Vanuatu', 'Port Vila', 0),
(241, 'WF', 'Wallis and Futuna', 'Matâ''Utu', 0),
(242, 'WS', 'Samoa', 'Apia', 0),
(243, 'YE', 'Yemen', 'San‘a’', 0),
(244, 'YT', 'Mayotte', 'Mamoudzou', 0),
(245, 'ZA', 'South Africa', 'Pretoria', 0),
(246, 'ZM', 'Zambia', 'Lusaka', 0),
(247, 'ZW', 'Zimbabwe', 'Harare', 0);
-- --------------------------------------------------------
/**
* Table structure for table `externalapp`
*
*/
CREATE TABLE IF NOT EXISTS `externalapp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`url` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Info on external apps(mobile) that work with your deployment' AUTO_INCREMENT=3 ;
--
-- Dumping data for table `externalapp`
--
INSERT INTO `externalapp` (`id`, `name`, `url`) VALUES
(1, 'iPhone', 'http://download.ushahidi.com/track_download.php?download=ios'),
(2, 'Android', 'http://download.ushahidi.com/track_download.php?download=android');
-- --------------------------------------------------------
/**
* Table structure for table `feed`
*
*/
CREATE TABLE IF NOT EXISTS `feed` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`feed_name` varchar(255) DEFAULT NULL,
`feed_url` varchar(255) DEFAULT NULL,
`feed_cache` text,
`feed_active` tinyint(4) DEFAULT '1',
`feed_update` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Information about RSS Feeds a deployment subscribes to' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `feed_item`
*
*/
CREATE TABLE IF NOT EXISTS `feed_item` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`feed_id` int(11) unsigned NOT NULL,
`location_id` bigint(20) unsigned DEFAULT '0',
`incident_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`item_title` varchar(255) DEFAULT NULL,
`item_description` text,
`item_link` varchar(255) DEFAULT NULL,
`item_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `feed_id` (`feed_id`),
KEY `incident_id` (`incident_id`),
KEY `location_id` (`location_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores feed items pulled from each RSS Feed' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `form`
*
*/
CREATE TABLE IF NOT EXISTS `form` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`form_title` varchar(200) NOT NULL,
`form_description` text,
`form_active` tinyint(4) DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores all report submission forms created(default+custom)' AUTO_INCREMENT=2 ;
--
-- Dumping data for table `form`
--
INSERT INTO `form` (`id`, `form_title`, `form_description`, `form_active`) VALUES
(1, 'Default Form', 'Default form, for report entry', 1);
-- --------------------------------------------------------
/**
* Table structure for table `form_field`
*
*/
CREATE TABLE IF NOT EXISTS `form_field` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`form_id` int(11) NOT NULL DEFAULT '0',
`field_name` varchar(200) DEFAULT NULL,
`field_type` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1 - TEXTFIELD, 2 - TEXTAREA (FREETEXT), 3 - DATE, 4 - PASSWORD, 5 - RADIO, 6 - CHECKBOX',
`field_required` tinyint(4) DEFAULT '0',
`field_options` text,
`field_position` tinyint(4) NOT NULL DEFAULT '0',
`field_default` varchar(200) DEFAULT NULL,
`field_maxlength` int(11) NOT NULL DEFAULT '0',
`field_width` smallint(6) NOT NULL DEFAULT '0',
`field_height` tinyint(4) DEFAULT '5',
`field_isdate` tinyint(4) NOT NULL DEFAULT '0',
`field_ispublic_visible` tinyint(4) NOT NULL DEFAULT '0',
`field_ispublic_submit` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `fk_form_id` (`form_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores all custom form fields created by users' AUTO_INCREMENT=1 ;
/**
* Constraints for table `form_field`
*/
ALTER TABLE `form_field`
ADD CONSTRAINT `form_field_ibfk_1` FOREIGN KEY (`form_id`) REFERENCES `form` (`id`) ON DELETE CASCADE;
-- --------------------------------------------------------
/**
* Table structure for table `form_field_option`
*
*/
CREATE TABLE IF NOT EXISTS `form_field_option` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`form_field_id` int(11) NOT NULL DEFAULT '0',
`option_name` varchar(200) DEFAULT NULL,
`option_value` text,
PRIMARY KEY (`id`),
KEY `form_field_id` (`form_field_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Options related to custom form fields' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `form_response`
*
*/
CREATE TABLE IF NOT EXISTS `form_response` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`form_field_id` int(11) NOT NULL,
`incident_id` bigint(20) unsigned NOT NULL,
`form_response` text NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_form_field_id` (`form_field_id`),
KEY `incident_id` (`incident_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores responses to custom form fields' AUTO_INCREMENT=1 ;
/**
* Constraints for table `form_response`
*/
ALTER TABLE `form_response`
ADD CONSTRAINT `form_response_ibfk_1` FOREIGN KEY (`form_field_id`) REFERENCES `form_field` (`id`) ON DELETE CASCADE;
-- --------------------------------------------------------
/**
* Table structure for table `geometry`
*
*/
CREATE TABLE IF NOT EXISTS `geometry` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`incident_id` bigint(20) unsigned NOT NULL,
`geometry` geometry NOT NULL,
`geometry_label` varchar(150) DEFAULT NULL,
`geometry_comment` varchar(255) DEFAULT NULL,
`geometry_color` varchar(20) DEFAULT NULL,
`geometry_strokewidth` varchar(5) DEFAULT NULL,
PRIMARY KEY (`id`),
SPATIAL KEY `geometry` (`geometry`),
KEY `incident_id` (`incident_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores map geometries i.e polygons, lines etc' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `incident`
*
*/
CREATE TABLE IF NOT EXISTS `incident` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`location_id` bigint(20) unsigned NOT NULL,
`form_id` int(11) NOT NULL DEFAULT '1',
`locale` varchar(10) NOT NULL DEFAULT 'en_US',
`user_id` int(11) unsigned DEFAULT NULL,
`incident_title` varchar(255) DEFAULT NULL,
`incident_description` longtext,
`incident_date` datetime DEFAULT NULL,
`incident_mode` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1 - WEB, 2 - SMS, 3 - EMAIL, 4 - TWITTER',
`incident_active` tinyint(4) NOT NULL DEFAULT '0',
`incident_verified` tinyint(4) NOT NULL DEFAULT '0',
`incident_dateadd` datetime DEFAULT NULL,
`incident_dateadd_gmt` datetime DEFAULT NULL,
`incident_datemodify` datetime DEFAULT NULL,
`incident_alert_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0 - Not Tagged for Sending, 1 - Tagged for Sending, 2 - Alerts Have Been Sent',
`incident_zoom` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `location_id` (`location_id`),
KEY `incident_active` (`incident_active`),
KEY `incident_date` (`incident_date`),
KEY `form_id` (`form_id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores reports submitted' AUTO_INCREMENT=2 ;
--
-- Dumping data for table `incident`
--
LOCK TABLES `incident` WRITE;
/*!40000 ALTER TABLE `incident` DISABLE KEYS */;
INSERT INTO `incident` (`id`, `location_id`, `form_id`, `locale`, `user_id`, `incident_title`, `incident_description`, `incident_date`, `incident_mode`, `incident_active`, `incident_verified`, `incident_dateadd`, `incident_dateadd_gmt`, `incident_datemodify`, `incident_alert_status`, `incident_zoom`) VALUES
(1, 1, 1, 'en_US', 1, 'Hello Ushahidi!', 'Welcome to Ushahidi. Please replace this report with a valid incident', '2012-04-04 12:54:31', 1, 1, 1, NULL, NULL, NULL, 0, NULL);
/*!40000 ALTER TABLE `incident` ENABLE KEYS */;
UNLOCK TABLES;
-- --------------------------------------------------------
/**
* Table structure for table `incident_category`
*
*/
CREATE TABLE IF NOT EXISTS `incident_category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`incident_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`category_id` int(11) unsigned NOT NULL DEFAULT '5',
PRIMARY KEY (`id`),
UNIQUE KEY `incident_category_ids` (`incident_id`,`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores submitted reports categories' AUTO_INCREMENT=2 ;
--
-- Dumping data for table `incident_category`
--
LOCK TABLES `incident_category` WRITE;
/*!40000 ALTER TABLE `incident_category` DISABLE KEYS */;
INSERT INTO `incident_category` (`id`, `incident_id`, `category_id`) VALUES
(1, 1, 1);
/*!40000 ALTER TABLE `incident_category` ENABLE KEYS */;
UNLOCK TABLES;
-- --------------------------------------------------------
/**
* Table structure for table `incident_lang`
*
*/
CREATE TABLE IF NOT EXISTS `incident_lang` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`incident_id` bigint(20) unsigned NOT NULL,
`locale` varchar(10) DEFAULT NULL,
`incident_title` varchar(255) DEFAULT NULL,
`incident_description` longtext,
PRIMARY KEY (`id`),
KEY `incident_id` (`incident_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Holds translations for report titles and descriptions' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `incident_person`
*
*/
CREATE TABLE IF NOT EXISTS `incident_person` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`incident_id` bigint(20) unsigned DEFAULT NULL,
`person_first` varchar(200) DEFAULT NULL,
`person_last` varchar(200) DEFAULT NULL,
`person_email` varchar(120) DEFAULT NULL,
`person_phone` varchar(60) DEFAULT NULL,
`person_ip` varchar(50) DEFAULT NULL,
`person_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `incident_id` (`incident_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Holds information provided by people who submit reports' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `layer`
*
*/
CREATE TABLE IF NOT EXISTS `layer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`layer_name` varchar(255) DEFAULT NULL,
`layer_url` varchar(255) DEFAULT NULL,
`layer_file` varchar(100) DEFAULT NULL,
`layer_color` varchar(20) DEFAULT NULL,
`layer_visible` tinyint(4) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Holds static layer information' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `level`
*
*/
CREATE TABLE IF NOT EXISTS `level` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`level_title` varchar(200) DEFAULT NULL,
`level_description` varchar(200) DEFAULT NULL,
`level_weight` tinyint(4) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores level of trust assigned to reporters of the platform' AUTO_INCREMENT=6 ;
--
-- Dumping data for table `level`
--
INSERT INTO `level` (`id`, `level_title`, `level_description`, `level_weight`) VALUES
(1, 'SPAM + Delete', 'SPAM + Delete', -2),
(2, 'SPAM', 'SPAM', -1),
(3, 'Untrusted', 'Untrusted', 0),
(4, 'Trusted', 'Trusted', 1),
(5, 'Trusted + Verify', 'Trusted + Verify', 2);
-- --------------------------------------------------------
/**
* Table structure for table `location`
*
*/
CREATE TABLE IF NOT EXISTS `location` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`location_name` varchar(255) DEFAULT NULL,
`country_id` int(11) NOT NULL DEFAULT '0',
`latitude` double NOT NULL DEFAULT '0',
`longitude` double NOT NULL DEFAULT '0',
`location_visible` tinyint(4) NOT NULL DEFAULT '1',
`location_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `country_id` (`country_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores location information' AUTO_INCREMENT=2 ;
--
-- Dumping data for table `location`
--
LOCK TABLES `location` WRITE;
/*!40000 ALTER TABLE `location` DISABLE KEYS */;
INSERT INTO `location` (`id`, `location_name`, `country_id`, `latitude`, `longitude`, `location_visible`, `location_date`) VALUES
(1, 'Nairobi', 115, -1.28730007070501, 36.8214511820082, 1, '2009-06-30 00:00:00');
/*!40000 ALTER TABLE `location` ENABLE KEYS */;
UNLOCK TABLES;
-- --------------------------------------------------------
/**
* Table structure for table `maintenance`
*
*/
CREATE TABLE IF NOT EXISTS `maintenance` (
`allowed_ip` varchar(15) NOT NULL,
PRIMARY KEY (`allowed_ip`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Puts a site in maintenance mode if data exists in this table';
-- --------------------------------------------------------
/**
* Table structure for table `media`
*
*/
CREATE TABLE IF NOT EXISTS `media` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`location_id` bigint(20) unsigned DEFAULT NULL,
`incident_id` bigint(20) unsigned DEFAULT NULL,
`checkin_id` bigint(20) unsigned DEFAULT NULL,
`message_id` bigint(20) unsigned DEFAULT NULL,
`badge_id` int(11) DEFAULT NULL,
`media_type` tinyint(4) DEFAULT NULL COMMENT '1 - IMAGES, 2 - VIDEO, 3 - AUDIO, 4 - NEWS, 5 - PODCAST',
`media_title` varchar(255) DEFAULT NULL,
`media_description` longtext,
`media_link` varchar(255) DEFAULT NULL,
`media_medium` varchar(255) DEFAULT NULL,
`media_thumb` varchar(255) DEFAULT NULL,
`media_date` datetime DEFAULT NULL,
`media_active` tinyint(4) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `incident_id` (`incident_id`),
KEY `location_id` (`location_id`),
KEY `checkin_id` (`checkin_id`),
KEY `badge_id` (`badge_id`),
KEY `message_id` (`message_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores any media submitted along with a report/checkin' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `message`
*
*/
CREATE TABLE IF NOT EXISTS `message` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` bigint(20) DEFAULT '0',
`incident_id` bigint(20) unsigned DEFAULT '0',
`user_id` int(11) unsigned DEFAULT '0',
`reporter_id` bigint(20) unsigned DEFAULT NULL,
`service_messageid` varchar(100) DEFAULT NULL,
`message_from` varchar(100) DEFAULT NULL,
`message_to` varchar(100) DEFAULT NULL,
`message` text,
`message_detail` text,
`message_type` tinyint(4) DEFAULT '1' COMMENT '1 - INBOX, 2 - OUTBOX (From Admin), 3 - DELETED',
`message_date` datetime DEFAULT NULL,
`message_level` tinyint(4) DEFAULT '0' COMMENT '0 - UNREAD, 1 - READ, 99 - SPAM',
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `incident_id` (`incident_id`),
KEY `reporter_id` (`reporter_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores tweets, emails and SMS messages' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `openid`
*
*/
CREATE TABLE IF NOT EXISTS `openid` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) unsigned NOT NULL,
`openid` varchar(255) NOT NULL,
`openid_email` varchar(127) NOT NULL,
`openid_server` varchar(255) NOT NULL,
`openid_date` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `openid` (`openid`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores users’ openid information' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `page`
*
*/
CREATE TABLE IF NOT EXISTS `page` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`page_title` varchar(255) NOT NULL,
`page_description` longtext,
`page_tab` varchar(100) NOT NULL,
`page_active` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores user created pages' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `permissions`
*
*/
CREATE TABLE IF NOT EXISTS `permissions` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=16 COMMENT='Stores permissions used for access control';
/* Data for permissions table */
INSERT IGNORE INTO `permissions` VALUES
(1,'reports_view'),
(2,'reports_edit'),
(4,'reports_comments'),
(5,'reports_download'),
(6,'reports_upload'),
(7,'messages'),
(8,'messages_reporters'),
(9,'stats'),
(10,'settings'),
(11,'manage'),
(12,'users'),
(13,'manage_roles'),
(14,'checkin'),
(15,'checkin_admin'),
(16,'reports_verify'),
(17,'reports_approve'),
(18,'admin_ui'),
(19,'member_ui');
-- --------------------------------------------------------
/**
* Table structure for table `permissions_roles`
*
*/
CREATE TABLE IF NOT EXISTS `permissions_roles` (
`role_id` int(11) NOT NULL,
`permission_id` int(11) NOT NULL,
PRIMARY KEY (`role_id`,`permission_id`)
) ENGINE=MyISAM COMMENT='Stores permissions assigned to roles';
--
-- Dumping data for table `permissions_roles`
--
INSERT INTO `permissions_roles` VALUES
(1,14),
(2,1),(2,2),(2,4),(2,5),(2,6),(2,7),(2,8),(2,9),(2,10),(2,11),(2,12),(2,14),(2,15),(2,16),(2,17),(2,18),
(3,1),(3,2),(3,4),(3,5),(3,6),(3,7),(3,8),(3,9),(3,10),(3,11),(3,12),(3,13),(3,14),(3,15),(3,16),(3,17),(3,18),
(4,19);
-- ---------------------------------------------------------
/**
* Table structure for table `plugin`
*
*/
CREATE TABLE IF NOT EXISTS `plugin` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`plugin_name` varchar(100) NOT NULL,
`plugin_url` varchar(250) DEFAULT NULL,
`plugin_description` text,
`plugin_priority` tinyint(4) DEFAULT '0',
`plugin_active` tinyint(4) DEFAULT '0',
`plugin_installed` tinyint(4) DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `plugin_name` (`plugin_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Holds a list of all plugins installed on a deployment' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `private_message`
*
*/
CREATE TABLE IF NOT EXISTS `private_message` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) NOT NULL DEFAULT '0',
`user_id` int(11) unsigned NOT NULL,
`from_user_id` int(11) DEFAULT '0',
`private_subject` varchar(255) NOT NULL,
`private_message` text NOT NULL,
`private_message_date` datetime NOT NULL,
`private_message_new` tinyint(4) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores private messages sent between Members' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `rating`
*
*/
CREATE TABLE IF NOT EXISTS `rating` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) unsigned DEFAULT '0',
`incident_id` bigint(20) unsigned DEFAULT NULL,
`comment_id` bigint(20) unsigned DEFAULT NULL,
`rating` tinyint(4) DEFAULT '0',
`rating_ip` varchar(100) DEFAULT NULL,
`rating_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `incident_id` (`incident_id`),
KEY `comment_id` (`comment_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores credibility ratings for reports and comments' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `reporter`
*
*/
CREATE TABLE IF NOT EXISTS `reporter` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`location_id` bigint(20) unsigned DEFAULT NULL,
`user_id` int(11) unsigned DEFAULT NULL,
`service_id` int(10) unsigned DEFAULT NULL,
`level_id` int(11) unsigned DEFAULT NULL,
`service_account` varchar(255) DEFAULT NULL,
`reporter_first` varchar(200) DEFAULT NULL,
`reporter_last` varchar(200) DEFAULT NULL,
`reporter_email` varchar(120) DEFAULT NULL,
`reporter_phone` varchar(60) DEFAULT NULL,
`reporter_ip` varchar(50) DEFAULT NULL,
`reporter_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `location_id` (`location_id`),
KEY `service_id` (`service_id`),
KEY `level_id` (`level_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Information on report submitters via email, twitter and sms' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `roles`
*
*/
CREATE TABLE IF NOT EXISTS `roles` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`description` varchar(255) NOT NULL,
`access_level` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Defines user access levels and privileges on a deployment' AUTO_INCREMENT=5 ;
--
-- Dumping data for table `roles`
--
INSERT INTO `roles` (`id`, `name`, `description`, `access_level`) VALUES
(1, 'login', 'Login privileges, granted after account confirmation', 0),
(2, 'admin', 'Administrative user, has access to almost everything.', 90),
(3, 'superadmin', 'Super administrative user, has access to everything.', 100),
(4, 'member', 'Regular user with access only to the member area', 10);
-- --------------------------------------------------------
/**
* Table structure for table `roles_users`
*
*/
CREATE TABLE IF NOT EXISTS `roles_users` (
`user_id` int(11) unsigned NOT NULL,
`role_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`user_id`,`role_id`),
KEY `fk_role_id` (`role_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores roles assigned to users registered on a deployment';
--
-- Dumping data for table `roles_users`
--
INSERT INTO `roles_users` (`user_id`, `role_id`) VALUES
(1, 1),
(1, 2),
(1, 3);
-- --------------------------------------------------------
/**
* Table structure for table `scheduler`
*
*/
CREATE TABLE IF NOT EXISTS `scheduler` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`scheduler_name` varchar(100) NOT NULL,
`scheduler_last` int(10) unsigned NOT NULL DEFAULT '0',
`scheduler_weekday` smallint(6) NOT NULL DEFAULT '-1',
`scheduler_day` smallint(6) NOT NULL DEFAULT '-1',
`scheduler_hour` smallint(6) NOT NULL DEFAULT '-1',
`scheduler_minute` smallint(6) NOT NULL,
`scheduler_controller` varchar(100) NOT NULL,
`scheduler_active` tinyint(4) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores schedules for cron jobs' AUTO_INCREMENT=6 ;
--
-- Dumping data for table `scheduler`
--
INSERT INTO `scheduler` (`id`, `scheduler_name`, `scheduler_last`, `scheduler_weekday`, `scheduler_day`, `scheduler_hour`, `scheduler_minute`, `scheduler_controller`, `scheduler_active`) VALUES
(1, 'Feeds', 0, -1, -1, -1, 0, 's_feeds', 1),
(2, 'Alerts', 0, -1, -1, -1, -1, 's_alerts', 1),
(3, 'Email', 0, -1, -1, -1, 0, 's_email', 1),
(4, 'Twitter', 0, -1, -1, -1, 0, 's_twitter', 1),
(5, 'Cleanup', 0, -1, -1, -1, 0, 's_cleanup', 1);
-- --------------------------------------------------------
/**
* Table structure for table `scheduler_log`
*
*/
CREATE TABLE IF NOT EXISTS `scheduler_log` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`scheduler_id` int(10) unsigned NOT NULL,
`scheduler_status` varchar(20) DEFAULT NULL,
`scheduler_date` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `scheduler_id` (`scheduler_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores a log of scheduler actions' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `service`
*
*/
CREATE TABLE IF NOT EXISTS `service` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`service_name` varchar(100) DEFAULT NULL,
`service_description` varchar(255) DEFAULT NULL,
`service_url` varchar(255) DEFAULT NULL,
`service_api` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Info on input sources i.e SMS, Email, Twitter' AUTO_INCREMENT=4 ;
--
-- Dumping data for table `service`
--
INSERT INTO `service` (`id`, `service_name`, `service_description`, `service_url`, `service_api`) VALUES
(1, 'SMS', 'Text messages from phones', NULL, NULL),
(2, 'Email', 'Email messages sent to your deployment', NULL, NULL),
(3, 'Twitter', 'Tweets tweets tweets', 'http://twitter.com', NULL);
-- --------------------------------------------------------
/**
* Table structure for table `sessions`
*
*/
CREATE TABLE IF NOT EXISTS `sessions` (
`session_id` varchar(127) NOT NULL,
`last_activity` int(10) unsigned NOT NULL,
`data` text NOT NULL,
PRIMARY KEY (`session_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores session information';
-- --------------------------------------------------------
/**
* Table structure for table `settings`
*
*/
CREATE TABLE IF NOT EXISTS `settings` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`key` varchar(100) NOT NULL DEFAULT '' COMMENT 'Unique identifier for the configuration parameter',
`value` text COMMENT 'Value for the settings parameter',
PRIMARY KEY (`id`),
UNIQUE KEY `uq_settings_key` (`key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40000 ALTER TABLE `settings` DISABLE KEYS */;
INSERT INTO `settings` (`id`, `key`, `value`)
VALUES
(1,'site_name','Ushahidi'),
(2,'site_tagline',NULL),
(3,'site_banner_id',NULL),
(4,'site_email',NULL),
(5,'site_key',NULL),
(6,'site_language','en_US'),
(7,'site_style','default'),
(8,'site_timezone',NULL),
(9,'site_contact_page','1'),
(10,'site_help_page','1'),
(11,'site_message',NULL),
(12,'site_copyright_statement',NULL),
(13,'site_submit_report_message',NULL),
(14,'allow_reports','1'),
(15,'allow_comments','1'),
(16,'allow_feed','1'),
(17,'allow_stat_sharing','1'),
(18,'allow_clustering','1'),
(19,'cache_pages','0'),
(20,'cache_pages_lifetime','1800'),
(21,'private_deployment','0'),
(22,'default_map','osm_mapnik'),
(23,'default_map_all','CC0000'),
(24,'default_map_all_icon_id',NULL),
(25,'api_google','ABQIAAAAjsEM5UsvCPCIHp80spK1kBQnONNwnjgPbDSioH0X5rmWMjc4axQCaMN2CIvMUCsXGLs-5pQ8xAx5cw'),
(26,'api_live','Apumcka0uPOF2lKLorq8aeo4nuqfVVeNRqJjqOcLMJ9iMCTsnMsNd9_OvpA8gR0i'),
(27,'api_akismet',''),
(28,'default_country','115'),
(29,'multi_country','0'),
(30,'default_city','nairobi'),
(31,'default_lat','-1.2873000707050097'),
(32,'default_lon','36.821451182008204'),
(33,'default_zoom','13'),
(34,'items_per_page','5'),
(35,'items_per_page_admin','20'),
(36,'sms_provider',''),
(37,'sms_no1',NULL),
(38,'sms_no2',NULL),
(39,'sms_no3',NULL),
(40,'google_analytics',NULL),
(41,'twitter_hashtags',NULL),
(42,'blocks','news_block|reports_block'),
(43,'blocks_per_row','2'),
(44,'date_modify','2008-08-25 10:25:18'),
(45,'stat_id',NULL),
(46,'stat_key',NULL),
(47,'email_username',NULL),
(48,'email_password',NULL),
(49,'email_port',NULL),
(50,'email_host',NULL),
(51,'email_servertype',NULL),
(52,'email_ssl',NULL),
(53,'ftp_server',NULL),
(54,'ftp_user_name',NULL),
(55,'alerts_email',NULL),
(56,'checkins','0'),
(57,'facebook_appid',NULL),
(58,'facebook_appsecret',NULL),
(59,'db_version','96'),
(60,'ushahidi_version','2.5b'),
(61,'allow_alerts','1'),
(62,'require_email_confirmation','0'),
(63,'manually_approve_users','0'),
(64,'enable_timeline','0');
-- --------------------------------------------------------
/**
* Table structure for table `users`
*
*/
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`riverid` varchar(128) NOT NULL,
`name` varchar(200) DEFAULT NULL,
`email` varchar(127) NOT NULL,
`username` varchar(100) NOT NULL DEFAULT '',
`password` char(50) NOT NULL,
`logins` int(10) unsigned NOT NULL DEFAULT '0',
`last_login` int(10) unsigned DEFAULT NULL,
`notify` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Flag incase admin opts in for email notifications',
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`color` varchar(6) NOT NULL DEFAULT 'FF0000',
`code` varchar(30) DEFAULT NULL,
`confirmed` tinyint(1) NOT NULL DEFAULT '0',
`public_profile` tinyint(1) NOT NULL DEFAULT '1',
`approved` tinyint(1) NOT NULL DEFAULT '1',
`needinfo` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_username` (`username`),
UNIQUE KEY `uniq_email` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores registered users’ information' AUTO_INCREMENT=2 ;
--
-- Dumping data for table `users`
--
INSERT INTO `users` (`id`, `name`, `email`, `username`, `password`, `logins`, `last_login`, `updated`, `public_profile`, `confirmed`) VALUES
(1, 'Administrator', 'myemail@example.com', 'admin', 'bae4b17e9acbabf959654a4c496e577003e0b887c6f52803d7', 0, 1221420023, '2008-09-14 14:17:22', 0, 1);
-- --------------------------------------------------------
/**
* Table structure for table `user_devices`
* Ties mobile devices to users without logging in so that the ids on the devices make the distinction
*/
CREATE TABLE IF NOT EXISTS `user_devices` (
`id` varchar(255) NOT NULL,
`user_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Works with checkins';
-- --------------------------------------------------------
/**
* Table structure for table `user_tokens`
*
*/
CREATE TABLE IF NOT EXISTS `user_tokens` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) unsigned NOT NULL,
`user_agent` varchar(40) NOT NULL,
`token` varchar(64) NOT NULL,
`created` int(10) unsigned NOT NULL,
`expires` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_token` (`token`),
KEY `fk_user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores browser tokens assigned to users' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
/**
* Table structure for table `verified`
*
*/
CREATE TABLE IF NOT EXISTS `verified` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`incident_id` bigint(20) unsigned DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
`verified_date` datetime DEFAULT NULL,
`verified_status` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `incident_id` (`incident_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stores all verified reports' AUTO_INCREMENT=1 ;
/**
* Version information for table `settings`
*
*/
UPDATE `settings` SET `value` = '96' WHERE `key` = 'db_version';
UPDATE `settings` SET `value` = '2.5b' WHERE `key`= 'ushahidi_version';
Jump to Line
Something went wrong with that request. Please try again.