I got tired of having to manually update multiple tables whenever I created a modifier, and it can get incredibly tedious to edit modifiers once you have a fair number of them as the code can get incredibly long.
I created this Modifier Generator in SQL so that I can input all the modifier fields from both Modifiers and ModifierArguments into a single line and both tables will be automatically updated. Modifier attachments will also automatically generate for (almost) all the tables, the exceptions being
GreatPersonIndividualActionModifiers and GreatPersonIndividualBirthModifiers.
I did the same with all the requirement tables (Requirements, RequirementArguments, RequirementSets, RequirementSetRequirements, etc), so that a single line will update all relevant tables.
This is most useful when you are creating many modifiers at once...it will have more limited usefulness when creating just a handful, although you certainly could still use it.
I literally have been teaching myself SQL for the past week, so if anyone who actually knows SQL would like to take a look at it to improve/optimize it I would be happy if you did so, as long as you tell me what I did wrong or inefficiently
I don't know if this will be useful to anyone else, but I thought I'd share it.
Here's the code:
I created this Modifier Generator in SQL so that I can input all the modifier fields from both Modifiers and ModifierArguments into a single line and both tables will be automatically updated. Modifier attachments will also automatically generate for (almost) all the tables, the exceptions being
GreatPersonIndividualActionModifiers and GreatPersonIndividualBirthModifiers.
I did the same with all the requirement tables (Requirements, RequirementArguments, RequirementSets, RequirementSetRequirements, etc), so that a single line will update all relevant tables.
This is most useful when you are creating many modifiers at once...it will have more limited usefulness when creating just a handful, although you certainly could still use it.
I literally have been teaching myself SQL for the past week, so if anyone who actually knows SQL would like to take a look at it to improve/optimize it I would be happy if you did so, as long as you tell me what I did wrong or inefficiently
I don't know if this will be useful to anyone else, but I thought I'd share it.
Here's the code:
Code:
/*
SQL Modifier Generator for Civilization VI
Created by thecrazyscotsman (7 Dec 2016)
This framework allows you to input all the relevant parts of a modifier and/or a requirement into a single line. The rest is automatically generated for you.
EXAMPLE:
INSERT INTO ModTempTable (ModifierId, RunOnce, Permanent, OwnerRequirementSetId, SubjectRequirementSetId, ModifierType, Name, Value, Extra, Name2, Value2, Extra2, Name3, Value3, Extra3, AttachedTo)
VALUES
("MODIFIER_ADD_RICEFOOD", 0, 0, NULL, "REQUIREMENT_PLOT_HAS_RICE", "MODIFIER_CITY_PLOT_YIELDS_ADJUST_PLOT_YIELD", "YieldType", "YIELD_FOOD", NULL, "Amount", 1, NULL, NULL, NULL, NULL, "BUILDING_GRANARY");
INSERT INTO ReqTempTable (RequirementSetId, RequirementSetType, RequirementId, RequirementType, Inverse, Name, Value, Extra, Name2, Value2, Extra2)
VALUES
("REQUIREMENT_PLOT_HAS_RICE", "REQUIREMENTSET_TEST_ALL", "PLOT_HAS_RICE", "REQUIREMENT_PLOT_RESOURCE_TYPE_MATCHES", 0, "ResourceType", "RESOURCE_RICE", NULL, NULL, NULL, NULL);
These inputs will generate and insert the correct rows on the Modifiers, ModifierArguments, Requirements, RequirementArguments, RequirementSetRequirements, and RequirementSets tables. It will also automatically attach the modifier to the Granary.
Modifier attachments are automatically created (using the AttachedTo field), with the exception of these two:
GreatPersonIndividualActionModifiers
GreatPersonIndividualBirthModifiers
In order to correctly generate the modifier attachment, the correct prefix must be used (i.e. BUILDING). Custom mod strings must go after the prefix (i.e. BUILDING_TCS_SALOON).
*/
/* CREATE FRAMEWORK -- DO NOT CHANGE */
--Temporary tables dropped at end
CREATE TABLE ModTempTable
(
ModifierId TEXT NOT NULL,
RunOnce BOOLEAN NOT NULL DEFAULT 0,
Permanent BOOLEAN NOT NULL DEFAULT 0,
OwnerRequirementSetId TEXT,
SubjectRequirementSetId TEXT,
ModifierType TEXT NOT NULL,
Name TEXT,
Value TEXT,
Extra TEXT,
Name2 TEXT,
Value2 TEXT,
Extra2 TEXT,
Name3 TEXT,
Value3 TEXT,
Extra3 TEXT,
Name4 TEXT,
Value4 TEXT,
Extra4 TEXT,
Name5 TEXT,
Value5 TEXT,
Extra5 TEXT,
AttachedTo TEXT
);
CREATE TABLE ReqTempTable
(
RequirementSetId TEXT,
RequirementSetType TEXT,
RequirementId TEXT,
RequirementType TEXT,
Inverse BOOLEAN NOT NULL DEFAULT 0,
Name TEXT,
Value TEXT,
Extra TEXT,
Name2 TEXT,
Value2 TEXT,
Extra2 TEXT
);
/* END CREATE FRAMEWORK */
/* BEGIN MOD INPUTS */
--INPUT MODIFIERS
INSERT INTO ModTempTable (ModifierId, RunOnce, Permanent, OwnerRequirementSetId, SubjectRequirementSetId, ModifierType, Name, Value, Extra, Name2, Value2, Extra2, Name3, Value3, Extra3, Name4, Value4, Extra4, Name5, Value5, Extra5, AttachedTo)
VALUES
--("ModifierId", 0, 0, "OwnerRequirementSetId", "SubjectRequirementSetId", "ModifierType", "Name", "Value", "Extra", "Name2", "Value2", "Extra2", "Name3", "Value3", "Extra3", "Name4", "Value4", "Extra4", "Name5", "Value5", "Extra5", "AttachedTo");
--INPUT REQUIREMENTS
INSERT INTO ReqTempTable (RequirementSetId, RequirementSetType, RequirementId, RequirementType, Inverse, Name, Value, Extra, Name2, Value2, Extra2)
VALUES
--("RequirementSetId", "RequirementSetType", "RequirementId", "RequirementType", 0, "Name", "Value", "Extra", "Name2", "Value2", "Extra2");
/* END MOD INPUTS */
/* DO NOT CHANGE ANYTHING BELOW HERE */
--MODIFIER INSERTS
--Populate Modifiers table
INSERT INTO Modifiers (ModifierId, ModifierType, OwnerRequirementSetId, SubjectRequirementSetId, RunOnce, Permanent)
SELECT ModTempTable.ModifierId, ModTempTable.ModifierType, ModTempTable.OwnerRequirementSetId, ModTempTable.SubjectRequirementSetId, ModTempTable.RunOnce, ModTempTable.Permanent FROM ModTempTable;
--Populate ModifierArguments table first set
INSERT INTO ModifierArguments (ModifierId, Name, Value, Extra)
SELECT ModTempTable.ModifierId, ModTempTable.Name, ModTempTable.Value, ModTempTable.Extra FROM ModTempTable;
--Populate ModifierArguments table second set
INSERT INTO ModifierArguments (ModifierId, Name, Value, Extra)
SELECT ModTempTable.ModifierId, ModTempTable.Name2, ModTempTable.Value2, ModTempTable.Extra2 FROM ModTempTable
WHERE ModTempTable.Name2 IS NOT NULL;
--Populate ModifierArguments table third set
INSERT INTO ModifierArguments (ModifierId, Name, Value, Extra)
SELECT ModTempTable.ModifierId, ModTempTable.Name3, ModTempTable.Value3, ModTempTable.Extra3 FROM ModTempTable
WHERE ModTempTable.Name3 IS NOT NULL;
--Populate ModifierArguments table fourth set
INSERT INTO ModifierArguments (ModifierId, Name, Value, Extra)
SELECT ModTempTable.ModifierId, ModTempTable.Name4, ModTempTable.Value4, ModTempTable.Extra4 FROM ModTempTable
WHERE ModTempTable.Name4 IS NOT NULL;
--Populate ModifierArguments table fifth set
INSERT INTO ModifierArguments (ModifierId, Name, Value, Extra)
SELECT ModTempTable.ModifierId, ModTempTable.Name5, ModTempTable.Value5, ModTempTable.Extra5 FROM ModTempTable
WHERE ModTempTable.Name5 IS NOT NULL;
--REQUIREMENT INSERTS
--Populate Requirements table
INSERT INTO Requirements (RequirementId, RequirementType, Inverse)
SELECT ReqTempTable.RequirementId, ReqTempTable.RequirementType, ReqTempTable.Inverse FROM ReqTempTable;
--Populate RequirementArguments table first set
INSERT INTO RequirementArguments (RequirementId, Name, Value, Extra)
SELECT ReqTempTable.RequirementId, ReqTempTable.Name, ReqTempTable.Value, ReqTempTable.Extra FROM ReqTempTable
WHERE ReqTempTable.Name IS NOT NULL;
--Populate RequirementArguments table second set
INSERT INTO RequirementArguments (RequirementId, Name, Value, Extra)
SELECT ReqTempTable.RequirementId, ReqTempTable.Name2, ReqTempTable.Value2, ReqTempTable.Extra2 FROM ReqTempTable
WHERE ReqTempTable.Name2 IS NOT NULL;
--Populate RequirementSetRequirements table
INSERT INTO RequirementSetRequirements (RequirementSetId, RequirementId)
SELECT ReqTempTable.RequirementSetId, ReqTempTable.RequirementId FROM ReqTempTable;
--Populate RequirementSets table with only unique values
INSERT INTO RequirementSets (RequirementSetId, RequirementSetType)
SELECT DISTINCT ReqTempTable.RequirementSetId, ReqTempTable.RequirementSetType FROM ReqTempTable;
--ATTACH MODIFIERS
--Attach Belief modifiers
INSERT INTO BeliefModifiers (BeliefType, ModifierID)
SELECT ModTempTable.AttachedTo, ModTempTable.ModifierId FROM ModTempTable
WHERE ModTempTable.AttachedTo LIKE 'BELIEF%';
--Attach Building modifiers
INSERT INTO BuildingModifiers (BuildingType, ModifierId)
SELECT ModTempTable.AttachedTo, ModTempTable.ModifierId FROM ModTempTable
WHERE ModTempTable.AttachedTo LIKE 'BUILDING%';
--Attach Civic modifiers
INSERT INTO CivicModifiers (CivicType, ModifierId)
SELECT ModTempTable.AttachedTo, ModTempTable.ModifierId FROM ModTempTable
WHERE ModTempTable.AttachedTo LIKE 'CIVIC%';
--Attach District modifiers
INSERT INTO DistrictModifiers (DistrictType, ModifierId)
SELECT ModTempTable.AttachedTo, ModTempTable.ModifierId FROM ModTempTable
WHERE ModTempTable.AttachedTo LIKE 'DISTRICT%';
--Attach Government modifiers
INSERT INTO GovernmentModifiers (GovernmentType, ModifierId)
SELECT ModTempTable.AttachedTo, ModTempTable.ModifierId FROM ModTempTable
WHERE ModTempTable.AttachedTo LIKE 'GOVERNMENT%';
--Attach Improvement modifiers
INSERT INTO ImprovementModifiers (ImprovementType, ModifierId)
SELECT ModTempTable.AttachedTo, ModTempTable.ModifierId FROM ModTempTable
WHERE ModTempTable.AttachedTo LIKE 'IMPROVEMENT%';
--Attach Policy modifiers
INSERT INTO PolicyModifiers (PolicyType, ModifierId)
SELECT ModTempTable.AttachedTo, ModTempTable.ModifierId FROM ModTempTable
WHERE ModTempTable.AttachedTo LIKE 'POLICY%';
--Attach Project completion modifiers
INSERT INTO ProjectCompletionModifiers (ProjectType, ModifierId)
SELECT ModTempTable.AttachedTo, ModTempTable.ModifierId FROM ModTempTable
WHERE ModTempTable.AttachedTo LIKE 'PROJECT%';
--Attach Technology modifiers
INSERT INTO TechnologyModifiers (TechnologyType, ModifierId)
SELECT ModTempTable.AttachedTo, ModTempTable.ModifierId FROM ModTempTable
WHERE ModTempTable.AttachedTo LIKE 'TECH%';
--Attach Unit Ability modifiers
INSERT INTO UnitAbilityModifiers (UnitAbilityType, ModifierId)
SELECT ModTempTable.AttachedTo, ModTempTable.ModifierId FROM ModTempTable
WHERE ModTempTable.AttachedTo LIKE 'ABILITY%';
--Attach Unit Promotion modifiers
INSERT INTO UnitPromotionModifiers (UnitPromotionType, ModifierId)
SELECT ModTempTable.AttachedTo, ModTempTable.ModifierId FROM ModTempTable
WHERE ModTempTable.AttachedTo LIKE 'PROMOTION%';
--DROP TABLES
DROP TABLE ModTempTable;
DROP TABLE ReqTempTable;
