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

SQL modding for dummies! (specially those that still use XML)

Discussion in 'Civ5 - Modding Tutorials & Reference' started by Leugi, Nov 20, 2014.

  1. whoward69

    whoward69 DLL Minion

    Joined:
    May 30, 2011
    Messages:
    8,441
    Location:
    Near Portsmouth, UK
    SQL tests (=, !=, >, etc) should be read as "has a value that ..." and not just as "that ...",

    Code:
    WHERE Special != 'SPECIALUNIT_PEOPLE'
    reads as "where Special has a value that is not equal to SPECIALUNIT_PEOPLE"

    NULL is not a value, which then explains why the first version doesn't do what you would expect.

    Aside: Some versions of SQL (but NOT SQLite) I've worked with permit you to write the second one as ... WHERE NOT(Special = 'SPECIALUNIT_PEOPLE'), ie, "NOT ..." reads as "everything in the table that is not ...", which fits the "IS NOT NULL" pattern
     
  2. qqqbbb

    qqqbbb Prince

    Joined:
    Sep 25, 2010
    Messages:
    526
    Can I convert this lua code to sql?
    Code:
    local var = 0
    While var < 10 do
        var = var + 1
    end
     
  3. whoward69

    whoward69 DLL Minion

    Joined:
    May 30, 2011
    Messages:
    8,441
    Location:
    Near Portsmouth, UK
    Typically no. (Most implementations of SQL use stored procedures to do something similar, but these are unavailable in SQLite.)

    However, you may be able to work around the problem (10 ints is not too many to list out in an IN condition for example), but I'd need to understand what you're trying to achieve.
     
  4. qqqbbb

    qqqbbb Prince

    Joined:
    Sep 25, 2010
    Messages:
    526
    I want to add about 30 similar entries to a table.
     
  5. whoward69

    whoward69 DLL Minion

    Joined:
    May 30, 2011
    Messages:
    8,441
    Location:
    Near Portsmouth, UK
    Assuming there are lots of columns with the same values and only a few with different values, insert just the differences, letting the other columns default, and then bulk update all the newly inserted rows in one go. You may need to use one of the columns as a flag,

    Code:
    INSERT INTO Units(Type, Cost, Combat, Class) VALUES('X', 100, 30, 'MARKER');
    INSERT INTO Units(Type, Cost, Combat, Class) VALUES('X', 120, 40, 'MARKER');
    INSERT INTO Units(Type, Cost, Combat, Class) VALUES('X', 150, 50, 'MARKER');
    INSERT INTO Units(Type, Cost, Combat, Class) VALUES('X', 180, 60, 'MARKER');
    INSERT INTO Units(Type, Cost, Combat, Class) VALUES('X', 220, 70, 'MARKER');
    
    UPDATE Units SET X=1, Y=2, Z=3, L='this', M='that', Class='RealClass' WHERE Class='MARKER';
    
     
  6. qqqbbb

    qqqbbb Prince

    Joined:
    Sep 25, 2010
    Messages:
    526
    I added several custom worker units to the game. Now I'm trying to write code that will allow them to build everything vanilla worker can build. This code:
    Code:
    INSERT INTO Unit_Builds (UnitType,BuildType)
    SELECT (SELECT Type FROM Units WHERE Type LIKE 'CUSTOM_WORKER_%'), BuildType	
    FROM Unit_Builds WHERE UnitType IS 'UNIT_WORKER';
    inserts entries only for the first custom unit that matches 'CUSTOM_WORKER_%'. How do I modify the code so that it loops through Units table and inserts new Unit_Builds entries for every unit that matches 'CUSTOM_WORKER_%'?
     
  7. whoward69

    whoward69 DLL Minion

    Joined:
    May 30, 2011
    Messages:
    8,441
    Location:
    Near Portsmouth, UK
    Code:
    INSERT INTO Unit_Builds(UnitType, BuildType)
      SELECT u.type, b.BuildType FROM Units u, Unit_Builds b
        WHERE u.CombatClass='UNITCOMBAT_MELEE' AND b.UnitType='UNIT_WORKER';
    
    You'll need to modify the
    u.CombatClass='UNITCOMBAT_MELEE'
    bit to meet your specific unit matching requirements
     
  8. Elucidus

    Elucidus King

    Joined:
    Mar 3, 2002
    Messages:
    983
    Location:
    USA
    Thanks guys, between this and the wiki you linked was a great help.
     
  9. lordplane

    lordplane Chieftain

    Joined:
    Jul 8, 2016
    Messages:
    22
    Can someone explain to me the difference between
    Code:
    INSERT INTO Table_1 (Column_1, Column_2)
    VALUES              ('Value_1', 'Value_2'),
                        ('Value_3', 'Value_4'),
                        ('Value_5', 'Value_6');
    
    And
    Code:
    INSERT INTO Table_1 (Column_1 ,Column_2)
    SELECT              'Value_1' ,'Value_2'  UNION ALL
    SELECT              'Value_3' ,'Value_4'  UNION ALL
    SELECT              'Value_5' ,'Value_6'  ;
    
     
  10. meRlinX_AT

    meRlinX_AT JustMe

    Joined:
    Jan 19, 2015
    Messages:
    161
    Location:
    Earth
    the first one should be faster ... especially when you insert many data
     
  11. meRlinX_AT

    meRlinX_AT JustMe

    Joined:
    Jan 19, 2015
    Messages:
    161
    Location:
    Earth
    PLEASE ... i beg you
    Never use something like this

    UPDATE xxx SET xxx = 'true' WHERE Type = xxx;
    (same with INSERT INTO)

    on a db field which is an boolean
    boolean are always 0 (=false) or 1 (=true)
     
  12. lordplane

    lordplane Chieftain

    Joined:
    Jul 8, 2016
    Messages:
    22
    But otherwise no difference?
     
  13. meRlinX_AT

    meRlinX_AT JustMe

    Joined:
    Jan 19, 2015
    Messages:
    161
    Location:
    Earth
    not in your example. you can see more possibility's here

    i would use VALUES for a simple input of data and SELECT when i need also data from another/same table or a combination of both.

    depending on the usecase, i would also use a UNION or an UNION ALL.
     
  14. Imp. Knoedel

    Imp. Knoedel Properly Paranoid Proletarian

    Joined:
    Nov 11, 2011
    Messages:
    8,733
    Location:
    The cooler Germany
    I'm not sure if this is the right thread, but here goes:
    I have already had some success playing around with SQL, getting stuff done with a single line of code that might otherwise have taken me hours painstakingly searching all relevant XML files, such as reducing the range of all ranged units like so:

    Code:
    UPDATE Units SET Range = '1' WHERE Range = '2';
    UPDATE Units SET Range = '2' WHERE Range = '3';
    (I know that's actually two lines of code, bear with me)
    Now I'm wondering if there is a similar way to grant the Penalty vs Naval (50) "Promotion" to all non-naval ranged units, or if I have to bite the bullet and manually search for every single unit this would affect and make an entry in Unit_FreePromotions for each and every one of them?
     
  15. Troller0001

    Troller0001 Not an actual Troll

    Joined:
    Mar 9, 2016
    Messages:
    739
    Gender:
    Male
    Location:
    The Netherlands
    Code:
    INSERT INTO Unit_FreePromotions
    (UnitType, PromotionType)
    SELECT Type, 'PROMOTION_PENALTY_VS_NAVAL_OR_WHATEVER_THIS_PROMOTION_IS_CALLED'
    FROM Units
    WHERE Domain != 'DOMAIN_SEA' AND Range>0 AND RangedCombat > 0;
    
    --Includes planes (E.g. Bomber) as well, but doesn't include Nuclear Missiles
    --To exclude planes, just change Domain != 'DOMAIN_SEA' to Domain == 'DOMAIN_LAND'
    
    --------------

    If you want to increase the range of all ranged units by 1 (which I assume you're wanting to do judging by the two-line snippet you posted), you can also do this:
    Code:
    UPDATE Units
    SET Range = Range+1
    WHERE Range>0;
    
    or alternatively the following if you want to exclude planes and nukes:
    Code:
    UPDATE Units
    SET Range = Range+1
    WHERE Range>0 AND Domain != 'DOMAIN_AIR';
    
    -------------

    EDIT: I realized I misread the statement. Oops :crazyeye:
     
    Last edited: Sep 20, 2017
    Imp. Knoedel likes this.
  16. Imp. Knoedel

    Imp. Knoedel Properly Paranoid Proletarian

    Joined:
    Nov 11, 2011
    Messages:
    8,733
    Location:
    The cooler Germany
    Uhm, thank you, but actually I wanted to and successfully did reduce the range of all non-missile and non-air ranged units, so that Archers only have 1 range and Artillery only 2 etc.
     
    Troller0001 likes this.
  17. Chrisy15

    Chrisy15 Flower, Beautiful

    Joined:
    Jul 9, 2015
    Messages:
    2,077
    Kinda a necro I guess but the inefficiency irritates me...

    Code:
    UPDATE Units
    SET Range = Range - 1
    WHERE Domain != 'DOMAIN_AIR' AND Range > 1;
    
    And then ofc:

    Code:
    CREATE TRIGGER IF NOT EXISTS C15_RangeChange_WooThoroughness
    AFTER INSERT ON Units
    WHEN NEW.Domain != 'DOMAIN_AIR' AND NEW.Range > 1
    BEGIN
    UPDATE Units
    SET Range = Range - 1
    WHERE Type = NEW.Type;
    END;
    
    [EDIT] Yup totally checked the code before sending it dw it worked the whole time honest :p

    [EDIT2] Troller plz I have absolutely no idea what you're talking about what is this Fake News you're fabricating...
     
    Last edited: Oct 4, 2017
    Imp. Knoedel likes this.
  18. Imp. Knoedel

    Imp. Knoedel Properly Paranoid Proletarian

    Joined:
    Nov 11, 2011
    Messages:
    8,733
    Location:
    The cooler Germany
    Uhm, you really think your code is more efficient than mine?
     
  19. Chrisy15

    Chrisy15 Flower, Beautiful

    Joined:
    Jul 9, 2015
    Messages:
    2,077
    You've got two statements; I've got one. The trigger is extra functionality which you would otherwise be missing out on, so it doesn't really count into the efficiency equation.

    Plus I'm surprised that the string numbers work tbh, but evidently they did so huh.
     
    Imp. Knoedel likes this.
  20. Troller0001

    Troller0001 Not an actual Troll

    Joined:
    Mar 9, 2016
    Messages:
    739
    Gender:
    Male
    Location:
    The Netherlands
    What happens with units that originally had a range of 0? I.e. any melee (civilian) unit such as the Warrior, Spearman, Settler, etc.
     
    Imp. Knoedel likes this.

Share This Page