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

[whoward69]

SQL tests (=, !=, >, etc) should be read as "has a value that ..." and not just as "that ...",

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

 

[qqqbbb]

Can I convert this lua code to sql?

local var = 0
While var < 10 do
    var = var + 1
end

 

[whoward69]

Can I convert this lua code to sql?

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.

 

[qqqbbb]

I want to add about 30 similar entries to a table.

 

[whoward69]

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,

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';

 

[qqqbbb]

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:

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_%'?

 

[whoward69]

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

 

[Elucidus]

Thanks guys, between this and the wiki you linked was a great help.

 

[lordplane]

Can someone explain to me the difference between

INSERT INTO Table_1 (Column_1, Column_2)
VALUES              ('Value_1', 'Value_2'),
                    ('Value_3', 'Value_4'),
                    ('Value_5', 'Value_6');

And

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'  ;

 

[meRlinX_AT]

Can someone explain to me the difference between

INSERT INTO Table_1 (Column_1, Column_2)
VALUES              ('Value_1', 'Value_2'),
                    ('Value_3', 'Value_4'),
                    ('Value_5', 'Value_6');

And

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'  ;

the first one should be faster ... especially when you insert many data

 

[meRlinX_AT]

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)

 

[lordplane]

the first one should be faster ... especially when you insert many data

But otherwise no difference?

 

[meRlinX_AT]

But otherwise no difference?

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.

 

[Imperator Knoedel]

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:

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?

 

[Troller0001]

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:

UPDATE Units
SET Range = Range+1
WHERE Range>0;

or alternatively the following if you want to exclude planes and nukes:

UPDATE Units
SET Range = Range+1
WHERE Range>0 AND Domain != 'DOMAIN_AIR';

-------------

EDIT: I realized I misread the statement. Oops

 

[Imperator Knoedel]

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:

UPDATE Units
SET Range = Range+1
WHERE Range>0;

or alternatively the following if you want to exclude planes and nukes:

UPDATE Units
SET Range = Range+1
WHERE Range>0 AND Domain != 'DOMAIN_AIR';

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.

 

[Chrisy15]

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:

UPDATE Units SET Range = '1' WHERE Range = '2';
UPDATE Units SET Range = '2' WHERE Range = '3';

Kinda a necro I guess but the inefficiency irritates me...

UPDATE Units
SET Range = Range - 1
WHERE Domain != 'DOMAIN_AIR' AND Range > 1;

And then ofc:

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

[EDIT2] Troller plz I have absolutely no idea what you're talking about what is this Fake News you're fabricating...

 

[Imperator Knoedel]

Uhm, you really think your code is more efficient than mine?

 

[Chrisy15]

Uhm, you really think your code is more efficient than mine?

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.

 

[Troller0001]

Kinda a necro I guess but the inefficiency irritates me...

UPDATE Units
SET Range = Range - 1
WHERE Domain != 'DOMAIN_AIR';

[..]

What happens with units that originally had a range of 0? I.e. any melee (civilian) unit such as the Warrior, Spearman, Settler, etc.