Crazy idea for spells... [Archive]

View Full Version : Crazy idea for spells...

AndMetal

01-17-2008, 10:34 PM

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?

ChaosSlayer

01-18-2008, 05:15 AM

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

AndMetal

01-18-2008, 08:05 AM

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.

AndMetal

01-21-2008, 09:04 AM

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.

Davood

01-28-2008, 12:11 AM

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 !

narcberry

01-28-2008, 03:25 AM

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.

AndMetal

09-10-2008, 02:20 PM

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 (http://eqemulator.cvs.sourceforge.net/eqemulator/EQEmuCVS/Source/zone/net.cpp?view=markup). 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 (http://eqemulator.cvs.sourceforge.net/eqemulator/EQEmuCVS/Source/zone/zonedb.cpp?view=markup) 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?

So_1337

09-10-2008, 02:32 PM

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.

KLS

09-10-2008, 07:35 PM

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.

AndMetal

09-11-2008, 02:18 PM

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.

KLS

09-11-2008, 11:24 PM

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

AndMetal

10-27-2008, 07:38 PM

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.

Congdar

10-27-2008, 09:04 PM

AndMetal

10-27-2008, 09:17 PM

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.

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).

Congdar

10-27-2008, 10:16 PM

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'.

ChaosSlayer

10-27-2008, 10:49 PM

AndMetal

10-27-2008, 11:22 PM

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).

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:
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^25 5^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^5 2^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:
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).

MNWatchdog

10-28-2008, 12:01 AM

trevius

10-28-2008, 12:08 AM

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 :)

ChaosSlayer

10-28-2008, 12:08 AM

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.

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

cavedude

10-28-2008, 12:15 AM

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.

KLS

10-28-2008, 12:38 AM

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.

ChaosSlayer

10-28-2008, 12:38 AM

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

MNWatchdog

10-28-2008, 01:00 AM

-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

MNWatchdog

10-28-2008, 01:03 AM

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.

trevius

10-28-2008, 01:43 AM

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.

MNWatchdog

10-28-2008, 03:03 AM

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.

AndMetal

10-28-2008, 03:19 AM

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).

-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 (http://dev.mysql.com/doc/refman/5.0/en/query-cache-in-select.html)):

<?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 . " \nUser: " . Database::$user . " \nPass: " . Database::$pass . " \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);
}
}

?>

<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)"] . " \n";
echo "Class Time: " . $db_time_total_overhead . " seconds \n";
echo "Query Time: " . $db_time_total_query . " seconds (MySQL Time: " . $db_time_total_mysql[Query][result][total] . " seconds) \n";
echo "Total Time: " . $db_time_total . " seconds \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) . " \n";
echo "Class Time: " . $db_time_total_overhead . " seconds \n";
echo "Query Time: " . $db_time_total_query . " seconds (MySQL Time: " . $db_time_total_mysql[Query][result][total] . " seconds) \n";
echo "Total Time: " . $db_time_total . " seconds \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 . " \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] . " \n";
echo "Open Time: " . $file_time_total_open . " seconds \n";
echo "Load Time: " . $file_time_total_load . " seconds \n";
echo "Parse Time: " . $file_time_total_parse . " seconds \n";
echo "Total Time: " . $file_time_total . " seconds \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 . " \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) . " \n";
echo "Open Time: " . $file_time_total_open . " seconds \n";
echo "Load Time: " . $file_time_total_load . " seconds \n";
echo "Parse Time: " . $file_time_total_parse . " seconds \n";
echo "Total Time: " . $file_time_total . " seconds \n";

}

echo "DatabaseGetMax() \n";
DatabaseGetMax();
echo " \n";
echo "DatabaseLoad() \n";
DatabaseLoad();
echo " \n";
echo "FileGetMax() \n";
FileGetMax();
echo " \n";
echo "FileLoad() \n";
FileLoad();

?>

</html>

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.

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

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).

KLS

10-28-2008, 03:28 AM

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.

MNWatchdog

10-28-2008, 03:34 AM

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.

AndMetal

10-28-2008, 05:15 AM

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).

Congdar

10-28-2008, 08:51 AM

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.

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

AndMetal

11-18-2008, 08:00 AM

I'll probably just make a Perl script

http://code.google.com/p/projecteqemu/source/browse/branches/DB_LoadSPDat/utils/import_spells.pl

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).

AndMetal

11-24-2008, 07:13 AM

Just finished up the export script (http://code.google.com/p/projecteqemu/source/browse/branches/DB_LoadSPDat/utils/export_spells.pl):

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.

trevius

11-24-2008, 08:34 AM

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?:

/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?:

/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?

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$

jenco420

11-24-2008, 08:55 AM

should be able to just

sh export_spells.pl

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

trevius

11-24-2008, 09:22 AM

This is what I get from sh:

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?:

//#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.

Andrew80k

11-24-2008, 11:57 AM

should be able to just

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.

cavedude

11-24-2008, 01:46 PM

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

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.

KLS

11-24-2008, 03:35 PM

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.

cavedude

11-24-2008, 03:53 PM

It doesn't look like it can be disabled. :( Without the table, zones crash as soon as a spell is cast, with a blank table, the zones won't boot at all.

leslamarch

11-24-2008, 04:36 PM

Andrew80k

11-24-2008, 04:44 PM

trevius

11-24-2008, 07:33 PM

Is it possible to retract an SVN submission? Would be pretty awesome to just be able to remove it and not have to either manually remove everything added in it, or commit an older version to revert it.

I was hoping to get the new food stats fix in that I got done last night, but couldn't because of the problems with this.

AndMetal

11-25-2008, 02:03 AM

Sheesh, I go away for 17 hours and everyone freaks out...

Anyways, to address the questions:

Can you give an example of using the script?
cavedude pretty much got it. I would recommend using the -h (for [h]elp) switch, recommended in the changelog:

==11/24/2008==
AndMetal: Spells can now be loaded from the database, and is now enabled by default. Use utils/import_spells.pl to import, utils/export_spells.pl to export. Use -h on either for usage.

Required SQL: utils/sql/svn/229_spells_table.sql

perl import_spells.pl -h

perl export_spells.pl -h

which gives the usage (which I also posted above):

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

Usage: export_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

By default, it will look for both eqemu_config.xml & spells_us.txt in the current directory (as indicated in the usage info for the switches, since they don't have any /'s before the names of the files), so you could easily just execute it from your server directory. For me, I would end up using something like this, cause even though symbolic links are awesome, I don't really like making them a lot:

eqemu@amsk-server:~/svn/EQEmuServer/utils$ perl import_spells.pl -c ~/server/eqemu_config.xml -s ~/server/spells_us.txt -d

eqemu@amsk-server:~/svn/EQEmuServer/utils$ perl export_spells.pl -c ~/server/eqemu_config.xml -s /var/www/eqemu/spells_us.txt -o

Then, you can just add the export to your startup script so it exports at runtime if you wanna be one of the cool kids :-)

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?
The idea is this will replace using the spells file. The only reason I didn't just straight take out the old stuff is so everyone can continue to use it if they want to (just like turning bots on, SPELL_EFFECT_SPAM, etc) by changing the #defines in zone/spdat.h (http://code.google.com/p/projecteqemu/source/browse/trunk/EQEmuServer/zone/spdat.h#42):
//#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

One change I might make would be to create a default case if neither are defined:

//#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
#if !defined(NEW_LoadSPDat) && !defined(DB_LoadSPDat)
#define NEW_LoadSPDat
#endif

However, then we're completely rid of the real old way of doing it (lots of hard coding). I honestly don't think anyone's using that old code, but who knows.

BTW, your code box in your export script post says "import", not "export" for usage.
Minor typo. I was copying/pasting the top half of the scripts, and forgot to make that change.

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.

I've actually been using this code, successfully, for just shy of a month (since October 27th to be specific). The only thing that took a little time was the scripts to import/export the spells (having a kid in the middle of all that did slow things down a bit). Not only that, but the main thing it's doing is loading spells into the spells[] "array" from the database instead of the spells_us.txt file. There were also a few unknown values in the SPDat_Spell_Struct struct that I came across, but I made sure it worked with the spells_us.txt file before I even tested with the database (which caused problems with sharedmem a few months ago when I first started working on it). I personally don't consider it to be a "large feature", rather just a bunch of copying, pasting, & tweaking of existing code.

Been trying the export script for 2 hours now with no luck :-x

perl export_spells.pl
install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains:
C:/Perl/site/lib C:/Perl/lib .) at (eval 4) line 3, <F> line 36.
Perhaps the DBD::mysql perl module hasn't been fully installed,
or perhaps the capitalisation of 'mysql' isn't right.
Available drivers: DBM, ExampleP, File, Gofer, Proxy, SQLite, Sponge, mysqlPP.
at export_spells.pl line 62*waves the white flag* :x

I'm running Debian & have libdbi-perl & libdbd-mysql-perl. The scripts use DBI, so Perl probably just assumes MySQL when trying to connect to the server (automatically negotiated maybe?):

use DBI;
use Getopt::Std;

As far as I can tell, both of the DB packages are installed automatically when installing the MySQL 5.0 server (http://packages.debian.org/etch/mysql-server-5.0), again at least on Debian.

I guess the question now is anyone actually having problems after getting the spells into the database? Because other than that, I'm not sure what all the ruckus is about...

I know it compiles, I know it runs, I know it works, and made sure to test it thoroughly before I got to this point. I even just checked my launcher status page & haven't had any zone crashes since my server was booted up at 6:28am today.

AndMetal

11-25-2008, 02:28 AM

Got to talk to trev, and it appears there is a compiling issue when setting to NEW_LoadSPDat. Looks like I missed some +'s when I was copying from a diff (thanks again Google!):
zone/net.cpp (http://code.google.com/p/projecteqemu/source/browse/trunk/EQEmuServer/zone/net.cpp?spec=svn230&r=230#1066)

+ // May crash zone
+ /*
+ sp[tempid].nodispell=atoi(row[186]);
+ sp[tempid].npc_category=atoi(row[187]);
+ sp[tempid].npc_usefulness=atoi(row[188]);
+
+ for (y = 0; y < 18; y++)
+ sp[tempid].spacing189[y]=atoi(row[189+y]);
+
+ sp[tempid].spellgroup=atoi(row[207]);
+
+ for (y = 0; y < 18; y++)
+ sp[tempid].spacing208[y]=atoi(row[208+y]);
+ */

Took 'em out, recompiled with NEW_LoadSPDat, and works fine.

EDIT: This is in SVN as Rev 232 (http://code.google.com/p/projecteqemu/source/detail?r=232).

KLS

11-26-2008, 04:18 PM

I've actually been using this code, successfully, for just shy of a month (since October 27th to be specific). The only thing that took a little time was the scripts to import/export the spells (having a kid in the middle of all that did slow things down a bit). Not only that, but the main thing it's doing is loading spells into the spells[] "array" from the database instead of the spells_us.txt file. There were also a few unknown values in the SPDat_Spell_Struct struct that I came across, but I made sure it worked with the spells_us.txt file before I even tested with the database (which caused problems with sharedmem a few months ago when I first started working on it). I personally don't consider it to be a "large feature", rather just a bunch of copying, pasting, & tweaking of existing code.

You don't, but I do. It interacts with a core part of the server functionality so I would consider it big even if it doesn't seem all that complex. Also given that there are no real benefits yet (it's all a idea for future expansion) I'd rather retain backwards compatibility right now. People who want to use the new system can use it but until we start getting applications that take advantage of it I'm not gonna force people.

I'm glad you guys got it all sorted out before I got back, kinda hard to fix stuff with svn when on the road =/ Keep working with this it has potential even if we haven't found it yet.

trevius

11-26-2008, 05:55 PM

Probably the biggest benefit is that web-based tools could be made for editing spells online. It might also be useful that we can have code that is able to check the table for certain things. I am sure there is more potential out there for it. If there was a way to convert the Ailia/Bleh spell editor to work with the new table, I think it would leave little need for the spell file. But, unless that table is added into the PEQ and AX databases, we would also need directions in the server setup wiki for new admins to set this up for their server.

Hmm, I think I might have come up with a good use for it already lol. Would it be possible to add 1 more column to the end that will let us enable/disable the spell from loading (enabled by default)? So, instead of removing a spell or setting it to be unusable by all classes, we could simply disable it. Maybe even having an extra "comments" field next to that one for notes about the spell, if it has been changed or removed for any reason. Or, if you wanted to replace the spell, you could put the old Spell ID number in the comments field and then change it to an unused Spell ID so that you can reuse the one it was previously using for another spell. Although, I imagine that would make the import export stuff hard to do and stay sorted properly with the comments and enabled/disabled field.

The reason I mention this idea is because all classes are capped at 400 spells max (by the client as far as I can tell). I tried changing the settings in the source, but it didn't add more than 50 pages to the player spell books. Since I have spells up to level 75 on my server, Druids exceed the 400 cap and lose access to many of the high level spells due to lack of a slot in the spell book. If I could easily disable spells in the table like mentioned, I could just go through and disable a bunch of unused ones like ports to new/locked zones etc.

cavedude

11-26-2008, 06:05 PM

But, unless that table is added into the PEQ and AX databases, we would also need directions in the server setup wiki for new admins to set this up for their server.

It's already in PEQ CVS.

KLS

11-26-2008, 07:19 PM

Yeah there's benefit but no one's tapped it yet + it's a new feature so I'm keeping it off by default.

AndMetal

11-27-2008, 06:41 PM

Yeah there's benefit but no one's tapped it yet + it's a new feature so I'm keeping it off by default.

I can agree to keeping it off by default. The main reason I turned it on by default was because I was getting the feeling from the feedback I was getting that, if I made the effort to make the spells relatively easy to import/export, that I should. However, I think the better way to do it (which is how I was leaning originally, but decided to take a little initiative) is what you recommend, which is to turn it off by default. If others start using it & it becomes more "mainstream", it's easy enough to change. At the very least, now everyone can use the same spells table for their tools :D

trevius

11-29-2008, 02:48 AM

AndMetal

11-29-2008, 02:56 AM

I run a custom spell file and when I ran the import script, I got this:

eqemu@muse:~/server$ perl import_spells.pl
Loading "Focus Ornate Spellcaster\'s Empowering Essence" (8447) Loading "Focus Runed Spellcaster\'s Empowering Essence" (8448) Loading "Focus Mass Elaborate Spellcaster\'s Empowering Essence" (8449) Loading "Focus Mass Ornate Spellcaster\'s Empowering Essence" (8450) Spells Loaded: 13373 of 13373
Highest ID: 19507

Do those messages mean it changed the name of the spells? Or are they anything to be concerned about?

The "Loading" info is more so you can see what the status is, although it goes so fast that you normally don't get to see pretty much any of it. I currently have it print the spell name after inserting the escape character (\) for the query to insert it into the database, mainly because it's the easiest, which is where the confusion about the name might be. Since it indicates it loaded all 13373 of the 13373 spells, it should be fine, although I'm not really sure why all of those ended up back-to-back on a single line instead of erasing each other. If there was an error loading 1 or more spells, it would output a line letting you know which one failed (1 line for each spell that failed to load).

Does that help to answer that question?

cavedude

11-29-2008, 11:48 AM

That happens with the item import script too (of which this is based off of I guess) I've never had a problem with the items it named, and as long as it claims to have loaded all the data it's fine. I would assume the same goes for the spell script.

AndMetal

11-29-2008, 02:16 PM

That happens with the item import script too (of which this is based off of I guess)

It was :-)

John Adams

11-30-2008, 05:18 PM

After reading this whole thread, and being convinced this feature was "off by default", I updated to the latest SVN, did the SQL and import_spells.pl. Now my server is broken. Try to mem a spell and get a message "Unexpected error: spell id out of range" and now I cannot click on my spell book at all.

Without reverting to an earlier SVN, is there a way to disable this completely (for real) and use the spells_*.txt way?

Please don't remove the old way just because 3 people have tried it and think it works.

John Adams

11-30-2008, 05:24 PM

Now my server is broken.
Ya know, the 30 second edit on this forum is bullshit, always hated it. :)

What I meant to say, "servers Spells are broken". The server itself appears ok.

Derision

11-30-2008, 05:25 PM

I think you use Linux, so try a 'make clean' and then 'make'.

John Adams

11-30-2008, 05:51 PM

Yes I do use linux for this server. I keep forgetting about the make clean (LE probably wants to hit me with a rotten tuna as well over this :))

I'll give it a try.

John Adams

11-30-2008, 06:04 PM

Ugh... ~blush~ make clean once again makes a fool of me ;) Thanks Derision for the reminder. I need to nail a sticky to my forehead (though I still cannot understand the need to whack the old compile for new changes).

Spells do work with this new table, the import thingy, and "off by default" which I will eventually learn how to turn this on and give it a try. Just not today. :)

Thanks AndMetal, sorry if my initial post sounded all assy.

AndMetal

12-01-2008, 02:31 AM

Thanks AndMetal, sorry if my initial post sounded all assy.

Np :)

It's probably something worth mentioning in the changelog, since the structure of the spells (for shared memory) changed since we discovered some of the unknown fields, I just haven't gotten off my lazy ass to update it, lol.

KLS

12-01-2008, 05:29 AM

How has this been working since the initial stumbles? Flawlessly I hope?

If it's working well I actually have an idea I'd like to implement next time I get some free time dealing with this (sekret idea but it's really cool trust me).

trevius

12-01-2008, 05:44 AM

AndMetal

12-02-2008, 03:36 AM

Well, the new DB Spells might be working ok, but I found a major issue with the current SVN revision that basically breaks spells if you try to use the old spells_us.txt way. The spells all still cast properly, but for some reason, they take 4X as long to cast either from items or just from casting a spell. There were so many changes that went in with the new spell changes AndMetal made, that I don't have any idea where to start looking for the cause of this.

I will probably move to the new DB version soon, but as long as the old way still exists, it should at least work :P

I just did quite a bit of testing with SVN Rev 232 (http://code.google.com/p/projecteqemu/source/detail?r=232) & 235 (http://code.google.com/p/projecteqemu/source/detail?r=235) using the unmodified Titanium spell file, trev's slightly custom spell file (http://stormhavenserver.com/wordpress/?page_id=3), and an outdated version of Live's spell file and have yet to reproduce the casting delay either by casting from clickies or from the spell bar. For the clickies, I used a Brown Ornate Chain Bridle (http://lucy.allakhazam.com/item.html?id=21819) & Fabled Jaundiced Bone Bracer (http://lucy.allakhazam.com/item.html?id=82715). I also used a combination of Ice Age (http://lucy.allakhazam.com/spell.html?id=5408) & Greater Immobilize (http://lucy.allakhazam.com/spell.html?id=3195) for casting spells.

The only thing I can think of is if there was a problem sourcing the spells into the database.

I've said it before, I'll say it again:
I guess the question now is anyone actually having problems after getting the spells into the database? Because other than that, I'm not sure what all the ruckus is about...

trevius

12-02-2008, 06:07 AM

Oops, it looks like the issue must somehow be on my end. Sorry that I was too quick to assume that the spell changes you made were the cause of it. Apparently, it is an issue with the test server I am running and it happens in all builds I have tried so far and I don't know why. The test server is a Debian build running on VMWare on my Windows PC. I don't think that should cause a problem, but who knows? The only thing I suspect is that it could be due to me running 2 servers at the same time from the same internet connection. I know I can't forward the required ports to the server needed for players to log in, but it works fine for me to log in, since I am on the same LAN and don't get restricted by ports. Maybe I somehow missed something else important in the build, but I have never heard of this issue before.

I did a bit more testing on it and it seems that spells are taking exactly 7 times longer to cast than what their cast time says on the spell or clickie item. I have no idea why it is 7X, but that is what happens every time for all spells. I have tried both the old spells file way and the new DB spells way and both have the exact same issue. That is an issue for another post though, so I am not asking for answers in this one.

Sorry to derail the thread and my apologies to AndMetal for making an assumption without better testing.

To keep this somewhat on topic, I must admit that it is awesome to be able to sort the new spells table by any column to quickly check for certain things in spells. It made finding all of the mounts in the file a very simple and quick task. Once a tool comparable to the Ailia/Bleh spell editor is created for working with the new DB spells, I don't see any need to keep the old system around. I am sure we will find even more benefits from it soon.

erik_llewellyn

01-17-2009, 02:33 AM

Been trying the export script for 2 hours now with no luck :-x

perl export_spells.pl
install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains:
C:/Perl/site/lib C:/Perl/lib .) at (eval 4) line 3, <F> line 36.
Perhaps the DBD::mysql perl module hasn't been fully installed,
or perhaps the capitalisation of 'mysql' isn't right.
Available drivers: DBM, ExampleP, File, Gofer, Proxy, SQLite, Sponge, mysqlPP.
at export_spells.pl line 62*waves the white flag* :x

This error is caused because the DBD-mysql perl module is NOT installed. I ran into this issue ever since the Nov update and could not get the import_spells.pl or export_spells.pl scripts to run. After getting around to conducting some research on it now that my time has freed up, the issue seems to be that the default PPM package availability list no longer has DBD-mysql listed as an option. The DBD-mysqlPP will NOT WORK for the sake of the spell scripts and you must install the normal DBD-mysql module. To install it, follow these steps:

1. open the Perl Package Manager
2. click the Edit menu - Preferences
3. click the Repositories tab
4. In the location box type "http://theoryx5.uwinnipeg.ca/ppms/"
5. Click Add
6. Click OK
7. Install the DBD-mysql package

OR

1. open a command line prompt
2. type "ppm install http://theoryx5.uwinnipeg.ca/ppms/DBD-mysql.ppd
"
3. Press Enter

At this point, the import/export scripts will run correctly.

trevius

02-24-2009, 01:08 AM

Since I think this is now a proven alternative systems for spell management, I was wondering if we should consider making it on by default. Otherwise, maybe we could set a rule that will allow admins to chose which type of spell system they want to use (the file or the table) and only have to set it the one time in the rules table instead of changing the define each time before updating the code on their server. Any ideas or input?

ChaosSlayer

02-24-2009, 01:26 AM

will players still need to have their own spell file if spells will now be loaded into DB?

trevius

02-24-2009, 02:17 AM

will players still need to have their own spell file if spells will now be loaded into DB?

Yes, this changes nothing about how the client handles spells. It just lets the server load spells directly from the database instead of from the spells_us.txt file. Basically, it doesn't really make much of a difference yet which way they load them. There is alot of potential with loading from the DB though. For one, it means you could download the PEQ database and have spells included with it so you don't have to put move the spells file from your EQ directory over. That is fairly minor, but 1 less step in the setup process. A big benefit of having it in a table like this is that you can give your GMs access to the table and they can edit it very easily to fix broken spells or make custom ones. Kayen has made quite a few adjustments to the custom spells on Storm Haven already. Many changes he makes doesn't require an update to the spells file that the clients use. It all depends on what changes you are making and to what spells.

The main benefit I can think of for loading spells into the database with this system would be that we can add extra features for spells that just aren't an easy option for the spells_us.txt way. My first idea for this is to create 2 new columns in the spells table at the end of it that will allow you to set a name and a value that could maybe be tied into quest globals. Basically, the idea is that the default name and value might be something like 0 for those fields, and anyone will gain those spells when using the #scribespells command or a spell scriber quest. But, any spells that are set to another name or value won't be scribed unless that player has that particular global with the same value or greater. So, basically, you could create tiers of spells or define special spells that will only be able to be learned if the player has earned them via a quest or whatever. I think it would be a great system and shouldn't be all that hard to create using the new database spells system and quest globals. That would be pretty tough to do/manage if spells weren't loaded into the database like they can be now.

Unfortunately, I won't have time to even think about working on that new system for quite a while. But, it is definitely something I would like to get going at some point.

Another quick idea to make use of the new database spells would be to create an additional field that would let admins set certain spells to be automatically converted into another spell if it was cast between a Titanium and a SoF client. This would be useful to correct any discrepancies between spell files of the 2 clients and also would be useful for servers with custom spell files. I don't even know if this idea is possible, but it would be pretty cool if so. The field could be called something like SoF_SpellID and you would just put the spell id of the spell that you want the SoF client to use in place of what the Titanium client is casting or visa versa. There could probably also be a Titanium_SpellID column added as well that would convert an SoF spell into a Titanium spell ID. So, say that the buff Temperance was spell ID 100 in Titanium, but spell ID 200 in SoF, by setting 200 in the SoF_SpellID field, it would convert the spell ID to the correct one so that the correct effect happens on SoF. Though, this might require 2 spells tables, one for Titanium and one for SoF. They would have to both be loaded into memory though. I think with 2 spells tables, it might actually not be too hard to do a conversion system like this though. Just an idea anyway.

trevius

06-14-2009, 07:24 AM

If no one has an objection, I am going to finally change the default to the spells_new table as apposed to loading spells from the spells_us.txt file tonight. I see no reason not to change it now, anyway.

Of course, anyone still using the spell file will either need to make the switch to using the table, or will need to change the define before compiling. I think Cavedude have been changing the default before compiling the binaries anyway, so no reason not to change it in the source. The old way will still be there and will probably remain there indefinitely or until it is decided that we never have need for it again.

Let me know if there are any objections before I change it tonight. If a reason does pop up later, it can always be changed back easily.

ChaosSlayerZ

06-14-2009, 01:11 PM

ah Trev, I may have a small objection :cool:
One thing about workign with spells, that txt file is far easier to swap around when something needs to be changed FAST. (like copy and paste for entire chunk of lines)

So instead of a define- could you make this option into a Rule instead? this won't even give an overhead since all spells load only ONCE when sever starts.

trevius

06-14-2009, 05:15 PM

To change it to a rule would probably mean adding in duplicate of a ton of code in multiple files. I don't really think it is a good option or it probably would have been done already.

If you need to do some major changes like that, you can easily use the import and export scripts to export the spell file from the spells_new table, change it and them import it back into the table. That is what the scripts were made for :)

Another reason that the table is a really good way to go is because if people export it to create their server's spell file, the exported file will work perfectly with both Titanium and SoF, since it has the correct number of fields to function with both clients.

I think the binaries have been set to use the table loading for a while now. So, if you use those, you would have been stuck with the table loading anyway. If you don't want to use the table loading way, it is very simple to change the source to use the file loading way instead and then compile your own binaries. All you have to do is go to zone/spdat.h and change the following:

//#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

to this
#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

ChaosSlayerZ

06-14-2009, 05:56 PM

Trev, isn't the both codes allready there for #define to be used?

binaries wise - they stil were using spells.txt as of R535 i last dowloaded - how else would i have been using my own spells =P (speaking of which its time to update the binaries on the oficial site)

unfortunately i don't have option to do my own compile =(

ALso what kind of loading script you refering to - could you post a sample?

trevius

06-14-2009, 06:07 PM

Everyone has the option to compile their own source. It is only a matter of taking the time to figure out how to do it the first time :P

The import/export scripts aren't included with the Binaries yet as far as I can see, but when this change goes in, they should probably get added to the Binaries. Otherwise, you can get the import and export scripts by downloading the source code through the SVN, as it comes with it in the main directory there.

Here is some more information on the import/export scripts and how to use them. It is in this very thread we are in right now:

http://www.eqemulator.net/forums/showthread.php?t=24193&page=3

See post #45 by AndMetal

ChaosSlayerZ

06-14-2009, 06:19 PM

Everyone has the option to compile their own source. It is only a matter of taking the time to figure out how to do it the first time :P

well as you could see number of downloads of ready binaries is well over 200 =P so as you see people far from comfortable of compiling their own source =P

pfyon

06-14-2009, 06:28 PM

Once you learn how to compile your own source, it's pretty much just as easy as downloading a pre-build binary, but gives you the option of making changes to the code.

trevius

06-14-2009, 06:40 PM

This page has all the info you should need for learning how to compile it on Windows:

http://www.eqemulator.net/wiki/wikka.php?wakka=VS2008

Or, you could setup a Linux server and make compiling as easy as typing "make" :P

IMO, if your main concern is having options, then you should take the time to figure out how to compile your own source. If you are not worried about options, then just use the Binaries and you will still have plenty of options but not as many as compiling yourself. Your main concern has been options for so long, you should have learned how to do this long ago! There is only so much we can do to give options with Binaries, but with compiling yourself, the options are practically limitless :P

ChaosSlayerZ

06-14-2009, 07:07 PM

well i will do my best to swith over to my own compiling, but just want to tell you to give other people advance warning that their custom spell file may stop working soon and then they will be all over the forum going "WTH?" =P

So i urge you to release CURENT last Rev in spell from txt format, and swith to Db spells AFTER that, and put a warning into changelog

trevius

06-14-2009, 07:34 PM

There will definitely be a warning about it in the changelog. The change is simple enough that Cavedude could change it to the file loading before compiling the next Binaries if needed. But really, if you want to stay current with the EQEmu updates, there is always going to be 1 thing or another that needs to be adjusted whether that be database changes or whatever. We can't support all legacy options indefinitely or it would be very hard to make good progression with it. I think the change from the spell file to the database is a very good one. Sometime soon I would like to get some new features added that will really make use of the benefit of having spells in a table vs in the spell file.

ChaosSlayerZ

06-15-2009, 12:42 PM

Trev, my cusdom spell file is designed under Titanium.
the Spell table in DB is deisgned under SoF

are there mututaly exclusive properties between the two?
Can I source the T-spells into Db wihout problem, or does the spell structure layout different for SoF spells?

cavedude

06-15-2009, 04:35 PM

Trev, would you mind zipping up and posting your spells_new file? I've never been able to get spell loading from the DB to work and it looks like the problem still exists. I want to rule out the DB first and go from there.

On a side note, once this is resolved I agree 100% with the change. Editing spells in the DB is far far easier and faster than that stupid flatfile.

Derision

06-15-2009, 04:43 PM

After Rev664 my server was crashing on loading spells with my copy of the PEQ DB due to NULLs in some spell entry columns. From my .mysql_history, this is what I had to do to get the spells to load:

update spells_new set teleport_zone='' where teleport_zone is null;
update spells_new set you_cast ='' where you_cast is null;
update spells_new set other_casts ='' where other_casts is null;
update spells_new set cast_on_you ='' where cast_on_you is null;
update spells_new set cast_on_other ='' where cast_on_other is null;
update spells_new set spell_fades ='' where spell_fades is null;

cavedude

06-15-2009, 04:46 PM

Ah ha! Thanks Derision, I was just about to reboot the server with spell logging on you just saved me some time :)

trevius

06-15-2009, 04:58 PM

Actually, this is one of the reasons why the spells table is so great. The scripts can work to import any type of spell file from any client even up to EQLive if you wanted. Even though the older clients lack the newer fields, the scripts and table are setup to work with any number of fields and just fill in default values for fields that are missing in the spell file that is imported. The good part is that spell files are backwards compatible. This means that if you then export your spells table back into a new spells_us.txt file, it will then work with any client version. The spell files are not forward compatible, so a Titanium spell file will not work for SoF. But, if you do the import/export, you can essentially convert a Titanium spell file to be compatible with the SoF client so all clients can use a matching spell file and also match the server.

Derision

06-15-2009, 05:11 PM

So I guess it should be possible to import the current Live spells_us.txt file into the DB so I can try and implement the Armor of Experience and Resupply Agent Veteran AAs ? (or at least just insert those two spells).

Zeice

06-15-2009, 05:37 PM

trevius

06-15-2009, 06:10 PM

Hmm, I don't know a ton about the perl version difference stuff. But, AndMetal wrote the scripts so maybe he would have an answer or can submit a fix to the SVN for those files if one is needed. You may even be able to download a version of perl that has that perl58.dll file and just put it in the right windows folder for it to work.

Andrew80k

06-15-2009, 06:27 PM

The only thing I've noticed is that the .pl files aren't compatible with perl 5.10...I upgraded a while back since 5.10 was supported in compiling. So whenever I try to run those scripts it says that it cannot find the perl58.dll. Any easy fix for this?
Hmmm. That's interesting. Perl is Perl. It shouldn't require compatibility...but if you are using a windows system (ActiveState Perl) it is possible that one of your modules, like DBD::Mysql, was downloaded using 5.8 and didn't get updated for 5.10. It would probably throw those types of errors. Check those and see if that is your problem. Off hand I don't know which modules it uses though.

ChaosSlayerZ

06-15-2009, 07:08 PM

trevius

06-15-2009, 07:35 PM

When I said that the table is great, I meant just that! It is fully compatible with all spell file types, so your spell file will import into it just fine.

I do agree that the ailia/bleh spell editor is by far the best way to edit the spells until you are really familiar with the table/fields. I am hoping that at some point, someone will be able to port the ailia/bleh editor to work with the database table, because that would really be awesome and is one of the reasons I think we were holding off on moving to the table. I wonder if ailia or bleh would be willing to take a look at making the switch for the tool, or giving out the source code so others could do it.

You can always export your spell table back into a file format and then use the ailia/bleh editor and once you are done just import it in again. It is extra steps, but it would still work fine that way.

The reason people like the table for editing is because it is easy to edit remotely so other devs working on the same server can all have access to edit spells if needed. Also, I am sure it wouldn't be overly complex to get a PHP webtool created at some point to allow for webpage based spell editing.

Zeice

06-15-2009, 07:48 PM

Hmmm. That's interesting. Perl is Perl. It shouldn't require compatibility...but if you are using a windows system (ActiveState Perl) it is possible that one of your modules, like DBD::Mysql, was downloaded using 5.8 and didn't get updated for 5.10. It would probably throw those types of errors. Check those and see if that is your problem. Off hand I don't know which modules it uses though.

Yep that was it. I feel a little stupid now lol. Thanks. =)

ChaosSlayerZ

06-15-2009, 08:01 PM

wel If It works.. It will be good =)
We can always bug George to make a DB side spell editor for us :D

cavedude

06-15-2009, 08:01 PM

The reason people like the table for editing is because it is easy to edit remotely so other devs working on the same server can all have access to edit spells if needed. Also, I am sure it wouldn't be overly complex to get a PHP webtool created at some point to allow for webpage based spell editing.

Actually, I plan on adding spell editing to the PEQ PHP editor down the road. AAs and items are ahead in the queue, however. But, the reason I like the DB over the flatfile personally is it's a hell of a lot easier and faster to edit spells using MySQL queries. I rarely use guis for anything, partly because PEQ has no gui, and partly because I am just a lot more comfortable at the prompt.

Randymarsh9

07-12-2009, 10:22 PM

This error is caused because the DBD-mysql perl module is NOT installed. I ran into this issue ever since the Nov update and could not get the import_spells.pl or export_spells.pl scripts to run. After getting around to conducting some research on it now that my time has freed up, the issue seems to be that the default PPM package availability list no longer has DBD-mysql listed as an option. The DBD-mysqlPP will NOT WORK for the sake of the spell scripts and you must install the normal DBD-mysql module. To install it, follow these steps:

1. open the Perl Package Manager
2. click the Edit menu - Preferences
3. click the Repositories tab
4. In the location box type "http://theoryx5.uwinnipeg.ca/ppms/"
5. Click Add
6. Click OK
7. Install the DBD-mysql package

OR

1. open a command line prompt
2. type "ppm install http://theoryx5.uwinnipeg.ca/ppms/DBD-mysql.ppd
"
3. Press Enter

At this point, the import/export scripts will run correctly.

I tried doing this but it had no effect. It kept saying things like no PPD found at http://theoryx5.uwinnipeg.ca/ppms/. So instead I just tried ppm install DBD-mysql and it looks like the import.pl is working now