24.02.2015, 10:40
I'm saving alot of info about vehicles in one table.
It all works fine, except the last column which holds all possible mods on a vehicle has multiple values stored in one column like this:
Should I remove that last column and create a separate table for it, like this:
And load this table separately?
I was loading it all into this enum structure:
Using this function:
It was only one line (see the sscanf-line) to load all values from that single column, separate them using sscanf and put them all at once in the proper enum-field in the array.
If I would need to load the second table separately, it takes alot more code to read the same data, as I need to loop through all rows, check the vehicle-model, load the component and store it in a free index in the same enum under "ValidComponents".
Alot more work to get the same result and seems like a waste.
Also, the second table has 1750 rows to hold the same data as the ValidComponents column in the first table.
The access-levels will have the same "problem" as it was meant to put multiple values in that column as well.
And later on, for my job-system, it would also have such columns where multiple values are stored into one single column.
Splitting it all up into separate columns, separate loading functions, and whatever else, only makes it more complicated.
The data is meant to be stored together and I tried to keep all related values into one single table, but I didn't want to add 30 separate columns either to store 30 possible components on each vehiclemodel.
It all works fine, except the last column which holds all possible mods on a vehicle has multiple values stored in one column like this:
pawn Code:
+----+-------------+-------+-------+-------+---------+-------------+------------+---------------------+---------------+----------+--------------+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| ID | Name | Class | Model | Price | MaxFuel | Consumption | RefuelTime | RefuelLitersPerStep | MaxPassengers | MaxCargo | AccessLevels | Disabled | ValidComponents |
+----+-------------+-------+-------+-------+---------+-------------+------------+---------------------+---------------+----------+--------------+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| 0 | Landstalker | 7 | 400 | 50000 | 72 | 0.08 | 25 | 0.72 | 3 | 0 | 0 | 0 | 1008-1009-1010-1013-1018-1019-1020-1021-1024-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 1 | Bravura | 11 | 401 | 50000 | 51 | 0.0566667 | 20 | 0.6375 | 1 | 0 | 0 | 0 | 1001-1003-1004-1005-1006-1007-1008-1009-1010-1013-1019-1020-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098-1143-1145 |
| 2 | Buffalo | 12 | 402 | 50000 | 53 | 0.0588889 | 20 | 0.6625 | 1 | 0 | 0 | 0 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1087-1096-1097-1098 |
| 3 | Linerunner | 5 | 403 | 50000 | 757 | 0.841111 | 25 | 7.57 | 1 | 0 | 0 | 0 | 1008-1009-1010 |
| 4 | Perenniel | 13 | 404 | 50000 | 60 | 0.0666667 | 20 | 0.75 | 3 | 0 | 0 | 0 | 1000-1002-1007-1008-1009-1010-1013-1016-1019-1020-1021-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 5 | Sentinel | 11 | 405 | 50000 | 90 | 0.1 | 20 | 1.125 | 3 | 0 | 0 | 0 | 1000-1001-1008-1009-1010-1014-1018-1019-1020-1021-1023-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 6 | Dumper | 15 | 406 | 50000 | 480 | 0.533333 | 25 | 4.8 | 1 | 0 | 0 | 0 | 0 |
| 7 | Firetruck | 9 | 407 | 50000 | 1893 | 2.10333 | 30 | 15.775 | 0 | 0 | 0 | 0 | 1008-1009-1010 |
| 8 | Trashmaster | 5 | 408 | 50000 | 189 | 0.21 | 25 | 1.89 | 1 | 0 | 0 | 0 | 1008-1009-1010 |
| 9 | Stretch | 15 | 409 | 50000 | 75 | 0.0833333 | 25 | 0.75 | 0 | 0 | 0 | 0 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098
pawn Code:
+-------+-----------+
| Model | Component |
+-------+-----------+
| 400 | 1008 |
| 400 | 1009 |
| 400 | 1010 |
| 400 | 1013 |
| 400 | 1018 |
| 400 | 1019 |
| 400 | 1020 |
| 400 | 1021 |
| 400 | 1024 |
| 400 | 1025 |
| 400 | 1074 |
| 400 | 1076 |
| 400 | 1078 |
| 400 | 1081 |
| 400 | 1082 |
| 400 | 1085 |
| 400 | 1086 |
| 400 | 1087 |
| 400 | 1096 |
| 400 | 1097 |
| 400 | 1098 |
| 401 | 1001 |
| 401 | 1003 |
| 401 | 1004 |
| 401 | 1005 |
| 401 | 1006 |
| 401 | 1007 |
| 401 | 1008 |
| 401 | 1009 |
| 401 | 1010 |
| 401 | 1013 |
| 401 | 1019 |
| 401 | 1020 |
| 401 | 1025 |
| 401 | 1074 |
| 401 | 1076 |
| 401 | 1078 |
| 401 | 1081 |
| 401 | 1082 |
| 401 | 1085 |
| 401 | 1086 |
| 401 | 1087 |
| 401 | 1096 |
| 401 | 1097 |
| 401 | 1098 |
| 401 | 1143 |
| 401 | 1145 |
| 402 | 1008 |
| 402 | 1009 |
| 402 | 1010 |
| 402 | 1025 |
| 402 | 1074 |
| 402 | 1076 |
| 402 | 1078 |
| 402 | 1081 |
| 402 | 1082 |
| 402 | 1085 |
| 402 | 1087 |
| 402 | 1096 |
| 402 | 1097 |
| 402 | 1098 |
| 403 | 1008 |
| 403 | 1009 |
| 403 | 1010 |
| 404 | 1000 |
| 404 | 1002 |
| 404 | 1007 |
| 404 | 1008 |
| 404 | 1009 |
| 404 | 1010 |
| 404 | 1013 |
| 404 | 1016 |
| 404 | 1019 |
| 404 | 1020 |
| 404 | 1021 |
| 404 | 1025 |
| 404 | 1074 |
| 404 | 1076 |
| 404 | 1078 |
| 404 | 1081 |
| 404 | 1082 |
| 404 | 1085 |
| 404 | 1086 |
| 404 | 1087 |
| 404 | 1096 |
| 404 | 1097 |
| 404 | 1098 |
| 405 | 1000 |
| 405 | 1001 |
| 405 | 1008 |
| 405 | 1009 |
| 405 | 1010 |
| 405 | 1014 |
| 405 | 1018 |
| 405 | 1019 |
| 405 | 1020 |
| 405 | 1021 |
| 405 | 1023 |
| 405 | 1025 |
| 405 | 1074 |
| 405 | 1076 |
| 405 | 1078 |
| 405 | 1081 |
| 405 | 1082 |
| 405 | 1085 |
| 405 | 1086 |
| 405 | 1087 |
| 405 | 1096 |
| 405 | 1097 |
| 405 | 1098 |
| 407 | 1008 |
| 407 | 1009 |
| 407 | 1010 |
| 408 | 1008 |
| 408 | 1009 |
| 408 | 1010 |
| 409 | 1008 |
| 409 | 1009 |
| 409 | 1010 |
| 409 | 1025 |
| 409 | 1074 |
| 409 | 1076 |
| 409 | 1078 |
| 409 | 1081 |
| 409 | 1082 |
| 409 | 1085 |
| 409 | 1086 |
| 409 | 1087 |
| 409 | 1096 |
| 409 | 1097 |
| 409 | 1098 |
| 410 | 1001 |
| 410 | 1003 |
| 410 | 1007 |
| 410 | 1008 |
| 410 | 1009 |
| 410 | 1010 |
| 410 | 1013 |
| 410 | 1019 |
| 410 | 1020 |
| 410 | 1021 |
| 410 | 1023 |
| 410 | 1024 |
| 410 | 1025 |
| 410 | 1074 |
| 410 | 1076 |
| 410 | 1078 |
| 410 | 1081 |
| 410 | 1082 |
| 410 | 1085 |
+-------+-----------+
I was loading it all into this enum structure:
pawn Code:
// This enum holds all info about a vehicle-model
enum TVehicleInfo
{
VehicleName[50], // Holds the name of the vehicle-model
VehicleClass, // Holds the ID of the vehicleclass to which this vehicle-model is related
VehicleModel, // Holds the vehicle-model ID
VehiclePrice, // Holds the price for the vehicle-model to buy this vehiclemodel as house/company vehicle
Float:VehicleMaxFuel, // Holds the maximum fuel for this vehicle-model
Float:FuelConsumption, // This holds the fuel consumption for this vehicle-model
RefuelTime, // This holds the duration in seconds for a complete refuel
Float:RefuelLitersPerStep, // This holds the amount of liters refuelled each step (every 250ms)
MaxPassengers, // Holds the maximum amount of passengers for this vehiclemodel
MaxCargo, // Holds the maximum cargo for this vehiclemodel
AccessLevels[MAX_CLASSES], // Holds the level required for each player-class to enter this vehiclemodel
bool:VehicleDisabled, // Holds "true" if this vehicle is disabled for buying as house-vehicle and spawning with /v command
ValidComponents[30] // Holds all component-ID's that are valid for this vehiclemodel
}
new AVehicleInfo[212][TVehicleInfo];
pawn Code:
// This function is called to load the vehicle-info from MySQL during OnGameModeInit
VehicleInfo_Load()
{
// Setup local variables
new Query[128], Cache:result, Rows, CountSuccess, CountFailed;
new ID, name[50], Class, Model, Price, Float:MaxFuel, Float:Consump, RefuelT, Float:refuellitersperstep, maxpass, maxcargo, accesslevels[50], Disabled, ValidComps[160], sscanf_text[16];
// Send a query to load all vehicle-info from MySQL
format(Query, sizeof(Query), "SELECT * FROM vehicleinfo");
result = mysql_query(SQL_db, Query, true);
// Print some debug info to the server console
printf("*** Loading vehicle-info from MySQL using \"%s\"", Query);
// Get the amount of rows (vehicle-info)
Rows = cache_get_row_count(SQL_db);
// If there are any rows (vehicle-info) loaded, load data and overwrite default script-values
if (Rows >= 1)
{
// Loop through all rows
for (new Row; Row < Rows; Row++)
{
// Load the data
ID = cache_get_field_content_int(Row, "ID", SQL_db);
cache_get_field_content(Row, "Name", name, SQL_db, 50);
Class = cache_get_field_content_int(Row, "Class", SQL_db);
Model = cache_get_field_content_int(Row, "Model", SQL_db);
Price = cache_get_field_content_int(Row, "Price", SQL_db);
MaxFuel = cache_get_field_content_float(Row, "MaxFuel", SQL_db);
Consump = cache_get_field_content_float(Row, "Consumption", SQL_db);
RefuelT = cache_get_field_content_int(Row, "RefuelTime", SQL_db);
refuellitersperstep = cache_get_field_content_float(Row, "RefuelLitersPerStep", SQL_db);
maxpass = cache_get_field_content_int(Row, "MaxPassengers", SQL_db);
maxcargo = cache_get_field_content_int(Row, "MaxCargo", SQL_db);
cache_get_field_content(Row, "AccessLevels", accesslevels, SQL_db, 160);
Disabled = cache_get_field_content_int(Row, "Disabled", SQL_db);
cache_get_field_content(Row, "ValidComponents", ValidComps, SQL_db, 160);
// Check if the ID is invalid (out of range)
if ((ID < 0) || (ID >= sizeof(AVehicleInfo)))
{
// Count the amount of failed vehicle-info entries (invalid ID's)
CountFailed++;
// Add a message to the server-console to inform the admin about the wrong ID
printf("*** ERROR: Invalid ID found in table \"vehicleinfo\": %i", ID);
// Continue with the next vehicle-info entry from the MySQL query
continue;
}
// Store all the data
format(AVehicleInfo[ID][VehicleName], 50, name);
AVehicleInfo[ID][VehicleClass] = Class;
AVehicleInfo[ID][VehicleModel] = Model;
AVehicleInfo[ID][VehiclePrice] = Price;
AVehicleInfo[ID][VehicleMaxFuel] = MaxFuel;
AVehicleInfo[ID][FuelConsumption] = Consump;
AVehicleInfo[ID][RefuelTime] = RefuelT;
AVehicleInfo[ID][RefuelLitersPerStep] = refuellitersperstep;
AVehicleInfo[ID][MaxPassengers] = maxpass;
AVehicleInfo[ID][MaxCargo] = maxcargo;
if (Disabled == 1)
AVehicleInfo[ID][VehicleDisabled] = true;
else
AVehicleInfo[ID][VehicleDisabled] = false;
// Use sscanf to extract all valid components from the big string to insert them into the AVehicleInfo array (use delimiter "-" to split the values and convert every value to an integer)
sscanf(ValidComps, "p<->a<i>[30]", AVehicleInfo[ID][ValidComponents]);
// Do the same for the accesslevels
format(sscanf_text, sizeof(sscanf_text), "p<->a<i>[%i]", MAX_CLASSES);
sscanf(accesslevels, sscanf_text, AVehicleInfo[ID][AccessLevels]);
// Count the succesfully loaded vehicle-info entries
CountSuccess++;
}
}
// Print the amount of vehicle-info entries loaded for debugging
printf("*** Vehicle-info loaded: %i (successful: %i, failed: %i)", Rows, CountSuccess, CountFailed);
printf("");
// Clear the cache to prevent memory-leaks
cache_delete(result, SQL_db);
return 1;
}
If I would need to load the second table separately, it takes alot more code to read the same data, as I need to loop through all rows, check the vehicle-model, load the component and store it in a free index in the same enum under "ValidComponents".
Alot more work to get the same result and seems like a waste.
Also, the second table has 1750 rows to hold the same data as the ValidComponents column in the first table.
The access-levels will have the same "problem" as it was meant to put multiple values in that column as well.
And later on, for my job-system, it would also have such columns where multiple values are stored into one single column.
Splitting it all up into separate columns, separate loading functions, and whatever else, only makes it more complicated.
The data is meant to be stored together and I tried to keep all related values into one single table, but I didn't want to add 30 separate columns either to store 30 possible components on each vehiclemodel.