API for fetching metadata for database and billing accounts

[akbertram]

The R package should expose a function for fetching metadata on the set of forms that belong to a database, or to a billing account. The results should include a list of forms, each with the metadata currently stored internally:

• The form's label
• The form's database
• The number of records in the form
• The time of the last update to the form
• The form's version number
• The form's schema version number
• The visibility of the form (private, reference or public)

Additional permissions metadata that would be useful:

• The number of users with read access
• The number of users with write access

[nickdickinson]

Dependencies

library(dplyr)
library(tidyr)
library(activityinfo)

Users with read access in a tibble dataframe

If you know the role id such as "readonly"

targetRole <- "readonly"
userRoles <- getDatabaseUsers(databaseId) %>% 
     as_tibble() %>% 
     unnest_wider(role, names_sep = "_") %>% 
     filter(role_id == targetRole)

If you are interested in a specific operation/expression like "VIEW"

tree <- getDatabaseTree(databaseId)

allRoleOperations <- tibble(roles = tree$roles) %>% 
     unnest_wider(roles) %>% 
     unnest_longer(grants) %>% 
     unnest_wider(grants) %>% 
     unnest_longer(operations) %>% 
     unnest_wider(operations)

viewOperations <- allRoleOperations %>% 
     filter(operation=="VIEW")

userRoles <- getDatabaseUsers(databaseId) %>% 
     as_tibble() %>% 
     unnest_wider(role, names_sep = "_")

userResourceWithView <- userRoles %>% 
     right_join(viewOperations, by=join_by(role_id==id)) %>% 
     filter(!is.na(databaseId))

usersWithView <- userResourceWithView %>% 
     select(userId, name, email) %>% 
     distinct()
n <- nrow(usersWithView)

All database roles and their definitions in a tibble dataframe

tibble(roles = tree$roles) %>% 
     unnest_wider(roles) %>% 
     unnest_longer(grants) %>% 
     unnest_wider(grants) %>% 
     unnest_longer(operations) %>% 
     unnest_wider(operations)

All users and their role ids for all databases in a tibble dataframe

lapply(getDatabases(asDataFrame = FALSE), function(x) {
     try(return(getDatabaseUsers(x$databaseId)))
     return(NULL)}
) %>% 
     bind_rows() %>% 
     as_tibble() %>% 
     unnest_wider(role, names_sep = "_")

Get all database roles and their definitions in a tibble dataframe

tibble(roles = tree$roles) %>% 
     unnest_wider(roles) %>% 
     unnest_longer(grants) %>% 
     unnest_wider(grants) %>% 
     unnest_longer(operations) %>% 
     unnest_wider(operations)

[nickdickinson]

These are relevant to #133