db.sql

CREATE TABLE `client_info` (
  `source_ip` varchar(15) NOT NULL,
  `source_port` int(11) NOT NULL,
  `ttl_value` int(11) NOT NULL,
  `updated_time` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `provider_info` (
  `id` int(11) UNSIGNED NOT NULL,
  `ip_block` varchar(32) DEFAULT NULL,
  `from_ip` varchar(15) GENERATED ALWAYS AS (inet_ntoa(inet_aton(substring_index(`ip_block`,'/',1)) & 0xffffffff ^ ((0x01 << 32 - substring_index(`ip_block`,'/',-1)) - 1))) STORED,
  `to_ip` varchar(15) GENERATED ALWAYS AS (inet_ntoa(inet_aton(substring_index(`ip_block`,'/',1)) | (0x0100000000 >> substring_index(`ip_block`,'/',-1)) - 1)) STORED,
  `conn_type` varchar(32) DEFAULT NULL,
  `isp_name` varchar(32) DEFAULT NULL,
  `ttl_value` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for table `provider_info`
--
ALTER TABLE `provider_info`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `provider_info`
--
ALTER TABLE `provider_info`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;
--