IF statements and SQL mods

[es4]

So I'm trying to add some tech-based bonuses to some improvements using SQL (which is much better than XML):

-- In the renaissance, improvements get a boost, particularly GP ones.
INSERT INTO Improvement_TechYieldChanges VALUES ("IMPROVEMENT_ACADEMY", "TECH_SCIENTIFIC_THEORY", "YIELD_SCIENCE", 3);
INSERT INTO Improvement_TechYieldChanges VALUES ("IMPROVEMENT_CUSTOMS_HOUSE", "TECH_ECONOMICS", "YIELD_GOLD", 3);
INSERT INTO Improvement_TechYieldChanges VALUES ("IMPROVEMENT_MANUFACTORY", "TECH_METALLURGY", "YIELD_PRODUCTION", 2);
INSERT INTO Improvement_TechYieldChanges VALUES ("IMPROVEMENT_CITADEL", "TECH_MILITARY_SCIENCE", "YIELD_GOLD", 3);
-- We can't add culture, but gold makes some sense.
INSERT INTO Improvement_TechYieldChanges VALUES ("IMPROVEMENT_LANDMARK", "TECH_ACOUSTICS", "YIELD_GOLD", 3);
INSERT INTO Improvement_TechYieldChanges VALUES ("IMPROVEMENT_PLANTATION", "TECH_BANKING", "YIELD_GOLD", 2);
INSERT INTO Improvement_TechYieldChanges VALUES ("IMPROVEMENT_TRADING_POST", "TECH_PRINTING_PRESS", "YIELD_GOLD", 1);
INSERT INTO Improvement_TechYieldChanges VALUES ("IMPROVEMENT_MINE", "TECH_DYNAMITE", "YIELD_PRODUCTION", 1);
INSERT INTO Improvement_TechYieldChanges VALUES ("IMPROVEMENT_CAMP", "TECH_GUNPOWDER", "YIELD_GOLD", 2);

That's working fine. However, if a different mod is already adding something like this, I wouldn't want my mod to do it as well. So, what I want to surround this statement with is something like:

IF (DB.Query(SELECT COUNT(*) FROM Improvements_TechYieldChanges) < 2) THEN
...
ENDIF


However, I can't get that (or anything else I've tried) working. There's no real standard I can find for SQL IF statements, and none of the combinations I am trying work.

Does anyone know how this works?

 

[Grumalg]

The base core of ANSI SQL does not include any 'Procedural' extensions. Many database engines do support such things, but SQLite doesn't. Nor does it support all of the basic ANSI SQL structure.

It supports executing a sequence of semicolon seperated SQL statements, but not any conditional execution of some of them.

You can find the full documentation here:
http://www.sqlite.org/docs.html

But the below parts will probably be the most useful to you...
http://www.sqlite.org/lang.html
http://www.sqlite.org/lang_corefunc.html
http://www.sqlite.org/lang_aggfunc.html

SQLite is a fairly strange beast. It supports declaring a field as a specific datatype such as integer, but it will cheerfully accept and write a string in an integer field and return the string back. They call this a 'Feature'... The docs say:

Most SQL database engines use static typing. A datatype is associated with each column in a table and only values of that particular datatype are allowed to be stored in that column. SQLite relaxes this restriction by using manifest typing. In manifest typing, the datatype is a property of the value itself, not of the column in which the value is stored. SQLite thus allows the user to store any value of any datatype into any column regardless of the declared type of that column. (There are some exceptions to this rule: An INTEGER PRIMARY KEY column may only store integers. And SQLite attempts to coerce values into the declared datatype of the column when it can.)

That can cause unpleasant issues if you read an 'integer' field into a integer variable and someone stuck a string there...

 

[es4]

The base core of ANSI SQL does not include any 'Procedural' extensions. Many database engines do support such things, but SQLite doesn't. Nor does it support all of the basic ANSI SQL structure.

So this is pure SQLite? I know that Kael's modding doc says to use the SQLite extension to read the DB files, but the FOR statement used there doesn't seem to be in SQLite, so I was hoping it was a variant with some conditional logic.

 

[Gedrin]

Google insert where not exists. Never used it myself and no time to try it. Heading out... But that may help you.