XML-SQL Cheat Sheet (Civ5) - Revision history

DonQuich at 12:09, 25 September 2012

2012-09-25T12:09:08Z

← Older revision		Revision as of 12:09, 25 September 2012
Line 1:		Line 1:
	''This page is a part of the [[Civ5 XML Reference]].''		''This page is a part of the [[Civ5 XML Reference]].''

	== 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 ===
	<div class="civ5-example"><syntaxhighlight lang="xml" style="left:40px;">		<div class="civ5-example"><syntaxhighlight lang="xml" style="left:40px;">
Line 30:		Line 30:


	== Deleting rows ==		= Deleting rows =
	{{Warning}} See [http://forums.civfanatics.com/showthread.php?t=463429 Deleting data and issues, and how to avoid them] if you plan to delete rows and are not aware of the troubles that may arise.		{{Warning}} See [http://forums.civfanatics.com/showthread.php?t=463429 Deleting data and issues, and how to avoid them] if you plan to delete rows and are not aware of the troubles that may arise.
	=== XML ===		=== XML ===
Line 52:		Line 52:


	== Updating rows ==		= Updating rows =
	=== XML ===		=== XML ===
	<div class="civ5-example"><syntaxhighlight lang="xml">		<div class="civ5-example"><syntaxhighlight lang="xml">
Line 94:		Line 94:


	== New tables ==		= New tables =
	=== XML ===		=== XML ===
	<div class="civ5-example"><syntaxhighlight lang="xml">		<div class="civ5-example"><syntaxhighlight lang="xml">
Line 112:		Line 112:


	== New columns ==		= New columns =
	=== SQL ===		=== SQL ===
	<div class="civ5-example"><syntaxhighlight lang="sql">		<div class="civ5-example"><syntaxhighlight lang="sql">

DonQuich at 08:44, 25 September 2012

2012-09-25T08:44:53Z

← Older revision		Revision as of 08:44, 25 September 2012
Line 31:		Line 31:

	== Deleting rows ==		== Deleting rows ==
			{{Warning}} See [http://forums.civfanatics.com/showthread.php?t=463429 Deleting data and issues, and how to avoid them] if you plan to delete rows and are not aware of the troubles that may arise.
	=== XML ===		=== XML ===
	<div class="civ5-example"><syntaxhighlight lang="xml">		<div class="civ5-example"><syntaxhighlight lang="xml">

DonQuich at 11:06, 24 September 2012

2012-09-24T11:06:56Z

← Older revision		Revision as of 11:06, 24 September 2012
Line 120:		Line 120:

	[[Category:Civ5 XML Files]]		[[Category:Civ5 XML Files]]
			[[Category:Civ5 Tutorials]]

DonQuich at 11:00, 24 September 2012

2012-09-24T11:00:42Z

← Older revision		Revision as of 11:00, 24 September 2012
Line 1:		Line 1:
			''This page is a part of the [[Civ5 XML Reference]].''

	== 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>.
Line 115:		Line 117:
	ALTER TABLE Colors COLUMN 'Description' TEXT DEFAULT NULL;		ALTER TABLE Colors COLUMN 'Description' TEXT DEFAULT NULL;
	</syntaxhighlight></div>		</syntaxhighlight></div>


			[[Category:Civ5 XML Files]]

DonQuich at 10:56, 24 September 2012

2012-09-24T10:56:24Z

← Older revision		Revision as of 10:56, 24 September 2012
Line 5:		Line 5:
	== Inserting rows ==		== Inserting rows ==
	=== XML ===		=== XML ===
	<syntaxhighlight lang="xml" style="left:40px;">		<div class="civ5-example"><syntaxhighlight lang="xml" style="left:40px;">
	<GameData>		<GameData>
	<Colors>		<Colors>
Line 18:		Line 18:
	</Colors>		</Colors>
	<GameData>		<GameData>
	</syntaxhighlight>		</syntaxhighlight></div>

	=== SQL ===		=== SQL ===
	<syntaxhighlight lang="sql">		<div class="civ5-example"><syntaxhighlight lang="sql">
	/* Inserts a new color */		/* Inserts a new color */
	INSERT INTO Colors (Type, Red, Green, Blue, Alpha)		INSERT INTO Colors (Type, Red, Green, Blue, Alpha)
	VALUES ('COLOR_CUSTOM', 0.1, 0.1, 0.1, 0.45);		VALUES ('COLOR_CUSTOM', 0.1, 0.1, 0.1, 0.45);
	</syntaxhighlight>		</syntaxhighlight></div>


	== Deleting rows ==		== Deleting rows ==
	=== XML ===		=== XML ===
	<syntaxhighlight lang="xml">		<div class="civ5-example"><syntaxhighlight lang="xml">
	<GameData>		<GameData>
	<Colors>		<Colors>
Line 39:		Line 39:
	</Colors>		</Colors>
	<GameData>		<GameData>
	</syntaxhighlight>		</syntaxhighlight></div>

	=== SQL ===		=== SQL ===
	<syntaxhighlight lang="sql">		<div class="civ5-example"><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 */
	DELETE FROM Colors		DELETE FROM Colors
	WHERE Type = 'COLOR_CUSTOM' AND Red = 1;		WHERE Type = 'COLOR_CUSTOM' AND Red = 1;
	</syntaxhighlight>		</syntaxhighlight></div>


	== Updating rows ==		== Updating rows ==
	=== XML ===		=== XML ===
	<syntaxhighlight lang="xml">		<div class="civ5-example"><syntaxhighlight lang="xml">
	<GameData>		<GameData>
	<Colors>		<Colors>
Line 76:		Line 76:
	</Colors>		</Colors>
	</GameData>		</GameData>
	</syntaxhighlight>		</syntaxhighlight></div>

	=== SQL ===		=== SQL ===
	<syntaxhighlight lang="sql">		<div class="civ5-example"><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 */
	UPDATE Colors		UPDATE Colors
Line 88:		Line 88:
	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;
	</syntaxhighlight>		</syntaxhighlight></div>


	== New tables ==		== New tables ==
	=== XML ===		=== XML ===
	<syntaxhighlight lang="xml">		<div class="civ5-example"><syntaxhighlight lang="xml">
	<GameData>		<GameData>
	<Table Name="MyColors">		<Table Name="MyColors">
Line 100:		Line 100:
	</Table>		</Table>
	</GameData>		</GameData>
	</syntaxhighlight>		</syntaxhighlight></div>

	=== SQL ===		=== SQL ===
	<syntaxhighlight lang="sql">		<div class="civ5-example"><syntaxhighlight lang="sql">
	/* Create one new table with two columns */		/* Create one new table with two columns */
	CREATE TABLE MyColors( id INTEGER PRIMARY KEY AUTOINCREMENT, Type TEXT UNIQUE );		CREATE TABLE MyColors( id INTEGER PRIMARY KEY AUTOINCREMENT, Type TEXT UNIQUE );
	</syntaxhighlight>		</syntaxhighlight></div>


	== New columns ==		== New columns ==
	=== SQL ===		=== SQL ===
	<syntaxhighlight lang="sql">		<div class="civ5-example"><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></div>

DonQuich at 20:10, 21 September 2012

2012-09-21T20:10:44Z

← Older revision		Revision as of 20:10, 21 September 2012
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 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>

DonQuich at 20:15, 2 September 2012

2012-09-02T20:15:32Z

← Older revision		Revision as of 20:15, 2 September 2012
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>

DonQuich at 20:14, 2 September 2012

2012-09-02T20:14:24Z

← Older revision		Revision as of 20:14, 2 September 2012
Line 1:		Line 1:
			== 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>.


	== Inserting rows ==		== Inserting rows ==
	=== XML ===		=== XML ===
Line 80:		Line 84:
	SET Red = 1, Green = 0		SET Red = 1, Green = 0
	WHERE Type = 'COLOR_CUSTOM' AND Red = 0;		WHERE Type = 'COLOR_CUSTOM' AND Red = 0;

			/* Make everything brighter */
			UPDATE Colors
			SET Red = Red + 0.1, Green = Green + 0.1, Blue=Blue + 0.1;
	</syntaxhighlight>		</syntaxhighlight>

DonQuich at 20:08, 2 September 2012

2012-09-02T20:08:46Z

← Older revision		Revision as of 20:08, 2 September 2012
Line 1:		Line 1:
	== Inserting rows ==		== Inserting rows ==
	=== XML ===		=== XML ===
	<syntaxhighlight lang="xml">		<syntaxhighlight lang="xml" style="left:40px;">
	<GameData>		<GameData>
	<Colors>		<Colors>
	<!-- ~~Insert~~ a new color -->		<!-- Inserts a new color -->
	<Row>		<Row>
	<Type>COLOR_CUSTOM</Type>		<Type>COLOR_CUSTOM</Type>
Line 18:		Line 18:
	=== SQL ===		=== SQL ===
	<syntaxhighlight lang="sql">		<syntaxhighlight lang="sql">
			/* Inserts a new color */
	INSERT INTO Colors (Type, Red, Green, Blue, Alpha)		INSERT INTO Colors (Type, Red, Green, Blue, Alpha)
	VALUES ('COLOR_CUSTOM', 0.1, 0.1, 0.1, 0.45);		VALUES ('COLOR_CUSTOM', 0.1, 0.1, 0.1, 0.45);
Line 38:		Line 39:
	=== SQL ===		=== SQL ===
	<syntaxhighlight lang="sql">		<syntaxhighlight lang="sql">
			/* Deletes all colors named TYPE_CUSTOM whose red channel is equal to 1 */
	DELETE FROM Colors		DELETE FROM Colors
	WHERE Type = 'COLOR_CUSTOM' AND Red = 1;		WHERE Type = 'COLOR_CUSTOM' AND Red = 1;
Line 71:		Line 73:
	</GameData>		</GameData>
	</syntaxhighlight>		</syntaxhighlight>


	=== SQL ===		=== SQL ===
	<syntaxhighlight lang="sql">		<syntaxhighlight lang="sql">
			/* Update all colors whose names are COLOR_CUSTOM and red channel is 0 */
	UPDATE Colors		UPDATE Colors
	SET Red = 1, Green = 0		SET Red = 1, Green = 0
	WHERE Type = 'COLOR_CUSTOM' AND Red = 0;		WHERE Type = 'COLOR_CUSTOM' AND Red = 0;
	</syntaxhighlight>		</syntaxhighlight>


	== New tables ==		== New tables ==
Line 93:		Line 96:
	=== SQL ===		=== SQL ===
	<syntaxhighlight lang="sql">		<syntaxhighlight lang="sql">
			/* Create one new table with two columns */
	CREATE TABLE MyColors( id INTEGER PRIMARY KEY AUTOINCREMENT, Type TEXT UNIQUE );		CREATE TABLE MyColors( id INTEGER PRIMARY KEY AUTOINCREMENT, Type TEXT UNIQUE );
	</syntaxhighlight>		</syntaxhighlight>
Line 100:		Line 104:
	=== SQL ===		=== SQL ===
	<syntaxhighlight lang="sql">		<syntaxhighlight lang="sql">
			/* Adds one Description column to Colors. */
	ALTER TABLE Colors COLUMN 'Description' TEXT DEFAULT NULL;		ALTER TABLE Colors COLUMN 'Description' TEXT DEFAULT NULL;
	</syntaxhighlight>		</syntaxhighlight>

DonQuich: Created page with "== Inserting rows == === XML === COLOR_CUSTOM
2012-09-02T20:02:29Z

Created page with "== Inserting rows == === XML === <syntaxhighlight lang="xml"> <GameData> <Colors>  <Row> <Type>COLOR_CUSTOM</T..."

New page
== Inserting rows ==
=== XML ===
<syntaxhighlight lang="xml">
<GameData>
<Colors>

<Row>
<Type>COLOR_CUSTOM</Type>
<Red>0.1</Red>
<Green>0.1</Green>
<Blue>0.1</Blue>
<Alpha>0.45</Alpha>
</Row>
</Colors>
<GameData>
</syntaxhighlight>

=== SQL ===
<syntaxhighlight lang="sql">
INSERT INTO Colors (Type, Red, Green, Blue, Alpha)
VALUES ('COLOR_CUSTOM', 0.1, 0.1, 0.1, 0.45);
</syntaxhighlight>

== Deleting rows ==
=== XML ===
<syntaxhighlight lang="xml">
<GameData>
<Colors>

<Delete Type="COLOR_CUSTOM" />

<Delete R="1" G="1"/>
</Colors>
<GameData>
</syntaxhighlight>

=== SQL ===
<syntaxhighlight lang="sql">
DELETE FROM Colors
WHERE Type = 'COLOR_CUSTOM' AND Red = 1;
</syntaxhighlight>

== Updating rows ==
=== XML ===
<syntaxhighlight lang="xml">
<GameData>
<Colors>

<Update>
<Where Type="COLOR_CUSTOM" Red="0"/>
<Set Red="1" Green="0"/>
</Update>

<Update>
<Where>
<Type>COLOR_CUSTOM</Type>
</Where>
<Set>
<Red>1</Red>
<Green>1</Green>
</Set>
</Update>
</Colors>
</GameData>
</syntaxhighlight>

=== SQL ===
<syntaxhighlight lang="sql">
UPDATE Colors
SET Red = 1, Green = 0
WHERE Type = 'COLOR_CUSTOM' AND Red = 0;
</syntaxhighlight>

== New tables ==
=== XML ===
<syntaxhighlight lang="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>
</syntaxhighlight>

=== SQL ===
<syntaxhighlight lang="sql">
CREATE TABLE MyColors( id INTEGER PRIMARY KEY AUTOINCREMENT, Type TEXT UNIQUE );
</syntaxhighlight>

== New columns ==
=== SQL ===
<syntaxhighlight lang="sql">
ALTER TABLE Colors COLUMN 'Description' TEXT DEFAULT NULL;
</syntaxhighlight>