detailed postgres information

37 views
Skip to first unread message

aussda....@gmail.com

unread,
Apr 27, 2018, 5:22:52 AM4/27/18
to Dataverse Users Community
Hi Everybody,

We're looking for more detailed information about the basic structure of Dataverse's postgres database, something along the lines of a database diagram. We've seen some of the flowchart pngs on github. But is there any additional dvndb documentation out there that could help us better understand the flow of information through the database? We don't have a java development team, so working through the application logic would be challenging for us, but we do have some database people that with a little guidance can help us with tweaks here and there.

Thanks very much for the help. Best, Frank

Donald Sizemore II

unread,
Apr 27, 2018, 5:52:10 AM4/27/18
to dataverse...@googlegroups.com
hello,

does this help?
--
You received this message because you are subscribed to the Google Groups "Dataverse Users Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dataverse-commu...@googlegroups.com.
To post to this group, send email to dataverse...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dataverse-community/5d197adb-3a7b-4c79-8aa5-467eea9f023d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Philip Durbin

unread,
Apr 27, 2018, 6:58:29 AM4/27/18
to dataverse...@googlegroups.com
Don, thank you for giving my standard answer while I was still sleeping, which is a link to http://phoenix.dataverse.org/schemaspy/latest/relationships.html :)

Frank, you and your database people should know that the SchemaSpy output (entity-relationship diagrams and navigation) represents the Dataverse database schema in the latest code in our main integration branch, which is called "develop". So, depending on how far back you are on Dataverse versions, your database might look slightly different. If you're interested in similar diagrams for your database, the command I use is checked into the source tree at https://github.com/IQSS/dataverse/blob/v4.8.6/scripts/deploy/phoenix.dataverse.org/post#L15

(I use that "phoenix" server to run our API test suite, dropping the database on each deployment and run SchemaSpy at the end. For more details, please see http://guides.dataverse.org/en/4.8.6/developers/testing.html#the-phoenix-server . Maybe I should document the SchemaSpy bit.)

I should back up and say that I'm slightly alarmed by the suggestion of tweaks here and there. On rare occasions, we will advise minor database edits ( http://guides.dataverse.org/en/4.8.6/admin/user-administration.html#deleting-an-api-token comes to mind) but in general database updates should be coming from actions in the GUI or calls to the API. For example, if a UNF is missing, we have an API to add it into the database: http://guides.dataverse.org/en/4.8.6/api/native-api.html#dataset-integrity

More commonly, database people are recruited to help you pull insights out of your database, to create reports. There's an ongoing effort at https://github.com/IQSS/dataverse/issues/4169 where we have been writing SQL queries for reporting purposes and putting those SQL queries in a Google Doc linked from that issue. If there's a query you'd like to contribute or get help with, you should suggest an edit to that Google doc or leave a comment. There's another effort underway where we are taking some of those SQL queries and coming up with a "metrics" API over at https://github.com/IQSS/dataverse/issues/4527 . Comments and feedback are welcome, as always.

I hope this helps!

Phil

On Fri, Apr 27, 2018 at 5:52 AM, Donald Sizemore II <don.si...@gmail.com> wrote:
hello,

does this help?

On Apr 27, 2018, at 05:22, aussda....@gmail.com wrote:

Hi Everybody,

We're looking for more detailed information about the basic structure of Dataverse's postgres database, something along the lines of a database diagram. We've seen some of the flowchart pngs on github. But is there any additional dvndb documentation out there that could help us better understand the flow of information through the database? We don't have a java development team, so working through the application logic would be challenging for us, but we do have some database people that with a little guidance can help us with tweaks here and there.

Thanks very much for the help. Best, Frank

--
You received this message because you are subscribed to the Google Groups "Dataverse Users Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dataverse-community+unsub...@googlegroups.com.
To post to this group, send email to dataverse-community@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Dataverse Users Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dataverse-community+unsub...@googlegroups.com.
To post to this group, send email to dataverse-community@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dataverse-community/1B790E1E-2EFF-492F-B77A-870EEB821B14%40gmail.com.

For more options, visit https://groups.google.com/d/optout.



--

aussda....@gmail.com

unread,
Apr 27, 2018, 7:56:16 AM4/27/18
to Dataverse Users Community
Hi Donald, Yes, thanks very much. This is exactly what I was hoping for!

On Friday, April 27, 2018 at 11:52:10 AM UTC+2, Donald Sizemore II wrote:
hello,

does this help?
http://phoenix.dataverse.org/schemaspy/latest/relationships.html

painstakingly pecked on my iphone.

On Apr 27, 2018, at 05:22, aussda....@gmail.com wrote:

Hi Everybody,

We're looking for more detailed information about the basic structure of Dataverse's postgres database, something along the lines of a database diagram. We've seen some of the flowchart pngs on github. But is there any additional dvndb documentation out there that could help us better understand the flow of information through the database? We don't have a java development team, so working through the application logic would be challenging for us, but we do have some database people that with a little guidance can help us with tweaks here and there.

Thanks very much for the help. Best, Frank

--
You received this message because you are subscribed to the Google Groups "Dataverse Users Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dataverse-community+unsub...@googlegroups.com.

aussda....@gmail.com

unread,
Apr 27, 2018, 8:17:38 AM4/27/18
to Dataverse Users Community
Hi Phil,

Definitely helps. Thanks very much for the reporting queries links (which we are very interested in) and especially the alarm. Right now we've got a dataverse development server that is made for education and testing, and is breakable by design - so no tweaks will see the light of day anytime soon... Primarily we're interested in small usability adaptations for our local user groups, for example, reordering the language and location vocabularies. 

Not sure how long it will take before we can contribute to the queries document, but if there is anything else we can help out with just let me know. Maybe contributing to the documentation process? We're gaining so much benefit from Dataverse right now that it would be great if we could return the favor somehow.

Best, Frank
 

On Friday, April 27, 2018 at 12:58:29 PM UTC+2, Philip Durbin wrote:
Don, thank you for giving my standard answer while I was still sleeping, which is a link to http://phoenix.dataverse.org/schemaspy/latest/relationships.html :)

Frank, you and your database people should know that the SchemaSpy output (entity-relationship diagrams and navigation) represents the Dataverse database schema in the latest code in our main integration branch, which is called "develop". So, depending on how far back you are on Dataverse versions, your database might look slightly different. If you're interested in similar diagrams for your database, the command I use is checked into the source tree at https://github.com/IQSS/dataverse/blob/v4.8.6/scripts/deploy/phoenix.dataverse.org/post#L15

(I use that "phoenix" server to run our API test suite, dropping the database on each deployment and run SchemaSpy at the end. For more details, please see http://guides.dataverse.org/en/4.8.6/developers/testing.html#the-phoenix-server . Maybe I should document the SchemaSpy bit.)

I should back up and say that I'm slightly alarmed by the suggestion of tweaks here and there. On rare occasions, we will advise minor database edits ( http://guides.dataverse.org/en/4.8.6/admin/user-administration.html#deleting-an-api-token comes to mind) but in general database updates should be coming from actions in the GUI or calls to the API. For example, if a UNF is missing, we have an API to add it into the database: http://guides.dataverse.org/en/4.8.6/api/native-api.html#dataset-integrity

More commonly, database people are recruited to help you pull insights out of your database, to create reports. There's an ongoing effort at https://github.com/IQSS/dataverse/issues/4169 where we have been writing SQL queries for reporting purposes and putting those SQL queries in a Google Doc linked from that issue. If there's a query you'd like to contribute or get help with, you should suggest an edit to that Google doc or leave a comment. There's another effort underway where we are taking some of those SQL queries and coming up with a "metrics" API over at https://github.com/IQSS/dataverse/issues/4527 . Comments and feedback are welcome, as always.

I hope this helps!

Phil
On Fri, Apr 27, 2018 at 5:52 AM, Donald Sizemore II <don.si...@gmail.com> wrote:
hello,

does this help?

On Apr 27, 2018, at 05:22, aussda....@gmail.com wrote:

Hi Everybody,

We're looking for more detailed information about the basic structure of Dataverse's postgres database, something along the lines of a database diagram. We've seen some of the flowchart pngs on github. But is there any additional dvndb documentation out there that could help us better understand the flow of information through the database? We don't have a java development team, so working through the application logic would be challenging for us, but we do have some database people that with a little guidance can help us with tweaks here and there.

Thanks very much for the help. Best, Frank

--
You received this message because you are subscribed to the Google Groups "Dataverse Users Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dataverse-community+unsub...@googlegroups.com.
To post to this group, send email to dataverse...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Dataverse Users Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dataverse-community+unsub...@googlegroups.com.
To post to this group, send email to dataverse...@googlegroups.com.

Philip Durbin

unread,
Apr 27, 2018, 8:42:25 AM4/27/18
to dataverse...@googlegroups.com
You should definitely consider opening a GitHub issue about any usability problems you're having with reordering the language and location vocabularies. I'm not even quite sure what you means so if you do create a GitHub issue, please include screenshots.

At last years community meeting we got similar feedback that the community wants to know how to help. I love it. It prompted me to post this "help wanted" message: https://groups.google.com/d/msg/dataverse-community/nMdEGUCOyzE/RtOG3a_HAQAJ

A little while later, I created a spreadsheet to help track what the community is working on: https://groups.google.com/d/msg/dataverse-community/X2diSWYll0w/ikp1TGcfBgAJ

If you're interested in helping with documentation specifically, great! We could use the help. I'd suggest browsing through GitHub issues that are labeled "Help Wanted: Documentation" like this: https://github.com/IQSS/dataverse/labels/Help%20Wanted%3A%20Documentation

If you can't find an interesting issue to work on, please just create one! For example, you could open an issue saying that the SchemaSpy link should be mentioned somewhere in the Dev Guide or Admin Guide (or both).

Thanks,

Phil

On Fri, Apr 27, 2018 at 8:17 AM, <aussda....@gmail.com> wrote:
Hi Phil,

Definitely helps. Thanks very much for the reporting queries links (which we are very interested in) and especially the alarm. Right now we've got a dataverse development server that is made for education and testing, and is breakable by design - so no tweaks will see the light of day anytime soon... Primarily we're interested in small usability adaptations for our local user groups, for example, reordering the language and location vocabularies. 

Not sure how long it will take before we can contribute to the queries document, but if there is anything else we can help out with just let me know. Maybe contributing to the documentation process? We're gaining so much benefit from Dataverse right now that it would be great if we could return the favor somehow.

Best, Frank
 

On Friday, April 27, 2018 at 12:58:29 PM UTC+2, Philip Durbin wrote:
Don, thank you for giving my standard answer while I was still sleeping, which is a link to http://phoenix.dataverse.org/schemaspy/latest/relationships.html :)

Frank, you and your database people should know that the SchemaSpy output (entity-relationship diagrams and navigation) represents the Dataverse database schema in the latest code in our main integration branch, which is called "develop". So, depending on how far back you are on Dataverse versions, your database might look slightly different. If you're interested in similar diagrams for your database, the command I use is checked into the source tree at https://github.com/IQSS/dataverse/blob/v4.8.6/scripts/deploy/phoenix.dataverse.org/post#L15

(I use that "phoenix" server to run our API test suite, dropping the database on each deployment and run SchemaSpy at the end. For more details, please see http://guides.dataverse.org/en/4.8.6/developers/testing.html#the-phoenix-server . Maybe I should document the SchemaSpy bit.)

I should back up and say that I'm slightly alarmed by the suggestion of tweaks here and there. On rare occasions, we will advise minor database edits ( http://guides.dataverse.org/en/4.8.6/admin/user-administration.html#deleting-an-api-token comes to mind) but in general database updates should be coming from actions in the GUI or calls to the API. For example, if a UNF is missing, we have an API to add it into the database: http://guides.dataverse.org/en/4.8.6/api/native-api.html#dataset-integrity

More commonly, database people are recruited to help you pull insights out of your database, to create reports. There's an ongoing effort at https://github.com/IQSS/dataverse/issues/4169 where we have been writing SQL queries for reporting purposes and putting those SQL queries in a Google Doc linked from that issue. If there's a query you'd like to contribute or get help with, you should suggest an edit to that Google doc or leave a comment. There's another effort underway where we are taking some of those SQL queries and coming up with a "metrics" API over at https://github.com/IQSS/dataverse/issues/4527 . Comments and feedback are welcome, as always.

I hope this helps!

Phil
On Fri, Apr 27, 2018 at 5:52 AM, Donald Sizemore II <don.si...@gmail.com> wrote:
hello,

does this help?

On Apr 27, 2018, at 05:22, aussda....@gmail.com wrote:

Hi Everybody,

We're looking for more detailed information about the basic structure of Dataverse's postgres database, something along the lines of a database diagram. We've seen some of the flowchart pngs on github. But is there any additional dvndb documentation out there that could help us better understand the flow of information through the database? We don't have a java development team, so working through the application logic would be challenging for us, but we do have some database people that with a little guidance can help us with tweaks here and there.

Thanks very much for the help. Best, Frank

--
You received this message because you are subscribed to the Google Groups "Dataverse Users Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dataverse-community+unsubscribe...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Dataverse Users Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dataverse-community+unsubscribe...@googlegroups.com.
To post to this group, send email to dataverse...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Dataverse Users Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dataverse-community+unsub...@googlegroups.com.
To post to this group, send email to dataverse-community@googlegroups.com.
 To view this discussion on the web visit https://groups.google.com/d/msgid/dataverse-community/4d7104a8-0410-4a20-991e-925aca1e4ff4%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

aussda....@gmail.com

unread,
Apr 27, 2018, 9:47:40 AM4/27/18
to Dataverse Users Community
Hi Phil,
Thanks. Our usability questions aren't problems so much as local preferences. So in the case of language this would be updating displayorder in controlledvocabularyvalue so that German is at the top of its list. For locations, Austria would be top. These are small things for sure, but some users have mentioned it and we figured it's worth exploring, if only to understand Dataverse internals a little better.

Thanks for the help wanted links. I'll check out documentation and see if we can contribute in some way. I noticed the list seems based on issue reporting, but we don't have any documentation issues so far!

Best, Frank
Hi Phil,
hello,

does this help?
To unsubscribe from this group and stop receiving emails from it, send an email to dataverse-community+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Dataverse Users Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dataverse-community+unsub...@googlegroups.com.
To post to this group, send email to dataverse...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Dataverse Users Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dataverse-community+unsub...@googlegroups.com.
To post to this group, send email to dataverse...@googlegroups.com.

Pete Meyer

unread,
Apr 27, 2018, 10:06:56 AM4/27/18
to Dataverse Users Community
Hi Frank,


On Friday, April 27, 2018 at 9:47:40 AM UTC-4, aussda....@gmail.com wrote:
Hi Phil,
Thanks. Our usability questions aren't problems so much as local preferences. So in the case of language this would be updating displayorder in controlledvocabularyvalue so that German is at the top of its list. For locations, Austria would be top. These are small things for sure, but some users have mentioned it and we figured it's worth exploring, if only to understand Dataverse internals a little better.

I've had reasonable success customizing things like displayorder (and displayoncreate) by editing the TSV files for the metadata blocks, and reloading the TSV using the admin API.

In my experience (and as I was advised ahead of time), removing things from TSV files is much more problematic, and probably best avoided.

Best,
Pete
 

aussda....@gmail.com

unread,
Apr 30, 2018, 9:09:21 AM4/30/18
to Dataverse Users Community
Hi Pete,
Thanks very much for the idea. Do you know if there is a controlledvocabularyvalue counterpart to the datasetfieldtype property bundle file you mention? It would be great if we could edit the displayorder values an upload through the api.
Best Frank

Pete Meyer

unread,
Apr 30, 2018, 9:50:22 AM4/30/18
to Dataverse Users Community
Hi Frank,

There's a `displayOrder` option for controlled vocabulary types defined in the TSV files, which (in my hands, at least) behaves similarly to the `displayOrder` option for DatasetFields.  `/api/admin/datasetfield/load` is the API endpoint (this should be available from localhost; I believe it's blocked from external hosts in the normal installation process).

I don't think that the bundle file (`Bundle.properties`) is involved here - sorry to add to the confusion.

Best,
Pete
Reply all
Reply to author
Forward
0 new messages