Serial creation of city names - is it possible with SQL?

[PawelS]

I have a question to SQL experts - is it possible to make creating city names easier?

In XML it looks like this, and you can see that that it contains lots or redundancy:

Spoiler:

<GameData>
	<Civilization_CityNames>
		<Row>
			<CivilizationType>CIVILIZATION_DREAMERS</CivilizationType>
			<CityName>TXT_KEY_DRE_01</CityName>
		</Row>
		<Row>
			<CivilizationType>CIVILIZATION_DREAMERS</CivilizationType>
			<CityName>TXT_KEY_DRE_02</CityName>
		</Row>
		<Row>
			<CivilizationType>CIVILIZATION_DREAMERS</CivilizationType>
			<CityName>TXT_KEY_DRE_03</CityName>
		</Row>
		<Row>
			<CivilizationType>CIVILIZATION_DREAMERS</CivilizationType>
			<CityName>TXT_KEY_DRE_04</CityName>
		</Row>
		<Row>
			<CivilizationType>CIVILIZATION_DREAMERS</CivilizationType>
			<CityName>TXT_KEY_DRE_05</CityName>
		</Row>
		<Row>
			<CivilizationType>CIVILIZATION_DREAMERS</CivilizationType>
			<CityName>TXT_KEY_DRE_06</CityName>
		</Row>
		<Row>
			<CivilizationType>CIVILIZATION_DREAMERS</CivilizationType>
			<CityName>TXT_KEY_DRE_07</CityName>
		</Row>
		<Row>
			<CivilizationType>CIVILIZATION_DREAMERS</CivilizationType>
			<CityName>TXT_KEY_DRE_08</CityName>
		</Row>
		<Row>
			<CivilizationType>CIVILIZATION_DREAMERS</CivilizationType>
			<CityName>TXT_KEY_DRE_09</CityName>
		</Row>
		<Row>
			<CivilizationType>CIVILIZATION_DREAMERS</CivilizationType>
			<CityName>TXT_KEY_DRE_10</CityName>
		</Row>
		<Row>
			<CivilizationType>CIVILIZATION_DREAMERS</CivilizationType>
			<CityName>TXT_KEY_DRE_11</CityName>
		</Row>
		<Row>
			<CivilizationType>CIVILIZATION_DREAMERS</CivilizationType>
			<CityName>TXT_KEY_DRE_12</CityName>
		</Row>
		<Row>
			<CivilizationType>CIVILIZATION_DREAMERS</CivilizationType>
			<CityName>TXT_KEY_DRE_13</CityName>
		</Row>
		<Row>
			<CivilizationType>CIVILIZATION_DREAMERS</CivilizationType>
			<CityName>TXT_KEY_DRE_14</CityName>
		</Row>
		<Row>
			<CivilizationType>CIVILIZATION_DREAMERS</CivilizationType>
			<CityName>TXT_KEY_DRE_15</CityName>
		</Row>
		<Row>
			<CivilizationType>CIVILIZATION_DREAMERS</CivilizationType>
			<CityName>TXT_KEY_DRE_16</CityName>
		</Row>
	</Civilization_CityNames>
	<Language_en_US>
		<Row Tag="TXT_KEY_DRE_01">
			<Text>Innsmouth</Text>
		</Row>
		<Row Tag="TXT_KEY_DRE_02">
			<Text>Dunwich</Text>
		</Row>
		<Row Tag="TXT_KEY_DRE_03">
			<Text>Kingsport</Text>
		</Row>
		<Row Tag="TXT_KEY_DRE_04">
			<Text>Aylesbury</Text>
		</Row>
		<Row Tag="TXT_KEY_DRE_05">
			<Text>Paradera</Text>
		</Row>
		<Row Tag="TXT_KEY_DRE_06">
			<Text>Bolans</Text>
		</Row>
		<Row Tag="TXT_KEY_DRE_07">
			<Text>Seroe Vakas</Text>
		</Row>
		<Row Tag="TXT_KEY_DRE_08">
			<Text>Bathsheba</Text>
		</Row>
		<Row Tag="TXT_KEY_DRE_09">
			<Text>Oistins</Text>
		</Row>
		<Row Tag="TXT_KEY_DRE_10">
			<Text>Boscobelle</Text>
		</Row>
		<Row Tag="TXT_KEY_DRE_11">
			<Text>Speightstown</Text>
		</Row>
		<Row Tag="TXT_KEY_DRE_12">
			<Text>Willikies</Text>
		</Row>
		<Row Tag="TXT_KEY_DRE_13">
			<Text>Garrislyr</Text>
		</Row>
		<Row Tag="TXT_KEY_DRE_14">
			<Text>Villanon</Text>
		</Row>
		<Row Tag="TXT_KEY_DRE_15">
			<Text>Undertow</Text>
		</Row>
		<Row Tag="TXT_KEY_DRE_16">
			<Text>Bren Bagage</Text>
		</Row>
	</Language_en_US>
</GameData>

(It's just an example, actually I'm going to use more names than 16. The city names I used here are "borrowed" from FFH, I hope nobody objects if I use them in my mod too...)

Would it be possible to use SQL to create series of data using less instructions, like I describe using pseudocode below:

In "Civilization_CityNames" create 16 rows with CivilizationType set to CIVILIZATION_DREAMERS and CityName set to TXT_KEY_DRE_xx, where xx ranges from 01 to 16.

In Language_en_US create 16 rows with Tag set to TXT_KEY_DRE_xx (xx is generated analogically to the above) and Text set to the following: Innsmouth, Dunwich, Kingsport... (a list of 16 names comes here).

If such thing is possible, it would really help me making my mod.

[whoward69]

DELETE FROM Language_en_US WHERE Tag LIKE 'TXT_KEY_DRE_%';

INSERT INTO Language_en_US(Tag, Text) SELECT 'TXT_KEY_DRE_'||ROWID as Tag, Text FROM (SELECT 'City A' as Text UNION ALL SELECT 'City B' UNION ALL SELECT 'City C');

INSERT INTO Civilization_CityNames(CivilizationType, CityName) SELECT 'CIVILIZATION_DREAMERS' as CivilizationType, Tag as CityName FROM Language_en_US WHERE Tag LIKE 'TXT_KEY_DRE_%';

Is the best I can come up with, but the UNION ALL ... stuff is vile!

(If Civ V was using SQLite 3.7.11 (and not 3.7.6) you could make it nicer with multi-row inserts via "VALUES(), (), ()" but it's not, so you can't!)

[Machiavelli24]

Wouldn't it work to copy paste what you've got and then do a find replace of:
"CIVILIZATION_DREAMERS" with "CIVILIZATION_ORCS"
"TXT_KEY_DRE_" with "TXT_KEY_ORC_"
You would still have to enter the new city names manually but I assume you would have to do that anyway?

If you wanted to get more automated it would be relatively trivial to write a perl/lua/python/lightweight scripting language of your choice that took in a list of city names and spat out a well formed xml file.

[PawelS]

I know, but I really prefer when the actual code in the mod is more clear and contains less redundancy. I think I'll use something like whoward69's solution.

[whoward69]

If there's no requirement for the civ city names to start from 1 (eg you can have TXT_KEY_ORCS_4 as the first orc city if there are three dreamers' cities)

Then this may be more readable/maintainable

  <Table name="MyCities">
    <Column name="Name" type="text" primarykey="true"/>
    <Column name="Code" type="text"/>
  </Table>

  <MyCities>
    <Row Code="DRE" Name="London"/>
    <Row Code="DRE" Name="Bristol"/>
    <Row Code="DRE" Name="Liverpool"/>
    <Row Code="THI" Name="Athens"/>
    <Row Code="THI" Name="Corinth"/>
    <Row Code="POS" Name="Rome"/>
    <Row Code="POS" Name="Milan"/>
    <Row Code="POS" Name="Hollywood"/>
    <Row Code="POS" Name="Paris"/>
  </MyCities>

INSERT INTO Language_en_US(Tag, Text) SELECT 'TXT_KEY_'||Code||'_'||ROWID as Tag, Name as Text FROM MyCities WHERE Code='DRE' ORDER BY ROWID ASC;

and the other SQL to update the civ_cities table.

The SQL will then be fairly static (only changing when you add a new civ), and the list of cities for each civ (by code) is nicely IMHO readable

HTH

W

[PawelS]

Looks good, thanks!

[PawelS]

I'm trying to accomplish another thing with SQL, reducing the amount of code: I want to disable a tech for all but a few civs.

So it should be something like this:

In the Civilization_DisableTechs table, create a row for every row in Civilizations, except those that have Type=CIVILIZATION_NIKKEI, CIVILIZATION_AZRACS or CIVILIZATION_LEMURIA. In that row, set CivilizationType the same as Type in Civilizations, and TechType to TECH_WAR_ELEPHANTS_1.

(Btw I heard that Barbarians and Minor Civs can get the disabled techs, but it's not a big problem in this case, because I can disable the units and buildings that these techs give for CIVILIZATION_BARBARIAN and CIVILIZATION_MINOR using Civilization_BuildingClassOverrides and Civilization_UnitClassOverrides.)

[whoward69]

INSERT INTO Civilization_DisableTechs(CivilizationType, TechType) SELECT Type, 'TECH_WAR_ELEPHANTS_1' FROM Civilizations WHERE Type NOT IN ('CIVILIZATION_NIKKEI', 'CIVILIZATION_AZRACS', 'CIVILIZATION_LEMURIA');

[PawelS]

I haven't expected that it's possible with one (albeit long) SQL command. Many thanks again, your code will help me learn SQL and resolve similar problems myself

[whoward69]

You can write it over multiple lines to make it easier to update/read

INSERT INTO Civilization_DisableTechs(CivilizationType, TechType)
    SELECT Type, 'TECH_WAR_ELEPHANTS_1'
        FROM Civilizations 
        WHERE Type NOT IN (
            'CIVILIZATION_NIKKEI', 
            'CIVILIZATION_AZRACS', 
            'CIVILIZATION_LEMURIA'
        );

Just don't forget the final ; (as I'm inclined to do!)

[magzhi]

Off-topic:
Lanun cities FTW ^^

[PawelS]

Yeah, I like them too, my favorite is Paradera

[Pazyryk]

Originally Posted by whoward69

Is the best I can come up with, but the UNION ALL ... stuff is vile!

(If Civ V was using SQLite 3.7.11 (and not 3.7.6) you could make it nicer with multi-row inserts via "VALUES(), (), ()" but it's not, so you can't!)

What's wrong with SELECT ... UNION ALL lines? It's neat, and super easy to read and edit:

Spoiler:

I agree that VALUES(), (), (), ... would be more elegant. But SELECT ... UNION ALL lines do the job well enough.

[PawelS]

@Pazyryk: Your code looks very clean indeed (I think whoward69 meant that the UNION ALL directive itself looks strange, but you're right that if organized well, the code looks good). I took a different approach though - I use XML, and put all things related to the unit after the code that defines it (stuff from additional tables for Units, and text keys). This way I have all things in one place, so it's easy to check a unit's free promotions, description etc.

Going back to the subject of reducing the amount of code: here is what I came up with - I assigned the starting Settler to all civs except Barbarians, using code analogous to whoward69's:

INSERT INTO Civilization_FreeUnits (CivilizationType, UnitClassType, UnitAIType, Count)
SELECT Type, 'UNITCLASS_SETTLER', 'UNITAI_SETTLE', 1 FROM Civilizations 
WHERE Type <> 'CIVILIZATION_BARBARIAN';

The same can be done with giving them the Palace, and a free tech (if it's the same for all civs like in vanilla game; in my mod civs get different starting techs).