1 -- -*- mode: sql; -*- 2 3 CREATE TABLE "Prospects" ( 4 "id" INTEGER UNIQUE, 5 "email" TEXT NOT NULL, 6 "date_time" TEXT NOT NULL, 7 PRIMARY KEY("id" AUTOINCREMENT) 8 ); 9 10 CREATE TABLE "Users" ( 11 "id" INTEGER UNIQUE, 12 "username" TEXT NOT NULL, 13 "password_hash" BLOB NOT NULL, 14 "real_first_name" TEXT, 15 "real_last_name" TEXT, 16 "date_of_birth" TEXT, 17 "last_login_date_time" TEXT, 18 "account_register_date_time" TEXT, 19 PRIMARY KEY("id" AUTOINCREMENT) 20 ); 21 22 INSERT INTO Users VALUES ( 23 '1', 24 'testMFT', 25 'testMFTUserNeedsNoPassword!', 26 'Testimothy', 27 'McTestington', 28 '1996-06-14', 29 '2024-05-09T14:34:12', 30 '2024-05-09T14:31:12' 31 ); 32 33 CREATE TABLE "Muscle_Groups" ( 34 "id" INTEGER NOT NULL UNIQUE, 35 "english_name" TEXT NOT NULL UNIQUE, 36 "french_name" TEXT NOT NULL UNIQUE, 37 "function" TEXT, 38 PRIMARY KEY("id" AUTOINCREMENT) 39 ); 40 41 -- Upper body 42 INSERT INTO Muscle_Groups ("id", "english_name", "french_name", "function") VALUES 43 ('1', 'neck', 'cou', NULL), 44 ('2', 'upper back', 'dos supérieur', NULL), 45 ('3', 'shoulder', 'épaule', NULL), 46 ('4', 'chest', 'torse', NULL), 47 ('5', 'triceps', 'triceps', NULL), 48 ('6', 'biceps', 'biceps', NULL), 49 ('7', 'forearm', 'avant-bras', NULL); 50 51 -- Core 52 INSERT INTO Muscle_Groups ("id", "english_name", "french_name", "function") VALUES 53 ('8', 'abdominals', 'abdominaux', NULL), 54 ('9', 'lower back', 'dos inférieur', NULL), 55 ('10', 'hip', 'hanche', NULL), 56 ('11', 'glutes', 'fessier', NULL); 57 58 -- Lower body 59 INSERT INTO Muscle_Groups ("id", "english_name", "french_name", "function") VALUES 60 ('12', 'quad', 'quadriceps', 'extends the knee'), 61 ('13', 'hamstring', 'ischio-jambier', 'flexes the knee and extends the hip'), 62 ('14', 'calf', 'mollet', NULL); 63 64 CREATE TABLE "Muscles" ( 65 "id" INTEGER NOT NULL UNIQUE, 66 "muscle_group_id" INTEGER NOT NULL, 67 "latin_name" TEXT NOT NULL UNIQUE, 68 "english_name" TEXT, 69 "function" TEXT, 70 FOREIGN KEY("muscle_group_id") REFERENCES Muscle_Groups("id"), 71 PRIMARY KEY("id" AUTOINCREMENT) 72 ); 73 74 -- Upper body 75 INSERT INTO Muscles ("muscle_group_id", "latin_name", "english_name", "function") VALUES 76 ('2', 77 'latissimus dorsi', 78 'lats', 79 'adducts and extends the shoulder' 80 ), 81 ('2', 82 'trapezius', 83 'trap', 84 'assists in moving and stabilizing the shoulder blades' 85 ), 86 ('3', 87 'deltoideus', 88 'delts', 89 NULL 90 ), 91 ('4', 92 'pectoralis major', 93 'pecs', 94 'performs shoulder flexion and adduction' 95 ), 96 ('4', 97 'pectorali minor', 98 'pectoral', 99 'plays a role in stabilizing the shoulder blade and assists in deep breathing' 100 ), 101 ('5', 102 'triceps brachii', 103 'triceps', 104 NULL 105 ), 106 ('6', 107 'biceps brachii', 108 'biceps', 109 NULL 110 ), 111 ('7', 112 'extensor carpi radialias brevis', 113 NULL, 114 'Responsible for extending the wrist and abducting the hand' 115 ), 116 ('7', 117 'brachio radialis', 118 NULL, 119 'Responsible for flexing the forearm at the elbow joint' 120 ), 121 ('7', 122 'flexor carpi radialis', 123 NULL, 124 'Responsible for flexing the wrist and abducting the hand' 125 ), 126 ('7', 127 'extensor carpi ulnaris', 128 NULL, 129 'Responsible for extending the wrist and adducting the hand' 130 ), 131 ('7', 132 'flexor carpi ulnaris', 133 NULL, 134 'Responsible for flexing the wrist and adducting the hand' 135 ), 136 ('7', 137 'extensor carpi radialis longus', 138 NULL, 139 'Responsible for extending the wrist and abducting the hand' 140 ); 141 142 -- Core 143 INSERT INTO Muscles ("muscle_group_id", "latin_name", "english_name", "function") VALUES 144 ('8', 145 'abdominalis', 146 'abs', 147 'wraps around the spine for protection and stability' 148 ), 149 ('8', 150 'obliquus externus abdominis', 151 'external oblique', 152 NULL 153 ), 154 ('8', 155 'rectus abdominis', 156 'six-pack', 157 NULL 158 ), 159 ('8', 160 'obliquus internus abdominis', 161 'internal oblique', 162 'Located on the front and side of the abdomen' 163 ), 164 ('9', 165 'erector spinae', 166 'erectors', 167 'extends the vertebral column and ensures maintaining an upright posture' 168 ), 169 ('11', 170 'gluteus maximus', 171 NULL, 172 'performs hip extension, outward rotation, and abduction' 173 ), 174 ('11', 175 'gluteus medius', 176 NULL, 177 'plays a role in hip abduction and medial rotation' 178 ), 179 ('11', 180 'gluteus minimus', 181 NULL, 182 'assists in hip abduction and medial rotation, works with the gluteus medius' 183 ); 184 185 -- Lower body 186 INSERT INTO Muscles ("muscle_group_id", "latin_name", "english_name", "function") VALUES 187 ('12', 188 'quadriceps femoris', 189 'quad', 190 NULL 191 ), 192 ('13', 193 'biceps femoris', 194 'hamstrings', 195 NULL 196 ), 197 ('13', 198 'semitendinosus', 199 'hamstrings', 200 NULL 201 ), 202 ('13', 203 'semimembranosus', 204 'hamstrings', 205 NULL 206 ), 207 ('14', 208 'gastrocnemius', 209 NULL, 210 'performs plantar flexion of the foot (pointing toes)' 211 ), 212 ('14', 213 'soleus', 214 NULL, 215 'assists the gastrocnemius in plantar flexion and helps maintain posture' 216 ); 217 218 CREATE TABLE "Exercises" ( 219 "id" INTEGER NOT NULL UNIQUE, 220 "primary_muscle_group_id" INTEGER NOT NULL, 221 "english_name" TEXT NOT NULL, 222 "french_name" TEXT, 223 FOREIGN KEY("primary_muscle_group_id") REFERENCES Muscle_Groups("id"), 224 PRIMARY KEY("id" AUTOINCREMENT) 225 ); 226 227 INSERT INTO Exercises ("primary_muscle_group_id", "english_name", "french_name") VALUES 228 ('02', 'cable row', 'rame avec cable'), 229 ('02', 'deadlift', 'soulevé de terre'), 230 ('02', 'lat pulldown', NULL), 231 ('02', 'pull up', 'traction'), 232 ('03', 'shoulder press (dumbell)', NULL), 233 ('04', 'bench press', 'développé couché'), 234 ('04', 'decline bench press', 'développé couché, incliné négatif'), 235 ('04', 'incline bench press', 'développé couché, incliné positif'), 236 ('05', 'tricep extension', 'extension triceps'), 237 ('06', 'bicep curl', 'flexion biceps'), 238 ('06', 'hammer curl', 'curl marteau'), 239 ('09', 'snatch', 'arraché'), 240 ('12', 'barbell squat', 'flexion sur jambes avec barre'), 241 ('12', 'clean and jerk', 'épaulé-jeté'), 242 ('12', 'goblet squat', NULL), 243 ('12', 'leg extension', NULL), 244 ('13', 'leg curl', NULL); 245 246 CREATE TABLE "Workouts" ( 247 "id" INTEGER UNIQUE, 248 "user_id" INTEGER NOT NULL, 249 "date" TEXT NOT NULL, 250 "primary_muscle_group_id" INTEGER, 251 FOREIGN KEY("user_id") REFERENCES Users("id"), 252 PRIMARY KEY("id" AUTOINCREMENT) 253 ); 254 255 CREATE TABLE "Sets" ( 256 "id" INTEGER NOT NULL UNIQUE, 257 "workout_id" INTEGER NOT NULL, 258 "exercise_id" INTEGER NOT NULL, 259 "set_group" INTEGER NOT NULL, 260 "set_number" INTEGER NOT NULL, 261 "reps" INTEGER NOT NULL, 262 "load" INTEGER, 263 "rpe" INTEGER, 264 "rest_after" NUMERIC, 265 FOREIGN KEY("workout_id") REFERENCES Workouts("id"), 266 FOREIGN KEY("exercise_id") REFERENCES Exercises("id"), 267 PRIMARY KEY("id" AUTOINCREMENT) 268 ); 269 270 CREATE TABLE "Weights" ( 271 "id" INTEGER UNIQUE, 272 "user_id" INTEGER NOT NULL, 273 "date" TEXT NOT NULL, 274 "value" NUMERIC NOT NULL, 275 PRIMARY KEY("id" AUTOINCREMENT) 276 ); 277 278 -- Local Variables: 279 -- sql-product: sqlite 280 -- End: 281