// Set the Group ID of the Row to be inserted.
// Has to be a Group of the user.
public Insert SetGroupID(long groupID)
{
try
{
// Here we will Add the Group ID given by the User to the Statement.
// Check if the Table is syncable, then only Add the row level security.
if (CloudDB.IsSyncable(tableName))
{
// Lets validate if the User is allowed to write with this role.
if (cdb.ValidateGroupRLS(groupID, CloudDB.CRUD.WRITE) || cdb.IsSystemUser)
{
// The user is allowed to write with this role.
rlsType = C.RLS.GROUP_RLS;
rlsID = groupID;
AddStatus("User is Authorized to Write in this Group");
return(this);
}
else
{
// The user is not allowed to write with this role.
rlsType = C.RLS.NO_RLS;
rlsID = 0;
isError = true;
AddStatus("User is NOT Authorized to Write in this Group");
return(this);
}
}
else
{
// The table is not syncable, so we will not Add any RLS.
rlsType = C.RLS.NO_RLS;
rlsID = 0;
AddStatus("Table is not a Syncable Table.");
return(this);
}
}
catch (Exception e)
{
// There was an Error.
rlsType = C.RLS.NO_RLS;
rlsID = 0;
AddStatus("Error : " + e.Message);
isError = true;
return(this);
}
}
// Allow the User to Get the SQL Statement Generated.
public string GetSQL()
{
try
{
// Validate if the User can proceed with the delete.
// Attach the Main Statement.
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("DELETE FROM ")
.Append(tableName);
// Add the Where Clauses.
if (whereClauseBuilder.Length > 0)
{
sqlBuilder.Append(" WHERE ")
.Append(whereClauseBuilder);
if (CloudDB.IsSyncable(tableName))
{
string rlsWhere = cdb.RLSWhere(CloudDB.QueryType.DELETE);
if (rlsWhere.Trim().Length > 0)
{
sqlBuilder.Append(" AND ")
.Append(rlsWhere);
}
}
}
else if (CloudDB.IsSyncable(tableName))
{
string rlsWhere = cdb.RLSWhere(CloudDB.QueryType.DELETE);
if (rlsWhere.Trim().Length > 0)
{
sqlBuilder.Append(" WHERE ")
.Append(rlsWhere);
}
}
return(sqlBuilder.ToString());
}
catch (Exception e)
{
// There was an Error.
return("Error : " + e.Message);
}
}
// Set the Role ID of the Row to be inserted.
// Has to be a role of the user.
public Delete SetRoleID(long roleID)
{
try
{
// Here we will Add the Role ID given by the User to the Statement.
// Check if the Table is syncable, then only Add the row level security.
if (CloudDB.IsSyncable(tableName))
{
// Lets validate if the User is allowed to write with this role.
if (cdb.ValidateRoleRLS(roleID, CloudDB.CRUD.DELETE))
{
// The user is allowed to write with this role.
rlsType = C.RLS.ROW_RLS;
rlsID = roleID;
return(this);
}
else
{
// The user is not allowed to write with this role.
rlsType = C.RLS.NO_RLS;
rlsID = 0;
isError = true;
AddStatus("User not Authorized to Delete from this Role");
return(this);
}
}
else
{
// The table is not syncable, so we will not Add any RLS.
rlsType = C.RLS.NO_RLS;
rlsID = 0;
return(this);
}
}
catch (Exception e)
{
// There was an Error.
rlsType = C.RLS.NO_RLS;
rlsID = 0;
isError = true;
AddStatus("Error : " + e.Message);
return(this);
}
}
// Main Constructor will be used to set the table name.
public Insert Into(string tableName)
{
try
{
// Here we will set the table name.
// Authentication.
if (this.tableName == null)
{
if (cdb.ValidateCRUD(tableName, CloudDB.CRUD.WRITE) && cdb.user.IsActive)
{
this.tableName = Regex.Replace(tableName, " ", "");
// The user is allowed to write into the table.
// Initiate the required variables.
columnDataBuilder = new StringBuilder();
columnPlaceHolderBuilder = new StringBuilder();
bindObjs = new Dictionary <string, object>();
syncable = CloudDB.IsSyncable(tableName);
AddStatus("Valid CRUD");
return(this);
}
else
{
// The user is not authorized to write into this table.
AddStatus("Not Authorized to Write into Table");
this.tableName = null;
return(this);
}
}
else
{
// The Table name has already been set.
return(this);
}
}
catch (Exception e)
{
// There was an Error.
this.tableName = null;
AddStatus("Error with the Cloud DB Validation");
return(this);
}
}
// This Constructor we will use to set the table name.
public Delete From(string tableName)
{
try
{
// Set the Table Name.
// Authentication.
if (cdb.ValidateCRUD(tableName, CloudDB.CRUD.DELETE) && cdb.user.IsActive)
{
this.tableName = Regex.Replace(tableName, " ", "");
// Initiate the Required Variables.
whereClauseBuilder = new StringBuilder();
whereBindObjs = new Dictionary <string, object>();
AddStatus("User Authorized to Delete from the Table");
if (CloudDB.IsSyncable(tableName))
{
// The table is syncable.
// Lets not do anything here, as the user might not want to sync this table.
}
else
{
// This table is not syncable.
syncable = false;
}
return(this);
}
else
{
// The user is not authorized to delete from this table.
isError = true;
AddStatus("User NOT Authorized to Delete from the Table");
return(this);
}
}
catch (Exception e)
{
// There was an Error.
isError = true;
AddStatus("Error : " + e.Message);
return(this);
}
}
// This method will handle the actual execution and will me called.
private void InternalExecutor()
{
try
{
// Here we will Execute the Query.
if (IsSuccessful())
{
SQLiteCommand dbSess = cdb.GetSession();
// Lets set the deleted time. (This will be used to reject any deletes trying to be run twice)
deletedTimeI = Helper.CurrentTimeMillis();
try
{
// Here we will Generate the SQL Statement.
// Add the RLS System.
if (CloudDB.IsSyncable(tableName))
{
// The table required RLS.
if (rlsID == 0)
{
// The user should not be allowed to execute this statement.
AddStatus("RLS NOT Provided");
}
else
{
// The RLS is Provided
AddWhere(new Where("rls_id_", Where.Type.EQUAL, rlsID));
AddWhere(new Where("rls_type_", Where.Type.EQUAL, rlsType));
}
}
}
catch (Exception e)
{
// There was an Error.
isError = true;
AddStatus("SQL Security : " + e.Message);
}
// Lets create the Statement.
dbSess.CommandText = GetSQL();
// Bind the Params of the Where clause.
foreach (string paramName in whereBindObjs.Keys)
{
// Bind the Params.
SQLiteParameter sqlParam = new SQLiteParameter(paramName, whereBindObjs[paramName]);
// Add the Param to the Command.
dbSess.Parameters.Add(sqlParam);
}
// Lets prepare the statement.
dbSess.Prepare();
// Lets Get the Data that will be deleted.
List <Row> deletedRows = new List <Row>();
try
{
// Lets get the updated rows.
// Lets Create the Statement to read from the updated table.
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("SELECT * FROM ")
.Append(tableName)
.Append(" WHERE ")
.Append(whereClauseBuilder);
// Lets Get the Session Object.
SQLiteCommand readSess = cdb.GetSession();
// Create the Statement.
readSess.CommandText = sqlBuilder.ToString();
// Bind the Columns Params.
foreach (string paramName in whereBindObjs.Keys)
{
// Bind the Params.
SQLiteParameter sqlParam = new SQLiteParameter(paramName, whereBindObjs[paramName]);
// Add the Param to the Command.
readSess.Parameters.Add(sqlParam);
}
// Lets Prepare the Statement.
readSess.Prepare();
// Lets execute the query.
SQLiteDataReader sdr = readSess.ExecuteReader();
// Lets read the data and add the data to the Rows List.
deletedRows = CloudDB.SQLiteReaderToRows(sdr);
// Reset the Session.
readSess.Reset();
}
catch (Exception e)
{
// There was an Error.
}
// Lets Execute the Query.
// The user is not asking to create a reader, just to insert the value.
int deletedRowsNum = dbSess.ExecuteNonQuery();
// Send to the Receiver if the user has set a insert result listener.
onDataDeleted?.Invoke(deletedRows);
try
{
// Checking about the deleted rows.
if (deletedRows.Count == deletedRowsNum)
{
AddStatus("All the Deletes worked perfectly.");
}
else if (deletedRows.Count > deletedRowsNum)
{
AddStatus("All the Rows Required didn't get deleted");
}
else
{
AddStatus("All the Rows Deleted, have not been sent to the user.");
}
} catch (Exception er)
{
// There was an Error
}
// Reset the Session if another similar query is to be run.
dbSess.Dispose();
// If the data is to be synced with the server, we will sync it with the server.
if (syncable && deletedRowsNum > 0)
{
// The data is to be synced with the Cloud Server.
CloudSync.Sync(this, deletedRows);
}
}
else
{
// The Delete System Failed somewhere.
AddStatus("Err : Delete Failure");
}
}
catch (Exception e)
{
// There was an Error.
AddStatus("Err : " + e.Message + " : " + e.StackTrace);
}
}
// Let the user to suggest which rls to add to in one method only.
public Delete SetRLSID(short rowLevelSecurityType, long rowLevelSecurityID)
{
try
{
// Here we will Set the RLS ID.
// Here we will Add the Group ID given by the User to the Statement.
// Check if the Table is syncable, then only Add the row level security.
if (CloudDB.IsSyncable(tableName))
{
// Lets check according to the RLS Type.
switch (rowLevelSecurityType)
{
// Its a Role Based RLS.
case 1:
// Lets validate if the User is allowed to write with this role.
if (cdb.ValidateRoleRLS(rowLevelSecurityID, CloudDB.CRUD.DELETE))
{
// The user is allowed to Delete with this role.
rlsType = C.RLS.ROW_RLS;
rlsID = rowLevelSecurityID;
AddStatus("User is Authorized to Delete in this Role");
return(this);
}
else
{
// The user is not allowed to Delete with this role.
rlsType = C.RLS.NO_RLS;
rlsID = 0;
AddStatus("User is NOT Authorized to Delete in this Role");
isError = true;
return(this);
}
// Its a Group Based RLS.
case 2:
// Lets validate if the User is allowed to Delete with this role.
if (cdb.ValidateGroupRLS(rowLevelSecurityID, CloudDB.CRUD.DELETE))
{
// The user is allowed to Delete with this role.
rlsType = C.RLS.GROUP_RLS;
rlsID = rowLevelSecurityID;
AddStatus("User is Authorized to Delete in this Group");
return(this);
}
else
{
// The user is not allowed to Delete with this role.
rlsType = C.RLS.NO_RLS;
rlsID = 0;
isError = true;
AddStatus("User is NOT Authorized to Delete in this Group");
return(this);
}
default:
// No RLS Type was given.
rlsType = C.RLS.NO_RLS;
rlsID = 0;
AddStatus("Invalid RLS Type Given");
isError = true;
return(this);
}
}
else
{
// The table is not syncable, so we will not Add any RLS.
rlsType = C.RLS.NO_RLS;
rlsID = 0;
AddStatus("Table is not a Syncable Table.");
return(this);
}
}
catch (Exception er)
{
// There was an Error.
rlsType = C.RLS.NO_RLS;
rlsID = 0;
AddStatus("Error : " + er.Message);
isError = true;
return(this);
}
}
// Here we will run an internal executor to run the actual execution of the statement.
private int InternalExecutor()
{
try
{
// Here we will Execute the Statement, and bind the Params.
// Lets create the Statement.
if (IsSuccessful())
{
// Reset the Session So if Any uncompleted Call does'nt interupt this call.
SQLiteCommand dbSess = cdb.GetSession();
dbSess.Reset();
// Check if the table is a syncable table.
if (CloudDB.IsSyncable(tableName))
{
// The table is a syncable table.
// Lets Add the RLS system.
if (rlsID == 0 || rlsType == 0)
{
// The table is a syncable table, but the RLS Values are not provided.
// So we will not let it complete the process.
isError = true;
AddStatus("User NOT Authenticated to Write in this RLS");
}
else
{
// The user has provided the RLS Data.
PutColumnInternal(new ColumnData("rls_id_", rlsID));
PutColumnInternal(new ColumnData("rls_type_", rlsType));
}
// Now lets generate the Sync ID.
if (GetSyncID() == null || GetSyncID().Trim().Length != 36)
{
// The sync id has not been set.
// Lets set it.
SetSyncID(Guid.NewGuid().ToString());
}
PutColumnInternal(new ColumnData("sync_id_", GetSyncID()));
// Lets set the Update Time.
try
{
if (updateTimeI == 0)
{
// Lets Create an Update Time.
updateTimeI = Helper.CurrentTimeMillis();
}
PutColumnInternal(new ColumnData("update_time_", updateTimeI));
}
catch (Exception er)
{
// There was an Error.
}
}
else
{
// Lets set the Other Data Points if they are given explicitly.
if (updateTimeI != 0)
{
PutColumnInternal(new ColumnData("update_time_", updateTimeI));
}
}
// Set the Tenant ID.
if (CloudDB.IsMultiTenant(tableName))
{
// The table is multi-tenant.
PutColumnInternal(new ColumnData("tenant_id_", tenantID));
}
// The Insert Statement Generating was succesful, we can run the Statement.
string sql = GetSQL();
breadCrumb.BC.Debug("INSERT SQL : " + sql);
dbSess.CommandText = sql;
// Bind the Params.
foreach (string paramName in bindObjs.Keys)
{
// Bind the Params.
SQLiteParameter sqlParam = new SQLiteParameter(paramName, bindObjs[paramName]);
// Add the Param to the Command.
dbSess.Parameters.Add(sqlParam);
}
// Lets prepare the statement.
dbSess.Prepare();
int inserted = -1;
// Lets Execute the Query.
// The user is not asking to create a reader, just to insert the value.
inserted = dbSess.ExecuteNonQuery();
// Send to the Receiver if the user has set a insert result listener.
onDataInserted?.Invoke(inserted);
// If the data is to be synced with the server, we will sync it with the server.
if (syncable)
{
// The data is to be synced with the Cloud Server.
CloudSync.Sync(this);
}
return(inserted);
}
else
{
// The Insert Statement Generation was not successful.
return(-1);
}
}
catch (Exception e)
{
// THere was an Error.
Console.WriteLine("Err : " + e.Message + " : " + e.StackTrace);
AddStatus("Err : " + e.Message + " : " + e.StackTrace);
return(-1);
}
}
// Here we generate the SQL Statement for the User.
private string GetSQL()
{
try
{
// Here we generate the SQL Statement.
// Attach the Main Select statement.
StringBuilder sqlBuilder = new StringBuilder();
if (columnNameBuilder.Length == 0)
{
columnNameBuilder.Append(" * ");
}
sqlBuilder.Append("SELECT ")
.Append(columnNameBuilder)
.Append(" FROM ")
.Append(tableName);
// Add the Join.
if (joinsBuilder.Length > 0)
{
sqlBuilder.Append(joinsBuilder);
}
// Add the Where Clauses.
if (whereClauseBuilder.Length > 0)
{
sqlBuilder.Append(" WHERE ")
.Append(whereClauseBuilder);
if (CloudDB.IsSyncable(tableName))
{
if (joinTables.Count > 0)
{
// There are tables that are joint.
string rlsWhere = cdb.RLSWhere(CloudDB.QueryType.SELECT, tableName);
if (rlsWhere.Trim().Length > 0)
{
sqlBuilder.Append(" AND ")
.Append(rlsWhere);
}
// Now Lets handle the Tables being Joint.
for (int joinTbIndex = 0; joinTbIndex < joinTables.Count; joinTbIndex++)
{
rlsWhere = cdb.RLSWhere(CloudDB.QueryType.SELECT, joinTables[joinTbIndex]);
if (rlsWhere.Trim().Length > 0)
{
sqlBuilder.Append(" AND ")
.Append(rlsWhere);
}
}
}
else
{
// No Tables have been Joined.
string rlsWhere = cdb.RLSWhere(CloudDB.QueryType.SELECT);
if (rlsWhere.Trim().Length > 0)
{
sqlBuilder.Append(" AND ")
.Append(rlsWhere);
}
}
}
}
else if (CloudDB.IsSyncable(tableName))
{
if (joinTables.Count > 0)
{
// There are Tables that are Joint.
string rlsWhere = cdb.RLSWhere(CloudDB.QueryType.SELECT, tableName);
if (rlsWhere.Trim().Length > 0)
{
sqlBuilder.Append(" WHERE ")
.Append(rlsWhere);
}
// Now Lets Handle the Tables being Joint.
for (int joinTbIndex = 0; joinTbIndex < joinTables.Count; joinTbIndex++)
{
rlsWhere = cdb.RLSWhere(CloudDB.QueryType.SELECT, joinTables[joinTbIndex]);
if (rlsWhere.Trim().Length > 0)
{
sqlBuilder.Append(" AND ")
.Append(rlsWhere);
}
}
}
else
{
// There are Tables Being Joint.
string rlsWhere = cdb.RLSWhere(CloudDB.QueryType.SELECT);
if (rlsWhere.Trim().Length > 0)
{
sqlBuilder.Append(" WHERE ")
.Append(rlsWhere);
}
}
}
// Add the Order by Clause.
if (orderByClauseBuilder.Length > 0)
{
sqlBuilder.Append(" ORDER BY ")
.Append(orderByClauseBuilder);
}
return(sqlBuilder.ToString());
}
catch (Exception e)
{
// There was an Error.
return("Err : " + e.Message);
}
}