EQEMU Database

From EQArchives
Revision as of 10:45, 15 June 2023 by Nazwahdi (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigationJump to search

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"
Retrieved from "https://eqarchives.com/w/index.php?title=EQEMU_Database&oldid=2560"