Using SQL to manipulate OARs
As I noted in my previous post “Moving and rotating OARs“, offline editing of Opensim Archives (OARs) opens a lot of interesting possibilities. In this article I present the results of several simple tests I have made by extending my “Zoe” OAR editor so that it supports (a very simplified sub-dialect of) SQL. Even in a simplified form, SQL has a lot of expressive power and allows to perform a number of pretty neat operation on OARs, when it comes to selecting and deleting parts of a region. Updating OARs, though, is much more complicated, because there is no “natural” semantics to apply when using the standard SQL UPDATE statement. Some simple cases can be singled out, however, where “natural” semantics actually make sense — and in some other “interesting” use cases, a custom pseudo-SQL statement seems to be sufficient.
The XML2 SceneObjectGroup format
[Note: You can skip this section if you are familiar with the SceneObjectGroup/SceneObjectPart format.]
OARs are gzipped tarballs — you can find a detailed description of their structure here. Linksets are stored in individual XML files in Opensim’s own XML2 format. Altho the XML2 format is as yet undocumented, manual inspection of the actual files in several OARs and some peeks at the source code allows us to deduce the following:
1) Each linkset (or “SceneObjectGroup” in the Opensim parlance) is stored in an ASCII-encoded XML file with all non-significant white space removed. This means that there are no carriage-return, line-feed, or end-of-file characters, and that there are no extra blanks between tags (i.e., no formatting or prettyprinting).
2) Each linkset (or “SceneObjectGroup”) has the following form:
where “(RootPart)” contains the information about the root part (the root prim of a linkset), “(OtherParts)” contains the information about all the other parts (prims) in the linkset, and the optional “(ScriptStates)” parts.
3) The “(RootPart)” is a “SceneObjectPart”. SceneObjectParts have the following format:
<SceneObjectPart xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> (contents) </SceneObjectPart>
(I’ve indented the tag for readability, but this really occurs in one line, without extra blanks). “(Contents)” is a set of tags describing the properties of the SceneObjectPart (prim). Some of these properties are described using simple tags; for example, the name of a prim is stored as follows:
<Name>(prim or plant name)</Name>
Other properties have a finer structure, and use nested tags with a fixed structure: for example, the spatial position of a prim might be the following:
Finally, other properties have a still more complex structure: for example, a prim may store “inside itself” (i.e., in the “Contents” tab) a number of other prims, scripts, animations, notecards, etc. — this is stored using a “<TaskInventory>” tag, and its contents will be the references to the (variable) contents of the prim. The same is true of the shape of the prim (I am not sure about that, but I think that Opensim stores the prim shapes in a separate SQL table, and that’s probably the reason why the shape information uses its own separate nested tag).
4) “(OtherParts)” is the (possibly empty) ordered collection of all the non-root prims (SceneObjectParts). If the linkset consists of only one part, an empty tag will be used:
On the other hand, if there is more than one part in the linkset, the format will be the following:
where each “(Part)” is in turn a “SceneObjectPart” as described above.
5) “(ScriptStates)” stores the states of running scripts, so that these scripts can be properly restored at OAR loading time. If there are no script states to store, there is nothing (i.e., not even an empty tag) in the XML2 file; if there are script states to store, they are bracketed between “<GroupScriptStates>” and “</GroupScriptStates>” tags.
Implementing SELECT and WHERE
Since the XML2 format is undocumented, I first implemented an agnostic XML parser, i.e., I read everything between “<tag>” and “</tag>” and stored it as-is as the value of the “tag” property of the containing object. Then I looked at the XML files and refined the parser to add hints to deserialize vectors (for example for the “GroupPosition” property) and quaternions (for example, for the “RotationOffset” property), as well as UUIDs (for example “CreatorID” or “UUID”).
I then implemented a simplified SQL WHERE condition parser and an algorithm to detect when an object matched a specified condition:
Now for simple properties like “Name” or “Description” this worked very well. But what about more complicated properties, like “GroupPosition”?
This is not bad, but we clearly would need to be able to refer to the individual coordinates of the “GroupPosition” property — fortunately, there is a “natural” notation for that:
i.e., we extend the SQL notation to allow the use of subproperties of a “column”.
Of course, having to manually specify “GroupPosition.X”, “GroupPosition.Y”, etc. all the time is very cumbersome. It would be very nice if we could define an alias, of, say, “x” for “GroupPosition.X”, “y” for “GroupPosition.Y”, and so on:
This looks even nicer using an “AS” clause:
Using CALL IT
The alias facility has another important use: let’s assume, for example, that we want to manipulate objects created by me — but “me” (that is, “Zonja Capalini”) is identified in the OAR by an UUID, not by my name. I do know that I’m the creator of the 2889-prims Klein bottle:
We can copy and paste the creatorid to create an alias, or we can use the following hack command:
This is very handy — CALL IT only works when the previous instruction has been a SELECT with a one-row, one-column result set. Let’s see it in action:
This looks pretty neat! (Of course “y” and “z” have been conveniently aliased before executing this SELECT command).
Some information about objects is either not stored as an explicit object property or is stored somewhere else. For example, the number of parts (prims) in a SceneObject can be easily calculated (it’s the number of SceneObjectParts in the OtherParts section plus one), but it’s not specifically stored anywhere; and the parcel a SceneObject is in is not stored anywhere, but it can be calculated from the parcel information with relative ease. These are two good candidates for our OAR-specific functions:
Here’s another example: we first identify the UUID of Ludmilla Writer, and then we use it (via CALL IT) to select all objects created by Ludmilla that are in the “Mirror Worlds” parcel.
Overall, this looks very easy and practical. Even if I’ve not (yet) implemented support for Shapes or TaskInventories, the expressive power of our very simple dialect of SELECT statements is quite strong. Let’s see what happens with DELETE and UPDATE.
Once we have at our disposal the WHERE functionality described above, implementing DELETE statements is trivial:
Here’s a more sophisticated example:
It’s when we want to extend our till now quite successful model to SQL UPDATE statements when we start to find difficulties.
1) In some simple cases (e.g., the “Name” or “Description” properties), standard SQL UPDATE semantics works well:
This is due to the fact that, in the case of these properties, the semantics for the whole SceneObjectGroup is determined by the root SceneObjectPart, i.e., the name of a linkset is the name of the root prim, and the same holds of its description.
2) For other properties this is not true: for example, altering the “GroupPosition” property implies altering the same property for all the SceneObjectParts in the SceneObjectGroup, not only the root part (see “Moving and rotating OARs”). Something similar happens with other properties.
3) For still other properties, the semantics is unclear: while changing the OwnerID of the root prim should imply changing it too for all SceneObjectParts in the SceneObjectGroup, what does it mean to change the CreatorID? Changing it for the root part only, or changing it for all parts in the linkset?
4) Finally, there are properties that should not be changed by the user: the clearest one is “LinkNum” — LinkNum stores the relative number of the part in the linkset (the root part has LinkNum = 0, the second prim in the linkset order LinkNum = 1, and so on), and we clearly don’t want the user to be able to change that.
Future work, and some open questions
Clarifying UPDATE semantics
While the implementation of SELECT and DELETE semantics allow for a lazy parsing, relatively agnostic, implementation of SceneObjectGroups, UPDATE requires a good knowledge of the semantics of SceneObjectGroups. A conservative approach would be to implement UPDATE functionality only for properties of the SceneObhectGroup that are determined by the corresponding property of the root SceneObjectPart alone (i.e., case (1) above). Updates for case (2) could also be implemented, although this is probably better handled by separate, specialized commands (like “MOVE” or “ROTATE”) to avoid mixing root-part-only and all-parts semantics. Updates for case (3) should probably be forbidden in its direct form, but allowed by the extended select form presented below. Updates for case (4) should be strictly forbidden.
SELECT FROM OBJECTS, SELECT FROM <object>
Readers conversant with SQL will have noticed an omission from all the SQL statements showed here: the reference to a table. This is because all statements refer to the standard “table” OBJECTS, which can be omitted for simplicity. Hence, “Select where name like ‘%Zonja%'” really means “Select FROM OBJECTS where name like ‘%Zonja%'”. This will allow for possible future extensions where, for example, parcel data (or profile information, when/if it is added to OARs) can be displayed or manipulated.
Another interesting possibility is to manage a SceneObjectGroup itself as if it was a table. For example, if “3890889f-49de-4564-acc7-7a0aeb9f319e” is the UUID for the Klein bottle, we should be able to write
Select name, description from "3890889f-49de-4564-acc7-7a0aeb9f319e" where linknum = 23
(here LinkNum is useful), or, using an appropriate ALIAS,
Select name, description from KleinBottle where linknum = 23
DELETE semantics, when applied to linksets, would take care to preserve the LinkNum ordering; for UPDATE statements, semantics is in general also quite obscure and should be clarified.
Using an interactive line-mode shell makes it trivial to implement a simple form of scripting for ZOE — indeed I’ve implemented one, but I have not described it here not to make this article too long. Scripting allows to do very nice things, like defining a set of standard alias (for example, for X, Y and Z), or programming simple modifications to a region (for example, a script cold be written which deleted all Linden plants and then distributed and used as-is by somebody without the knowledge about how to do such a thing).
Programmed object creation
While a combination of INSERT and UPDATE SQL statements seem to be overkill and probably too difficult to use to allow for the programmed creation of new objects, it would be relatively easy to expose an API for that, and use it to build objects offline. I will probably make some experiments in this direction when time allows.
Visual feedback: load oar –update, anyone? :-)
Justin Clark-Casey has hinted, in the opensim-dev mailing list, that he could consider the possibility of saving and loading OARs directly from the filesystem (i.e., without having to unzip and untar the OAR first). This would allow to do something very nice: edit an OAR, and every so often load that oar in a local sim, just to see the results (without having to tar and gzip it every time, which is time-consuming).
It would be still better if the Opensim “load oar” command implemented an “–update” switch in addition to the “–merge” switch introduced in a4d2a9. Currently, the “load oar” command does one of two things: if “–merge” is not specified, it clears all objects from the region, then loads the new objects (and the terrain, etc); if “–merge” is specified, it does not clear the existing objects, but it simply adds the new ones; what “–update” would do is the following: keep all objects that have not changed alone, delete the ones that have been deleted, alter the ones that have been altered, and add the new ones, if any (I feel stupid writing that :-)). When a sim has a lot of objects and only a few ones have changed, this should be much cheaper than deleting everything and recreating it back (but I might be wrong about that).
Then, on a test installation, using a timed script with a “load oar –update” command would implement a great form of visual feedback.
Uding SQL as a way to manipulate OARs is not perfect, but it has a lot of potential, specially for SELECT and DELETE statements. UPDATE semantics needs to be carefully tuned to be of any practically use, but the use of non-standard SQL statements with a standard WHERE clause (e.g., “ADD 256 TO X WHERE …” or “ROTATE 90 DEGREES WHERE …”) can also be very practical.