SQL method for entering many many items (rows)

Pazyryk

Deity
Joined
Jun 13, 2008
Messages
3,584
July 2013 Update: SQLite got updated with the new patch, allowing you to now use the "VALUES" construction below. See here.

If you have 40 or so items (rows) to add to a table, you can actually do it in not many more than 40 lines of SQL, with only one (or maybe a few) INSERT statements.

This is supposed to work in SQL but does not work in Civ5's SQLite (perhaps that's why it's "Lite"):

Code:
INSERT INTO MyTable (Type, TxtTag, Col3, Col4, Col5)
VALUES ('MYTHING',  'TXT_TAG_MYTHING',  'data3', 'data4', 'data5'),
	('MYTHING2', 'TXT_TAG_MYTHING2', 'data3', 'data4', 'data5'),
	('MYTHING3', 'TXT_TAG_MYTHING3', 'data3', 'data4', 'data5'),
	('MYTHING4', 'TXT_TAG_MYTHING4', 'data3', 'data4', 'data5'),
	('MYTHING5', 'TXT_TAG_MYTHING5', 'data3', 'data4', 'data5');

Actually, I never tested the above in game because it doesn't work in SQLite Manager. Since I'm going to be entering 100s of items for my mod I have no hope if I can't troubleshoot in SQLite Manager first. (If you can add 5 new things without mistakes, then you are a better typer than me. SQLite Manager catches these mistakes in a microsecond and gives some indication of where the problem is.)

However, after googling a bit and finding this, I tried the following and it works perfectly:

Code:
INSERT INTO MyTable (Type, TxtTag,     Col3,    Col4,    Col5)
SELECT 'MYTHING',  'TXT_TAG_MYTHING',  'data3', 'data4', 'data5' UNION ALL
SELECT 'MYTHING2', 'TXT_TAG_MYTHING2', 'data3', 'data4', 'data5' UNION ALL
SELECT 'MYTHING3', 'TXT_TAG_MYTHING3', 'data3', 'data4', 'data5' UNION ALL
SELECT 'MYTHING4', 'TXT_TAG_MYTHING4', 'data3', 'data4', 'data5' UNION ALL
SELECT 'MYTHING5', 'TXT_TAG_MYTHING5', 'data3', 'data4', 'data5';

The construction seems weird to me, but it works as I've shown. Don't try to add UNION ALL in the last line or additional commas. I also find it helpful to line up the column names with the data by using tabs in the ModBuddy editor, especially for tables with a lot of stuff (see screenshot in post #2 below). If you have a lot of items to add to the game that need to change different columns, but you can organize them into groups in some way, then you can do something like this:

Code:
INSERT INTO MyTable (Type, TxtTag,     Col3,    Col4,    Col5)
SELECT 'MYTHING',  'TXT_TAG_MYTHING',  'data3', 'data4', 'data5' UNION ALL
SELECT 'MYTHING2', 'TXT_TAG_MYTHING2', 'data3', 'data4', 'data5' UNION ALL
SELECT 'MYTHING3', 'TXT_TAG_MYTHING3', 'data3', 'data4', 'data5' UNION ALL
SELECT 'MYTHING4', 'TXT_TAG_MYTHING4', 'data3', 'data4', 'data5' UNION ALL
SELECT 'MYTHING5', 'TXT_TAG_MYTHING5', 'data3', 'data4', 'data5';

INSERT INTO MyTable (Type, TxtTag,     Col6,    Col7,     Col8)
SELECT 'MYTHING6', 'TXT_TAG_MYTHING6', 'data6', 'data7', 'data8' UNION ALL
SELECT 'MYTHING7', 'TXT_TAG_MYTHING7', 'data6', 'data7', 'data8';

INSERT INTO MyTable (Type, TxtTag,       Col4,    Col9,    Col10)
SELECT 'MYTHING8',  'TXT_TAG_MYTHING8',  'data4', 'data9', 'data10' UNION ALL
SELECT 'MYTHING9',  'TXT_TAG_MYTHING9',  'data4', 'data9', 'data10' UNION ALL
SELECT 'MYTHING10', 'TXT_TAG_MYTHING10', 'data4', 'data9', 'data10' UNION ALL
SELECT 'MYTHING11', 'TXT_TAG_MYTHING11', 'data4', 'data9', 'data10';

So that's 11 items in 14 lines of SQL that is very readable and can be tested in SQLite. Trying to write even my small example in XML would be a pain (77 lines if I'm not mistaken). As a matter of fact, I just created two tables with 24 columns (between them) and 58 total rows. It took me a while to populate it but it only took me about 15 minutes to find and fix about 5 different typos.
 
Edit: I kept saying "tested in SQLite" above, which sounds kind of ignorant. What I really meant was SQlite Manager from Firefox. You should be using this for any modding, whether you are adding with XML or SQL, so you know that you are having the desired effect on the DB.

Here's an example from my Éa mod in progress. If you use tabs, you can get all columns lined up for easy reading/editing. It looks like this in ModBuddy:

Spoiler :
 

Attachments

  • sql example.jpg
    sql example.jpg
    336.4 KB · Views: 5,856
I did not realise you could insert tabbing...thats cool...I will be going over my code to make it more easier to read.
 
I see in your examples that you have left out the ID Column. Is this not needed?

I have this(its Plit into 1 block per era.):-
Code:
INSERT INTO Technologies	(ID,	Type,										Description,											Civilopedia,										Help,												AIWeight,	AITradeModifier,	Cost,	AdvancedStartCost,	Era,				FirstFreeUnitClass, FeatureProductionModifier,	WorkerSpeedModifier,	FirstFreeTechs, EmbarkedMoveChange,		EndsGame,	AllowsEmbarking,	AllowsDefensiveEmbarking,	EmbarkedAllWaterPassage,	AllowsBarbarianBoats,	Repeat, Trade,	Disable, GoodyTech, ExtraWaterSeeFrom,	MapCentering,	MapVisible, MapTrading, TechTrading,	GoldTrading,	AllowEmbassyTradingAllowed, OpenBordersTradingAllowed,	DefensivePactTradingAllowed,	ResearchAgreementTradingAllowed,	TradeAgreementTradingAllowed,	PermanentAllianceTradingAllowed,	BridgeBuilding, WaterWork,	GridX,	GridY,	Quote,												PortraitIndex,	IconAtlas,			 AudioIntro,							AudioIntroHeader,								TechCat,				Tier)
							-- Populate table, Ancient First, 
							--Settler		
SELECT						[COLOR="Red"]0[/COLOR],		'TECH_AGRICULTURE',							'TXT_KEY_TECH_AGRICULTURE_TITLE',						'TXT_KEY_TECH_AGRICULTURE_DESC',					'TXT_KEY_TECH_AGRICULTURE_HELP',					0,			0,					10,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			0,		3,		'TXT_KEY_TECH_AGRICULTURE_QUOTE',					0,				'TECH_ATLAS_1',		'AS2D_TECH_AGRICULTURE',				'AS2D_HEADING_TECH_AGRICULTURE',				'TECHTREE_LAND',		1	UNION ALL
SELECT						1,		'TECH_CALENDAR',							'TXT_KEY_TECH_CALENDAR_TITLE',							'TXT_KEY_TECH_CALENDAR_DESC',						'TXT_KEY_TECH_CALENDAR_HELP',						0,			0,					12,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			1,		3,		'TXT_KEY_TECH_CALENDAR_QUOTE',						9,				'TECH_ATLAS_1',		'AS2D_TECH_CALENDAR',					'AS2D_HEADING_TECH_CALENDAR',					'TECHTREE_LAND',		2	UNION ALL
SELECT						2,		'TECH_ANIMAL_HUSBANDRY',					'TXT_KEY_TECH_ANIMAL_HUSBANDRY_TITLE',					'TXT_KEY_TECH_ANIMAL_HUSBANDRY_DESC',				'TXT_KEY_TECH_ANIMAL_HUSBANDRY_HELP',				0,			0,					12,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			1,		2,		'TXT_KEY_TECH_ANIMAL_HUSBANDRY_QUOTE',				2,				'TECH_ATLAS_1',		'AS2D_TECH_ANIMAL_HUSBANDRY',			'AS2D_HEADING_TECH_ANIMAL_HUSBANDRY',			'TECHTREE_LAND',		2	UNION ALL
SELECT						3,		'TECH_HERDING',								'TXT_KEY_TECH_HERDING_TITLE',							'TXT_KEY_TECH_HERDING_DESC',						'TXT_KEY_TECH_HERDING_HELP',						0,			0,					14,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			2,		2,		'TXT_KEY_TECH_HERDING_QUOTE',						62,				'TECH_ATLAS_4',		NULL,									NULL,											'TECHTREE_LAND',		3	UNION ALL

SELECT						4,		'TECH_MINING',								'TXT_KEY_TECH_MINING_TITLE',							'TXT_KEY_TECH_MINING_DESC',							'TXT_KEY_TECH_MINING_HELP',							0,			0,					10,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			0,		6,		'TXT_KEY_TECH_MINING_QUOTE',						1,				'TECH_ATLAS_1',		'AS2D_TECH_MINING',						'AS2D_HEADING_TECH_MINING',						'TECHTREE_LAND',		1	UNION ALL
SELECT						5,		'TECH_RAW_MATERIALS',						'TXT_KEY_TECH_RAW_MATERIALS_TITLE',						'TXT_KEY_TECH_RAW_MATERIALS_DESC',					'TXT_KEY_TECH_RAW_MATERIALS_HELP',					0,			0,					12,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			1,		6,		'TXT_KEY_TECH_RAW_MATERIALS_QUOTE',					2,				'TECH_ATLAS_5',		NULL,									NULL,											'TECHTREE_LAND',		2	UNION ALL
SELECT						6,		'TECH_POTTERY',								'TXT_KEY_TECH_POTTERY_TITLE',							'TXT_KEY_TECH_POTTERY_DESC',						'TXT_KEY_TECH_POTTERY_HELP',						0,			0,					14,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			2,		6,		'TXT_KEY_TECH_POTTERY_QUOTE',						4,				'TECH_ATLAS_1',		'AS2D_TECH_POTTERY',					'AS2D_HEADING_TECH_POTTERY',					'TECHTREE_LAND',		3	UNION ALL
SELECT						7,		'TECH_MASONRY',								'TXT_KEY_TECH_MASONRY_TITLE',							'TXT_KEY_TECH_MASONRY_DESC',						'TXT_KEY_TECH_MASONRY_HELP',						0,			0,					16,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			3,		6,		'TXT_KEY_TECH_MASONRY_QUOTE',						6,				'TECH_ATLAS_1',		'AS2D_TECH_MASONRY',					'AS2D_HEADING_TECH_MASONRY',					'TECHTREE_LAND',		4	UNION ALL
			
							--Nomad
SELECT						8,		'TECH_SAILING',								'TXT_KEY_TECH_SAILING_TITLE',							'TXT_KEY_TECH_SAILING_DESC',						'TXT_KEY_TECH_SAILING_HELP',						0,			0,					10,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			0,		2,		'TXT_KEY_TECH_SAILING_QUOTE',						13,				'TECH_ATLAS_1',		'AS2D_TECH_SAILING',					'AS2D_HEADING_TECH_SAILING',					'TECHTREE_SEA',			1	UNION ALL
SELECT						9,		'TECH_SHIPBUILDING',						'TXT_KEY_TECH_SHIPBUILDING_TITLE',						'TXT_KEY_TECH_SHIPBUILDING_DESC',					'TXT_KEY_TECH_SHIPBUILDING_HELP',					0,			0,					12,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			1,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			1,		2,		'TXT_KEY_TECH_SHIPBUILDING_QUOTE',					18,				'TECH_ATLAS_4',		NULL,									NULL,											'TECHTREE_SEA',			2	UNION ALL

SELECT						10,		'TECH_ECONOMY_OF_SCALE',					'TXT_KEY_TECH_ECONOMY_OF_SCALE_TITLE',					'TXT_KEY_TECH_ECONOMY_OF_SCALE_DESC',				'TXT_KEY_TECH_ECONOMY_OF_SCALE_HELP',				0,			0,					10,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			0,		4,		'TXT_KEY_TECH_ECONOMY_OF_SCALE_QUOTE',				34,				'TECH_ATLAS_4',		NULL,									NULL,											'TECHTREE_SEA',			1	UNION ALL
SELECT						11,		'TECH_TRADING',								'TXT_KEY_TECH_TRADING_TITLE',							'TXT_KEY_TECH_TRADING_DESC',						'TXT_KEY_TECH_TRADING_HELP',						0,			0,					12,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			1,		4,		'TXT_KEY_TECH_TRADING_QUOTE',						44,				'TECH_ATLAS_4',		NULL,									NULL,											'TECHTREE_SEA',			2	UNION ALL
SELECT						12,		'TECH_FERMENTATION',						'TXT_KEY_TECH_FERMENTATION_TITLE',						'TXT_KEY_TECH_FERMENTATION_DESC',					'TXT_KEY_TECH_FERMENTATION_HELP',					0,			0,					14,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			2,		4,		'TXT_KEY_TECH_FERMENTATION_QUOTE',					35,				'TECH_ATLAS_4',		NULL,									NULL,											'TECHTREE_SEA',			3	UNION ALL

SELECT						13,		'TECH_TRAPPING',							'TXT_KEY_TECH_TRAPPING_TITLE',							'TXT_KEY_TECH_TRAPPING_DESC',						'TXT_KEY_TECH_TRAPPING_HELP',						0,			0,					10,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			0,		6,		'TXT_KEY_TECH_TRAPPING_QUOTE',						13,				'TECH_ATLAS_2',		'AS2D_TECH_TRAPPING',					'AS2D_HEADING_TECH_TRAPPING',					'TECHTREE_SEA',			1	UNION ALL
SELECT						14,		'TECH_ARCHERY',								'TXT_KEY_TECH_ARCHERY_TITLE',							'TXT_KEY_TECH_ARCHERY_DESC',						'TXT_KEY_TECH_ARCHERY_HELP',						0,			0,					12,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			1,		6,		'TXT_KEY_TECH_ARCHERY_QUOTE',						7,				'TECH_ATLAS_1',		'AS2D_TECH_ARCHERY',					'AS2D_HEADING_TECH_ARCHERY',					'TECHTREE_SEA',			2	UNION ALL
SELECT						15,		'TECH_BRONZE_WORKING',						'TXT_KEY_TECH_BRONZE_WORKING_TITLE',					'TXT_KEY_TECH_BRONZE_WORKING_DESC',					'TXT_KEY_TECH_BRONZE_WORKING_HELP',					0,			0,					12,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			1,		7,		'TXT_KEY_TECH_BRONZE_WORKING_QUOTE',				5,				'TECH_ATLAS_1',		'AS2D_TECH_BRONZE_WORKING',				'AS2D_HEADING_TECH_BRONZE_WORKING',				'TECHTREE_SEA',			2	UNION ALL
							--Spirit
SELECT						16,		'TECH_ORAL_TRADITION',						'TXT_KEY_TECH_ORAL_TRADITION_TITLE',					'TXT_KEY_TECH_ORAL_TRADITION_DESC',					'TXT_KEY_TECH_ORAL_TRADITION_HELP',					0,			0,					10,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			0,		2,		'TXT_KEY_TECH_ORAL_TRADITION_QUOTE',				27,				'TECH_ATLAS_4',		NULL,									NULL,											'TECHTREE_SPIRIT',		1	UNION ALL
SELECT						17,		'TECH_CEREMONIAL_BURIAL',					'TXT_KEY_TECH_CEREMONIAL_BURIAL_TITLE',					'TXT_KEY_TECH_CEREMONIAL_BURIAL_DESC',				'TXT_KEY_TECH_CEREMONIAL_BURIAL_HELP',				0,			0,					12,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			1,		2,		'TXT_KEY_TECH_CEREMONIAL_BURIAL_QUOTE',				40,				'TECH_ATLAS_4',		NULL,									NULL,											'TECHTREE_SPIRIT',		2	UNION ALL
SELECT						18,		'TECH_GREAT_MONUMENTS',						'TXT_KEY_TECH_GREAT_MONUMENTS_TITLE',					'TXT_KEY_TECH_GREAT_MONUMENTS_DESC',				'TXT_KEY_TECH_GREAT_MONUMENTS_HELP',				0,			0,					14,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			2,		2,		'TXT_KEY_TECH_GREAT_MONUMENTS_QUOTE',				60,				'TECH_ATLAS_4',		NULL,									NULL,											'TECHTREE_SPIRIT',		3	UNION ALL

SELECT						19,		'TECH_MYSTICISM',							'TXT_KEY_TECH_MYSTICISM_TITLE',							'TXT_KEY_TECH_MYSTICISM_DESC',						'TXT_KEY_TECH_MYSTICISM_HELP',						0,			0,					10,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			0,		4,		'TXT_KEY_TECH_MYSTICISM_QUOTE',						0,				'TECH_ATLAS_3',		NULL,									NULL,											'TECHTREE_SPIRIT',		1	UNION ALL
SELECT						20,		'TECH_POLYTHEISM',							'TXT_KEY_TECH_POLYTHEISM_TITLE',						'TXT_KEY_TECH_POLYTHEISM_DESC',						'TXT_KEY_TECH_POLYTHEISM_HELP',						0,			0,					12,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			1,		4,		'TXT_KEY_TECH_POLYTHEISM_QUOTE',					1,				'TECH_ATLAS_3',		NULL,									NULL,											'TECHTREE_SPIRIT',		2	UNION ALL
SELECT						21,		'TECH_MEDITATION',							'TXT_KEY_TECH_MEDITATION_TITLE',						'TXT_KEY_TECH_MEDITATION_DESC',						'TXT_KEY_TECH_MEDITATION_HELP',						0,			0,					14,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			2,		4,		'TXT_KEY_TECH_MEDITATION_QUOTE',					2,				'TECH_ATLAS_3',		NULL,									NULL,											'TECHTREE_SPIRIT',		3	UNION ALL
					
SELECT						22,		'TECH_SPORTS',								'TXT_KEY_TECH_SPORTS_TITLE',							'TXT_KEY_TECH_SPORTS_DESC',							'TXT_KEY_TECH_SPORTS_HELP',							0,			0,					16,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			3,		6,		'TXT_KEY_TECH_SPORTS_QUOTE',						36,				'TECH_ATLAS_4',		NULL,									NULL,											'TECHTREE_SPIRIT',		4	UNION ALL
SELECT						23,		'TECH_MILITARY_TRAINING',					'TXT_KEY_TECH_MILITARY_TRAINING_TITLE',					'TXT_KEY_TECH_MILITARY_TRAINING_DESC',				'TXT_KEY_TECH_MILITARY_TRAINING_HELP',				0,			0,					18,		-1,					'ERA_ANCIENT',		NULL,				0,							0,						0,				0,						0,			0,					0,							0,							0,						0,		1,		0,		 1,			0,					0,				0,			0,			0,				0,				0,							0,							0,								0,									0,								0,									0,				0,			4,		6,		'TXT_KEY_TECH_MILITARY_TRAINING_QUOTE',				4,				'TECH_ATLAS_5',		NULL,									NULL,											'TECHTREE_SPIRIT',		5;
The character in red displays a Syntax Error, Expected: all, distinct, count, avg, min, max, stdev, stdevp, sum, var, varp, id_simple * , in Modbuddy.
I have dropped the whole table in a different sql(that works) and repopulating it with my own values, + 2 additional column's.

Edit:

Adjusted code a bit.
 
So that's 11 items in 14 lines of SQL that is very readable and can be tested in SQLite. Trying to write even my small example in XML would be a pain (77 lines if I'm not mistaken).

It's not commonly known but you can replace child elements with attributes in the <Row> tag, so

Code:
<GameData><MyTable>
  <Row Type="MYTHING"  TxtTag="TXT_TAG_MYTHING"  Col3="data3" Col4="data4" Col5="data5"/>
  <Row Type="MYTHING2" TxtTag="TXT_TAG_MYTHING2" Col3="data3" Col4="data4" Col5="data5"/>
  <Row Type="MYTHING3" TxtTag="TXT_TAG_MYTHING3" Col3="data3" Col4="data4" Col5="data5"/>
  <Row Type="MYTHING4" TxtTag="TXT_TAG_MYTHING4" Col3="data3" Col4="data4" Col5="data5"/>
  <Row Type="MYTHING5" TxtTag="TXT_TAG_MYTHING5" Col3="data3" Col4="data4" Col5="data5"/>
</MyTable></GameData>

It's more verbose (all the attribute names), but doesn't have to be any more lines that the equivalent SQL
 
The character in red displays a Syntax Error, Expected: all, distinct, count, avg, min, max, stdev, stdevp, sum, var, varp, id_simple * , in Modbuddy.
I have dropped the whole table in a different sql(that works) and repopulating it with my own values, + 2 additional column's.
The ModBuddy SQL editor always sees a bug (even when there isn't one). IIRC it's any insert statement, but I may be wrong on that. It has been noted by others. I do bug checking by having SQlite Manager open -- you can paste blocks of code in to get instant feedback.


I see in your examples that you have left out the ID Column. Is this not needed?

Depends.

For core game tables, I always delete-add-renumber:
Code:
DELETE FROM BuildingClasses;
INSERT INTO BuildingClasses (Type,			DefaultBuilding,				Description)
SELECT 'BUILDINGCLASS_MONUMENT',			'BUILDING_MONUMENT',			'TXT_KEY_EA_BUILDING_MONUMENT'				UNION ALL
...

--fixinator
CREATE TABLE IDRemapper ( id INTEGER PRIMARY KEY AUTOINCREMENT, Type TEXT );
INSERT INTO IDRemapper (Type) SELECT Type FROM BuildingClasses;
UPDATE BuildingClasses SET ID =	( SELECT IDRemapper.id-1 FROM IDRemapper WHERE BuildingClasses.Type = IDRemapper.Type);
DROP TABLE IDRemapper;
So no need for IDs there. They will be consecutive (starting with 0) after the renumbering at the end.

New tables are always created with id autoincrement:
Code:
CREATE TABLE EaArtifacts ('ID' INTEGER PRIMARY KEY AUTOINCREMENT,
						'Type' TEXT NOT NULL UNIQUE,
						'Description' TEXT DEFAULT NULL,
						'ItemClass' TEXT DEFAULT NULL,
						'EaAction' TEXT DEFAULT NULL,
						'IconIndex' INTEGER DEFAULT NULL,
						'IconAtlas' TEXT DEFAULT NULL);
SQlite default is to always start with ID=1. You can eaither leave it that way (and have all of your added tables start with 1...which is not a problem as far as I have seen) or you can set ID=0 for your first entry (the rest will autoincrement after that).

Or you can explicitly set it as you are doing. I don't see any problem with that except it looks like a pain if you want to add a new item in the middle of your table.

@whoward69,

OK, you got me there. But I still like my nice neat SQL columns better.
 
TY for replies, I also prefer the look of sql over xml :), that error seems to be irrelevant since it all loads into the db fine.
 
Top Bottom