Esp-QuickEditor

All TES III modding discussion
Post Reply
User avatar
worsas
Project Administrator
Posts: 2678
Joined: Wed Dec 31, 2014 1:23 am

Esp-QuickEditor

Post by worsas »

I have lately extended on my previous efforts to programmatically interpret and modify esp-files by including an sql-parser and doing a rudimentary sql-support with it. At the moment there is only one table: cell_objects. It's a quite far away and abstracted from how cell objects (object references) are actually present in the esp-file. Anyone who has ever dealt with sql in any way, should see how this might be useful.

Sorry berry, but:
Image
Image

The support for different sql language elements will remain limited. I'm probably not going to add support for LIMIT or ORDER BY (in case anyone knows what it is) for a while. Also functions like COUNT() or others will not work. and there is no way to do subselection or really any kind of substatement within statements.

At the moment it's only intended for having abstracted views on the file contents in the shape of tables and being able to do simple CRUD-operations on them to save ourselves a lot of manual work. As soon as I'm a bit further along I'll share the program and the source code. I could actually already upload what I have, in case you already want to use what is there.

Edit:
Download-Link : https://www.dropbox.com/s/m42ub6yjn1ty2 ... r.jar?dl=1

User avatar
Infragris
Project Administrator
Posts: 1396
Joined: Fri Jan 02, 2015 7:51 pm

Post by Infragris »

I have no idea how this works but it looks very impressive.

User avatar
berry
PT Modder
Posts: 670
Joined: Fri Jan 16, 2015 4:16 pm

Post by berry »

I can't say I understood a word from what you wrote here, but is sure looks impressive indeed. :P That's the programm you wrote to deal with the books matter, right? So it mechanically alters the position of select objects, to an extent it's told to? That's pretty cool and it will surely save us lot of time, thanks!

User avatar
Luxray
Cat Herder
Posts: 544
Joined: Mon Jan 05, 2015 7:11 pm
Location: South-West England

Post by Luxray »

Nice work muspila, there's surely a spot reserved in sovngarde for our resident code-demon. ^_^
<roerich> woah it's hot in here
<Lord Berandas> it must be Summer.
<Infragris> #hell is meant as a spam and off topic channel. Doing a great job already

User avatar
roerich
Cruel Warlord
Posts: 2166
Joined: Sat Jan 03, 2015 3:10 pm
Location: Denmark

Post by roerich »

What the others said.

Worsas, you should have your own hero-cult by now.
"I don't know if you are kidding but I 100% support a Big Mouth Billy Bass in PC"
- Taniquetil

User avatar
worsas
Project Administrator
Posts: 2678
Joined: Wed Dec 31, 2014 1:23 am

Post by worsas »

I can't say I understood a word from what you wrote here, but is sure looks impressive indeed. :P That's the programm you wrote to deal with the books matter, right? So it mechanically alters the position of select objects, to an extent it's told to? That's pretty cool and it will surely save us lot of time, thanks!
The book rescaling program uses the same data structures in the background as does this program, but this program is far more flexible and can change a lot of things with simple commands. With this program you cannot only change position, but also scale and referenced object of a reference. For example: In the first screenshot I submitted 4 commands that respectively changed all references of _Flora_Bm_Holly_XX in the file to be references of the original bm hollies instead. And below it i just executed a command to view which references of objects starting with underscore there are still present in this file (you can see the list starting below the numbers).

User avatar
worsas
Project Administrator
Posts: 2678
Joined: Wed Dec 31, 2014 1:23 am

Post by worsas »

I herewith present the first version of the Esp-Quickeditor for testing.
https://www.dropbox.com/s/m42ub6yjn1ty2 ... r.jar?dl=1


How to use it:
- You need to have the newest java 8 installed.
- You can right click the jar file and select open with > java (TM) Platform SE Binary. Alternatively double-clicking the file will often automatically choose java to execute the file for you.
- Make sure to backup any file you want to edit with this program.


You can edit esm, esp and ess - files by issuing SQL-commands in the upper textarea. SQL is a language used for handling databases, which consist of one or several tables. The Esp-Quickeditor treats an esp/esm/ess file like a database with tables. Currently the following tables are featured:

idObjects - A table containing all objects with an id that are declared within the file. Except are only cells and dialogue entries.

cellObjects - A table containing all instances of objects that are physically placed within the gameworld.

dialogueResponses - A table containing all dialogue responses declared in the current file.

scripts - A table containing all scripts declared in the current file.

exteriorCells - A table containing all exterior cells declared in the current file.

masterFiles - A table containing all master files the current file depends on.

Below are further convenience/shortcut tables, I created for being able to update any kind of reference to other object

bodypartReferences - A table containing all references to body parts within armors, clothing and npcs
classReferences - A table containing all references to classes within npcs
creatureReferences - A table containing all references to npcs/creatures within levelled lists, soul data, owner data, soundgenerators.
enchantmentReferences - A table containing all references to enchantments within weapons, armor, clothing and books
factionReferences - A table containing all references to factions within npcs
fileReferences - A table containing all filepaths referenced in items, statics, groundtextures etc.
itemReferences - A table containing all references to items in containers, npcs, creatures, levelled items or key data.
scriptReferences - A table containing all references to scripts in items and activators etc.
soundReferences - A table containing all references to sound entries in regions, doors, lights and soundgenerators.
spellReferences - A table containing all references to spells in npcs, creatures or traps.


Some use-examples:

It's always a good idea to issue a SELECT command on a table first to see what it contains and what fields it has:
SELECT * FROM idObjects;

This can be done likewise with any of the other tables listed above and will show all entries of this table within the file.

Now I'd like to only see the npcs declared in my current file:
SELECT * FROM idObjects WHERE type = 'npc';

Or I want to see all objects whose id starts with _:
SELECT * FROM idObjects WHERE id = '_%';

I want to change the id of a custom activator I created in my file from myActivator to myNewActivator. So I issue the following commands:
UPDATE idObjects SET id = 'myNewActivator' WHERE id = 'myActivator';
UPDATE cellObjects SET id = 'myNewActivator' WHERE id = 'myActivator';


The first command updates the id of the base activator. The second command makes all instances of the activator point to the new activator name.

I want to change the id of an npc myNpc in my file. This requires an additional step:
UPDATE idObjects SET id = 'myNewNpc' WHERE id = 'myNpc';
UPDATE cellObjects SET id = 'myNewNpc' WHERE id = 'myNpc';
UPDATE creatureReferences SET creature = 'myNewNpc' WHERE creature = 'myNpc';


The third step ensures that any objects owned by this npc will still belong to her/him after the update (the creatureReferences - table keeps track of these additional references to npcs or creatures). Likewise, if you have a script or item or sound or whatever else whose id you want to change, make sure to make a call to the respective references table to ensure that these are updated to the new id. Another example:
UPDATE idObjects SET id = 'myNewMiscItem' WHERE id = 'myMiscItem';
UPDATE cellObjects SET id = 'myNewMiscItem' WHERE id = 'myMiscItem';
UPDATE itemReferences SET item = 'myNewMiscItem' WHERE item = 'myMiscItem';

^^ the last line will update the references in inventories, containers, levelled lists, etc.

Sometimes you have an unclean file with accidental changes to vanilla objects. You can just remove this accidental modification (you usually do this with TESAME)
DELETE FROM idObjects WHERE id='furn_bone_01';

This will delete your accidental object entry but will leave the instances of furn_bone_01 within your file unharmed (they are in the cellObjects table)

Next, I would like to adjust the position of all instances of a certain plant flora_floating in my file to make sure they are not floating anymore. Let's assume all instances are 50 units too high above the ground:
UPDATE cellObjects SET zPos = zPos-50 WHERE id = 'flora_floating';

On another day, I decide to change the container plant composition within a region examplecoast, by switching plant1 for plant2 and maybe slightly adjusting position and scale to make them fit into place.
UPDATE cellObjects SET id = 'plant2', zPos = zPos + 15, scale = scale*0.9 WHERE id = 'plant1' AND cellRegion = 'examplecoast';

I want to remove all placed objects in my file except for a certain grass plant grass_01 (I want to create a separate esp with just the grass in it, for example). So I do the following:
DELETE FROM cellObjects WHERE id <> 'grass_01';

Next thing: I want to assign a region to a cell that is very far away from 0,0 and doing it in the CS is a hit and miss - business, so I do it here with the exteriorCells - table:
UPDATE exteriorCells SET region = 'my region' WHERE gridX = 120 AND gridY = -33;

I have the adjacent exterior cell at 125,12 I accidentally edited in my claim, so I issue the command:
DELETE FROM exteriorCells WHERE gridX = 125 AND gridY = 12;

This will delete both the objects and the landscape of this cell from my file. Maybe an additional landscape- table would help, in case you just want to remove landscape changes but keep added objects to a neighbour cell. Maybe in a future version.

I want to remove dependency to a masterfile unwanted.esm from my file:
DELETE FROM masterFiles WHERE name = 'unwanted.esm';

I want to remove Almsivi Intervention from all of my npcs:
DELETE FROM spellReferences WHERE spell = 'almsivi intervention'


Regarding all the various references-tables, I'll leave it to you to figure out possible uses.

A general note: If you are a little bit familiar with SQL, you will notice that the support for different expression is quite limited at this point. There are no functions, aliases, subselects or any other advanced functions available. Only very basic SELECT, UPDATES and DELETES work. Many tables even don't allow deletions on them and there are many columns that cannot be updated. INSERTs are only possible with the cellObjects- and masterFiles table at the moment.

User avatar
Infragris
Project Administrator
Posts: 1396
Joined: Fri Jan 02, 2015 7:51 pm

Post by Infragris »

This will be very useful. Looks like we'll have a much more elegant way of removing unwanted spells from NPC inventories. Could this be used to mass-edit NPC AI packages?

User avatar
worsas
Project Administrator
Posts: 2678
Joined: Wed Dec 31, 2014 1:23 am

Post by worsas »

AI packages cannot be edited with this yet. There is no table for it. But I could add one.

Is there a particular action you would like to perform with them? That would help me figuring out how to structurize the table.

User avatar
Infragris
Project Administrator
Posts: 1396
Joined: Fri Jan 02, 2015 7:51 pm

Post by Infragris »

I'm not sure actually. Editing idles, wander packages, and fight/flee/alarm setting is a major pain, but it pretty much has to be done on an individual basis. Never mind, actually.

User avatar
worsas
Project Administrator
Posts: 2678
Joined: Wed Dec 31, 2014 1:23 am

Post by worsas »

So, has anyone tried out the program yet? Does it work well? Have you experienced any major issues?

The program is currently not very verbose when errors take place, btw. Sql-syntact issues or exceptions are printed to the standard output instead of appearing somewhere in the user interface. The whole error handling is still a lot of crap, actually. Been mostly focusing on creating and making the tables work so far and haven't paid much attention to the these things.

User avatar
berry
PT Modder
Posts: 670
Joined: Fri Jan 16, 2015 4:16 pm

Post by berry »

I want to remove all placed objects in my file except for a certain grass plant grass_01 (I want to create a separate esp with just the grass in it, for example). So I do the following:
DELETE FROM cellObjects WHERE id <> 'grass_01';

Next thing: I want to assign a region to a cell that is very far away from 0,0 and doing it in the CS is a hit and miss - business, so I do it here with the exteriorCells - table:
UPDATE exteriorCells SET region = 'my region' WHERE gridX = 120 AND gridY = -33;
These two look especially fantastic, and will be very handy soon. Thanks again for this program!

I'm a total tool when it comes to programming and computer languages, so I'm afraid I can't offer any feedback here, but I'll try to fiddle with this program around the weekend.

I was wondering, can this program be used to randomly delete a specified percentage of select objects from a cell? For a while already I've been looking for a way to get this done - I used too high numbers when generating grass for my Druadach claims, especially compared to how it's handled in the forest and up the mesas, and it would be good to have it's quantity cut down by like 1/3, or just have a better part of flora_grass_01_03 removed, maybe?

User avatar
worsas
Project Administrator
Posts: 2678
Joined: Wed Dec 31, 2014 1:23 am

Post by worsas »

For the Druadach Highlands keep in mind that grass ground should get more grass than dirt or forest ground. So the amount of grass in the druadach might be totally fine, but i don't have the file infront of myself, so cannot judge well. Just compare it to other grass surfaces, I'd say. Randomly deleting objects is not possible with this tool at this point, but it could be added in with some work.

User avatar
worsas
Project Administrator
Posts: 2678
Joined: Wed Dec 31, 2014 1:23 am

Post by worsas »

Since the old download link broke, here is a new one:
https://www.dropbox.com/s/m42ub6yjn1ty2 ... r.jar?dl=1

User avatar
Scamp
Lesser Daedra
Posts: 465
Joined: Sat Jan 03, 2015 2:51 pm
Location: Kilkreath Mountains
Contact:

Post by Scamp »

Just want to say this is one of the best things that happened to PT, thanks for this fantastic tool. Makes a lot of things much, much easier and less prone to errors.
Everyone who isn't using this instead of ye olde "rename cell and delete in TESAME" is missing out big time.

User avatar
worsas
Project Administrator
Posts: 2678
Joined: Wed Dec 31, 2014 1:23 am

Post by worsas »

I uploaded a new version, which handles exterior cells with illegalToSleepHere=1 the correct way. Previously those cells were not listed in the exteriorCells-table and couldn't be edited or deleted. Also, now if an exterior cell with a pathgrid is deleted, the pathgrid will be deleted aswell.

User avatar
Scamp
Lesser Daedra
Posts: 465
Joined: Sat Jan 03, 2015 2:51 pm
Location: Kilkreath Mountains
Contact:

Post by Scamp »

Great! Thanks for your work on this, worsas.

User avatar
worsas
Project Administrator
Posts: 2678
Joined: Wed Dec 31, 2014 1:23 am

Post by worsas »

A new version has been uploaded.

Changes:
- A new table interiorCells has been added. This allows one to mass-edit properties of a big amount of interior cells, such as lighting settings or names.
- The tables exteriorCells and interiorCells have gotten a new column objectCount. This can be used, for example, to clear a finished exterior claim from empty, adjacent exterior cells that don't belong to the claim.
- the function RAND() can now be used in number expressions. One usage example would be randomly deleting a certain percentage of cellObjects in a cell. Please note that there is no support for LIMIT or ORDER BY yet.
- You no longer have to copy and paste the name of the file, you want to edit, into the file textfield. You can use a file selection menu instead.
Spoiler
Makes all interior cells in the file where one may sleep in (dungeons and caves) 40% darker

UPDATE interiorCells SET ambientRed=ambientRed*0.6, ambientGreen=ambientGreen*0.6, ambientBlue=ambientBlue*0.6, sunlightRed=sunlightRed*0.6, sunlightGreen=sunlightGreen*0.6, sunlightBlue=sunlightBlue*0.6 WHERE illegalToSleepHere=0;
Spoiler
Removes all exteriorCells from the file no objects were added to

DELETE FROM exteriorCells WHERE objectCount = 0;
Spoiler
Randomly deletes roughly 50% of the objects from cell 'ExampleCell'

DELETE FROM cellObjects WHERE cell='ExampleCell' AND RAND() < 0.5

Post Reply

Return to “General Discussion”