From 423e9aaf33366a210a853e67836c6db5d8390680 Mon Sep 17 00:00:00 2001 From: Mark Campbell-Smith Date: Thu, 24 Jul 2014 23:44:46 +1000 Subject: Update to RunnerUpLive --- README | 23 ++--- README_Backitude | 42 --------- api_gmaps.js | 15 ++-- api_openlayers.js | 4 +- auth.php | 7 +- config.php | 36 +++----- distance.php | 65 ++++++++++++++ download.php | 1 + export.php | 1 + getpositions.php | 22 ++--- gettrips.php | 1 + index.php | 24 +++--- lang.php | 3 +- logout.php | 1 + main.css | 23 +++++ main.js | 84 ++++++++++++++---- requests.php | 250 +++++++++++++++++------------------------------------- runneruplive.sql | 116 +++++++++++++++++++++++++ trackme.sql | 134 ----------------------------- 19 files changed, 410 insertions(+), 442 deletions(-) delete mode 100644 README_Backitude create mode 100755 distance.php create mode 100755 runneruplive.sql delete mode 100755 trackme.sql diff --git a/README b/README index 256d018..2623b80 100755 --- a/README +++ b/README @@ -1,11 +1,7 @@ -This is a simple web viewer for GPS tracks uploaded with mobile client. -It is designed to work with Android version of great app TrackMe (http://www.luisespinosa.com/trackme_eng.html), -but it should be easy to adjust it for other clients (other database tables). -Interface "look and feel" is based on TrackMe Display (http://forum.xda-developers.com/showthread.php?t=477394). -It is possible to switch between Google Maps API and OpenLayers API with OpenStreetMap (any other compatible base layer). +This is a simple web viewer to work with the excellent open-source fitness app RunnerUp +(https://github.com/jonasoreland/runnerup or https://play.google.com/store/apps/details?id=org.runnerup) -Live demo: -- http://flaa.fabiszewski.net/phptrackme/ +This is heavily based on the phpTrackMe (https://github.com/bfabiszewski/phpTrackme) Requirements: - PHP 5.1.2 @@ -14,21 +10,18 @@ Requirements: Features: - simple -- allows live tracking +- live tracking - track statistics -- altitudes graph +- altitudes graph (to be implemented in RunnerUp android app) - multiple users - user authentication - Google Maps API v3 -- OpenLayers v2 - ajax - server based configuration - user preferences stored in cookies -Todo -- install script -- custom icons -- write opensource client? - License - GPL + +The server address in the Android app should be set to http:///requests.php + diff --git a/README_Backitude b/README_Backitude deleted file mode 100644 index c5c1839..0000000 --- a/README_Backitude +++ /dev/null @@ -1,42 +0,0 @@ -Backitude is an Android client that is designed to capture the location -of your device at configurable time intervals or whenever a position is served -up by the operating system. You can grab it from https://play.google.com/store/apps/details?id=gaugler.backitude - -To configure Backitude to work with the custom server, you need to -set the following parameters in the custom server settings: - -Server URL: http:///requests.php -Request Type: POST -Successful Response Codes: 200,201 -Authentication options: Credentials in POST parameters -User Name: What ever you choose -Password: What ever you choose -Custom Field 1: upload -Custom Field 2: backitude - -User Name: u -Password: p -Latitude: lat -Longitude: long -Accuracy: accuracy -Speed: speed -Altitude: alt -Direction Bearing: angle -Location Timestamp: -Request Timestamp: do -Timezone Offset: -Custom Field 1: a -Custom Field 2: db - -mysql needs to be configured by running the trackme.sql sql script file. - -config.php needs to be configured. - -Within config.php: - - - Use trackme as the database name. - - Initially set allow_registration = 1 so Backitude can register and your user/pass is automatically created - - - - diff --git a/api_gmaps.js b/api_gmaps.js index 2ca65f7..52dfc61 100755 --- a/api_gmaps.js +++ b/api_gmaps.js @@ -80,7 +80,7 @@ function displayTrack(xml,update) { setTimeout(function(){google.maps.event.removeListener(zListener)}, 2000); } } - latestTime = p.dateoccured; + latestTime = p.dateAdded; polies.push(poly); updateSummary(p.dateoccured,totalMeters,totalSeconds); @@ -124,20 +124,21 @@ function setMarker(p,i,posLen) { if (latest==1) { marker.setIcon('http://maps.google.com/mapfiles/dd-end.png') } else if (i==0) { marker.setIcon('http://maps.google.com/mapfiles/marker_greenA.png') } else if (i==posLen-1) { marker.setIcon('http://maps.google.com/mapfiles/markerB.png') } - else { marker.setIcon('http://labs.google.com/ridefinder/images/mm_20_gray.png') } +// else { marker.setIcon('http://labs.google.com/ridefinder/images/mm_20_gray.png') } +// else { marker.setVisible(false) } + else { marker.setIcon('http://maps.gstatic.com/intl/en_us/mapfiles/markers2/measle_blue.png') } // popup var content = ''+ '
'+ ((p.comments != null)?'
'+p.comments+'
':'')+ - '
'+lang_time+': '+p.dateoccured+'
'+ + '
'+lang_time+': '+p.dateAdded+'
'+ ((p.speed != null)?''+lang_speed+': '+(p.speed.toKmH()*factor_kmh)+' '+unit_kmh+'
':'')+ ((p.altitude != null)?''+lang_altitude+': '+(p.altitude*factor_m).toFixed()+' '+unit_m+'
':'')+'
'+ ((latest==0)? diff --git a/auth.php b/auth.php index c3978ef..c278557 100755 --- a/auth.php +++ b/auth.php @@ -2,6 +2,7 @@ /* phpTrackme * * Copyright(C) 2013 Bartek Fabiszewski (www.fabiszewski.net) + * Copyright(C) 2014 Mark Campbell-Smith (campbellsmith.me) * * This is free software; you can redistribute it and/or modify it under * the terms of the GNU Library General Public License as published by @@ -64,7 +65,7 @@ if ($require_authentication) {
'.$lang_title.'
'.$lang_private.'
-
+ '.$lang_username.':

'.$lang_password.':
@@ -104,7 +105,7 @@ if ($require_authentication) { else { $_SESSION['auth'] = $rec_ID; } - $url = str_replace("//", "/", $_SERVER['HTTP_HOST'].dirname($_SERVER['SCRIPT_NAME'])."/index.php"); + $url = str_replace("//", "/", $_SERVER['HTTP_HOST'].dirname($_SERVER['SCRIPT_NAME'])."/index1.php"); header("Location: $ssl://$url"); exit; } else { @@ -117,7 +118,7 @@ if ($require_authentication) { } session_destroy(); $mysqli->close(); - $url = str_replace("//", "/", $_SERVER['HTTP_HOST'].dirname($_SERVER['SCRIPT_NAME'])."/index.php"); + $url = str_replace("//", "/", $_SERVER['HTTP_HOST'].dirname($_SERVER['SCRIPT_NAME'])."/index1.php"); header("Location: $ssl://$url$error"); exit; } diff --git a/config.php b/config.php index b623cca..9d7c121 100755 --- a/config.php +++ b/config.php @@ -2,6 +2,7 @@ /* phpTrackme * * Copyright(C) 2013 Bartek Fabiszewski (www.fabiszewski.net) + * Copyright(C) 2014 Mark Campbell-Smith (campbellsmith.me) * * This is free software; you can redistribute it and/or modify it under * the terms of the GNU Library General Public License as published by @@ -17,45 +18,32 @@ * License along with this program; if not, write to the Free Software * Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. */ -$version = "2.2"; +$version = "0.1"; -// default map drawing framework -// (gmaps = google maps, openlayers = openlayers/osm) -//$mapapi = "gmaps"; -$mapapi = "openlayers"; - -// openlayers additional map layers -// OpenCycleMap (0 = no, 1 = yes) -$layer_ocm = 1; -// MapQuest-OSM (0 = no, 1 = yes) -$layer_mq = 1; -// osmapa.pl (0 = no, 1 = yes) -$layer_osmapa = 1; -// UMP (0 = no, 1 = yes) -$layer_ump = 1; +$mapapi = "gmaps"; // you may set your google maps api key // this is not obligatory by now -//$gkey = ""; +// $gkey = "AIzaSyC2CYwhwpM2NLhTm6L6ZKg9-SBpvft4P4U"; // MySQL config -$dbhost = ""; // mysql host, eg. localhost -$dbuser = ""; // database user -$dbpass = ""; // database pass -$dbname = ""; // database name +$dbhost = "127.0.0.1"; // mysql host, eg. localhost +$dbuser = "user"; // database user +$dbpass = "pass"; // database pass +$dbname = "runneruplive"; // database name $salt = ""; // fill in random string here, it will increase security of password hashes // other // require login/password authentication // (0 = no, 1 = yes) -$require_authentication = 1; +$require_authentication = 0; // admin user who has access to all users locations -$admin_user = ""; +$admin_user = " "; // allow automatic registration of new users // (0 = no, 1 = yes) -$allow_registration = 0; +$allow_registration = 1; // Default interval in seconds for live auto reload $interval = 10; @@ -67,8 +55,6 @@ $lang = "en"; //$lang = "de"; // units -// (metric, imperial) $units = "metric"; -//$units = "imperial"; ?> diff --git a/distance.php b/distance.php new file mode 100755 index 0000000..3d10de4 --- /dev/null +++ b/distance.php @@ -0,0 +1,65 @@ +"; +// echo distance(32.9697, -96.80322, 29.46786, -98.53506, "K") . " Kilometers
"; +// echo distance(32.9697, -96.80322, 29.46786, -98.53506, "N") . " Nautical Miles
"; + +?> diff --git a/download.php b/download.php index 995f190..1637978 100755 --- a/download.php +++ b/download.php @@ -2,6 +2,7 @@ /* phpTrackme * * Copyright(C) 2013 Bartek Fabiszewski (www.fabiszewski.net) + * Copyright(C) 2014 Mark Campbell-Smith (campbellsmith.me) * * This is free software; you can redistribute it and/or modify it under * the terms of the GNU Library General Public License as published by diff --git a/export.php b/export.php index 7df3960..bb012a1 100755 --- a/export.php +++ b/export.php @@ -2,6 +2,7 @@ /* phpTrackme * * Copyright(C) 2013 Bartek Fabiszewski (www.fabiszewski.net) + * Copyright(C) 2014 Mark Campbell-Smith (campbellsmith.me) * * This is free software; you can redistribute it and/or modify it under * the terms of the GNU Library General Public License as published by diff --git a/getpositions.php b/getpositions.php index 9a3acc1..578c5b5 100755 --- a/getpositions.php +++ b/getpositions.php @@ -2,6 +2,7 @@ /* phpTrackme * * Copyright(C) 2013 Bartek Fabiszewski (www.fabiszewski.net) + * Copyright(C) 2014 Mark Campbell-Smith (campbellsmith.me) * * This is free software; you can redistribute it and/or modify it under * the terms of the GNU Library General Public License as published by @@ -36,16 +37,16 @@ function haversine_distance($lat1, $lon1, $lat2, $lon2) { if ($userid) { if ($trackid) { // get all track data - $query = $mysqli->prepare("SELECT positions.ID,positions.Latitude,positions.Longitude,positions.Altitude,positions.Speed,positions.Angle,positions.DateOccurred,positions.Comments,users.username,trips.Name,trips.ID FROM positions LEFT JOIN users ON (positions.FK_Users_ID=users.ID) LEFT JOIN trips ON (positions.FK_Trips_ID=trips.ID) WHERE positions.FK_Users_ID=? AND positions.FK_Trips_ID=? ORDER BY positions.DateOccurred"); + $query = $mysqli->prepare("SELECT positions.ID,positions.Latitude,positions.Longitude,positions.Pace,positions.DateAdded,positions.TotalDistance,positions.TotalTime,positions.EventType,users.username,trips.Name,trips.ID FROM positions LEFT JOIN users ON (positions.FK_Users_ID=users.ID) LEFT JOIN trips ON (positions.FK_Trips_ID=trips.ID) WHERE positions.FK_Users_ID=? AND positions.FK_Trips_ID=? ORDER BY positions.DateAdded"); $query->bind_param('ii', $userid, $trackid); } else { // get data only for latest point - $query = $mysqli->prepare("SELECT positions.ID,positions.Latitude,positions.Longitude,positions.Altitude,positions.Speed,positions.Angle,positions.DateOccurred,positions.Comments,users.username,trips.Name,trips.ID FROM positions LEFT JOIN users ON (positions.FK_Users_ID=users.ID) LEFT JOIN trips ON (positions.FK_Trips_ID=trips.ID) WHERE positions.FK_Users_ID=? ORDER BY positions.DateOccurred DESC LIMIT 1"); + $query = $mysqli->prepare("SELECT positions.ID,positions.Latitude,positions.Longitude,positions.Pace,positions.DateAdded,positions.TotalDistance,positions.TotalTime,positions.EventType,users.username,trips.Name,trips.ID FROM positions LEFT JOIN users ON (positions.FK_Users_ID=users.ID) LEFT JOIN trips ON (positions.FK_Trips_ID=trips.ID) WHERE positions.FK_Users_ID=? ORDER BY positions.DateAdded DESC LIMIT 1"); $query->bind_param('i', $userid); } $query->execute(); - $query->bind_result($positionid,$latitude,$longitude,$altitude,$speed,$angle,$dateoccured,$comments,$username,$trackname,$trackid); + $query->bind_result($positionid,$latitude,$longitude,$pace,$dateadded,$totaldistance,$totaltime,$eventtype,$username,$trackname,$trackid); header("Content-type: text/xml"); $xml = new XMLWriter(); @@ -59,11 +60,12 @@ if ($userid) { $xml->writeAttribute("id", $positionid); $xml->writeElement("latitude", $latitude); $xml->writeElement("longitude", $longitude); - $xml->writeElement("altitude", ($altitude)?round($altitude):$altitude); - $xml->writeElement("speed", $speed); - $xml->writeElement("angle", $angle); - $xml->writeElement("dateoccured", $dateoccured); - $xml->writeElement("comments", $comments); + $xml->writeElement("pace", $pace); + $xml->writeElement("dateadded", $dateadded); + $xml->writeElement("TotalDistance", $totaldistance); + $xml->writeElement("TotalTime", $totaltime); + $xml->writeElement("EventType", $eventtype); + $xml->writeElement("username", $username); $xml->writeElement("trackid", $trackid); $xml->writeElement("trackname", $trackname); @@ -71,8 +73,8 @@ if ($userid) { $prev_latitude = $latitude; $prev_longitude = $longitude; $xml->writeElement("distance", round($distance)); - $seconds = (isset($prev_dateoccured))?(strtotime($dateoccured)-strtotime($prev_dateoccured)):0; - $prev_dateoccured = $dateoccured; + $seconds = (isset($prev_dateadded))?(strtotime($dateadded)-strtotime($prev_dateadded)):0; + $prev_dateadded = $dateadded; $xml->writeElement("seconds", $seconds); $xml->endElement(); } diff --git a/gettrips.php b/gettrips.php index 6883d08..601227f 100755 --- a/gettrips.php +++ b/gettrips.php @@ -2,6 +2,7 @@ /* phpTrackme * * Copyright(C) 2013 Bartek Fabiszewski (www.fabiszewski.net) + * Copyright(C) 2014 Mark Campbell-Smith (campbellsmith.me) * * This is free software; you can redistribute it and/or modify it under * the terms of the GNU Library General Public License as published by diff --git a/index.php b/index.php index f64a792..b124f36 100755 --- a/index.php +++ b/index.php @@ -2,6 +2,7 @@ /* phpTrackme * * Copyright(C) 2013 Bartek Fabiszewski (www.fabiszewski.net) + * Copyright(C) 2014 Mark Campbell-Smith (campbellsmith.me) * * This is free software; you can redistribute it and/or modify it under * the terms of the GNU Library General Public License as published by @@ -19,24 +20,24 @@ */ require_once("config.php"); require_once("auth.php"); +$user = ""; -if (($auth) and ($auth != $admin_user)) { +if (($auth) and ($auth != $admin_user) and ($require_authentication)) { // get username + error_log("here"); $query = "SELECT username FROM users WHERE ID='$auth' LIMIT 1"; $result = $mysqli->query($query); $row = $result->fetch_assoc(); $user = $row["username"]; - - // users - $user_form = ''.$lang_user.'
'.$user.' ('.$lang_logout.')'; } else { // free access or admin user - // prepare user select form + // prepare user select form if (($auth == $admin_user) and ($admin_user != "")) { $user = $auth; $auth = NULL; } + $user_form = ' '.$lang_user.'
@@ -51,7 +52,10 @@ $user_form .= '
'; - $user_form .= ''.$lang_user.'
'.$user.' ('.$lang_logout.')'; +} + +if ($require_authentication) { + $user_form .= ''.$lang_user.'
'.$user.' ('.$lang_logout.')'; } // prepare track select form @@ -176,15 +180,9 @@ print ' -
- '.$api_form.' -
'.$lang_form.'
-
- '.$units_form.' -
'.$lang_download.'
kml
@@ -192,7 +190,7 @@ print '
- +
diff --git a/lang.php b/lang.php index 7944718..7b8a922 100755 --- a/lang.php +++ b/lang.php @@ -2,6 +2,7 @@ /* phpTrackme * * Copyright(C) 2013 Bartek Fabiszewski (www.fabiszewski.net) + * Copyright(C) 2014 Mark Campbell-Smith (campbellsmith.me) * * This is free software; you can redistribute it and/or modify it under * the terms of the GNU Library General Public License as published by @@ -20,7 +21,7 @@ switch($lang) { default: case "en": - $lang_title = "• phpTrackme •"; + $lang_title = "• RunnerUpLive •"; $lang_private = "You need login and password to access this page."; $lang_authfail = "Wrong username or password"; $lang_user = "User"; diff --git a/logout.php b/logout.php index a786403..4533e9b 100755 --- a/logout.php +++ b/logout.php @@ -2,6 +2,7 @@ /* phpTrackme * * Copyright(C) 2013 Bartek Fabiszewski (www.fabiszewski.net) + * Copyright(C) 2014 Mark Campbell-Smith (campbellsmith.me) * * This is free software; you can redistribute it and/or modify it under * the terms of the GNU Library General Public License as published by diff --git a/main.css b/main.css index 049a5b8..bd378bb 100755 --- a/main.css +++ b/main.css @@ -1,6 +1,7 @@ /* phpTrackme * * Copyright(C) 2013 Bartek Fabiszewski (www.fabiszewski.net) + * Copyright(C) 2014 Mark Campbell-Smith (campbellsmith.me) * * This is free software; you can redistribute it and/or modify it under * the terms of the GNU Library General Public License as published by @@ -25,6 +26,25 @@ body { padding: 0; background-color: #666; } +.bgCover { background:#000; position:absolute; left:0; top:0; display:none; overflow:hidden } +.overlayBox { + border:5px solid #09F; + position:absolute; + display:none; + width:90%; + height:80%; + background:#fff; +} +.overlayContent { + padding:10px; + height:95%; + width:95% +} +.closeLink { + float:right; + color:red; +} + a { color: #bce; text-decoration: none; @@ -38,6 +58,8 @@ select { font-weight: normal; padding-top: 0.2em; } + + #menu input, #login input { width: 150px; @@ -181,3 +203,4 @@ select { #close a, #close:link, #close:visited { color: #5070af; } + diff --git a/main.js b/main.js index 7d18bb1..705562d 100755 --- a/main.js +++ b/main.js @@ -1,6 +1,7 @@ /* phpTrackme * * Copyright(C) 2013 Bartek Fabiszewski (www.fabiszewski.net) + * Copyright(C) 2014 Mark Campbell-Smith (campbellsmith.me) * * This is free software; you can redistribute it and/or modify it under * the terms of the GNU Library General Public License as published by @@ -28,7 +29,7 @@ if (units=='imperial') { } else { factor_kmh = 1; - unit_kmh = 'km/h'; + unit_kmh = '/km'; factor_m = 1; unit_m = 'm'; factor_km = 1; @@ -119,7 +120,7 @@ function loadTrack(userid,trackid,update) { xhr.onreadystatechange = function() { if (xhr.readyState==4 && xhr.status==200) { var xml = xhr.responseXML; - var positions = xml.getElementsByTagName('position'); + var positions = xml.getElementsByTagName('position'); if (positions.length>0) { clearMap(); displayTrack(xml,update); @@ -135,32 +136,40 @@ function parsePosition(p) { // read data var latitude = getNode(p,'latitude'); var longitude = getNode(p,'longitude'); - var altitude = getNode(p,'altitude'); // may be null - if (altitude != null) { altitude = parseInt(altitude); } - var speed = getNode(p,'speed'); // may be null - if (speed != null) { speed = parseInt(speed); } - var angle = getNode(p,'angle'); // may be null - if (angle != null) { angle = parseInt(angle); } - var comments = getNode(p,'comments'); // may be null + var altitude = 0; // may be null + //if (altitude != null) { altitude = parseInt(altitude); } + var speed = getNode(p,'pace'); // may be null + //speed = 1; + // var speed = 0; + //if (speed != null) { speed = parseInt(speed); } + //var angle = getNode(p,'angle'); // may be null + var angle = 0; + //if (angle != null) { angle = parseInt(angle); } + //var comments = getNode(p,'comments'); // may be null + var comments = ""; var username = getNode(p,'username'); var trackname = getNode(p,'trackname'); var tid = getNode(p,'trackid'); - var dateoccured = getNode(p,'dateoccured'); + var dateadded = getNode(p,'dateadded'); var distance = parseInt(getNode(p,'distance')); var seconds = parseInt(getNode(p,'seconds')); + var TotalDistance = parseInt(getNode(p,'TotalDistance')); + var TotalTime = getNode(p,'TotalTime'); + var EventType = getNode(p,'EventType'); return { 'latitude': latitude, 'longitude': longitude, - 'altitude': altitude, - 'speed': speed, - 'angle': angle, - 'comments': comments, + 'speed' : speed, + 'angle' : angle, 'username': username, 'trackname': trackname, 'tid': tid, - 'dateoccured': dateoccured, + 'dateadded': dateadded, 'distance': distance, - 'seconds': seconds + 'seconds': seconds, + 'totaldistance' : TotalDistance, + 'totaltime' : TotalTime, + 'EventType' : EventType, }; } @@ -397,3 +406,46 @@ function setUnits(unit) { setCookie('units',unit,30); location.reload(); } + +function showOverlayBox() { + //if box is not set to open then don't do anything + if( isOpen == false ) return; + // set the properties of the overlay box, the left and top positions + $('.overlayBox').css({ + display:'block', + left:( $(window).width() - $('.overlayBox').width() )/2, + top:( $(window).height() - $('.overlayBox').height() )/2 - 20, + position:'absolute' + }); + // set the window background for the overlay. i.e the body becomes darker + $('.bgCover').css({ + display:'block', + width: $(window).width(), + height:$(window).height(), + }); +} +function doOverlayOpen() { + //set status to open + isOpen = true; + showOverlayBox(); + $('.bgCover').css({opacity:0}).animate( {opacity:0.5, backgroundColor:'#000'} ); + // dont follow the link : so return false. + return false; +} +function doOverlayClose() { + //set status to closed + isOpen = false; + $('.overlayBox').css( 'display', 'none' ); + // now animate the background to fade out to opacity 0 + // and then hide it after the animation is complete. + $('.bgCover').animate( {opacity:0}, null, null, function() { $(this).hide(); } ); +} + +// if window is resized then reposition the overlay box +$(window).bind('resize',showOverlayBox); +// activate when the link with class launchLink is clicked +$('a.launchLink').click( doOverlayOpen ); +// close it when closeLink is clicked +$('a.closeLink').click( doOverlayClose ); + + diff --git a/requests.php b/requests.php index b112412..c9d8c59 100755 --- a/requests.php +++ b/requests.php @@ -2,6 +2,7 @@ /* phpTrackme * * Copyright(C) 2013 Bartek Fabiszewski (www.fabiszewski.net) + * Copyright(C) 2014 Mark Campbell-Smith (campbellsmith.me) * * This is free software; you can redistribute it and/or modify it under * the terms of the GNU Library General Public License as published by @@ -21,21 +22,35 @@ // http://forum.xda-developers.com/showpost.php?p=3250539&postcount=2 require_once("config.php"); -$user = (isset($_REQUEST['u']) ? $_REQUEST['u'] : ""); -$pass = (isset($_REQUEST['p']) ? md5($salt.$_REQUEST['p']) : ""); -$requireddb = (isset($_REQUEST['db']) ? $_REQUEST['db'] : 0); -$tripname = (isset($_REQUEST['tn']) ? $_REQUEST['tn'] : ""); -$action = (isset($_REQUEST['a']) ? $_REQUEST['a'] : ""); +$debug = 0; +$activityType = array("","Running", "Bike"); +$inputJSON = file_get_contents('php://input'); -// If the client uses Backitude then define the tripname as user-date -if ($requireddb == 'backitude') { - $tripname = $user.'-'.date("Ymd"); -} -// FIXME what is it for? -elseif ($requireddb<8) { - //Result:5 Incompatible database. - quit(5); -} + + $input= json_decode( $inputJSON, TRUE ); //convert JSON into array + $user = $input['userName']; + $pass = $input['password']; + $lat = $input['lat']; + $long = $input['long']; + $pace = $input['Pace']; + $TotalDistance = $input['TotalDistance']; + $eventType = $input['runningEventType']; + $TotalTime = $input['TotalTime']; + $dateoccurred = time(); + $tripname = $user.'-'.date("Ymd"); + $iconid = 50; +// error_log("$tripname"); +// foreach ($input as $key => $value) { +// echo ""; +// echo ""; +// echo $key; +// echo ""; +// echo ""; +// echo $value; +// echo ""; +// echo ""; +// error_log("key $key value $value"); +// } $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if ($mysqli->connect_errno) { @@ -43,11 +58,11 @@ if ($mysqli->connect_errno) { quit(4); } -if ((!$user) || (!$pass)){ +if ((!$user) || (!$pass)){ //Result:3 User or password not specified. + error_log("user or pass not specified ($user / $pass)"); quit(3); } - $query = $mysqli->prepare("SELECT ID,username,password FROM users WHERE username=? LIMIT 1"); $query->bind_param('s', $user); $query->execute(); @@ -57,9 +72,11 @@ $query->fetch(); $num = $query->num_rows; $query->free_result(); $query->close(); + if ($num) { if (($user==$rec_user) && ($pass!=$rec_pass)) { //Result:1 User correct, invalid password. + error_log("user correct, invalid password ($user / $pass <> $rec_pass)"); quit(1); } } @@ -74,141 +91,57 @@ else { if (!$userid) { //Result:2 User did not exist but after being created couldn't be found. // Or rather something went wrong while updating database - quit(2); + error_log("User not created"); + quit(2); } - } + } else { // User unknown, we don't allow autoregistration // Let's use this one: //Result:1 User correct, invalid password. - quit(1); + error_log("User unknown"); + quit(1); } } -switch($action) { - // action: noop - case "noop": - // test - quit(0); - break; - - // action: deletetrip - case "deletetrip": - if ($tripname) { - $sql = "DELETE FROM positions LEFT JOIN trips ON positions.FK_Trips_ID=trips.ID " - ."WHERE positions.FK_Users_ID=? AND trips.Name=?"; - $query = $mysqli->prepare($sql); - if ($query) { - $query->bind_param('is', $userid, $tripname); - $query->execute(); - $query->close(); - } - $sql = "DELETE FROM trips WHERE FK_Users_ID=? AND Name=?"; - $query = $mysqli->prepare($sql); - $query->bind_param('is', $userid, $tripname); - $query->execute(); - $rows = $mysqli->affected_rows; - $query->close(); - if ($rows) { - quit(0); - } - else { - //Result:7 Trip not found - quit(7); - } - } - else { - //Result:6 Trip not specified. - quit(6); - } - break; - - // action: addtrip - case "addtrip": - if ($tripname) { - $sql = "INSERT INTO trips (FK_Users_ID,Name) VALUES (?,?)"; - $query = $mysqli->prepare($sql); - $query->bind_param('is', $userid, $tripname); - $query->execute(); - $query->close(); - } - else { - //Result:6 Trip not specified. - quit(6); - } - break; - - // action: gettriplist - case "gettriplist": - $sql = "SELECT a1.Name,(SELECT MIN(a2.DateOccurred) FROM positions a2 " - ."WHERE a2.FK_Trips_ID=a1.ID) AS startdate " - ."FROM trips a1 WHERE a1.FK_Users_ID=? ORDER BY Name"; - $query = $mysqli->prepare($sql); - $query->bind_param('i', $userid); - $query->execute(); - $query->store_result(); - $query->bind_result($tripname,$startdate); - $num = $query->num_rows; - $triplist = array(); - if ($num) { - while ($query->fetch()) { - $triplist[] = $tripname."|".$startdate; - } - } - $query->free_result(); - $query->close(); - $param = implode("\n",$triplist); - quit(0,$param); - break; - - // action: upload - case "upload": - $lat = isset($_REQUEST["lat"]) ? $_REQUEST["lat"] : NULL; - $long = isset($_REQUEST["long"]) ? $_REQUEST["long"] : NULL; - // If the client uses Backitude then convert the date into handled format - $dateoccurred = isset($_REQUEST["do"]) ? $_REQUEST["do"] : NULL; - $altitude = isset($_REQUEST["alt"]) ? $_REQUEST["alt"] : NULL; - $angle = isset($_REQUEST["ang"]) ? $_REQUEST["ang"] : NULL; - $speed = isset($_REQUEST["sp"]) ? $_REQUEST["sp"] : NULL; - $iconname = isset($_REQUEST["iconname"]) ? $_REQUEST["iconname"] : NULL; - $comments = isset($_REQUEST["comments"]) ? $_REQUEST["comments"] : NULL; - $imageurl = isset($_REQUEST["imageurl"]) ? $_REQUEST["imageurl"] : NULL; - $cellid = isset($_REQUEST["cid"]) ? $_REQUEST["cid"] : NULL; - $signalstrength = isset($_REQUEST["ss"]) ? $_REQUEST["ss"] : NULL; - $signalstrengthmax = isset($_REQUEST["ssmax"]) ? $_REQUEST["ssmax"] : NULL; - $signalstrengthmin = isset($_REQUEST["ssmin"]) ? $_REQUEST["ssmin"] : NULL; - $batterystatus = isset($_REQUEST["bs"]) ? $_REQUEST["bs"] : NULL; - $uploadss = isset($_REQUEST["upss"]) ? $_REQUEST["upss"] : NULL; // FIXME is it needed? - $iconid = NULL; - if ($iconname) { - $sql = "SELECT ID FROM icons WHERE Name=? LIMIT 1"; - $query = $mysqli->prepare($sql); - $query->bind_param('s', $iconname); + if ($tripname) { + // get tripid + //$query = $mysqli->prepare("SELECT ID FROM trips WHERE FK_Users_ID=? AND Name=? LIMIT 1"); + //$query->bind_param('is', $userid, $tripname); + $query = $mysqli->prepare("SELECT ID,Name FROM trips WHERE FK_Users_ID=? AND Name LIKE '$tripname%' ORDER BY Name DESC limit 1"); + $query->bind_param('i', $userid ); $query->execute(); $query->store_result(); - $query->bind_result($id); + $query->bind_result($tripid,$tripname); $query->fetch(); $num = $query->num_rows; $query->free_result(); $query->close(); - if ($num) { - $iconid = $id; - } - } - $tripid = NULL; // FIXME: not sure what trips with null id are - if ($tripname) { - // get tripid - $query = $mysqli->prepare("SELECT ID FROM trips WHERE FK_Users_ID=? AND Name=? LIMIT 1"); - $query->bind_param('is', $userid, $tripname); + + $query = $mysqli->prepare("SELECT EventType FROM positions where FK_Users_ID=? and FK_Trips_ID=? ORDER BY ID DESC limit 1"); + $query->bind_param('ii', $userid, $tripid); $query->execute(); $query->store_result(); - $query->bind_result($tripid); + $query->bind_result($eventTypeLast); $query->fetch(); - $num = $query->num_rows; $query->free_result(); - $query->close(); - if (!$num) { + $query->close(); + + if ((!$num) || ($eventTypeLast == 3)) { // create trip + error_log("create trip"); + if (substr_count($tripname, '-') == 1) { + $tripname = $tripname."-1"; + error_log("create trip 1 $tripname"); + } + else { + $ptn = "/(.*)-(\d+$)/"; + preg_match($ptn, $tripname, $matches); + $matches[2]=$matches[2]+1; + $tripname = $matches[1]."-".$matches[2]; + error_log("create trip regex 2 $tripname"); + } + $query = $mysqli->prepare("INSERT INTO trips (FK_Users_ID,Name) VALUES (?,?)"); $query->bind_param('is', $userid, $tripname); $query->execute(); @@ -218,64 +151,33 @@ switch($action) { //Result:6 Trip didn't exist and system was unable to create it. quit(6); } - } } - if ($requireddb == 'backitude') { - $sql = "INSERT INTO positions " - ."(FK_Users_ID,FK_Trips_ID,Latitude,Longitude,DateOccurred,FK_Icons_ID," - ."Speed,Altitude,Comments,ImageURL,Angle,SignalStrength,SignalStrengthMax," - ."SignalStrengthMin,BatteryStatus) VALUES (?,?,?,?,FROM_UNIXTIME(?),?,?,?,?,?,?,?,?,?,?)"; - } else { - $sql = "INSERT INTO positions " - ."(FK_Users_ID,FK_Trips_ID,Latitude,Longitude,DateOccurred,FK_Icons_ID," - ."Speed,Altitude,Comments,ImageURL,Angle,SignalStrength,SignalStrengthMax," - ."SignalStrengthMin,BatteryStatus) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; - } - + + $sql = "INSERT INTO positions " + ."(FK_Users_ID,FK_Trips_ID,Latitude,Longitude,FK_Icons_ID," + ."Pace,TotalDistance,TotalTime,EventType) VALUES (?,?,?,?,?,?,?,?,?)"; $query = $mysqli->prepare($sql); - $query->bind_param('iiddsiddssdiiii', - $userid,$tripid,$lat,$long,$dateoccurred,$iconid, - $speed,$altitude,$comments,$imageurl,$angle,$signalstrength,$signalstrengthmax, - $signalstrengthmin,$batterystatus); + $query->bind_param('iiddisisi',$userid,$tripid,$lat,$long,$iconid,$pace,$TotalDistance,$TotalTime,$eventType); + $query->execute(); $query->close(); if ($mysqli->errno) { //Result:7|SQLERROR Insert statement failed. + error_log("Mysql error on upload 1"); quit(7,$mysqli->error); } - //FIXME Are cellids used in Android client? - $upcellext = isset($_REQUEST["upcellext"]) ? $_REQUEST["upcellext"] : NULL; - if ($upcellext==1 && $cellid) { - $sql = "INSERT INTO cellids (CellID,Latitude,Longitude,SignalStrength,SignalStrengthMax,SignalStrengthMin) " - ."VALUES (?,?,?,?,?,?)"; - $query = $mysqli->prepare($sql); - $query->bind_param('sddiii',$cellid,$lat,$long,$signalstrength,$signalstrengthmax,$signalstrengthmin); - $query->execute(); - $query->close(); - if ($mysqli->errno) { - //Result:7|SQLERROR Insert statement failed. - quit(7,$mysqli->error); - } - } + error_log("finish upload"); quit(0); break; - - // action: geticonlist - // action: renametrip - // action: findclosestbuddy - // action: delete - // action: sendemail - // action: updateimageurl - // action: findclosestpositionbytime - // action: findclosestpositionbyposition - // action: gettripinfo - // action: gettriphighlights } function quit($errno,$param=""){ print "Result:".$errno.(($param)?"|$param":""); +// error_log( "Result:".$errno.(($param)?"|$param":"")); + exit(); } $mysqli->close(); ?> + diff --git a/runneruplive.sql b/runneruplive.sql new file mode 100755 index 0000000..b9b00d7 --- /dev/null +++ b/runneruplive.sql @@ -0,0 +1,116 @@ +-- +-- Database layout inherited from TrackMe +-- Some tables/columns are not used by the viewer at the moment. +-- Kept for compatibility with old data. +-- + +SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; +SET time_zone = "+00:00"; +CREATE DATABASE IF NOT EXISTS `runneruplive` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; +USE `runneruplive`; + +CREATE TABLE IF NOT EXISTS `icons` ( + `ID` int(11) NOT NULL auto_increment, + `Name` varchar(255) NOT NULL, + `URL` varchar(512) NOT NULL, + PRIMARY KEY (`ID`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8; + + +CREATE TABLE IF NOT EXISTS `positions` ( + `ID` int(11) NOT NULL auto_increment, + `FK_Users_ID` int(11) NOT NULL, + `FK_Trips_ID` int(11) default NULL, + `FK_Icons_ID` int(11) default NULL, + `Latitude` double NOT NULL, + `Longitude` double NOT NULL, + `Pace` varchar(255) default NULL, + `DateAdded` timestamp NOT NULL default CURRENT_TIMESTAMP, + `TotalDistance` int(11) default NULL, + `TotalTime` varchar(255) default NULL, + `EventType` int(11) default NULL, + PRIMARY KEY (`ID`), + KEY `Index_FK_Trips_ID` (`FK_Trips_ID`), + KEY `Index_FK_Users_ID` (`FK_Users_ID`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8; + +CREATE TABLE IF NOT EXISTS `trips` ( + `ID` int(11) NOT NULL auto_increment, + `FK_Users_ID` int(11) NOT NULL, + `Name` varchar(255) NOT NULL, + `Comments` varchar(1024) default NULL, + PRIMARY KEY (`ID`), + UNIQUE KEY `Index_FK_Users_ID_Name` (`FK_Users_ID`,`Name`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8; + +CREATE TABLE IF NOT EXISTS `users` ( + `ID` int(11) NOT NULL auto_increment, + `username` varchar(15) NOT NULL, + `password` varchar(50) NOT NULL default '', + PRIMARY KEY (`ID`), + UNIQUE KEY `Index_username` (`username`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8; + +INSERT INTO `icons` VALUES (1, 'Snack', 'http://maps.google.com/mapfiles/ms/micons/snack_bar.png'); +INSERT INTO `icons` VALUES (2, 'Hiking', 'http://maps.google.com/mapfiles/ms/micons/hiker.png'); +INSERT INTO `icons` VALUES (3, 'Lodging', 'http://maps.google.com/mapfiles/ms/micons/lodging.png'); +INSERT INTO `icons` VALUES (4, 'Restaurant', 'http://maps.google.com/mapfiles/ms/micons/restaurant.png'); +INSERT INTO `icons` VALUES (5, 'POI', 'http://maps.google.com/mapfiles/ms/micons/POI.png'); +INSERT INTO `icons` VALUES (6, 'Arts', 'http://maps.google.com/mapfiles/ms/micons/arts.png'); +INSERT INTO `icons` VALUES (7, 'Bar', 'http://maps.google.com/mapfiles/ms/micons/bar.png'); +INSERT INTO `icons` VALUES (8, 'Blue-dot', 'http://maps.google.com/mapfiles/ms/micons/blue-dot.png'); +INSERT INTO `icons` VALUES (9, 'Bus', 'http://maps.google.com/mapfiles/ms/micons/bus.png'); +INSERT INTO `icons` VALUES (10, 'Taxi', 'http://maps.google.com/mapfiles/ms/micons/cabs.png'); +INSERT INTO `icons` VALUES (11, 'Camera', 'http://maps.google.com/mapfiles/ms/micons/camera.png'); +INSERT INTO `icons` VALUES (12, 'Camping', 'http://maps.google.com/mapfiles/ms/micons/campground.png'); +INSERT INTO `icons` VALUES (13, 'Caution', 'http://maps.google.com/mapfiles/ms/micons/caution.png'); +INSERT INTO `icons` VALUES (14, 'Coffee House', 'http://maps.google.com/mapfiles/ms/micons/coffeehouse.png'); +INSERT INTO `icons` VALUES (15, 'Store', 'http://maps.google.com/mapfiles/ms/micons/convienancestore.png'); +INSERT INTO `icons` VALUES (16, 'Cycling', 'http://maps.google.com/mapfiles/ms/micons/cycling.png'); +INSERT INTO `icons` VALUES (17, 'Dollar', 'http://maps.google.com/mapfiles/ms/micons/dollar.png'); +INSERT INTO `icons` VALUES (18, 'Drinking water', 'http://maps.google.com/mapfiles/ms/micons/drinking_water.png'); +INSERT INTO `icons` VALUES (19, 'Electronics', 'http://maps.google.com/mapfiles/ms/micons/electronics.png'); +INSERT INTO `icons` VALUES (20, 'Falling Rocks', 'http://maps.google.com/mapfiles/ms/micons/fallingrocks.png'); +INSERT INTO `icons` VALUES (21, 'Ferry', 'http://maps.google.com/mapfiles/ms/micons/ferry.png'); +INSERT INTO `icons` VALUES (22, 'Fire Dept.', 'http://maps.google.com/mapfiles/ms/micons/firedept.png'); +INSERT INTO `icons` VALUES (23, 'Fishing', 'http://maps.google.com/mapfiles/ms/micons/fishing.png'); +INSERT INTO `icons` VALUES (24, 'Flag', 'http://maps.google.com/mapfiles/ms/micons/flag.png'); +INSERT INTO `icons` VALUES (25, 'Gas', 'http://maps.google.com/mapfiles/ms/micons/gas.png'); +INSERT INTO `icons` VALUES (26, 'Grocery Store', 'http://maps.google.com/mapfiles/ms/micons/grocerystore.png'); +INSERT INTO `icons` VALUES (27, 'Helicopter', 'http://maps.google.com/mapfiles/ms/micons/helicopter.png'); +INSERT INTO `icons` VALUES (28, 'Horseback riding', 'http://maps.google.com/mapfiles/ms/micons/horsebackriding.png'); +INSERT INTO `icons` VALUES (29, 'Hospital', 'http://maps.google.com/mapfiles/ms/micons/hospitals.png'); +INSERT INTO `icons` VALUES (30, 'Hot springs', 'http://maps.google.com/mapfiles/ms/micons/hotsprings.png'); +INSERT INTO `icons` VALUES (31, 'Info', 'http://maps.google.com/mapfiles/ms/micons/info.png'); +INSERT INTO `icons` VALUES (32, 'Info 2', 'http://maps.google.com/mapfiles/ms/micons/info_circle.png'); +INSERT INTO `icons` VALUES (33, 'Man', 'http://maps.google.com/mapfiles/ms/micons/man.png'); +INSERT INTO `icons` VALUES (34, 'Marina', 'http://maps.google.com/mapfiles/ms/micons/marina.png'); +INSERT INTO `icons` VALUES (35, 'Mechanic', 'http://maps.google.com/mapfiles/ms/micons/mechanic.png'); +INSERT INTO `icons` VALUES (36, 'Motorcycling', 'http://maps.google.com/mapfiles/ms/micons/motorcycling.png'); +INSERT INTO `icons` VALUES (37, 'Parking', 'http://maps.google.com/mapfiles/ms/micons/parkinglot.png'); +INSERT INTO `icons` VALUES (38, 'Partly Cloudy', 'http://maps.google.com/mapfiles/ms/micons/partly_cloudy.png'); +INSERT INTO `icons` VALUES (39, 'Phone', 'http://maps.google.com/mapfiles/ms/micons/phone.png'); +INSERT INTO `icons` VALUES (40, 'Picnic', 'http://maps.google.com/mapfiles/ms/micons/picnic.png'); +INSERT INTO `icons` VALUES (41, 'Plane', 'http://maps.google.com/mapfiles/ms/micons/plane.png'); +INSERT INTO `icons` VALUES (42, 'Police', 'http://maps.google.com/mapfiles/ms/micons/police.png'); +INSERT INTO `icons` VALUES (43, 'Post Office', 'http://maps.google.com/mapfiles/ms/micons/postoffice-us.png'); +INSERT INTO `icons` VALUES (44, 'Question mark', 'http://maps.google.com/mapfiles/ms/micons/question.png'); +INSERT INTO `icons` VALUES (45, 'Rail', 'http://maps.google.com/mapfiles/ms/micons/rail.png'); +INSERT INTO `icons` VALUES (46, 'Rainy', 'http://maps.google.com/mapfiles/ms/micons/rainy.png'); +INSERT INTO `icons` VALUES (47, 'Ranger Station', 'http://maps.google.com/mapfiles/ms/micons/rangerstation.png'); +INSERT INTO `icons` VALUES (48, 'Recycle', 'http://maps.google.com/mapfiles/ms/micons/recycle.png'); +INSERT INTO `icons` VALUES (49, 'Snow', 'http://maps.google.com/mapfiles/ms/micons/snowflake_simple.png'); +INSERT INTO `icons` VALUES (50, 'Sport', 'http://maps.google.com/mapfiles/ms/micons/sportvenue.png'); +INSERT INTO `icons` VALUES (51, 'Subway', 'http://maps.google.com/mapfiles/ms/micons/subway.png'); +INSERT INTO `icons` VALUES (52, 'Sunny', 'http://maps.google.com/mapfiles/ms/micons/sunny.png'); +INSERT INTO `icons` VALUES (53, 'Swimming', 'http://maps.google.com/mapfiles/ms/micons/swimming.png'); +INSERT INTO `icons` VALUES (54, 'Toilets', 'http://maps.google.com/mapfiles/ms/micons/toilets.png'); +INSERT INTO `icons` VALUES (55, 'Trail', 'http://maps.google.com/mapfiles/ms/micons/trail.png'); +INSERT INTO `icons` VALUES (56, 'Tree', 'http://maps.google.com/mapfiles/ms/micons/tree.png'); +INSERT INTO `icons` VALUES (57, 'Truck', 'http://maps.google.com/mapfiles/ms/micons/truck.png'); +INSERT INTO `icons` VALUES (58, 'Volcano', 'http://maps.google.com/mapfiles/ms/micons/volcano.png'); +INSERT INTO `icons` VALUES (59, 'Water', 'http://maps.google.com/mapfiles/ms/micons/water.png'); +INSERT INTO `icons` VALUES (60, 'Waterfalls', 'http://maps.google.com/mapfiles/ms/micons/waterfalls.png'); +INSERT INTO `icons` VALUES (61, 'Wheel Chair', 'http://maps.google.com/mapfiles/ms/micons/wheel_chair_accessible.png'); +INSERT INTO `icons` VALUES (62, 'Woman', 'http://maps.google.com/mapfiles/ms/micons/woman.png'); +INSERT INTO `icons` VALUES (63, 'Monster gate', 'http://img.gamespot.com/gamespot/shared/user/emblem_e3monster_s.jpg'); diff --git a/trackme.sql b/trackme.sql deleted file mode 100755 index 5c47d24..0000000 --- a/trackme.sql +++ /dev/null @@ -1,134 +0,0 @@ --- --- Database layout inherited from TrackMe --- Some tables/columns are not used by the viewer at the moment. --- Kept for compatibility with old data. --- - -SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; -SET time_zone = "+00:00"; -CREATE DATABASE IF NOT EXISTS `trackme` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; -USE `trackme`; - -CREATE TABLE IF NOT EXISTS `cellids` ( - `ID` int(11) NOT NULL auto_increment, - `CellID` varchar(255) NOT NULL, - `Latitude` double NOT NULL, - `Longitude` double NOT NULL, - `DateAdded` timestamp NOT NULL default CURRENT_TIMESTAMP, - `SignalStrength` int(11) default NULL, - `SignalStrengthMax` int(11) default NULL, - `SignalStrengthMin` int(11) default NULL, - PRIMARY KEY (`ID`), - KEY `Index_CellID` (`CellID`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; - -CREATE TABLE IF NOT EXISTS `icons` ( - `ID` int(11) NOT NULL auto_increment, - `Name` varchar(255) NOT NULL, - `URL` varchar(512) NOT NULL, - PRIMARY KEY (`ID`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; - -CREATE TABLE IF NOT EXISTS `positions` ( - `ID` int(11) NOT NULL auto_increment, - `FK_Users_ID` int(11) NOT NULL, - `FK_Trips_ID` int(11) default NULL, - `FK_Icons_ID` int(11) default NULL, - `Latitude` double NOT NULL, - `Longitude` double NOT NULL, - `Altitude` double default '0', - `Speed` double default '0', - `Angle` double default NULL, - `DateAdded` timestamp NOT NULL default CURRENT_TIMESTAMP, - `DateOccurred` timestamp NULL default '0000-00-00 00:00:00', - `Comments` varchar(255) default NULL, - `ImageURL` varchar(255) default NULL, - `SignalStrength` int(11) default NULL, - `SignalStrengthMax` int(11) default NULL, - `SignalStrengthMin` int(11) default NULL, - `BatteryStatus` tinyint(4) default NULL, - PRIMARY KEY (`ID`), - KEY `Index_FK_Trips_ID` (`FK_Trips_ID`), - KEY `Index_FK_Users_ID` (`FK_Users_ID`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; - -CREATE TABLE IF NOT EXISTS `trips` ( - `ID` int(11) NOT NULL auto_increment, - `FK_Users_ID` int(11) NOT NULL, - `Name` varchar(255) NOT NULL, - `Comments` varchar(1024) default NULL, - PRIMARY KEY (`ID`), - UNIQUE KEY `Index_FK_Users_ID_Name` (`FK_Users_ID`,`Name`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; - -CREATE TABLE IF NOT EXISTS `users` ( - `ID` int(11) NOT NULL auto_increment, - `username` varchar(15) NOT NULL, - `password` varchar(50) NOT NULL default '', - PRIMARY KEY (`ID`), - UNIQUE KEY `Index_username` (`username`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; - -INSERT INTO `icons` VALUES (1, 'Snack', 'http://maps.google.com/mapfiles/ms/micons/snack_bar.png'); -INSERT INTO `icons` VALUES (2, 'Hiking', 'http://maps.google.com/mapfiles/ms/micons/hiker.png'); -INSERT INTO `icons` VALUES (3, 'Lodging', 'http://maps.google.com/mapfiles/ms/micons/lodging.png'); -INSERT INTO `icons` VALUES (4, 'Restaurant', 'http://maps.google.com/mapfiles/ms/micons/restaurant.png'); -INSERT INTO `icons` VALUES (5, 'POI', 'http://maps.google.com/mapfiles/ms/micons/POI.png'); -INSERT INTO `icons` VALUES (6, 'Arts', 'http://maps.google.com/mapfiles/ms/micons/arts.png'); -INSERT INTO `icons` VALUES (7, 'Bar', 'http://maps.google.com/mapfiles/ms/micons/bar.png'); -INSERT INTO `icons` VALUES (8, 'Blue-dot', 'http://maps.google.com/mapfiles/ms/micons/blue-dot.png'); -INSERT INTO `icons` VALUES (9, 'Bus', 'http://maps.google.com/mapfiles/ms/micons/bus.png'); -INSERT INTO `icons` VALUES (10, 'Taxi', 'http://maps.google.com/mapfiles/ms/micons/cabs.png'); -INSERT INTO `icons` VALUES (11, 'Camera', 'http://maps.google.com/mapfiles/ms/micons/camera.png'); -INSERT INTO `icons` VALUES (12, 'Camping', 'http://maps.google.com/mapfiles/ms/micons/campground.png'); -INSERT INTO `icons` VALUES (13, 'Caution', 'http://maps.google.com/mapfiles/ms/micons/caution.png'); -INSERT INTO `icons` VALUES (14, 'Coffee House', 'http://maps.google.com/mapfiles/ms/micons/coffeehouse.png'); -INSERT INTO `icons` VALUES (15, 'Store', 'http://maps.google.com/mapfiles/ms/micons/convienancestore.png'); -INSERT INTO `icons` VALUES (16, 'Cycling', 'http://maps.google.com/mapfiles/ms/micons/cycling.png'); -INSERT INTO `icons` VALUES (17, 'Dollar', 'http://maps.google.com/mapfiles/ms/micons/dollar.png'); -INSERT INTO `icons` VALUES (18, 'Drinking water', 'http://maps.google.com/mapfiles/ms/micons/drinking_water.png'); -INSERT INTO `icons` VALUES (19, 'Electronics', 'http://maps.google.com/mapfiles/ms/micons/electronics.png'); -INSERT INTO `icons` VALUES (20, 'Falling Rocks', 'http://maps.google.com/mapfiles/ms/micons/fallingrocks.png'); -INSERT INTO `icons` VALUES (21, 'Ferry', 'http://maps.google.com/mapfiles/ms/micons/ferry.png'); -INSERT INTO `icons` VALUES (22, 'Fire Dept.', 'http://maps.google.com/mapfiles/ms/micons/firedept.png'); -INSERT INTO `icons` VALUES (23, 'Fishing', 'http://maps.google.com/mapfiles/ms/micons/fishing.png'); -INSERT INTO `icons` VALUES (24, 'Flag', 'http://maps.google.com/mapfiles/ms/micons/flag.png'); -INSERT INTO `icons` VALUES (25, 'Gas', 'http://maps.google.com/mapfiles/ms/micons/gas.png'); -INSERT INTO `icons` VALUES (26, 'Grocery Store', 'http://maps.google.com/mapfiles/ms/micons/grocerystore.png'); -INSERT INTO `icons` VALUES (27, 'Helicopter', 'http://maps.google.com/mapfiles/ms/micons/helicopter.png'); -INSERT INTO `icons` VALUES (28, 'Horseback riding', 'http://maps.google.com/mapfiles/ms/micons/horsebackriding.png'); -INSERT INTO `icons` VALUES (29, 'Hospital', 'http://maps.google.com/mapfiles/ms/micons/hospitals.png'); -INSERT INTO `icons` VALUES (30, 'Hot springs', 'http://maps.google.com/mapfiles/ms/micons/hotsprings.png'); -INSERT INTO `icons` VALUES (31, 'Info', 'http://maps.google.com/mapfiles/ms/micons/info.png'); -INSERT INTO `icons` VALUES (32, 'Info 2', 'http://maps.google.com/mapfiles/ms/micons/info_circle.png'); -INSERT INTO `icons` VALUES (33, 'Man', 'http://maps.google.com/mapfiles/ms/micons/man.png'); -INSERT INTO `icons` VALUES (34, 'Marina', 'http://maps.google.com/mapfiles/ms/micons/marina.png'); -INSERT INTO `icons` VALUES (35, 'Mechanic', 'http://maps.google.com/mapfiles/ms/micons/mechanic.png'); -INSERT INTO `icons` VALUES (36, 'Motorcycling', 'http://maps.google.com/mapfiles/ms/micons/motorcycling.png'); -INSERT INTO `icons` VALUES (37, 'Parking', 'http://maps.google.com/mapfiles/ms/micons/parkinglot.png'); -INSERT INTO `icons` VALUES (38, 'Partly Cloudy', 'http://maps.google.com/mapfiles/ms/micons/partly_cloudy.png'); -INSERT INTO `icons` VALUES (39, 'Phone', 'http://maps.google.com/mapfiles/ms/micons/phone.png'); -INSERT INTO `icons` VALUES (40, 'Picnic', 'http://maps.google.com/mapfiles/ms/micons/picnic.png'); -INSERT INTO `icons` VALUES (41, 'Plane', 'http://maps.google.com/mapfiles/ms/micons/plane.png'); -INSERT INTO `icons` VALUES (42, 'Police', 'http://maps.google.com/mapfiles/ms/micons/police.png'); -INSERT INTO `icons` VALUES (43, 'Post Office', 'http://maps.google.com/mapfiles/ms/micons/postoffice-us.png'); -INSERT INTO `icons` VALUES (44, 'Question mark', 'http://maps.google.com/mapfiles/ms/micons/question.png'); -INSERT INTO `icons` VALUES (45, 'Rail', 'http://maps.google.com/mapfiles/ms/micons/rail.png'); -INSERT INTO `icons` VALUES (46, 'Rainy', 'http://maps.google.com/mapfiles/ms/micons/rainy.png'); -INSERT INTO `icons` VALUES (47, 'Ranger Station', 'http://maps.google.com/mapfiles/ms/micons/rangerstation.png'); -INSERT INTO `icons` VALUES (48, 'Recycle', 'http://maps.google.com/mapfiles/ms/micons/recycle.png'); -INSERT INTO `icons` VALUES (49, 'Snow', 'http://maps.google.com/mapfiles/ms/micons/snowflake_simple.png'); -INSERT INTO `icons` VALUES (50, 'Sport', 'http://maps.google.com/mapfiles/ms/micons/sportvenue.png'); -INSERT INTO `icons` VALUES (51, 'Subway', 'http://maps.google.com/mapfiles/ms/micons/subway.png'); -INSERT INTO `icons` VALUES (52, 'Sunny', 'http://maps.google.com/mapfiles/ms/micons/sunny.png'); -INSERT INTO `icons` VALUES (53, 'Swimming', 'http://maps.google.com/mapfiles/ms/micons/swimming.png'); -INSERT INTO `icons` VALUES (54, 'Toilets', 'http://maps.google.com/mapfiles/ms/micons/toilets.png'); -INSERT INTO `icons` VALUES (55, 'Trail', 'http://maps.google.com/mapfiles/ms/micons/trail.png'); -INSERT INTO `icons` VALUES (56, 'Tree', 'http://maps.google.com/mapfiles/ms/micons/tree.png'); -INSERT INTO `icons` VALUES (57, 'Truck', 'http://maps.google.com/mapfiles/ms/micons/truck.png'); -INSERT INTO `icons` VALUES (58, 'Volcano', 'http://maps.google.com/mapfiles/ms/micons/volcano.png'); -INSERT INTO `icons` VALUES (59, 'Water', 'http://maps.google.com/mapfiles/ms/micons/water.png'); -INSERT INTO `icons` VALUES (60, 'Waterfalls', 'http://maps.google.com/mapfiles/ms/micons/waterfalls.png'); -INSERT INTO `icons` VALUES (61, 'Wheel Chair', 'http://maps.google.com/mapfiles/ms/micons/wheel_chair_accessible.png'); -INSERT INTO `icons` VALUES (62, 'Woman', 'http://maps.google.com/mapfiles/ms/micons/woman.png'); -INSERT INTO `icons` VALUES (63, 'Monster gate', 'http://img.gamespot.com/gamespot/shared/user/emblem_e3monster_s.jpg'); -- cgit v1.2.3