Crazy idea for spells...

[AndMetal]

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

Anyways, to address the questions:

Originally Posted by trevius

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

Originally Posted by AndMetal

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

Originally Posted by AndMetal

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

Originally Posted by trevius

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:

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

Originally Posted by trevius

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.

Originally Posted by KLS

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.

Originally Posted by leslamarch

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

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*

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, 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]

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

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

[KLS]

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]

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]

Originally Posted by trevius

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]

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]

Originally Posted by KLS

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

[trevius]

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?