EQEmulator Forums - Crazy idea for spells...

EQEmulator Forums (https://www.eqemulator.org/forums/index.php)

- Development::Development (https://www.eqemulator.org/forums/forumdisplay.php?f=590)

- - Crazy idea for spells... (https://www.eqemulator.org/forums/showthread.php?t=24193)

Crazy idea for spells...

It seems like there have been several posts recently about spells, and trying to make easy ways to modify them, and it got me thinking... Why aren't the spells in the DB yet? Config files && hard coded variables == bad, database == good :D

I'm no programming expert, but it looks like somewhere the spells_us.txt file is parsed into an array (spells[]) using Separator, I think (from common/seperator.h). In theory, it should be relatively easy to change how it is loaded from a file to loading it from the database. I'd take a stab at this, but I'm having some trouble finding where in the source the spells file is actually loaded.

Does anyone know where that can actually be found?

I am not a developer, but...
it may something to do with the fact that spell_us is loaded from BOTH sides, client and server. And since client cannot be altered, my best guess that spells format will have to stay the way they areat least on the client end.

Thought a fetaure where spels could be stored/edited in server db, and then dumped into txt file and given to players would be nice

Quote:

Originally Posted by ChaosSlayer (Post 142014)

Thought a fetaure where spels could be stored/edited in server db, and then dumped into txt file and given to players would be nice

That's exactly the end result I'm looking for :)

Right now, if you make custom spells, you have to give the altered copy of the spells_us.txt file for distribution anyway. If it is in a database, you can either setup a daily script to output the file for you, or have it manually created when you click a link (dynamic file), the latter being more resource intensive, but more real-time. This makes it easier for users to have access to it, and easier for admins to make changes.

After doing some more digging, I answered my first question. The spells are loaded in EMuShareMem using EMuShareMem/Spells.cpp. Unfortunately, I can't make much sense out of it.

assuming you want to do it all with a db,,

what if you made spells work by #cast 1-9 and text based spellbook? :P

then you could setup the spellbook to let you do searches

#book list 1 30
level range 1 to 30

and mem a spell !

#book mem 1 321

(casting slot 1, spell 321 from book)

then you could create tiers of spells all the way to 250 ! hahaha..............

mind you the players might balk at having to use commands for everything spell related, even though they can make hotkeys.

ok i experienced a momentary "mr wee" there... .. but seriously !

This would make the server much cleaner, make spell editing easier and faster. It also means an extra installation step to convert the spells file into the db. Hope to see this soon.

I have finally decided to give this a shot, but I wanted to see if I could get some feedback.

I found where the functions are that actually load the spells from the spells_us.txt file, which is in zone/net.cpp. For the life of me, I can't figure out why they would be defined in the net code. Sure, I could just add some #include's to allow database functionality, but if possible, I'd rather put it in a more appropriate spot (zone/zonedb.cpp maybe?)

The feedback I'm looking for is this: would it be better to leave the code where it is, in zone/net.cpp, or should I put it somewhere else? And if I put it somewhere else, where should it go?

If you use the PHP editor, the spells already have to be pulled into the database. So it's been done already for another purpose, just not actually used by the server. Might check that out.

zone/net.cpp is actually not named very well it should be named something like main.cpp as it is what actually contains the main() program. It's probably defined there because it was implemented rather early and that's a trivial way to get access to spells throughout the code.

That makes sense.

I think I might actually be lucky, because I think dbcore functions (RunQuery mainly) are accessible, although I'm not sure how because it's not #include'd directly. If not, I'll just add it, but I've already made about 1/3 of the changes needed, so hopefully I'll be able to start compiling & testing it by this weekend.

As long as the zonedatabase has been initialized you can use database.RunQuery() pretty accessibly throughout almost any part of the zone code.

I finally had a chance to revisit this (I was using RunQuery as opposed to database.RunQuery, plus I fubar'd some stuff with shared memory) and it's working great. However, I wanted to get some additional feedback: should we make everyone do this (just change the source), or should we #define DB_LoadSPDat vs #define NEW_LoadSPDat, that way everyone can update as they wish?

I'm kinda thinking the latter, but I wanted to see what everyone else thought, especially since we'll probably want to make a Perl script (I only have a PHP script) to load a custom spell set into the database vs using the Titanium spell set.

Could you make it so it loads the default titanium spells first and then loads additional spells later if they exist by checking some rule? That would be ideal to keep it compatible with the current way and then enable the custom stuff.

Quote:

Originally Posted by Congdar (Post 159232)

I'm not sure I understand what you mean by "default titanium spells first".

This change allows the spells_us.txt file to be sourced into the database once & read from there instead of opening the spells_us.txt file each time the server (zone?) is loaded. The only thing that would change by the #define is whether or not you load from the spells_us.txt file (the "old" way) or load from the database (the "new" way).

I like the idea of making things faster, but the added problem of downloading a txt file that matches a particular servers emu code level would be a pain if you played on multiple servers with possibly different code levels(like the one you play on when your favorite is down, etc).

So there would need to be the 'default titanium spells first' list in the db that works with anybody that connects and doesn't have 'the latest custom spell list with all the fixes'.

Quote:

Originally Posted by Congdar (Post 159234)

from what I know most people who play on difirent custom servers simply have 2 or more EQ folders, rather than ever mess with continusly changing reqiured files

Quote:

Originally Posted by Congdar (Post 159234)

there would need to be the 'default titanium spells first' list in the db that works with anybody that connects and doesn't have 'the latest custom spell list with all the fixes'.

I think there must be some confusion about the spells (or I'm completely misunderstanding what you're saying), because the client & server don't handle spells differently based on the client like we do with other things (opcodes, spawn structs, player profile structs, etc).

Quote:

Originally Posted by Congdar (Post 159234)

the added problem of downloading a txt file that matches a particular servers emu code level would be a pain if you played on multiple servers with possibly different code levels(like the one you play on when your favorite is down, etc)

That's the way it currently works (which is a pita, and why most, if not all servers, use either the straight Titanium spell file or slightly modified version), and I think that's why I'm confused about where you're coming from.

Here's the current flow of how the spells are loaded:

main()
LoadSPDat() called in main()
FileLoadSPDat() called via a callback (extFileLoadSPDat) in LoadSPDat()
Read from spells_us.txt as directed by FileLoadSPDat()

All I'm doing is changing the last step to this:

main()
LoadSPDat() called in main()
FileLoadSPDat() called via a callback (extFileLoadSPDat) in LoadSPDat()
Read from database as directed by FileLoadSPDat()

So instead of parsing through this:

Code:

3^Summon Corpse^PLAYER_1^^^^^^^10000^^0^0^5000^2250^12000^0^0^0^700^70^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^2512^2106^17355^-1^-1^-1^1^1^1^1^-1^-1^-1^-1^100^100^100^100^100^100^100^100^100^100^100^100^0^1^0^0^91^254^254^254^254^254^254^254^254^254^254^254^6^20^14^-1^0^0^255^255^255^255^51^255^255^255^255^255^35^255^255^255^255^255^43^^0^4^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^100^0^109^307^0^0^0^0^0^0^0^0^0^3^125^64^^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^5^101^49^52^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^1^0^0

we're iterating through this:

Code:

mysql> SELECT * FROM spells_new LIMIT 1 OFFSET 1;

+----+---------------+----------+---------------+----------+-------------+-------------+---------------+-------------+-------+----------+----------+--------+-----------+---------------+-------------+---------------------+--------------+------------+------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+----------------------+----------------------+----------------------+------+------+------+------+------+------+------+------+------+-------+-------+-------+------+---------+-------------+-------------+-------------+-------------+-------------------+-------------------+-------------------+-------------------+------------------+------------------+------------------+------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+-----------+-----------+-----------+-----------+------------+-----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+------------+------------+------------+------------+----------+-------+----------+-----------------+-----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-------------+------------+------------+------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+----------+----------+----------+-----------+-----------------+------------+---------------------+------------+--------------+----------+----------+----------+----------------+---------+-------------+---------------+----------+----------+----------+----------+-----------+----------+----------+----------+-----------+-----------------+----------+----------+----------+----------+----------+-----------+-------------+----------+---------+---------------+---------------+--------------+----------------+----------+----------+----------+----------+---------+-----------+--------------+----------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+------------+----------+----------+----------+----------+----------+----------+----------+

| id | name          | player_1 | teleport_zone | you_cast | other_casts | cast_on_you | cast_on_other | spell_fades | range | aoerange | pushback | pushup | cast_time | recovery_time | recast_time | buffdurationformula | buffduration | AEDuration | mana | effect_base_value1 | effect_base_value2 | effect_base_value3 | effect_base_value4 | effect_base_value5 | effect_base_value6 | effect_base_value7 | effect_base_value8 | effect_base_value9 | effect_base_value10 | effect_base_value11 | effect_base_value12 | effect_limit_value1 | effect_limit_value2 | effect_limit_value3 | effect_limit_value4 | effect_limit_value5 | effect_limit_value6 | effect_limit_value7 | effect_limit_value8 | effect_limit_value9 | effect_limit_value10 | effect_limit_value11 | effect_limit_value12 | max1 | max2 | max3 | max4 | max5 | max6 | max7 | max8 | max9 | max10 | max11 | max12 | icon | memicon | components1 | components2 | components3 | components4 | component_counts1 | component_counts2 | component_counts3 | component_counts4 | NoexpendReagent1 | NoexpendReagent2 | NoexpendReagent3 | NoexpendReagent4 | formula1 | formula2 | formula3 | formula4 | formula5 | formula6 | formula7 | formula8 | formula9 | formula10 | formula11 | formula12 | LightType | goodEffect | Activated | resisttype | effectid1 | effectid2 | effectid3 | effectid4 | effectid5 | effectid6 | effectid7 | effectid8 | effectid9 | effectid10 | effectid11 | effectid12 | targettype | basediff | skill | zonetype | EnvironmentType | TimeOfDay | classes1 | classes2 | classes3 | classes4 | classes5 | classes6 | classes7 | classes8 | classes9 | classes10 | classes11 | classes12 | classes13 | classes14 | classes15 | classes16 | CastingAnim | TargetAnim | TravelType | SpellAffectIndex | field124 | field125 | deities1 | deities2 | deities3 | deities4 | deities5 | deities6 | deities7 | deities8 | deities9 | deities10 | deities11 | deities12 | deities13 | deities14 | deities15 | deities16 | field142 | field143 | new_icon | spellanim | uninterruptable | ResistDiff | dot_stacking_exempt | deleteable | RecourseLink | field151 | field152 | field153 | short_buff_box | descnum | typedescnum | effectdescnum | field158 | field159 | field160 | field161 | bonushate | field163 | field164 | field165 | EndurCost | EndurTimerIndex | field168 | field169 | field170 | field171 | field172 | HateAdded | EndurUpkeep | field175 | numhits | pvpresistbase | pvpresistcalc | pvpresistcap | spell_category | field181 | field182 | field183 | field184 | can_mgb | nodispell | npc_category | npc_usefulness | field189 | field190 | field191 | field192 | field193 | field194 | field195 | field196 | field197 | field198 | field199 | field200 | field201 | field202 | field203 | field204 | field205 | field206 | spellgroup | field208 | field209 | field210 | field211 | field212 | field213 | field214 |

+----+---------------+----------+---------------+----------+-------------+-------------+---------------+-------------+-------+----------+----------+--------+-----------+---------------+-------------+---------------------+--------------+------------+------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+----------------------+----------------------+----------------------+------+------+------+------+------+------+------+------+------+-------+-------+-------+------+---------+-------------+-------------+-------------+-------------+-------------------+-------------------+-------------------+-------------------+------------------+------------------+------------------+------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+-----------+-----------+-----------+-----------+------------+-----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+------------+------------+------------+------------+----------+-------+----------+-----------------+-----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-------------+------------+------------+------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+----------+----------+----------+-----------+-----------------+------------+---------------------+------------+--------------+----------+----------+----------+----------------+---------+-------------+---------------+----------+----------+----------+----------+-----------+----------+----------+----------+-----------+-----------------+----------+----------+----------+----------+----------+-----------+-------------+----------+---------+---------------+---------------+--------------+----------------+----------+----------+----------+----------+---------+-----------+--------------+----------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+------------+----------+----------+----------+----------+----------+----------+----------+

|  3 | Summon Corpse | PLAYER_1 |               |          |             |             |               |             | 10000 |        0 |        0 |      0 |      5000 |          2250 |       12000 |                   0 |            0 |          0 |  700 |                 70 |                  0 |                  0 |                  0 |                  0 |                  0 |                  0 |                  0 |                  0 |                   0 |                   0 |                   0 |                   0 |                   0 |                   0 |                   0 |                   0 |                   0 |                   0 |                   0 |                   0 |                    0 |                    0 |                    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |     0 |     0 |     0 | 2512 |    2106 |       17355 |          -1 |          -1 |          -1 |                 1 |                 1 |                 1 |                 1 |               -1 |               -1 |               -1 |               -1 |      100 |      100 |      100 |      100 |      100 |      100 |      100 |      100 |      100 |       100 |       100 |       100 |         0 |          1 |         0 |          0 |        91 |       254 |       254 |       254 |       254 |       254 |       254 |       254 |       254 |        254 |        254 |        254 |          6 |       20 |    14 |       -1 |               0 |         0 |      255 |      255 |      255 |      255 |       51 |      255 |      255 |      255 |      255 |       255 |        35 |       255 |       255 |       255 |       255 |       255 |          43 |          0 |          0 |                4 |        0 |        0 |        0 |        0 |        0 |        0 |        0 |        0 |        0 |        0 |        0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |      100 |        0 |      109 |       307 |               0 |          0 |                   0 |          0 |            0 |        0 |        0 |        0 |              0 |       3 |         125 |            64 |        0 |        0 |        0 |        0 |         0 |      100 |        0 |        0 |         0 |               0 |        0 |        0 |        0 |        0 |        0 |         0 |           0 |        0 |       0 |             5 |           101 |           49 |             52 |        0 |        0 |        0 |        0 |       0 |         0 |            0 |              0 |        0 |        0 |        0 |        0 |        0 |        0 |        0 |        0 |        0 |        0 |        0 |        1 |        0 |        0 |        0 |        0 |        0 |       -1 |          0 |        0 |        0 |        1 |        0 |        0 |        1 |        1 |

+----+---------------+----------+---------------+----------+-------------+-------------+---------------+-------------+-------+----------+----------+--------+-----------+---------------+-------------+---------------------+--------------+------------+------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+----------------------+----------------------+----------------------+------+------+------+------+------+------+------+------+------+-------+-------+-------+------+---------+-------------+-------------+-------------+-------------+-------------------+-------------------+-------------------+-------------------+------------------+------------------+------------------+------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+-----------+-----------+-----------+-----------+------------+-----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+------------+------------+------------+------------+----------+-------+----------+-----------------+-----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-------------+------------+------------+------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+----------+----------+----------+-----------+-----------------+------------+---------------------+------------+--------------+----------+----------+----------+----------------+---------+-------------+---------------+----------+----------+----------+----------+-----------+----------+----------+----------+-----------+-----------------+----------+----------+----------+----------+----------+-----------+-------------+----------+---------+---------------+---------------+--------------+----------------+----------+----------+----------+----------+---------+-----------+--------------+----------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+------------+----------+----------+----------+----------+----------+----------+----------+

1 row in set (0.00 sec)

I'm not trying to reinvent the wheel, I'm just trying to make it easier to work with (especially in tools & other editors) :-)

EDIT: The main reason I started towards this in the first place is that different utilities, including the PEQ editor, EQBrowser (Allakhazam clone), etc, usually have you source a trimmed copy of the spells file into the database anyways. However, they have a tendency to step over each others' toes, because some use fewer fields than others. This was to A) help alleviate the conflict, and B) make it easier to customize spell files, including tweaking the vanilla Titanium spell file (since we all know it's not perfect).

It wasnt broke, so why are you fixing it?

Youre just adding an additional level of complication IMO.

Since there already exists Spells.txt editors so loading the spells into the DB for editing isnt needed and since the client has to have the same Spells.txt file as the server for spells to work correctly for both the client and server and since this list is loaded once upon booting the world and since your still going to want to load the spells into memory no matter which method you use,for these reasons and more there doesnt seem to be any point to doing this.

Ya, that would definitely be useful for web-based tools. And if a tool similar to the Ailia/Bleh Spell Editor was made to edit the Table version, I think it could really make a nice option for spell file management. Plus, this would give the option to use mysql commands to mass edit the spell file if people wanted to do that for any reason.

The only thing I would recommend for this system would be a really simple way to convert the spell file to the table and then the table back into the spell file. Otherwise, custom servers might have issues when trying to make sure that their custom spell files match the custom spell files that their players downloaded for their server. If a tool was made similar to the Ailia/Bleh one for the Table version, it would be very nice if the same tool would also convert the file to table and visa versa.

Since the spell data is loaded globally when the server starts, I doubt this would be possible. But, it would be really cool if we could have a command that would let NPCs use any spell effect with custom values at any time. Essentially making a custom spell in real time via Perl. I think the command could use the fields in the table and then also set a value for it, and then just use the effects from the source for those fields. So, maybe something like quest::spelleffect(type, value), or maybe an extended version like quest::spelleffect(type, value, range, target_type, duration_ticks, pet_name, icon, "description", "msg_cast_on_you", "msg_cast_on_other") or something like that. Kinda off-topic idea, but sounds like fun to me :)

Quote:

Originally Posted by MNWatchdog (Post 159237)

txt based editor are ULTRA slow and have ultra high tendency to break spell file complitly. I doubt that on LIVE they store server side spell data in a text file

puting them into DB will:
a) makes need of 1 spell editor LESS
b) keeps all server related things in one place

Quote:

Originally Posted by ChaosSlayer (Post 159239)

I doubt that on LIVE they store server side spell data in a text file

Text file probably not, but data file certainly. Most professional MMOs only use a database for character and account storage. Everything else is stored in data files which gets completely loaded in the memory. Boy, I wish I had a setup that was able to do that!

That being said, what is the real advantage of this change? You do realize it will create a performance hit, right? Currently, all spells are loading into shared memory when the server first boots. I think somebody said it reloads them when a zone boots or something but that is incorrect. Spells only get loaded into memory once. If they did get loaded when a zone boots, then you would be able to edit spells on the fly, which you currently cannot. Moving this functionality to the DB is going to slow this down considerably... Having everything loaded into memory is the fastest way to do something, loading from a DB is among the slowest.

I don't think he's talking about reloading it every time someone tries to access a spell; it will still be loaded only once.

I'd say it's pretty likely they store it in a file similar to what they put on the patcher, perhaps bundled into a data archive.

Here's my thoughts on this:

The reasons for adding it to the database aren't really all that compelling if you ask me. Assuming this went into effect right now.

-You have slightly increased load on SQL server.
-You have slightly increased load times for spells.
-You have additional complexity when it comes to actually getting the spells to the client. (Now instead of modifying the spells directly you have to edit the database and then convert it.)
-You have increased database size.
-You don't really eliminate the need for a spell editor, you just lessen it if you're trying to change something simple. It will still be quite a hassle to heavily modify spells, especially many spells without one.
-You force people who wrote their spell editors to have to rewrite them for both systems, or you can continue to use them and then convert it over to the database but then what's the point.
-You've eliminated the need for a tool to need to write a spell file parser to get spells(which is incredibly simple btw).
-In my opinion you made it harder on new users who now have to try to figure out 'hey there's two systems for spells, wait do I need them both? I can't find the db table I need anywhere help!!'.

Lots of cons, a few pros. I personally just don't think it's a worthwhile idea.

I don't think AndMetal plans to have spells to be reloaded from Db over and over again evetytime zone boots or someone zones. The spell will also only load ONCE. The reason for puting server side files in DB are for purpose to work with them

Quote:

Originally Posted by KLS (Post 159244)

-You have slightly increased load on SQL server.
-You have slightly increased load times for spells.
-You have additional complexity when it comes to actually getting the spells to the client. (Now instead of modifying the spells directly you have to edit the database and then convert it.)
-You have increased database size.
-You don't really eliminate the need for a spell editor, you just lessen it if you're trying to change something simple. It will still be quite a hassle to heavily modify spells, especially many spells without one.
-You force people who wrote their spell editors to have to rewrite them for both systems, or you can continue to use them and then convert it over to the database but then what's the point.
-You've eliminated the need for a tool to need to write a spell file parser to get spells(which is incredibly simple btw).
-In my opinion you made it harder on new users who now have to try to figure out 'hey there's two systems for spells, wait do I need them both? I can't find the db table I need anywhere help!!'.

Lots of cons, a few pros. I personally just don't think it's a worthwhile idea.

Theres the more I mentioned. :p

Im guessing if youre going to do this, just do it one way and not have both methods available. All that does is double the number of spell editors that need to be maintained.

Ya, they would still be loaded into shared memory the same as they are now.

Even if the table wasn't actually used to load spells from when the server starts, it still could be a very useful table to have to spell editing. You would know every field and could quickly and easily sort by them to find all spells that use that particular field or certain effect. I think it would make finding spells for custom encounters a bit easier.

Another possible bonus to using a Table would be that it would be possible to include with the PEQ DB (though I doubt Cavedude would allow it). If it was included, it would be one less step to do when setting up a server (even though it is one of the easiest steps). And more importantly, it could be updated to correct or remove certain broken spells with the default Titanium Spell file. Not only that, but it could potentially be customized to add support for higher level spells as well as every spell from live past the point of titanium (around spell ID 8400). That wouldn't be too bad IMO. Sure, an edited spell file could be distributed with it, but an edited table is a little less iffy if you know what I mean. This possible bonus is pretty unlikely to happen, but it is something to maybe consider.

I think the main use for the table would be for web-based tools like PEQ Editor and others. With a full spell table like this, it would open up alot of options for web-tools. You could have them search for spells with certain criteria, or even go as far as editing the spells directly from the web-tool. AndMetal has a good point about multiple tools using variations of spell tables. I think his idea would be a good solution to take away the need to build extra spell tables for new tools.

I don't think it would hurt in any way for him to add this as an option to the source. Where you would need to change source before compiling by defining the spell table or something. It never hurts to have more options. If it started getting popular, maybe more positive uses for it would pop up.

Quote:

Originally Posted by trevius (Post 159248)

It never hurts to have more options.

Actually, this is often not true. Options as a rule are nice but also as a rule add complications and confusion.

This change CAN certainly be nice and allow more flexibility, but there is often a unforseen cost too.

Good example. I once wrote a macro to do some simple repetitive tasks. I enjoyed it, it did it job but I thought it could be made even more useful and I continued to develope and tweek it.

Eventually, it reached a point that I had to read my own documentation to remember how to use it as it had become so all encompasing it was no longer simple to use. Sure, it could do more, but there was a definate trade off.

PS I posted said macro after all this work and as I recall, noone seemed to use it was still to complicated and thus it was useless.

First, I would like to clarify, I have been talking about loading the spells into shared memory, not loading info on the fly from the database (that's just resource suicide).

Quote:

Originally Posted by KLS (Post 159244)

-You have slightly increased load times for spells.

I know PHP isn't the best "language" for speed, but according to preliminary testing, it actually IS faster to calculate the max spell ID & load the spells from the database than the spells_us.txt file WITHOUT caching (SQL_NO_CACHE):

PHP Code:


		
			
<?php



class Database

{

    private $connectoid = false; //Used to track this specific database connection so we don't mess with the other connections, also can be used to determine the state of the connection

    private static $user = "username";    //Username to access the Database

    private static $pass = "password";    //Password to access the Database

    private static $host = "localhost";        //IP address or hostname

    protected $db = "peq";    //Name of the Database we're accessing, can be changed if needed

    

    protected $benchmarks = Array();

    

    public function __construct() {

        if ($this->connectoid = mysql_connect(Database::$host, Database::$user, Database::$pass)) {

            mysql_select_db($this->db, $this->connectoid);

        }

    }

    

    public function __toString() {

        return "Database " . ($this->IsConnected() ? "" : "not ") . "connected";

    }

    

    public function IsConnected() {

        return ($this->connectoid ? true : false);

    }

    

    //test function, we don't want to be broadcasting our login info to everyone irl

    public function ReturnLoginInfo() {

        return "Host: " . Database::$host . "<br>\nUser: " . Database::$user . "<br>\nPass: " . Database::$pass . "<br>\nDB: " . $this->db;

    }

    

    public function Query($q) {

        $this->benchmarks[Query][result][start] = microtime(true);

        $res = mysql_query($q, $this->connectoid);    //results

        $this->benchmarks[Query][result][end] = microtime(true);

        $this->benchmarks[Query][row][start] = microtime(true);

        if ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {    //single row of data

            $this->benchmarks[Query][row][end] = microtime(true);

            $i = 0;    //iterator

            $ret = array();

            do {

                $ret[++$i] = $row;

            } while ($row = mysql_fetch_array($res, MYSQL_ASSOC));

            if (is_array($ret))    //Only return data in an array, otherwise it's an error

                return $ret;

        } else {

            return $this->GetErrorNum($this->connectoid);

        }

    }

    

    public function GetBenchmarks() {

        $this->TotalBenchmarks();

        return $this->benchmarks;

    }

    

    private function TotalBenchmarks() {

        $this->benchmarks[Query][result][total] = $this->benchmarks[Query][result][end] - $this->benchmarks[Query][result][start];

        $this->benchmarks[Query][row][total] = $this->benchmarks[Query][row][end] - $this->benchmarks[Query][row][start];

    }

    

    protected function GetErrorNum($c) {

        return mysql_errno($c);

    }

    

    public function __destruct() {

        if ($this->IsConnected())

            mysql_close($this->connectoid);

    }

}



?>

PHP Code:


		
			
<html>



<?php



ini_set("memory_limit","256M");

ini_set("max_execution_time","600");



//Automagically load Classes where needed from ../includes/ClassName.class.php

function __autoload($class_name) {

    require_once '../includes/' . $class_name . '.class.php';

}



function DatabaseGetMax() {



    $db_time_start = microtime(true);

    $db = new Database;

    $db_time_query = microtime(true);

    $db_spells = $db->Query("SELECT SQL_NO_CACHE MAX(id) FROM spells_new");

    $db_time_end = microtime(true);

    

    $db_time_total_overhead = $db_time_query - $db_time_start;

    $db_time_total_query = $db_time_end - $db_time_query;

    $db_time_total_mysql = $db->GetBenchmarks();

    $db_time_total = $db_time_end - $db_time_start;

    echo "Result: " . $db_spells[1]["MAX(id)"] . "<br>\n";

    echo "Class Time: " . $db_time_total_overhead . " seconds<br>\n";

    echo "Query Time: " . $db_time_total_query . " seconds (MySQL Time: " . $db_time_total_mysql[Query][result][total] . " seconds)<br>\n";

    echo "Total Time: " . $db_time_total . " seconds<br>\n";



}



function DatabaseLoad() {



    $db_time_start = microtime(true);

    $db = new Database;

    $db_time_query = microtime(true);

    $db_spells = $db->Query("SELECT SQL_NO_CACHE * FROM spells_new");

    $db_time_end = microtime(true);

    

    $db_time_total_overhead = $db_time_query - $db_time_start;

    $db_time_total_query = $db_time_end - $db_time_query;

    $db_time_total_mysql = $db->GetBenchmarks();

    $db_time_total = $db_time_end - $db_time_start;

    echo "Spells Loaded: " . count($db_spells) . "<br>\n";

    echo "Class Time: " . $db_time_total_overhead . " seconds<br>\n";

    echo "Query Time: " . $db_time_total_query . " seconds (MySQL Time: " . $db_time_total_mysql[Query][result][total] . " seconds)<br>\n";

    echo "Total Time: " . $db_time_total . " seconds<br>\n";



}



function FileGetMax() {



    $file_dir = "";    // needs / at the end if anything other than the current directory

    $file_name = "spells_us.txt";

    

    $file_time_start = microtime(true);

    if (file_exists($file_dir . $file_name) && is_readable($file_dir . $file_name)) {



        $file_time_load = microtime(true);

        $Lines = file($file_dir . $file_name);

        

        $file_time_parse = microtime(true);

        foreach($Lines as $key => $value) {

            $Data[$key] = explode("^", $value);

        }



        $file_time_end = microtime(true);



    } else {

        echo "Unable to read: " . $file_dir . $file_name . "<br>\n";

    }

    

    if (!$file_time_end)

        $file_time_end = microtime(true);

    

    $file_time_total_open = $file_time_load - $file_time_start;

    $file_time_total_load = $file_time_parse - $file_time_load;

    $file_time_total_parse = $file_time_end - $file_time_parse;

    $file_time_total = $file_time_end - $file_time_start;

    echo "Result: " . $Data[count($Data)-1][0] . "<br>\n";

    echo "Open Time: " . $file_time_total_open . " seconds<br>\n";

    echo "Load Time: " . $file_time_total_load . " seconds<br>\n";

    echo "Parse Time: " . $file_time_total_parse . " seconds<br>\n";

    echo "Total Time: " . $file_time_total . " seconds<br>\n";





}



function FileLoad() {



    $file_dir = "";    // needs / at the end if anything other than the current directory

    $file_name = "spells_us.txt";

    

    $file_time_start = microtime(true);

    if (file_exists($file_dir . $file_name) && is_readable($file_dir . $file_name)) {



        $file_time_load = microtime(true);

        $Lines = file($file_dir . $file_name);

        

        $file_time_parse = microtime(true);

        foreach($Lines as $key => $value) {

            $Data[$key] = explode("^", $value);

        }



        $file_time_end = microtime(true);



    } else {

        echo "Unable to read: " . $file_dir . $file_name . "<br>\n";

    }

    

    if (!$file_time_end)

        $file_time_end = microtime(true);

    

    $file_time_total_open = $file_time_load - $file_time_start;

    $file_time_total_load = $file_time_parse - $file_time_load;

    $file_time_total_parse = $file_time_end - $file_time_parse;

    $file_time_total = $file_time_end - $file_time_start;

    echo "Spells Loaded: " . count($Lines) . "<br>\n";

    echo "Open Time: " . $file_time_total_open . " seconds<br>\n";

    echo "Load Time: " . $file_time_total_load . " seconds<br>\n";

    echo "Parse Time: " . $file_time_total_parse . " seconds<br>\n";

    echo "Total Time: " . $file_time_total . " seconds<br>\n";

    

}



echo "<b>DatabaseGetMax()</b><br>\n";

DatabaseGetMax();

echo "<br>\n";

echo "<b>DatabaseLoad()</b><br>\n";

DatabaseLoad();

echo "<br>\n";

echo "<b>FileGetMax()</b><br>\n";

FileGetMax();

echo "<br>\n";

echo "<b>FileLoad()</b><br>\n";

FileLoad();



?>



</html>

Code:

DatabaseGetMax()

Result: 11999

Class Time: 0.000842094421387 seconds

Query Time: 0.000253915786743 seconds (MySQL Time: 0.000201940536499 seconds)

Total Time: 0.00109601020813 seconds



DatabaseLoad()

Spells Loaded: 11540

Class Time: 0.000181913375854 seconds

Query Time: 1.7691822052 seconds (MySQL Time: 0.547831058502 seconds)

Total Time: 1.76936411858 seconds



FileGetMax()

Result: 11999

Open Time: 3.50475311279E-05 seconds

Load Time: 0.0381419658661 seconds

Parse Time: 0.978446960449 seconds

Total Time: 1.01662397385 seconds



FileLoad()

Spells Loaded: 11540

Open Time: 3.38554382324E-05 seconds

Load Time: 0.0379700660706 seconds

Parse Time: 0.978180885315 seconds

Total Time: 1.01618480682 seconds

However, if we are talking about JUST loading the spells (which can't be done, since we need to know what the highest spell ID is before we load them into shared memory), loading from a file is considerably faster.

Quote:

Originally Posted by KLS (Post 159244)

-In my opinion you made it harder on new users who now have to try to figure out 'hey there's two systems for spells, wait do I need them both? I can't find the db table I need anywhere help!!'.

That's why I'm not sure if it would be better to give the option or to shove it down everyone's throat (I prefer the former). I guess the expectation would be to switch it over to completely DB. Giving the options would just give an "easier" transition in the short term.

Quote:

Originally Posted by MNWatchdog (Post 159237)

It wasnt broke, so why are you fixing it?

Youre just adding an additional level of complication IMO.

Why have a database if we're not going to take advantage of it? Yes, we can have the spells stored in a file, but we could also have items, spawns, NPC types, merchant lists, spell lists, AAs, zones, zone servers, etc all in files. But that's just crazy. That's why we have a variables system & a rules system instead of config files like with the MaNGOS emulator (for WoW).

I think it's a good idea as a supplement to tools but again I'm not sure it's the greatest idea for the base server. I don't want to discourage you if you really want to put forth the effort to not only create the database but create the tools to input and extract spells from it.

However I don't see much reason to actually put it in the server standalone is what I was trying to say, having a unified standard for tools would simplify their design considerably.

Theres a millions ways to trap a mouse, but the snap trap works with 95% efficiency.

Personally, I think you should change to the DB method and it only with a importer and exporter app for the spells.txt file.

Quote:

Originally Posted by MNWatchdog (Post 159252)

Personally, I think you should change to the DB method and it only with a importer and exporter app for the spells.txt file.

I think that's the route I'll go.

I'll probably just make a Perl script to do both (since not everyone has PHP installed on their server systems), although I'm already to the point where I have about 80% of the base Titanium spell file in a .sql file that can be easily sourced (80% being the table structure, 20% importing it from the spells file).

Quote:

Originally Posted by AndMetal (Post 159236)

Your very first post said something about modifying spells which seemed to me to be the pita way.

Quote:

Originally Posted by AndMetal (Post 159253)

I'll probably just make a Perl script

http://code.google.com/p/projecteqem...port_spells.pl

Code:

Usage: import_spells.pl [-c path] [-s path] [-t table] [-d]

  -c path       path/to/eqemu_config.xml. defaults to eqemu_config.xml

  -s path       path/to/spells_us.txt. defaults to spells_us.txt

  -t table      table to load the spells into. defaults to spells_new

  -d            erase all spells from the database first

Tested it out several times and it works fine, including the erasing of the table.

I feel there are probably some opportunities to clean up the script a little (I've never worked with DBI before), so if anyone has any recommendations, let me know (or if you have SVN access, feel free to change it).

Just finished up the export script:

Code:

Usage: import_spells.pl [-c path] [-t table] [-i column] [-s path] [-o]

  -c path       path/to/eqemu_config.xml. defaults to eqemu_config.xml

  -t table      table to load the spells from. defaults to spells_new

  -i column     name of the column in the database table to order by. defaults to id

  -s path       path/to/export/spells_us.txt. defaults to spells_us.txt

  -o            overwrite spells_us.txt if it exists

The is also now officially in SVN as of revision 230. If anyone is confused about what to do, let me know.

Can you give an example of using the script? Also, how do you disable using this so it continues to use the normal spells file? From reading the changelog, it sounds like it is supposed to use this table by default. I am guessing that means it will ignore the spells file unless you disable this feature? Or, will it use the spells file if it can't find the table?

BTW, your code box in your export script post says "import", not "export" for usage.

So, to import spells into the database, I would do this?:

Code:

/utils/import_spells.pl -c /home/eqemu/server/eqemu_config.xml -s /home/eqemu/server/spells_us.txt -t spells_new -d

And to export the table, I would do this?:

Code:

/utils/export_spells.pl -c /home/eqemu/server/eqemu_config.xml -t spells_new -s /home/eqemu/server/spells_us.txt

EDIT: Just tried to run this, but I haven't ran a .pl file before and it doesn't seem to run just from a terminal window. Is there something else I need to do to run it?

Code:

eqemu@muse:~/source/EQEmuServer2/utils$ ls 

0.6.1-upgrade.sql  items-0.6.0-DR2-0.6.1-DR1-convert.sql  ppskillfix

apathing           itemtablechanges.sql                   schema.xml

asmtools           load_13thfloor_items.pl                serialize_items.pl

azone              Makefile                               spell_explorer.cpp

azone2             opcodes.conf                           sql

charmove           patch_6.2.conf                         struct_fields.sh

cleanipc.cpp       patch_Anniversary.conf                 TaskMaster

defaults           patch_Live.conf                        throwpackets.pl

export_spells.pl   patch_Titanium.conf                    wr_update.sql

hexvis             perlxs                                 ZONECFG.SQL

import_showeq      ppconvert

import_spells.pl   ppreader.pl

eqemu@muse:~/source/EQEmuServer2/utils$ import_spells.pl -h bash: import_spells.pl: command not found

eqemu@muse:~/source/EQEmuServer2/utils$ ./import_spells.pl -h

bash: ./import_spells.pl: /usr/bin/perl^M: bad interpreter: No such file or directory

eqemu@muse:~/source/EQEmuServer2/utils$

should be able to just

Code:

sh export_spells.pl

but i don't have a linux box near me atm

This is what I get from sh:

Code:

eqemu@muse:~/source/EQEmuServer2/utils$ sh export_spells.pl -h

: command not foundine 2:

export_spells.pl: line 5: use: command not found

: command not foundine 5:

export_spells.pl: line 6: use: command not found

: command not foundine 6:

: command not foundine 7:

export_spells.pl: line 9: syntax error near unexpected token `'c:t:i:s:oh''

'xport_spells.pl: line 9: `getopts('c:t:i:s:oh');

eqemu@muse:~/source/EQEmuServer2/utils$ sh import_spells.pl -c /home/eqemu/server/eqemu_config.xml -s /home/eqemu/server/spells_us.txt -t spells_new -d

: command not foundine 2:

import_spells.pl: line 5: use: command not found

: command not foundine 5:

import_spells.pl: line 6: use: command not found

: command not foundine 6:

: command not foundine 7:

import_spells.pl: line 9: syntax error near unexpected token `'c:s:t:dh''

'mport_spells.pl: line 9: `getopts('c:s:t:dh');

eqemu@muse:~/source/EQEmuServer2/utils$

And, to set it back to load the normal spell file, I am guessing I need to modify this part of the spdat.h?:

Code:

//#define NEW_LoadSPDat

#define DB_LoadSPDat        //load from DB vs spells_us.txt. for now, we're piggybacking NEW_LoadSPDat, so it will take precedence

Or does that mean this is still disabled by default? Change log says enabled by default, so I am not understanding. Do I just need to comment out #define DB_LoadSPDat ? I can't even get it to compile if that line is commented out. If loading spells from the DB is now enabled by default, and it doesn't automatically use the spells_us.txt file if the spells_new table isn't available, then I think this needs to be disabled ASAP. Or we at least need a complete noobie guide to setting it up, because I know that if I am having trouble with it, many others will too.

Quote:

Originally Posted by jenco420 (Post 160473)

should be able to just

Code:

sh export_spells.pl

but i don't have a linux box near me atm

egads! Don't feed it to shell. Make sure the script is at least executable, ie 755 or whatever you want it to be and make sure the first line of the script points to the location of your perl executable. Depending on you distro and how you installed it, it could be in either /usr/bin or /usr/local/bin, unless you installed it intentionally in another location.

Edit: Looked at your error and it seems to be picking up the ^M from the DOS file type. You can edit those out or you can run dos2unix to remove them.

It's a perl script, not a shell script so use:

Code:

perl export_spells.pl

It'll work on either Windows or Linux of course.

Not sure if anybody would use it, but here is the sql dump of the spells file: http://projecteq.net/cavedude/spells_new.zip Source utils/230_spells_table.sql first for the schema, as that is just data. It'll appear as a system table in PEQ CVS.

Just for the future: I'd rather you turn large (potentially game breaking) features off by default at least at first when they coexist with previous implementations.