Perl Import_Spells.pl
 

Ok after reading up on everything, I'm trying to import SoF spells_us.txt file Deleted the Spells_New table. Sourced in the 230_spells_table.sql then ran perl import_spells.pl and I get a grand total of 28 spells in the db and a ton of errors in the cmd screen like the following:
Using SoF client
spells_us.txt from the SoF Client
sourced in the 230_spells_table.sql
copy the import_spells.pl from a fresh download of the Trunk
perl import_spells.pl and get the above errors (more than it listed of course)

Import_spells working for SoF spells_us.txt ? or am I missing something that if it was a snake it would have bit me twice?

I even sourced in a fresh DB and copied the Structure only from it to my DB and still get the same errors as I did when I sourced in the 230_spells_table.sql.

From /trunk/EQEmuServer/utils/sql/svn/230_spells_table.sql:
It appears it's pulling a null value for that field in your spells file. Easiest thing to do would be to change that column in the database to allow null values.

....
 

Started anew.

Pulled the spells.us.txt from a fresh install of the SoF Client

Had to modify the 230_spells_table.sql to allow the following to be NULL before it would source in via import_spells.pl would get no errors.

range
aoerange
pushback
pushup
cast_time
recovery_time
recast_time
buffduration
mana
effect_base_value2
effect_base_value5
effect_limit_value5
max1
max2
formula2
formula3
formula4
Activated
resisttype
targettype
CastingAnin
TargetAnin
field124
newicon
ResistDiff
typedescnum
effectdescnum
field158
field160
field161
EndurTimerIndex
field175
spell_category
field182
field183
field209
field210

Using Rev669 from Trunk with no modifications (I have two folders, one I modify the other is clean from trunk with no modifications).

Zone.exe crashes when I try to get past character select.

Hrrmmm back to drawing board to see what the heck is going on.

Restored Database to 61209 then downloaded Trunk Rev 656. Compile it with no personal mods.

Everything works fine.

update to Trunk Rev 664.

Compile got error. Fixed client_packet.cpp and recompiled.

Start up and zone crashes over and over (bad spell data in db)

Change spdat.h to load from spells.us.txt instead of using the new database method then recompiled. (fixed the zone crashing at start up)

Works fine.

Will see if I can get import_spells to work again with the SoF spells.us.txt file. From what I am getting so far it seems its not compatible (either that or I have flying monkeys on my WinXP pro box causing havoic with my spell import :/ )

Reading the change log and doing what it tells me to do... it seems busted to me (blasted flying monkeys)

Follow up
 

What I had to do to get import_spells.pl to work on the SoF spells.us.txt file.
I changed the following fields in 230_spells_table.sql from int(11) NOT NULL to varchar(64).

range
aoerange
pushback
pushup
cast_time
recovery_time
recast_time
buffduration
mana
effect_base_value2
effect_base_value5
effect_limit_value5
max1
max2
formula2
formula3
formula4
Activated
resisttype
targettype
CastingAnin
TargetAnin
field124
newicon
ResistDiff
typedescnum
effectdescnum
field158
field160
field161
EndurTimerIndex
field175
spell_category
field182
field183
field209
field210

Sourced in all the spells (14799 of 14799).
Changed the spdat.h file to load from the DB instead of spells.us.txt file.
Removed spells.us.txt file from eqemu folder.

Started up the server.

Zone didn't crash on start up. Loaded into West Commons and casted a few spells, no crash. Not sure if everything is 100% but will keep testing......

Anyways, the 230_spells_table.sql needs to be adjusted to work with SoF spells.us.txt or it wont import via import_spells.pl

Is it just me or did anyone else have this problem? LOL

follow up
 

Ok been doing some more research on this issue.

I'm using MySQL 5.0.77

From what I have read that in MySQL 4 and prior, if a column is set to ' ', it was just converted to a zero value but in MySQL 5 it will spit out the error "Incorrect integer value".

Further reading into this matter suggests its a sql_mode issue. It is suggested to get the old MySQL 4 behaviour back that one should execute set sql_mode = ' '; which they said an empty string means Traditional MySQL mode.

(if I'm wrong guys feel free to correct me at anytime! :) )

Since the default value is empty (no modes set) and that is what set sql_mode = ' ' does.... I cleared my SQL Mode, left it empty. This setting is in your my.ini file (for those like me that didn't know :p ). There are a few different ways to access it. Directly by just finding your my.ini file and clear the SQL MODE... another way is to load up MySQL Administrator and goto Startup Variables, Click on Advanced Tab then slide about midway down in the Various box you you will find SQL Mode: You can clear it here as well.

Now after clearing this. I deleted my spells_new table. Sourced in the 230_spells_table.sql unmodified. Ran Perl import_spells.pl and it reported no errors and I had a full listing of spells (14799 of 14799).

So it was me all this time! Gah. What got me to do further research was when I tried to update my PEQ DB editor and the isbot filed spit out the error "Incorrect integer value". I looked on the boards, found no one else having this problem so off to Google I went. A few seconds later I found what my problem was. Sheesh.

So the moral of the story...... Search the Boards, Google it and do some research.

Anyways, just thought I would share :)

I am having trouble with this script. When I double click it, it just immediately closes and when I run it from a command prompt I get this

That just reminds me to finish a new spell file importer...
I think many would benefit from this.

GeorgeS

Yeah, that would definitely be a handy tool to have. It would also be really nice to have a tool similar to the Ailia/Bleh spell editor for editing the spells table vs just being able to edit the spells file. I briefly spoke with Ailia about this recently and she said that there is a possibility that she will update the spells editor and share it with the community, but that it depends on how busy she is with other things and a few other factors. It would be really great to have a good spells tool that worked so well on the table.

GeorgeS, if/when you make the import tool, can you add in the option to export as well. Since a customized spell table means clients may need to use a custom spell file, it is good to have a simple way to export the file from the table as well. The scripts are actually fairly simple to run, but for people that aren't familiar with it, I can see how it might be tough to figure out. Plus, it will overwrite stuff without asking, so it might be nice to give a warning before overwriting any existing table/file or maybe an option to rename them before importing/exporting something that would overwrite existing data.

Working on the tool now.

I'm about 1/2 done, and probably might be done in two days or so.

It will load and export.

GeorgeS

..can someone point me to this .pl file please?

I believe it's this one:

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

Ok, after some effort, I have it done

If you have trouble getting the perl program to work, this tool is for you!


spells_us.txt import/export ver 1.0.0
-- This tool allows one to import the SOF spells file and export.
-- Place your SOF spells_us.txt file in the main program folder.
-- The exported file is named spells_us_COPY.txt.

Non SOF files are not supported/will not work.

file is found on my site.. (if the .zip won't open, rename it to .rar)

Please report any bugs here

GeorgeS

is there a specific difference between T and Sof spell files, other than number of spells?

cuase all my custom spells are T based, you saying the program won't work for them?

It's the number of fields and the field names in the spell table and if they line up or not is not tested. It's possible to add this in at some stage as a beta test

I would need a table struct/schema of your spell table with a few sample spells, then I can add this in.

Fear not, I just don't have non SOF spell tables, and providing me with them - I can add non SOF support.

*edit*
I looked at the Titanium spell file, and it looks like it lines up with SOF, but with 15 or so fewer fields, so I will code up Titanium next.


GeorgeS

I gave it a try but all it did was run at 100% thread processor usage for 2 hours before I killed the process tree. Never saw an interface of any type, if there is one.

If it ran for that long, there should have been some progress indicated. On my laptop it loads 500 spells in 3 seconds. I cannot comment adversely otherwise without more details

Please backup your table first, before running this

Also, in addition, how do you all create the table? -- it should have 215 fields for SOF

GeorgeS

...
 

the .sql for the spell table file is located in the eqemu/utils/sql/svn/ then look for the file 230_spells_table.sql that will be the sql needed to create the table for SoF.

Its listed in the change log as well as the directions on how to import spells via the perl script :)

For those who use Ti, the program supports Ti spells.
Select the option for Ti when exporting, or if you select SOF when using a Ti spell set, it will export to SOF compatibility


GeorgeS


-- the above SQL is what I used. I hope everyone uses the same file

GeorgeS,

There isn't a need for Titanium exporting, only for Titanium importing. The spell files are backwards compatible, meaning that a Titanium client can use a SoF client spell file perfectly fine. The only problem is that it is not forward compatible, so an SoF client cannot use a Titanium client spell file. For any server wanting to share a customized spell file or a single spell file to use for both clients, they should only be sharing the SoF version, since it will work with both types. IMO, there isn't any need to export into Titanium format. The only possible use of it would be to slightly reduce the file size. The only other difference between the clients as far as the spell files go is that Titanium can only load spell IDs up to 9999, where SoF has a much higher limit (currently I am not aware of what the max is, but it should be at least 20K+).

Ti export is for just re-creating the Ti spell file. You have the choice of exporting it to a SOF compat. format. I'll leave that end to the user to determine, but in any event, it should cover all bases.

GeorgeS

Yeah, no harm in leaving the option. Just wanted to give a heads-up mostly.

Nice! I am looking forward to trying this out as soon as I done with Rev updates

I also tried to run the application, it ran my processor at 100% until I killed it.

Yes, the importation of that much data takes 100% of your CPU. Let it run till it's done.

It takes up to 5 min on my pc

GeorgeS

Grabbed the new version from your site tonight and it ran very well for exporting. The only note if no importance I can make is you can't export without at least having a blank spells_us.txt file. However, with one, all Titanium spells dumped, across the Internet, in less than 5 seconds, including extra latency from my port remapper since the MySQL port isn't configurable through your front-ends.

Saved me a few hours work so thanks George!

Glad you like the tool!

Saves me quite some time as well when I make custom spells.

GeorgeS

Do you still have to use the import_spells.pl to get new spells on your server? I noticed I'm already having trouble with it again and I want to know if you can still just use a spells_us for spells on your server.

in spdat.h Find this (around line 42)
change it to this
Then it will use the spell file like it used to...

Awesome, changed that, compiled. and now my toon has his 75 spells. Thanks again, lol

i really wish they would have made that part into a rule, rather than define option...

Is there any quicker way to test new spells than rebooting the server after I import them?

Spells load into memory when the server starts, so you can't see any changes until a restart. Best bet is to test the changes on a test server first, so you don't affect your players with multiple restarts.

I tinkered with import_spells.pl a bit.
As a test, I created a new table copy of "spells_new" table. Named it "Spells_live" and emptied it., then changed import_spells.pl with this
The added single line converts any null value to 0 before the string is populated.
Tested with a copy of the Spells_en.txt and spells_us.txt from my live EQ folder, and got a result of "Spells loaded: 23120 of 23120"
The resulting table 'looks' correct also, though I haven't gotten around to testing anything with it. Not sure how "correct" the code is either, since I've never really looked at perl before. Should work fine with SoF files.

OK, probably nevermind on that.. Worked on my laptop, but not on my desktop on a different copy of the database. Still trying to figure it out. lol.

you have to use GoergeS ODBC drivers
 

On the Right side of the screen in the box that says Installing the Tools
If you download and install
http://www.georgestools.eqemulator.net/ODBC_DRIVERS.zip

From his download page and install them..you should have no problems. Had same issue until i did so :) hope helps.

Sorry for bringing this up again, but it would be really nice to have something like #reloadspells similar to the #reloadquest in-game command. I assume it wouldn't be too hard, or even take too much time, to write a small function in the source code to do this? Would make testing edits a lot quicker.

Hope someone notices my request =)

Actually, it would be difficult and time consuming to do so, yeah. Currently, spells are loaded into shared memory which is only loaded when the server is first started. This behavior can be changed, but it would take far more time than adding a simple function. The same goes for faction, loot, and items.

** sad face **