backup/dn42-pingfinder
1
Fork 0
mirror of https://git.dn42.dev/dn42/pingfinder.git synced 2025-07-08 14:04:30 -07:00
Code Issues Actions Packages Projects Releases Wiki Activity
Files
master
dn42-pingfinder/peers.sql
Jon Lundy 956fda89c0 add sql dump
2016-09-08 13:01:29 +00:00

358 lines
16 KiB
SQL
Raw Permalink Blame History

-- MySQL dump 10.15 Distrib 10.0.26-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: localhost Database: peers
-- ------------------------------------------------------
-- Server version 10.0.26-MariaDB-1~jessie
/*!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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Temporary table structure for view `last_pings`
--
DROP TABLE IF EXISTS `last_pings`;
/*!50001 DROP VIEW IF EXISTS `last_pings`*/;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `last_pings` (
`peer_id` tinyint NOT NULL,
`last_ping` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;
--
-- Temporary table structure for view `peer_status`
--
DROP TABLE IF EXISTS `peer_status`;
/*!50001 DROP VIEW IF EXISTS `peer_status`*/;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `peer_status` (
`peer_nick` tinyint NOT NULL,
`peer_name` tinyint NOT NULL,
`peer_country` tinyint NOT NULL,
`peer_scriptver` tinyint NOT NULL,
`last_ping` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;
--
-- Table structure for table `peers`
--
DROP TABLE IF EXISTS `peers`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `peers` (
`peer_id` char(36) CHARACTER SET utf8 NOT NULL,
`peer_name` varchar(64) CHARACTER SET utf8 NOT NULL,
`peer_note` varchar(128) CHARACTER SET utf8 NOT NULL,
`peer_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`peer_pin` varchar(64) CHARACTER SET utf8 NOT NULL,
`peer_active` tinyint(4) NOT NULL DEFAULT '0',
`peer_country` varchar(3) COLLATE utf8_bin DEFAULT 'XD',
`peer_family` tinyint(4) DEFAULT NULL,
`peer_type` set('openvpn','gre/plain','gre/ipsec','fastd','tinc','zerotier','pptp','l2tp','other','wireguard') COLLATE utf8_bin DEFAULT 'openvpn',
`peer_nick` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`peer_scriptver` varchar(32) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`peer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_ALL_TABLES' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `peers`.`peers_BEFORE_INSERT` BEFORE INSERT ON `peers` FOR EACH ROW
BEGIN
SET NEW.peer_created = current_timestamp;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `requests`
--
DROP TABLE IF EXISTS `requests`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `requests` (
`req_id` char(36) COLLATE utf8_bin NOT NULL,
`req_ip` varchar(39) CHARACTER SET utf8 NOT NULL,
`req_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`req_hidden` tinyint(4) NOT NULL DEFAULT '0',
`req_family` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`req_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_ALL_TABLES' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `peers`.`requests_BEFORE_INSERT` BEFORE INSERT ON `requests` FOR EACH ROW
BEGIN
SET NEW.req_created = current_timestamp;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Temporary table structure for view `requests_completed`
--
DROP TABLE IF EXISTS `requests_completed`;
/*!50001 DROP VIEW IF EXISTS `requests_completed`*/;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `requests_completed` (
`peer_id` tinyint NOT NULL,
`req_id` tinyint NOT NULL,
`peer_name` tinyint NOT NULL,
`peer_note` tinyint NOT NULL,
`req_ip` tinyint NOT NULL,
`res_latency` tinyint NOT NULL,
`req_created` tinyint NOT NULL,
`peer_country` tinyint NOT NULL,
`peer_family` tinyint NOT NULL,
`peer_nick` tinyint NOT NULL,
`peer_type` tinyint NOT NULL,
`res_created` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;
--
-- Temporary table structure for view `requests_pending`
--
DROP TABLE IF EXISTS `requests_pending`;
/*!50001 DROP VIEW IF EXISTS `requests_pending`*/;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `requests_pending` (
`peer_id` tinyint NOT NULL,
`req_id` tinyint NOT NULL,
`req_ip` tinyint NOT NULL,
`req_family` tinyint NOT NULL,
`req_created` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;
--
-- Temporary table structure for view `requests_view`
--
DROP TABLE IF EXISTS `requests_view`;
/*!50001 DROP VIEW IF EXISTS `requests_view`*/;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `requests_view` (
`req_id` tinyint NOT NULL,
`req_ip` tinyint NOT NULL,
`req_created` tinyint NOT NULL,
`req_hidden` tinyint NOT NULL,
`req_family` tinyint NOT NULL,
`req_count` tinyint NOT NULL,
`req_success` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;
--
-- Temporary table structure for view `result_count`
--
DROP TABLE IF EXISTS `result_count`;
/*!50001 DROP VIEW IF EXISTS `result_count`*/;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `result_count` (
`req_id` tinyint NOT NULL,
`req_count` tinyint NOT NULL,
`req_success` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;
--
-- Table structure for table `results`
--
DROP TABLE IF EXISTS `results`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `results` (
`peer_id` char(36) CHARACTER SET utf8 NOT NULL,
`req_id` char(36) CHARACTER SET utf8 NOT NULL,
`res_latency` float DEFAULT NULL,
`res_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`peer_id`,`req_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_ALL_TABLES' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `peers`.`results_BEFORE_INSERT` BEFORE INSERT ON `results` FOR EACH ROW
BEGIN
SET NEW.res_created = current_timestamp;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Final view structure for view `last_pings`
--
/*!50001 DROP TABLE IF EXISTS `last_pings`*/;
/*!50001 DROP VIEW IF EXISTS `last_pings`*/;
/*!50001 SET @saved_cs_client = @@character_set_client */;
/*!50001 SET @saved_cs_results = @@character_set_results */;
/*!50001 SET @saved_col_connection = @@collation_connection */;
/*!50001 SET character_set_client = utf8 */;
/*!50001 SET character_set_results = utf8 */;
/*!50001 SET collation_connection = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `last_pings` AS select `results`.`peer_id` AS `peer_id`,max(`results`.`res_created`) AS `last_ping` from `results` group by `results`.`peer_id` */;
/*!50001 SET character_set_client = @saved_cs_client */;
/*!50001 SET character_set_results = @saved_cs_results */;
/*!50001 SET collation_connection = @saved_col_connection */;
--
-- Final view structure for view `peer_status`
--
/*!50001 DROP TABLE IF EXISTS `peer_status`*/;
/*!50001 DROP VIEW IF EXISTS `peer_status`*/;
/*!50001 SET @saved_cs_client = @@character_set_client */;
/*!50001 SET @saved_cs_results = @@character_set_results */;
/*!50001 SET @saved_col_connection = @@collation_connection */;
/*!50001 SET character_set_client = utf8 */;
/*!50001 SET character_set_results = utf8 */;
/*!50001 SET collation_connection = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `peer_status` AS select `peers`.`peer_nick` AS `peer_nick`,`peers`.`peer_name` AS `peer_name`,`peers`.`peer_country` AS `peer_country`,`peers`.`peer_scriptver` AS `peer_scriptver`,`p`.`last_ping` AS `last_ping` from (`peers` left join `last_pings` `p` on((`peers`.`peer_id` = `p`.`peer_id`))) order by `peers`.`peer_nick`,`peers`.`peer_name` */;
/*!50001 SET character_set_client = @saved_cs_client */;
/*!50001 SET character_set_results = @saved_cs_results */;
/*!50001 SET collation_connection = @saved_col_connection */;
--
-- Final view structure for view `requests_completed`
--
/*!50001 DROP TABLE IF EXISTS `requests_completed`*/;
/*!50001 DROP VIEW IF EXISTS `requests_completed`*/;
/*!50001 SET @saved_cs_client = @@character_set_client */;
/*!50001 SET @saved_cs_results = @@character_set_results */;
/*!50001 SET @saved_col_connection = @@collation_connection */;
/*!50001 SET character_set_client = utf8 */;
/*!50001 SET character_set_results = utf8 */;
/*!50001 SET collation_connection = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `requests_completed` AS select `results`.`peer_id` AS `peer_id`,`results`.`req_id` AS `req_id`,`peers`.`peer_name` AS `peer_name`,`peers`.`peer_note` AS `peer_note`,`requests`.`req_ip` AS `req_ip`,`results`.`res_latency` AS `res_latency`,`requests`.`req_created` AS `req_created`,`peers`.`peer_country` AS `peer_country`,`peers`.`peer_family` AS `peer_family`,`peers`.`peer_nick` AS `peer_nick`,`peers`.`peer_type` AS `peer_type`,`results`.`res_created` AS `res_created` from ((`results` join `requests` on((`results`.`req_id` = `requests`.`req_id`))) join `peers` on((`results`.`peer_id` = `peers`.`peer_id`))) order by isnull(`results`.`res_latency`),`results`.`res_latency` */;
/*!50001 SET character_set_client = @saved_cs_client */;
/*!50001 SET character_set_results = @saved_cs_results */;
/*!50001 SET collation_connection = @saved_col_connection */;
--
-- Final view structure for view `requests_pending`
--
/*!50001 DROP TABLE IF EXISTS `requests_pending`*/;
/*!50001 DROP VIEW IF EXISTS `requests_pending`*/;
/*!50001 SET @saved_cs_client = @@character_set_client */;
/*!50001 SET @saved_cs_results = @@character_set_results */;
/*!50001 SET @saved_col_connection = @@collation_connection */;
/*!50001 SET character_set_client = utf8 */;
/*!50001 SET character_set_results = utf8 */;
/*!50001 SET collation_connection = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `requests_pending` AS select `peers`.`peer_id` AS `peer_id`,`requests`.`req_id` AS `req_id`,`requests`.`req_ip` AS `req_ip`,`requests`.`req_family` AS `req_family`,`requests`.`req_created` AS `req_created` from ((`requests` join `peers`) left join `results` on(((`requests`.`req_id` = `results`.`req_id`) and (`peers`.`peer_id` = `results`.`peer_id`)))) where (isnull(`results`.`peer_id`) and (`peers`.`peer_active` = 1) and (`requests`.`req_created` > (now() - interval 4 hour)) and ((`peers`.`peer_family` & `requests`.`req_family`) > 0)) order by `requests`.`req_created` */;
/*!50001 SET character_set_client = @saved_cs_client */;
/*!50001 SET character_set_results = @saved_cs_results */;
/*!50001 SET collation_connection = @saved_col_connection */;
--
-- Final view structure for view `requests_view`
--
/*!50001 DROP TABLE IF EXISTS `requests_view`*/;
/*!50001 DROP VIEW IF EXISTS `requests_view`*/;
/*!50001 SET @saved_cs_client = @@character_set_client */;
/*!50001 SET @saved_cs_results = @@character_set_results */;
/*!50001 SET @saved_col_connection = @@collation_connection */;
/*!50001 SET character_set_client = utf8 */;
/*!50001 SET character_set_results = utf8 */;
/*!50001 SET collation_connection = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `requests_view` AS select `requests`.`req_id` AS `req_id`,`requests`.`req_ip` AS `req_ip`,`requests`.`req_created` AS `req_created`,`requests`.`req_hidden` AS `req_hidden`,`requests`.`req_family` AS `req_family`,`result_count`.`req_count` AS `req_count`,`result_count`.`req_success` AS `req_success` from (`requests` join `result_count` on((`requests`.`req_id` = `result_count`.`req_id`))) */;
/*!50001 SET character_set_client = @saved_cs_client */;
/*!50001 SET character_set_results = @saved_cs_results */;
/*!50001 SET collation_connection = @saved_col_connection */;
--
-- Final view structure for view `result_count`
--
/*!50001 DROP TABLE IF EXISTS `result_count`*/;
/*!50001 DROP VIEW IF EXISTS `result_count`*/;
/*!50001 SET @saved_cs_client = @@character_set_client */;
/*!50001 SET @saved_cs_results = @@character_set_results */;
/*!50001 SET @saved_col_connection = @@collation_connection */;
/*!50001 SET character_set_client = utf8 */;
/*!50001 SET character_set_results = utf8 */;
/*!50001 SET collation_connection = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `result_count` AS select `results`.`req_id` AS `req_id`,count(0) AS `req_count`,count(`results`.`res_latency`) AS `req_success` from `results` group by `results`.`req_id` */;
/*!50001 SET character_set_client = @saved_cs_client */;
/*!50001 SET character_set_results = @saved_cs_results */;
/*!50001 SET collation_connection = @saved_col_connection */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!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 */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2016-09-08 12:59:44
Reference in New Issue View Git Blame Copy Permalink