Adding column to npc_types

[Disorder]

Greetings!

I've been messing around with npc_types editing the entire DB at a time, just so see how it works out. I made a few changes..

HP = HP / 8
maxdmg = maxdmg / 8
mindmg = mindmg / 8

Doing this affected pets. After going through and comparing spells to npcs that are spawned, I realize that to change all pets in a similar manner is not a quick task. Would it be possible to add a column to the npc_types called "ispet", 0 being default of non pet mobs, and 1 being pet types. This would allow for easier changes to pets I think, using a WHERE argument in db changes. I was thinking about trying this with my own table and going through manually and updating the pet npcs. Would this stop my DB from functioning correctly?

I know some of the words I used loosely relate to their actual definitions. I'm still ignorant of all the correct language to use when describing such items. Regardless, I think most of you should understand what I am saying. Thanks, guys. :)

[c0ncrete]

it shouldn't be an issue unless there are areas of the server's source code that select all fields (via *) from the npc_types table and expect them to be in a particular order (or a specific field count). you also want to be aware that it'll probably make any gm (#) commands that might add entries to the npc_types table behave (or fail) unexpectedly.

you could make a separate table entirely and store just the id of any npc_type that you know is a pet. then when you're running queries, you can use "... WHERE npc_type.id IN (SELECT npc_type_id FROM known_pets)" as a filter.

[Disorder]

The second method sounds better, as I'd prefer to avoid any negative side effects. It could also easily be shared with others this way I think. Just a table such as npc_pets, with petid column and npc_types id column.

By doing this, you are saying that I could make changes in npc_types only to npc's listed in npc_pets table?

Mind giving an example of what this command would look like?

As always, thank you, c0ncrete.

[c0ncrete]

yeah, it's called a subquery.

say for instance you have a table called known_pets that has a column, called npc_type_id. this would obviously store the id for npc types that you know are pets.

if you wanted to double the hitpoints for all pets, you'd use the following query:

UPDATE
npc_types
SET
hp = hp * 2
WHERE
npc_types.id
IN
(SELECT npc_type_id FROM known_pets)

if you wanted to do something to everything that isn't a pet, you'd just use NOT IN, instead of IN.

be aware that subqueries can only return a single column from a table. more info can be found here: http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

[Disorder]

Very helpful. Thank you very much. :)

I'm always hesitant to do changes like this. THERE IS NO UNDO BUTTON!!! :D

[c0ncrete]

http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html :p

[Burningsoul]

Quick reply so forgive a potential screw up, but aren't all pets below ID 1000? I'd just update your queries with a "where id<1000;" to affect only pets or >1000 for non-pets.

[Disorder]

This appears to be true to levels lower than 70 I think.

Problem I am running into.

If I look at spells_new and locate the npc id it spawns in the teleport_zone - Not all the NPCs are listed in the npc_types.

For example : spell id: 9983 Kyrah's Faithful lists shaman_wolf_72_ as the mob spawned by the spell. However, if I search for (contains) shaman_wolf in npc_types name column, I only find shaman_wolf_67_ .

Am I thinking this works differently than it does?

Thanks.

[lerxst2112]

No, it just means that the level 72 pet doesn't exist.

I don't have a database handy atm, but I remember it being pretty simple to filter pets by joining on the id in the pet table.

[Disorder]

Ah ok, well if you do get time, I would appreciate the information :)

[Disorder]

And you are correct. I attempted to cast the 72 pet spell..

Unable to find data for pet shaman_wolf_72_

interesting. Why is this data missing?

[lerxst2112]

And you are correct. I attempted to cast the 72 pet spell..



interesting. Why is this data missing?

Because nobody has added it.

[c0ncrete]

you can also do ridiculous pattern matching against the name field with REGEXP, but you'll end up having to use COLLATE utf8_bin if you want case sensitivity. i'm not sure what the side-effects might be as far as unexpected results with binary collation. i was puttering around with it earlier and stopped when my query started to look like this:

SELECT
name, id
FROM
npc_types
WHERE
name
REGEXP
'^[^#]?Familiar|Sum(Air|Earth|Water|Fire)|(Swarm|Pet)[A-Z]'
COLLATE
utf8_bin;

mysql info on regexp
http://dev.mysql.com/doc/refman/5.1/en/regexp.html

regular expression pattern matching is insanely powerful, but you sometimes end up going insane trying to get your pattern to do exactly what you want it to do. they're not used for anywhere near what they have the power to do in most quest scripts you'll see, as they are normally only in EVENT_SAY for simple (and often incorrect) string matches.

perl re info
http://perldoc.perl.org/perlre.html

here are a couple of gui apps that are handy to use when writing more complex expressions
http://www.weitz.de/regex-coach/
http://www.regexbuddy.com/

[Disorder]

Wow awesome. Thanks again :)

[Disorder]

For an immediate fix, I figured out how to use LIKE %sumair (or fire, etc) for mage pets.

UPDATE npc_types SET hp = hp * 8 WHERE npc_types.name LIKE 'sumfire%';

It's not streamlined like I was hoping for, but better than doing every single one by hand.

I think it would be interesting to see pets become easily distinguished from all other npc_types, but maybe it isn't as useful as I think it would be.

[rencro]

UPDATE npc_types
INNER JOIN
pets
ON npc_types.id=pets.npcID
SET hp = hp * 8;

[c0ncrete]

UPDATE npc_types
INNER JOIN
pets
ON npc_types.id=pets.npcID
SET hp = hp * 8;


you'd still have the problem with 3 of them (for now) being non-client pets with that query.

UPDATE
npc_types
INNER JOIN
pets
ON
npc_types.id = pets.npcID
AND
pets.npcID < 29000
SET
hp = hp * 8;

[Furniture]

all the pets have ids less then 999, so just run a query affecting those

[rencro]

all the pets have ids less then 999, so just run a query affecting those

In my experience, when one goes customizing a database, the original structure no longer exists, so your statement may or may not be true, depending on whats been done to that specific database.

The original post asked for a custom column that would state if an npc in npc_type was a pet to help locate which ids in the table were pets. As can be seen by queries posted throughout this thread, thats not neccessary as there are many ways to determine which ids are tied to pets, as well, a query was given that actually makes the changes asked for further down the thread..

As for the three non pc pets in the pets table, they are, well, pets.

[Disorder]

UPDATE
npc_types
INNER JOIN
pets
ON
npc_types.id = pets.npcID
AND
pets.npcID < 29000
SET
hp = hp * 8;

Is this in reference to a new table being created first, c0ncrete?

[c0ncrete]

nope, that was joining the npc type ids from a separate table that should already exist in your database called pets. i haven't done a lot of tinkering where the database is concerned for mobs and whatnot, so apparently the fact that it was there completely eluded me.

the table in question has at least 3 entries that are npc only pets... but as rencro pointed out, they're still pets. i guess you'd be modifying npc pets either way, considering pets summoned via spells are shared between npc/bot/pc. i'm not sure what i was thinking when i imagined there would be a difference.

[Disorder]

Interesting. I can't believe I didn't realize there was a pets table. Completely missed it. This helps tremendously.