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!!!

[c0ncrete]

http://dev.mysql.com/doc/refman/5.1/...p-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]

Originally Posted by Disorder

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.