internal void Delete <T>(T entry)
{
if (entry == null)
{
throw new ArgumentNullException(nameof(entry));
}
string table = DatabaseTableNameFromObject(entry);
int id = IdValFromObject(entry, table);
Expression e = new Expression("id", Operators.Equals, id);
_Database.Delete(table, e);
}
/// <summary>
/// Delete document by its GUID.
/// </summary>
/// <param name="guid">GUID.</param>
public void DeleteDocumentByGuid(string guid)
{
if (String.IsNullOrEmpty(guid))
{
throw new ArgumentNullException(nameof(guid));
}
Log("deleting document GUID " + guid);
Expression eIndexEntries = new Expression("docs_guid", Operators.Equals, guid);
Expression eDocs = new Expression("guid", Operators.Equals, guid);
_Database.Delete("index_entries", eIndexEntries);
_Database.Delete("docs", eDocs);
return;
}
private static void DeleteRows()
{
for (int i = 20; i < 30; i++)
{
Expression e = new Expression("id", Operators.Equals, i);
client.Delete("person", e);
}
}
private static void DeleteRows()
{
for (int i = 20; i < 30; i++)
{
Expression e = new Expression("id", Operators.Equals, i);
_Database.Delete(_Table, e);
}
}
public void Run(APIGatewayProxyRequest request, APIGatewayProxyResponse response, FinanceUser user)
{
var spotReservation = JsonConvert.DeserializeObject <SpotReservation>(request.Body);
var databaseClient = new DatabaseClient <SpotReservation>(new AmazonDynamoDBClient(), new ConsoleLogger());
databaseClient.Delete(spotReservation);
response.StatusCode = 200;
response.Body = Constants.JSON_EMPTY;
}
/// <summary>
/// DELETE an object.
/// </summary>
/// <typeparam name="T">Type of object.</typeparam>
/// <param name="obj">Object to DELETE.</param>
public void Delete <T>(T obj) where T : class, new()
{
if (!_Initialized)
{
throw new InvalidOperationException("Initialize WatsonORM and database using the .InitializeDatabase() method first.");
}
if (obj == null)
{
throw new ArgumentNullException(nameof(obj));
}
string tableName = _TypeMetadataMgr.GetTableNameFromObject(obj);
string primaryKeyColumnName = _TypeMetadataMgr.GetPrimaryKeyColumnName(typeof(T));
string primaryKeyPropertyName = _TypeMetadataMgr.GetPrimaryKeyPropertyName(typeof(T));
object primaryKeyValue = _TypeMetadataMgr.GetPrimaryKeyValue(obj, primaryKeyPropertyName);
Expression e = new Expression(primaryKeyColumnName, WatsonORMCommon.DbOperatorsConverter(DbOperators.Equals), primaryKeyValue);
_Database.Delete(tableName, e);
}
private static Action <int> CreateAction(DatabaseClient client)
{
var randomItem = Random.Next(0, 110);
if (randomItem < 80)
{
return(i => client.Post(i.ToString(), GetTitleById(i)));
}
if (randomItem < 95)
{
return(i => client.Delete(i.ToString()));
}
return(i => client.Get(i.ToString()));
}
static void Main(string[] args)
{
Console.WriteLine("Opening file");
DatabaseClient sql = new DatabaseClient("test");
sql.Logger = Logger;
sql.LogQueries = true;
sql.LogResults = true;
Console.WriteLine("Creating table...");
List <Column> columns = new List <Column>();
columns.Add(new Column("id", true, DataType.Integer, false));
columns.Add(new Column("name", false, DataType.Text, true));
columns.Add(new Column("postal", false, DataType.Integer, true));
sql.CreateTable("company", columns);
Console.WriteLine("Checking if table 'company' exists: " + sql.TableExists("company"));
Console.WriteLine("Retrieving list of tables...");
List <string> tableNames = sql.ListTables();
if (tableNames != null && tableNames.Count > 0)
{
foreach (string curr in tableNames)
{
Console.WriteLine(" " + curr);
}
}
Console.WriteLine("Describing database...");
Dictionary <string, List <Column> > describeResult = sql.DescribeDatabase();
if (describeResult != null && describeResult.Count > 0)
{
foreach (KeyValuePair <string, List <Column> > curr in describeResult)
{
Console.WriteLine(" Table " + curr.Key);
if (curr.Value != null && curr.Value.Count > 0)
{
foreach (Column col in curr.Value)
{
Console.WriteLine(" " + col.ToString());
}
}
}
}
Console.WriteLine("Describing table 'company'...");
List <Column> cols = sql.DescribeTable("company");
if (cols != null && cols.Count > 0)
{
foreach (Column curr in cols)
{
Console.WriteLine(" " + curr.ToString());
}
}
Console.WriteLine("Adding data...");
Dictionary <string, object> d1 = new Dictionary <string, object>();
d1.Add("name", "company 1");
d1.Add("postal", 95128);
Dictionary <string, object> d2 = new Dictionary <string, object>();
d2.Add("name", "company 2");
d2.Add("postal", 62629);
Dictionary <string, object> d3 = new Dictionary <string, object>();
d3.Add("name", "company 3");
d3.Add("postal", 10101);
Dictionary <string, object> d4 = new Dictionary <string, object>();
d4.Add("name", "company 4");
d4.Add("postal", 90210);
Console.WriteLine("Created ID: " + sql.Insert("company", d1).ToString());
Console.WriteLine("Created ID: " + sql.Insert("company", d2).ToString());
Console.WriteLine("Created ID: " + sql.Insert("company", d3).ToString());
Console.WriteLine("Created ID: " + sql.Insert("company", d4).ToString());
Console.WriteLine("Selecting companies with postal > 70000...");
Expression eRetrieve1 = new Expression("postal", Operators.GreaterThan, 70000);
DataTable selectResult1 = sql.Select("company", 0, null, null, eRetrieve1, null);
Console.WriteLine("Retrieved " + selectResult1.Rows.Count + " rows");
Console.WriteLine("Selecting companies with postal > 70000 or postal < 50000...");
Expression eRetrieve2 = new Expression
{
LeftTerm = new Expression("postal", Operators.GreaterThan, 70000),
Operator = Operators.Or,
RightTerm = new Expression("postal", Operators.LessThan, 50000)
};
DataTable selectResult2 = sql.Select("company", 0, null, null, eRetrieve2, null);
Console.WriteLine("Retrieved " + selectResult2.Rows.Count + " rows");
Console.WriteLine("Deleting records...");
Expression eDelete = new Expression("id", Operators.GreaterThan, 0);
sql.Delete("company", eDelete);
Console.WriteLine("Dropping table...");
sql.DropTable("company");
}
public void RemoveApiKey(ApiKey key)
{
if (key == null)
{
throw new ArgumentNullException(nameof(key));
}
Expression e = new Expression("GUID", Operators.Equals, key.GUID);
_Database.Delete(API_KEY_TABLE, e);
e = new Expression("ApiKeyId", Operators.Equals, key.Id);
_Database.Delete(PERMISSION_TABLE, e);
}
static async Task DeleteTable(RequestMetadata md)
{
string dbName = md.Http.Request.Url.Elements[0];
string tableName = md.Http.Request.Url.Elements[1];
int idVal = 0;
if (md.Http.Request.Url.Elements.Length == 3)
{
Int32.TryParse(md.Http.Request.Url.Elements[2], out idVal);
}
Table currTable = _Databases.GetTableByName(dbName, tableName);
if (currTable == null)
{
md.Http.Response.StatusCode = 404;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(SerializationHelper.SerializeJson(new ErrorResponse(ErrorCodeEnum.NotFound, "The requested object was not found", null), true));
return;
}
DatabaseClient db = _Databases.GetDatabaseClient(dbName);
if (db == null)
{
md.Http.Response.StatusCode = 404;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(SerializationHelper.SerializeJson(new ErrorResponse(ErrorCodeEnum.NotFound, "The requested object was not found", null), true));
return;
}
if (md.Params.Truncate)
{
#region Truncate
db.Truncate(tableName);
_Logging.Warn("DeleteTable truncated table " + tableName + " in database " + dbName);
md.Http.Response.StatusCode = 204;
await md.Http.Response.Send();
return;
#endregion
}
else if (md.Params.Drop)
{
#region Drop
db.DropTable(tableName);
_Logging.Warn("DeleteTable dropped table " + tableName + " in database " + dbName);
md.Http.Response.StatusCode = 204;
await md.Http.Response.Send();
return;
#endregion
}
else if (md.Http.Request.Url.Elements.Length >= 2)
{
#region Delete-Objects
Expr filter = null;
if (idVal > 0)
{
if (String.IsNullOrEmpty(currTable.PrimaryKey))
{
_Logging.Warn("DeleteTable no primary key defined for table " + tableName + " in database " + dbName);
md.Http.Response.StatusCode = 400;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(SerializationHelper.SerializeJson(new ErrorResponse(ErrorCodeEnum.InvalidRequest, "Invalid request", "No primary key for table " + tableName), true));
return;
}
filter = new Expr(currTable.PrimaryKey, OperatorEnum.Equals, idVal);
}
if (md.Http.Request.Query.Elements != null && md.Http.Request.Query.Elements.Count > 0)
{
foreach (KeyValuePair <string, string> currKvp in md.Http.Request.Query.Elements)
{
if (Constants.QueryKeys.Contains(currKvp.Key))
{
continue;
}
if (filter == null)
{
filter = new Expr(currKvp.Key, OperatorEnum.Equals, currKvp.Value);
}
else
{
filter.PrependAnd(currKvp.Key, OperatorEnum.Equals, currKvp.Value);
}
}
}
db.Delete(tableName, filter);
md.Http.Response.StatusCode = 204;
await md.Http.Response.Send();
return;
#endregion
}
}
static async Task DeleteTable(HttpContext ctx)
{
string dbName = ctx.Request.RawUrlEntries[0];
string tableName = ctx.Request.RawUrlEntries[1];
int idVal = 0;
if (ctx.Request.RawUrlEntries.Count == 3)
{
Int32.TryParse(ctx.Request.RawUrlEntries[2], out idVal);
}
Table currTable = _Databases.GetTableByName(dbName, tableName);
if (currTable == null)
{
ctx.Response.StatusCode = 404;
ctx.Response.ContentType = "application/json";
await ctx.Response.Send(Common.SerializeJson(new ErrorResponse("Not found", null), true));
return;
}
DatabaseClient db = _Databases.GetDatabaseClient(dbName);
if (db == null)
{
ctx.Response.StatusCode = 404;
ctx.Response.ContentType = "application/json";
await ctx.Response.Send(Common.SerializeJson(new ErrorResponse("Not found", null), true));
return;
}
if (idVal == 0 &&
ctx.Request.RawUrlEntries.Count == 2)
{
#region Retrieve-Table
if (!ctx.Request.QuerystringEntries.ContainsKey("_truncate"))
{
_Logging.Warn("DeleteTable table deletion not allowed without setting _truncate in querystring for " + tableName + " in database " + dbName);
ctx.Response.StatusCode = 400;
ctx.Response.ContentType = "application/json";
await ctx.Response.Send(Common.SerializeJson(new ErrorResponse("Bad request", "Cannot truncate table without setting _truncate in querystring to true"), true));
return;
}
db.Truncate(tableName);
_Logging.Warn("DeleteTable truncated table " + tableName + " in database " + dbName);
ctx.Response.StatusCode = 200;
await ctx.Response.Send();
return;
#endregion
}
else
{
#region Delete-Objects
DataTable result = null;
Expression filter = null;
if (idVal > 0)
{
if (String.IsNullOrEmpty(currTable.PrimaryKey))
{
_Logging.Warn("DeleteTable no primary key defined for table " + tableName + " in database " + dbName);
ctx.Response.StatusCode = 400;
ctx.Response.ContentType = "application/json";
await ctx.Response.Send(Common.SerializeJson(new ErrorResponse("Bad request", "No primary key for table " + tableName), true));
return;
}
filter = new Expression(currTable.PrimaryKey, Operators.Equals, idVal);
}
if (ctx.Request.QuerystringEntries != null && ctx.Request.QuerystringEntries.Count > 0)
{
foreach (KeyValuePair <string, string> currKvp in ctx.Request.QuerystringEntries)
{
if (_ControlQueryKeys.Contains(currKvp.Key))
{
continue;
}
if (filter == null)
{
filter = new Expression(currKvp.Key, Operators.Equals, currKvp.Value);
}
else
{
filter = Expression.PrependAndClause(
new Expression(currKvp.Key, Operators.Equals, currKvp.Value),
filter);
}
}
}
result = db.Delete(tableName, filter);
ctx.Response.StatusCode = 200;
await ctx.Response.Send();
return;
#endregion
}
}
static HttpResponse DeleteTable(HttpRequest req)
{
string dbName = req.RawUrlEntries[0];
string tableName = req.RawUrlEntries[1];
int? idVal = req.RetrieveIdValue();
Table currTable = _Databases.GetTableByName(dbName, tableName);
if (currTable == null)
{
_Logging.Log(LoggingModule.Severity.Warn, "DeleteTable unknown table " + tableName + " in database " + dbName);
return(new HttpResponse(req, false, 404, null, null,
Common.SerializeJson(new ErrorResponse("Not found", null), true), true));
}
DatabaseClient db = _Databases.GetDatabaseClient(dbName);
if (db == null)
{
_Logging.Log(LoggingModule.Severity.Warn, "DeleteTable unable to retrieve database client for database " + dbName);
return(new HttpResponse(req, false, 404, null, null,
Common.SerializeJson(new ErrorResponse("Not found", null), true), true));
}
if (idVal == null &&
req.RawUrlEntries.Count == 2)
{
#region Retrieve-Table
bool truncate = Common.IsTrue(req.RetrieveHeaderValue("_truncate"));
if (!truncate)
{
_Logging.Log(LoggingModule.Severity.Warn, "DeleteTable table deletion not allowed without setting _truncate in querystring for " + tableName + " in database " + dbName);
return(new HttpResponse(req, false, 400, null, null,
Common.SerializeJson(new ErrorResponse("Bad request", "Cannot truncate table without setting _truncate in querystring to true"), true), true));
}
db.Truncate(tableName);
_Logging.Log(LoggingModule.Severity.Warn, "DeleteTable truncated table " + tableName + " in database " + dbName);
return(new HttpResponse(req, true, 200, null, null, null, true));
#endregion
}
else
{
#region Delete-Objects
DataTable result = null;
Expression filter = null;
if (idVal != null)
{
if (String.IsNullOrEmpty(currTable.PrimaryKey))
{
_Logging.Log(LoggingModule.Severity.Warn, "DeleteTable no primary key defined for table " + tableName + " in database " + dbName);
return(new HttpResponse(req, false, 400, null, null,
Common.SerializeJson(new ErrorResponse("Bad request", "No primary key for table " + tableName), true), true));
}
filter = new Expression(currTable.PrimaryKey, Operators.Equals, idVal);
}
if (req.QuerystringEntries != null && req.QuerystringEntries.Count > 0)
{
foreach (KeyValuePair <string, string> currKvp in req.QuerystringEntries)
{
if (_ControlQueryKeys.Contains(currKvp.Key))
{
continue;
}
if (filter == null)
{
filter = new Expression(currKvp.Key, Operators.Equals, currKvp.Value);
}
else
{
filter = Expression.PrependAndClause(
new Expression(currKvp.Key, Operators.Equals, currKvp.Value),
filter);
}
}
}
result = db.Delete(tableName, filter);
return(new HttpResponse(req, true, 200, null, null, null, true));
#endregion
}
}
static void Main(string[] args)
{
Console.WriteLine("Opening file");
DatabaseClient sql = new DatabaseClient("test");
sql.Logger = Logger;
sql.LogQueries = true;
sql.LogResults = true;
Console.WriteLine("Creating table...");
string createTableQuery =
"CREATE TABLE IF NOT EXISTS company " +
"( id INTEGER PRIMARY KEY AUTOINCREMENT, " +
" name NVARCHAR(64), " +
" postal INT)";
DataTable createTableResult = sql.Query(createTableQuery);
Console.WriteLine("Adding data");
Dictionary <string, object> d1 = new Dictionary <string, object>();
d1.Add("name", "company 1");
d1.Add("postal", 95128);
Dictionary <string, object> d2 = new Dictionary <string, object>();
d2.Add("name", "company 2");
d2.Add("postal", 62629);
Dictionary <string, object> d3 = new Dictionary <string, object>();
d3.Add("name", "company 3");
d3.Add("postal", 10101);
Dictionary <string, object> d4 = new Dictionary <string, object>();
d4.Add("name", "company 4");
d4.Add("postal", 90210);
Console.WriteLine("Created ID: " + sql.Insert("company", d1).ToString());
Console.WriteLine("Created ID: " + sql.Insert("company", d2).ToString());
Console.WriteLine("Created ID: " + sql.Insert("company", d3).ToString());
Console.WriteLine("Created ID: " + sql.Insert("company", d4).ToString());
Console.WriteLine("Selecting companies with postal > 70000");
Expression eRetrieve1 = new Expression("postal", Operators.GreaterThan, 70000);
DataTable selectResult1 = sql.Select("company", 0, null, null, eRetrieve1, null);
Console.WriteLine("Retrieved " + selectResult1.Rows.Count + " rows");
Console.WriteLine("Selecting companies with postal > 70000 or postal < 50000");
Expression eRetrieve2 = new Expression
{
LeftTerm = new Expression("postal", Operators.GreaterThan, 70000),
Operator = Operators.Or,
RightTerm = new Expression("postal", Operators.LessThan, 50000)
};
DataTable selectResult2 = sql.Select("company", 0, null, null, eRetrieve2, null);
Console.WriteLine("Retrieved " + selectResult2.Rows.Count + " rows");
Console.WriteLine("Deleting records");
Expression eDelete = new Expression("id", Operators.GreaterThan, 0);
sql.Delete("company", eDelete);
}