API tools faq
paste
Advertisement
Sweetening

gpstracker database dump

Sweetening
Mar 1st, 2024
103
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 11.68 KB | None | 0 0
raw download clone embed print report
  1. root@localhost:/var/www/gps-tracker/servers/php/mysql# cat gpstracker-09-14-14.sql
  2. -- MySQL dump 10.13 Distrib 5.5.38, for debian-linux-gnu (i686)
  3. --
  4. -- Host: localhost Database: gpstracker
  5. -- ------------------------------------------------------
  6. -- Server version 5.5.38-0ubuntu0.12.04.1
  7.  
  8. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  9. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  10. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  11. /*!40101 SET NAMES utf8 */;
  12. /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
  13. /*!40103 SET TIME_ZONE='+00:00' */;
  14. /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
  15. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
  16. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
  17. /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
  18.  
  19. --
  20. -- Table structure for table `gpslocations`
  21. --
  22.  
  23. DROP TABLE IF EXISTS `gpslocations`;
  24. /*!40101 SET @saved_cs_client = @@character_set_client */;
  25. /*!40101 SET character_set_client = utf8 */;
  26. CREATE TABLE `gpslocations` (
  27. `GPSLocationID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  28. `lastUpdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  29. `latitude` decimal(10,7) NOT NULL DEFAULT '0.0000000',
  30. `longitude` decimal(10,7) NOT NULL DEFAULT '0.0000000',
  31. `phoneNumber` varchar(50) NOT NULL DEFAULT '',
  32. `userName` varchar(50) NOT NULL DEFAULT '',
  33. `sessionID` varchar(50) NOT NULL DEFAULT '',
  34. `speed` int(10) unsigned NOT NULL DEFAULT '0',
  35. `direction` int(10) unsigned NOT NULL DEFAULT '0',
  36. `distance` decimal(10,1) NOT NULL DEFAULT '0.0',
  37. `gpsTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  38. `locationMethod` varchar(50) NOT NULL DEFAULT '',
  39. `accuracy` int(10) unsigned NOT NULL DEFAULT '0',
  40. `extraInfo` varchar(255) NOT NULL DEFAULT '',
  41. `eventType` varchar(50) NOT NULL DEFAULT '',
  42. PRIMARY KEY (`GPSLocationID`),
  43. KEY `sessionIDIndex` (`sessionID`),
  44. KEY `phoneNumberIndex` (`phoneNumber`),
  45. KEY `userNameIndex` (`userName`)
  46. ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
  47. /*!40101 SET character_set_client = @saved_cs_client */;
  48.  
  49. --
  50. -- Dumping data for table `gpslocations`
  51. --
  52.  
  53. LOCK TABLES `gpslocations` WRITE;
  54. /*!40000 ALTER TABLE `gpslocations` DISABLE KEYS */;
  55. INSERT INTO `gpslocations` VALUES (1,'2007-01-03 19:37:00',47.627327,-122.325691,'gpsTracker3','gpsTracker3','8BA21D90-3F90-407F-BAAE-800B04B1F5EB',0,0,0.0,'2007-01-03 19:37:00','na',137,'na','gpsTracker'),(2,'2007-01-03 19:38:00',47.607258,-122.330077,'gpsTracker3','gpsTracker3','8BA21D90-3F90-407F-BAAE-800B04B1F5EB',0,0,0.0,'2007-01-03 19:38:00','na',137,'na','gpsTracker'),(3,'2007-01-03 19:39:00',47.601703,-122.324670,'gpsTracker3','gpsTracker3','8BA21D90-3F90-407F-BAAE-800B04B1F5EB',0,0,0.0,'2007-01-03 19:39:00','na',137,'na','gpsTracker'),(4,'0000-00-00 00:00:00',47.593757,-122.195074,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:40:00','na',137,'na','gpsTracker'),(5,'2007-01-03 19:41:00',47.601397,-122.190353,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:41:00','na',137,'na','gpsTracker'),(6,'2007-01-03 19:42:00',47.610020,-122.190697,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:42:00','na',137,'na','gpsTracker'),(7,'2007-01-03 19:43:00',47.636631,-122.214558,'gpsTracker1','gpsTracker1','8BA21D90-3F90-407F-BAAE-800B04B1F5ED',0,0,0.0,'2007-01-03 19:43:00','na',137,'na','gpsTracker'),(8,'2007-01-03 19:44:00',47.637961,-122.201769,'gpsTracker1','gpsTracker1','8BA21D90-3F90-407F-BAAE-800B04B1F5ED',0,0,0.0,'2007-01-03 19:44:00','na',137,'na','gpsTracker'),(9,'2007-01-03 19:45:00',47.642935,-122.209579,'gpsTracker1','gpsTracker1','8BA21D90-3F90-407F-BAAE-800B04B1F5ED',0,0,0.0,'2007-01-03 19:45:00','na',137,'na','gpsTracker');
  56. /*!40000 ALTER TABLE `gpslocations` ENABLE KEYS */;
  57. UNLOCK TABLES;
  58.  
  59. --
  60. -- Dumping routines for database 'gpstracker'
  61. --
  62. /*!50003 DROP PROCEDURE IF EXISTS `prcDeleteRoute` */;
  63. /*!50003 SET @saved_cs_client = @@character_set_client */ ;
  64. /*!50003 SET @saved_cs_results = @@character_set_results */ ;
  65. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  66. /*!50003 SET character_set_client = utf8 */ ;
  67. /*!50003 SET character_set_results = utf8 */ ;
  68. /*!50003 SET collation_connection = utf8_general_ci */ ;
  69. /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
  70. /*!50003 SET sql_mode = '' */ ;
  71. DELIMITER ;;
  72. CREATE DEFINER=`root`@`localhost` PROCEDURE `prcDeleteRoute`(
  73. _sessionID VARCHAR(50))
  74. BEGIN
  75. DELETE FROM gpslocations
  76. WHERE sessionID = _sessionID;
  77. END ;;
  78. DELIMITER ;
  79. /*!50003 SET sql_mode = @saved_sql_mode */ ;
  80. /*!50003 SET character_set_client = @saved_cs_client */ ;
  81. /*!50003 SET character_set_results = @saved_cs_results */ ;
  82. /*!50003 SET collation_connection = @saved_col_connection */ ;
  83. /*!50003 DROP PROCEDURE IF EXISTS `prcGetAllRoutesForMap` */;
  84. /*!50003 SET @saved_cs_client = @@character_set_client */ ;
  85. /*!50003 SET @saved_cs_results = @@character_set_results */ ;
  86. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  87. /*!50003 SET character_set_client = utf8 */ ;
  88. /*!50003 SET character_set_results = utf8 */ ;
  89. /*!50003 SET collation_connection = utf8_general_ci */ ;
  90. /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
  91. /*!50003 SET sql_mode = '' */ ;
  92. DELIMITER ;;
  93. CREATE DEFINER=`root`@`localhost` PROCEDURE `prcGetAllRoutesForMap`()
  94. BEGIN
  95. SELECT sessionId, gpsTime, CONCAT('{ "latitude":"', CAST(latitude AS CHAR),'", "longitude":"', CAST(longitude AS CHAR), '", "speed":"', CAST(speed AS CHAR), '", "direction":"', CAST(direction AS CHAR), '", "distance":"', CAST(distance AS CHAR), '", "locationMethod":"', locationMethod, '", "gpsTime":"', DATE_FORMAT(gpsTime, '%b %e %Y %h:%i%p'), '", "userName":"', userName, '", "phoneNumber":"', phoneNumber, '", "sessionID":"', CAST(sessionID AS CHAR), '", "accuracy":"', CAST(accuracy AS CHAR), '", "extraInfo":"', extraInfo, '" }') json
  96. FROM (SELECT MAX(GPSLocationID) ID
  97. FROM gpslocations
  98. WHERE sessionID != '0' && CHAR_LENGTH(sessionID) != 0 && gpstime != '0000-00-00 00:00:00'
  99. GROUP BY sessionID) AS MaxID
  100. JOIN gpslocations ON gpslocations.GPSLocationID = MaxID.ID
  101. ORDER BY gpsTime;
  102. END ;;
  103. DELIMITER ;
  104. /*!50003 SET sql_mode = @saved_sql_mode */ ;
  105. /*!50003 SET character_set_client = @saved_cs_client */ ;
  106. /*!50003 SET character_set_results = @saved_cs_results */ ;
  107. /*!50003 SET collation_connection = @saved_col_connection */ ;
  108. /*!50003 DROP PROCEDURE IF EXISTS `prcGetRouteForMap` */;
  109. /*!50003 SET @saved_cs_client = @@character_set_client */ ;
  110. /*!50003 SET @saved_cs_results = @@character_set_results */ ;
  111. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  112. /*!50003 SET character_set_client = utf8 */ ;
  113. /*!50003 SET character_set_results = utf8 */ ;
  114. /*!50003 SET collation_connection = utf8_general_ci */ ;
  115. /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
  116. /*!50003 SET sql_mode = '' */ ;
  117. DELIMITER ;;
  118. CREATE DEFINER=`root`@`localhost` PROCEDURE `prcGetRouteForMap`(
  119. _sessionID VARCHAR(50))
  120. BEGIN
  121. SELECT CONCAT('{ "latitude":"', CAST(latitude AS CHAR),'", "longitude":"', CAST(longitude AS CHAR), '", "speed":"', CAST(speed AS CHAR), '", "direction":"', CAST(direction AS CHAR), '", "distance":"', CAST(distance AS CHAR), '", "locationMethod":"', locationMethod, '", "gpsTime":"', DATE_FORMAT(gpsTime, '%b %e %Y %h:%i%p'), '", "userName":"', userName, '", "phoneNumber":"', phoneNumber, '", "sessionID":"', CAST(sessionID AS CHAR), '", "accuracy":"', CAST(accuracy AS CHAR), '", "extraInfo":"', extraInfo, '" }') json
  122. FROM gpslocations
  123. WHERE sessionID = _sessionID
  124. ORDER BY lastupdate;
  125. END ;;
  126. DELIMITER ;
  127. /*!50003 SET sql_mode = @saved_sql_mode */ ;
  128. /*!50003 SET character_set_client = @saved_cs_client */ ;
  129. /*!50003 SET character_set_results = @saved_cs_results */ ;
  130. /*!50003 SET collation_connection = @saved_col_connection */ ;
  131. /*!50003 DROP PROCEDURE IF EXISTS `prcGetRoutes` */;
  132. /*!50003 SET @saved_cs_client = @@character_set_client */ ;
  133. /*!50003 SET @saved_cs_results = @@character_set_results */ ;
  134. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  135. /*!50003 SET character_set_client = utf8 */ ;
  136. /*!50003 SET character_set_results = utf8 */ ;
  137. /*!50003 SET collation_connection = utf8_general_ci */ ;
  138. /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
  139. /*!50003 SET sql_mode = '' */ ;
  140. DELIMITER ;;
  141. CREATE DEFINER=`root`@`localhost` PROCEDURE `prcGetRoutes`()
  142. BEGIN
  143. CREATE TEMPORARY TABLE tempRoutes (
  144. sessionID VARCHAR(50),
  145. userName VARCHAR(50),
  146. startTime DATETIME,
  147. endTime DATETIME)
  148. ENGINE = MEMORY;
  149.  
  150. INSERT INTO tempRoutes (sessionID, userName)
  151. SELECT DISTINCT sessionID, userName
  152. FROM gpslocations;
  153.  
  154. UPDATE tempRoutes tr
  155. SET startTime = (SELECT MIN(gpsTime) FROM gpslocations gl
  156. WHERE gl.sessionID = tr.sessionID
  157. AND gl.userName = tr.userName);
  158.  
  159. UPDATE tempRoutes tr
  160. SET endTime = (SELECT MAX(gpsTime) FROM gpslocations gl
  161. WHERE gl.sessionID = tr.sessionID
  162. AND gl.userName = tr.userName);
  163.  
  164. SELECT
  165.  
  166. CONCAT('{ "sessionID": "', CAST(sessionID AS CHAR), '", "userName": "', userName, '", "times": "(', DATE_FORMAT(startTime, '%b %e %Y %h:%i%p'), ' - ', DATE_FORMAT(endTime, '%b %e %Y %h:%i%p'), ')" }') json
  167. FROM tempRoutes
  168. ORDER BY startTime DESC;
  169.  
  170. DROP TABLE tempRoutes;
  171. END ;;
  172. DELIMITER ;
  173. /*!50003 SET sql_mode = @saved_sql_mode */ ;
  174. /*!50003 SET character_set_client = @saved_cs_client */ ;
  175. /*!50003 SET character_set_results = @saved_cs_results */ ;
  176. /*!50003 SET collation_connection = @saved_col_connection */ ;
  177. /*!50003 DROP PROCEDURE IF EXISTS `prcSaveGPSLocation` */;
  178. /*!50003 SET @saved_cs_client = @@character_set_client */ ;
  179. /*!50003 SET @saved_cs_results = @@character_set_results */ ;
  180. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  181. /*!50003 SET character_set_client = utf8 */ ;
  182. /*!50003 SET character_set_results = utf8 */ ;
  183. /*!50003 SET collation_connection = utf8_general_ci */ ;
  184. /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
  185. /*!50003 SET sql_mode = '' */ ;
  186. DELIMITER ;;
  187. CREATE DEFINER=`root`@`localhost` PROCEDURE `prcSaveGPSLocation`(
  188. _latitude DECIMAL(10,7),
  189. _longitude DECIMAL(10,7),
  190. _speed INT(10),
  191. _direction INT(10),
  192. _distance DECIMAL(10,1),
  193. _date TIMESTAMP,
  194. _locationMethod VARCHAR(50),
  195. _userName VARCHAR(50),
  196. _phoneNumber VARCHAR(50),
  197. _sessionID VARCHAR(50),
  198. _accuracy INT(10),
  199. _extraInfo VARCHAR(255),
  200. _eventType VARCHAR(50)
  201. )
  202. BEGIN
  203. INSERT INTO gpslocations (latitude, longitude, speed, direction, distance, gpsTime, locationMethod, userName, phoneNumber, sessionID, accuracy, extraInfo, eventType)
  204. VALUES (_latitude, _longitude, _speed, _direction, _distance, _date, _locationMethod, _userName, _phoneNumber, _sessionID, _accuracy, _extraInfo, _eventType);
  205. SELECT NOW();
  206. END ;;
  207. DELIMITER ;
  208. /*!50003 SET sql_mode = @saved_sql_mode */ ;
  209. /*!50003 SET character_set_client = @saved_cs_client */ ;
  210. /*!50003 SET character_set_results = @saved_cs_results */ ;
  211. /*!50003 SET collation_connection = @saved_col_connection */ ;
  212. /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
  213.  
  214. /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
  215. /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
  216. /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
  217. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  218. /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  219. /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  220. /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
  221.  
  222. -- Dump completed on 2014-09-14 18:38:51
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!