1. We have added a Gift Upgrades feature that allows you to gift an account upgrade to another member, just in time for the holiday season. You can see the gift option when going to the Account Upgrades screen, or on any user profile screen.
    Dismiss Notice

[CODE SNIPPET] Modifier/Requirement Auto-Generator 3

Code to auto-populate modifier/requirement and all related tables

  1. thecrazyscot
    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;
    
    

Recent Updates

  1. v3 Update
  2. v2 Update