Sign in to follow this  
Ulviirala

WUSI - Wurm Unlimited Skills Import

Recommended Posts

TL;DR https://rawgit.com/dmon82/WUSI/master/index.html (If you don't know what to do, read instructions in the spoiler tags).

TL;DR And here's the github link if you want the source or to fork: https://github.com/dmon82/WUSI

Cheers,

so here's my go at helping you import your Wurm Online skill dumps into your Wurm Unlimited databases. Any feedback is greatly appreciated.

What does this do?

This creates SQL statements from a Wurm Online skill dump, to import a character's skills and affinities into a Wurm Unlimited database, while mimicking the WU server's behaviour in creating database entries. You can execute these SQL statements to put them into your database. Simple as that. If you don't know why this is needed, you haven't yet tried to manually set every skill with the in-game interface hehe :)

You will need to use the SQLite3 shell to make changes to the database.

Affinities will be imported. Existing affinities on your WU character will not be removed, you will need to drop those from the wurmplayers.db->affinities table yourself.

Although Faith, Favor, and Alignment values will be set, it cannot make you a priest character. You likely need to figure that out in-game.

If you want or need thorough information, read this version:

Background information (to understand what's going on, not required though):

Skills have a unique number as identified, and their mapping can be found in the com.wurmonline.server.skills.SkillList class (Note: The definition PEWPEWDIE is the (unused) "Turrets" skill).

Every skill that has a value other than the initial default skill value is saved in the wurmplayers.db->skills table. Each entry gets a unique ID that is assembled from an indexer variable, the local server ID, and what I assume is a version number. Imagine it as (++playerSkillIDs << 24) + (localServerID << 8) + 10, and as an 8 byte (64 bit) number looks like IIIIIIIIIIISSSSV (where I is ID, S is local server ID, and V the version, each letter representing a byte). The entry is associated with a player's or creature's unique identified, the WurmId, in the owner column.

You'll find the indexer values in the wurmlogin.db->IDS table, for skills it's the playerSkillIDs column.

Faith, Favor, and Alignment, although seen like skills, are saved in the wurmplayers.db->players table instead, each in their equally named column.

Affinities are saved in the wurmplayers.db->affinities table.

Pre-requisites (important)

1) Shutdown the server. It's highlhy recommended that the server is not running when importing characters, to avoid conflicts in the database.

2) Make a backup. Just in case something gets screwed up, make a backup of your server directory. At the very least, backup the wurmlogin.db and wurmplayers.db file.

You will need to know 3 values before you start.

3) Your local server ID, it can be found in the "Local server" tab of the server's configuration UI. It can also be found in the wurmlogin.db->servers table (SERVER column, the int primary key). You will likely need to use the server ID that the player you want to import skills for is located on.

4) The player character's unique ID, which can be found in-game as a GM. For example, use the GMTOOL keybind on the player character. Though I think it's also displayed in the tool tip when hovering over with the mouse?

5) The value of the playerSkillIDs indexer variable, it can be found in wurmlogin.db->IDS.PLAYERSKILLIDS. To get this value, navigate to your server directory's SQLITE folder (e.g. .\Wurm Unlimited Dedicated Server\Yourdirectory\Sqlite\) and open SQLITE3.EXE (if you're on Windows), and use the following commands:

.open wurmlogin.db

SELECT playerSkillIds FROM ids WHERE server = LOCALSERVERID;

Where LOCALSERVERID is the local server ID you have located in step 3.

6) Get a valid skill dump from the WO client.

How to use (Website version with JavaScript (RECOMMENDED)):

1) Fulfill the pre-requisites.

2) Open https://rawgit.com/dmon82/WUSI/master/index.html

3) Enter the localServerID, playerSkillIDs and ownerID (player character ID) into their respective text fields.

4) Paste the skill dump.

5) Click the Process button.

6) Fix any problems (it will tell you if something is wrong, what is wrong, and where, the output box is also red). If you're having trouble fixing a problem, post here.

7) Click the "Select all" button, and copy it to your clipboard.

You now have two options, save it as a CharName.SQL file, or paste the code directly into the SQLite3 shell.

CharName.SQL:

1) Navigate back to your server's SQLITE folder (e.g. .\Wurm Unlimited Dedicated Server\Yourdirectory\Sqlite\).

2) Start SQLite3.exe and use the following commands:

3) .open wurmplayers.db

4) .read CharName.SQL

5) .open wurmlogin.db

6) UPDATE ids SET playerSkillIds = NEWINDEXVALUE WHERE server = LOCALSERVERID;

In the output SQL are two comments (prefixed with double dashes, that is --), telling you your new playerSkillIDs value. Replace NEWINDEXVALUE with that number. Also make sure you replace LOCALSERVERID with your actual local server ID.

Paste directly into shell:

1) Navigate back to your server's SQLITE folder (e.g. .\Wurm Unlimited Dedicated Server\Yourdirectory\Sqlite\).

2) Start SQLite3.exe and use the following commands:

3) .open wurmplayers.db

4) Paste your clipboard contents (In a windows shell, click the top left corner button, and select Edit -> Paste).

5) .open wurmlogin.db

6) UPDATE ids SET playerSkillIds = NEWINDEXVALUE WHERE server = LOCALSERVERID;

You should be done. You can now start the server and login, or import more characters. I have imported my own character and confirmed that it works.

How to use (Pastebin.com paste with C# version on .NET Fiddle (NOT RECOMMENDED)):

(Likely useful for programmers only, will probably not be maintained. Only pro: Can have people put their dumps on pastebin.com)

1) Fulfill the pre-requisites.

2) Goto http://www.pastebin.com and paste the full skill dump.

3) Get the link to the RAW version of the paste (e.g. http://pastebin.com/raw.php?i=PASTEID)

3) Open https://dotnetfiddle.net/IiqT7n

4) Change the variables at the top to their respective values, you should have those as the pre-requisites.

5) Wait a second for it to run, or click the Run button at the top of the page.

6) Copy the output at the bottom of the page.

-- Note: You can ignore the following errors: "Skills" and "Characteristics" not found as skill, "Religion: 0.0 0.0 0" malformed. They're dummy skills, the lines will be skipped.

7) Make sure there are no error messages in the output.

You now have two options, save it as a CharName.SQL file, or paste the code directly into the SQLite3 shell.

CharName.SQL:

1) Navigate back to your server's SQLITE folder (e.g. .\Wurm Unlimited Dedicated Server\Yourdirectory\Sqlite\).

2) Start SQLite3.exe and use the following commands:

3) .open wurmplayers.db

4) .read CharName.SQL

5) .open wurmlogin.db

6) UPDATE ids SET playerSkillIds = NEWINDEXVALUE WHERE server = LOCALSERVERID;

In the output SQL are two comments (prefixed with double dashes, that is --), telling you your new playerSkillIDs value. Replace NEWINDEXVALUE with that number. Also make sure you replace LOCALSERVERID with your actual local server ID.

Paste directly into shell:

1) Navigate back to your server's SQLITE folder (e.g. .\Wurm Unlimited Dedicated Server\Yourdirectory\Sqlite\).

2) Start SQLite3.exe and use the following commands:

3) .open wurmplayers.db

4) Paste your clipboard contents (In a windows shell, click the top left corner button, and select Edit -> Paste).

5) .open wurmlogin.db

6) UPDATE ids SET playerSkillIds = NEWINDEXVALUE WHERE server = LOCALSERVERID;

You should be done. You can now start the server and login, or import more characters. I have imported my own character and confirmed that it works.

If you consider yourself an expert user or even developer, here's a shortened version:

Background information (to understand some inner working, not required though):

Skill ID values are found in the com.wurmonline.server.skill.SkillList class, each skill has a unique ID (Note: The definition PEWPEWDIE is the (unused) "Turrets" skill).

Skills with a value are saved in wurmplayers.db->skills, rows have a unique ID put together as (++playerSkillIDs << 24) + (localServerID << 8) + 10. playerSkillIDs is an indexer, it's value is saved in wurmlogin.db->IDS.playerSkillIds. All rows also have an owner, it's the character ID.

Faith, Favor, and Alignment are saved in the wurmplayers.db->players table in their matching column names instead.

Affinities are saved in the wurmplayers.db->affinities table.

Pre-requisites (important)

1) Shutdown the server. It's highlhy recommended that the server is not running when importing characters, to avoid conflicts in the database.

2) Make a backup.

3) Find your local server ID, either in the server configuration UI, "Local server" tab, or look it up in wurmlogin.db->servers.

4) The character's ID you want to import skills for.

5) The playerSkillIDs indexer value, found in wurmlogin.db->IDS.playerSkillIDs.

6) The skill dump to import, make sure it's complete.

How to use (Website version with JavaScript (RECOMMENDED)):

1) Fulfill the pre-requisites.

2) Open https://rawgit.com/dmon82/WUSI/master/index.html

3) Enter values to their respective fields, paste skill dump, press process.

4) Handle any errors, if you need help post here or contact me on IRC (Rizon, #WUmodding).

You now have to options.

Save as file:

1) Paste SQL output and save as file, like CharName.SQL

2) use ".read CharName.SQL" in the SQLite3 shell on the wurmplayers.db database

3) UPDATE the playerSkillIDs value in wurmlogin.db->IDS.playerSkillIds

The new value for the playerSkillIDs is in the SQL output, once at the top and once at the bottom as an SQL comment.

Paste into shell:

1) Open the SQlite3 shell and wurmplayers.db

2) Paste SQL directly into shell.

3) UPDATE the playerSkillIDs value in wurmlogin.db->IDS.playerSkillIds

The new value for the playerSkillIDs is in the SQL output, once at the top and once at the bottom as an SQL comment.

You should be done. You can now start the server and login, or import more characters. I have imported my own character and confirmed that it works.

How to use (Pastebin.com paste with C# version on .NET Fiddle (NOT RECOMMENDED)):

(Likely useful for programmers only, will probably not be maintained. Only pro: Can have people put their dumps on pastebin.com)

1) Fulfill the pre-requisites and paste skill dump on Pastebin.com

3) Open https://dotnetfiddle.net/IiqT7n

4) Change the variables at the top.

-- Note: You can ignore the following errors: "Skills" and "Characteristics" not found as skill, "Religion: 0.0 0.0 0" malformed. They're dummy skills, the lines will be skipped.

7) Make sure there are no error messages in the output.

You now have two options, save it as a CharName.SQL file, or paste the code directly into the SQLite3 shell.

Save as file:

1) Paste SQL output and save as file, like CharName.SQL

2) use ".read CharName.SQL" in the SQLite3 shell on the wurmplayers.db database

3) UPDATE the playerSkillIDs value in wurmlogin.db->IDS.playerSkillIds

The new value for the playerSkillIDs is at the bottom of the SQL output.

Paste into shell:

1) Open the SQlite3 shell and wurmplayers.db

2) Paste SQL directly into shell.

3) UPDATE the playerSkillIDs value in wurmlogin.db->IDS.playerSkillIds

The new value for the playerSkillIDs is at the bottom of the SQL output.

You should be done. You can now start the server and login, or import more characters. I have imported my own character and confirmed that it works.

Many thanks to ghowden for porting my c# version to a much more user friendly JS version, effective and simple :3

If you have any problems or questions, post here and I'll try to help.

Edited by Ulviirala
  • Like 6

Share this post


Link to post
Share on other sites

Changelog:

Thursday, 5th November 2015 (master)

  • Now uses the higher value from either the dump or an existing database entry
  • Now includes the .open commands for sqlite3.
Thursday, 5th November 2015 (1.1)
  • Fixed a bug where it always skips inserting skills when affinities are present. The affinities have been inserted though.
  • Now takes the higher of the two skill values found in the dump, for haywire skill dumps.
  • Now adds the two commands to update playerSkillIds at the end of the list of statements.
Monday, 26th October 2015 (1.0)
  • Fixed a bug where it unintentionally limited the playerSkillIds value to 65535.
Edited by Ulviirala

Share this post


Link to post
Share on other sites

so this "playerSkillIDs value:" on your website version it says it has to be "less then or equal 65565" yet the id stored in my database is "272821" 

i checked the ids from my dedicated server that i run for a few friends(different machine) and once again i find an id that is higher then 65k

Share this post


Link to post
Share on other sites

Cheers, thanks for pointing that out!

It's probably just an overlooked mishap copy&pasting the field, I've even missed that when finishing up yesterday. Local server ID looked like it should only ever be a 16 bit value because of how the ID is assembled (playerSkillIds << 24) + (localServerId << 8) + 10, leaving it only 16 bits to not interfere with what I believe is a version number (10). Limiting the playerSkillIDs to 0-65535 is a bug.

That limits the total number of skill entries to 39 bits, unless negative numbers count (since java usually uses only signed primitives), then it's 40 bits, so either 549,755,813,887 or 1,099,511,627,774 entries. I guess it's expected to never hit that number, but I find it a bit bad by design. So you can have at least about 3.9 trillion characters with 138 skills each.

/edit: It's fixed now and should allow a more proper range for numbers, the committed version should be available at rawgit shortly.

Edited by Ulviirala

Share this post


Link to post
Share on other sites

Thanks for this! Made moving from one custom map to another after we'd leveled up some skills already very painless (except for losing what we'd already built :P)!


Share this post


Link to post
Share on other sites

Thanks for this! Made moving from one custom map to another after we'd leveled up some skills already very painless (except for losing what we'd already built :P)!

At first glance it looks like you could simply backup your wurmplayers.db file instead, it seems slightly easier to do, unless it causes some other kinds of conflicts, which is entirely possible.

But yea, I'm glad it's of good use :) Before I started working on it, I was maybe 1/3rd through setting my first character's skills by hand as GM... ugh, very tedious to do, but then again... the in-game interface was not intended to do this, so no hard feelings.

Edited by Ulviirala

Share this post


Link to post
Share on other sites

At first glance it looks like you could simply backup your wurmplayers.db file instead, it seems slightly easier to do, unless it causes some other kinds of conflicts, which is entirely possible.

But yea, I'm glad it's of good use :) Before I started working on it, I was maybe 1/3rd through setting my first character's skills by hand as GM... ugh, very tedious to do, but then again... the in-game interface was not intended to do this, so no hard feelings.

I thought about doing that, but I didn't want any weird settings from the previous server db to interfere. Not sure if they would, but this was quick and it worked fine :D 

Share this post


Link to post
Share on other sites

Ran into a few interesting situations when using this awesome tool to migrate skills for fellow players yesterday. Here are two sample skilldump lines:


 


1) Fighting: 70.93769 5.694899 0


 


For some reason the skill dump had a 5.69 in the second column. The tool took it as the value to use rather then the proper 70.93


 


2) Long bow: 63.15549 63.15549 2


 


In this case the player had 2 extra affinities in Long bow. Rather then take their 63 skill it used 2 for their skill


 


Would it be possible to update the logic to use the highest number on the line instead?


 


Also, would it be possible to change the query so that you can tell it to only update if the number used is higher then the number already in place. So if I wanted to update Archery skill to 75 but skill was already at 83 it would keep the higher 83?


 


Otherwise very nice and useful tool.


 


~Nappy


Share this post


Link to post
Share on other sites

I'm not familiar with sql so this is really complicated for me. I was able to struggle my way through the pre-reqs, but implementing them is another story. I got the calculator to work fine, cannot get my skills to update to what they now should be. I tried the pasting into the shell method, it didn't change anything. So I tried the charname method. Now, I'm assuming that you are to replace CharName with the character's name, no? I wasn't sure though, so I tried both. I got an error message with my character's name, and also with using CharName - "error - cannot open Newbie.SQL". No idea why neither method is working. Any ideas? I'm not a programmer, so layman's terms would really help me!


Share this post


Link to post
Share on other sites

Ran into a few interesting situations when using this awesome tool to migrate skills for fellow players yesterday. Here are two sample skilldump lines:

 

1) Fighting: 70.93769 5.694899 0

 

For some reason the skill dump had a 5.69 in the second column. The tool took it as the value to use rather then the proper 70.93

Would it be possible to update the logic to use the highest number on the line instead?

Yes, definitely. It will now be using the higher of the two values.

 

2) Long bow: 63.15549 63.15549 2

 

In this case the player had 2 extra affinities in Long bow. Rather then take their 63 skill it used 2 for their skill

There was actually a bug, where when a skill had an affinity, it would generate the statement to give that player the affinity but skill inserting the actual skill. The long bow skill in this case shouldn't be imported at all.

I'm not sure how that got there. The easiest way to fix that for already imported players would be setting it manually.

 

Also, would it be possible to change the query so that you can tell it to only update if the number used is higher then the number already in place. So if I wanted to update Archery skill to 75 but skill was already at 83 it would keep the higher 83?

I might be able to have it coalesce the imported value with a queried value from an existing row (or null if there was no entry), and get the max from either that or the imported value. It would've been much easier if SQLite3 supported stored procedures, to implement this logic, but I'll look into it later today and run some tests.

Share this post


Link to post
Share on other sites

I'm not familiar with sql so this is really complicated for me. I was able to struggle my way through the pre-reqs, but implementing them is another story. I got the calculator to work fine, cannot get my skills to update to what they now should be. I tried the pasting into the shell method, it didn't change anything. So I tried the charname method. Now, I'm assuming that you are to replace CharName with the character's name, no? I wasn't sure though, so I tried both. I got an error message with my character's name, and also with using CharName - "error - cannot open Newbie.SQL". No idea why neither method is working. Any ideas? I'm not a programmer, so layman's terms would really help me!

It's hard to tell where you took a wrong step, if your skills in-game didn't change at all after starting the server up again, I would assume that one of the pre-reqs is off, my best guess would be the player character ID.

I'll send you a PM and try to walk you through.

Share this post


Link to post
Share on other sites

Also, would it be possible to change the query so that you can tell it to only update if the number used is higher then the number already in place. So if I wanted to update Archery skill to 75 but skill was already at 83 it would keep the higher 83?

 

I might be able to have it coalesce the imported value with a queried value from an existing row (or null if there was no entry), and get the max from either that or the imported value. It would've been much easier if SQLite3 supported stored procedures, to implement this logic, but I'll look into it later today and run some tests.

As soon as the cache is updated, there should be a new version available via rawgit. I think I managed to implement this via MAX(COALESCE(n1, n2), n2) calls. It takes the higher value from either the skill dump (n2), or the database if an entry (n1) exists at all.

Share this post


Link to post
Share on other sites

Ulviirala you are incredibly awesome. I just built a new server yesterday so I am about to upload the skill dumps to that server. Instead of tweaking the skill dumps I had to make them work, I will use the same ones I did last time to test the fixes for you (and I).


 


I am looking forward to giving you an update.


 


Thanks again, super impressed with the fast response and awesome support.


 


~Nappy


Share this post


Link to post
Share on other sites

Ran 14 skill dumps through this updated process/tool. Worked great. No problems reported thus far.


 


~Nappy


Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this