SQL only partly working

Timmy563 · Jul 1, 2015

Hi guys,

I recently decided to try and start modding. So I read a bit as to how to start and made this rough conceptual SQL to update pantheon beliefs:

Spoiler :

Code:

INSERT INTO Belief_ImprovementYieldChanges
			(BeliefType,				ImprovementType,			YieldType,		Yield)
VALUES		('BELIEF_GODDESS_HUNT',		'IMPROVEMENT_CAMP',			'YIELD_FAITH',		2);

INSERT INTO Belief_ImprovementYieldChanges
			(BeliefType,				ImprovementType,			YieldType,		Yield)
VALUES		('BELIEF_FERTILITY_RITES',	'IMPROVEMENT_FARM',			'YIELD_FAITH',		1);

UPDATE	Belief_CityYieldChanges
SET		Yield = 2
WHERE	BeliefType = 'BELIEF_GOD_CRAFTSMEN';
INSERT INTO	Belief_CityYieldChanges
			(BeliefType,				YieldType,		Yield)
VALUES		('BELIEF_GOD_CRATFSMEN',	'YIELD_FAITH',	5);

INSERT INTO Belief_ImprovementYieldChanges
			(BeliefType,				ImprovementType,			YieldType,		Yield)
VALUES		('BELIEF_GOD_SEA',			'IMPROVEMENT_FISHING_BOAT',	'YIELD_FAITH',		2);

INSERT INTO Belief_ImprovementYieldChanges
			(BeliefType,				ImprovementType,			YieldType,		Yield)
VALUES		('BELIEF_OPEN_SKY',			'IMPROVEMENT_PASTURE',		'YIELD_FAITH',		2);

INSERT INTO Belief_YieldChangeTradeRoute
			(BeliefType,				YieldType,		Yield)
VALUES		('BELIEF_MESSENGER_GODS',	'YIELD_FAITH',	5);

UPDATE	Belief_YieldChangeNaturalWonder
SET		Yield = 5
WHERE	BeliefType = 'BELIEF_ONE_WITH_NATURE';
INSERT INTO Belief_YieldChangeNaturalWonder
			(BeliefType,				YieldType,			Yield)
VALUES		('BELIEF_ONE_WITH_NATURE',	'YIELD_PRODUCTION',	1),
			('BELIEF_ONE_WITH_NATURE',	'YIELD_GOLD',		1),
			('BELIEF_ONE_WITH_NATURE',	'YIELD_CULTURE',	1),
			('BELIEF_ONE_WITH_NATURE',	'YIELD_SCIENCE',	1);

UPDATE	Belief_ResourceYieldChanges
SET		Yield = 2
WHERE	BeliefType = 'BELIEF_FORMAL_LITURGY' AND YieldType = 'YIELD_FAITH';

INSERT INTO Belief_ResourceYieldChanges
			(BeliefType,				ResourceType,		YieldType,		Yield)
VALUES		('BELIEF_FORMAL_LITURGY',	'RESOURCE_GOLD',	'YIELD_GOLD',	2),
			('BELIEF_FORMAL_LITURGY',	'RESOURCE_SILVER',	'YIELD_GOLD',	2);

INSERT INTO Belief_ImprovementYieldChanges
			(BeliefType,				ImprovementType,			YieldType,		Yield)
VALUES		('BELIEF_STONE_CIRCLES',	'IMPROVEMENT_QUARRY',		'YIELD_PRODUCTION',	1),
			('BELIEF_STONE_CIRCLES',	'IMPROVEMENT_QUARRY',		'YIELD_GOLD',		1);

ALTER TABLE	Beliefs
ADD COLUMN	FriendlyTerritoryStrengthModifier INTEGER DEFAULT = 0;
UPDATE	Beliefs
SET		FriendlyTerritoryStrengthModifier = 15,
		FaithFromKills = 200
WHERE	Type = 'BELIEF_GOD_WAR';

INSERT INTO Belief_FeatureYieldChanges
			(BeliefType,				FeatureType,		YieldType,		Yield)
VALUES		('BELIEF_SACRED_WATERS',	'FEATURE_RIVER',	'YIELD_FAITH',	1),
			('BELIEF_SACRED_WATERS',	'FEATURE_RIVER',	'YIELD_GOLD',	1);

CREATE TABLE	Belief_YieldChangePerHappiness	(
				BeliefType						TEXT	REFERENCES Beliefs(Type),
				YieldType						TEXT	REFERENCES Yields(Type),
				Yield							FLOAT								DEFAULT 0);
INSERT INTO	Belief_YieldChangePerHappiness
			(BeliefType,			YieldType,			Yield)
VALUES		('BELIEF_GODDESS_LOVE',	'YIELD_FAITH',		1),
			('BELIEF_GODDESS_LOVE',	'YIELD_CULTURE',	1);

CREATE TABLE	Belief_YieldPerTile (
				BeliefType			TEXT	REFERENCES Beliefs(Type),
				YieldType			TEXT	REFERENCES Yields(Type),
				Yield				FLOAT								DEFAULT 0);
INSERT INTO	Belief_YieldPerTile
			(BeliefType,						YieldType,		Yield)
VALUES		('BELIEF_RELIGIOUS_SETTLEMENTS',	'YIELD_FAITH',	0.5);
CREATE TABLE	Belief_YieldPerBorderExpansion (
				BeliefType						TEXT	REFERENCES Beliefs(Type),
				YieldType						TEXT	REFERENCES Yields(Type),
				Yield							INTEGER								DEFAULT 0);
INSERT INTO	Belief_YieldPerBorderExpansion
			(BeliefType,						YieldType,		Yield)
VALUES		('BELIEF_RELIGIOUS_SETTLEMENTS',	'YIELD_GOLD',	20);

UPDATE	Belief_ResourceYieldChanges
SET		Yield = 2
WHERE	BeliefType = 'BELIEF_GOD_FESTIVALS' AND YieldType = 'YIELD_FAITH';
CREATE TABLE	Belief_BuildingClassYieldChangePerResource (
				BeliefType									TEXT	REFERENCES Beliefs(Type),
				BuildingClassType							TEXT	REFERENCES BuildingClasses(Type),
				ResourceType								TEXT	REFERENCES Resources(Type),
				YieldType									TEXT	REFERENCES Yields(Type),
				Yield										INTEGER										DEFAULT 0);
INSERT INTO	Belief_BuildingClassYieldChangePerResource
			(BeliefType,				BuildingClassType,			ResourceType,		YieldType,			Yield)
VALUES		('BELIEF_GOD_FESTIVALS',	'BUILDINGCLASS_MONASTERY',	'RESOURCE_WINE',	'YIELD_CULTURE',	1),
			('BELIEF_GOD_FESTIVALS',	'BUILDINGCLASS_MONASTERY',	'RESOURCE_INCENSE',	'YIELD_CULTURE',	1);

INSERT INTO Belief_ImprovementYieldChanges
			(BeliefType,				ImprovementType,			YieldType,		Yield)
VALUES		('BELIEF_ORAL_TRADITION',	'IMPROVEMENT_PLANTATION',	'YIELD_FAITH',		2),
			('BELIEF_ORAL_TRADITION',	'IMPROVEMENT_PLANTATION',	'YIELD_PRODUCTION',	1);

INSERT INTO	Belief_BuildingClassYieldChanges
			(BeliefType,				BuildingClassType,		YieldType,			Yield)
VALUES		('BELIEF_ANCESTOR_WORSHIP',	'BUILDINGCLASS_SHRINE',	'YIELD_FAITH',		2),
			('BELIEF_ANCESTOR_WORSHIP',	'BUILDINGCLASS_SHRINE',	'YIELD_SCIENCE',	1);

INSERT INTO	Belief_TerrainYieldChanges
			(BeliefType,				TerrainType,		YieldType,		Yield)
VALUES		('BELIEF_DESERT_FOLKLORE',	'TERRAIN_DESERT',	'YIELD_GOLD',	1);

INSERT INTO Belief_FeatureYieldChanges
			(BeliefType,				FeatureType,		YieldType,		Yield)
VALUES		('BELIEF_SACRED_PATH',		'FEATURE_JUNGLE',	'YIELD_FAITH',	1);

CREATE TABLE	Belief_CityStrengthYieldChange	(
				BeliefType						TEXT	REFERENCES Beliefs(Type),
				YieldType						TEXT	REFERENCES Yields(Type),
				Yield							INTEGER								DEFAULT 0);
INSERT INTO	Belief_CityStrengthYieldChange
			(BeliefType,				YieldType,		Yield)
VALUES		('BELIEF_GODDESS_STRATEGY',	'YIELD_FAITH',	20);

ALTER TABLE	Beliefs
ADD COLUMN	HealingFaithYield INTEGER DEFAULT = 0;	
UPDATE	Beliefs
SET		HealingFaithYield = 50
WHERE	Type = 'BELIEF_FAITH_HEALERS';

UPDATE	Beliefs
SET		WonderProductionModifier = 20
WHERE	Type = 'BELIEF_MONUMENT_GODS';
CREATE TABLE	Belief_YieldOnWonderConstruction	(
				BeliefType							TEXT	REFERENCES Beliefs(Type),
				YieldType							TEXT	REFERENCES Yields(Type),
				Yield								INTEGER								DEFAULT 0);
INSERT INTO	Belief_YieldOnWonderConstruction
			(BeliefType,				YieldType,		Yield)
VALUES		('BELIEF_MONUMENT_GODS',	'YIELD_FAITH',	100);

UPDATE	Belief_TerrainYieldChanges
SET		Yield = 2
WHERE	Type = 'BELIEF_DANCE_AURORA';
INSERT INTO	Belief_TerrainYieldChanges
			(BeliefType,			YieldType,			Yield)
VALUES		('BELIEF_DANCE_AURORA',	'YIELD_CULTURE',	1);

INSERT INTO	Belief_ResourceYieldChanges
			(BeliefType,				ResourceType,		YieldType,		Yield)
VALUES		('BELIEF_TEARS_OF_GODS',	'RESOURCE_PEARLS',	'YIELD_GOLD',	2),
			('BELIEF_TEARS_OF_GODS',	'RESOURCE_GEMS',	'YIELD_GOLD',	2);

UPDATE	Belief_ResourceYieldChanges
SET		Yield = 2
WHERE	Type = 'BELIEF_EARTH_MOTHER';
INSERT INTO	Belief_ResourceYieldChanges
			(BeliefType,			ResourceType,		YieldType,			Yield)
VALUES		('BELIEF_EARTH_MOTHER',	'RESOURCE_COPPER',	'YIELD_GOLD',		2),
			('BELIEF_EARTH_MOTHER',	'RESOURCE_IRON',	'YIELD_PRODUCTION',	1),
			('BELIEF_EARTH_MOTHER',	'RESOURCE_SALT',	'YIELD_FOOD',		1);

UPDATE	Belief_BuildingClassYieldChanges
SET		Yield = 2
WHERE	Type = 'BELIEF_GOD_KING' AND YieldType <> 'YIELD_FAITH';
UPDATE	Belief_BuildingClassYieldChanges
SET		Yield = 6
WHERE	Type = 'BELIEF_GOD_KING' AND YieldType = 'YIELD_FAITH';
INSERT INTO	Belief_BuildingClassYieldChanges
			(BeliefType,		BuildingClassType,		YieldType,		Yield)
VALUES		('BELIEF_GOD_KING',	'BUILDINGCLASS_PALACE',	'YIELD_FOOD',	2);
CREATE TABLE	Belief_BuildingClassMaxYieldModifiers	(
				BeliefType							TEXT	REFERENCES Beliefs(Type),
				BuildingClassType					TEXT	REFERENCES BuildingClasses(Type),
				YieldType							TEXT	REFERENCES Yields(Type),
				Max									INTEGER											DEFAULT 0);
INSERT INTO	Belief_BuildingClassMaxYieldModifiers
			(BeliefType,		BuildingClassType,		YieldType,			Max)
VALUES		('BELIEF_GOD_KING',	'BUILDINGCLASS_PALACE',	'YIELD_PRODUCTION', 255),
			('BELIEF_GOD_KING',	'BUILDINGCLASS_PALACE',	'YIELD_GOLD',		255),
			('BELIEF_GOD_KING',	'BUILDINGCLASS_PALACE',	'YIELD_SCIENCE',	255),
			('BELIEF_GOD_KING',	'BUILDINGCLASS_PALACE',	'YIELD_FAITH',		255),
			('BELIEF_GOD_KING',	'BUILDINGCLASS_PALACE',	'YIELD_CULTURE',	255);
CREATE TABLE	Belief_BuildingClassMaxGrowthModifiers (
				BeliefType								TEXT	REFERENCES Beliefs(Type),
				BuildingClassType						TEXT	REFERENCES BuildingClasses(Type),
				Max										INTEGER										DEFAULT 0);
INSERT INTO	Belief_BuildingClassMaxGrowthModifiers
			(BeliefType,		BuildingClassType,		Max)
VALUES		('BELIEF_GOD_KING',	'BUILDINGCLASS_PALACE',	255);
CREATE TABLE	Belief_BuildingClassMaxTourismModifiers (
				BeliefType								TEXT	REFERENCES Beliefs(Type),
				BuildingClassType						TEXT	REFERENCES BuildingClasses(Type),
				Max										INTEGER										DEFAULT 0);
INSERT INTO	Belief_BuildingClassMaxTourismModifiers
			(BeliefType,		BuildingClassType,		Max)
VALUES		('BELIEF_GOD_KING',	'BUILDINGCLASS_PALACE',	255);

INSERT INTO Belief_RessourceYieldChanges
			(BeliefType,		ResourceType,		YieldType,		Yield)
VALUES		('BELIEF_SUN_GOD',	'RESOURCE_BANANA',	'YIELD_FAITH',	2),
			('BELIEF_SUN_GOD',	'RESOURCE_CITRUS',	'YIELD_FAITH',	2),
			('BELIEF_SUN_GOD',	'RESOURCE_WHEAT',	'YIELD_FAITH',	2);

Now, when I try and test this code, it only applies the changes to Goddes of the Hunt, to Fertility Rites, and the production part of God of Craftsmen, but none of the other statements. Yet, when I tried a few of the others seperately, they did work - only including the changes to Desert Folklore made that one work, to give an example.

As far as I've read, SQL statements are independent from each other, so an error in one of the statements shouldn't affect the others, unless the erroneous one added a column. Even so, I was unable to find the cause to my problem, so what could be the reason for that behaviour and what could I do to fix it?

monju125 · Jul 2, 2015

You've hit one of the other ways a SQL file will fail to load all the way: Foreign (or really any primary) key constraint. Your 4th query has a typo; you have BeliefType as 'BELIEF_GOD_CRATFSMEN', but it should be 'BELIEF_GOD_CRAFTSMEN'. The BeliefType column of the Belief_CityYieldChanges table has a foreign key that references the Type column in the Beliefs table, but since it couldn't find the typo, it errors out and never runs the rest of your queries.

monju125 · Jul 2, 2015

By the way, I'd recommend testing your SQL changes in SQLiteManager using the instructions here: http://modiki.civfanatics.com/index.php/Debugging_%28Civ5%29#Using_the_snapshots. That's how I found your error.

Timmy563 · Jul 2, 2015

Ah, I see. That SQLiteManager is really helpful, it made searching for typos much easier and I've found a few so far. Thank you very much!

edit: In case anyone is reading this edit, is it possible to change river yields by adding an entry to FeatureYieldChanges with FEATURE_RIVER or do I have to wirte my own lua for that?

LeeS · Jul 2, 2015

Timmy563 said:
Ah, I see. That SQLiteManager is really helpful, it made searching for typos much easier and I've found a few so far. Thank you very much!

edit: In case anyone is reading this edit, is it possible to change river yields by adding an entry to FeatureYieldChanges with FEATURE_RIVER or do I have to wirte my own lua for that?

FEATURE_RIVER is not a 'real' feature. It is listed under the Fake Features table in the XML, and cannot be used in any Something_FeatureSomthing table provided by Firaxis.

Buildings have a specific table for changing plot yields on river tiles. Beliefs unfortunately do not have a specific table for tying a plot yield change between a specific belief and River plot yields

Timmy563 · Jul 2, 2015

Yeah, I just noticed myself... I guess I have to take the lua-route then.

SQL only partly working

Timmy563

Chieftain

monju125

Chieftain

monju125

Chieftain

Timmy563

Chieftain

LeeS

Imperator

Timmy563

Chieftain

Similar threads