/// <summary>
/// Add new table/view/sp/function to visible data sources of a connection
/// and then attach it to an existing role.
/// We will hardcode the ConnectionName, TableName, ViewName, SPName, FunctionName, RoleName
///
/// Step 1: Check if the hardcoded connection name is already existed by
/// - Call API to get all connections of the tenant and then search by name to check existed.
/// - If not existed, log error and return.
/// Step 2: Get connection detail by calling API to reload the remote schema.
/// This will support the case when user add a table, view, ... to database,
/// but in Izenda it still not refresh in the Available Data Sources yet.
/// Step 3: Validate the connection detail's data. If not valid, log error and return
/// Step 4: For hardcoded TableName, ViewName, SPName, FunctionName, check if it existed in the
/// connection Available Data Sources.
/// If not, log error and return.
/// If existed, set the Selected flag to true to move it to Visible Data Sources.
/// Step 5: Call API to update the connection (add table,view,sp,function to Visible Data Sources).
/// Step 6: Check if the hardcoded role name is existed.
/// If not, log error and return.
/// If existed, call API to get role detail.
/// Step 7: Check if the table,view,sp,function is already in the Visible Data Sources of the role.
/// If not, add it to the role's Visible Data Sources.
/// Step 8: Call API to save role (attach table,view,sp,function to role).
/// </summary>
private async Task <bool> AddTableViewSPFunction()
{
//HARDCODED tenantUniqueName, we will add table, view, sp, function to this tenant
//If tenantUniqueName is empty, data will be added to the default 'System' tenant with tenantId = null
var tenantUniqueName = "DELDG";
//HARDCODED values
var connectionName = "retail";
var tableName = "Employee";
var viewName = "OrdersByRegion";
var spName = "";
var functionName = "";
var roleName = "role8";
//private variables
bool needToUpdate = false;
logger.Info("----------- Start adding Table/View/SP/Function -----------");
//Get the encrypted token from the configuration: izusername, iztenantname
//Use this token when calling Izenda Rest API
var token = GetToken();
//Get tenantId by calling API to get list of tenants then search by tenantUniqueName
//If tenantUniqueName is not existed then throw exception.
//Use this tenantId when calling Izenda Rest API
Guid?tenantId = null;
//GET /tenant/allTenants
var tenants = await IzendaUtility.GetTenants(token);
if (!string.IsNullOrEmpty(tenantUniqueName))
{
var tenant = tenants.Where(t => t.Name == tenantUniqueName).FirstOrDefault();
if (tenant == null)
{
logger.ErrorFormat("Tenant = {0} is not existed.", tenantUniqueName);
throw new ArgumentException("Invalid tenantUniqueName.");
}
else
{
tenantId = tenant.Id;
}
}
QuerySourceModel table = null;
QuerySourceModel view = null;
QuerySourceModel sp = null;
QuerySourceModel function = null;
//Get connection by name by calling API to get all connections then search by name.
//GET /connection/(tenant_id)
var connections = await IzendaUtility.GetConnections(tenantId, token);
var conn = connections.SingleOrDefault(x => x.Name.Equals(connectionName, StringComparison.InvariantCultureIgnoreCase));
if (conn == null)
{
logger.ErrorFormat("The connection = {0} is not existed.", connectionName);
return(false);
}
//Get connection detail by reloading the remote schema
//This will support the case when user add a table to database
//But in Izenda it still not refresh in Available Data Sources yet
var connectionModel = await IzendaUtility.ReloadRemoteSchema(
new
{
ConnectionId = conn.Id,
ConnectionString = conn.ConnectionString,
ServerTypeId = conn.ServerTypeId
}, token);
var querySourceCategories = connectionModel.DBSource.QuerySources;
var querySourceCategory = connectionModel.DBSource.QuerySources[0];
//keep available data sources
var querySources = querySourceCategory.QuerySources;
#region update Connection Detail Model
//To add table,view,sp,function to Visible Data Sources, we only need to set the Selected flag to true
//table
if (!string.IsNullOrEmpty(tableName))
{
table = querySources.Where(q => q.Name == tableName).FirstOrDefault();
table.Selected = true;
needToUpdate = true;
}
//view
if (!string.IsNullOrEmpty(viewName))
{
view = querySources.Where(q => q.Name == viewName).FirstOrDefault();
view.Selected = true;
needToUpdate = true;
}
//sp
if (!string.IsNullOrEmpty(spName))
{
sp = querySources.Where(q => q.Name == spName).FirstOrDefault();
sp.Selected = true;
needToUpdate = true;
}
//function
if (!string.IsNullOrEmpty(functionName))
{
function = querySources.Where(q => q.Name == functionName).FirstOrDefault();
function.Selected = true;
needToUpdate = true;
}
#endregion
if (needToUpdate == true)
{
conn.DBSource = connectionModel.DBSource;
//update connnection visible data sources
//POST /connection
await IzendaUtility.UpdateConnectionDetail(conn, token);
logger.Info("Update connnection visible data sources succesfully.");
//If we input RoleName, we will attach the added table,view,sp,function to this existing role
if (!string.IsNullOrEmpty(roleName))
{
//Get roles of tenant
//GET role/all/(tenant_id)
var roles = await IzendaUtility.GetRoles(tenantId, token);
//Check if role is existed
var existedRole = roles.Where(r => r.Name == roleName).FirstOrDefault();
if (existedRole != null)
{
logger.InfoFormat("Role = {0} is already existed => continue to update Data Model Permissions", roleName);
//Get role detail
//GET /role/{role_id}
var role = await IzendaUtility.GetRole(existedRole.Id, token);
#region update Data Model Permissions of role
//if role doesn't have any VisibleQuerySources, instantiate it
if (role.VisibleQuerySources == null)
{
role.VisibleQuerySources = new List <QuerySourceModel>();
}
//Check if the added table, view, sp, function already in VisibleQuerySources. If not existed, attach it to Role
if (table != null)
{
if (role.VisibleQuerySources.Where(q => q.Id == table.Id).FirstOrDefault() == null)
{
role.VisibleQuerySources.Add(BuildQuerySourceForRole(table, querySources));
}
else
{
logger.InfoFormat("Table = {0} is already in the VisibleQuerySources of Role = {1}", table.Name, roleName);
}
}
if (view != null)
{
if (role.VisibleQuerySources.Where(q => q.Id == view.Id).FirstOrDefault() == null)
{
role.VisibleQuerySources.Add(BuildQuerySourceForRole(view, querySources));
}
else
{
logger.InfoFormat("View = {0} is already in the VisibleQuerySources of Role = {1}", view.Name, roleName);
}
}
if (sp != null)
{
if (role.VisibleQuerySources.Where(q => q.Id == sp.Id).FirstOrDefault() == null)
{
role.VisibleQuerySources.Add(BuildQuerySourceForRole(sp, querySources));
}
else
{
logger.InfoFormat("SP = {0} is already in the VisibleQuerySources of Role = {1}", sp.Name, roleName);
}
}
if (function != null)
{
if (role.VisibleQuerySources.Where(q => q.Id == function.Id).FirstOrDefault() == null)
{
role.VisibleQuerySources.Add(BuildQuerySourceForRole(function, querySources));
}
else
{
logger.InfoFormat("Function = {0} is already in the VisibleQuerySources of Role = {1}", function.Name, roleName);
}
}
#endregion
//need to set TenantUniqueName to use POST /role/intergration/saveRole
role.TenantUniqueName = tenantUniqueName;
//Call API to update role
//POST /role/intergration/saveRole
await IzendaUtility.SaveRole(role, token);
}
else
{
logger.ErrorFormat("Role = {0} is not existed. Please specify another existed role to update Data Model Permissions", roleName);
return(false);
}
}
}
logger.Info("----------- End adding Table/View/SP/Function -----------");
return(true);
}
/// <summary>
/// Add a new role if the role does not exist.
/// We will hardcode the role name, some permissions and assign an existing user to this role.
///
/// Step 1: Check if the hardcoded role name is already exist by
/// - Call API to get all roles of the tenant and then search by name to check exist.
/// Step 2: If the hardcoded role name does not exist then
/// - Setup a new RoleDetail model
/// - Hardcoded some permissions
/// - Hardcoded an existing user name, check if this user is already existed by calling API
/// to get all users of the tenant and then search by name.
/// If user is not existed, log an error and return.
/// If user is existed, setup a new UserDetail model and add it to RoleDetail.
/// Step 3: Call API to save role.
/// </summary>
private async Task <bool> AddRole()
{
//HARDCODED tenantUniqueName, we will add role to this tenant
//If tenantUniqueName is empty, data will be added to the default 'System' tenant with tenantId = null
var tenantUniqueName = "DELDG";
//HARDCODED role name
var roleName = "role8";
//HARDCODED an existing user
var userName = "******";
//Get the encrypted token from the configuration: izusername, iztenantname
//Use this token when calling Izenda Rest API
var token = GetToken();
logger.Info("----------- Start adding role -----------");
//Get tenantId by calling API to get list of tenants then search by tenantUniqueName
//If tenantUniqueName is not existed then throw exception.
//Use this tenantId when calling Izenda Rest API
Guid?tenantId = null;
//GET /tenant/allTenants
var tenants = await IzendaUtility.GetTenants(token);
if (!string.IsNullOrEmpty(tenantUniqueName))
{
var tenant = tenants.Where(t => t.Name == tenantUniqueName).FirstOrDefault();
if (tenant == null)
{
logger.ErrorFormat("Tenant = {0} is not existed.", tenantUniqueName);
throw new ArgumentException("Invalid tenantUniqueName.");
}
else
{
tenantId = tenant.Id;
}
}
//Setup RoleDetail model to pass to API
//only need to pass TenantUniqueName to POST /role/intergration/saveRole, no need tenantId
var role = new RoleDetail();
role.TenantUniqueName = tenantUniqueName;
role.Name = roleName;
role.Active = true;
role.NotAllowSharing = false;
role.Permission = new Permission();
//HARDCODED some permissions
role.Permission.DataSetup.DataModel.Value = true;
role.Permission.RoleSetup.Actions.Create = true;
role.Permission.RoleSetup.Actions.Edit = true;
role.Permission.RoleSetup.Actions.Del = true;
//Check if hardcoded user is existed by get all users of tenant and search by name
//GET user/all/(tenant_id)
var users = await IzendaUtility.GetUsers(tenantId, token);
if (!string.IsNullOrEmpty(userName))
{
var user = users.Where(u => u.UserName == userName).FirstOrDefault();
if (user == null)
{
logger.ErrorFormat("user = {0} is not existed", userName);
return(false);
}
else
{
//new UserDetail model
var userDetail = new UserDetail
{
Id = user.Id,
UserName = user.UserName
};
//add to RoleDetail model
role.Users.Add(userDetail);
}
}
//Call API to create role
//POST /role/intergration/saveRole
await IzendaUtility.SaveRole(role, token);
logger.InfoFormat("Role = {0} created.", roleName);
logger.Info("----------- End adding role -----------");
return(true);
}
