SQL Issues

[bane_]

I'm using whoward's Clean Slate and Paz's Table SQL for Total Conversions, but I keep getting problems.
When starting the game, the Settler does not exist so I automatically lose. I have no time to get in the Civpedia to see if the other units are there or not.

Mostly they are these:

[3320.871] UnitClasses.Type may not be NULL
[3320.871] UnitClasses.Type may not be NULL

But Buildings also do not appear, I suppose is the same error as the aforementioned.

The Unit's SQL snippet:

Spoiler :

UPDATE Units SET Class = REPLACE (Type, 'UNIT_', 'UNITCLASS_') WHERE Class = NULL;
DELETE FROM UnitClasses;
INSERT INTO UnitClasses (Type, Description, DefaultUnit) SELECT Class, Description, Type FROM Units;

*PS I tried:
DELETE FROM UnitClasses WHERE TYPE NOT IN ('UNIT_SETTLER', 'UNIT_SCOUT', 'UNIT_ARCHER', 'UNIT_WARRIOR', 'UNIT_WORKER', 'UNIT_WORKBOAT', 'UNIT_PROPHET', 'UNIT_ENGINEER', 'UNIT_SCIENTIST', 'UNIT_ARTIST', 'UNIT_MERCHANT', 'UNIT_WRITER', 'UNIT_GREAT_GENERAL', 'UNIT_GREAT_ADMIRAL');
It still didn't worked.

The Unit's whole SQL:

Spoiler :

--
-- NOTE: Due to references in the DLL, it is a really bad idea to delete the following units (unless you fully mod the game to never need them!!!)
-- Great people, ie UNIT_PROPHET, UNIT_ENGINEER, UNIT_SCIENTIST, UNIT_ARTIST, UNIT_MERCHANT, UNIT_GREAT_GENERAL, UNIT_WRITER, UNIT_MUSICIAN, UNIT_GREAT_GENERAL, UNIT_GREAT_ADMIRAL
-- Religious units, ie UNIT_MISSIONARY, UNIT_INQUISITOR
-- Standard civilians, ie UNIT_SETTLER, UNIT_WORKER, UNIT_WORKBOAT
--

--
-- Delete ALL combat Units, except Warrior (and Barbarian variant)
-- Delete specific civilian units explicity, UNIT_ARCHAEOLOGIST
-- Delete ALL UnitPromotions, except those used elsewhere (including PostDefines)
--

-- MUST execute after DeleteBuildings.sql (as Projects are also deleted with Buildings)

DELETE FROM Units WHERE Type NOT IN ('UNIT_SETTLER', 'UNIT_SCOUT', 'UNIT_ARCHER', 'UNIT_WARRIOR', 'UNIT_WORKER', 'UNIT_WORKBOAT', 'UNIT_PROPHET', 'UNIT_ENGINEER', 'UNIT_SCIENTIST', 'UNIT_ARTIST', 'UNIT_MERCHANT', 'UNIT_WRITER', 'UNIT_GREAT_GENERAL', 'UNIT_GREAT_ADMIRAL');
DELETE FROM Units WHERE Type IN ('UNIT_ARCHAEOLOGIST');
INSERT INTO Units
(TYPE, Combat, Moves, Cost, IconAtlas, PortraitIndex)
SELECT ('UNIT_WBC_WILDLING_WARRIOR'), Combat, Moves, Cost, IconAtlas, PortraitIndex FROM Units WHERE (TYPE = 'UNIT_WARRIOR');
DELETE FROM Units WHERE Type = 'UNIT_WARRIOR';
DELETE FROM Unit_ClassUpgrades WHERE UnitType IN ('UNIT_BARBARIAN_WARRIOR');

-- Leave melee units just the basic promotions
DELETE FROM UnitPromotions_UnitCombats WHERE PromotionType NOT IN ('PROMOTION_INSTA_HEAL', 'PROMOTION_CAN_MOVE_AFTER_ATTACKING', 'PROMOTION_ONLY_DEFENSIVE', 'PROMOTION_AMPHIBIOUS', 'PROMOTION_DEFENSIVE_EMBARKATION', 'PROMOTION_UNWELCOME_EVANGELIST', 'PROMOTION_ONLY_ATTACKS_CITIES', 'PROMOTION_EMBARKATION');


-- TIDY UP
DELETE FROM Unit_AITypes WHERE UnitType NOT IN (SELECT Type FROM Units);
DELETE FROM Unit_BuildingClassRequireds WHERE UnitType NOT IN (SELECT Type FROM Units);
DELETE FROM Unit_Buildings WHERE UnitType NOT IN (SELECT Type FROM Units);
DELETE FROM Unit_Builds WHERE UnitType NOT IN (SELECT Type FROM Units);
DELETE FROM Unit_ClassUpgrades WHERE UnitType NOT IN (SELECT Type FROM Units);
DELETE FROM Unit_Flavors WHERE UnitType NOT IN (SELECT Type FROM Units);
DELETE FROM Unit_FreePromotions WHERE UnitType NOT IN (SELECT Type FROM Units);
DELETE FROM Unit_GreatPersons WHERE UnitType NOT IN (SELECT Type FROM Units);
DELETE FROM Unit_NotAITypes WHERE UnitType NOT IN (SELECT Type FROM Units);
DELETE FROM Unit_ProductionModifierBuildings WHERE UnitType NOT IN (SELECT Type FROM Units);
DELETE FROM Unit_ProductionTraits WHERE UnitType NOT IN (SELECT Type FROM Units);
DELETE FROM Unit_ResourceQuantityRequirements WHERE UnitType NOT IN (SELECT Type FROM Units);
DELETE FROM Unit_TechTypes WHERE UnitType NOT IN (SELECT Type FROM Units);
DELETE FROM Unit_UniqueNames WHERE UnitType NOT IN (SELECT Type FROM Units);
DELETE FROM Unit_YieldFromKills WHERE UnitType NOT IN (SELECT Type FROM Units);
DELETE FROM UnitGameplay2DScripts WHERE UnitType NOT IN (SELECT Type FROM Units);

-- There are indirect references in the DLL to BUILD_REMOVE_*, so make sure not to delete these!!!
DELETE FROM Builds WHERE Type NOT IN (SELECT BuildType FROM Unit_Builds);
DELETE FROM Civilization_UnitClassOverrides WHERE UnitType NOT IN (SELECT Type FROM Units);
DELETE FROM UnitClasses WHERE DefaultUnit NOT IN (SELECT Type FROM Units);
UPDATE Units SET GoodyHutUpgradeUnitClass=NULL WHERE GoodyHutUpgradeUnitClass NOT IN (SELECT Type FROM UnitClasses);
DELETE FROM Civilization_UnitClassOverrides WHERE UnitClassType NOT IN (SELECT Type FROM UnitClasses);
DELETE FROM Policy_FreeUnitClasses WHERE UnitClassType NOT IN (SELECT Type FROM UnitClasses);
DELETE FROM UnitPromotions_UnitClasses WHERE UnitClassType NOT IN (SELECT Type FROM UnitClasses);
DELETE FROM UnitPromotions_UnitCombats WHERE UnitCombatType NOT IN (SELECT DISTINCT CombatClass FROM Units WHERE CombatClass IS NOT NULL);
DELETE FROM UnitPromotions_UnitCombatMods WHERE UnitCombatType NOT IN (SELECT DISTINCT CombatClass FROM Units WHERE CombatClass IS NOT NULL);

-- Clear out the UnitPromotions table
DELETE FROM UnitPromotions WHERE Type NOT IN (SELECT DISTINCT PromotionType FROM (SELECT AdjacentUnitFreePromotion AS PromotionType FROM Features UNION SELECT PromotionType FROM UnitPromotions_UnitCombats UNION SELECT PromotionType FROM Unit_FreePromotions UNION SELECT Key AS PromotionType FROM PostDefines WHERE Key LIKE 'PROMOTION_%') WHERE PromotionType IS NOT NULL);
DELETE FROM UnitPromotions_CivilianUnitType WHERE PromotionType NOT IN (SELECT Type FROM UnitPromotions);
DELETE FROM UnitPromotions_Domains WHERE PromotionType NOT IN (SELECT Type FROM UnitPromotions);
DELETE FROM UnitPromotions_Features WHERE PromotionType NOT IN (SELECT Type FROM UnitPromotions);
DELETE FROM UnitPromotions_PostCombatRandomPromotion WHERE PromotionType NOT IN (SELECT Type FROM UnitPromotions);
DELETE FROM UnitPromotions_Terrains WHERE PromotionType NOT IN (SELECT Type FROM UnitPromotions);

-- DELETE FROM DeleteUnits;

DELETE FROM UnitCombatInfos;
INSERT INTO UnitCombatInfos (TYPE) VALUES
('UNITCOMBAT_RECON'),
('UNITCOMBAT_DRAGON'),
('UNITCOMBAT_MOUNTED'),
('UNITCOMBAT_MOUNTED_RANGED'),
('UNITCOMBAT_RANGED'),
('UNITCOMBAT_MELEE'),
('UNITCOMBAT_MECHANICAL'),
('UNITCOMBAT_PRIEST'),
('UNITCOMBAT_MAGE'),
('UNITCOMBAT_NAVAL'),
('UNITCOMBAT_MONSTER'),
('UNITCOMBAT_NAVAL_RANGED'),
('UNITCOMBAT_HERO'), -- unused for now
('UNITCOMBAT_TITAN'); -- unused for now

--UNITS
---GENERIC
INSERT INTO Units (TYPE, Combat, RangedCombat, Range, Moves, Cost, DOMAIN, DefaultUnitAI, UnitFlagAtlas, UnitFlagIconOffset, PortraitIndex) VALUES
--RECON
('UNIT_WBC_INFILTRATOR', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_EXPLORE', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_SABOTEUR', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_EXPLORE', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_PATHFINDER', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_EXPLORE', 'UNIT_FLAG_ATLAS', 5, 0),
--RANGED
('UNIT_WBC_SQUIRE', 1, 1, 1, 2, 10, 'DOMAIN_LAND', 'UNITAI_RANGED', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_COMPOSITE_BOWMAN', 1, 1, 2, 2, 10, 'DOMAIN_LAND', 'UNITAI_RANGED', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_CROSSBOWMAN', 1, 1, 1, 2, 10, 'DOMAIN_LAND', 'UNITAI_RANGED', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_HAND_AXE', 1, 1, 1, 2, 10, 'DOMAIN_LAND', 'UNITAI_RANGED', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_SKIRMISHER', 1, 1, 1, 2, 10, 'DOMAIN_LAND', 'UNITAI_RANGED', 'UNIT_FLAG_ATLAS', 5, 0),
--MOUNTED
('UNIT_WBC_HORSEMAN', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_CHARIOTEER', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_MAN_CENTAUR', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_LANCER', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_HEAVY_CAVALRY', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
--MOUNTED_ARCHER
('UNIT_WBC_CHARIOT_ARCHER', 1, 1, 1, 2, 10, 'DOMAIN_LAND', 'UNITAI_RANGED', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_HORSE_ARCHER', 1, 1, 2, 2, 10, 'DOMAIN_LAND', 'UNITAI_RANGED', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_NUMIDIAN', 1, 1, 1, 2, 10, 'DOMAIN_LAND', 'UNITAI_RANGED', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_WAR_CHARIOT', 1, 1, 1, 2, 10, 'DOMAIN_LAND', 'UNITAI_RANGED', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_WAR_ELEPHANT', 1, 1, 1, 2, 10, 'DOMAIN_LAND', 'UNITAI_RANGED', 'UNIT_FLAG_ATLAS', 5, 0),
--MELEE
('UNIT_WBC_WILDLING_BERSERKER', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_WILDLING_SPEARMAN', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_SPEARMAN', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_SWORDSMAN', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_TORCHMAN', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_PIKEMAN', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_SAMURAI', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_FOREST_BRAWLER', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_PALADIN', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_CRUSADER', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_DOOM_KNIGHT', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
--MECHANICAL
('UNIT_WBC_CATAPULT', 1, 1, 2, 2, 10, 'DOMAIN_LAND', 'UNITAI_CITY_BOMBARD', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_BATTERING_RAM', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_CITY_BOMBARD', 'UNIT_FLAG_ATLAS', 5, 0),
--MONSTER
('UNIT_WBC_UNICORN', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_CENTAUR_MONSTER', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_GIANT', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_OGRE', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_BASILISK', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_GRIFFON', 1, 0, 0, 2, 10, 'DOMAIN_AIR', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_EAGLE', 1, 0, 0, 2, 10, 'DOMAIN_AIR', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_PEGASUS', 1, 0, 0, 2, 10, 'DOMAIN_AIR', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_ARCHON', 1, 0, 0, 2, 10, 'DOMAIN_AIR', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_DOPPELGANGER', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_HARPY', 1, 0, 0, 2, 10, 'DOMAIN_AIR', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_BLACK_DOG', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_SPIDER_QUEEN', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_IMP', 1, 0, 0, 2, 10, 'DOMAIN_AIR', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
--NAVAL RANGED
('UNIT_WBC_GALLEY', 1, 1, 1, 2, 10, 'DOMAIN_SEA', 'UNITAI_ATTACK_SEA', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_DROMON', 1, 1, 1, 2, 10, 'DOMAIN_SEA', 'UNITAI_ATTACK_SEA', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_FRIGATE', 1, 1, 1, 2, 10, 'DOMAIN_SEA', 'UNITAI_ATTACK_SEA', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_GALLEAS', 1, 1, 2, 2, 10, 'DOMAIN_SEA', 'UNITAI_ATTACK_SEA', 'UNIT_FLAG_ATLAS', 5, 0),
--NAVAL MELEE
('UNIT_WBC_TRIREME', 1, 0, 0, 2, 10, 'DOMAIN_SEA', 'UNITAI_ATTACK_SEA', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_LONGBOAT', 1, 0, 0, 2, 10, 'DOMAIN_SEA', 'UNITAI_ATTACK_SEA', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_CARAVEL', 1, 0, 0, 2, 10, 'DOMAIN_SEA', 'UNITAI_ATTACK_SEA', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_PRIVATEER', 1, 0, 0, 2, 10, 'DOMAIN_SEA', 'UNITAI_ATTACK_SEA', 'UNIT_FLAG_ATLAS', 5, 0),
--SECONDARY SPELLCASTERS
('UNIT_WBC_SACRIFICER', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_MONK', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_MYSTIC', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
--SPELLCASTERS
('UNIT_WBC_SORCERESS', 1, 1, 1, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_NECROMANCER', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_MARAUDER', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_WIZARD', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_SUMMONER', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
---Runemaster is a Civilian unit
('UNIT_WBC_RUNEMASTER', 0, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_DRUID', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_CLERIC', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_ILLUSIONIST', 1, 0, 0, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0),
('UNIT_WBC_ORACLE', 1, 1, 1, 2, 10, 'DOMAIN_LAND', 'UNITAI_ATTACK', 'UNIT_FLAG_ATLAS', 5, 0);



UPDATE Units SET Class = REPLACE (Type, 'UNIT_', 'UNITCLASS_') WHERE Class = NULL;
DELETE FROM UnitClasses;
INSERT INTO UnitClasses (Type, Description, DefaultUnit) SELECT Class, Description, Type FROM Units;
UPDATE Units SET Description = 'TXT_KEY_WBC_' || TYPE WHERE TYPE NOT IN ('UNIT_SETTLER', 'UNIT_SCOUT', 'UNIT_ARCHER', 'UNIT_WARRIOR', 'UNIT_WORKER', 'UNIT_WORKBOAT', 'UNIT_PROPHET', 'UNIT_ENGINEER', 'UNIT_SCIENTIST', 'UNIT_ARTIST', 'UNIT_MERCHANT', 'UNIT_WRITER', 'UNIT_GREAT_GENERAL', 'UNIT_GREAT_ADMIRAL');
UPDATE Units SET Help = Description || '_HELP', Strategy = Description || '_STRATEGY', Civilopedia = Description || '_PEDIA' WHERE TYPE NOT IN ('UNIT_SETTLER', 'UNIT_SCOUT', 'UNIT_ARCHER', 'UNIT_WARRIOR', 'UNIT_WORKER', 'UNIT_WORKBOAT', 'UNIT_PROPHET', 'UNIT_ENGINEER', 'UNIT_SCIENTIST', 'UNIT_ARTIST', 'UNIT_MERCHANT', 'UNIT_WRITER', 'UNIT_GREAT_GENERAL', 'UNIT_GREAT_ADMIRAL');

--Paz's fixinator
CREATE TABLE IDRemapper ( id INTEGER PRIMARY KEY AUTOINCREMENT, Type TEXT );
INSERT INTO IDRemapper (Type) SELECT Type FROM UnitClasses ORDER BY ID;
UPDATE UnitClasses SET ID = ( SELECT IDRemapper.id-1 FROM IDRemapper WHERE UnitClasses.Type = IDRemapper.Type);
DROP TABLE IDRemapper;
UPDATE Units SET IconAtlas = 'WBC_UNIT_ATLAS_GENERIC' WHERE Class = NULL;
UPDATE Units SET HurryCostModifier = 100,
FaithCost = 100,
RequiresFaithPurchaseEnabled = 1,
MilitarySupport = 1,
MilitaryProduction = 1,
Pillage = 1,
IgnoreBuildingDefense = 1,
AdvancedStartCost = 30
WHERE CombatClass IN ('UNITCOMBAT_RECON',
'UNITCOMBAT_MOUNTED',
'UNITCOMBAT_MOUNTED_RANGED',
'UNITCOMBAT_RANGED',
'UNITCOMBAT_MELEE',
'UNITCOMBAT_MECHANICAL',
'UNITCOMBAT_PRIEST',
'UNITCOMBAT_MAGE',
'UNITCOMBAT_NAVAL',
'UNITCOMBAT_MONSTER',
'UNITCOMBAT_NAVAL_RANGED');





/*-- Renumber the UnitClasses
CREATE TABLE Temp_UnitClasses AS SELECT * FROM UnitClasses;
UPDATE Temp_UnitClasses SET ID=(rowid-1);
DELETE FROM UnitClasses;
INSERT INTO UnitClasses SELECT * FROM Temp_UnitClasses ORDER BY rowid ASC;
DROP TABLE Temp_UnitClasses;*/

-- Renumber the Units
CREATE TABLE Temp_Units AS SELECT * FROM Units;
UPDATE Temp_Units SET ID=(rowid-1);
DELETE FROM Units;
INSERT INTO Units SELECT * FROM Temp_Units ORDER BY rowid ASC;
DROP TABLE Temp_Units;

-- Renumber the UnitPromotions
CREATE TABLE Temp_UnitPromotions AS SELECT * FROM UnitPromotions;
UPDATE Temp_UnitPromotions SET ID=(rowid-1);
DELETE FROM UnitPromotions;
INSERT INTO UnitPromotions SELECT * FROM Temp_UnitPromotions ORDER BY rowid ASC;
DROP TABLE Temp_UnitPromotions;

-- DELETE FROM RenumberUnits;

The Building's SQL snippet:
*next post*

 

[bane_]

The Building's SQL snippet:

Spoiler :

UPDATE Buildings SET BuildingClass = REPLACE(Type, 'BUILDING_', 'BUILDINGCLASS_');
DELETE FROM BuildingClasses;
INSERT INTO BuildingClasses
(Type, DefaultBuilding, Description)
SELECT BuildingClass, Type, Description FROM Buildings GROUP BY BuildingClass ORDER BY ID;

The Building's whole SQL:

Spoiler :

--
-- Delete ALL Buildings and associated BuildingClasses, except Palace and Monument
-- Delete ALL Projects, except Apollo (as that's required for the science victory) and it's associated components (PROJECT_SS_BOOSTER, PROJECT_SS_COCKPIT, PROJECT_SS_ENGINE, PROJECT_SS_STASIS_CHAMBER)
--

DELETE FROM Buildings WHERE TYPE NOT IN ('BUILDING_GARDEN',
'BUILDING_SEAPORT',
'BUILDING_WINDMILL',
'BUILDING_GRANARY',
'BUILDING_WATERMILL',
'BUILDING_LIGHTHOUSE',
'BUILDING_WORKSHOP',
'BUILDING_HARBOR',
'BUILDING_FORGE',
'BUILDING_STONEWORKS',
'BUILDING_STABLE',
'BUILDING_MONUMENT',
'BUILDING_LIBRARY',
'BUILDING_AMPHITHEATER',
'BUILDING_SHRINE',
'BUILDING_TEMPLE',
'BUILDING_PYRAMID',
'BUILDING_BANK',
'BUILDING_CARAVANSARY',
'BUILDING_MARKET',
'BUILDING_MINT',
'BUILDING_CASTLE',
'BUILDING_ARSENAL',
'BUILDING_ARMORY',
'BUILDING_BARRACKS',
'BUILDING_WALLS',
'BUILDING_COURTHOUSE',
'BUILDING_OBSERVATORY',
'BUILDING_COLOSSEUM',
'BUILDING_PALACE',
'BUILDING_CIRCUS');
DELETE FROM Projects WHERE Type NOT IN ('PROJECT_APOLLO_PROGRAM', 'PROJECT_SS_BOOSTER', 'PROJECT_SS_COCKPIT', 'PROJECT_SS_ENGINE', 'PROJECT_SS_STASIS_CHAMBER');


-- TIDY UP
DELETE FROM Building_AreaYieldModifiers WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_BuildingClassHappiness WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_BuildingClassYieldChanges WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_ClassesNeededInCity WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_DomainFreeExperiences WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_DomainFreeExperiencePerGreatWork WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_DomainProductionModifiers WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_FeatureYieldChanges WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_Flavors WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_FreeSpecialistCounts WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_FreeUnits WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_GlobalYieldModifiers WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_HurryModifiers WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_LakePlotYieldChanges WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_LocalResourceAnds WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_LocalResourceOrs WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_LockedBuildingClasses WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_PrereqBuildingClasses WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_ResourceCultureChanges WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_ResourceFaithChanges WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_ResourceQuantity WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_ResourceQuantityRequirements WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_ResourceYieldChanges WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_ResourceYieldModifiers WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_RiverPlotYieldChanges WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_SeaPlotYieldChanges WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_SeaResourceYieldChanges WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_SpecialistYieldChanges WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_TechAndPrereqs WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_TerrainYieldChanges WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_ThemingBonuses WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_UnitCombatFreeExperiences WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_UnitCombatProductionModifiers WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_YieldChanges WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_YieldChangesPerPop WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_YieldChangesPerReligion WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_TechEnhancedYieldChanges WHERE BuildingType NOT IN (SELECT Type FROM Buildings);
DELETE FROM Building_YieldModifiers WHERE BuildingType NOT IN (SELECT Type FROM Buildings);

UPDATE Policies SET FreeBuildingOnConquest=NULL WHERE FreeBuildingOnConquest NOT IN (SELECT Type FROM Buildings);
UPDATE LeagueProjectRewards SET Building=NULL WHERE Building NOT IN (SELECT Type FROM Buildings);

DELETE FROM BuildingClasses WHERE DefaultBuilding NOT IN (SELECT Type FROM Buildings);
DELETE FROM BuildingClass_VictoryThresholds WHERE BuildingClassType NOT IN (SELECT Type FROM BuildingClasses);
DELETE FROM Belief_BuildingClassFaithPurchase WHERE BuildingClassType NOT IN (SELECT Type FROM BuildingClasses);
DELETE FROM Belief_BuildingClassHappiness WHERE BuildingClassType NOT IN (SELECT Type FROM BuildingClasses);
DELETE FROM Belief_BuildingClassTourism WHERE BuildingClassType NOT IN (SELECT Type FROM BuildingClasses);
DELETE FROM Belief_BuildingClassYieldChanges WHERE BuildingClassType NOT IN (SELECT Type FROM BuildingClasses);
DELETE FROM Civilization_BuildingClassOverrides WHERE BuildingClassType NOT IN (SELECT Type FROM BuildingClasses);
DELETE FROM Policy_BuildingClassCultureChanges WHERE BuildingClassType NOT IN (SELECT Type FROM BuildingClasses);
DELETE FROM Policy_BuildingClassHappiness WHERE BuildingClassType NOT IN (SELECT Type FROM BuildingClasses);
DELETE FROM Policy_BuildingClassProductionModifiers WHERE BuildingClassType NOT IN (SELECT Type FROM BuildingClasses);
DELETE FROM Policy_BuildingClassTourismModifiers WHERE BuildingClassType NOT IN (SELECT Type FROM BuildingClasses);
DELETE FROM Policy_BuildingClassYieldChanges WHERE BuildingClassType NOT IN (SELECT Type FROM BuildingClasses);
DELETE FROM Policy_BuildingClassYieldModifiers WHERE BuildingClassType NOT IN (SELECT Type FROM BuildingClasses);
DELETE FROM Unit_BuildingClassRequireds WHERE BuildingClassType NOT IN (SELECT Type FROM BuildingClasses);


DELETE FROM Project_Flavors WHERE ProjectType NOT IN (SELECT Type FROM Projects);
DELETE FROM Project_Prereqs WHERE ProjectType NOT IN (SELECT Type FROM Projects);
DELETE FROM Project_ResourceQuantityRequirements WHERE ProjectType NOT IN (SELECT Type FROM Projects);
DELETE FROM Project_VictoryThresholds WHERE ProjectType NOT IN (SELECT Type FROM Projects);

DELETE FROM Units WHERE ProjectPrereq IS NOT NULL AND ProjectPrereq NOT IN (SELECT Type FROM Projects);
DELETE FROM Units WHERE SpaceshipProject IS NOT NULL AND SpaceshipProject NOT IN (SELECT Type FROM Projects);

-- DELETE FROM DeleteBuildings;
--WBCiV
--ALTER TABLE Buildings
--ADD UnitManaChange integer;
INSERT INTO Buildings (Type, Cost, PrereqTech, IconAtlas, PortraitIndex) VALUES
--SPECIAL
('BUILDING_WBC_REFORMATORY', 1, NULL, 'ICON_ATLAS_WBC_BUILDING_SPECIAL', 0),
('BUILDING_WBC_MEETING_HOUSE', 1, NULL, 'ICON_ATLAS_WBC_BUILDING_SPECIAL', 0),
('BUILDING_WBC_ASYLUM', 1, NULL, 'ICON_ATLAS_WBC_BUILDING_SPECIAL', 0),
--HAPPINESS
('BUILDING_WBC_ARENA', 1, NULL, 'ICON_ATLAS_WBC_BUILDING_HAPPINESS', 0);



UPDATE Buildings SET Capital = 1, NeverCapture = 1 WHERE TYPE IN ('BUILDING_WBC_BASTION_I',
'BUILDING_WBC_BASTION_II',
'BUILDING_WBC_BASTION_III',
'BUILDING_WBC_BASTION_IV',
'BUILDING_WBC_BASTION_V');
UPDATE Buildings SET Defense = 250, FreeTechs = 1 WHERE TYPE = 'BUILDING_WBC_BASTION_I';
UPDATE Buildings SET Defense = 300, HealRateChange = 5 WHERE TYPE = 'BUILDING_WBC_BASTION_II';
UPDATE Buildings SET Defense = 350, ExtraCityHitPoints = 15, HealRateChange = 10 WHERE TYPE = 'BUILDING_WBC_BASTION_III';
UPDATE Buildings SET Defense = 450, ExtraCityHitPoints = 30, HealRateChange = 15 WHERE TYPE = 'BUILDING_WBC_BASTION_IV';
UPDATE Buildings SET Defense = 600, ExtraCityHitPoints = 75, HealRateChange = 25 WHERE TYPE = 'BUILDING_WBC_BASTION_V';
--UPDATE Buildings SET ConquestProb = 33 WHERE NeverCapture NOT 1;
UPDATE Buildings SET GoldMaintenance = 1 WHERE TYPE NOT IN ('BUILDING_WBC_BASTION_I',
'BUILDING_WBC_BASTION_II',
'BUILDING_WBC_BASTION_III',
'BUILDING_WBC_BASTION_IV',
'BUILDING_WBC_BASTION_V');
UPDATE Buildings SET Happiness = 1 WHERE TYPE IN ('BUILDING_WBC_CIRCUS',
'BUILDING_WBC_DYIER',
'BUILDING_WBC_LIBRARY',
'BUILDING_WBC_ORATORIUM',
'BUILDING_WBC_ASYLUM',
'BUILDING_WBC_ARENA');
UPDATE Buildings SET Happiness = 2 WHERE TYPE IN ('BUILDING_WBC_COLOSSEUM',
'BUILDING_WBC_BREWERY');
UPDATE Buildings SET Defense = 200, ExtraCityHitPoints = 25 WHERE TYPE IN ('BUILDING_WBC_GARRISON',
'BUILDING_WBC_RAMPARTS');
UPDATE Buildings SET Defense = 200, ExtraCityHitPoints = 40 WHERE TYPE = 'BUILDING_WBC_CASTLE';
UPDATE Buildings SET Defense = 300, ExtraCityHitPoints = 45 WHERE TYPE = 'BUILDING_WBC_ARSENAL';
UPDATE Buildings SET Defense = 450, ExtraCityHitPoints = 65 WHERE TYPE = 'BUILDING_WBC_FORTRESS';
UPDATE Buildings SET WonderProductionModifier = 15 WHERE TYPE = 'BUILDING_WBC_FOUNDRY';
UPDATE Buildings SET Experience = 5 WHERE TYPE = 'BUILDING_WBC_ARMORY';
UPDATE Buildings SET Experience = 5 WHERE TYPE = 'BUILDING_WBC_FORTRESS';
UPDATE Buildings SET UnhappinessModifier = 7 WHERE TYPE = 'BUILDING_WBC_ASYLUM';
UPDATE Buildings SET UnhappinessModifier = 4 WHERE TYPE IN ('BUILDING_WBC_HEALER',
'BUILDING_WBC_LIBRARY');

INSERT INTO Building_DomainFreeExperiences (BuildingType, DomainType, Experience) VALUES
('BUILDING_WBC_GARRISON', 'DOMAIN_LAND', 2),
('BUILDING_WBC_BARRACKS', 'DOMAIN_LAND', 3),
('BUILDING_WBC_FORTRESS', 'DOMAIN_LAND', 5);
INSERT INTO Building_UnitCombatFreeExperiences (BuildingType, UnitCombatType, Experience) VALUES
('BUILDING_WBC_SHOOTING_RANGE', 'UNITCOMBAT_RANGED', 5),
('BUILDING_WBC_SHOOTING_RANGE', 'UNITCOMBAT_MOUNTED_RANGED', 5),
('BUILDING_WBC_ARENA', 'UNITCOMBAT_MELEE', 3),
('BUILDING_WBC_ARENA', 'UNITCOMBAT_MONSTER', 5),
('BUILDING_WBC_CAGE', 'UNITCOMBAT_MONSTER', 3),
('BUILDING_WBC_COLLEGE', 'UNITCOMBAT_MAGE', 7),
('BUILDING_WBC_COLLEGE', 'UNITCOMBAT_PRIEST', 7);
INSERT INTO Building_YieldChanges (BuildingType, YieldType, Yield) VALUES
('BUILDING_WBC_BASTION_I', 'YIELD_GOLD', 3),
('BUILDING_WBC_BASTION_I', 'YIELD_PRODUCTION', 3),
('BUILDING_WBC_BASTION_I', 'YIELD_SCIENCE', 100),
('BUILDING_WBC_BASTION_I', 'YIELD_CULTURE', 3);



UPDATE Buildings SET Description = 'TXT_KEY_WBC_' || TYPE WHERE TYPE;
UPDATE Buildings SET Help = Description || '_HELP', Strategy = Description || '_STRATEGY', Civilopedia = Description || '_PEDIA';
UPDATE Buildings SET BuildingClass = REPLACE(Type, 'BUILDING_', 'BUILDINGCLASS_');
DELETE FROM BuildingClasses;
INSERT INTO BuildingClasses
(Type, DefaultBuilding, Description)
SELECT BuildingClass, Type, Description FROM Buildings GROUP BY BuildingClass ORDER BY ID;



-- Renumber the BuildingClasses
CREATE TABLE Temp_BuildingClasses AS SELECT * FROM BuildingClasses;
UPDATE Temp_BuildingClasses SET ID=(rowid-1);
DELETE FROM BuildingClasses;
INSERT INTO BuildingClasses SELECT * FROM Temp_BuildingClasses ORDER BY rowid ASC;
DROP TABLE Temp_BuildingClasses;

-- Renumber the Buildings
CREATE TABLE Temp_Buildings AS SELECT * FROM Buildings;
UPDATE Temp_Buildings SET ID=(rowid-1);
DELETE FROM Buildings;
INSERT INTO Buildings SELECT * FROM Temp_Buildings ORDER BY rowid ASC;
DROP TABLE Temp_Buildings;

-- Renumber the Projects
CREATE TABLE Temp_Projects AS SELECT * FROM Projects;
UPDATE Temp_Projects SET ID=(rowid-1);
DELETE FROM Projects;
INSERT INTO Projects SELECT * FROM Temp_Projects ORDER BY rowid ASC;
DROP TABLE Temp_Projects;

-- DELETE FROM RenumberBuildings;

 

[whoward69]

OK, you've misunderstood how to use the clean slate

It is in three parts

1) Delete everything not needed (these are the DeleteXyz.sql files)
2) Add everything you need for your scenario (these are the xml files)
3) Renumber everything to make sure the DLL is happy (these are the RenumberXzy.sql files)

• DO NOT EDIT the Delete/Renumber.sql files (with the possible exception of the initial DELETE .. WHERE Type IN(...) statements if you don't want to remove everything from some tables). You should be making NO changes at all after the -- TIDY UP lines
• Add your own units, either via XML or SQL in their own files, at the same place in the list of the OnModActivated->UpdateDatabase files as the sample XML files adding back in the civs/leaders/traits/uniques
• DO NOT USE THE FIXINATOR - this is what the RenumberXyz.sql files do

And add stuff in small steps. Don't try to add approx. 70 units in one go and then wonder which one broke the game!

 

[Nutty]

@bane_, you've been around long enough, you should know it makes our job so much easier if you attach the built mod.

Anyway, the very first snippet is really bizarre. I'm not entirely sure what you're even going for unless you've done some earlier stuff to clear out the Class column.

The second version (*PS) that you tried is close, except that the UnitClasses Type column is UNITCLASS_*, not UNIT_*.

 

[bane_]

Ok! Done it all, the Units are now not showing up in the pedia (probably due to the new error) and buildings never did, so that's that.
The new error is:

[8768.831] column Type is not unique
[8768.831] column Type is not unique

BUT it's not in the Units table, probably something I'm missing about the SQL automation (replicated UnitClass I suppose, but I can't quite get why). I opened the DebugDatabase to see if I could figure it out which UnitClass was being replicated, but since the whole file failed (I assume), I still see all vanilla Classes and Units (only) - or, possibly that file wasn't supposed to have the modifications at all, because I can't see my new techs in it, while the game actually show them all.
I can confirm it is the UnitClasses.Type that is creating the error, as noted from trying to edit the database through SQLiteSpy.


*.civ5mod file added, as I should've done from the beginning.


On a barely related note, two of my techs show as having the '1' title, even though they have the right TXT_KEY assigned to them. Is that a known issue?

Spoiler :

 

[whoward69]

[8768.831] column Type is not unique

Another good reason to split your SQL/XML up into smaller chunks in their own files

The 8768.831 bits is a time-stamp. Look in stopwatch.log and it lists the order it processes the XML/SQL files in, but, and it's a very important but, the time-stamps in stopwatch.log can be used to work out which file generated the error(s) in database/xml.log

 

[bane_]

Huh, that's a very interesting, useful information.
Although in this case it won't matter, I already know which file (and even which command) the error comes from, it's the UnitClass automation.

There must be something I'm missing regarding the automation, because I copied/pasted from Paz's Tables SQL thread.

I'll try to add a few units manually, without the automation and see how it goes.

 

[bane_]

I can attest now with certainty: the problem was with the UnitClasses and CombatClasses.
After a few tweaks and seeing the unit but not being able to train/buy it, I finally made them appear by manually inputting the UnitClasses data, humbling me in learning that I'm not as good with SQL as I thought.

I'll make some extra tests with automation after I read a few tutorials on SQL now.
EDIT: Hah, funny. Right after I posted, I thought about the 'TYPE not unique' error and realized I was adding UnitClasses for EVERY unit... which included the ones that weren't deleted - hence already had a UnitClass row -, prompting the error!

Thanks for the help, guys!!