XML-SQL Cheat Sheet (Civ5): Difference between revisions

From Civilization Modding Wiki
Jump to navigationJump to search
No edit summary
No edit summary
Line 1: Line 1:
== Things to remember ==
= Things to remember =
* '''Booleans:''' SQLite does not have booleans, it uses integers. So do not use <code>true</code> or <code>false</code>, use <code>1</code> and <code>0</code>.
* '''Booleans:''' SQLite does not have booleans, it uses integers. So do not use <code>true</code> or <code>false</code>, use <code>1</code> and <code>0</code>.




== Inserting rows ==
= Inserting rows =
=== XML ===
== XML ==
<syntaxhighlight lang="xml" style="left:40px;">
<syntaxhighlight lang="xml" style="left:40px;">
<GameData>
<GameData>
Line 20: Line 20:
</syntaxhighlight>
</syntaxhighlight>


=== SQL ===
== SQL ==
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
/* Inserts a new color */
/* Inserts a new color */
Line 28: Line 28:




== Deleting rows ==
= Deleting rows =
=== XML ===
== XML ==
<syntaxhighlight lang="xml">
<syntaxhighlight lang="xml">
<GameData>
<GameData>
Line 41: Line 41:
</syntaxhighlight>
</syntaxhighlight>


=== SQL ===
== SQL ==
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
/* Deletes all colors named TYPE_CUSTOM whose red channel is equal to 1 */
/* Deletes all colors named TYPE_CUSTOM whose red channel is equal to 1 */
Line 49: Line 49:




== Updating rows ==
= Updating rows =
=== XML ===
== XML ==
<syntaxhighlight lang="xml">
<syntaxhighlight lang="xml">
<GameData>
<GameData>
Line 78: Line 78:
</syntaxhighlight>
</syntaxhighlight>


=== SQL ===
== SQL ==
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
/* Update all colors whose names are COLOR_CUSTOM and red channel is 0 */
/* Update all colors whose names are COLOR_CUSTOM and red channel is 0 */
Line 85: Line 85:
WHERE Type = 'COLOR_CUSTOM' AND Red = 0;
WHERE Type = 'COLOR_CUSTOM' AND Red = 0;


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




== New tables ==
= New tables =
=== XML ===
== XML ==
<syntaxhighlight lang="xml">
<syntaxhighlight lang="xml">
<GameData>
<GameData>
Line 102: Line 102:
</syntaxhighlight>
</syntaxhighlight>


=== SQL ===
== SQL ==
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
/* Create one new table with two columns */
/* Create one new table with two columns */
Line 109: Line 109:




== New columns ==
= New columns =
=== SQL ===
== SQL ==
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
/* Adds one Description column to Colors. */
/* Adds one Description column to Colors. */
ALTER TABLE Colors COLUMN 'Description' TEXT DEFAULT NULL;
ALTER TABLE Colors COLUMN 'Description' TEXT DEFAULT NULL;
</syntaxhighlight>
</syntaxhighlight>

Revision as of 20:15, 2 September 2012

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;