// The User Token Constructor. public Delete(string token) { try { // Authorize the User. // Initialize the Cloud DB Service. CloudDB cdb = new CloudDB(token); if (cdb.IsSystemUser || cdb.user.IsActive) { this.cdb = cdb; // Set the Cloud DB Service. wheresJObj = new JObject(); // The User is authorized AddStatus("User Authenticated"); } else { // The user is not authorized. isError = true; AddStatus("User not Authenticated"); } } catch (Exception e) { // There was an Error. isError = true; AddStatus("Error : " + e.Message); } }
// Constructors. // Default Constructor. // The Cloud DB Constructor. public Insert(CloudDB cdb) { try { // Lets set the required dsta. // Initialize the Cloud DB Service, just in case. statusTrace = new StringBuilder(); // Initialize the Cloud DB Service // Lets Connect or create a Cloud DB instance. if (cdb.IsSystemUser || cdb.user.IsActive) { // Default Constructor. this.cdb = cdb; // Set the Cloud DB Service. rlsType = 0; tenantID = 0; rlsID = 0; isError = false; // Lets set the Tenant ID. try { tenantID = cdb.user.GetTenantID(); } catch (Exception e) { } AddStatus("User is Authenticated, Insert is Ready."); } else { // The user is not authenticated. isError = false; AddStatus("User not Authenticated"); } } catch (Exception e) { // There was an Error. AddStatus("Constructor Err : " + e.StackTrace); } }
// Default Constructor. // The Cloud DB Constructor public Select(CloudDB cdb) { try { // Default Constructor. // Lets initialize the Cloud DB Service, just in case. // Initialize the Cloud DB Service. // Lets Connect or create a Cloud DB instance. if (cdb.IsSystemUser || cdb.user.IsActive) { this.cdb = cdb; // Set the Cloud DB Service. // We will have to allow the user to set the context as well. // Initiate the required variables. columnNameBuilder = new StringBuilder(); joinsBuilder = new StringBuilder(); whereClauseBuilder = new StringBuilder(); orderByClauseBuilder = new StringBuilder(); bindObjs = new Dictionary <string, object>(); whereObjs = new List <Where>(); joinTables = new List <string>(); //AddStatus("Initiated"); } else { // The user is not authenticated. isError = false; //AddStatus("User not Authenticated"); } } catch (Exception e) { // There was an Error. // AddStatus("Construction Err : " + e.Message); } }
// 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); } }
// This method will be used to execute the statement, but will only be called by another method in the class. private void InternalExecution() { try { // Set the Select Listener with Cloud Sync. CloudSync.SetSelectListener(this); // Here we will Execute the Query. // Reset the Session so it is clean. if (IsSuccessful()) { SQLiteCommand dbSess = cdb.GetSession(); dbSess.Reset(); // Create the SQL Statement. string sql = GetSQL(); 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(); // Lets Execute the Query. SQLiteDataReader sdr = dbSess.ExecuteReader(); // Check if the user is waiting for the result. onDataResult?.Invoke(CloudDB.SQLiteReaderToRows(sdr)); } else { // The Select was not Successful. onDataResult?.Invoke(new List <Row>()); } } catch (Exception e) { // There was an Error. AddStatus("Execution Err : " + e.StackTrace + " ::: " + e.Message); onDataResult?.Invoke(new List <Row>()); } }
// Constructor. public Create() { try { // Default Constructor. // Lets initialize the Cloud DB Service, just in case. // We will have to allow the user to set the context as well. // Initiate the required variables. AddStatus("Initiated"); // Lets Connect or create a Cloud DB instance. cdb = new CloudDB(C.SYSTEM_USER_DB_PASSCODE); } catch (Exception e) { // There was an Error. AddStatus("Constructor Err : " + e.Message); } }
// 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); } }