CREATE TABLE `point` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(50) collate utf8_unicode_ci NOT NULL, `address` text collate utf8_unicode_ci, PRIMARY KEY (`id`), UNIQUE KEY `uk_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CREATE TABLE `route` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(50) collate utf8_unicode_ci NOT NULL, `startPointId` int(10) unsigned NOT NULL, `endPointId` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_name` (`name`), KEY `k_startPointId` (`startPointId`), KEY `k_stopPointId` (`endPointId`), CONSTRAINT `route_ibfk_1` FOREIGN KEY (`startPointId`) REFERENCES `point` (`id`), CONSTRAINT `route_ibfk_3` FOREIGN KEY (`endPointId`) REFERENCES `point` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | CREATE TABLE `bike` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(50) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CREATE TABLE `ride` ( `id` int(10) unsigned NOT NULL auto_increment, `bikeId` int(10) unsigned NOT NULL, `routeId` int(10) unsigned NOT NULL, `startTimestamp` datetime NOT NULL, `mileage` float NOT NULL, `timeElapsed` time NOT NULL, `maximumSpeed` float default NULL, `notes` text collate utf8_unicode_ci, PRIMARY KEY (`id`), KEY `k_routeId` (`routeId`), KEY `k_bikeId` (`bikeId`), CONSTRAINT `ride_ibfk_1` FOREIGN KEY (`routeId`) REFERENCES `route` (`id`), CONSTRAINT `ride_ibfk_2` FOREIGN KEY (`bikeId`) REFERENCES `bike` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CREATE TABLE `stop` ( `id` int(10) unsigned NOT NULL auto_increment, `rideId` int(10) unsigned NOT NULL, `pointId` int(10) unsigned NOT NULL, `mileage` float default NULL, `timeElapsed` time default NULL, `notes` text collate utf8_unicode_ci, PRIMARY KEY (`id`), KEY `k_pointId` (`pointId`), KEY `k_rideId` (`rideId`), CONSTRAINT `stop_ibfk_1` FOREIGN KEY (`pointId`) REFERENCES `point` (`id`), CONSTRAINT `stop_ibfk_2` FOREIGN KEY (`rideId`) REFERENCES `ride` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `ride_info` AS select `ride`.`id` AS `id`, `ride`.`bikeId` AS `bikeId`, ( select `bike`.`name` AS `name` from `bike` where (`bike`.`id` = `ride`.`bikeId`) ) AS `bikeName`, `ride`.`routeId` AS `routeId`, `route`.`name` AS `routeName`, ( select `point`.`name` AS `name` from `point` where (`point`.`id` = `route`.`startPointId`) ) AS `startPoint`, ( select `point`.`name` AS `name` from `point` where (`point`.`id` = `route`.`endPointId`) ) AS `endPoint`, `ride`.`startTimestamp` AS `startTimestamp`, ( `ride`.`startTimestamp` + interval (time_to_sec(`ride`.`timeElapsed`) + ifnull(( select sum(time_to_sec(`stop`.`timeElapsed`)) AS `stopage` from `stop` where (`stop`.`rideId` = `ride`.`id`) ),0)) second ) AS `endTimestamp`, cast((round((`ride`.`mileage` * 100),0) / 100) as decimal) AS `mileage`, `ride`.`timeElapsed` AS `rideTimeElapsed`, cast(ifnull(( select sum(`stop`.`timeElapsed`) AS `sum(timeElapsed)` from `stop` where (`stop`.`rideId` = `ride`.`id`) ),0) as time) AS `stopTimeElapsed`, `ride`.`maximumSpeed` AS `maximumSpeed`, cast((round((((`ride`.`mileage` / time_to_sec(`ride`.`timeElapsed`)) * 3600) * 100),0) / 100) as decimal) AS `averageSpeed`, ( select count(0) AS `count(*)` from `stop` where (`stop`.`rideId` = `ride`.`id`) ) AS `stopCount`, `ride`.`notes` AS `notes` from `ride` left outer join `route` on `ride`.`routeId` = `route`.`id` create table user ( `id` int(10) unsigned NOT NULL auto_increment, username varchar(50) not null, passhash char(32) not null, firstName varchar(50) not null, lastName varchar(50) not null, email varchar(255) not null, accountType enum('public', 'protected', 'private') not null default 'public', primary key (id), unique key uk_username (username) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci insert into user values (null, 'barneyb', '1c5386cb9ac81b3f6f225f00f4019f74', 'Barney', 'Boisvert', 'bboisvert@gmail.com', 'public' ) alter table ride add userId int unsigned after id alter table point add userId int unsigned after id alter table bike add userId int unsigned after id alter table route add userId int unsigned after id update ride set userId = (select min(id) from user) update bike set userId = (select min(id) from user) update point set userId = (select min(id) from user) update route set userId = (select min(id) from user) alter table ride modify userId int unsigned not null, add foreign key (userId) references user (id) alter table point modify userId int unsigned not null, add foreign key (userId) references user (id) alter table bike modify userId int unsigned not null, add foreign key (userId) references user (id) alter table route modify userId int unsigned not null, add foreign key (userId) references user (id) drop view if exists ride_info create ALGORITHM=UNDEFINED SQL SECURITY INVOKER view `ride_info` AS select ride.id AS id, ride.userId, ( select username as username from user where id = ride.userId ) as username, ride.routeId, route.name, ride.bikeId, ( select name AS name from bike where id = ride.bikeId ) AS bikeName, ( select name AS name from point where id = route.startPointId ) AS startPoint, ( select name AS name from point where id = route.endPointId ) AS endPoint, ride.startTimestamp, ( startTimestamp + interval (time_to_sec(timeElapsed) + ifnull(( select sum(time_to_sec(timeElapsed)) AS stopage from stop where rideId = ride.id ),0)) second ) AS endTimestamp, cast((round((mileage * 100),0) / 100) as decimal) AS mileage, ride.timeElapsed AS rideTimeElapsed, cast(ifnull(( select sum(timeElapsed) AS totalTimeElapsed from stop where rideId = ride.id ),0) as time) AS stopTimeElapsed, ride.maximumSpeed, cast((round((((mileage / time_to_sec(timeElapsed)) * 3600) * 100),0) / 100) as decimal) AS averageSpeed, ( select count(0) AS `cnt` from stop where rideId = ride.id ) AS stopCount, ride.notes from ride left outer join route on ride.routeId = route.id create table link_associate ( userId int unsigned not null, associateId int unsigned not null, primary key (userId, associateId), key k_associateId (associateId), foreign key (userId) references user (id) on delete cascade, foreign key (associateId) references user (id) on delete cascade ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci alter table point drop key uk_name, add unique key uk_userId_name (userId, name) alter table route drop key uk_name, add unique key uk_userId_name (userId, name) alter table bike drop key uk_name, add unique key uk_userId_name (userId, name) drop view if exists ride_info create ALGORITHM=UNDEFINED SQL SECURITY INVOKER view `ride_info` AS select ride.id AS id, ride.userId, ( select username as username from user where id = ride.userId ) as username, ride.routeId, route.name as routeName, ride.bikeId, ( select name AS name from bike where id = ride.bikeId ) AS bikeName, ( select name AS name from point where id = route.startPointId ) AS startPoint, ( select name AS name from point where id = route.endPointId ) AS endPoint, ride.startTimestamp, ( startTimestamp + interval (time_to_sec(timeElapsed) + ifnull(( select sum(time_to_sec(timeElapsed)) AS stopage from stop where rideId = ride.id ),0)) second ) AS endTimestamp, cast((round((mileage * 100),0) / 100) as decimal) AS mileage, ride.timeElapsed AS rideTimeElapsed, cast(ifnull(( select sum(timeElapsed) AS totalTimeElapsed from stop where rideId = ride.id ),0) as time) AS stopTimeElapsed, ride.maximumSpeed, cast((round((((mileage / time_to_sec(timeElapsed)) * 3600) * 10),0) / 10) as decimal) AS averageSpeed, ( select count(0) AS `cnt` from stop where rideId = ride.id ) AS stopCount, ride.notes from ride left outer join route on ride.routeId = route.id drop view if exists ride_info create ALGORITHM=UNDEFINED SQL SECURITY INVOKER view `ride_info` AS select ride.id AS id, ride.userId, ( select username as username from user where id = ride.userId ) as username, ride.routeId, route.name as routeName, ride.bikeId, ( select name AS name from bike where id = ride.bikeId ) AS bikeName, ( select name AS name from point where id = route.startPointId ) AS startPoint, ( select name AS name from point where id = route.endPointId ) AS endPoint, ride.startTimestamp, ( startTimestamp + interval (time_to_sec(timeElapsed) + ifnull(( select sum(time_to_sec(timeElapsed)) AS stopage from stop where rideId = ride.id ),0)) second ) AS endTimestamp, cast((round((mileage * 100),0) / 100) as decimal) AS mileage, ride.timeElapsed AS rideTimeElapsed, cast(ifnull(( select sum(timeElapsed) AS totalTimeElapsed from stop where rideId = ride.id ),0) as time) AS stopTimeElapsed, ride.maximumSpeed, cast((round((((mileage / time_to_sec(timeElapsed)) * 3600) * 10),0) / 10) as decimal) AS averageSpeed, ( select count(0) AS `cnt` from stop where rideId = ride.id ) AS stopCount, ride.notes from ride left outer join route on ride.routeId = route.id drop table link_associate create table user_group ( id int(10) unsigned NOT NULL auto_increment, name varchar(50) not null, ownerId int unsigned not null, primary key (id), unique key uk_name (name), key k_ownerId (ownerId), foreign key (ownerId) references user (id) ) engine=InnoDB default charset=utf8 collate=utf8_unicode_ci create table link_user_group ( userId int unsigned not null, groupId int unsigned not null, primary key (userId, groupId), key k_groupId (groupId), foreign key (userId) references user (id) on delete cascade, foreign key (groupId) references user_group (id) on delete cascade ) engine=InnoDB default charset=utf8 collate=utf8_unicode_ci alter table route add mapUrl text drop view if exists ride_info create ALGORITHM=UNDEFINED SQL SECURITY INVOKER view `ride_info` AS select ride.id AS id, ride.userId, ( select username as username from user where id = ride.userId ) as username, ride.routeId, route.name as routeName, route.mapUrl as routeMapUrl, ride.bikeId, ( select name AS name from bike where id = ride.bikeId ) AS bikeName, ( select name AS name from point where id = route.startPointId ) AS startPoint, ( select name AS name from point where id = route.endPointId ) AS endPoint, ride.startTimestamp, ( startTimestamp + interval (time_to_sec(timeElapsed) + ifnull(( select sum(time_to_sec(timeElapsed)) AS stopage from stop where rideId = ride.id ),0)) second ) AS endTimestamp, cast((round((mileage * 100),0) / 100) as decimal) AS mileage, ride.timeElapsed AS rideTimeElapsed, cast(ifnull(( select sum(timeElapsed) AS totalTimeElapsed from stop where rideId = ride.id ),0) as time) AS stopTimeElapsed, ride.maximumSpeed, cast((round((((mileage / time_to_sec(timeElapsed)) * 3600) * 10),0) / 10) as decimal) AS averageSpeed, ( select count(0) AS `cnt` from stop where rideId = ride.id ) AS stopCount, ride.notes from ride left outer join route on ride.routeId = route.id alter table user_group add groupType enum('public', 'protected', 'private') not null default 'public' alter table user_group modify groupType enum('public', 'private') not null default 'public'