PostgreSQL question *SOLVED!!!*

pfunkyfize

Hi all, this is probably the closest place for me to ask this question-

I understand that PostgreSQL is the DB being used in DAZ. I write SQL/DML queries in products like Oracle and MySQL relational databases but have never touched PostgreSQL in any of my job sites at my day gig. Is there a way for me to get to the actual tables in DAZ's postgresql database so that I can edit data across thousands of products that exist in my catalog without using the metadata editor?  I imagine if I can find the relational database manager for postgresql and connect it to DAZ's DB tables it will be possible to do with UPDATE queries and the like. As far as I know, we as users can only Update/Create/Delete one product set of records at a time in DAZ Studios metadata editor in the Content Library. For editing data across multiple products, this is very tedious and cumbersome...

If there IS a way to do this via some sort of external RDBMS for PostgreSQL that would be AWESOMEEEE

Thanks for any suggestions!

Richard Haseltine

I'm not sure about the SDK, but scripting (next door) has tools for accessing metadata

pfunkyfize

Richard Haseltine said:

I'm not sure about the SDK, but scripting (next door) has tools for accessing metadata

Thanks! Is it as powerful as batched SQL commands to take on gigantic amount of records across multiple tables?

Richard Haseltine

pfunkyfize said:

Richard Haseltine said:

I'm not sure about the SDK, but scripting (next door) has tools for accessing metadata

Thanks! Is it as powerful as batched SQL commands to take on gigantic amount of records across multiple tables?

Sorry, I have no idea.

pfunkyfize

I did see the sample scripts in the scripting area and I have modified one to pull product names and IDs of products that are assigned to LOCAL USER. That does solve part of a problem

pfunkyfize

So I opened the postresql RDBNS tool in the server directory and saw something very peculiar, the database contains 0 Tables! How on earth is DAZ Studio storing the metadata and CMS data if they aren't using any Tables in PostgreSQL???

This IT guy wants to know :) I  want the power to be able to write SQL instructions that reports/queries data stored inside the database at a massive level. DAZ has provided the DB environment with the Postgresql database installation that comes with Studio. Yes I realize this is a very powerful and destructive tool but I do this every day with my day gig so I will be less likely to blow up the database with a DELETE DML statement with no WHERE clause compared to a casual DAZ user.

I looked at the code example found here that reports on all the products in the CMS database-

http://docs.daz3d.com/doku.php/public/software/dazstudio/4/referenceguide/scripting/api_reference/samples/metadata/list_products/start

The SQL command entered at the command line or RDBMS interface to retrieve the same information that the above sample script does would be:

 SELECT DAZ_PRODUCT_NAME FROM DAZ_PRODUCT_MASTER_TABLE -- That's all folks!

I don't know what the columns or table names really are because we can't see them in the admin tool provided by DAZ with the postgresql installation so DAZ_PRODUCT_NAME would be the column name that the DAZ guys made to store the product name and the DAZ_PRODUCT_MASTER_TABLE would be the table that holds the DAZ_PRODUCT_NAME column.

See how dramatically succinct that 1 line of SQL code is when compared to the DAZ script that does the same thing? SQL can be used in the PGadmin tool provided but for some reason the database objects are not being revealed in PGadmin ...

How is DAZ storing data in the PostgreSQL database if there are no tables to hold that data?

Thanks!

See attached

Richard Haseltine

Daz doesn't publish the tables, in part at least as by keeping it all internal (and giving access as in the scripting tools to results) theya re free to adapt the system as needed. There was, if I recall, an internal rearrangement in DS 4.9 which meant databases were translated into a new form (leaving the old in place, for compatibility) - if they had published the internal details that would have been much harder, or even impossible, to do (without breaking add-ons and user-generated tools).

pfunkyfize

Thanks for the reply Richard,

Why would DAZ give us the ability to look at the database directly by shipping the PGadmin tool with PostgreSQL's Studio installation if they did not intend for us SQL geeks to use it?

andya_b341b7c5f5

pfunkyfize said:

Thanks for the reply Richard,

Why would DAZ give us the ability to look at the database directly by shipping the PGadmin tool with PostgreSQL's Studio installation if they did not intend for us SQL geeks to use it?

pgAdmin is part of the default PostgreSQL download.  I guess they just didn't remove it.  Even if they had, you could have downloaded it separately.  The obvious way to stop you or me from inadvertently wrecking the DB that Daz Studio uses is to hide the relevant objects from users (though there are ways to get around that).  It avoids the support team having to deal with the wreckage, or anger users by saying 'you broke it and we don't support you when do that'.

pfunkyfize

andya_b341b7c5f5 said:

pgAdmin is part of the default PostgreSQL download.  I guess they just didn't remove it.  Even if they had, you could have downloaded it separately.  The obvious way to stop you or me from inadvertently wrecking the DB that Daz Studio uses is to hide the relevant objects from users (though there are ways to get around that).  It avoids the support team having to deal with the wreckage, or anger users by saying 'you broke it and we don't support you when do that'.

Awesome!

I am an Oracle/SQLite/MysQL/SQL Server/DB2 guy and never used PostgreSQL though I imagine it's really similar since the other SQL tools are virtually the same, how would you unhide the objects in the PGAdmin tool? No wrecking DBs here, nothing SELECTs can wreck at least ;)

andya_b341b7c5f5

pfunkyfize said:

andya_b341b7c5f5 said:

pgAdmin is part of the default PostgreSQL download.  I guess they just didn't remove it.  Even if they had, you could have downloaded it separately.  The obvious way to stop you or me from inadvertently wrecking the DB that Daz Studio uses is to hide the relevant objects from users (though there are ways to get around that).  It avoids the support team having to deal with the wreckage, or anger users by saying 'you broke it and we don't support you when do that'.

Awesome!

I am an Oracle/SQLite/MysQL/SQL Server/DB2 guy and never used PostgreSQL though I imagine it's really similar since the other SQL tools are virtually the same, how would you unhide the objects in the PGAdmin tool? No wrecking DBs here, nothing SELECTs can wreck at least ;)

I don't think it's my place to tell you how to do something that Daz apparently doesn't want you to do.  However, with your skillset, the internet and the (excellent) PostgreSQL docs, I feel sure you will be able to work it out for yourself.

pfunkyfize

andya_b341b7c5f5 said:

I don't think it's my place to tell you how to do something that Daz apparently doesn't want you to do.  However, with your skillset, the internet and the (excellent) PostgreSQL docs, I feel sure you will be able to work it out for yourself.

Were you just being neighborly or were you suggesting a thinly veiled RTFM? Hopefully the former and not the latter ;)

pfunkyfize

Richard Haseltine said:

Daz doesn't publish the tables, in part at least as by keeping it all internal (and giving access as in the scripting tools to results) theya re free to adapt the system as needed. There was, if I recall, an internal rearrangement in DS 4.9 which meant databases were translated into a new form (leaving the old in place, for compatibility) - if they had published the internal details that would have been much harder, or even impossible, to do (without breaking add-ons and user-generated tools).

There's a saying in the IT business,

'Change Happens...'

It means that we as subscribers to any IT driven solution have to adapt to any changes that roll down to us by the architects of a solution whether we like the changes or not. The database architects are free to change what they want with their schema and it's up to us supporters to change along with them. So that means that publishing the internals on how something works is not a bad thing because software is a living thing. It will evolve and change and it's up to the technical user base to change along with it. I see no problems, only upsides with allowing direct SQL communication with the database and not have to be hobbled by lots of script writing to accomplish a small subset of what SQL can do.

pfunkyfize

Geez, why do i feel like I'm at work? LOL

Omniflux

It looks like you are connecting to the wrong instance of PostgreSQL

Try localhost:17237 (unless you changed the port in the configuration files) with username "dzcms" and no password.

pfunkyfize

Omniflux said:

It looks like you are connecting to the wrong instance of PostgreSQL

Try localhost:17237 (unless you changed the port in the configuration files) with username "dzcms" and no password.

Thanks, I'll give it a shot, I've been poking around the directory structure of Postgresql 12 that I installed recently and the DAZ CMS installation and it looks like the DAZ installation does not have a /data directory so any config files there might not be getting loaded at server startup time. I haven't given it a shot yet but thanks for the suggestion and I'll give it a try when I get home tonight!

pfunkyfize

Omniflux said:

It looks like you are connecting to the wrong instance of PostgreSQL

Try localhost:17237 (unless you changed the port in the configuration files) with username "dzcms" and no password.

Came home quickly and didn't waste time trying it-

GOLD!!!!!!!

Thanks Omni! I am not new to SQL but I am new to PostgreSQL so I'd like to know where you looked to find the port number and the login credentials for the localhost Server/Database. The data folder isn't even installed in the DAZ version of PostgreSQL so I am wondering how we are able to see the tables without any configuration....

I can now look at my products in a more powerful way with referential keyed indexing through SQL and not have to make some long QT script that pings the database to do it...

I did a simple SELECT query for all my product records on the Product table and saw all my products there- It's a beautiful thing.

Go to the head of the class Omni ;)

pfunkyfize

After looking through the data in the tables I can tell i'll be using lots of joined DML on my Product and Content tables, the functions are telling me how DIM works in the background to update the DB. this is an awesome find.

I'll be able to solve my product problems a bit easier now that I can report on the right associations in the database. Thanks again Omni!

 

pfunkyfize

DELETE FROM PRODUCTS;

DROP TABLE CONTENT;

COMMIT;

No just kidding *DO NOT* do this to anyone who may be peeking in. This will cause a major cardiac event - you've been warned ;)

bytescapes

I used PostgreSQL at my last job. It's similar enough to other SQL dialects that you should have no problem adapting. It's actually a pretty nice DBMS; a fair number of people I know prefer it to MySQL, and it has specialist extensions for things like GIS (which is what we used it for).

I did once have to do some spelunking into the DAZ CMS to recover a borked content manager. If I remember correctly, the PostgreSQL transaction logs had become corrupted following a disk problem, and the only way I was able to get the CMS back on its feet was by blowing away the corrupted logs. What I saw at the time suggested to me that it's a fairly standard, if slightly elderly PostgreSQL. I think it might be 9-dot-something, and PostgreSQL is now up to 12.4. You should verify, and make sure that the docs you're referring to cover the actual version in use.

Personally, I'd want to be really cautious, because you could easily corrupt the content manager's database by accessing it directly. Using the scripting tools as Richard suggests is not only safer but more likely to be future-proof. When I had my corruption issue, I used the Mac's Time Machine backup system to roll back to an earlier database state: I suggest that you make sure you have a known-good version of the database securely backed up so you can revert if the worst happens.

Omniflux

The CMS configuration is at "%APPDATA%\DAZ 3D\cms\cmscfg.json". It contains the configuration location for launching PostgreSQL when needed and the port for DAZ applications to connect to.

The username is listed in a commented out line in pg_hba.conf.

pfunkyfize

Nevermind my last set of questions, just had another Eureka moment, it was bothering me that I couldn't find where the login credentials and port you mentioned were so i did a grep equivalent on my hard drive for 'dzcms'  and walked away for awhile. The requisite conf files were in the USER\Appdata\... folder hierarchy and NOT the Program Files\DAZ 3d\PostgreSQL CMS\ folder that looked like the logical place to run it. It was making me crazy that an installation of PostgreSQL was operating without a /data configuration folder so this discovery makes it all better now... ;)

pfunkyfize

Now to pick apart the triggers and functions to see how the cascading trails work...

pfunkyfize

bytescapes said:

I used PostgreSQL at my last job. It's similar enough to other SQL dialects that you should have no problem adapting. It's actually a pretty nice DBMS; a fair number of people I know prefer it to MySQL, and it has specialist extensions for things like GIS (which is what we used it for).

I did once have to do some spelunking into the DAZ CMS to recover a borked content manager. If I remember correctly, the PostgreSQL transaction logs had become corrupted following a disk problem, and the only way I was able to get the CMS back on its feet was by blowing away the corrupted logs. What I saw at the time suggested to me that it's a fairly standard, if slightly elderly PostgreSQL. I think it might be 9-dot-something, and PostgreSQL is now up to 12.4. You should verify, and make sure that the docs you're referring to cover the actual version in use.

Personally, I'd want to be really cautious, because you could easily corrupt the content manager's database by accessing it directly. Using the scripting tools as Richard suggests is not only safer but more likely to be future-proof. When I had my corruption issue, I used the Mac's Time Machine backup system to roll back to an earlier database state: I suggest that you make sure you have a known-good version of the database securely backed up so you can revert if the worst happens.

Absolutely! I love SQL and there's no way I'm not going to experiment on my data but with all the precautions that a software developer is accustomed to. ( backed up data) and a development set of libraries and environment. So far all I've done is make some joined SELECT queries and studying the table relationships and key constraints. The triggers and functions that are baked in with the tables are not too complex actually and there's only a couple of handfulls of them. I'm getting fed up with the metadata editor and it crashes my system when I apply it to the LOCAL USER product. Once I figure out how a product record gets ingested in the database and records cascade to the appropriate tables I'll write more scripts that update the 'production' metadata files. I will definitely keep that lessons learned about the log file corruption - thanks!

pfunkyfize

Omniflux said:

The CMS configuration is at "%APPDATA%\DAZ 3D\cms\cmscfg.json". It contains the configuration location for launching PostgreSQL when needed and the port for DAZ applications to connect to.

The username is listed in a commented out line in pg_hba.conf.

LOL,

Had I hit the refresh button while I was typing out my discovery after using a recursive findstr operation, I probably wouldn't have gone down that path since your answer was already there and I didn't see it. Oh well, I found all the .conf files with the port references and user name dzcms references. It was driving me bonkers when you were asking if I changed the conf settings and I was throwing my hands in the air because there wasn't even a data directory to hold any conf files in the directory I was looking inside. When I was doing early investigation i was trying to find the right directory by using the task manager to find all the postgresql services and when I found them I opened the location where they were executing from and it was dumping me into the DAZ installation directory with the binaries running, so when I didn't see any configuration files (only samples) it was bugging me that if they weren't here they had to be somewhere else so the grep/findstr command helped out. But thanks again, I was only an F5 refresh away from not missing the boat lol

pfunkyfize

bytescapes said:

I did once have to do some spelunking into the DAZ CMS to recover a borked content manager. If I remember correctly, the PostgreSQL transaction logs had become corrupted following a disk problem, and the only way I was able to get the CMS back on its feet was by blowing away the corrupted logs. What I saw at the time suggested to me that it's a fairly standard, if slightly elderly PostgreSQL. I think it might be 9-dot-something, and PostgreSQL is now up to 12.4. You should verify, and make sure that the docs you're referring to cover the actual version in use.

Yeah it's up to V 12 now and I have stopped using PGadmin 3 and using the webbrowser based DBMS for PGadmin 4 that comes with V12 to access the CMS DB now, it's much cleaner looking and easier to get around in. Viewing the data is a nice feature by just clicking the view button and not have to type out each Select...

pfunkyfize

bytescapes said:

I used PostgreSQL at my last job. It's similar enough to other SQL dialects that you should have no problem adapting. It's actually a pretty nice DBMS; a fair number of people I know prefer it to MySQL, and it has specialist extensions for things like GIS (which is what we used it for).

I am actually enjoying it! I am seeing the PosgreSQL nuances and those are tripping me up like referring to table names with  " " for some but not all of them. There's a TON of GIS positions opening up by me. I am getting a bunch of interview requests in my linkedin and IT recruiter posts mainly C++ and SQLite/PostgreSQL/MySQL right now. Interesting you mentioned GIS...