EQEmulator Forums

EQEmulator Forums (https://www.eqemulator.org/forums/index.php)
-   Development::Database/World Building (https://www.eqemulator.org/forums/forumdisplay.php?f=596)
-   -   Required items (https://www.eqemulator.org/forums/showthread.php?t=29315)

Shadow-Wolf 08-23-2009 09:07 PM
Required items
 
Hello folks, need a little help. It's been forever since I've done anything eqemu related and I would like some info if anyone has it. What I am wanting to do is simple, I want to wipe my item table clean except for all items required for functioning spells and other misc required items. If anyone has a list of these items I would greatly appreciate you sharing it. If not I will look my self, I am simply trying to not reinvent the wheel here. Thank you for your time.

trevius 08-23-2009 09:59 PM
Quote:
Originally Posted by Shadow-Wolf (Post 177214)
What I am wanting to do is simple, I want to wipe my item table clean except for all items required for functioning spells and other misc required items.
Not exactly sure what you mean by this. No items are required for anything. Though, if you only want certain items available on your server, you might be better off wiping out the loot tables and creating new ones with the select items you want to have available. There isn't much need to clear out the items table as there are thousands of IDs still available that you can create items in.

Shadow-Wolf 08-23-2009 10:11 PM
What I mean are items like bone chips and peridots, ttems that spells consume when they are used. I want to wipe the item database and build a new one with custom items organized in a way that makes development of a custom world easier for me. I just don't want to bother with the spell files therefore I want to leave the items consumed by them.

drakelord 08-24-2009 05:48 AM
Quickest way I can think of is to make a list of all the item id's required by the spells, then just run a query in your choice of MySQL browser selecting for any entries with those ids. Back those up, wipe the table, then restore, and you have yourself an empty table.

Secrets 08-24-2009 04:10 PM
Simpler way is to use excel or a similar program to make a query for you. You can sort by reagent ID in navicat, and copy-paste it into excel. Something like
Code:
"Select * from items where id = (id from navicat) and
id = | (next id from navicat) | and"
with the | being a delimiter for the cell. Then, you can copy-paste into navicat, run the query, and use navicat's export tools to export all of the item IDs that are used by the spells table, and then empty the table (also using navicat) and import the SQL you also exported. Try that, see if it works.

If all else fails look into JOIN queries.

Protip: Navicat's ordering views + Excel = heaven.

GeorgeS 08-25-2009 01:26 AM
Please use a simplied sql query rather than a long string

Code:
SELECT name  from items i where id IN (1003,1001,1002)
returns name where item id = 1001-1003

GeorgeS

AndMetal 08-25-2009 03:14 AM
I'm sure the syntax is 100% correct, but you should be able to do something like this:

Code:
DELETE FROM items
        WHERE id NOT IN (
                (SELECT components1 FROM spells_new WHERE components1 > 1000),
                (SELECT components2 FROM spells_new WHERE components2 > 1000),
                (SELECT components3 FROM spells_new WHERE components3 > 1000),
                (SELECT components4 FROM spells_new WHERE components4 > 1000),
                (SELECT NoexpendReagent1 FROM spells_new WHERE NoexpendReagent1 > 1000),
                (SELECT NoexpendReagent2 FROM spells_new WHERE NoexpendReagent2 > 1000),
                (SELECT NoexpendReagent3 FROM spells_new WHERE NoexpendReagent3 > 1000),
                (SELECT NoexpendReagent4 FROM spells_new WHERE NoexpendReagent4 > 1000)
        )

pfyon 08-25-2009 10:26 AM
I was working on a query last night similar to AndMetal's, but it ended up running for about 2 hours before I killed it.

cavedude 08-25-2009 11:51 AM
Quote:
Originally Posted by AndMetal (Post 177282)
I'm sure the syntax is 100% correct, but you should be able to do something like this:

Code:
DELETE FROM items
        WHERE id NOT IN (
                (SELECT components1 FROM spells_new WHERE components1 > 1000),
                (SELECT components2 FROM spells_new WHERE components2 > 1000),
                (SELECT components3 FROM spells_new WHERE components3 > 1000),
                (SELECT components4 FROM spells_new WHERE components4 > 1000),
                (SELECT NoexpendReagent1 FROM spells_new WHERE NoexpendReagent1 > 1000),
                (SELECT NoexpendReagent2 FROM spells_new WHERE NoexpendReagent2 > 1000),
                (SELECT NoexpendReagent3 FROM spells_new WHERE NoexpendReagent3 > 1000),
                (SELECT NoexpendReagent4 FROM spells_new WHERE NoexpendReagent4 > 1000)
        )
effect_base_value1, 2, 3, and 4 are also used, to summon items I believe.

Shadow-Wolf 08-25-2009 03:09 PM
Quote:
Originally Posted by AndMetal (Post 177282)
I'm sure the syntax is 100% correct, but you should be able to do something like this:

Code:
DELETE FROM items
        WHERE id NOT IN (
                (SELECT components1 FROM spells_new WHERE components1 > 1000),
                (SELECT components2 FROM spells_new WHERE components2 > 1000),
                (SELECT components3 FROM spells_new WHERE components3 > 1000),
                (SELECT components4 FROM spells_new WHERE components4 > 1000),
                (SELECT NoexpendReagent1 FROM spells_new WHERE NoexpendReagent1 > 1000),
                (SELECT NoexpendReagent2 FROM spells_new WHERE NoexpendReagent2 > 1000),
                (SELECT NoexpendReagent3 FROM spells_new WHERE NoexpendReagent3 > 1000),
                (SELECT NoexpendReagent4 FROM spells_new WHERE NoexpendReagent4 > 1000)
        )
With a little modification adding the fields Cavedude specified this will give me exactly what I needed. Thank you everyone for your help!

Shadow-Wolf 08-25-2009 06:30 PM
Hmm, while the syntax throws no errors there is something wrong with the statements logic as it deletes no items at all, looking into it.

Edit - When running this through an sql validator I get this

The following feature outside Core SQL-200x (draft) is used:
F561, "Full value expressions""


All times are GMT -4. The time now is 01:34 AM.

Powered by vBulletin®, Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.