EQExtractor2

[cavedude]

Originally Posted by Akkadius

There's got to be a way we could manipulate as script to pull raw loot tables from Lucy. And modify from there.

Gaeorn is working on that, actually. He did merchant lists, though there is a major bug with the output I need to talk to him about before the sql released.

Hopefully that is fixed in v1.3 (just uploaded it).

Yep, Derision that fixed it, I can now build! I did find one more tiny problem, though. You'll need to add a doorid field next to version. The doorid currently always starts at 1, and that will conflict if the zone already has doors in another version.

What would be your suggestion in order to keep everything compatible and to be able to submit it to PEQ?

Also, this just gets doors, zone entries, NPC entries in-game. What about adding in special attacks, spells, loot, etc? What's the easiest way to help with this?

To merge into PEQ is pretty tough, because for everything except for NPCs, we use the next available ID. If you feel comfortable, you can send any pcap logs you gather to me at npcswithoutfaces@gmail.com. Otherwise, I don't know a way for multiple people to successfully merge into PEQ, especially since our IDs are going to change QUICK now. SQLs won't help much in this case because the IDs will almost certainly conflict.

[Akkadius]

Originally Posted by cavedude

Gaeorn is working on that, actually. He did merchant lists, though there is a major bug with the output I need to talk to him about before the sql released.



Yep, Derision that fixed it, I can now build! I did find one more tiny problem, though. You'll need to add a doorid field next to version. The doorid currently always starts at 1, and that will conflict if the zone already has doors in another version.



To merge into PEQ is pretty tough, because for everything except for NPCs, we use the next available ID. If you feel comfortable, you can send any pcap logs you gather to me at npcswithoutfaces@gmail.com. Otherwise, I don't know a way for multiple people to successfully merge into PEQ, especially since our IDs are going to change QUICK now. SQLs won't help much in this case because the IDs will almost certainly conflict.

A loot table script would be phenomenal. I wouldn't mind helping with this.

[Derision]

Originally Posted by cavedude

our IDs are going to change QUICK now. SQLs won't help much in this case because the IDs will almost certainly conflict.

I did wonder if I could set variables for the starting Insert IDs at the start of the generated SQL and just reference them with increments afterwards, so you could just change
a few variables at the start to the next free IDs. I didn't know if I could do that in SQL, but I just tested it with select statements, and it seems to be possible:

set @myinsertid = 1001;
select id, name from npc_types where id = @myinsertid;
select id, name from npc_types where id = @myinsertid + 1;

Output:

mysql> source test.sql
Query OK, 0 rows affected (0.00 sec)

+------+-------------+
| id   | name        |
+------+-------------+
| 1001 | Guard_Mezzt |
+------+-------------+
1 row in set (0.00 sec)

+------+--------------+
| id   | name         |
+------+--------------+
| 1002 | Guard_Jerith |
+------+--------------+
1 row in set (0.00 sec)

So maybe that is the way to go.

[cavedude]

Originally Posted by Derision

So maybe that is the way to go.

Yes! I think you got it! It would mean everybody would need to re-dump their SQLs to share, but it would also mean that with a couple changes all those files will be universal over any database.

[Derision]

Originally Posted by cavedude

Yes! I think you got it! It would mean everybody would need to re-dump their SQLs to share, but it would also mean that with a couple changes all those files will be universal
over any database.

How would it be if I removed all the INSERT ID fields from the Extractor UI, so you didn't have to worry about them when generating the SQL, and could just go in afterwards
and set them as required, i.e. I would put a template at the start of the generated SQL like:

set @NPCTypesStartingInsertID = XXXXXXX
set @SpawnEntryStartingInsertID = XXXXXXX
...
...
-- Set the starting Insert IDs above and remove the exit statement below before executing this SQL
exit
<Generated Insert statements follow, referencing the variables defined above>

I.e. you would just have to press the Load .pcap button, select the .pcap file, alter the check boxes if you wanted, not have to worry about IDs at that point, and then just click 'Generate SQL'.

[Secrets]

Originally Posted by Derision

How would it be if I removed all the INSERT ID fields from the Extractor UI, so you didn't have to worry about them when generating the SQL, and could just go in afterwards
and set them as required, i.e. I would put a template at the start of the generated SQL like:

set @NPCTypesStartingInsertID = XXXXXXX
set @SpawnEntryStartingInsertID = XXXXXXX
...
...
-- Set the starting Insert IDs above and remove the exit statement below before executing this SQL
exit
<Generated Insert statements follow, referencing the variables defined above>

I.e. you would just have to press the Load .pcap button, select the .pcap file, alter the check boxes if you wanted, not have to worry about IDs at that point, and then just click 'Generate SQL'.

That seems ideal to me. That way it's compatable with any database.

[Secrets]

As for loot drops and everything, I found that Magelo is a better resource than Lucy. I don't know why or how, but it is. I think Magelo uses the same ID in items too.

edit: yeah it does. http://eq.magelo.com/item/1001

[trevius]

Originally Posted by Derision

How would it be if I removed all the INSERT ID fields from the Extractor UI, so you didn't have to worry about them when generating the SQL, and could just go in afterwards
and set them as required, i.e. I would put a template at the start of the generated SQL like:

set @NPCTypesStartingInsertID = XXXXXXX
set @SpawnEntryStartingInsertID = XXXXXXX
...
...
-- Set the starting Insert IDs above and remove the exit statement below before executing this SQL
exit
<Generated Insert statements follow, referencing the variables defined above>

I.e. you would just have to press the Load .pcap button, select the .pcap file, alter the check boxes if you wanted, not have to worry about IDs at that point, and then just click 'Generate SQL'.

I prefer having the fields there, but a simple toggle box to toggle them enabled/disabled would work well. That way, people could toggle them off when collecting for PEQ or leave them on if collecting for their own DB. A simple toggle would make it nice and quick to generate one of each type of you wanted. I think it is nice to be able to see the actual range of numbers that the SQL is planning to use so it can be easily compared against the DB to make sure it will be ok.

[cavedude]

I agree with Trevius. Although I would personally disable the IDs in the program, I think it should still be an option for others.

[trevius]

I don't know the details of how the tool works exactly, but it seems like it parses the .pcap when you first load it. Perhaps you could just give an option to generate a file of that parse, which could then be loaded and used to generate SQL from. Figured that may be another possible option for creating files anyone could use and share without worrying about private info. Another nice bonus to using a parse instead of some generated SQL is that a parse should be able to hold data that may not currently be set to be generated into SQL yet, so as the extractor tool is refined/expanded, those same parses could be ran through the tool again for an instant update with the latest features. This should prevent people from having to run their own .pcaps through the tool and sending them again. Instead, Cavedude could just run the parses through the tool, and have the latest with minimal work.

[Akkadius]

Originally Posted by cavedude

I agree with Trevius. Although I would personally disable the IDs in the program, I think it should still be an option for others.

I just got back from ball, I agree completely that this should be a universal application for dumping collects, but should also be customizable if need be.

[gaeorn]

Originally Posted by Akkadius

A loot table script would be phenomenal. I wouldn't mind helping with this.

I already farmed all the data from magelo for their loot info for all mobs in all zones. I suppose the same could be done for lucy, but I believe the magelo data is more complete. I can write a script to parse out the usable data without an issue. The problem comes in when it's time to write out that data in some usable form. I haven't started on the script yet because I haven't yet decided how to write out the data. I'm sure I'll either write out SQL or I'll create temp tables and dump the data there, but I'm sure it'll take a human to merge it in cleanly with the PEQ db.

If you want to help, look at the magelo drop data and see what ideas you come up with for using it. I'd love to hear more ideas about how best to use that data.

[gaeorn]

Originally Posted by Derision

I did wonder if I could set variables for the starting Insert IDs at the start of the generated SQL and just reference them with increments afterwards, so you could just change
a few variables at the start to the next free IDs. I didn't know if I could do that in SQL, but I just tested it with select statements, and it seems to be possible:

set @myinsertid = 1001;
select id, name from npc_types where id = @myinsertid;
select id, name from npc_types where id = @myinsertid + 1;

Output:

mysql> source test.sql
Query OK, 0 rows affected (0.00 sec)

+------+-------------+
| id   | name        |
+------+-------------+
| 1001 | Guard_Mezzt |
+------+-------------+
1 row in set (0.00 sec)

+------+--------------+
| id   | name         |
+------+--------------+
| 1002 | Guard_Jerith |
+------+--------------+
1 row in set (0.00 sec)

So maybe that is the way to go.

Why not just fill in the ID with a sub select? You could simply do a (select max(id) from table)+1 where you would otherwise put in the ID. Or you could at least use a select to automatically set the variable at the start and otherwise do it as you have above.

[gaeorn]

Originally Posted by trevius

I don't know the details of how the tool works exactly, but it seems like it parses the .pcap when you first load it. Perhaps you could just give an option to generate a file of that parse, which could then be loaded and used to generate SQL from. Figured that may be another possible option for creating files anyone could use and share without worrying about private info. Another nice bonus to using a parse instead of some generated SQL is that a parse should be able to hold data that may not currently be set to be generated into SQL yet, so as the extractor tool is refined/expanded, those same parses could be ran through the tool again for an instant update with the latest features. This should prevent people from having to run their own .pcaps through the tool and sending them again. Instead, Cavedude could just run the parses through the tool, and have the latest with minimal work.

Why not just have a tool that zeros out any personal info from the .pcap files so they could be sent in as is. Then the full communication between client and server is complete in case we figure out something additional to extract from it later, but it won't contain any personal information that a player may wish kept from the general populace.

[trevius]

Originally Posted by gaeorn

Why not just have a tool that zeros out any personal info from the .pcap files so they could be sent in as is. Then the full communication between client and server is complete in case we figure out something additional to extract from it later, but it won't contain any personal information that a player may wish kept from the general populace.

I think the old collectors originally used to do just that, but since then, SOE has added quite a few more packets with character names, and probably other information, and it might be hard to track them all down. I suppose we could put together a list of all possible packets we may ever need collected, and just filter out the rest of the unneeded ones. As-is, I think Derision already has the most important packets being collected. As far as I can think, the only other one we might even want at this point would be OP_SpawnAppearance.

To make use of many more packets would require a different kind of collecting, which would be more interactive. The collecting this tool currently does best works when there is no interaction at all with the zone other than maybe checking merchants for merchant lists. Maybe in the future, other types of collects could be made that collect things like tasks, loot drops, spell sets on NPCs, or any other number of things that require considerably more time to collect enough info for it to be useful. Most of that stuff would probably be better off just adding it manually after a little research from various websites. Though, tasks might be one thing we could get enough info from without much time, but Derision would be much better to answer that.