[Mod Request] Streamlined unit upgrade paths

[Quappas]

Hi,

I am annoyed by some of the unit upgrade paths in Civ 5 BNW and since I don't know how to change them myself I figured I could ask for help here.

Here is a list of some inconsistencies and problems I came across that I would like to fix:

1. Pikemen upgrade to lancers, which in turn upgrade to anti tank guns. I get it, tanks replace mounted units in the late game and the spearmen upgrade line is in theory supposed to counter these units.
The main problem I see here is that this is in fact not at all how spearmen and pikemen are used in the game. They are rather used as alternative to Swordsmen and Longswordsmen when you are out of iron or don't have the necessary techs to build them yet. Spearmen and pikemen are used primarily as meat shields, collecting Drill and Cover promotions along the way so that you can stack defensive terrain bonuses on them. When they upgrade to a mounted unit, they loose their defensive terrain bonuses and suddenly change their role from a tanking unit to a mobile harassment unit.
Another Problem I see with this upgrade path is that the gaps between pikemen and lancers and lancers and anti tank gun are very large to the point where it becomes pretty useless to have them around.

2. Chariot archers upgrade into knights. This is just stupid. A ranged unit upgrades into a melee unit, all its promotions become useless and if your army was a well rounded pikemen chariot mix before, all of a sudden you end up with a bunch of melee cavalry you did not want at all in the first place. An exception for this are Mongolian and Arabian chariot archers, as they upgrade to their unique knight replacements which are mounted ranged units. But this just kind of delays the problem, as Keshiks and Camel Archers upgrade into cavalry later.

3. Privateers skip the upgrade into ironclads for no apparent reason and are kind of to week for an era until they finally upgrade into destroyers, together with ironclads.

So, i would like someone to either to create a mod which makes the following changes to the unit upgrade paths or to help me a little to learn how to do this myself:

For all Civs:
1. Privateers should upgrade to Ironclads, not to Destroyers, including all unique units.
2. Pikemen should upgrade to Musketmen, not to Lancers, including all unique units.
3. Knights should upgrade to Lancers, not to Cavalry, except for Mongolian and Arabian unique units (Keshiks and Camel Archers).
4. Lancers should upgrade to Cavalry, not to anti tank guns, including all unique units.
5. Chariot Archers should upgrade to Crossbowmen, not to Knights, except for Mongolian and Arabian Chariots.
Only for Mongolia and Arabia:
6. Mongolian and Arabian Chariots should still upgrade to the unique Knight replacements Keshik and Camel Archer.
7. Keshiks and Camel Archers should upgrade to Gatling Guns, not to Cavalry.
8. Mongolian and Arabian horseman should upgrade directly to Lancers, not to Keshiks and Camel Archers.

That would be it. If this is possible and not to difficult to code, I would be really happy if someone could make such a mod or teach me how to do it myself.

Greetings,
Quappas.

 

[SnowyNix]

i second this notion, lots of great ideas

 

[Nutty]

I'm not sure I agree with all of the ideas, but in any event it's a very easy mod, EXCEPT for the exceptions (#6 and #8 count as exceptions, though #7 is OK).

Below is a simplistic version without the exceptions. I'll try to come back later with a more complicated one that deals with your exceptions and also deals properly with the UU's of modded civs.

If you don't know how to use ModBuddy, just download My - Changes (v 1).civ5mod and extract it with 7-Zip to your MODS folder.

Then edit .\SQL\MySqlChanges.sql with Notepad or another text editor and paste the following:

UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_IRONCLAD'
  WHERE UnitType='UNIT_PRIVATEER';
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_MUSKETMAN'
  WHERE UnitType IN ('UNIT_PIKEMAN', 'UNIT_ZULU_IMPI');
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_LANCER'
  WHERE UnitType IN ('UNIT_KNIGHT', 'UNIT_SPANISH_CONQUISTADOR', 'UNIT_SONGHAI_MUSLIMCAVALRY', 'UNIT_SIAMESE_WARELEPHANT');
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_CAVALRY'
  WHERE UnitType IN ('UNIT_LANCER', 'UNIT_OTTOMAN_SIPAHI', 'UNIT_POLISH_WINGED_HUSSAR', 'UNIT_SWEDISH_HAKKAPELIITTA');
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_CROSSBOWMAN'
  WHERE UnitType IN ('UNIT_CHARIOT_ARCHER', 'UNIT_EGYPTIAN_WARCHARIOT', 'UNIT_HUN_HORSE_ARCHER', 'UNIT_INDIAN_WARELEPHANT', 'UNIT_BARBARIAN_AXMAN');
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_GATLINGGUN'
  WHERE UnitType IN ('UNIT_MONGOLIAN_KESHIK', 'UNIT_ARABIAN_CAMELARCHER');

[Edited to make more concise. Note I haven't actually tested it ingame to make sure I didn't make a typo in any of the unit names, but there don't appear to be any syntax errors.]

 

[Quappas]

Thank you very much for the quick reply!

I'm not sure I agree with all of the ideas

I'd be happy about any feedback regarding my proposed changes. Actually I have not even really used some of the units I would like to change in game, so if I am about to mess stuff up, please tell me.

'll try to come back later with a more complicated one that deals with your exceptions

Thanks again for the effort. Is it possible to check which civilization a unit belongs to? So that you can make something like this:

UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_CROSSBOWMAN'
  WHERE UnitType IN ('UNIT_CHARIOT_ARCHER', 'UNIT_EGYPTIAN_WARCHARIOT', 'UNIT_HUN_HORSE_ARCHER', 'UNIT_INDIAN_WARELEPHANT', 'UNIT_BARBARIAN_AXMAN')
  [COLOR="Blue"]AND NOT "Civ is Mongolia or Arabia"[/COLOR];

UPDATE Unit_ClassUpgrades SET UnitClassType=[COLOR="blue"]"Lancer"[/COLOR]
  WHERE UnitType IN ([COLOR="blue"]"Horseman"[/COLOR]) [COLOR="Blue"] AND "Civ is Mongolia or Arabia"[/COLOR];

 

[Nutty]

I wish it was that easy. No, at the database level there's no distinction between the units of various civs (other than unique units, which are just like other units, they just have a defined relationship with the civ).

EDIT: To accomplish what you want, we need to either add more unique units for each of your exceptions (civs can have more than two uniques, but doing so means that it will show one of the units instead of Arabia's unique building in the loading screen and civilopedia), or you can make "anti" unique units (meaning making brand new unit classes that are copies of the existing units, and either set all the OTHER civs to have a null unique, or disallow them from training the unit by using a Lua script. Then the opposite must be done for the original units for your 2 civs).

Obviously the first option is easier, but the user interface issue is a bit of a shame.

 

[whoward69]

Or you can use Lua and the CanHaveUpgrade event added in 3.276

 

[Nutty]

Or you can use Lua and the CanHaveUpgrade event added in 3.276

Hmm... As I understand it, Player/CityCanTrain can't let you train a unit you're otherwise ineligible for, right? CanHaveUpgrade isn't the same way?

EDIT: Oh, that was from your batch of events that Firaxis "borrowed." And I see the parameters include the class. Wow, that makes the job much easier.

EDIT 2: Though the name is misleading... Anyway, were those events backported to vanilla and G+K, or are they BNW-only?

 

[LeeS]

Hmm... As I understand it, Player/CityCanTrain can't let you train a unit you're otherwise ineligible for, right? CanHaveUpgrade isn't the same way?

EDIT: Oh, that was from your batch of events that Firaxis "borrowed." And I see the parameters include the class. Wow, that makes the job much easier.

EDIT 2: Though the name is misleading... Anyway, were those events backported to vanilla and G+K, or are they BNW-only?

Look on page two of Pouakai's thread. Whoward has a table showing compatibilities for hooks and expansions @ http://forums.civfanatics.com/showpost.php?p=13628161&postcount=28

 

[whoward69]

Edited to make more concise.

Even more concise (and future proof) if you use a SELECT within the IN
eg

UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_CROSSBOWMAN'
  WHERE UnitType IN (SELECT Type FROM Units WHERE Class='UNITCLASS_CHARIOT_ARCHER');

Also means you don't miss the Dutch Sea Bagger

Spoiler :

-- Handle all the generic cases
-- 1. Privateers should upgrade to Ironclads, not to Destroyers, including all unique units.
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_IRONCLAD'
  WHERE UnitType IN (SELECT Type FROM Units WHERE Class='UNITCLASS_PRIVATEER');
 
-- 2. Pikemen should upgrade to Musketmen, not to Lancers, including all unique units.
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_MUSKETMAN'
  WHERE UnitType IN (SELECT Type FROM Units WHERE Class='UNITCLASS_PIKEMAN');

-- 3. Knights should upgrade to Lancers, not to Cavalry,
-- except for Mongolian and Arabian unique units (Keshiks and Camel Archers). (see below)
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_LANCER'
  WHERE UnitType IN (SELECT Type FROM Units WHERE Class='UNITCLASS_KNIGHT');

-- 4. Lancers should upgrade to Cavalry, not to anti tank guns, including all unique units.
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_CAVALRY'
  WHERE UnitType IN (SELECT Type FROM Units WHERE Class='UNITCLASS_LANCER');

-- 5. Chariot Archers should upgrade to Crossbowmen, not to Knights, except for Mongolian and Arabian Chariots.
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_CROSSBOWMAN'
  WHERE UnitType IN (SELECT Type FROM Units WHERE Class='UNITCLASS_CHARIOT_ARCHER');

-- Handle the exceptions
-- 7. Keshiks and Camel Archers should upgrade to Gatling Guns, not to Cavalry.
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_GATLINGGUN'
  WHERE UnitType IN ('UNIT_MONGOLIAN_KESHIK', 'UNIT_ARABIAN_CAMELARCHER');

Working on a Lua based solution for the exceptions ...

 

[whoward69]

Babylon Horsemen


Mongol Horsemen


MySqlChanges.sql

Spoiler :

--
-- Blank SQL file for adding your own changes to
--
-- BEFORE making any changes to this file enable logging (see http://forums.civfanatics.com/showthread.php?t=487482)
-- Any errors in this file will appear in database.log
--

-- Insert a value into the Defines table, you can then use SQLiteSpy or the FireFox SQLite plug-in to prove that this file is being executed
INSERT INTO Defines(Name, Value) VALUES ('_MY_CHANGES_SQL', '1');

-- Add you own SQL code below here (don't forget the semi-colons), and don't forget to check the log files for errors

--
-- For all Civs:
--

-- 1. Privateers should upgrade to Ironclads, not to Destroyers, including all unique units.
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_IRONCLAD'
  WHERE UnitType IN (SELECT Type FROM Units WHERE Class='UNITCLASS_PRIVATEER');

-- 2. Pikemen should upgrade to Musketmen, not to Lancers, including all unique units.
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_MUSKETMAN'
  WHERE UnitType IN (SELECT Type FROM Units WHERE Class='UNITCLASS_PIKEMAN');

-- 3. Knights should upgrade to Lancers, not to Cavalry, except for Mongolian and Arabian unique units (Keshiks and Camel Archers).
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_LANCER'
  WHERE UnitType IN (SELECT Type FROM Units WHERE Class='UNITCLASS_KNIGHT');

-- 4. Lancers should upgrade to Cavalry, not to anti tank guns, including all unique units.
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_CAVALRY'
  WHERE UnitType IN (SELECT Type FROM Units WHERE Class='UNITCLASS_LANCER');

-- 5. Chariot Archers should upgrade to Crossbowmen, not to Knights, except for Mongolian and Arabian Chariots.
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_CROSSBOWMAN'
  WHERE UnitType IN (SELECT Type FROM Units WHERE Class='UNITCLASS_CHARIOT_ARCHER');

  
--
-- Only for Mongolia and Arabia:
--

-- This new column will be used by the Lua code
-- Ideally the following ALTER TABLE statement should be in its own .sql file
ALTER TABLE Unit_ClassUpgrades ADD CivilizationType TEXT DEFAULT NULL;

-- 6. Mongolian and Arabian Chariots should still upgrade to the unique Knight replacements Keshik and Camel Archer.
INSERT INTO Unit_ClassUpgrades(UnitType, CivilizationType, UnitClassType)
  VALUES('UNIT_CHARIOT_ARCHER', 'CIVILIZATION_MONGOL', 'UNITCLASS_KNIGHT');
INSERT INTO Unit_ClassUpgrades(UnitType, CivilizationType, UnitClassType)
  VALUES('UNIT_CHARIOT_ARCHER', 'CIVILIZATION_ARABIA', 'UNITCLASS_KNIGHT');

-- 7. Keshiks and Camel Archers should upgrade to Gatling Guns, not to Cavalry.
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_GATLINGGUN'
  WHERE UnitType IN ('UNIT_MONGOLIAN_KESHIK', 'UNIT_ARABIAN_CAMELARCHER');

-- 8. Mongolian and Arabian horseman should upgrade directly to Lancers, not to Keshiks and Camel Archers.
INSERT INTO Unit_ClassUpgrades(UnitType, CivilizationType, UnitClassType)
  VALUES('UNIT_HORSEMAN', 'CIVILIZATION_MONGOL', 'UNITCLASS_LANCER');
INSERT INTO Unit_ClassUpgrades(UnitType, CivilizationType, UnitClassType)
  VALUES('UNIT_HORSEMAN', 'CIVILIZATION_ARABIA', 'UNITCLASS_LANCER');

MyLuaChanges.lua

Spoiler :

--
-- Blank LUA file for adding your own changes to
--
-- BEFORE making any changes to this file enable logging (see http://forums.civfanatics.com/showthread.php?t=487482)
-- Any errors in this file will appear in lua.log (as will the output from print() statements)
--

-- Display a line in the lua.log file, this proves that the file is being executed
print("This is the 'My - Changes' mod script")

-- Add you own Lua code below here, and don't forget to check the log files for errors

function OnCanHaveUpgrade(iPlayer, iUnit, iUpgradeUnitClass, iUpgradeUnitType)
  local pPlayer = Players[iPlayer]
  local pUnit = pPlayer:GetUnitByID(iUnit)
  
  local sCivilizationType = GameInfo.Civilizations[pPlayer:GetCivilizationType()].Type
  local sUpgradeUnitClass = GameInfo.UnitClasses[iUpgradeUnitClass].Type
  
  -- Get the upgrade details for this given unit type
  local sGeneric = nil
  local sSpecific = nil
  for row in DB.Query("SELECT up.CivilizationType, up.UnitClassType FROM Unit_ClassUpgrades up, Units u WHERE up.UnitType=u.Type AND u.ID=?;", pUnit:GetUnitType()) do
    -- We will get, for example, for a Horseman
    --   (NULL,                  'UNITCLASS_KNIGHT')
    --   ('CIVILIZATION_ARABIA', 'UNITCLASS_LANCER')
    --   ('CIVILIZATION_MONGOL', 'UNITCLASS_LANCER')
    if (row.CivilizationType == nil) then
      sGeneric = row.UnitClassType
    elseif (row.CivilizationType == sCivilizationType) then
      sSpecific = row.UnitClassType
    end
  end
  
  -- For the unit, we now have any specific upgrade and the generic upgrade, so we can answer the question about this particular upgrade class
  -- For example,
  --   can a French Horseman upgrade to UNITCLASS_KNIGHT - YES, as there is no specific French upgrade and the generic upgrade is what we are being asked about
  --   can a French Horseman upgrade to UNITCLASS_LANCER - NO, as there is no specific French upgrade and the generic upgrade is NOT what we are being asked about
  --   can a Mongolian Horseman upgrade to UNITCLASS_KNIGHT - NO, as there IS a specifc Mongolian upgrade and it is NOT what we are being asked about
  --   can a Mongolian Horseman upgrade to UNITCLASS_LANCER - YES, as there IS a specifc Mongolian upgrade and it IS what we are being asked about
  if (sSpecific) then
    return (sSpecific == sUpgradeUnitClass)
  else
    return (sGeneric == sUpgradeUnitClass)
  end
  
  return true
end
GameEvents.CanHaveUpgrade.Add(OnCanHaveUpgrade)

 

[Quappas]

Very nice! I will try this out by conquering the world as Genghis Khan! Thanks again to all of you guys.

 

[Nutty]

Even more concise (and future proof) if you use a SELECT within the IN...

Oh, we're showing each other up, now? OK, I see your SELECTs and raise you some TRIGGERs for mod compatibility--but only where the default upgrade relationship is in place for the new UU (and in a couple cases there is a check to see if the unit is of the proper ranged/melee type).

Replacement for above SQL code (but leave the Lua as is): EDIT: As whoward69 points out, this won't work most of the time.

Spoiler :

--
-- For all Civs:
--

-- 1. Privateers should upgrade to Ironclads, not to Destroyers, including all unique units.
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_IRONCLAD'
  WHERE UnitType IN (SELECT Type FROM Units WHERE Class = 'UNITCLASS_PRIVATEER');
CREATE TRIGGER PrivateerUpgrade AFTER INSERT ON Units
  WHEN NEW.Class = 'UNITCLASS_PRIVATEER'
  BEGIN
    UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_IRONCLAD'
    WHERE UnitClassType='UNITCLASS_DESTROYER' AND UnitType = NEW.Type;
  END;

-- 2. Pikemen should upgrade to Musketmen, not to Lancers, including all unique units.
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_MUSKETMAN'
  WHERE UnitType IN (SELECT Type FROM Units WHERE Class='UNITCLASS_PIKEMAN');
CREATE TRIGGER PikemanUpgrade AFTER INSERT ON Units
  WHEN NEW.Class = 'UNITCLASS_PIKEMAN' 
  BEGIN
    UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_MUSKETMAN'
    WHERE UnitClassType='UNITCLASS_LANCER' AND UnitType = NEW.Type;
  END;

-- 3. Knights should upgrade to Lancers, not to Cavalry, except for Mongolian and Arabian unique units (Keshiks and Camel Archers).
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_LANCER'
  WHERE UnitType IN (SELECT Type FROM Units WHERE Class='UNITCLASS_KNIGHT');
CREATE TRIGGER KnightUpgrade AFTER INSERT ON Units
  WHEN NEW.Class = 'UNITCLASS_KNIGHT'
  BEGIN
    UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_LANCER'
    WHERE UnitClassType='UNITCLASS_CAVALRY' AND UnitType = NEW.Type;
  END;

-- 4. Lancers should upgrade to Cavalry, not to anti tank guns, including all unique units [and mod UUs with melee lancer replacements].
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_CAVALRY'
  WHERE UnitType IN (SELECT Type FROM Units WHERE Class='UNITCLASS_LANCER');
CREATE TRIGGER LancerUpgrade AFTER INSERT ON Units
  WHEN NEW.Class = 'UNITCLASS_LANCER' AND NEW.RangedCombat = 0
  BEGIN
    UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_CAVALRY'
    WHERE UnitClassType='UNITCLASS_ANTI_TANK_GUN' AND UnitType = NEW.Type;
  END;

-- 5. Chariot Archers should upgrade to Crossbowmen, not to Knights, except for Mongolian and Arabian Chariots [and mod UUs with ranged chariot replacements].
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_CROSSBOWMAN'
  WHERE UnitType IN (SELECT Type FROM Units WHERE Class='UNITCLASS_CHARIOT_ARCHER');
CREATE TRIGGER ChariotArcherUpgrade AFTER INSERT ON Units
  WHEN NEW.Class = 'UNITCLASS_CHARIOT_ARCHER' AND NEW.RangedCombat > 0
  BEGIN
    UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_CROSSBOWMAN'
    WHERE UnitClassType='UNITCLASS_KNIGHT' AND UnitType = NEW.Type;
  END;

  
--
-- Only for Mongolia and Arabia:
--

-- This new column will be used by the Lua code
-- Ideally the following ALTER TABLE statement should be in its own .sql file
ALTER TABLE Unit_ClassUpgrades ADD CivilizationType TEXT DEFAULT NULL;

-- 6. Mongolian and Arabian Chariots should still upgrade to the unique Knight replacements Keshik and Camel Archer.
INSERT INTO Unit_ClassUpgrades(UnitType, CivilizationType, UnitClassType)
  VALUES('UNIT_CHARIOT_ARCHER', 'CIVILIZATION_MONGOL', 'UNITCLASS_KNIGHT');
INSERT INTO Unit_ClassUpgrades(UnitType, CivilizationType, UnitClassType)
  VALUES('UNIT_CHARIOT_ARCHER', 'CIVILIZATION_ARABIA', 'UNITCLASS_KNIGHT');

-- 7. Keshiks and Camel Archers should upgrade to Gatling Guns, not to Cavalry.
UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_GATLINGGUN'
  WHERE UnitType IN ('UNIT_MONGOLIAN_KESHIK', 'UNIT_ARABIAN_CAMELARCHER');

-- 8. Mongolian and Arabian horseman should upgrade directly to Lancers, not to Keshiks and Camel Archers.
INSERT INTO Unit_ClassUpgrades(UnitType, CivilizationType, UnitClassType)
  VALUES('UNIT_HORSEMAN', 'CIVILIZATION_MONGOL', 'UNITCLASS_LANCER');
INSERT INTO Unit_ClassUpgrades(UnitType, CivilizationType, UnitClassType)
  VALUES('UNIT_HORSEMAN', 'CIVILIZATION_ARABIA', 'UNITCLASS_LANCER');

OK, @whoward69, now you've got to show me a generalized solution for melee-->ranged relationships (and vice versa). So, first we need a table of equivalency...

[emoji14]

 

[whoward69]

OK, @whoward69, now you've got to show me a generalized solution for melee-->ranged relationships (and vice versa). So, first we need a table of equivalency...

If you're talking about promotions ... been there, done that (a loooooooong time ago) ... see Promotions - Crossovers

 

[whoward69]

CREATE TRIGGER PrivateerUpgrade AFTER INSERT ON Units
  WHEN NEW.Class = 'UNITCLASS_PRIVATEER'
  BEGIN
    UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_IRONCLAD'
    WHERE UnitClassType='UNITCLASS_DESTROYER' AND UnitType = NEW.Type;
  END;

The trigger is order dependant. That is, it relies on the unit's upgrade path having been created (inserted) into the Unit_ClassUpgrades table before the unit's data is added to the Units table.
Given that most modders add the primary table entries before the secondary ones, ie, they tend to write

<GameData>
  <Units>
    ...
  </Units>
  
  <Unit_ClassUpgrades>
  </Unit_ClassUpgrades>
</GameData>

that's unlikely to be the case.

We need to create a trigger on the Unit_ClassUpgrades table

CREATE TRIGGER PrivateerUpgrade AFTER INSERT ON Unit_ClassUpgrades
  WHEN NEW.UnitClassType = 'UNITCLASS_DESTROYER'
  BEGIN
    UPDATE Unit_ClassUpgrades SET UnitClassType='UNITCLASS_IRONCLAD'
    WHERE UnitType = NEW.UnitType AND NEW.UnitClassType = 'UNITCLASS_DESTROYER';
  END;

However, if we use a mod that adds another ship line that merges at Destroyers, the trigger will change those to merge at Ironclads as well (as we are not testing the unit's original class, but it's upgrade class)
To fix this our internal UPDATE needs join to the Units table, but this introduces order dependancy again, and the only way to fix that would be by adding a second trigger on the Units table.
Therefore the final solution for Privateers to Ironclads will be two lines of initial update and (at least) 14 lines of trigger - a significant muddying of the waters.

 

[whoward69]

The solution(s) ignore Goody Hut upgrades. While these are quite simple to fix for the generic upgrade paths, there is no (simple) solution for the Mongol and Arab exceptions. (You have to use Lua to scan the map for all Mongol and Arab exception units at the start of the turn and then again at the end of the turn and see if any had been "goodied" into something they shouldn't be.)

 

[LeeS]

hmmm...do units even record what they started life as once they've been upgraded? Or in this case can the unit's 'Turn Created' data be relied upon to be the same turn number as it was goody-upgraded?

 

[whoward69]

Probably not. I also haven't considered if there's any special handling needed for any exception units that have been gifted by City States. France could end up with a Keshik.

 

[Nutty]

Quite right about my triggers and exception goody hut upgrades, but I don't think CS gifts are an issue. Keshiks, e.g., will go to Gatling Guns no matter who controls them, and as I understand it, it's the melee<-->ranged movement that is the source of the issue for the knight replacements, and therefore the appropriate follow-on line for the prior units.

Why not just change the class of the 2 UU knights to crossbowman replacements? Obviously, that means that they can now field both knights and e.g., keshiks, and can't make cheaper crossbowmen, but that may be an OK balance to strike.

If that doesn't seem quite right, perhaps they should be a new class (KNIGHT_RANGED) for which other civs have a null UU or Lua training prevention. Since we want chariots of the appropriate civs to upgrade to ranged knights, including for goody hut upgrades, we could just make their chariots a new dummy class as well (luckily that's where the upgrade path starts, so we need not go further down the line).

Hmmm... Crap, so what's the logic for CS unit gifts? Would they gift one of these special chariots if there isn't a UU in that class?

 

[whoward69]

Hmmm... Crap, so what's the logic for CS unit gifts? Would they gift one of these special chariots if there isn't a UU in that class?

Military CSes pick one random "not-in-play" unique at the start of the game. "Unique" in this case means any unit that is not a default unit for a class. CSes then either gift their unique, or if not relevant, a standard unit. "Standard" in this case is any unit that is a default unit for a class. So ultimately, military CSes will consider all units for gifting (provided they are combative land units of course)

 

[ATPoseidon]

Well I couldn't find a rule to not necro a 5 year old thread so here we go:

I'm looking to change Pathfinder's Upgrade Path to go into Swordsman rather than Composite Bowman, reason being if you play with a bunch of mods that let you get Survivalism 3 and Scouting 3 through fighting a bunch of barbarians, those upgrades are a lot more useful on a melee unit, rather than ranged. I was going to create a simple SQL as @Nutty suggested, just replacing unit names, but since it's:
1. A unique CIV unit that's likely more complicated from what I read in here
2. Not a standard upgrade, but an upgrade possible only through Ancient Ruins (goody huts)
...I'm afraid it wouldn't be as simple as I originally thought.
Any help/advice is welcome.