Calling SQL experts... Need advice!

[sman1975]

Hi!

I'm working on a mod that changes how Barbarians work (i.e. It's a "Wandering Monster" mod that adds 50 WMs to the game, who spawn as Barbarians). Part of the mod seeks to adjust Barb DEFINES as well.


I have a "configuration.sql" file that is loaded at the beginning of the mod.

The mod wants to have 3 different difficulty levels: EASY, SAVAGE, and APOCALYPTIC.

I'm thinking about adding a row in the DEFINES table, something like 'WANDERING_MONSTER_DIFFICULTY_LEVEL" - with the text value of the 3 levels above.

Ideally, I'm looking for the syntax that would look at the value of that NAME, then adjust the Barb rows accordingly.

Currently, I have the 9 different Barb rows replicated for each difficulty level, and the player can simply "comment out" the levels he doesn't like. But that requires 9 changes. It would be nice if it only required one.


Appreciate any help. My SQL is worse than my LUA, and we all know how bad my LUA is...

Current config file:

-- ====================================================================================================================================
--        Barbarian/Wandering Monster Settings    --    Adjusting these settings will make Barbs easier, more difficult, or MUCH more difficult...
--
--        Default setting is to "Savage"
--
--        Select the difficulty you want to play by "uncommenting" the lines for the appropriate setting.
--        You do this by ensuring there are no dashes in front of the commands.
--        If you leave these lines "commented" (with the double dash in front), they won't impact normal gameplay.
--
--        There are 3 difficulty levels:  Easy, Savage, and Apcolyptic
--        To select a particular difficulty, uncomment the UPDATE commands under the section for that difficulty below:
--
-- ===================================================================================================================================
--
--            EASY Difficulty:  Wandering Monsters are 25% weaker, normal Barbs spawn less frequently
--

--UPDATE Units SET Combat = round(Combat*0.75), RangedCombat = round(RangedCombat*0.75) WHERE Type LIKE 'UNIT_FAWM_%';

--UPDATE Defines SET Value = 1            WHERE Name = 'BARBARIAN_CAMP_ODDS_OF_NEW_CAMP_SPAWNING';                        -- Default 2
--UPDATE Defines SET Value = 5            WHERE Name = 'BARBARIAN_CAMP_MINIMUM_DISTANCE_CAPITAL';                            -- Default 4
--UPDATE Defines SET Value = 9            WHERE Name = 'BARBARIAN_CAMP_MINIMUM_DISTANCE_ANOTHER_CAMP';                    -- Default 7
--UPDATE Defines SET Value = 1            WHERE Name = 'MAX_BARBARIANS_FROM_CAMP_NEARBY';                                    -- Default 2
--UPDATE Defines SET Value = 6            WHERE Name = 'MAX_BARBARIANS_FROM_CAMP_NEARBY_RANGE';                            -- Default 4
--UPDATE Defines SET Value = 45            WHERE Name = 'BARBARIAN_NAVAL_UNIT_START_TURN_SPAWN';                            -- Default 30
--UPDATE Defines SET Value = 50            WHERE Name = 'BARBARIAN_TECH_PERCENT';                                            -- Default 75
--UPDATE Defines SET Value = -15        WHERE Name = 'BARBARIAN_CITY_ATTACK_MODIFIER';                                    -- Default -25




-- ====================================================================================================================================
--
--            SAVAGE Difficulty:   Wandering Monsters are 50% stronger, normal Barbs spawn more frequently
--

UPDATE Units SET Combat = round(Combat*1.5), RangedCombat = round(RangedCombat*1.5) WHERE Type LIKE 'UNIT_FAWM_%';

UPDATE Defines SET Value = 4            WHERE Name = 'BARBARIAN_CAMP_ODDS_OF_NEW_CAMP_SPAWNING';                        -- Default 2
UPDATE Defines SET Value = 4            WHERE Name = 'BARBARIAN_CAMP_MINIMUM_DISTANCE_CAPITAL';                            -- Default 4
UPDATE Defines SET Value = 6            WHERE Name = 'BARBARIAN_CAMP_MINIMUM_DISTANCE_ANOTHER_CAMP';                    -- Default 7
UPDATE Defines SET Value = 3            WHERE Name = 'MAX_BARBARIANS_FROM_CAMP_NEARBY';                                    -- Default 2
UPDATE Defines SET Value = 4            WHERE Name = 'MAX_BARBARIANS_FROM_CAMP_NEARBY_RANGE';                            -- Default 4
UPDATE Defines SET Value = 25            WHERE Name = 'BARBARIAN_NAVAL_UNIT_START_TURN_SPAWN';                            -- Default 30
UPDATE Defines SET Value = 85            WHERE Name = 'BARBARIAN_TECH_PERCENT';                                            -- Default 75
UPDATE Defines SET Value = -5            WHERE Name = 'BARBARIAN_CITY_ATTACK_MODIFIER';                                    -- Default -25




-- ====================================================================================================================================
--
--            APOCALYPTIC Difficulty:  Wandering Monsters are 300% stronger, normal Barbs spawn much more frequently
--

--UPDATE Units SET Combat = round(Combat*3), RangedCombat = round(RangedCombat*3) WHERE Type LIKE 'UNIT_FAWM_%';

--UPDATE Defines SET Value = 6            WHERE Name = 'BARBARIAN_CAMP_ODDS_OF_NEW_CAMP_SPAWNING';                        -- Default 2
--UPDATE Defines SET Value = 3            WHERE Name = 'BARBARIAN_CAMP_MINIMUM_DISTANCE_CAPITAL';                            -- Default 4
--UPDATE Defines SET Value = 4            WHERE Name = 'BARBARIAN_CAMP_MINIMUM_DISTANCE_ANOTHER_CAMP';                    -- Default 7
--UPDATE Defines SET Value = 5            WHERE Name = 'MAX_BARBARIANS_FROM_CAMP_NEARBY';                                    -- Default 2
--UPDATE Defines SET Value = 3            WHERE Name = 'MAX_BARBARIANS_FROM_CAMP_NEARBY_RANGE';                            -- Default 4
--UPDATE Defines SET Value = 20            WHERE Name = 'BARBARIAN_NAVAL_UNIT_START_TURN_SPAWN';                            -- Default 30
--UPDATE Defines SET Value = 100        WHERE Name = 'BARBARIAN_TECH_PERCENT';                                            -- Default 75
--UPDATE Defines SET Value = 0            WHERE Name = 'BARBARIAN_CITY_ATTACK_MODIFIER';                                    -- Default -25

 

[whoward69]

Due to the way the database is loaded, your best bet will be to have three sql files, one for easy, medium and hard say WM_easy.sql, WM_medium.sql and WM_hard.sql

Have all three in your mod, but load none of them. Copy WM_medium.sql to WM.sql and load that. Now all the user has to do to change level is to copy the required difficulty file over the top of WM.sql

 

[sman1975]

@whoward69 - Grrrrrrrrrrrrrrr..... I was hoping there was some little conditional logic that I could use to check a certain DEFINES value, then load the appropriate changes based on that....

Appreciate the suggestion!

 

[LeeS]

I believe this would work:

UPDATE Defines
SET Value = 1
WHERE Name = 'BARBARIAN_CAMP_ODDS_OF_NEW_CAMP_SPAWNING'
AND EXISTS (SELECT 1 FROM Defines WHERE Name = 'WANDERING_MONSTER_DIFFICULTY_LEVEL' AND Value = 'EASY');

But you would need an insert "chunk" structured like this for each of your updates to the Defines table, and for each of the Difficulty levels you want to create.

There are some methods you can use to employ ELSE ... THEN conditions in SQL but I'm not sure enough of the required syntax to attempt to suggest an example.

-----------------------------------------------------------

You would also need to update table Defines to add the new row with

Name = 'WANDERING_MONSTER_DIFFICULTY_LEVEL'

and your default setting with instructions to users on how to change to one of the other settings. I would probably put this update to the Defines table with the new Row name in a "ConfigMod.sql" or some similar named file, and make it load to the database as the very first action your mod does.

------------------------------------------------------------------

Then I would implement the adjustments based on the chosen difficulty setting within a seperated SQL file with a note at the top not to diddle with the code therein -- and make this second SQL file be loaded into the database after the "ConfigMod.sql" file.

 

[LeeS]

The other thing you could do instead of using the Defines table with a new row is to create your own table that you can use for all your mods where you want to do something similar, like

CREATE TABLE IF NOT EXISTS 
	SmanModdingValues (
	Name	text,
	Value	text);

INSERT INTO SmanModdingValues (Name, Value)
VALUES ('WANDERING_MONSTER_DIFFICULTY_LEVEL',	'SAVAGE');

This would set up the default value for you, and would keep away from using the Defines table, which sometimes is an bit of an odd kettle of fish in that some values are only implemented into a game at set-up and don't seem to be applied even if changed after a game is first set up.

Then if you need another "base" setting in another mod, you just do in that mod as like

CREATE TABLE IF NOT EXISTS 
	SmanModdingValues (
	Name	text,
	Value	text);

INSERT INTO SmanModdingValues (Name, Value)
VALUES ('NUMBER_CHEESEBURGER_PER_PLAYER',	4);

If both mods that are using the same table are active and enabled, only one will create the table, but both mods will add their needed row to the table for that mod.

------------------------------

Your updates to table Defines would then look like

UPDATE Defines
SET Value = 1
WHERE Name = 'BARBARIAN_CAMP_ODDS_OF_NEW_CAMP_SPAWNING'
AND EXISTS (SELECT 1 FROM SmanModdingValues WHERE Name = 'WANDERING_MONSTER_DIFFICULTY_LEVEL' AND Value = 'EASY');

 

[sman1975]

@LeeS - I "think" I understand where you're going with this. I'm off that mod for a couple of weeks, but will work up a solution along these lines, then post it when I get it working.

Appreciate very, very, very much the help in streamlining this out!