GUID Use In Metadata - Content Package Assist

pfunkyfize

Hi all, this post is in regards to my SQL solution for this post-

https://www.daz3d.com/forums/discussion/430666/content-database-products-getting-deleted/p1

So at this point in the quest, I've learned how to open the DAZ Studio database in the PostgrSQL admin tool so that I can actually see all the data records stored in our content library. I have identified that the problem outlined in the above mentioned post is caused by missing records in the Product and Contents table of the CMS database. My solution to the problem is to reload the missing records via SQL scripts and SQL/DML code.

Before I lay out the INSERT portion for the records I did a joined SELECT on all of my owned DAZ content in the database to see if I can find out the rhyme or reason to the GUID characters that seems to be in all the DSX files that loads the metadata. Apparently, the GUID is sometimes missing in the database records and the Product table does not even make it a UNIQUE or even a mandatory field in the Product table creation SQL. HUH???  That means, I could populate each new product I want to load with a GUID =  "THIS-IS-REALLY-REALLY-STUPID" for every product I load from now until forever and it would be accepted by the database.

What is the GUID actually used for if it's not even required in the Product table in the content database?

I've been using the Content Package Assist to make custom dims for content I buy outside of the store here and there's an option to make a GUID key for the product you're making a zip installer for. in the Content Package Assist, it can NOT be empty and is therefore a required field.

Why is the GUID a required field in the Content Package Assist if its data is not a mandatory field (aka missing NOT NULL designation) in the Product table of our CMS?

I plan on using SQL to reload all my busted products in one fell swoop but I want to know what the GUID is used for so I am not blowing anything up or destroying referential integrity. From the looks of this though, I don't even have to worry about it. It just seems every DSX I see from the DAZ end seems to have GUID values but the database isn't storing every single one. The value looks like it is setup to be really really unique too with this dash delimited character sequence it uses but the Product table doesn't even require the format or uniqueness either... That's the mystery here.

See attached screencap.

Thanks!

pfunkyfize

As a side note,

I am seeing that the date I bought an item isn't showing up in the Product table for some items. Why is that?

pfunkyfize

INTERESTINGLY,

The table called "tblProduct" DOES have GUID field with a NOT NULL designation. BUT that table holds a small and inaccurate amount of product records in my actual collection from DAZ so that can't be the table that holds my actual inventory of DAZ items. I am 99.9% certain that the Product and Content table are the main master tables that ecapsulate my actual collection because when I load a DIM zipped file and query the Product and Content table, the new product shows up there...

kenshaw011267

GUID is a Globally Unique IDentifier. There are several ways they can be generated, the most common way is a Windows system call which produces one based on the Windows system clock.

In databases they are usually used to create an ID for a record so that joins can be done on definitely unique key.

I have no idea how or why the GUID is getting fouled up. You should be able to see the table definition through the admin tool. I really doubt the GUID is allowed to be NULL.

Richard Haseltine

I'm wary of commenting, but one thing I do know (which harks back to Daz' not wanting to make the direct interface with the database available so they can chnage its structure) is that there was a substantial change around DS 4.9 - the old tables can be read in and converted to the new (which is done when DIM installs, or was in the past). It's possible that you are seeing the tables from the old fomrat for files that were saved directly to the new, and so were left unpopulated. Note that this is a very tentative suggestion, not a statement of fact.

pfunkyfize

kenshaw011267 said:

GUID is a Globally Unique IDentifier. There are several ways they can be generated, the most common way is a Windows system call which produces one based on the Windows system clock.

In databases they are usually used to create an ID for a record so that joins can be done on definitely unique key.

I have no idea how or why the GUID is getting fouled up. You should be able to see the table definition through the admin tool. I really doubt the GUID is allowed to be NULL.

Hi Kenshaw,

If you check out the file attached to the original post, I have the admin tool open to the Products table which indeed shows that the GUID field has no field constraints whatsoever so it IS allowed to be NULL. (There isn't a NOT NULL constraint or a UNIQUE constraint like the primary key fields above it) so it can be a repeatable or it can be left empty and a whole lot of trash can be entered in it (or not). So in effect, it is a useless text field. If a script called from DAZ wants to find a GUID for all of the records, it can't because some GUID fields are empty. There's a table called 'tblProduct' in the schema that does have a GUID field with a NOT NULL constraint, however, the amount of records in my case is about 600-700 records and my collection of DAZ items is about 4,000+ products so you can guess that the tblProduct table is not being used to store all of my product records like the Product table is doing. I haven't figured out what that tvlProduct table is for yet because it has no foreign key constraints to the Product table so they are similar but not referentially related.  In that attachment is a joined query to the product owner table to show me all my owned products and there are 100 records without GUID values. So that's why I wonder why GUID is being used, it's not uniquely identifying anything because the data fields don't have to be unique. (Or even exist). I would agree that the GUID is supposed to identify a unique installation of a product but as the database sees it, it's ok if it is repeated, blank or just plain wrong format. That defeats the purpose of a unique identifier so why do we bother putting GUID values in all these metadata DSX files if the field in the receiving table is just a container of anything you want to put in there?

 

pfunkyfize

Richard Haseltine said:

I'm wary of commenting, but one thing I do know (which harks back to Daz' not wanting to make the direct interface with the database available so they can chnage its structure) is that there was a substantial change around DS 4.9 - the old tables can be read in and converted to the new (which is done when DIM installs, or was in the past). It's possible that you are seeing the tables from the old fomrat for files that were saved directly to the new, and so were left unpopulated. Note that this is a very tentative suggestion, not a statement of fact.

That could be an explanation for why the table/schema is a mess but it doesn't explain how you avoid unique identifier collisions if the table column is just a regular text description field with no constraints on the data. Before being able to see the database structure, I've always wondered how the GUID was made unique and there was never any problems with colliding uniqueness, now I know - because there are no constraints and the GUID isn't uniquely identifying anything... LOL

What makes it funny is that the GUID is this long dash delimited sequence that looks like it's unique but in all reality it's just filler .

It's like The Wizard of OZ is behind the curtainis telling his subjects"Ok, you must do the hokey pokey and turn yourself about before you can open the door at the far end of the hall" all the while he's laughing because they're doing the hokey pokey for no reason at all and the door at the end of the hall can just open by turning the doorknob...

kenshaw011267

pfunkyfize said:

kenshaw011267 said:

GUID is a Globally Unique IDentifier. There are several ways they can be generated, the most common way is a Windows system call which produces one based on the Windows system clock.

In databases they are usually used to create an ID for a record so that joins can be done on definitely unique key.

I have no idea how or why the GUID is getting fouled up. You should be able to see the table definition through the admin tool. I really doubt the GUID is allowed to be NULL.

Hi Kenshaw,

If you check out the file attached to the original post, I have the admin tool open to the Products table which indeed shows that the GUID field has no field constraints whatsoever so it IS allowed to be NULL. (There isn't a NOT NULL constraint or a UNIQUE constraint like the primary key fields above it) so it can be a repeatable or it can be left empty and a whole lot of trash can be entered in it (or not). So in effect, it is a useless text field. If a script called from DAZ wants to find a GUID for all of the records, it can't because some GUID fields are empty. There's a table called 'tblProduct' in the schema that does have a GUID field with a NOT NULL constraint, however, the amount of records in my case is about 600-700 records and my collection of DAZ items is about 4,000+ products so you can guess that the tblProduct table is not being used to store all of my product records like the Product table is doing. I haven't figured out what that tvlProduct table is for yet because it has no foreign key constraints to the Product table so they are similar but not referentially related.  In that attachment is a joined query to the product owner table to show me all my owned products and there are 100 records without GUID values. So that's why I wonder why GUID is being used, it's not uniquely identifying anything because the data fields don't have to be unique. (Or even exist). I would agree that the GUID is supposed to identify a unique installation of a product but as the database sees it, it's ok if it is repeated, blank or just plain wrong format. That defeats the purpose of a unique identifier so why do we bother putting GUID values in all these metadata DSX files if the field in the receiving table is just a container of anything you want to put in there?

 

No,it is supposed to be for indexing.

The last line of the table def makes an index off guid and makes guid unique.

pfunkyfize

kenshaw011267 said:

No,it is supposed to be for indexing.

The last line of the table def makes an index off guid and makes guid unique.

Nice! You found a constraint! I totaly missed it at the bottom

However, it can still be NULL so it's still useless for indexing... and I have 100 records with a NULL value in the GUID field just from the DAZ products. I don't know how many NULL GUIDS I have for my products outside of DAZ so there could be a lot more. So is the GUID indexing important for just our local installation or is it something that DAZ as a company needs also? AFAIK, if you or I as a customer go back to DAZ with a problem in one of our purchased downloads all they would need to know is an order number (to prove we bought the item in the first place) and then give us a new download /fix the problem and wouldn't even use the GUID as a unique key for DAZ support staff trying to help us... Also, would any of you guys know if there are DAZ scripts that cross reference or use the GUID in any way?

When I use the DIM to uninstall a product, I noticed that the associated records DO NOT get deleted from the CMS. When I noticed that my database was blowing up was when I saw that the products in my Content Library that are not from DAZ had no records in the Product or Content tables then that's where I started to follow the meandering trail...

pfunkyfize

I can see that the Content Package Assist product I bought from here can guarantee uniqueness because apparently SQLite is baked into it with a Sqlite.dll. It could keep track of all the GUIDs it writes on manifest files inside its own relational database.

Thanks guys, this discussion is making me understand what the GUID is really used for and will help me write my SQL INSERT solution to fix my busted external products.

If I get hungry enough I'm going to migrate the content database to PostgreSQL 12 - once my products are fixed of course....lol

kenshaw011267

pfunkyfize said:

kenshaw011267 said:

No,it is supposed to be for indexing.

The last line of the table def makes an index off guid and makes guid unique.

Nice! You found a constraint! I totaly missed it at the bottom

However, it can still be NULL so it's still useless for indexing... and I have 100 records with a NULL value in the GUID field just from the DAZ products. I don't know how many NULL GUIDS I have for my products outside of DAZ so there could be a lot more. So is the GUID indexing important for just our local installation or is it something that DAZ as a company needs also? AFAIK, if you or I as a customer go back to DAZ with a problem in one of our purchased downloads all they would need to know is an order number (to prove we bought the item in the first place) and then give us a new download /fix the problem and wouldn't even use the GUID as a unique key for DAZ support staff trying to help us... Also, would any of you guys know if there are DAZ scripts that cross reference or use the GUID in any way?

When I use the DIM to uninstall a product, I noticed that the associated records DO NOT get deleted from the CMS. When I noticed that my database was blowing up was when I saw that the products in my Content Library that are not from DAZ had no records in the Product or Content tables then that's where I started to follow the meandering trail...

It is common practice to not delete rows in a table ever but to rather mark them in some way to show that they have a status equivalent to "deleted." There are good reasons for this. You can discussion so fthis online of you look. It's way too involved for a forum post.

That nulls are allowed in guid is likely to allow third pqarty products. But who knows.

pfunkyfize

kenshaw011267 said:

It is common practice to not delete rows in a table ever but to rather mark them in some way to show that they have a status equivalent to "deleted." There are good reasons for this. You can discussion so fthis online of you look. It's way too involved for a forum post.

That nulls are allowed in guid is likely to allow third pqarty products. But who knows.

Yep!

That's what clued me into the problem with my disappearing products , because the records were deleted-

I think the nulls are there for a different purpose like free items as I look closer at my data. If that's the case then the GUID is used for local installation indexing only (and I use the term indexing loosely until I figure out why some GUIDs are null) since DAZ as a company doesn't need to know our local GUIDs DAZ could just not even populate it. But that's a theory that is only now just popping up because it seems all the free stuff we get from DAZ is showing up with no GUIDs. The CPA doesn't allow a null GUID field and it knows how to keep whatever it makes unique because it stores what it gave out in its own database, SQLite.