Resource icon

[CODE SNIPPET] Modifier/Requirement Auto-Generator 3

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:
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;
Author
thecrazyscot
Downloads
544
Views
544
First release
Last update
Rating
0.00 star(s) 0 ratings

More resources from thecrazyscot

Latest updates

  1. v3 Update

    Added support for up to 5 modifier arguments per modifierid
  2. v2 Update

    -A few updates which fix some code issues and extend functionality (changes detailed in...
Top Bottom