mmo-ref/eluna-scripts-ornfelt
1
0
Fork 0
mirror of https://github.com/ornfelt/azerothcore_lua_scripts synced 2026-08-22 06:26:03 -04:00
Code Issues Projects Releases Packages Wiki Activity Actions
eluna-scripts-ornfelt/lua/Eluna-scripts/AIO Scripts/GameMasterUI/Server/Database/GameMasterUI_Database.lua
Jonas Ornfelt eb96b65ca6 update code
2026-02-24 16:53:17 +01:00

836 lines
36 KiB
Lua
Raw Permalink Blame History

local DatabaseHelper = require("GameMasterUI.Server.Core.GameMasterUI_DatabaseHelper")
local queries = {
TrinityCore = {
loadCreatureDisplays = function()
return [[
SELECT `entry`, `name`, `subname`, `IconName`, `type_flags`, `type`, `family`, `rank`, `KillCredit1`, `KillCredit2`, `HealthModifier`, `ManaModifier`, `RacialLeader`, `MovementType`, `modelId1`, `modelId2`, `modelId3`, `modelId4`
FROM `creature_template`
]]
end,
loadItemForPacket = function(itemEntry)
return string.format(
[[SELECT entry, class, subclass, name, displayid, Quality, Flags, FlagsExtra,
BuyPrice, SellPrice, InventoryType, AllowableClass, AllowableRace,
ItemLevel, RequiredLevel, RequiredSkill, RequiredSkillRank,
requiredspell, requiredhonorrank, RequiredCityRank,
RequiredReputationFaction, RequiredReputationRank,
maxcount, stackable, ContainerSlots,
stat_type1, stat_value1, stat_type2, stat_value2,
stat_type3, stat_value3, stat_type4, stat_value4,
stat_type5, stat_value5, stat_type6, stat_value6,
stat_type7, stat_value7, stat_type8, stat_value8,
stat_type9, stat_value9, stat_type10, stat_value10,
ScalingStatDistribution, ScalingStatValue,
dmg_min1, dmg_max1, dmg_type1, dmg_min2, dmg_max2, dmg_type2,
armor, holy_res, fire_res, nature_res, frost_res, shadow_res, arcane_res,
delay, ammo_type, RangedModRange,
spellid_1, spelltrigger_1, spellcharges_1, spellppmRate_1, spellcooldown_1, spellcategory_1, spellcategorycooldown_1,
spellid_2, spelltrigger_2, spellcharges_2, spellppmRate_2, spellcooldown_2, spellcategory_2, spellcategorycooldown_2,
spellid_3, spelltrigger_3, spellcharges_3, spellppmRate_3, spellcooldown_3, spellcategory_3, spellcategorycooldown_3,
spellid_4, spelltrigger_4, spellcharges_4, spellppmRate_4, spellcooldown_4, spellcategory_4, spellcategorycooldown_4,
spellid_5, spelltrigger_5, spellcharges_5, spellppmRate_5, spellcooldown_5, spellcategory_5, spellcategorycooldown_5,
bonding, COALESCE(description, '') as description, PageText, LanguageID, PageMaterial,
startquest, lockid, Material, sheath, RandomProperty, RandomSuffix,
block, itemset, MaxDurability, area, Map, BagFamily, TotemCategory,
socketColor_1, socketContent_1, socketColor_2, socketContent_2, socketColor_3, socketContent_3,
socketBonus, GemProperties, RequiredDisenchantSkill, ArmorDamageModifier,
duration, ItemLimitCategory, HolidayId
FROM item_template WHERE entry = %d;]],
itemEntry
)
end,
npcData = function(sortOrder, pageSize, offset)
return string.format(
[[
SELECT entry, modelid1, modelid2, modelid3, modelid4, name, subname, type
FROM creature_template
WHERE modelid1 != 0 OR modelid2 != 0 OR modelid3 != 0 OR modelid4 != 0
ORDER BY entry %s
LIMIT %d OFFSET %d;
]],
sortOrder,
pageSize,
offset
)
end,
npcCount = function()
return [[
SELECT COUNT(*)
FROM creature_template
WHERE modelid1 != 0 OR modelid2 != 0 OR modelid3 != 0 OR modelid4 != 0;
]]
end,
gobData = function(sortOrder, pageSize, offset)
-- Check if gameobjectdisplayinfo table exists
local hasDisplayInfo = DatabaseHelper.IsOptionalTableAvailable("gameobjectdisplayinfo", "world")
if hasDisplayInfo then
return string.format(
[[
SELECT g.entry, g.displayid, g.name, m.ModelName
FROM gameobject_template g
LEFT JOIN gameobjectdisplayinfo m ON g.displayid = m.ID
ORDER BY g.entry %s
LIMIT %d OFFSET %d;
]],
sortOrder,
pageSize,
offset
)
else
return string.format(
[[
SELECT g.entry, g.displayid, g.name, 'N/A' as ModelName
FROM gameobject_template g
ORDER BY g.entry %s
LIMIT %d OFFSET %d;
]],
sortOrder,
pageSize,
offset
)
end
end,
gobCount = function()
-- Use simple count without join to avoid issues
return [[
SELECT COUNT(*)
FROM gameobject_template;
]]
end,
spellCount = function()
return [[
SELECT COUNT(*)
FROM spell;
]]
end,
spellData = function(sortOrder, pageSize, offset)
return string.format(
[[
SELECT s.id, s.spellName0, s.spellDescription0, s.spellToolTip0, s.spellVisual1, s.spellVisual2,
s.EffectMiscValue1, s.EffectMiscValue2, s.EffectMiscValue3,
s.Effect1, s.Effect2, s.Effect3, s.schoolMask,
COALESCE(
cast1_base.FilePath, cast1_world.FilePath, cast1_special.FilePath,
impact1_base.FilePath, impact1_world.FilePath, impact1_special.FilePath,
state1_base.FilePath, state1_world.FilePath, state1_special.FilePath,
''
) as visualFilePath1,
COALESCE(
cast2_base.FilePath, cast2_world.FilePath, cast2_special.FilePath,
impact2_base.FilePath, impact2_world.FilePath, impact2_special.FilePath,
state2_base.FilePath, state2_world.FilePath, state2_special.FilePath,
''
) as visualFilePath2
FROM spell s
LEFT JOIN spellvisual sv1 ON s.SpellVisual1 = sv1.ID
LEFT JOIN spellvisualkit cast1 ON sv1.CastKit = cast1.ID
LEFT JOIN spellvisualeffectname cast1_base ON cast1.BaseEffect = cast1_base.ID
LEFT JOIN spellvisualeffectname cast1_world ON cast1.WorldEffect = cast1_world.ID
LEFT JOIN spellvisualeffectname cast1_special ON cast1.SpecialEffect1 = cast1_special.ID
LEFT JOIN spellvisualkit impact1 ON sv1.ImpactKit = impact1.ID
LEFT JOIN spellvisualeffectname impact1_base ON impact1.BaseEffect = impact1_base.ID
LEFT JOIN spellvisualeffectname impact1_world ON impact1.WorldEffect = impact1_world.ID
LEFT JOIN spellvisualeffectname impact1_special ON impact1.SpecialEffect1 = impact1_special.ID
LEFT JOIN spellvisualkit state1 ON sv1.StateKit = state1.ID
LEFT JOIN spellvisualeffectname state1_base ON state1.BaseEffect = state1_base.ID
LEFT JOIN spellvisualeffectname state1_world ON state1.WorldEffect = state1_world.ID
LEFT JOIN spellvisualeffectname state1_special ON state1.SpecialEffect1 = state1_special.ID
LEFT JOIN spellvisual sv2 ON s.SpellVisual2 = sv2.ID
LEFT JOIN spellvisualkit cast2 ON sv2.CastKit = cast2.ID
LEFT JOIN spellvisualeffectname cast2_base ON cast2.BaseEffect = cast2_base.ID
LEFT JOIN spellvisualeffectname cast2_world ON cast2.WorldEffect = cast2_world.ID
LEFT JOIN spellvisualeffectname cast2_special ON cast2.SpecialEffect1 = cast2_special.ID
LEFT JOIN spellvisualkit impact2 ON sv2.ImpactKit = impact2.ID
LEFT JOIN spellvisualeffectname impact2_base ON impact2.BaseEffect = impact2_base.ID
LEFT JOIN spellvisualeffectname impact2_world ON impact2.WorldEffect = impact2_world.ID
LEFT JOIN spellvisualeffectname impact2_special ON impact2.SpecialEffect1 = impact2_special.ID
LEFT JOIN spellvisualkit state2 ON sv2.StateKit = state2.ID
LEFT JOIN spellvisualeffectname state2_base ON state2.BaseEffect = state2_base.ID
LEFT JOIN spellvisualeffectname state2_world ON state2.WorldEffect = state2_world.ID
LEFT JOIN spellvisualeffectname state2_special ON state2.SpecialEffect1 = state2_special.ID
ORDER BY s.id %s
LIMIT %d OFFSET %d;
]],
sortOrder,
pageSize,
offset
)
end,
searchNpcData = function(query, typeId, sortOrder, pageSize, offset)
return string.format(
[[
SELECT entry, modelid1, modelid2, modelid3, modelid4, name, subname, type
FROM creature_template
WHERE name LIKE '%%%s%%' OR subname LIKE '%%%s%%' OR entry LIKE '%%%s%%' %s
ORDER BY entry %s
LIMIT %d OFFSET %d;
]],
query,
query,
query,
typeId and string.format("OR type = %d", typeId) or "",
sortOrder,
pageSize,
offset * pageSize
)
end,
searchGobData = function(query, typeId, sortOrder, pageSize, offset)
-- Check if gameobjectdisplayinfo table exists
local hasDisplayInfo = DatabaseHelper.IsOptionalTableAvailable("gameobjectdisplayinfo", "world")
if hasDisplayInfo then
return string.format(
[[
SELECT g.entry, g.displayid, g.name, g.type, m.ModelName
FROM gameobject_template g
LEFT JOIN gameobjectdisplayinfo m ON g.displayid = m.ID
WHERE g.name LIKE '%%%s%%' OR g.entry LIKE '%%%s%%' %s
ORDER BY g.entry %s
LIMIT %d OFFSET %d;
]],
query,
query,
typeId and string.format("OR g.type = %d", typeId) or "",
sortOrder,
pageSize,
offset * pageSize
)
else
return string.format(
[[
SELECT g.entry, g.displayid, g.name, g.type, 'N/A' as ModelName
FROM gameobject_template g
WHERE g.name LIKE '%%%s%%' OR g.entry LIKE '%%%s%%' %s
ORDER BY g.entry %s
LIMIT %d OFFSET %d;
]],
query,
query,
typeId and string.format("OR g.type = %d", typeId) or "",
sortOrder,
pageSize,
offset * pageSize
)
end
end,
searchSpellData = function(query, sortOrder, pageSize, offset)
return string.format(
[[
SELECT s.id, s.spellName0, s.spellDescription0, s.spellToolTip0, s.spellVisual1, s.spellVisual2,
s.EffectMiscValue1, s.EffectMiscValue2, s.EffectMiscValue3,
s.Effect1, s.Effect2, s.Effect3, s.schoolMask,
COALESCE(
cast1_base.FilePath, cast1_world.FilePath, cast1_special.FilePath,
impact1_base.FilePath, impact1_world.FilePath, impact1_special.FilePath,
state1_base.FilePath, state1_world.FilePath, state1_special.FilePath,
''
) as visualFilePath1,
COALESCE(
cast2_base.FilePath, cast2_world.FilePath, cast2_special.FilePath,
impact2_base.FilePath, impact2_world.FilePath, impact2_special.FilePath,
state2_base.FilePath, state2_world.FilePath, state2_special.FilePath,
''
) as visualFilePath2
FROM spell s
LEFT JOIN spellvisual sv1 ON s.SpellVisual1 = sv1.ID
LEFT JOIN spellvisualkit cast1 ON sv1.CastKit = cast1.ID
LEFT JOIN spellvisualeffectname cast1_base ON cast1.BaseEffect = cast1_base.ID
LEFT JOIN spellvisualeffectname cast1_world ON cast1.WorldEffect = cast1_world.ID
LEFT JOIN spellvisualeffectname cast1_special ON cast1.SpecialEffect1 = cast1_special.ID
LEFT JOIN spellvisualkit impact1 ON sv1.ImpactKit = impact1.ID
LEFT JOIN spellvisualeffectname impact1_base ON impact1.BaseEffect = impact1_base.ID
LEFT JOIN spellvisualeffectname impact1_world ON impact1.WorldEffect = impact1_world.ID
LEFT JOIN spellvisualeffectname impact1_special ON impact1.SpecialEffect1 = impact1_special.ID
LEFT JOIN spellvisualkit state1 ON sv1.StateKit = state1.ID
LEFT JOIN spellvisualeffectname state1_base ON state1.BaseEffect = state1_base.ID
LEFT JOIN spellvisualeffectname state1_world ON state1.WorldEffect = state1_world.ID
LEFT JOIN spellvisualeffectname state1_special ON state1.SpecialEffect1 = state1_special.ID
LEFT JOIN spellvisual sv2 ON s.SpellVisual2 = sv2.ID
LEFT JOIN spellvisualkit cast2 ON sv2.CastKit = cast2.ID
LEFT JOIN spellvisualeffectname cast2_base ON cast2.BaseEffect = cast2_base.ID
LEFT JOIN spellvisualeffectname cast2_world ON cast2.WorldEffect = cast2_world.ID
LEFT JOIN spellvisualeffectname cast2_special ON cast2.SpecialEffect1 = cast2_special.ID
LEFT JOIN spellvisualkit impact2 ON sv2.ImpactKit = impact2.ID
LEFT JOIN spellvisualeffectname impact2_base ON impact2.BaseEffect = impact2_base.ID
LEFT JOIN spellvisualeffectname impact2_world ON impact2.WorldEffect = impact2_world.ID
LEFT JOIN spellvisualeffectname impact2_special ON impact2.SpecialEffect1 = impact2_special.ID
LEFT JOIN spellvisualkit state2 ON sv2.StateKit = state2.ID
LEFT JOIN spellvisualeffectname state2_base ON state2.BaseEffect = state2_base.ID
LEFT JOIN spellvisualeffectname state2_world ON state2.WorldEffect = state2_world.ID
LEFT JOIN spellvisualeffectname state2_special ON state2.SpecialEffect1 = state2_special.ID
WHERE s.spellName0 LIKE '%%%s%%' OR s.id LIKE '%%%s%%'
ORDER BY s.id %s
LIMIT %d OFFSET %d;
]],
query,
query,
sortOrder,
pageSize,
offset * pageSize
)
end,
spellVisualCount = function()
return [[
SELECT COUNT(*)
FROM spellvisualeffectname;
]]
end,
spellVisualData = function(sortOrder, pageSize, offset)
return string.format(
[[
SELECT ID, Name, FilePath, AreaEffectSize, Scale, MinAllowedScale, MaxAllowedScale
FROM spellvisualeffectname
ORDER BY ID %s
LIMIT %d OFFSET %d;
]],
sortOrder,
pageSize,
offset
)
end,
searchSpellVisualData = function(query, sortOrder, pageSize, offset)
return string.format(
[[
SELECT ID, Name, FilePath, AreaEffectSize, Scale, MinAllowedScale, MaxAllowedScale
FROM spellvisualeffectname
WHERE Name LIKE '%%%s%%' OR ID LIKE '%%%s%%'
ORDER BY ID %s
LIMIT %d OFFSET %d;
]],
query,
query,
sortOrder,
pageSize,
offset * pageSize
)
end,
itemCount = function(inventoryType)
if inventoryType and inventoryType >= 0 then
return string.format([[
SELECT COUNT(*)
FROM item_template
WHERE InventoryType = %d;
]], inventoryType)
else
return [[
SELECT COUNT(*)
FROM item_template;
]]
end
end,
itemData = function(sortOrder, pageSize, offset, inventoryType)
local whereClause = ""
if inventoryType then
whereClause = string.format("WHERE InventoryType = %d", inventoryType)
end
return string.format(
[[SELECT entry, name, COALESCE(description, ''), displayid, Quality, InventoryType, ItemLevel, class, subclass
FROM item_template
%s
ORDER BY entry %s
LIMIT %d OFFSET %d;]],
whereClause,
sortOrder,
pageSize,
offset
)
end,
searchItemData = function(query, sortOrder, pageSize, offset, inventoryType)
local whereClause = [[WHERE (name LIKE '%%%s%%' OR entry LIKE '%%%s%%')]]
if inventoryType then
whereClause = whereClause .. string.format(" AND InventoryType = %d", inventoryType)
end
return string.format(
[[SELECT entry, name, COALESCE(description, ''), displayid, Quality, InventoryType, ItemLevel, class, subclass
FROM item_template
%s
ORDER BY entry %s
LIMIT %d OFFSET %d;]],
string.format(whereClause, query, query),
sortOrder,
pageSize,
offset
)
end,
},
AzerothCore = {
loadCreatureDisplays = function()
return [[
SELECT ct.`entry`, ct.`name`, ct.`subname`, ct.`IconName`, ct.`type_flags`, ct.`type`, ct.`family`, ct.`rank`, ct.`KillCredit1`, ct.`KillCredit2`, ct.`HealthModifier`, ct.`ManaModifier`, ct.`RacialLeader`, ct.`MovementType`, ctm.`CreatureDisplayID`
FROM `creature_template` ct
LEFT JOIN `creature_template_model` ctm ON ct.`entry` = ctm.`CreatureID`
]]
end,
loadItemForPacket = function(itemEntry)
return string.format(
[[SELECT entry, class, subclass, name, displayid, Quality, Flags, FlagsExtra,
BuyPrice, SellPrice, InventoryType, AllowableClass, AllowableRace,
ItemLevel, RequiredLevel, RequiredSkill, RequiredSkillRank,
requiredspell, requiredhonorrank, RequiredCityRank,
RequiredReputationFaction, RequiredReputationRank,
maxcount, stackable, ContainerSlots,
stat_type1, stat_value1, stat_type2, stat_value2,
stat_type3, stat_value3, stat_type4, stat_value4,
stat_type5, stat_value5, stat_type6, stat_value6,
stat_type7, stat_value7, stat_type8, stat_value8,
stat_type9, stat_value9, stat_type10, stat_value10,
ScalingStatDistribution, ScalingStatValue,
dmg_min1, dmg_max1, dmg_type1, dmg_min2, dmg_max2, dmg_type2,
armor, holy_res, fire_res, nature_res, frost_res, shadow_res, arcane_res,
delay, ammo_type, RangedModRange,
spellid_1, spelltrigger_1, spellcharges_1, spellppmRate_1, spellcooldown_1, spellcategory_1, spellcategorycooldown_1,
spellid_2, spelltrigger_2, spellcharges_2, spellppmRate_2, spellcooldown_2, spellcategory_2, spellcategorycooldown_2,
spellid_3, spelltrigger_3, spellcharges_3, spellppmRate_3, spellcooldown_3, spellcategory_3, spellcategorycooldown_3,
spellid_4, spelltrigger_4, spellcharges_4, spellppmRate_4, spellcooldown_4, spellcategory_4, spellcategorycooldown_4,
spellid_5, spelltrigger_5, spellcharges_5, spellppmRate_5, spellcooldown_5, spellcategory_5, spellcategorycooldown_5,
bonding, COALESCE(description, '') as description, PageText, LanguageID, PageMaterial,
startquest, lockid, Material, sheath, RandomProperty, RandomSuffix,
block, itemset, MaxDurability, area, Map, BagFamily, TotemCategory,
socketColor_1, socketContent_1, socketColor_2, socketContent_2, socketColor_3, socketContent_3,
socketBonus, GemProperties, RequiredDisenchantSkill, ArmorDamageModifier,
duration, ItemLimitCategory, HolidayId
FROM item_template WHERE entry = %d;]],
itemEntry
)
end,
npcData = function(sortOrder, pageSize, offset)
return string.format(
[[
SELECT ct.entry, ctm.CreatureDisplayID, ct.name, ct.subname, ct.type
FROM creature_template ct
LEFT JOIN creature_template_model ctm ON ct.entry = ctm.CreatureID
ORDER BY ct.entry %s
LIMIT %d OFFSET %d;
]],
sortOrder,
pageSize,
offset
)
end,
gobData = function(sortOrder, pageSize, offset)
-- Check if gameobjectdisplayinfo table exists
local hasDisplayInfo = DatabaseHelper.IsOptionalTableAvailable("gameobjectdisplayinfo", "world")
if hasDisplayInfo then
return string.format(
[[
SELECT g.entry, g.displayid, g.name, m.ModelName
FROM gameobject_template g
LEFT JOIN gameobjectdisplayinfo m ON g.displayid = m.ID
ORDER BY g.entry %s
LIMIT %d OFFSET %d;
]],
sortOrder,
pageSize,
offset
)
else
return string.format(
[[
SELECT g.entry, g.displayid, g.name, 'N/A' as ModelName
FROM gameobject_template g
ORDER BY g.entry %s
LIMIT %d OFFSET %d;
]],
sortOrder,
pageSize,
offset
)
end
end,
gobCount = function()
-- Use simple count without join to avoid issues
return [[
SELECT COUNT(*)
FROM gameobject_template;
]]
end,
spellCount = function()
return [[
SELECT COUNT(*)
FROM spell;
]]
end,
spellData = function(sortOrder, pageSize, offset)
return string.format(
[[
SELECT s.id, s.spellName0, s.spellDescription0, s.spellToolTip0, s.spellVisual1, s.spellVisual2,
s.EffectMiscValue1, s.EffectMiscValue2, s.EffectMiscValue3,
s.Effect1, s.Effect2, s.Effect3, s.schoolMask,
COALESCE(
cast1_base.FilePath, cast1_world.FilePath, cast1_special.FilePath,
impact1_base.FilePath, impact1_world.FilePath, impact1_special.FilePath,
state1_base.FilePath, state1_world.FilePath, state1_special.FilePath,
''
) as visualFilePath1,
COALESCE(
cast2_base.FilePath, cast2_world.FilePath, cast2_special.FilePath,
impact2_base.FilePath, impact2_world.FilePath, impact2_special.FilePath,
state2_base.FilePath, state2_world.FilePath, state2_special.FilePath,
''
) as visualFilePath2
FROM spell s
LEFT JOIN spellvisual sv1 ON s.SpellVisual1 = sv1.ID
LEFT JOIN spellvisualkit cast1 ON sv1.CastKit = cast1.ID
LEFT JOIN spellvisualeffectname cast1_base ON cast1.BaseEffect = cast1_base.ID
LEFT JOIN spellvisualeffectname cast1_world ON cast1.WorldEffect = cast1_world.ID
LEFT JOIN spellvisualeffectname cast1_special ON cast1.SpecialEffect1 = cast1_special.ID
LEFT JOIN spellvisualkit impact1 ON sv1.ImpactKit = impact1.ID
LEFT JOIN spellvisualeffectname impact1_base ON impact1.BaseEffect = impact1_base.ID
LEFT JOIN spellvisualeffectname impact1_world ON impact1.WorldEffect = impact1_world.ID
LEFT JOIN spellvisualeffectname impact1_special ON impact1.SpecialEffect1 = impact1_special.ID
LEFT JOIN spellvisualkit state1 ON sv1.StateKit = state1.ID
LEFT JOIN spellvisualeffectname state1_base ON state1.BaseEffect = state1_base.ID
LEFT JOIN spellvisualeffectname state1_world ON state1.WorldEffect = state1_world.ID
LEFT JOIN spellvisualeffectname state1_special ON state1.SpecialEffect1 = state1_special.ID
LEFT JOIN spellvisual sv2 ON s.SpellVisual2 = sv2.ID
LEFT JOIN spellvisualkit cast2 ON sv2.CastKit = cast2.ID
LEFT JOIN spellvisualeffectname cast2_base ON cast2.BaseEffect = cast2_base.ID
LEFT JOIN spellvisualeffectname cast2_world ON cast2.WorldEffect = cast2_world.ID
LEFT JOIN spellvisualeffectname cast2_special ON cast2.SpecialEffect1 = cast2_special.ID
LEFT JOIN spellvisualkit impact2 ON sv2.ImpactKit = impact2.ID
LEFT JOIN spellvisualeffectname impact2_base ON impact2.BaseEffect = impact2_base.ID
LEFT JOIN spellvisualeffectname impact2_world ON impact2.WorldEffect = impact2_world.ID
LEFT JOIN spellvisualeffectname impact2_special ON impact2.SpecialEffect1 = impact2_special.ID
LEFT JOIN spellvisualkit state2 ON sv2.StateKit = state2.ID
LEFT JOIN spellvisualeffectname state2_base ON state2.BaseEffect = state2_base.ID
LEFT JOIN spellvisualeffectname state2_world ON state2.WorldEffect = state2_world.ID
LEFT JOIN spellvisualeffectname state2_special ON state2.SpecialEffect1 = state2_special.ID
ORDER BY s.id %s
LIMIT %d OFFSET %d;
]],
sortOrder,
pageSize,
offset
)
end,
searchNpcData = function(query, typeId, sortOrder, pageSize, offset)
return string.format(
[[
SELECT ct.entry, ctm.CreatureDisplayID, ct.name, ct.subname, ct.type
FROM creature_template ct
LEFT JOIN creature_template_model ctm ON ct.entry = ctm.CreatureID
WHERE ct.name LIKE '%%%s%%' OR ct.subname LIKE '%%%s%%' OR ct.entry LIKE '%%%s%%' %s
ORDER BY ct.entry %s
LIMIT %d OFFSET %d;
]],
query,
query,
query,
typeId and string.format("OR ct.type = %d", typeId) or "",
sortOrder,
pageSize,
offset
)
end,
searchGobData = function(query, typeId, sortOrder, pageSize, offset)
-- Check if gameobjectdisplayinfo table exists
local hasDisplayInfo = DatabaseHelper.IsOptionalTableAvailable("gameobjectdisplayinfo", "world")
if hasDisplayInfo then
return string.format(
[[
SELECT g.entry, g.displayid, g.name, g.type, m.ModelName
FROM gameobject_template g
LEFT JOIN gameobjectdisplayinfo m ON g.displayid = m.ID
WHERE g.name LIKE '%%%s%%' OR g.entry LIKE '%%%s%%' %s
ORDER BY g.entry %s
LIMIT %d OFFSET %d;
]],
query,
query,
typeId and string.format("OR g.type = %d", typeId) or "",
sortOrder,
pageSize,
offset * pageSize
)
else
return string.format(
[[
SELECT g.entry, g.displayid, g.name, g.type, 'N/A' as ModelName
FROM gameobject_template g
WHERE g.name LIKE '%%%s%%' OR g.entry LIKE '%%%s%%' %s
ORDER BY g.entry %s
LIMIT %d OFFSET %d;
]],
query,
query,
typeId and string.format("OR g.type = %d", typeId) or "",
sortOrder,
pageSize,
offset * pageSize
)
end
end,
searchSpellData = function(query, sortOrder, pageSize, offset)
return string.format(
[[
SELECT s.id, s.spellName0, s.spellDescription0, s.spellToolTip0, s.spellVisual1, s.spellVisual2,
s.EffectMiscValue1, s.EffectMiscValue2, s.EffectMiscValue3,
s.Effect1, s.Effect2, s.Effect3, s.schoolMask,
COALESCE(
cast1_base.FilePath, cast1_world.FilePath, cast1_special.FilePath,
impact1_base.FilePath, impact1_world.FilePath, impact1_special.FilePath,
state1_base.FilePath, state1_world.FilePath, state1_special.FilePath,
''
) as visualFilePath1,
COALESCE(
cast2_base.FilePath, cast2_world.FilePath, cast2_special.FilePath,
impact2_base.FilePath, impact2_world.FilePath, impact2_special.FilePath,
state2_base.FilePath, state2_world.FilePath, state2_special.FilePath,
''
) as visualFilePath2
FROM spell s
LEFT JOIN spellvisual sv1 ON s.SpellVisual1 = sv1.ID
LEFT JOIN spellvisualkit cast1 ON sv1.CastKit = cast1.ID
LEFT JOIN spellvisualeffectname cast1_base ON cast1.BaseEffect = cast1_base.ID
LEFT JOIN spellvisualeffectname cast1_world ON cast1.WorldEffect = cast1_world.ID
LEFT JOIN spellvisualeffectname cast1_special ON cast1.SpecialEffect1 = cast1_special.ID
LEFT JOIN spellvisualkit impact1 ON sv1.ImpactKit = impact1.ID
LEFT JOIN spellvisualeffectname impact1_base ON impact1.BaseEffect = impact1_base.ID
LEFT JOIN spellvisualeffectname impact1_world ON impact1.WorldEffect = impact1_world.ID
LEFT JOIN spellvisualeffectname impact1_special ON impact1.SpecialEffect1 = impact1_special.ID
LEFT JOIN spellvisualkit state1 ON sv1.StateKit = state1.ID
LEFT JOIN spellvisualeffectname state1_base ON state1.BaseEffect = state1_base.ID
LEFT JOIN spellvisualeffectname state1_world ON state1.WorldEffect = state1_world.ID
LEFT JOIN spellvisualeffectname state1_special ON state1.SpecialEffect1 = state1_special.ID
LEFT JOIN spellvisual sv2 ON s.SpellVisual2 = sv2.ID
LEFT JOIN spellvisualkit cast2 ON sv2.CastKit = cast2.ID
LEFT JOIN spellvisualeffectname cast2_base ON cast2.BaseEffect = cast2_base.ID
LEFT JOIN spellvisualeffectname cast2_world ON cast2.WorldEffect = cast2_world.ID
LEFT JOIN spellvisualeffectname cast2_special ON cast2.SpecialEffect1 = cast2_special.ID
LEFT JOIN spellvisualkit impact2 ON sv2.ImpactKit = impact2.ID
LEFT JOIN spellvisualeffectname impact2_base ON impact2.BaseEffect = impact2_base.ID
LEFT JOIN spellvisualeffectname impact2_world ON impact2.WorldEffect = impact2_world.ID
LEFT JOIN spellvisualeffectname impact2_special ON impact2.SpecialEffect1 = impact2_special.ID
LEFT JOIN spellvisualkit state2 ON sv2.StateKit = state2.ID
LEFT JOIN spellvisualeffectname state2_base ON state2.BaseEffect = state2_base.ID
LEFT JOIN spellvisualeffectname state2_world ON state2.WorldEffect = state2_world.ID
LEFT JOIN spellvisualeffectname state2_special ON state2.SpecialEffect1 = state2_special.ID
WHERE s.spellName0 LIKE '%%%s%%' OR s.id LIKE '%%%s%%'
ORDER BY s.id %s
LIMIT %d OFFSET %d;
]],
query,
query,
sortOrder,
pageSize,
offset * pageSize
)
end,
itemCount = function(inventoryType)
if inventoryType and inventoryType >= 0 then
return string.format([[
SELECT COUNT(*)
FROM item_template
WHERE InventoryType = %d;
]], inventoryType)
else
return [[
SELECT COUNT(*)
FROM item_template;
]]
end
end,
itemData = function(sortOrder, pageSize, offset, inventoryType)
local whereClause = ""
if inventoryType then
whereClause = string.format("WHERE InventoryType = %d", inventoryType)
end
return string.format(
[[
SELECT entry, name, COALESCE(description, ''), displayid, Quality, InventoryType, ItemLevel, class, subclass
FROM item_template
%s
ORDER BY entry %s
LIMIT %d OFFSET %d;
]],
whereClause,
sortOrder,
pageSize,
offset
)
end,
searchItemData = function(query, sortOrder, pageSize, offset, inventoryType)
local whereClause = [[WHERE (name LIKE '%%%s%%' OR entry LIKE '%%%s%%')]]
if inventoryType then
whereClause = whereClause .. string.format(" AND InventoryType = %d", inventoryType)
end
return string.format(
[[
SELECT entry, name, COALESCE(description, ''), displayid, Quality, InventoryType, ItemLevel, class, subclass
FROM item_template
%s
ORDER BY entry %s
LIMIT %d OFFSET %d;
]],
string.format(whereClause, query, query),
sortOrder,
pageSize,
offset * pageSize
)
end,
spellVisualCount = function()
return [[
SELECT COUNT(*)
FROM spellvisualeffectname;
]]
end,
spellVisualData = function(sortOrder, pageSize, offset)
return string.format(
[[
SELECT ID, Name, FilePath, AreaEffectSize, Scale, MinAllowedScale, MaxAllowedScale
FROM spellvisualeffectname
ORDER BY ID %s
LIMIT %d OFFSET %d;
]],
sortOrder,
pageSize,
offset
)
end,
searchSpellVisualData = function(query, sortOrder, pageSize, offset)
return string.format(
[[
SELECT ID, Name, FilePath, AreaEffectSize, Scale, MinAllowedScale, MaxAllowedScale
FROM spellvisualeffectname
WHERE Name LIKE '%%%s%%' OR ID LIKE '%%%s%%'
ORDER BY ID %s
LIMIT %d OFFSET %d;
]],
query,
query,
sortOrder,
pageSize,
offset * pageSize
)
end,
},
}
-- Function to get the appropriate query based on the core name
local function getQuery(coreName, queryType)
return queries[coreName] and queries[coreName][queryType] or nil
end
-- Table name mappings for different query types
local queryTableMappings = {
-- NPC queries
loadCreatureDisplays = {"creature_template", "creature_template_model"},
npcData = {"creature_template", "creature_template_model"},
-- Item queries
loadItemForPacket = {"item_template"},
npcCount = {"creature_template"},
searchNpcData = {"creature_template", "creature_template_model"},
-- GameObject queries
gobData = {"gameobject_template", "gameobjectdisplayinfo"},
gobCount = {"gameobject_template"},
searchGobData = {"gameobject_template", "gameobjectdisplayinfo"},
-- Spell queries
spellCount = {"spell"},
spellData = {"spell", "spellvisual", "spellvisualkit", "spellvisualeffectname"},
searchSpellData = {"spell", "spellvisual", "spellvisualkit", "spellvisualeffectname"},
spellVisualCount = {"spellvisualeffectname"},
spellVisualData = {"spellvisualeffectname"},
searchSpellVisualData = {"spellvisualeffectname"},
-- Item queries
itemCount = {"item_template"},
itemData = {"item_template"},
searchItemData = {"item_template"},
}
-- Safe query execution functions
local function executeSafeQuery(queryFunc, databaseType, queryType)
databaseType = databaseType or "world"
local success, result = pcall(function()
local query = queryFunc()
if not query then
return nil
end
-- Add database prefix support if configured
if DatabaseHelper then
-- Get the tables used in this query type
local tables = queryTableMappings[queryType] or {}
local modifiedQuery, error = DatabaseHelper.BuildSafeQuery(query, tables, databaseType)
if not modifiedQuery then
return nil, error
end
query = modifiedQuery
end
return DatabaseHelper.SafeQuery(query, databaseType)
end)
if success then
return result
else
if DatabaseHelper and DatabaseHelper.debug then
print(string.format("[GameMasterUI] Query execution failed: %s", tostring(result)))
end
return nil
end
end
-- Async version of safe query execution
local function executeSafeQueryAsync(queryFunc, callback, databaseType, queryType)
databaseType = databaseType or "world"
local success, error = pcall(function()
local query = queryFunc()
if not query then
callback(nil, "Query function returned nil")
return
end
-- Add database prefix support if configured
if DatabaseHelper then
-- Get the tables used in this query type
local tables = queryTableMappings[queryType] or {}
DatabaseHelper.BuildSafeQueryAsync(query, tables, callback, databaseType)
else
-- Fallback to direct async query if no DatabaseHelper
DatabaseHelper.SafeQueryAsync(query, callback, databaseType)
end
end)
if not success then
if DatabaseHelper and DatabaseHelper.debug then
print(string.format("[GameMasterUI] Async query setup failed: %s", tostring(error)))
end
callback(nil, tostring(error))
end
end
-- Initialize database helper when module loads
local function initialize()
if DatabaseHelper and DatabaseHelper.Initialize then
-- Config will be injected by the main system
-- This is just a placeholder - actual initialization happens in Init.lua
end
end
return {
queries = queries,
getQuery = getQuery,
executeSafeQuery = executeSafeQuery,
executeSafeQueryAsync = executeSafeQueryAsync,
queryTableMappings = queryTableMappings,
initialize = initialize,
}
Reference in a new issue View git blame Copy permalink