EQEMU Database: Difference between revisions
From EQArchives
Jump to navigationJump to search
No edit summary |
No edit summary |
||
(One intermediate revision by the same user not shown) | |||
Line 11: | Line 11: | ||
* ''Any'' changes to Spell data requires pushing an updated spells_us.txt patch to all clients. | * ''Any'' changes to Spell data requires pushing an updated spells_us.txt patch to all clients. | ||
==SQL Snippets== | == SQL Snippets == | ||
=== Merchants === | |||
==== List Merchants by Zone and Merchant_ID ==== | |||
<pre> | |||
select nt.name, nt.merchant_id | |||
from npc_types as nt | |||
INNER JOIN spawnentry as se | |||
ON nt.id = se.npcID | |||
INNER JOIN spawn2 as s | |||
on se.spawngroupID = s.spawngroupID | |||
INNER JOIN zone as z | |||
on s.zone = z.short_name | |||
where nt.merchant_id > 0 and z.short_name = 'grobb'; | |||
</pre> | |||
==== Disable merchantlist entries by item ==== | |||
<pre> | |||
UPDATE `merchantlist` | |||
SET probability = 0 | |||
WHERE item = 38399 | |||
</pre> | |||
=== Lootdrops === | |||
==== Identify empty lootdrops ==== | |||
SELECT ld.id | SELECT ld.id | ||
Line 20: | Line 42: | ||
WHERE lde.lootdrop_id IS NULL | WHERE lde.lootdrop_id IS NULL | ||
== Clenaup empty lootdrops == | ==== Clenaup empty lootdrops ==== | ||
<pre> | <pre> | ||
DELETE ld | DELETE ld | ||
Line 110: | Line 132: | ||
== Get all loottable entry fields for all npc_types with enabled spawns == | == Get all loottable entry fields for all npc_types with enabled spawns == | ||
< | <pre> | ||
SELECT lte.loottable_id, lte.lootdrop_id, lte.multiplier, lte.droplimit, lte.mindrop, lte.probability, n.name, lt.name | SELECT lte.loottable_id, lte.lootdrop_id, lte.multiplier, lte.droplimit, lte.mindrop, lte.probability, n.name, lt.name | ||
FROM loottable_entries AS lte | FROM loottable_entries AS lte | ||
Line 125: | Line 147: | ||
WHERE z.short_name = "ecommons" AND n.loottable_id > 0 | WHERE z.short_name = "ecommons" AND n.loottable_id > 0 | ||
</pre> | </pre> | ||
<pre> | <pre> | ||
SELECT n.* | SELECT n.* | ||
Line 136: | Line 159: | ||
WHERE name LIKE ? AND race != '127' AND race != '240' AND z.min_status = 0 LIMIT 1 | WHERE name LIKE ? AND race != '127' AND race != '240' AND z.min_status = 0 LIMIT 1 | ||
</pre> | </pre> | ||
=== Get all loottable entry fields for all npc_types with enabled spawns === | === Get all loottable entry fields for all npc_types with enabled spawns === | ||
<pre> | <pre> |
Latest revision as of 10:45, 15 June 2023
This page is intended to provide information regarding Database Administration for an EQEMU Database.
Spawn Conditions
See DB:Spawn_Events
Reboots Required
The following changes require a reboot due to shared memory:
- Any changes involving loot tables.
Patching Required
- Any changes to Spell data requires pushing an updated spells_us.txt patch to all clients.
SQL Snippets
Merchants
List Merchants by Zone and Merchant_ID
select nt.name, nt.merchant_id from npc_types as nt INNER JOIN spawnentry as se ON nt.id = se.npcID INNER JOIN spawn2 as s on se.spawngroupID = s.spawngroupID INNER JOIN zone as z on s.zone = z.short_name where nt.merchant_id > 0 and z.short_name = 'grobb';
Disable merchantlist entries by item
UPDATE `merchantlist` SET probability = 0 WHERE item = 38399
Lootdrops
Identify empty lootdrops
SELECT ld.id FROM lootdrop AS ld LEFT OUTER JOIN lootdrop_entries AS lde ON ld.id = lde.lootdrop_id WHERE lde.lootdrop_id IS NULL
Clenaup empty lootdrops
DELETE ld FROM lootdrop AS ld LEFT OUTER JOIN lootdrop_entries AS lde ON ld.id = lde.lootdrop_id WHERE lde.lootdrop_id IS NULL
Find all the disabled stuff that's higher ID
SELECT ld.id, ld.name, lde.item_id, i.name, lde.chance, lde.disabled_chance FROM lootdrop AS ld INNER JOIN lootdrop_entries AS lde ON ld.id = lde.lootdrop_id INNER JOIN items AS i ON lde.item_id = i.id WHERE lde.chance = "0" AND lde.item_id > "20000" GROUP BY lde.item_id
List who killed a mob
SELECT cd.name, kre.event_id, kr.fight_id, npc.name FROM character_data AS cd INNER JOIN qs_player_npc_kill_record_entries AS kre ON kre.char_id = cd.id INNER JOIN qs_player_npc_kill_record AS kr ON kre.event_id = kr.fight_id INNER JOIN npc_types AS npc ON kr.npc_id = npc.id WHERE cd.name = "stringname"
Drop chance of item by item ID
SELECT ld.item_id, i.name, s.zone, npc.name, lte.mindrop, lte.droplimit, ld.chance FROM lootdrop_entries AS ld INNER JOIN items AS i ON ld.item_id = i.id INNER JOIN loottable_entries AS lte ON ld.lootdrop_id = lte.lootdrop_id INNER JOIN npc_types AS npc ON lte.loottable_id = npc.loottable_id INNER JOIN spawnentry AS se ON npc.id = se.npcID INNER JOIN spawn2 AS s ON se.spawngroupID = s.spawngroupID INNER JOIN zone AS z ON s.zone = z.short_name WHERE s.enabled = "1" AND i.id = "intid" AND z.min_status < "1" ORDER BY 7 DESC
Inventory by Item Name (partial)
SELECT cd.name AS "Player", inv.itemid AS "Item ID", i.Name AS "Item Name", inv.charges AS "Stacked", COUNT(*) AS "Count" FROM character_data AS cd INNER JOIN inventory AS inv ON inv.charid = cd.id INNER JOIN items AS i ON inv.itemid = i.id WHERE i.Name LIKE "%item name%" GROUP BY cd.name ORDER BY 5 DESC
Check where tradeskills are at
SELECT cs.id, cd.name, cs.skill_id, cs.value FROM character_skills AS cs INNER JOIN character_data AS cd ON cs.id = cd.id INNER JOIN account AS a ON cd.account_id = a.id WHERE a.status = 0 AND (cs.skill_id = 60 -- 60 - Baking OR cs.skill_id = 61 -- 61 - Tailoring OR cs.skill_id = 63 -- 63 - Blacksmithing OR cs.skill_id = 64 -- 64 - Fletching OR cs.skill_id = 65 -- 65 - Brewing OR cs.skill_id = 68 -- 68 - Jewelry Making OR cs.skill_id = 69) -- 69 - Pottery ORDER BY 4 DESC
Get all loottable entry fields for all npc_types with enabled spawns
SELECT lte.loottable_id, lte.lootdrop_id, lte.multiplier, lte.droplimit, lte.mindrop, lte.probability, n.name, lt.name FROM loottable_entries AS lte INNER JOIN loottable AS lt ON lt.id = lte.loottable_id INNER JOIN npc_types AS n ON lte.loottable_id = n.loottable_id INNER JOIN spawnentry AS se ON n.id = se.npcID INNER JOIN spawn2 AS s on se.spawngroupID = s.spawngroupID INNER JOIN zone AS z ON s.zone = z.short_name WHERE z.short_name = "ecommons" AND n.loottable_id > 0
SELECT n.* FROM npc_types AS n INNER JOIN spawnentry AS se ON n.id = se.npcID INNER JOIN spawn2 AS s ON se.spawngroupID = s.spawngroupID INNER JOIN zone AS z ON s.zone = z.short_name WHERE name LIKE ? AND race != '127' AND race != '240' AND z.min_status = 0 LIMIT 1
Get all loottable entry fields for all npc_types with enabled spawns
SELECT lte.loottable_id, lte.lootdrop_id, lte.multiplier, lte.droplimit, lte.mindrop, lte.probability, n.name, lt.name FROM loottable_entries AS lte INNER JOIN loottable AS lt ON lt.id = lte.loottable_id INNER JOIN npc_types AS n ON lte.loottable_id = n.loottable_id INNER JOIN spawnentry AS se ON n.id = se.npcID INNER JOIN spawn2 AS s on se.spawngroupID = s.spawngroupID INNER JOIN zone AS z ON s.zone = z.short_name WHERE z.short_name = "ecommons" AND n.loottable_id > 0
Check where tradeskills are at
SELECT cs.id, cd.name, cs.skill_id, cs.value FROM character_skills AS cs INNER JOIN character_data AS cd ON cs.id = cd.id INNER JOIN account AS a ON cd.account_id = a.id WHERE a.status = 0 AND (cs.skill_id = 60 -- 60 - Baking OR cs.skill_id = 61 -- 61 - Tailoring OR cs.skill_id = 63 -- 63 - Blacksmithing OR cs.skill_id = 64 -- 64 - Fletching OR cs.skill_id = 65 -- 65 - Brewing OR cs.skill_id = 68 -- 68 - Jewelry Making OR cs.skill_id = 69) -- 69 - Pottery ORDER BY 4 DESC
Inventory by Item Name (partial)
SELECT cd.name AS "Player", inv.itemid AS "Item ID", i.Name AS "Item Name", inv.charges AS "Stacked", COUNT(*) AS "Count" FROM character_data AS cd INNER JOIN inventory AS inv ON inv.charid = cd.id INNER JOIN items AS i ON inv.itemid = i.id WHERE i.Name LIKE "%item name%" GROUP BY cd.name ORDER BY 5 DESC
Drop chance of item by item ID
SELECT ld.item_id, i.name, s.zone, npc.name, lte.mindrop, lte.droplimit, ld.chance FROM lootdrop_entries AS ld INNER JOIN items AS i ON ld.item_id = i.id INNER JOIN loottable_entries AS lte ON ld.lootdrop_id = lte.lootdrop_id INNER JOIN npc_types AS npc ON lte.loottable_id = npc.loottable_id INNER JOIN spawnentry AS se ON npc.id = se.npcID INNER JOIN spawn2 AS s ON se.spawngroupID = s.spawngroupID INNER JOIN zone AS z ON s.zone = z.short_name WHERE s.enabled = "1" AND i.id = "intid" AND z.min_status < "1" ORDER BY 7 DESC
List who killed a mob
SELECT cd.name, kre.event_id, kr.fight_id, npc.name FROM character_data AS cd INNER JOIN qs_player_npc_kill_record_entries AS kre ON kre.char_id = cd.id INNER JOIN qs_player_npc_kill_record AS kr ON kre.event_id = kr.fight_id INNER JOIN npc_types AS npc ON kr.npc_id = npc.id WHERE cd.name = "stringname"