XML-SQL Cheat Sheet (Civ5)

Things to remember

• Booleans: SQLite does not have booleans, it uses integers. So do not use true or false, use 1 and 0.

Inserting rows

XML

<GameData>
	<Colors>
            <!-- Inserts a new color -->
            <Row>
                <Type>COLOR_CUSTOM</Type>
                <Red>0.1</Red>
                <Green>0.1</Green>
                <Blue>0.1</Blue>
                <Alpha>0.45</Alpha>
            </Row>	
	</Colors>
<GameData>

SQL

/* Inserts a new color */
INSERT INTO Colors (Type, Red, Green, Blue, Alpha)
VALUES ('COLOR_CUSTOM', 0.1, 0.1, 0.1, 0.45);

Deleting rows

XML

<GameData>
	<Colors>
            <!-- Deletes all colors named COLOR_CUSTOM (there is only one actually) -->
            <Delete Type="COLOR_CUSTOM" />
            <!-- You can specify as many conditions as you want -->
            <Delete R="1" G="1"/>
	</Colors>
<GameData>

SQL

/* Deletes all colors named TYPE_CUSTOM whose red channel is equal to 1 */
DELETE FROM Colors
WHERE Type = 'COLOR_CUSTOM' AND Red = 1;

Updating rows

XML

<GameData>
	<Colors>
            <!-- Changes the red channel for all colors named COLOR_CUSTOM (there is only one actually)
            <Update>
                <Where Type="COLOR_CUSTOM"/>
                <Set Red="1"/>
            </Update>
            <!-- You can specify as many conditions as you want and set as many properties as you want -->
            <Update>
                <Where Type="COLOR_CUSTOM" Red="0"/>
                <Set Red="1" Green="0"/>
            </Update>
            <!-- As always, formatting rules are relaxed, use what's the most convenient for you -->
            <Update>
                <Where>
                    <Type>COLOR_CUSTOM</Type>
                </Where>
                <Set>
                    <Red>1</Red>
                    <Green>1</Green>
                </Set>
            </Update>
        </Colors>
</GameData>

SQL

/* Update all colors whose names are COLOR_CUSTOM and red channel is 0 */
UPDATE Colors
SET Red = 1, Green = 0
WHERE Type = 'COLOR_CUSTOM' AND Red = 0;

/* Makes everything brighter */
UPDATE Colors
SET Red = Red + 0.1, Green = Green + 0.1, Blue=Blue + 0.1;

New tables

XML

<GameData>
    <Table Name="MyColors">
	<Column name="ID" type="integer" primarykey="true" autoincrement="true"/>
	<Column name="Type" type="text" notnull="true" unique="true"/>
    </Table>
</GameData>

SQL

/* Create one new table with two columns */
CREATE TABLE MyColors( id INTEGER PRIMARY KEY AUTOINCREMENT, Type TEXT UNIQUE );

New columns

SQL

/* Adds one Description column to Colors. */
ALTER TABLE Colors COLUMN 'Description' TEXT DEFAULT NULL;