Trouble with SQL

[bouncymischa]

For the newest version of Future Worlds, I have an improvement that can potentially replace a Farm or Plantation, so I'd like it to be able to improve all resources that those two improvements can be built on and duplicate their bonus yields. However, since I often use resource-modifying mods like Barathor's More Luxuries, I'd like to do this via SQL rather than simply hard code them in XML. I thought I'd figured out how to do it, but when I start up the mod, the improvement doesn't seem to be able to improve the resources as I'd hoped.

This is the code I'm currently using:

INSERT INTO Improvement_ResourceTypes
            (ImprovementType,                         ResourceType)
SELECT        ('IMPROVEMENT_FW_FUNGAL_GROWTH'),        ResourceType
FROM Improvement_ResourceTypes WHERE (Type = 'IMPROVEMENT_FARM');

INSERT INTO Improvement_ResourceTypes
            (ImprovementType,                         ResourceType)
SELECT        ('IMPROVEMENT_FW_FUNGAL_GROWTH'),        ResourceType
FROM Improvement_ResourceTypes WHERE (Type = 'IMPROVEMENT_PLANTATION');

INSERT INTO Improvement_ResourceType_Yields
            (ImprovementType,                         ResourceType,    YieldType,    Yield)
SELECT        ('IMPROVEMENT_FW_FUNGAL_GROWTH'),        ResourceType,    YieldType,    Yield
FROM Improvement_ResourceType_Yields WHERE (Type = 'IMPROVEMENT_FARM');

INSERT INTO Improvement_ResourceType_Yields
            (ImprovementType,                         ResourceType,    YieldType,    Yield)
SELECT        ('IMPROVEMENT_FW_FUNGAL_GROWTH'),        ResourceType,    YieldType,    Yield
FROM Improvement_ResourceType_Yields WHERE (Type = 'IMPROVEMENT_PLANTATION');

EDIT: I found the mistake -- I was using WHERE (Type = 'IMPROVEMENT_FARM') instead of WHERE (ImprovementType = 'IMPROVEMENT_FARM')! Ahhh, such simple errors...

 

[Chrisy15]

Ftr, you can make it more efficicent:

INSERT INTO Improvement_ResourceTypes
            (ImprovementType,                         ResourceType)
SELECT        'IMPROVEMENT_FW_FUNGAL_GROWTH',        ResourceType
FROM Improvement_ResourceTypes WHERE ImprovementType IN ('IMPROVEMENT_FARM', 'IMPROVEMENT_PLANTATION';

I'd also recommend a trigger for any future resources that are added:

CREATE TRIGGER UniqueName 
AFTER INSERT ON Improvement_ResourceTypes
WHEN NEW.ImprovementType IN ('IMPROVEMENT_FARM', 'IMPROVEMENT_PLANTATION')
BEGIN 
INSERT INTO Improvement_ResourceTypes
(ImprovementType, ResourceType) 
VALUES ('IMPROVEMENT_FW_FUNGAL_GROWTH', NEW.ResourceType);
END;

You can do both of these things for the second table too.

Good luck with the FW update

 

[bouncymischa]

Okay... I decided to try and buff the Genocentre a little by having it improve resources similar to the Fungal Growth, but... for some reason, it's not working!

INSERT INTO Improvement_ResourceTypes
            (ImprovementType,                         ResourceType)
SELECT        'IMPROVEMENT_FW_FUNGAL_GROWTH',        ResourceType
FROM Improvement_ResourceTypes WHERE ImprovementType IN ('IMPROVEMENT_FARM', 'IMPROVEMENT_PLANTATION');

INSERT INTO Improvement_ResourceType_Yields 
            (ImprovementType,                         ResourceType,    YieldType,    Yield)
SELECT        ('IMPROVEMENT_FW_FUNGAL_GROWTH'),        ResourceType,    YieldType,    Yield
FROM Improvement_ResourceType_Yields WHERE ImprovementType IN ('IMPROVEMENT_FARM', 'IMPROVEMENT_PLANTATION')

INSERT INTO Improvement_ResourceTypes
            (ImprovementType,                         ResourceType)
SELECT        'IMPROVEMENT_FW_GENOCENTRE',        ResourceType
FROM Improvement_ResourceTypes WHERE ImprovementType IN ('IMPROVEMENT_FARM', 'IMPROVEMENT_PASTURE');

INSERT INTO Improvement_ResourceType_Yields 
            (ImprovementType,                         ResourceType,    YieldType,    Yield)
SELECT        ('IMPROVEMENT_FW_GENOCENTRE'),        ResourceType,    YieldType,    Yield
FROM Improvement_ResourceType_Yields WHERE ImprovementType IN ('IMPROVEMENT_FARM', 'IMPROVEMENT_PASTURE')

The Fungal Growth shows the resources it modifies, but the Genocentre doesn't... o.o;;

 

[LeeS]

two syntax errors: you are missing ; at the end of the second and the fourth INSERT block. So only the 1st INSERT block is actually executed into the database.