-- phpMyAdmin SQL Dump
-- version 4.3.11.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 13, 2015 at 10:24 AM
-- Server version: 10.0.17-MariaDB-1~wheezy-log
-- PHP Version: 5.4.36-0+deb7u3

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `test_db`
--
CREATE DATABASE IF NOT EXISTS `test_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `test_db`;

-- --------------------------------------------------------

--
-- Stand-in structure for view `city_join`
--
CREATE TABLE IF NOT EXISTS `city_join` (
`network_poly` geometry
,`latitude` decimal(7,4)
,`longitude` decimal(7,4)
,`country_name` varchar(75)
,`subdivision_1_name` varchar(75)
,`city_name` varchar(75)
);

-- --------------------------------------------------------

--
-- Table structure for table `geolite2_city_blocks`
--

CREATE TABLE IF NOT EXISTS `geolite2_city_blocks` (
  `id` int(10) unsigned NOT NULL,
  `network_start_integer` int(10) unsigned NOT NULL,
  `network_last_integer` int(10) unsigned NOT NULL,
  `network_poly` geometry NOT NULL,
  `geoname_id` int(8) NOT NULL DEFAULT '0',
  `registered_country_geoname_id` int(8) DEFAULT NULL,
  `represented_country_geoname_id` varchar(10) DEFAULT NULL,
  `is_anonymous_proxy` tinyint(1) DEFAULT NULL,
  `is_satellite_provider` tinyint(1) DEFAULT NULL,
  `postal_code` varchar(10) DEFAULT NULL,
  `latitude` decimal(7,4) DEFAULT NULL,
  `longitude` decimal(7,4) DEFAULT NULL
) ENGINE=MyISAM AUTO_INCREMENT=2926783 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `geolite2_city_blocks`
--

INSERT INTO `geolite2_city_blocks` (`id`, `network_start_integer`, `network_last_integer`, `network_poly`, `geoname_id`, `registered_country_geoname_id`, `represented_country_geoname_id`, `is_anonymous_proxy`, `is_satellite_provider`, `postal_code`, `latitude`, `longitude`) VALUES
/*
(406199, 1034825728, 1034827775, '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0��A\0\0\0\0\0\0�\0\0����A\0\0\0\0\0\0�\0\0����A\0\0\0\0\0\0�?\0\0\0\0��A\0\0\0\0\0\0�?\0\0\0\0��A\0\0\0\0\0\0�', 1806535, 1814991, '', 0, 0, '', '30.8703', '120.0933'),
(1874899, 1946812416, 1946943487, '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0��A\0\0\0\0\0\0�\0\0����A\0\0\0\0\0\0�\0\0����A\0\0\0\0\0\0�?\0\0\0\0��A\0\0\0\0\0\0�?\0\0\0\0��A\0\0\0\0\0\0�', 1799869, 1814991, '', 0, 0, '', '22.8167', '108.3167'),
(2298157, 3079198720, 3079199743, '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0��A\0\0\0\0\0\0�\0\0���A\0\0\0\0\0\0�\0\0���A\0\0\0\0\0\0�?\0\0\0\0��A\0\0\0\0\0\0�?\0\0\0\0��A\0\0\0\0\0\0�', 1981295, 1814991, '', 0, 0, '', '26.9689', '109.7725'),
(2926782, 3657900032, 3657904127, '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0�@�A\0\0\0\0\0\0�\0\0���@�A\0\0\0\0\0\0�\0\0���@�A\0\0\0\0\0\0�?\0\0\0\0�@�A\0\0\0\0\0\0�?\0\0\0\0�@�A\0\0\0\0\0\0�', 2037013, 1814991, '', 0, 0, '', '45.7500', '126.6500');

Rebuild the network polygons from the start and end ip values since they will not restore properly from the ascii dump */
(406199, 1034825728, 1034827775, GEOMFROMWKB(POLYGON(LINESTRING(POINT(1034825728, -1),POINT(1034827775,  -1),POINT(1034827775,   1),POINT(1034825728,  1),POINT(1034825728, -1)))), 1806535, 1814991, '', 0, 0, '', '30.8703', '120.0933'),
(1874899, 1946812416, 1946943487, GEOMFROMWKB(POLYGON(LINESTRING(POINT(1946812416, -1),POINT(1946943487,  -1),POINT(1946943487,   1),POINT(1946812416,  1),POINT(1946812416, -1)))), 1799869, 1814991, '', 0, 0, '', '22.8167', '108.3167'),
(2298157, 3079198720, 3079199743, GEOMFROMWKB(POLYGON(LINESTRING(POINT(3079198720, -1),POINT(3079199743,  -1),POINT(3079199743,   1),POINT(3079198720,  1),POINT(3079198720, -1)))), 1981295, 1814991, '', 0, 0, '', '26.9689', '109.7725'),
(2926782, 3657900032, 3657904127, GEOMFROMWKB(POLYGON(LINESTRING(POINT(3657900032, -1),POINT(3657904127,  -1),POINT(3657904127,   1),POINT(3657900032,  1),POINT(3657900032, -1)))), 2037013, 1814991, '', 0, 0, '', '45.7500', '126.6500');

-- --------------------------------------------------------

--
-- Table structure for table `geolite2_city_locations`
--

CREATE TABLE IF NOT EXISTS `geolite2_city_locations` (
  `id` int(10) unsigned NOT NULL,
  `geoname_id` int(8) NOT NULL DEFAULT '0',
  `locale_code` varchar(2) DEFAULT NULL,
  `continent_code` varchar(2) DEFAULT NULL,
  `continent_name` varchar(25) DEFAULT NULL,
  `country_iso_code` varchar(2) DEFAULT NULL,
  `country_name` varchar(75) DEFAULT NULL,
  `subdivision_1_iso_code` varchar(3) DEFAULT NULL,
  `subdivision_1_name` varchar(75) DEFAULT NULL,
  `subdivision_2_iso_code` varchar(3) DEFAULT NULL,
  `subdivision_2_name` varchar(75) DEFAULT NULL,
  `city_name` varchar(75) DEFAULT NULL,
  `metro_code` varchar(3) DEFAULT NULL,
  `time_zone` varchar(30) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=10512 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `geolite2_city_locations`
--

INSERT INTO `geolite2_city_locations` (`id`, `geoname_id`, `locale_code`, `continent_code`, `continent_name`, `country_iso_code`, `country_name`, `subdivision_1_iso_code`, `subdivision_1_name`, `subdivision_2_iso_code`, `subdivision_2_name`, `city_name`, `metro_code`, `time_zone`) VALUES
(8181, 1799869, 'en', 'AS', 'Asia', 'CN', 'China', '45', 'Guangxi Zhuangzu Zizhiqu', '', '', 'Nanning', '', 'Asia/Shanghai'),
(8371, 1806535, 'en', 'AS', 'Asia', 'CN', 'China', '33', 'Zhejiang Sheng', '', '', 'Huzhou', '', 'Asia/Shanghai'),
(10148, 1981295, 'en', 'AS', 'Asia', 'CN', 'China', '43', 'Hunan', '', '', 'Zhongxin', '', 'Asia/Shanghai'),
(10511, 2037013, 'en', 'AS', 'Asia', 'CN', 'China', '23', 'Heilongjiang Sheng', '', '', 'Harbin', '', 'Asia/Shanghai');

-- --------------------------------------------------------

--
-- Stand-in structure for view `ulog_city_join`
--
/*CREATE TABLE IF NOT EXISTS `ulog_city_join` (
);*/

-- --------------------------------------------------------

--
-- Table structure for table `ulog`
--

CREATE TABLE IF NOT EXISTS `ulog` (
  `id` int(10) unsigned NOT NULL,
  `oob_time_sec` int(10) unsigned NOT NULL DEFAULT '0',
  `oob_prefix` varchar(32) DEFAULT NULL,
  `ip_saddr` int(10) unsigned NOT NULL DEFAULT '0',
  `tcp_dport` smallint(5) unsigned DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `ulog`
--

INSERT INTO `ulog` (`id`, `oob_time_sec`, `oob_prefix`, `ip_saddr`, `tcp_dport`) VALUES
(1, 1403732309, '* China Dropped *', 1946861523, 22),
(2, 1403732536, '* China Dropped *', 1034826720, 22),
(3, 1403733049, '* China Dropped *', 1946861520, 22),
(4, 1403735381, '* China Dropped *', 1034826464, 22),
(5, 1403735990, '* China Dropped *', 1946861475, 22),
(6, 1403736566, '* China Dropped *', 1946861518, 22),
(7, 1403738955, '* China Dropped *', 3657901506, 22),
(8, 1403740072, '* China Dropped *', 1034826715, 22),
(9, 1403740097, '* China Dropped *', 1946861480, 22),
(10, 1403742008, '* China Dropped *', 3079198814, 22);

-- --------------------------------------------------------

--
-- Structure for view `city_join`
--
DROP TABLE IF EXISTS `city_join`;

CREATE SQL SECURITY DEFINER VIEW `city_join` AS select `geolite2_city_blocks`.`network_poly` AS `network_poly`,`geolite2_city_blocks`.`latitude` AS `latitude`,`geolite2_city_blocks`.`longitude` AS `longitude`,`geolite2_city_locations`.`country_name` AS `country_name`,`geolite2_city_locations`.`subdivision_1_name` AS `subdivision_1_name`,`geolite2_city_locations`.`city_name` AS `city_name` from (`geolite2_city_blocks` left join `geolite2_city_locations` on((`geolite2_city_locations`.`geoname_id` = `geolite2_city_blocks`.`geoname_id`)));

-- --------------------------------------------------------

--
-- Structure for view `ulog_city_join`
--
DROP TABLE IF EXISTS `ulog_city_join`;

CREATE SQL SECURITY DEFINER VIEW `ulog_city_join` AS select `ulog`.`oob_time_sec` AS `oob_time_sec`,`ulog`.`oob_prefix` AS `oob_prefix`,`ulog`.`ip_saddr` AS `ip_saddr`,`ulog`.`tcp_dport` AS `tcp_dport`,`city_join`.`latitude` AS `latitude`,`city_join`.`longitude` AS `longitude`,`city_join`.`country_name` AS `country_name`,`city_join`.`subdivision_1_name` AS `subdivision_1_name`,`city_join`.`city_name` AS `city_name` from (`ulog` join `city_join` on(mbrcontains(`city_join`.`network_poly`,geometryfromwkb(point(`ulog`.`ip_saddr`,0)))));

--
-- Indexes for dumped tables
--

--
-- Indexes for table `geolite2_city_blocks`
--
ALTER TABLE `geolite2_city_blocks`
  ADD PRIMARY KEY (`id`), ADD SPATIAL KEY `network_poly` (`network_poly`), ADD KEY `geoname_id` (`geoname_id`);

--
-- Indexes for table `geolite2_city_locations`
--
ALTER TABLE `geolite2_city_locations`
  ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `geoname_id` (`geoname_id`);

--
-- Indexes for table `ulog`
--
ALTER TABLE `ulog`
  ADD PRIMARY KEY (`id`), ADD KEY `ip_saddr` (`ip_saddr`), ADD KEY `oob_prefix` (`oob_prefix`(7)), ADD KEY `oob_time_sec` (`oob_time_sec`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `geolite2_city_blocks`
--
ALTER TABLE `geolite2_city_blocks`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2926783;
--
-- AUTO_INCREMENT for table `geolite2_city_locations`
--
ALTER TABLE `geolite2_city_locations`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=10512;
--
-- AUTO_INCREMENT for table `ulog`
--
ALTER TABLE `ulog`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=11;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
