public BsonValue Execute(LiteDatabase db)
{
using (var reader = db.Execute(this.Sql))
{
return(reader.FirstOrDefault());
}
}
public BsonValue[] Query(string sql, params BsonValue[] args)
{
var log = new LogItem
{
Task = _taskName,
Index = _index,
Concurrent = _concurrent.Increment() - 1,
Command = sql,
Thread = Task.CurrentId
};
var start = Stopwatch.StartNew();
try
{
return(_db.Execute(sql, args).ToArray());
}
catch (Exception ex)
{
log.Error = ex.Message + '\n' + ex.StackTrace;
throw ex;
}
finally
{
_concurrent.Decrement();
log.Elapsed = start.ElapsedMilliseconds;
_logger?.Insert(log);
}
}
public String exportAll()
{
File.Delete("allWtrs.csv");
using (var db = new LiteDatabase(DATABASE_FILE))
{
db.Execute("SELECT $ INTO $FILE('allWtrs.csv') FROM worktimeRecords");
}
return("allWtrs.csv");
}
// Execution ValheimPermissions.ValheimDB.DelGroup(GROUP_NAME)
// Results: Will return false if the group does not exist
// Or: Will remove all users & permissions associated with the group
// And: Will remove the Group itself
// And: Will return true;
// NOTE: Results will be returned to you in BOOL format (true/false).
public static bool DelGroup(string group_name)
{
using (var db = new LiteDatabase(DatabaseLocation))
{
var groups = db.GetCollection <Group>("Group");
var users = db.GetCollection <User>("User");
var permission = db.GetCollection <Group_Permission>("Group_Permission");
var exists = groups.FindOne(Query.EQ("Group_Name", group_name));
if (exists == null)
{
return(false);
}
else
{
var resultpermission = db.Execute($"DELETE Group_Permission WHERE Group_Name='{group_name}'");
var resultsusers = db.Execute($"DELETE User WHERE Group_Name='{group_name}'");
var resultsgroup = db.Execute($"DELETE Group WHERE Group_Name='{group_name}'");
return(true);
}
}
}
public void Home_Example()
{
using (var f = new TempFile())
using (var db = new LiteDatabase(f.Filename))
{
// Get customer collection
var customers = db.GetCollection <Customer>("customers");
// Create your new customer instance
var customer = new Customer
{
Name = "John Doe",
Phones = new string[] { "8000-0000", "9000-0000" },
IsActive = true
};
// Insert new customer document (Id will be auto-incremented)
customers.Insert(customer);
// Update a document inside a collection
customer.Name = "Joana Doe";
customers.Update(customer);
// Index document using a document property
customers.EnsureIndex(x => x.Name);
// Now, let's create a simple query
var results = customers.Find(x => x.Name.StartsWith("Jo")).ToList();
results.Count.Should().Be(1);
// Or you can query using new Query() syntax
var results2 = customers.Query()
.Where(x => x.Phones.Any(p => p.StartsWith("8000")))
.OrderBy(x => x.Name)
.Select(x => new { x.Id, x.Name })
.Limit(10)
.ToList();
// Or using SQL
var reader = db.Execute(
@"SELECT _id, Name
FROM customers
WHERE Phones ANY LIKE '8000%'
ORDER BY Name
LIMIT 10");
results2.Count.Should().Be(1);
reader.ToList().Count.Should().Be(1);
}
}
public StressTest(string filename, bool synced)
{
File.Delete(filename);
File.Delete(GetSufixFile(filename, "-log", false));
_db = new LiteDatabase(filename);
_db.Execute("PRAGMA TIMEOUT = 3600");
_debugger = new DatabaseDebugger(_db);
_debugger.Start(8001);
}
// Execution ValheimPermissions.ValheimDB.DelGroupPermission(GROUP_NAME, PERMISSION_NODE)
// Results: Will return false if the Group does not already have access to the permission node
// Or: WIll return true if the deletion was successful
// NOTE: Results will be returned to you in BOOL format (true/false)
public static bool DelGroupPermission(string Group_Name, string permission)
{
permission = permission.ToLower();
using (var db = new LiteDatabase(DatabaseLocation))
{
var permissions = db.GetCollection <Group_Permission>("Group_Permission");
var pexists = permissions.FindOne(Query.And(Query.EQ("Group_Name", Group_Name), Query.EQ("permission", permission)));
if (pexists != null)
{
var result = db.Execute($"DELETE Group_Permission WHERE Group_Name='{Group_Name}' and permission='{permission}'");
return(true);
}
return(false);
}
}
// Execution ValheimPermissions.ValheimDB.DelUserPermission(STEAMID, PERMISSION_NODE)
// Important Note: You need to send the SteamID in STRING format instead of the default LONG format. use SteamID.ToString() before sending.
// Results: Will return false if the user does not already have access to the permission node
// Or: WIll return true if the deletion was successful
// NOTE: Results will be returned to you in BOOL format (true/false)
public static bool DelUserPermission(string SteamID, string permission)
{
permission = permission.ToLower();
using (var db = new LiteDatabase(DatabaseLocation))
{
var permissions = db.GetCollection <User_Permission>("User_Permission");
var pexists = permissions.FindOne(Query.And(Query.EQ("SteamID", SteamID), Query.EQ("permission", permission)));
if (pexists != null)
{
db.Execute($"DELETE User_Permission WHERE SteamID='{SteamID}' and permission='{permission}'");
return(true);
}
return(false);
}
}
// Execution ValheimPermissions.ValheimDB.DelUserGroup(STEAMID)
// Results: Will return false if the User is not in a group
// Or: Will remove the user from the group they are currently in..
// NOTE: Results will be returned to you in BOOL format (true/false).
public static bool DelUserGroup(string steamid)
{
using (var db = new LiteDatabase(DatabaseLocation))
{
var users = db.GetCollection <User>("User");
var exists = users.FindOne(Query.EQ("SteamID", steamid));
if (exists == null)
{
return(false);
}
else
{
var resultsusers = db.Execute($"DELETE User WHERE SteamID='{steamid}'");
return(true);
}
}
}
public void IndexExtendedLength_Tests()
{
using var db = new LiteDatabase(":memory:");
var col = db.GetCollection("customers", BsonAutoId.Int32);
col.EnsureIndex("$.Name");
col.Insert(new BsonDocument {
["Name"] = new string('A', 1010)
});
col.Insert(new BsonDocument {
["Name"] = new string('B', 230)
});
var results = db.Execute("select $ from customers where $.Name < 'B'").ToArray();
Assert.Single(results);
}
public BsonValue[] Execute(string sql, params BsonValue[] args)
{
var parameters = new BsonDocument();
for (var i = 0; i < args.Length; i++)
{
parameters[i.ToString()] = args[i];
}
var log = new Log
{
Task = _taskName,
Timer = (int)_watch.ElapsedMilliseconds,
Concurrent = _concurrent.Increment(),
Delay = (int)(_watch.ElapsedMilliseconds - _delay),
Sql = sql,
Params = parameters
};
var start = DateTime.Now;
try
{
using (var reader = _db.Execute(sql, parameters))
{
return(reader.ToArray());
}
}
catch (Exception ex)
{
log.Error = ex.Message;
throw ex;
}
finally
{
_concurrent.Decrement();
log.Elapsed = DateTime.Now.Subtract(start).TotalMilliseconds;
_logger.Insert(log);
_delay = _watch.ElapsedMilliseconds;
}
}
private BsonValue[] Execute(string sql, params BsonValue[] args)
{
var parameters = new BsonDocument();
for (var i = 0; i < args.Length; i++)
{
parameters[i.ToString()] = args[i];
}
var log = new Log
{
Task = _taskName,
Timer = (int)_watch.ElapsedMilliseconds,
Concurrent = _concurrent.Increment() - 1,
Delay = (int)(_watch.ElapsedMilliseconds - _delay),
Command = sql,
Thread = Task.CurrentId
};
var start = DateTime.Now;
try
{
return(_db.Execute(sql, parameters).ToArray());
}
catch (Exception ex)
{
log.Error = ex.Message + '\n' + ex.StackTrace;
throw ex;
}
finally
{
_concurrent.Decrement();
log.Elapsed = DateTime.Now.Subtract(start).TotalMilliseconds;
_logger?.Insert(log);
_delay = _watch.ElapsedMilliseconds;
}
}
public void Execute()
{
if (_file.Delete)
{
this.DeleteFiles();
}
_db = new LiteDatabase(_file.Filename);
_db.Pragma("TIMEOUT", (int)_file.Timeout.TotalSeconds);
foreach (var setup in _file.Setup)
{
_db.Execute(setup);
}
// create all threads
this.CreateThreads();
// start report thread
var t = new Thread(() => this.ReportThread());
t.Name = "REPORT";
t.Start();
}
public void ExecuteSql(string sql) => Database.Execute(sql);
public string GetDatabaseName()
{
var keyPairCollection = (IEnumerable <KeyValuePair <string, LiteDB.BsonValue> >)LiteDatabase
.Execute("SELECT name FROM $database").Current.RawValue;
string result = keyPairCollection.First().Value.AsString;
return(result);
}
public double GetDatabaseSize()
{
var keyPairCollection = (IEnumerable <KeyValuePair <string, LiteDB.BsonValue> >)LiteDatabase
.Execute("SELECT dataFileSize FROM $database").Current.RawValue;
double result = keyPairCollection.First().Value.AsDouble;
return(result);
}
public void Index_With_Like()
{
using (var db = new LiteDatabase("filename=:memory:"))
{
var col = db.GetCollection("names", BsonAutoId.Int32);
col.Insert(new[]
{
new BsonDocument {
["name"] = "marcelo"
},
new BsonDocument {
["name"] = "mauricio"
},
new BsonDocument {
["name"] = "Mauricio"
},
new BsonDocument {
["name"] = "MAUricio"
},
new BsonDocument {
["name"] = "MAURICIO"
},
new BsonDocument {
["name"] = "mauRO"
},
new BsonDocument {
["name"] = "ANA"
}
});
col.EnsureIndex("idx_name", "name", false);
var all = db.Execute("SELECT name FROM names").ToArray();
// LIKE are case insensitive
var r0 = db.Execute("SELECT name FROM names WHERE name LIKE 'Mau%'").ToArray();
var r1 = db.Execute("SELECT name FROM names WHERE name LIKE 'MAU%'").ToArray();
var r2 = db.Execute("SELECT name FROM names WHERE name LIKE 'mau%'").ToArray();
r0.Length.Should().Be(5);
r1.Length.Should().Be(5);
r2.Length.Should().Be(5);
// only `mauricio´
var r3 = db.Execute("SELECT name FROM names WHERE name LIKE 'ma%ci%'").ToArray();
var r4 = db.Execute("SELECT name FROM names WHERE name LIKE 'maUriCIO").ToArray();
r3.Length.Should().Be(4);
r4.Length.Should().Be(4);
var r5 = db.Execute("SELECT name FROM names WHERE name LIKE 'marc_o").ToArray();
r5.Length.Should().Be(0);
// `marcelo`
var r6 = db.Execute("SELECT name FROM names WHERE name LIKE 'marc__o").ToArray();
r6.Length.Should().Be(1);
}
}
private void CreateThread(TaskData task)
{
while (true)
{
task.WaitHandle.Wait();
if (task.ThreadRunning == false)
{
break;
}
if (task.Sql.Trim() == "")
{
task.WaitHandle.Reset();
continue;
}
var sw = new Stopwatch();
sw.Start();
try
{
task.Executing = true;
task.IsGridLoaded = task.IsTextLoaded = task.IsParametersLoaded = false;
_synchronizationContext.Post(o => { LoadResult(task); }, task);
task.Parameters = new BsonDocument();
var sql = new StringReader(task.Sql.Trim());
while (sql.Peek() >= 0 && _db != null)
{
using (var reader = _db.Execute(sql, task.Parameters))
{
task.ReadResult(reader);
}
}
task.Elapsed = sw.Elapsed;
task.Exception = null;
task.Executing = false;
// update form button selected
_synchronizationContext.Post(o =>
{
var t = o as TaskData;
if (ActiveTask?.Id == t.Id)
{
LoadResult(o as TaskData);
}
}, task);
}
catch (Exception ex)
{
task.Executing = false;
task.Result = null;
task.Elapsed = sw.Elapsed;
task.Exception = ex;
_synchronizationContext.Post(o =>
{
var t = o as TaskData;
if (ActiveTask?.Id == t.Id)
{
tabResult.SelectedTab = tabText;
LoadResult(o as TaskData);
}
}, task);
}
// put thread in wait mode
task.WaitHandle.Reset();
}
task.WaitHandle.Dispose();
}
public void MultiKey_Index_Test()
{
using var db = new LiteDatabase("filename=:memory:");
var col = db.GetCollection("customers", BsonAutoId.Int32);
col.EnsureIndex("$.Phones[*].Type");
var doc1 = new BsonDocument
{
["Name"] = "John Doe",
["Phones"] = new BsonArray
(
new BsonDocument
{
["Type"] = "Mobile",
["Number"] = "9876-5432"
},
new BsonDocument
{
["Type"] = "Fixed",
["Number"] = "3333-3333"
}
)
};
var doc2 = new BsonDocument
{
["Name"] = "Jane Doe",
["Phones"] = new BsonArray
(
new BsonDocument
{
["Type"] = "Fixed",
["Number"] = "3000-0000"
}
)
};
col.Insert(doc1);
col.Insert(doc2);
var query1 = "select $ from customers where $.Phones[*].Type any = 'Mobile'";
var query2 = "select $ from customers where $.Phones[*].Type any = 'Fixed'";
var explain1 = db.Execute("explain " + query1).First();
Assert.True(!explain1["index"]["mode"].AsString.Contains("_id"));
var explain2 = db.Execute("explain " + query2).First();
Assert.True(!explain2["index"]["mode"].AsString.Contains("_id"));
var result1 = db.Execute(query1).ToArray();
Assert.True(result1.Length == 1);
var result2 = db.Execute(query2).ToArray();
Assert.True(result2.Length == 2);
}