Editing Object Data stored in MySQL tables

[Kosmous]

Since Bioware created SCO/RCO functionality, Ive wanted to be able to edit object data through these functions. There were some successful attempts to edit objects which extended the use of NWNX even before SCO/RCO was tapped into by the avlis guys to allow manipulation of certain properties in many object types that were previously impossible to change on runtime. I also believe, that recently, ppl are once again working on more functions that manipulate more properties.

Anyway, what im trying to get at is that despite its inefficiencies, Ive done some work on editing the object data stored in blob fields for NWN. I finally did the work and learned how gffs work. Although gffs stored in modules are different when they are stored in the mySQL db via SCO/RCO, the gff basic structure is the same when entered as a campaign object into mysql.

Although this could be done better, Ive been able to put together a couple of SQL queries which obtain and update byte and dword fields (ie appearance index of items, cost and additional cost of items) directly from the blob fields.

select name, ascii(substring(objectdata, (ascii(substring(objectdata, 17,1)) + 9 + (((instr(objectdata,'Cost') - 1) - (ascii(substring(objectdata,26,1))*256+ascii(substring(objectdata,25,1))))/16) *12),1)) from weapons

Merely change the the word 'Cost' into the desired byte or dword field and you should get the decimal value of the desired field from the blob data which has a field name of "ObjectData" in this example. This example also assumes a "Weapons" table exists. Lastly, this example assumes that the Cost is only up to 255 while in reality it is four bytes long. You can easily edit the above example by reading all four bytes instead of just the first one.

update weapons set objectdata = INSERT(objectdata, ascii(substring(objectdata, 17,1)) + 9 + (((instr(objectdata,'StackSize') - 1) - (ascii(substring(objectdata,26,1))*256+ascii(substring(objectdata,25,1))))/16) *12, 4,char(conv(substring(lpad(hex(20),8,'0'),7,2),16,10),conv(substring(lpad(hex(20),8,'0'),5,2),16,10),conv(substring(lpad(hex(20),8,'0'),3,2),16,10), conv(substring(lpad(hex(20),8,'0'),1,2),16,10)))

This example, on the other hand, edits the "StackSize" of the item to 20 (as seen in the four instances within the hex() function calls). Again it assumes the blob field is named "ObjectData" and the table being edited is "Weapons".

Unlike the previous query, this query utilizes all four bytes available to dword fields (even though StackSize is only a byte). However, this example only assumes that the offset of the field data array is up to 255 (offsets are all dword) and that the label array offset is only up to 256*255 big. This should function flawlessly for all item blueprints but again can be edited to take into consideration all four bytes of the dword offsets found in the gff header in case the struct array is considerably bigger than in my test samples.

As with the first query, you may change the "StackSize" key word to edit another byte, dword fields but you must also edit the hex(20) to reflect the desired value to change to.

Take note that both example take into consideration offsets which are stored in the gff header so it shouldnt matter what object file your trying to manipulate.

What took me the longest to do was converting integers to dword and back. I initially tried to find a mysql function which does this automatically but found none. If anyone knows of a integer to dword or dword to integer converter, I would greatly appreciate you telling me.

I know the uses for this information may be limited,but im guessing someon out there might find some use for it like I have.

[Blacksting]

Letoscript has the tested functionality to (GFF) edit blob fields although the version that does so is still in beta. Currently I am using a recompiled ODBC plugin that has direct GFF editing of incoming objects (including all relevent fields, strings and locstrings, integers, and list navigation.) For the efficiency minded the ODBC direct editing is the best route as it is possible to edit in game objects without ever accessing the hard drive. I have not released it yet, though. I might talk to JeroenB about having the additions be released as the "official" next version so I do not have to add it each time a new version comes out .

[Kosmous]

Ya i know about your work blacksting, youve mentioned it b4 in these forums. I did not know about the Leto blob field stuff though. I will be hoping for your work to be released soon.

However, im not sure how the Leto editor will work with blob fields, but i did these queries to edit hundreds of entries in several tables. I have a bad habit of always changing SOMETHING in my blueprints and unfortunately i have a few hundred. I have a system which runs off the mySQL db data objects instead of standard blueprints (yes, i know very inefficient) so these queries serve my particular purpose. I just wanted to share in case anyone else wanted to do something similiar to myself.

As for strings and local strings, these are very simple as well. Just changing the header offset values and the field data itself should make manipulation of those field values very simple.

I never really got into list fields though. I thought they looked to complicated to mess around with. What list fields do u manipulated during runtime?

[Blacksting]

I use list field editing mostly for editing/generating class info for NPCs: adding new classes, adding class levels, spells, etc. I also have in the back of my mind the idea that I will develop an inventory/equipped item edit through GFF (that way spawns can generate random equipment on the way in without having to bother NWNScript with destruction of items, creation of new items, and equip instructions.) The idea of having complete random spawn generation without the need for a boat load of templates and pretty much no cpu hit appeals to me.

When you add string edit routines to your functions here is a little tip. Old string data should be just left there. In my GFF edit routines if a string is shorter than its predecessor I just overwrite the data (and the string length part of the entry is changed with some old string letters hanging) but if it is longer I just add the string to the end of the string data block and point to it. The "garbage" left behind is not bloat because the way GFF files are read in to the engine it is never seen..... and the way the GFF files are reconstituted if the file is ever saved the garbage does not even exist.



Letoscript (Leto) has recently added reading/writing blob fields to its language.... and since the language (well at least in the old Phoenix version) edited GFF files in the Bioware (FoxPro) DB files it is just the same thing applied to the blob field. It is far more advanced than my editing routines at the moment.