How to delete data for all but a specific user from the PL/SQL database

[drmason789]

My Trixter X-Dream V1 bike was originally owned by a gym and all the user data is still in the database.

I want to get rid of all but mine. Is this possible?

[drmason789]

This answer relates to version 3 of the X-Dream software provided with the X-Dream V1 Bike, which stores user data in a PL/SQL database.

You can do this by issuing SQL commands.

These instructions are currently not as "polished" as they could be. If you are not confident to use instructions from elsewhere in this forum to back up (see #5) and restore (see #7) the database, and figure out yourself how to issue these SQL commands, you're recommended to not try. Better instructions may follow, and the facility may be included in a future release of the software provided in this repository.

Firstly, you should make a backup of the database otherwise you might end up having to start with a blank database. Instructions for this can be found in #5.
If this is not your first try, you may have temporary tables already present. Delete them:

drop table if exists specific_usersessions;
drop table if exists specific_ridesessions;
drop table if exists remaining_bikesessions;
drop table if exists specific_userdata;

Start by creating a temporary table with the users you want to delete in it. Change the 'USERNAME' to contain the capitalised username you want to preserve.

SELECT DISTINCT userdata_id as userdata_id INTO temporary table specific_userdata
FROM userdata 
WHERE username<>'USERNAME';

Next, if you haven't done it before, create an index on the sample database otherwise some instructions might take a long time:

CREATE INDEX sample_ridesession_id_idx 
  ON public.sample
  USING btree
  (ridesession_id);

Now collect the records that need to be deleted into temporary tables and do the actual deleting:

SELECT DISTINCT usersession_id, bikesession_id INTO temporary table specific_usersessions
FROM usersession us INNER JOIN specific_userdata sud on us.userdata_id=sud.userdata_id;

SELECT DISTINCT ridesession_id INTO temporary table specific_ridesessions
FROM ridesession rs INNER JOIN specific_usersessions sus on rs.usersession_id=sus.usersession_id;

delete from sample 
where ridesession_id in (select ridesession_id from specific_ridesessions);

delete from ghoststartpositions
where ridesession_id in (select ridesession_id from specific_ridesessions);

delete from ridesession
where ridesession_id in (select ridesession_id from specific_ridesessions);

delete from usersession 
where usersession_id in (select usersession_id from specific_usersessions);

-- A case where the same bikesession record was shared by 2 usersessions for different users
-- has been observed.
select bikesession_id into temporary table remaining_bikesessions
from usersession;

delete from bikesession 
where bikesession_id in (select bikesession_id from specific_usersessions) and
      bikesession_id not in (select bikesession_id from remaining_bikesessions);

delete from highscores where userdata_id in (select userdata_id from specific_userdata);
delete from laptimes where userdata_id in (select userdata_id from specific_userdata);
delete from durationscores where userdata_id in (select userdata_id from specific_userdata);
delete from maledurationscores where userdata_id in (select userdata_id from specific_userdata);
delete from femaledurationscores where userdata_id in (select userdata_id from specific_userdata);
delete from userdata where userdata_id in (select userdata_id from specific_userdata);

Finally, delete the temporary tables:

drop table if exists specific_usersessions;
drop table if exists specific_ridesessions;
drop table if exists remaining_bikesessions;
drop table if exists specific_userdata;

[drmason789]

On seeing this question, some might ask another question:

"Is there personal data from public gym members stored in the databases of the Trixter X-Dream PCs that are now in the possession of private users?"

Here's what the userdata table in the database stores:

Considering the username is entered via the 80s video game style "keyboard", entry of full identifying names is impractical.

I can't offer any legal advice on whether or not this is a problem.

You can simply delete the database. If it's not there, the Trixter X-Dream software simply starts over with a new one.