select id, username from user where username = and passhash = insert into bike set userId = , name = select last_insert_id() as id insert into point set userId = , name = , address = select last_insert_id() as id insert into ride set userId = , bikeId = ( select id from bike where id = and userId = ), routeId = ( select id from route where id = and userId = ), startTimestamp = , mileage = , timeElapsed = sec_to_time(), maximumSpeed = , notes = select last_insert_id() as id insert into route set userId = , name = , startPointId = ( select id from point where id = and userId = ), endPointId = ( select id from point where id = and userId = ), mapUrl = select last_insert_id() as id insert into stop set rideId = , pointId = ( select id from point where id = and userId = ), mileage = , timeElapsed = sec_to_time(), notes = select last_insert_id() as id select count(*) as `count` from user where username = insert into user set username = , passhash = , firstName = , lastName = , email = , accountType = select last_insert_id() as id insert into user_group set ownerId = , name = , groupType = select last_insert_id() as id insert into link_user_group set userId = , groupId = select id, name from bike where userId = order by name select id, name, address from point where userId = order by name select /* stats */ (sum(mileage) / sum(time_to_sec(rideTimeElapsed)) * 3600) as averageSpeed, max(maximumSpeed) as maximumSpeed, sum(mileage) as mileage, sum(time_to_sec(rideTimeElapsed)) as timeElapsed, count(*) as rideCount, /* timepoint */ cast(case when 'day' then cast(startTimestamp as date) when 'week' then date_add(cast(startTimestamp as date), interval -1 * (dayofweek(startTimestamp) - 1) day) when 'month' then date_add(cast(startTimestamp as date), interval -1 * (dayofmonth(startTimestamp) - 1) day) else startTimestamp end as datetime) as timePoint from ride_info where userId = and ( bikeId = routeId = dayofweek(startTimestamp) = userId = 1 = 1 ) group by timePoint order by timePoint select id, bikeId, routeId, startTimestamp, mileage, time_to_sec(rideTimeElapsed) as timeElapsed, maximumSpeed, notes, stopCount from ride_info where userId = order by startTimestamp desc select id, name, startPointId, endPointId, mapUrl from route where userId = order by name select id, rideId, pointId, mileage, time_to_sec(timeElapsed) as timeElapsed, notes from stop where rideId = and ( select userId from ride where id = stop.rideId ) = order by mileage select id, ownerId, name, groupType, ( select count(*) from link_user_group where groupId = user_group.id ) as memberCount, ( select count(*) from bike where userId in ( select userId from link_user_group where groupId = user_group.id ) ) as bikeCount, ( select count(*) from ride where userId in ( select userId from link_user_group where groupId = user_group.id ) ) as rideCount, ( select sum(mileage) from ride where userId in ( select userId from link_user_group where groupId = user_group.id ) ) as totalMileage, ( select sum(time_to_sec(timeElapsed)) from ride where userId in ( select userId from link_user_group where groupId = user_group.id ) ) as totalTimeElapsed, ( select (sum(mileage) / sum(time_to_sec(timeElapsed)) * 3600) from ride where userId in ( select userId from link_user_group where groupId = user_group.id ) ) as averageSpeed from user_group where groupType = 'public' or ownerId = or exists ( select * from link_user_group where groupId = user_group.id and userId = ) order by name select id, username, passhash, firstName, lastName, email, accountType, ( select count(*) from bike where userId = user.id ) as bikeCount, ( select count(*) from ride where userId = user.id ) as rideCount, ( select sum(mileage) from ride where userId = user.id ) as totalMileage, ( select sum(time_to_sec(timeElapsed)) from ride where userId = user.id ) as totalTimeElapsed, ( select (sum(mileage) / sum(time_to_sec(timeElapsed)) * 3600) from ride where userId = user.id ) as averageSpeed from user where 1 = 1 and (accountType in () or id = ) and id in () order by username update bike set name = where id = and userId = update point set name = , address = where id = and userId = update ride set bikeId = ( select id from bike where id = and userId = ), routeId = ( select id from route where id = and userId = ), startTimestamp = , mileage = , timeElapsed = sec_to_time(), maximumSpeed = , notes = where id = and userId = select id from stop where id = update route set name = , startPointId = ( select id from point where id = and userId = ), endPointId = ( select id from point where id = and userId = ), mapUrl = where id = and userId = update stop set pointId = ( select id from point where id = and userId = ), mileage = , timeElapsed = sec_to_time(), notes = where id = and rideId in ( select rideId from ride where userId = ) select passhash from user where id = update user set passhash = , firstName = , lastName = , email = , accountType = where id = and id = and passhash = update user_group set name = , groupType = where id = and ownerId = select last_insert_id() as id