Simple SQL question

[Thalassicus]

What are these math functions in the implementation of SQL for CiV?

• Power
• Log

I've tried various versions I know of from some implementations I've used -- POW(x,y), POWER(x,y), x ** y, x ^ y -- but none seemed to be right. (Though it's really difficult to tell what the problem is since Civ V doesn't appear to give any error messages, it just ignores your mod if the sql statements are incorrect.)

 

[ArgentumStudios]

It is a SQLite implementation, which doesn't include pow() or log() in its standard syntax, so both are either part of a custom implementation by Firaxis or they used a 3rd party extension to add them.

 

[Thalassicus]

Have they released a reference to what formulas they've implimented? It's odd they don't use any of the sql standards... though the industry is really bad about using standards in this realm. It's always so cumbersome to have to code multiple DAO's for each vendor.

I ended up just manually entering a table of values, though it would help to be able to use a simple formula, if you have any ideas.

 

[ArgentumStudios]

Nope, nothing has been documented as far as I know. Sorry.

 

[Thalassicus]

Ah, the wonders of company-specific sql!

Based on more attempts to do various math functions it seems they might not exist. Hopefully if these functions were left out in their implementation of SQLite it'll be simple enough to add them. I haven't used Lite -- primarily db2, oracle or sql server -- but from the reading I've done it seems there's ways to hook in new things into Lite through C?

Hopefully once we get that part of the SDK! Writing out reference tables or such is a pain.

Thank you for the information.

 

[ArgentumStudios]

I'm guessing that they have very little custom implementation done at all and are using pretty much 'standard' SQLite, which would explain why pow() and log() are missing. They are available in Lua, so if you are mixing lua and sql you might be able to handle things in lua before inserting them into the database.

I'm looking forward to the day when we have docs too.

 

[Thalassicus]

Hmm, I haven't delved into the lua parts yet. It's not a language I'm familiar with, though I have a good tutor - brother's been doing half-life 2 modding and gamemode design for years now.

Specifically here's what I need:

UPDATE Technologies
SET 'Cost' = (Cost - 35) ^ 1.022 + 35
WHERE Cost > 0;

How difficult would it be to build a Lua hook to accomplish this with SQL, or even in Lua alone? The log function is not strictly necessary, if the power function can handle fractional exponents and a logarithmic conversion isn't required.

 

[ArgentumStudios]

I haven't tested this and it looks ugly to me, but you should be able to do:

for cost in DB.Query( "SELECT Cost FROM Technologies WHERE Cost > 34" ) do
    for key,value in pairs(cost) do
        DB.Query( "UPDATE Technologies SET 'Cost' = " .. math.floor( ( value - 35 )^1.022 ) + 35 .. " WHERE Cost = " .. value .. ";" );
    end;
end;

By the way, I changed it to only select those with costs greater than 34, because there is one record with a cost of 20, which resulted in a cost of -1.#IND

 

[Thalassicus]

Thank you, I'll try that.

 

[eviltypeguy]

What are these math functions in the implementation of SQL for CiV?

• Power
• Log

I've tried various versions I know of from some implementations I've used -- POW(x,y), POWER(x,y), x ** y, x ^ y -- but none seemed to be right. (Though it's really difficult to tell what the problem is since Civ V doesn't appear to give any error messages, it just ignores your mod if the sql statements are incorrect.)

For future reference, you can see the list of core sqlite functions here:
http://www.sqlite.org/lang_corefunc.html