surfrock66
commented
8 years ago Lemme take a look a little later....I can't think of a way to recover now, but I just had a baby and am in the hospital and am a little scatterbrained! 😁
Closed aldodemi closed 7 years ago
surfrock66
commented
8 years ago Lemme take a look a little later....I can't think of a way to recover now, but I just had a baby and am in the hospital and am a little scatterbrained! 😁
aldodemi
commented
8 years ago Just think to your baby, database and a lot of other things can wait!!!!!!!!!!!!!!!!! :-)
surfrock66
commented
8 years ago Ok, so, I don't think there's a way to recover the data :(
This is a great point though; I just copied the table create script from econpy's repo, and it doesn't accommodate if there's a table there already. I never really considered a migration path from his install to mine, and unfortunately you paid the price for that, and I'm sorry.
I'm gonna update the table create scripts and maybe do a rename of existing tables, instead of a drop.
surfrock66
commented
8 years ago This commit should fix it, I tested it on a temp table:
https://github.com/surfrock66/torque/commit/f63ee7f5c0b0bd2e760e83828bb8d8fdce5b40d8
aldodemi
commented
8 years ago No, I’m sorry, my request is different.
I’ve not used your tablescript to recreate the main database, so my previous data are still in the database.
My problem is, simply, that all “old” record doesnt show, I think because they dont have a link in the key table, so my question was if there is a way to create missing information for these old record.
Thank
Aldo
Da: surfrock66 [mailto:notifications@github.com] Inviato: sabato 9 luglio 2016 05:40 A: surfrock66/torque torque@noreply.github.com Cc: aldodemi aldo.demicheli@fastwebnet.it; Author author@noreply.github.com Oggetto: Re: [surfrock66/torque] Litte help for old data recovery (#6)
Ok, so, I don't think there's a way to recover the data :(
This is a great point though; I just copied the table create script from econpy's repo, and it doesn't accommodate if there's a table there already. I never really considered a migration path from his install to mine, and unfortunately you paid the price for that, and I'm sorry.
I'm gonna update the table create scripts and maybe do a rename of existing tables, instead of a drop.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/surfrock66/torque/issues/6#issuecomment-231512731 , or mute the thread https://github.com/notifications/unsubscribe/ATQMVlZAdK9z3JNiYuVe4igP-IUuQ5lLks5qTxgjgaJpZM4JF4YC . https://github.com/notifications/beacon/ATQMVkVheR6VapoWcdjAyA4rRT6PxpO0ks5qTxgjgaJpZM4JF4YC.gif
surfrock66
commented
8 years ago Oh! My mistake, I fully admit to being a bit distracted. That's good that your data is still there.
Rather than having a separate keys table; econpy stored the field info as a comment in the raw_logs table. Run this in the mysql console to see the full list of info:
use torque; show full columns from raw_logs;
From there, you need to compare that to the output of this:
use torque; select * from torque_keys;
raw_logs.Field => torque_keys.id raw_logs.Comment => torque_keys.description raw_logs.Type => torque_keys.type
The "torque_keys.units", "torque_keys.min", and "torque_keys.max" variables are not super important; I'll dump mine below since they should be pretty universal.
You'll need to do an insert query into the "torque_keys" table for any variables that have a column in raw_logs, but aren't in torque_keys.
The big one here though, is "torque_keys.populated". If that's a 0, it'll not show up in the variable select list...this is my way of logging everything but only graphing relevant data. You can edit this in the mysql console with an update query, or from the /torque/pid_edit.php page.
Does that help?
mysql> use torque; select * from torque_keys;
Database changed
+-----------------+--------------------------------------------------+--------------+---------+-----------+------+-------+
| id | description | type | units | populated | min | max |
+-----------------+--------------------------------------------------+--------------+---------+-----------+------+-------+
| kff122e | 0-100kph Time | float | s | 0 | 0 | 16 |
| kff124f | 0-200kph Time | float | s | 0 | 0 | 16 |
| kff1277 | 0-30mph Time | float | s | 0 | 0 | 16 |
| kff122d | 0-60mph Time | float | s | 0 | 0 | 16 |
| kff122f | 1/4 mile Time | float | s | 0 | 0 | 30 |
| kff1230 | 1/8 mile Time | float | s | 0 | 0 | 16 |
| kff1264 | 100-0kph Time | float | s | 0 | 0 | 16 |
| kff1260 | 40-60mph Time | float | s | 0 | 0 | 16 |
| kff1265 | 60-0mph Time | float | s | 0 | 0 | 16 |
| kff125e | 60-120mph Time | float | s | 0 | 0 | 16 |
| kff1276 | 60-130mph Time | float | s | 0 | 0 | 16 |
| kff125f | 60-80mph Time | float | s | 0 | 0 | 16 |
| kff1261 | 80-100mph Time | float | s | 0 | 0 | 16 |
| kff1275 | 80-120kph Time | float | s | 0 | 0 | 16 |
| k47 | Absolute Throttle Position B | float | % | 1 | 0 | 100 |
| kff1223 | Acceleration Sensor (Total) | float | g | 0 | -1 | 1 |
| kff1220 | Acceleration Sensor (X Axis) | float | g | 1 | -1 | 1 |
| kff1221 | Acceleration Sensor (Y Axis) | float | g | 1 | -1 | 1 |
| kff1222 | Acceleration Sensor (Z Axis) | float | g | 1 | -1 | 1 |
| k49 | Accelerator Pedal Position D | float | % | 1 | 0 | 100 |
| k4a | Accelerator Pedal Position E | float | % | 1 | 0 | 100 |
| k4b | Accelerator Pedal Position F | float | % | 0 | 0 | 100 |
| kff124d | Air Fuel Ratio (Commanded) | float | NULL | 0 | 0 | 30 |
| kff1249 | Air Fuel Ratio (Measured) | float | NULL | 0 | NULL | NULL |
| k12 | Air Status | float | NULL | 0 | NULL | NULL |
| k46 | Ambient Air Temp | float | °C | 1 | -40 | 50 |
| kff1263 | Average Trip Speed (Whilst Moving Only) | float | km/h | 1 | 0 | 16 |
| kff1272 | Average Trip Speed (Whilst Stopped or Moving) | float | km/h | 1 | 0 | 16 |
| kff1270 | Barometer (On Android device) | float | mb | 0 | 800 | 1100 |
| k33 | Barometric Pressure (From Vehicle) | float | kPa | 0 | 0 | 255 |
| k3c | Catalyst Temperature (Bank 1 Sensor 1) | float | °C | 1 | 0 | 60 |
| k3e | Catalyst Temperature (Bank 1 Sensor 2) | float | °C | 0 | NULL | NULL |
| k3d | Catalyst Temperature (Bank 2 Sensor 1) | float | °C | 1 | 0 | 60 |
| k3f | Catalyst Temperature (Bank 2 Sensor 2) | float | °C | 0 | NULL | NULL |
| kff1258 | CO2 (Average) | float | g/km | 1 | 0 | 120 |
| kff1257 | CO2 (Instantaneous) | float | g/km | 0 | 0 | 120 |
| k44 | Commanded Equivalence Ratio (lambda) | float | NULL | 0 | 0 | 2 |
| kff126d | Cost per mile/km (Instant) | float | $/km | 0 | 0 | 100 |
| kff126e | Cost per mile/km (Trip) | float | $/km | 0 | 0 | 100 |
| kff126a | Distance to empty (Estimated) | float | km | 1 | 0 | 100 |
| k31 | Distance Travelled Since Codes Cleared | float | km | 1 | 0 | 100 |
| k21 | Distance Travelled With MIL/CEL Lit | float | km | 1 | 0 | 100 |
| k2c | EGR Commanded | float | NULL | 0 | NULL | NULL |
| k2d | EGR Error | float | NULL | 0 | NULL | NULL |
| k5 | Engine Coolant Temperature | float | °C | 1 | -40 | 120 |
| kff1273 | Engine kW (At the Wheels) | float | kW | 1 | 0 | 100 |
| k4 | Engine Load | float | % | 1 | 0 | 100 |
| k43 | Engine Load (Absolute) | float | % | 0 | 0 | 20000 |
| k5c | Engine Oil Temperature | float | °C | 0 | NULL | NULL |
| kc | Engine RPM | float | rpm | 1 | 0 | 10000 |
| k52 | Ethanol Fuel % | float | % | 0 | NULL | NULL |
| k32 | Evap System Vapor Pressure | float | Pa | 1 | 0 | 1000 |
| k78 | Exhaust Gas Temperature 1 | float | °C | 0 | NULL | NULL |
| k79 | Exhaust Gas Temperature 2 | float | °C | 0 | NULL | NULL |
| kff125c | Fuel Cost (Trip) | float | $ | 0 | 0 | 100 |
| kff125d | Fuel Flow Rate/Hour | float | l/hr | 1 | 0 | 100 |
| kff125a | Fuel Flow Rate/Minute | float | cc/min | 1 | 0 | 100 |
| k2f | Fuel Level (From Engine ECU) | float | % | 1 | 0 | 100 |
| ka | Fuel Pressure | float | NULL | 0 | NULL | NULL |
| k23 | Fuel Rail Pressure | float | NULL | 0 | NULL | NULL |
| k22 | Fuel Rail Pressure (Relative to Manifold Vacuum) | float | NULL | 0 | NULL | NULL |
| kff126b | Fuel Remaining (Calculated From Vehicle Profile) | float | % | 1 | 0 | 100 |
| k3 | Fuel Status | float | NULL | 0 | NULL | NULL |
| k7 | Fuel Trim Bank 1 Long Term | float | % | 1 | -25 | 25 |
| k14 | Fuel Trim Bank 1 Sensor 1 | float | % | 1 | -100 | 100 |
| k15 | Fuel Trim Bank 1 Sensor 2 | float | % | 0 | NULL | NULL |
| k16 | Fuel Trim Bank 1 Sensor 3 | float | % | 0 | NULL | NULL |
| k17 | Fuel Trim Bank 1 Sensor 4 | float | % | 0 | NULL | NULL |
| k6 | Fuel Trim Bank 1 Short Term | float | % | 1 | -25 | 25 |
| k9 | Fuel Trim Bank 2 Long Term | float | % | 1 | -25 | 25 |
| k18 | Fuel Trim Bank 2 Sensor 1 | float | % | 1 | -100 | 100 |
| k19 | Fuel Trim Bank 2 Sensor 2 | float | % | 0 | NULL | NULL |
| k1a | Fuel Trim Bank 2 Sensor 3 | float | % | 0 | NULL | NULL |
| k1b | Fuel Trim Bank 2 Sensor 4 | float | % | 0 | NULL | NULL |
| k8 | Fuel Trim Bank 2 Short Term | float | % | 1 | -25 | 25 |
| kff1271 | Fuel Used (Trip) | float | l | 1 | 0 | 100 |
| kff1239 | GPS Accuracy | float | m | 1 | 0 | 100 |
| kff1010 | GPS Altitude | float | m | 1 | 0 | 100 |
| kff123b | GPS Bearing | float | ° | 1 | 0 | 360 |
| kff1006 | GPS Latitude | float | ° | 1 | 0 | 100 |
| kff1005 | GPS Longitude | float | ° | 1 | 0 | 100 |
| kff123a | GPS Satellites | float | NULL | 1 | 0 | 24 |
| kff1237 | GPS vs OBD Speed Difference | float | km/h | 0 | 0 | 10 |
| kff1226 | Horsepower (At the Wheels) | float | hp | 1 | 0 | 100 |
| kf | Intake Air Temperature | float | °C | 1 | -40 | 60 |
| kb | Intake Manifold Pressure | float | kPa | 1 | 0 | 255 |
| kff1203 | Kilometers Per Litre (Instant) | float | kpl | 0 | 0 | 100 |
| kff5202 | Kilometers Per Litre (Long Term Average) | float | kpl | 0 | 0 | 100 |
| kff1207 | Litres Per 100 Kilometer (Instant) | float | l/100km | 0 | 0 | 100 |
| kff5203 | Litres Per 100 Kilometer (Long Term Average) | float | l/100km | 0 | 0 | 100 |
| k10 | Mass Air Flow Rate | float | NULL | 1 | NULL | NULL |
| kff1201 | Miles Per Gallon (Instant) | float | mpg | 1 | 0 | 101 |
| kff5201 | Miles Per Gallon (Long Term Average) | float | mpg | 0 | 0 | 100 |
| k24 | O2 Sensor1 Equivalence Ratio | float | NULL | 0 | NULL | NULL |
| k34 | O2 Sensor1 Equivalence Ratio (Alternate) | float | NULL | 0 | NULL | NULL |
| kff1240 | O2 Sensor1 Wide-range Voltage | float | V | 0 | NULL | NULL |
| k25 | O2 Sensor2 Equivalence Ratio | float | NULL | 0 | NULL | NULL |
| kff1241 | O2 Sensor2 Wide-range Voltage | float | V | 0 | NULL | NULL |
| k26 | O2 Sensor3 Equivalence Ratio | float | NULL | 0 | NULL | NULL |
| kff1242 | O2 Sensor3 Wide-range Voltage | float | V | 0 | NULL | NULL |
| k27 | O2 Sensor4 Equivalence Ratio | float | NULL | 0 | NULL | NULL |
| kff1243 | O2 Sensor4 Wide-range Voltage | float | V | 0 | NULL | NULL |
| k28 | O2 Sensor5 Equivalence Ratio | float | NULL | 0 | NULL | NULL |
| kff1244 | O2 Sensor5 Wide-range Voltage | float | V | 0 | NULL | NULL |
| k29 | O2 Sensor6 Equivalence Ratio | float | NULL | 0 | NULL | NULL |
| kff1245 | O2 Sensor6 Wide-range Voltage | float | V | 0 | NULL | NULL |
| k2a | O2 Sensor7 Equivalence Ratio | float | NULL | 0 | NULL | NULL |
| kff1246 | O2 Sensor7 Wide-range Voltage | float | V | 0 | NULL | NULL |
| k2b | O2 Sensor8 Equivalence Ratio | float | NULL | 0 | NULL | NULL |
| kff1247 | O2 Sensor8 Wide-range Voltage | float | V | 0 | NULL | NULL |
| kff1214 | O2 Volts Bank 1 Sensor 1 | float | V | 0 | 0 | 1 |
| kff1215 | O2 Volts Bank 1 Sensor 2 | float | V | 0 | 0 | 1 |
| kff1216 | O2 Volts Bank 1 Sensor 3 | float | V | 0 | NULL | NULL |
| kff1217 | O2 Volts Bank 1 Sensor 4 | float | V | 0 | NULL | NULL |
| kff1218 | O2 Volts Bank 2 Sensor 1 | float | V | 0 | 0 | 1 |
| kff1219 | O2 Volts Bank 2 Sensor 2 | float | V | 0 | 0 | 1 |
| kff121a | O2 Volts Bank 2 Sensor 3 | float | V | 0 | NULL | NULL |
| kff121b | O2 Volts Bank 2 Sensor 4 | float | V | 0 | NULL | NULL |
| k5a | Relative Accelerator Pedal Position | float | % | 0 | NULL | NULL |
| k45 | Relative Throttle Position | float | % | 1 | 0 | 100 |
| k1f | Run Time Since Engine Start | float | s | 1 | 0 | 100 |
| session | Session ID | varchar(255) | NULL | 1 | NULL | NULL |
| kff1001 | Speed (GPS) | float | km/h | 0 | 0 | 140 |
| kd | Speed (OBD) | float | km/h | 1 | 0 | 140 |
| k11 | Throttle Position (Manifold) | float | % | 1 | 0 | 100 |
| kff124a | Tilt (x) | float | NULL | 0 | NULL | NULL |
| kff124b | Tilt (y) | float | NULL | 0 | NULL | NULL |
| kff124c | Tilt (z) | float | NULL | 0 | NULL | NULL |
| time | Timestamp | varchar(255) | NULL | 1 | NULL | NULL |
| ke | Timing Advance | float | ° | 1 | -64 | 63 |
| kff1225 | Torque | float | ft-lb | 1 | 0 | 100 |
| kfe1805 | Transmission Temperature (Method 1) | float | °C | 1 | NULL | NULL |
| kb4 | Transmission Temperature (Method 2) | float | °C | 1 | NULL | NULL |
| kff1206 | Trip Average KPL | float | kpl | 0 | 0 | 100 |
| kff1208 | Trip Average Litres/100 KM | float | l/100km | 0 | 0 | 100 |
| kff1205 | Trip Average MPG | float | mpg | 1 | 0 | 100 |
| kff1204 | Trip Distance | float | km | 1 | 0 | 200 |
| kff120c | Trip Distance (Stored in Vehicle Profile) | float | km | 0 | 0 | 100 |
| kff1266 | Trip Time (Since Journey Start) | float | s | 1 | 0 | 100 |
| kff1268 | Trip Time (Whilst Moving) | float | s | 1 | 0 | 100 |
| kff1267 | Trip Time (Whilst Stationary) | float | s | 1 | 0 | 100 |
| kff1202 | Turbo Boost & Vacuum Gauge | float | psi | 1 | -20 | 20 |
| k42 | Voltage (Control Module) | float | V | 0 | 0 | 16 |
| kff1238 | Voltage (OBD Adapter) | float | V | 0 | 0 | 16 |
| kff1269 | Volumetric Efficiency (Calculated) | float | % | 0 | NULL | NULL |
| kff1007 | Â | float | NULL | 0 | NULL | NULL |
| kff1280 | kff1280 | varchar(255) | NULL | 1 | NULL | NULL |
| profileVe | profileVe | float | NULL | 1 | NULL | NULL |
| profileWeight | profileWeight | float | NULL | 1 | NULL | NULL |
| profileFuelCost | profileFuelCost | float | NULL | 1 | NULL | NULL |
| profileFuelType | profileFuelType | varchar(255) | NULL | 1 | NULL | NULL |
+-----------------+--------------------------------------------------+--------------+---------+-----------+------+-------+
151 rows in set (0.06 sec)
In my raw_logs table I have my old trip that doen't have a corresponding record in the sessions table so I can not see it in the web gui. So all I need I think, in my case, is how to create sessions entry from my old trip record.
surfrock66
commented
8 years ago Ok, good. Even better.
A lot of the profile information isn't recorded very well, so let's call those fields optional. To be honest, they show up 50% of the time, and I haven't figured out why...every so often I just update all entries in the session table. To that end, let's consider the following values static as long as you're using the same profile:
eml profileName profileFuelType profileWeight profileVe profileFuelCost
Run this query:
use torque; select distinct session, id, v, min(time) as time, min(time) as timestart, max(time) as timeend, count(time) as sessionsize from raw_logs group by session, id, v;
I took the results of that, dumped it into libreoffice calc, then did a concatenate on every row to create an insert string that also put in the static values. Something like "CONCATENATE("insert into sessions (v, id, session, time, eml, profileName, timestart, timeend, sessionsize, profileFuelType, profileWeight, profileVe, profileFuelCost) VALUES(",A2,",",B2,",",C2,",",D2,",'aldodemi@domain.com','CoolCarProfile',",E2,",",F2,",",G2,",'4','2000','85','.6')")" then dragged it down.
Thats an example, substitute your own vals of course. I'm sure there is a more graceful way to that avoiding the work in office, but it'll work.
I've used the "econpy" version till last week, now that I've found this new version I started use it with the previous database adding the two new tables (create_torque_keys_table.sql and create_torque_log_table.sql). New trips are recorded and displayed correctly the old ones obviously no. There is a way to recover also old trips? Thank in advance for your help.