SQL modding for dummies! (specially those that still use XML)

[LastSword]

Heh.
Ok, I might miss something.
How do you do stuff in 4.2? You open Civ5Buildings.xml, find the building and what? Copy/type every single column? Or there is some program are you using? Because I can't simply calculate how can it be faster than copying it into xml and replace Gold, BuildingType values... or even not stressful (for impatient people BuildingType, BuildingClass, close SDK).

That easy commenting and interesting code (lol at this one). Speaking of advantages, I think Sql loads faster (because game converts xml into sql anyway, at least I heard so), though I don't know the time difference.

 

[Civitar]

So... how does one go about adding a column to a table only if it does not already exist?
I know how to add a table if it doesn't already exist, but how do I do it with columns? Would this work?

ALTER TABLE TableName ADD COLUMN IF NOT EXISTS 'ColumnName';

 

[whoward69]

So... how does one go about adding a column to a table only if it does not already exist?
I know how to add a table if it doesn't already exist, but how do I do it with columns? Would this work?

ALTER TABLE TableName ADD COLUMN IF NOT EXISTS 'ColumnName';

You can't use IF NOT EXISTS on columns.

You could probably construct something hideous by checking the existing definition of the table, but there is an easier way ...

Put the "ALTER TABLE ..." in an SQL file on its own, make sure it executes first (or very early) and simply ignore the error in database.log if it fails.

 

[notque]

Any tips on where a log file is for loading the sql, and if it worked or not? I made a new mod, uploaded it. Turned it on in game, if I look in sqllite manager, I don't see my edits.

Where can I find the log of errors?

 

[whoward69]

Any tips on where a log file is for loading the sql

Enable logging (link 1 in my sig), then look in database.log

 

[meRlinX_AT]

Hello,
i stuck a little with my knowledge ... maybe someone can enlighten me?

i have a query, which works

but i will replace the entry's (value AirModifier which can also 1 of ~80 of the others), and make things simpler and something like this,... which works half



have also tested some other query's, but still no luck.

thanks for all hints.

 

[meRlinX_AT]

thanks

 

[qqqbbb]

I have this sql code:

INSERT INTO Units ( Type )
SELECT 	'NEW_'||Type
FROM Units WHERE Combat > 0

How do I modify it so that it checks value in another column and works even if the column is not in the table?

 

[Nutty]

Hopefully you've already figured it out, but I'd do it like:

...WHERE Combat > 0 AND (COL_LENGTH(Units, Foo) IS NULL OR Foo > 17);

 

[qqqbbb]

Hopefully you've already figured it out, but I'd do it like:

...WHERE Combat > 0 AND (COL_LENGTH(Units, Foo) IS NULL OR Foo > 17);

I get error " no such function: COL_LENGTH".
What version of sql does the game use?

 

[PawelS]

It's SQLite (I don't know which version). It doesn't seem to have the COL_LENGTH function indeed.

 

[Nutty]

Sorry, that's right...

 

[ArchaicWarrior]

Anyone know how to access the figure animations, like builders buiding fences around cows, sheep and horses, in SQL ?

I think it might be in the ART_DEF tables but not sure.

thanks

 

[Nutty]

No, the animations aren't referenced in the database. The art define's model entry in the database refers to a .fxsxml file which references a base .gr2 3D model and contains event codes for animations, sounds, and particle effects that are defined in the referenced .ftsxml (fx_triggers) file. The animations themselves are also .gr2 files. If you have further questions, this isn't the thread for it. Create a new thread in the main Creation & Customization subforum.

 

[meRlinX_AT]

stored queries are not possible with sqlite - or is there a solution?
Maybe someone can help with a sqlite_prepare/sqlite_userfunction?

 

[Nutty]

What are you trying to do?

 

[meRlinX_AT]

What are you trying to do?

i will select a "stored"query from a table, only if another table/column exists.

atm i have separate files, which fails if the table/column not exists (which is ok)... but it will flood the logfiles and 20 files are not so easy to overlook.

 

[Nutty]

Seems like an EXISTS clause would be all you need.

 

[meRlinX_AT]

Seems like an EXISTS clause would be all you need.

nope ... the whole query (and further the whole file) fails, if you check an non-existent table/column

even when you use something like this

Spoiler :

SELECT AnC_Flavor2Yields.FT AS FT, Building_BuildingClassHappiness.BuildingType AS BT, Building_BuildingClassHappiness.Happiness
*(SELECT sum(AnC_AllBU.Weight) /count(AnC_AllBU.BC) FROM AnC_AllBU WHERE AnC_AllBU.BC = Building_BuildingClassHappiness.BuildingClassType)
*AnC_Flavor2Yields.Flavor AS Flavor, 'Building_BuildingClassHappiness.Happiness' AS FromTable, 'GnK' AS Mod
FROM AnC_Flavor2Yields, Building_BuildingClassHappiness
WHERE 1 = (SELECT count(*) FROM sqlite_master WHERE type='table' AND name='Building_BuildingClassHappiness') -- GnK
AND /* we can also have negative Yields */ Building_BuildingClassHappiness.Happiness <> 0 AND AnC_Flavor2Yields.YT = 'BuildingClassHappiness'
GROUP BY Building_BuildingClassHappiness.BuildingType, Building_BuildingClassHappiness.BuildingClassType

 

[PawelS]

I just learned something about SQL.

If I want to select all IDs of units that aren't Great People, this:

SELECT Units.ID FROM Units WHERE Special != 'SPECIALUNIT_PEOPLE';

Won't work as I expected - it won't select the units that don't have anything in the Special column.

Instead, I should use the following:

SELECT Units.ID FROM Units WHERE Special != 'SPECIALUNIT_PEOPLE' OR Special IS NULL;