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"):
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:
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:
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.
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.