Database overview needed

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Database overview needed

Frank W Sweet
Would some kind-hearted DB designer be willing to volunteer to post a
short overview of the Opensim database into the Wiki? For example, I
have never fully understood the difference between an "asset" and an
"inventory item. I gather that if I have three copies of a specific
script in inventory or in prims, say, I have only one asset. But what
happens when I modify one of the copies? Perhaps a one-sentence
definition of each table (and how it relates to other tables in an E-R
sense) would to the trick.

Frank W Sweet
Backintyme Publishing

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database overview needed

Jeff Ames
There is some existing documentation at
http://opensimulator.org/wiki/Database:Documentation and the pages
linked there.

As for assets versus inventory items, an asset is the object itself,
whereas the inventory item simply points to the asset (via the asset
ID), and adds some information about the asset that the inventory
needs to be aware of, such as permissions.

When assets are modified, a copy is created, since we can't be sure
that there is only one reference to the original asset.

(I believe this is accurate, but those of you more familiar with
current asset handling, please correct me if I'm mistaken.)

Jeff
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database overview needed

Frank W Sweet
Thank you so much! I had no idea that section of the Wiki was out
there!

One other question: Since there is no reference counter in "asset" for
inventory-to-asset references (nor in "userfriend" for
user-to-userfriend references, nor in any other table receiving
one-to-many relationships, for that matter), I assume that there are
utility procedures in the code that are run periodically to purge
unreferenced records and to check referential integrity. Where in the
code would these be, and how often are they run (at startup, shutdown,
every so often)?

Frank W Sweeet
Backintyme Publishing

----- Original Message -----
From: "Jeff Ames" <[hidden email]>
To: "Frank W Sweet" <[hidden email]>;
<[hidden email]>
Sent: Monday, March 02, 2009 10:58 PM
Subject: Re: [Opensim-users] Database overview needed


There is some existing documentation at
http://opensimulator.org/wiki/Database:Documentation and the pages
linked there.

As for assets versus inventory items, an asset is the object itself,
whereas the inventory item simply points to the asset (via the asset
ID), and adds some information about the asset that the inventory
needs to be aware of, such as permissions.

When assets are modified, a copy is created, since we can't be sure
that there is only one reference to the original asset.

(I believe this is accurate, but those of you more familiar with
current asset handling, please correct me if I'm mistaken.)

Jeff

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database overview needed

Impalah Shenzhou
Not, there aren't any, at least in the "original" opensim servers.

Asset server is a huge store of anything, even the generated maps and terrain revisions (opensim region servers). Not only inventory assets are stored there.

Asset server and inventory server are separated processes each one with their own data models. The connection between them is assetID(inventoryItems)->id(assets) but there aren't any foreign keys. You could use different databases for assets/inventory.

I have only doubts about the new AssetsInventory server, I hadn't time to play with it.

Greetings

2009/3/3 Frank W Sweet <[hidden email]>
Thank you so much! I had no idea that section of the Wiki was out
there!

One other question: Since there is no reference counter in "asset" for
inventory-to-asset references (nor in "userfriend" for
user-to-userfriend references, nor in any other table receiving
one-to-many relationships, for that matter), I assume that there are
utility procedures in the code that are run periodically to purge
unreferenced records and to check referential integrity. Where in the
code would these be, and how often are they run (at startup, shutdown,
every so often)?

Frank W Sweeet
Backintyme Publishing

----- Original Message -----
From: "Jeff Ames" <[hidden email]>
To: "Frank W Sweet" <[hidden email]>;
<[hidden email]>
Sent: Monday, March 02, 2009 10:58 PM
Subject: Re: [Opensim-users] Database overview needed


There is some existing documentation at
http://opensimulator.org/wiki/Database:Documentation and the pages
linked there.

As for assets versus inventory items, an asset is the object itself,
whereas the inventory item simply points to the asset (via the asset
ID), and adds some information about the asset that the inventory
needs to be aware of, such as permissions.

When assets are modified, a copy is created, since we can't be sure
that there is only one reference to the original asset.

(I believe this is accurate, but those of you more familiar with
current asset handling, please correct me if I'm mistaken.)

Jeff

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users


_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database overview needed

Frank W Sweet
Impalah wrote: The connection between them is
assetID(inventoryItems)->id(assets) but there aren't any foreign keys.

The asset UUID in the inventory record is a foreign key. Located in
one table (inventoryitems), it is the unique key to an entry in a
different table (assets). The problem, in this specific example, is
that if the asset record is copied whenever it is modified, for fear
that some other inventory item might be pointing to it, eventually
most of the asset records in my database will be junk--not referenced
by any item and so unreachable. Worse yet, due to glitches and
crashes, an inventory item might be pointing to a non-existent
asset-ID. (Now that I know the data structure, I have already found
several such situations in my database.)

Frank W Sweet
Backintyme Publishing.

----- Original Message -----
From: "Impalah" <[hidden email]>
To: "Frank W Sweet" <[hidden email]>;
<[hidden email]>
Sent: Tuesday, March 03, 2009 9:12 AM
Subject: Re: [Opensim-users] Database overview needed


Not, there aren't any, at least in the "original" opensim servers.

Asset server is a huge store of anything, even the generated maps and
terrain revisions (opensim region servers). Not only inventory assets
are
stored there.

Asset server and inventory server are separated processes each one
with
their own data models. The connection between them is
assetID(inventoryItems)->id(assets) but there aren't any foreign keys.
You
could use different databases for assets/inventory.

I have only doubts about the new AssetsInventory server, I hadn't time
to
play with it.

Greetings

2009/3/3 Frank W Sweet <[hidden email]>

> Thank you so much! I had no idea that section of the Wiki was out
> there!
>
> One other question: Since there is no reference counter in "asset"
> for
> inventory-to-asset references (nor in "userfriend" for
> user-to-userfriend references, nor in any other table receiving
> one-to-many relationships, for that matter), I assume that there are
> utility procedures in the code that are run periodically to purge
> unreferenced records and to check referential integrity. Where in
> the
> code would these be, and how often are they run (at startup,
> shutdown,
> every so often)?
>
> Frank W Sweeet
> Backintyme Publishing
>
> ----- Original Message -----
> From: "Jeff Ames" <[hidden email]>
> To: "Frank W Sweet" <[hidden email]>;
> <[hidden email]>
> Sent: Monday, March 02, 2009 10:58 PM
> Subject: Re: [Opensim-users] Database overview needed
>
>
> There is some existing documentation at
> http://opensimulator.org/wiki/Database:Documentation and the pages
> linked there.
>
> As for assets versus inventory items, an asset is the object itself,
> whereas the inventory item simply points to the asset (via the asset
> ID), and adds some information about the asset that the inventory
> needs to be aware of, such as permissions.
>
> When assets are modified, a copy is created, since we can't be sure
> that there is only one reference to the original asset.
>
> (I believe this is accurate, but those of you more familiar with
> current asset handling, please correct me if I'm mistaken.)
>
> Jeff
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database overview needed

Impalah Shenzhou
It's a "foreign key" in the sense that it references a "record in another table", but for opensim assetID it's only an uuid to be served for the "asset server", not for being recovered directly from the database. That is, you could set a foreign key in your database model (I did it last year when starting with nhibernate, but I was playing in standalone mode) but asset management and inventory management are independent.

When you are in "grid" mode you will have 5 UGAIM + X region servers, each one with its data model and you could be using different schemas/database servers/machines/networks...

If you need to implement a process to take care of referencial integrity between assets and inventory I think (please "masters of the opensim" correct me if I'm wrong) you must implement the "look for shit" process in the inventory server and a REST delete method in the assets server, but taking care with the things you delete, you must be absolutely sure that the asset you are deleting is ONLY and inventory item (and not library asset, region map, terrain, etc.). It isn't anything similar right now (at least not in the 0.6 version I am working on)

The implementation of the asset server is very simple, extremely simple (I did it with less than 100 php code lines.. well... using a mvc framework :-P ). It only has get and save... by now, of course.

I think was Adam Frisby? who made a new asset server with authentication and some other things like storing into amazon's S3 (my memory fails often) but I don't know if this implements something like delete or modify.

So... maybe you need to create the methods for yourself or wait for the next evolution of opensim. If you want to develope them, my advice is to include the possibility to deactivate the behavior (config file or something).

Greetings



2009/3/3 Frank W Sweet <[hidden email]>
Impalah wrote: The connection between them is
assetID(inventoryItems)->id(assets) but there aren't any foreign keys.

The asset UUID in the inventory record is a foreign key. Located in
one table (inventoryitems), it is the unique key to an entry in a
different table (assets). The problem, in this specific example, is
that if the asset record is copied whenever it is modified, for fear
that some other inventory item might be pointing to it, eventually
most of the asset records in my database will be junk--not referenced
by any item and so unreachable. Worse yet, due to glitches and
crashes, an inventory item might be pointing to a non-existent
asset-ID. (Now that I know the data structure, I have already found
several such situations in my database.)

Frank W Sweet
Backintyme Publishing.

----- Original Message -----
From: "Impalah" <[hidden email]>
To: "Frank W Sweet" <[hidden email]>;
<[hidden email]>
Sent: Tuesday, March 03, 2009 9:12 AM
Subject: Re: [Opensim-users] Database overview needed


Not, there aren't any, at least in the "original" opensim servers.

Asset server is a huge store of anything, even the generated maps and
terrain revisions (opensim region servers). Not only inventory assets
are
stored there.

Asset server and inventory server are separated processes each one
with
their own data models. The connection between them is
assetID(inventoryItems)->id(assets) but there aren't any foreign keys.
You
could use different databases for assets/inventory.

I have only doubts about the new AssetsInventory server, I hadn't time
to
play with it.

Greetings

2009/3/3 Frank W Sweet <[hidden email]>

> Thank you so much! I had no idea that section of the Wiki was out
> there!
>
> One other question: Since there is no reference counter in "asset"
> for
> inventory-to-asset references (nor in "userfriend" for
> user-to-userfriend references, nor in any other table receiving
> one-to-many relationships, for that matter), I assume that there are
> utility procedures in the code that are run periodically to purge
> unreferenced records and to check referential integrity. Where in
> the
> code would these be, and how often are they run (at startup,
> shutdown,
> every so often)?
>
> Frank W Sweeet
> Backintyme Publishing
>
> ----- Original Message -----
> From: "Jeff Ames" <[hidden email]>
> To: "Frank W Sweet" <[hidden email]>;
> <[hidden email]>
> Sent: Monday, March 02, 2009 10:58 PM
> Subject: Re: [Opensim-users] Database overview needed
>
>
> There is some existing documentation at
> http://opensimulator.org/wiki/Database:Documentation and the pages
> linked there.
>
> As for assets versus inventory items, an asset is the object itself,
> whereas the inventory item simply points to the asset (via the asset
> ID), and adds some information about the asset that the inventory
> needs to be aware of, such as permissions.
>
> When assets are modified, a copy is created, since we can't be sure
> that there is only one reference to the original asset.
>
> (I believe this is accurate, but those of you more familiar with
> current asset handling, please correct me if I'm mistaken.)
>
> Jeff
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>



_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users