So it seems that a number of tables/columns dont properly update after being edited. I have made this article for us to discuss which tables are working and to try narrow in on the reason some dont seem to be updating correctly.
Notifications
5 comments
EDIT: NVM, found it. Thanks anyways for what you already provided.
[NAME]
ALT-XP
[TYPE]
Stats
[NOTES]
ALT-XP by Roosterdog
required exp to level up / 2
[ONENABLE]
UPDATE ExperienceLevels
SET PointsForNextLevel = (
SELECT PointsForNextLevel
FROM ExperienceLevels
WHERE ExperienceLevel = 39
)
+ 3500
WHERE ExperienceLevel = 40;
UPDATE ExperienceLevels
SET PointsForNextLevel = PointsForNextLevel / 2;
[ONDISABLE]
UPDATE ExperienceLevels
SET PointsForNextLevel = PointsForNextLevel * 2;
[QUERY]
SELECT PointsForNextLevel
FROM ExperienceLevels
[TRIGGERS]
OnGameBoot: true
OnModLoaded: false
OnLoadScreenEnd: false
OnPlayerCharLoaded: false
OnPlayerGearLoaded: false
OnPlayerTeleported: false
OnPlayerTakeDamage: false
OnPlayerSpellCast: false
[NAME]Alt Experience Actions (x2)
[TYPE]
Stats
[NOTES]
by Roosterdog
Changes how much experience is gained from various activities
[ONENABLE]
UPDATE ExperienceActions
SET ExperiencePoints = ExperiencePoints * 2;
[ONDISABLE]
UPDATE ExperienceActions
SET ExperiencePoints = ExperiencePoints / 2;
[QUERY]
SELECT ExperiencePoints
FROM ExperienceActions
[TRIGGERS]
OnGameBoot: true
OnModLoaded: false
OnLoadScreenEnd: false
OnPlayerCharLoaded: false
OnPlayerGearLoaded: false
OnPlayerTeleported: false
OnPlayerTakeDamage: false
OnPlayerSpellCast: false
[NAME]QoL Improvements - Beasts
[TYPE]
Items
[NOTES]
by Whracing
Aims to improve gameplay with some annoying timers and yields (Unbalanced)
[ONENABLE]
UPDATE CreatureDefinition
SET ByproductHarvestCount = (
SELECT ByproductHarvestCount
)
* 2
WHERE ByProduct IS NOT NULL;
[ONDISABLE]
UPDATE CreatureDefinition
SET ByproductHarvestCount = (
SELECT ByproductHarvestCount
)
/ 2
WHERE ByProduct IS NOT NULL;
[QUERY]
SELECT CreatureID, ByproductHarvestCount
FROM CreatureDefinition
WHERE ByProduct IS NOT NULL;
[TRIGGERS]
OnGameBoot: true
OnModLoaded: false
OnLoadScreenEnd: false
OnPlayerCharLoaded: false
OnPlayerGearLoaded: false
OnPlayerTeleported: false
OnPlayerTakeDamage: false
OnPlayerSpellCast: false
[NAME]QoL Improvements - Plants
[TYPE]
Items
[NOTES]
by Whracing
Aims to improve gameplay with some annoying timers and yields (Unbalanced)
[ONENABLE]
UPDATE PlantDefinition
SET RegrowthTimeSec = (
SELECT RegrowthTimeSec
)
/ 10, Yield_Planted =(
SELECT Yield_Planted
)
* 2
WHERE Plant_Product IS NOT NULL;
[ONDISABLE]
UPDATE PlantDefinition
SET RegrowthTimeSec = (
SELECT RegrowthTimeSec
)
* 10, Yield_Planted =(
SELECT Yield_Planted
)
/ 2
WHERE Plant_Product IS NOT NULL;
[QUERY]
SELECT PlantID, RegrowthTimeSec, Yield_Planted
FROM PlantDefinition
WHERE Plant_Product IS NOT NULL;
[TRIGGERS]
OnGameBoot: true
OnModLoaded: false
OnLoadScreenEnd: false
OnPlayerCharLoaded: false
OnPlayerGearLoaded: false
OnPlayerTeleported: false
OnPlayerTakeDamage: false
OnPlayerSpellCast: false
[NAME]QoL Improvements - Potions
[TYPE]
Items
[NOTES]
by Whracing
Aims to improve gameplay with some annoying timers and yields (Unbalanced)
[ONENABLE]
UPDATE PotionDefinition
SET TimeToBrewSec = (
SELECT TimeToBrewSec
)
/ 15, Yield =(
SELECT Yield
)
* 10
WHERE TimeToBrewSec IS NOT NULL;
[ONDISABLE]
UPDATE PotionDefinition
SET TimeToBrewSec = (
SELECT TimeToBrewSec
)
* 15, Yield =(
SELECT Yield
)
/ 10
WHERE TimeToBrewSec IS NOT NULL;
[QUERY]
SELECT PotionID, TimeToBrewSec, Yield
FROM PotionDefinition
WHERE TimeToBrewSec IS NOT NULL;
[TRIGGERS]
OnLoadScreenEnd: false
OnPlayerCharLoaded: false
OnPlayerGearLoaded: false
OnPlayerTeleported: false
OnPlayerTakeDamage: false
OnPlayerSpellCast: false
OnModLoaded: true
OnGameBoot: false
[NAME]Legendary Loot - Max
[TYPE]
Gear
[NOTES]
by Immusama
Adds legendary drop chance to normal humanoid enemies.
[ONENABLE]
INSERT OR REPLACE INTO LootContainerContents (
LootCategoryID,
ContainerID,
ContainerRandomWeight,
ContainerWeightAdjust,
ItemRollCount,
NPCRangeLow,
NPCRangeHigh,
RequiredUnlock,
Knowledge,
KnowledgeLevel,
GenerationType
)
VALUES (
'E_Humanoid',
'RONE_Gear_Back_Legendary',
1000,
0,
1,
1,
100,
NULL,
NULL,
NULL,
'Normal'
);
INSERT OR REPLACE INTO LootContainerContents (
LootCategoryID,
ContainerID,
ContainerRandomWeight,
ContainerWeightAdjust,
ItemRollCount,
NPCRangeLow,
NPCRangeHigh,
RequiredUnlock,
Knowledge,
KnowledgeLevel,
GenerationType
)
VALUES (
'E_Humanoid',
'RONE_Gear_Face_Legendary',
1000,
0,
1,
1,
100,
NULL,
NULL,
NULL,
'Normal'
);
INSERT OR REPLACE INTO LootContainerContents (
LootCategoryID,
ContainerID,
ContainerRandomWeight,
ContainerWeightAdjust,
ItemRollCount,
NPCRangeLow,
NPCRangeHigh,
RequiredUnlock,
Knowledge,
KnowledgeLevel,
GenerationType
)
VALUES (
'E_Humanoid',
'RONE_Gear_Hand_Legendary',
1000,
0,
1,
1,
100,
NULL,
NULL,
NULL,
'Normal'
);
INSERT OR REPLACE INTO LootContainerContents (
LootCategoryID,
ContainerID,
ContainerRandomWeight,
ContainerWeightAdjust,
ItemRollCount,
NPCRangeLow,
NPCRangeHigh,
RequiredUnlock,
Knowledge,
KnowledgeLevel,
GenerationType
)
VALUES (
'E_Humanoid',
'RONE_Gear_Head_Legendary',
1000,
0,
1,
1,
100,
NULL,
NULL,
NULL,
'Normal'
);
INSERT OR REPLACE INTO LootContainerContents (
LootCategoryID,
ContainerID,
ContainerRandomWeight,
ContainerWeightAdjust,
ItemRollCount,
NPCRangeLow,
NPCRangeHigh,
RequiredUnlock,
Knowledge,
KnowledgeLevel,
GenerationType
)
VALUES (
'E_Humanoid',
'RONE_Gear_Neck_Legendary',
1000,
0,
1,
1,
100,
NULL,
NULL,
NULL,
'Normal'
);
INSERT OR REPLACE INTO LootContainerContents (
LootCategoryID,
ContainerID,
ContainerRandomWeight,
ContainerWeightAdjust,
ItemRollCount,
NPCRangeLow,
NPCRangeHigh,
RequiredUnlock,
Knowledge,
KnowledgeLevel,
GenerationType
)
VALUES (
'E_Humanoid',
'RONE_Gear_Outfit_Legendary',
1000,
0,
1,
1,
100,
NULL,
NULL,
NULL,
'Normal'
);
[ONDISABLE]
DELETE FROM LootContainerContents
WHERE ContainerRandomWeight = 1000 AND ContainerID IN ('RONE_Gear_Back_Legendary','RONE_Gear_Face_Legendary','RONE_Gear_Hand_Legendary','RONE_Gear_Head_Legendary','RONE_Gear_Neck_Legendary','RONE_Gear_Outfit_Legendary');
[QUERY]
SELECT LootCategoryID, ContainerID
FROM LootContainerContents
WHERE ContainerRandomWeight = 1000 AND ContainerID IN ('RONE_Gear_Back_Legendary','RONE_Gear_Face_Legendary','RONE_Gear_Hand_Legendary','RONE_Gear_Head_Legendary','RONE_Gear_Neck_Legendary','RONE_Gear_Outfit_Legendary');
[TRIGGERS]
OnGameBoot: true
OnModLoaded: false
OnLoadScreenEnd: false
OnPlayerCharLoaded: false
OnPlayerGearLoaded: false
OnPlayerTeleported: false
OnPlayerTakeDamage: false
OnPlayerSpellCast: false
[NAME]No Cooldowns
[TYPE]
Spells
[NOTES]
by R0WB0T
This mod removes the cooldown time from all spells in the game, making them immediately available after casting. Have fun!
[ONENABLE]
CREATE TABLE IF NOT EXISTS SpellDefinition_SQLModBackup AS SELECT *
FROM SpellDefinition
WHERE SpellTypeID = 'Accio' OR
SpellTypeID = 'Accio_Charged' OR
SpellTypeID = 'Accio_Charged' OR
SpellTypeID = 'ArrestoMomentum' OR
SpellTypeID = 'ArrestoMomentum' OR
SpellTypeID = 'ArrestoMomentum' OR
SpellTypeID = 'ArrestoMomentum' OR
SpellTypeID = 'AvadaKedavra' OR
SpellTypeID = 'Confringo' OR
SpellTypeID = 'Confringo' OR
SpellTypeID = 'Crucio' OR
SpellTypeID = 'Depulso' OR
SpellTypeID = 'DepulsoDH' OR
SpellTypeID = 'Descendo' OR
SpellTypeID = 'DescendoDH' OR
SpellTypeID = 'DescendoDH' OR
SpellTypeID = 'DescendoDH' OR
SpellTypeID = 'Diffindo' OR
SpellTypeID = 'DiffindoDH' OR
SpellTypeID = 'Disillusionment' OR
SpellTypeID = 'Expelliarmus' OR
SpellTypeID = 'Expelliarmus' OR
SpellTypeID = 'Expulso' OR
SpellTypeID = 'ExpulsoDH' OR
SpellTypeID = 'ExpulsoDH' OR
SpellTypeID = 'Flipendo' OR
SpellTypeID = 'Glacius' OR
SpellTypeID = 'Glacius' OR
SpellTypeID = 'Glacius' OR
SpellTypeID = 'Imperius' OR
SpellTypeID = 'Incendio' OR
SpellTypeID = 'Incendio' OR
SpellTypeID = 'Incendio' OR
SpellTypeID = 'Incendio' OR
SpellTypeID = 'Levioso' OR
SpellTypeID = 'Lumos' OR
SpellTypeID = 'Reparo' OR
SpellTypeID = 'Transformation' OR
SpellTypeID = 'TransformationOverland' OR
SpellTypeID = 'Wingardium';
UPDATE SpellDefinition
SET CooldownTime = 0.1
WHERE SpellTypeID IN (
SELECT SpellTypeID
FROM SpellDefinition_SQLModBackup
);
[ONDISABLE]
UPDATE SpellDefinition
SET CooldownTime = (
SELECT CooldownTime
FROM SpellDefinition_SQLModBackup
)
WHERE SpellTypeID IN (
SELECT SpellTypeID
FROM SpellDefinition_SQLModBackup
);
[QUERY]
SELECT SpellTypeID, CooldownTime
FROM SpellDefinition
WHERE SpellTypeID IN (
SELECT SpellTypeID
FROM SpellDefinition_SQLModBackup
);
[TRIGGERS]
OnGameBoot: true
OnModLoaded: false
OnLoadScreenEnd: false
OnPlayerCharLoaded: false
OnPlayerGearLoaded: false
OnPlayerTeleported: false
OnPlayerTakeDamage: false
OnPlayerSpellCast: false
[NAME]Revelio Maxima
[TYPE]
Spells
[NOTES]
by Whracing
Increased range of Revelio
[ONENABLE]
UPDATE SpellDefinition
SET Range = (
SELECT Range
)
* 2
WHERE SpellTypeID = 'Revelio';
[ONDISABLE]
UPDATE SpellDefinition
SET Range = (
SELECT Range
)
/ 2
WHERE SpellTypeID = 'Revelio';
[QUERY]
SELECT SpellTypeID,
Range,
DurationCharacter
FROM SpellDefinition
WHERE SpellTypeID = 'Revelio';
[TRIGGERS]
OnGameBoot: true
OnModLoaded: false
OnLoadScreenEnd: false
OnPlayerCharLoaded: false
OnPlayerGearLoaded: false
OnPlayerTeleported: false
OnPlayerTakeDamage: false
OnPlayerSpellCast: false
This seems to effect these additional tables: Registry, PotionDefinition, and RPGAbilityDurationDefinition
Issue seems to be with additions primarily but have seen issues with updates for PotionDefinition too.
TalentResetPotion and Recipe for it have a few dependencies such as icons, locality BIN file stored with SQL Database.
Dependency Files include:
zTalentResetPotionIcon_P.pak
zTalentResetPotionIcon_P.ucas
zTalentResetPotionIcon_P.utoc
(And a repackaged version of the Database to only include Phoenix\Content\Localization\WIN64\MAIN-enUS.bin and specific SQL entries)
As for the SQLite database it needs these entries prior to the game launching:
<ul>
<li>INSERT OR IGNORE INTO ItemDefinition (ItemID,ItemType,RarityTier,ItemLevel,SellPrice,Inventoryable,Persistent,Giftable,Sellable,Dropable,SlotLevel,ItemUsagetype,Consumable,TriggerAbilityOnConsume,UsableFromInventory,UIDisplayMethod,UsableOnDiamond,StorageLocation) VALUES ('TalentResetPotion','PotionUsable','Common',0,-1,1,0,1,1,1,'Small','Drink',1,1,1,'Minor',1,'MissionItems');</li>
<li>INSERT OR IGNORE INTO PotionDefinition (PotionID,PotionTypeID,LockID,UICategory,SortingIndex,Ingredient1,Quantity1,Ingredient2,Quantity2,Ingredient3,Quantity3,TimeToBrewSec,Yield) VALUES ('TalentResetPotion','energy','Potion_TalentResetPotion','Category_Adventuring',0,'HorklumpJuice',5,'Shrivelfig_Byproduct',5,'StenchOfTheDead',5,180,1);</li>
<li>INSERT OR IGNORE INTO RPGAbilityDurationDefinition (AbilityID,Duration,Units) VALUES ('TalentResetPotion',0.0,'Seconds');</li>
<li>INSERT OR IGNORE INTO Registry (RegistryID,SubtypeID) VALUES ('TalentResetPotion','Potion');</li>
</ul>
These entries work fine from the SQL Mod Menu as they are accessible on load:
<ul>
<li>INSERT OR IGNORE INTO LootContainerContents (LootCategoryID,ContainerID,ContainerRandomWeight,ContainerWeightAdjust,ItemRollCount,NPCRangeLow,NPCRangeHigh,GenerationType) VALUES ('VEND_PercivalPippin','ITEM_Recipe_TalentResetPotion',0,0,1,1,100,'Normal');</li>
<li>UPDATE ItemDefinition SET ItemType='Recipe_Potion', EconomyValue=3000 WHERE ItemID='Recipe_TalentResetPotion';</li>
</ul>
From Testing I even duplicated the table then dropped the original table to clear all items.
As a next step I then recreated the original table using the duplicate which shows fine in the debug view.
Once a game is loaded all items from your inventory and vendors completely disappears.
These are some additional operations I used to do further testing with the table:
CREATE TABLE IF NOT EXISTS GameItemDefinition AS
SELECT
ItemID, ItemType, ItemType2, RarityTier, ItemLevel, EconomyValue, SellPrice, Inventoryable, Persistent, Giftable, Sellable, Dropable, SlotLevel, ItemUsagetype, ItemUsageObjectID, Consumable, TriggerAbilityOnConsume, LockID, OnUseLockID, PrerequisiteLockID, PrerequisiteLockID2, UsableFromInventory, UIDisplayMethod, UsableOnDiamond, StorageLocation
FROM ItemDefinition;
DROP TABLE ItemDefinition;
REATE TABLE IF NOT EXISTS ItemDefinition AS
SELECT
ItemID, ItemType, ItemType2, RarityTier, ItemLevel, EconomyValue, SellPrice, Inventoryable, Persistent, Giftable, Sellable, Dropable, SlotLevel, ItemUsagetype, ItemUsageObjectID, Consumable, TriggerAbilityOnConsume, LockID, OnUseLockID, PrerequisiteLockID, PrerequisiteLockID2, UsableFromInventory, UIDisplayMethod, UsableOnDiamond, StorageLocation
FROM GameItemDefinition;
As a next step, I even deleted the individual entries from ItemDefinition and then the Registry table too, which will cause the items to disappear. If re-added they will still not show.
DELETE FROM ItemDefinition
WHERE ItemID = 'TalentResetPotion';
DELETE FROM ItemDefinition
WHERE ItemID = 'Recipe_TalentResetPotion';
DELETE FROM Registry
WHERE RegistryID = 'TalentResetPotion';
DELETE FROM Registry
WHERE RegistryID = 'Recipe_TalentResetPotion';
Details:
Lists Names of Identifiable Tables from the Master List for SQLite
(This is not a full list of tables from what I have noticed)
Query:
SELECT tbl_name FROM SQLITE_MASTER WHERE type='table';
Details:
Lists Names of Identifiable Indexes (DynamicTables) from the Master List for SQLite
Query:
SELECT tbl_name FROM SQLITE_MASTER WHERE type='index';
Details:Lists all details for Identifiable Tables and Indexes(DynamicTables)
Query:
SELECT * FROM SQLITE_MASTER;
Details:
Returns all Columns and values for the Table(PlayerHealthLevelMap)
Query:
SELECT * FROM PlayerHealthLevelMap;
Details:
Returns a Count of Rows from the Table(PlayerHealthLevelMap) based on the Column(PlayerHealth)
Query:
SELECT COUNT(PlayerHealth) FROM PlayerHealthLevelMap;
Preview:
COUNT(PlayerHealth): 40
Details:
Returns all Columns and values for specific rows where the Column(LockID) equals the specified values for Alohomora
Query:
SELECT * FROM LockDefinition
WHERE LockID='Spell_Alohomora'
OR LockID='Spell_Alohomora_L2'
OR LockID='Spell_Alohomora_L3';
Preview:
LockID: Spell_Alohomora
LockTypeID: Simple_Locked
LockMsg: SpellLockChanged
LockID: Spell_Alohomora_L2
LockTypeID: Simple_Locked
LockMsg: SpellLockChanged
LockID: Spell_Alohomora_L3
LockTypeID: Simple_Locked
LockMsg: SpellLockChanged
To get more familiar with the Tables including ones not viewable via the SQLITE_MASTER then I would suggest downloading SQLite Studio and using it to browse an exported clean copy of the PhoenixShipData.sqlite.
Some key tables to look at for starters would be these Tables:
PlayerHealthLevelMap, GearPowerStats, GearPowerLevels, ItemDefinition, LockDefinition
Also to note, SQLITE_TEMP_MASTER is another table structure but is empty currently so may have content loaded later on in the game.
SQLite Studio shows a total of 528 Tables where SQLITE_MASTER only has 167 viewable from.
These are some additional Queries based on Views from the loaded database in SQLite Sudio:
(These do work as Queries in the SQL Mod Menu)
Details:Lists the Students Names based on the Table(Registry) and lists them in order by their RegistryID number
Query:
SELECT RegistryID FROM Registry WHERE SubTypeID = 'Student' Order By RegistryID
Details:
Joins 2 Tables (PotionDefinition and PlantDefinition) to list all potions that have a plant as an ingredient
Query:
SELECT PotionID FROM PotionDefinition JOIN PlantDefinition ON Ingredient1 = Plant_Product OR Ingredient2 = Plant_Product OR Ingredient3 = Plant_Product OR Ingredient4 = Plant_Product OR Ingredient5 = Plant_Product OR NULL = Plant_Product OR NULL = Plant_Product OR NULL = Plant_Product
Preview:
PotionID: AMFillPotion
PotionID: AutoDamagePotion
PotionID: FelixFelicis
PotionID: InvisibilityPotion
PotionID: TalentResetPotion
PotionID: WoundCleaning