public void SaveDatabase()
{
Debug.Log(baseSamplePath + databaseName);
Debug.Log(SampleTracker.samplesList.Count);
if (File.Exists(baseSamplePath + databaseName))
{
Debug.Log("Exists");
// Read and concatinate file
string file = File.ReadAllText(baseSamplePath + databaseName);
SampleDatabase tempDatabase = JsonUtility.FromJson <SampleDatabase>(file);
Debug.Log(tempDatabase.database.Length);
Debug.Log(tempDatabase.database[1].label);
Debug.Log(tempDatabase.database[22].label);
SampleDatabase newDatabase = new SampleDatabase(tempDatabase.database, SampleTracker.samplesList.ToArray());
string newFile = JsonUtility.ToJson(newDatabase);
File.WriteAllText(baseSamplePath + databaseName, newFile);
}
else
{
SampleDatabase tempDatabase = new SampleDatabase(SampleTracker.samplesList.ToArray());
string newFile = JsonUtility.ToJson(tempDatabase);
File.WriteAllText(baseSamplePath + databaseName, newFile);
}
}
public void Suggest_Basics()
{
SampleDatabase.EnsureBuilt();
QuerySuggester suggester = new QuerySuggester(SampleDatabase.XDatabaseContext);
// Verbs
Assert.AreEqual(s_verbs, Values(suggester.Suggest("")));
Assert.AreEqual(s_verbs, Values(suggester.Suggest("re")));
// Tables
Assert.AreEqual(s_sources, Values(suggester.Suggest("read")));
// Valid
Assert.AreEqual(null, Values(suggester.Suggest($"read WebRequest")));
// Verbs (newline)
Assert.AreEqual(s_verbs, Values(suggester.Suggest($"read WebRequest\r\n")));
Assert.AreEqual(s_verbs, Values(suggester.Suggest($"read WebRequest\r\n ")));
// CompareOperator
Assert.AreEqual("!=|:|::||>|<|<=|<>|=|==|>|>=", Values(suggester.Suggest($@"
read WebRequest
where [HttpStatus] !")));
// Value missing
Assert.AreEqual(s_selectListOptions, Values(suggester.Suggest($@"
read WebRequest
where [HttpStatus] != ")));
// ColumnFunctionOrLiteral
Assert.AreEqual(s_selectListOptions, Values(suggester.Suggest($@"
read WebRequest
select")));
// Function argument (type)
Assert.AreEqual(s_types, Values(suggester.Suggest($@"
read WebRequest
select Trim(Cast(Cast(5, Int32), ")));
// Function argument (ColumnFunctionOrLiteral)
Assert.AreEqual(s_stringSelectListOptions, Values(suggester.Suggest($@"
read WebRequest
select Trim(")));
// Nested Function argument (ColumnFunctionOrLiteral)
Assert.AreEqual(s_stringSelectListOptions, Values(suggester.Suggest($@"
read WebRequest
select Cast(Trim(")));
// Correct nested function use
Assert.AreEqual(true, suggester.Suggest($@"
read WebRequest
select Trim(Cast(Cast(5, Int32), String8)) AS [Fiver]").IsValid);
// Select next argument
Assert.AreEqual(s_selectListOptions, Values(suggester.Suggest($@"
read WebRequest
select [HttpStatus]")));
}
public void Suggest_NoErrorsForValidValues()
{
SampleDatabase.EnsureBuilt();
QuerySuggester suggester = new QuerySuggester(SampleDatabase.XDatabaseContext);
// No error on constant
Assert.AreEqual("", suggester.Suggest($@"
read WebRequest
where Cast([HttpStatus], UInt16) < 10").Context.ErrorMessage);
}
public void Sample()
{
SampleDatabase.EnsureBuilt();
string xqlQuery = @"
read WebRequest
select [ServerPort], Cast([ResponseBytes], Int32)
where [ServerPort] = ""80""
limit 1000
";
int desiredRowCountPerPage = 100;
int maxResponseBytes = -1;
// Build a Pipeline for the query. Wrap in a using statement to Dispose it when done.
using (IXTable pipeline = SampleDatabase.XDatabaseContext.Query(xqlQuery))
{
// Identify the columns you're consuming by requesting and caching the getter functions for them.
// You must request the getters before the first call to Next().
// This tells the caller which columns you care about and builds hardcoded logic to get the data you want.
Func <XArray> columnGetter = pipeline.Columns.Find("ResponseBytes").CurrentGetter();
// Call Next() to get an XArray of rows. Ask for only as many as you need. Ask for an XArray size convenient to work with.
// Next() may return fewer rows than you asked for, but will not return zero until the input has run out of rows.
while (pipeline.Next(desiredRowCountPerPage) > 0)
{
// Get the values for your desired column for this set of rows.
XArray responseBytesxarray = columnGetter();
// If you know the type of the column, you can safely cast the array to the right type.
// This allows you to write C# code hard-coded to the type, so there's no boxing and no interface calls.
int[] array = (int[])responseBytesxarray.Array;
// Loop from zero to the count the xarray says it returned
for (int i = 0; i < responseBytesxarray.Count; ++i)
{
// You need to look up the real index of each value in the array.
// Index() allows callers to pass a whole array, and array slice, or lookup indices into the array.
// The CPU figures out the pattern quickly so branch costs are minimal.
int responseBytes = array[responseBytesxarray.Index(i)];
// Run your own logic (in this case, track the Max of the value)
if (responseBytes > maxResponseBytes)
{
maxResponseBytes = responseBytes;
}
}
}
}
Assert.AreEqual(1335, maxResponseBytes);
}
public void Suggest_FullErrorFidelity()
{
SampleDatabase.EnsureBuilt();
QuerySuggester suggester = new QuerySuggester(SampleDatabase.XDatabaseContext);
SuggestResult result = suggester.Suggest(@"
read UsageError.WebRequest.MissingColumn");
Assert.AreEqual(false, result.IsValid);
Assert.AreEqual("UsageError.WebRequest.MissingColumn", result.Context.TableName);
Assert.AreEqual(2, result.Context.QueryLineNumber);
Assert.AreEqual("where {Expression}", result.Context.Usage);
Assert.AreEqual("BadColumnName", result.Context.InvalidValue);
Assert.AreEqual("[Column]", result.Context.InvalidValueCategory);
Assert.AreEqual(s_columnNames, string.Join("|", result.Context.ValidValues));
}
public void Suggest_FullErrorFidelity()
{
SampleDatabase.EnsureBuilt();
QuerySuggester suggester = new QuerySuggester(SampleDatabase.XDatabaseContext);
SuggestResult result = suggester.Suggest(@"
read WebRequest
where [BadColumnName] != ""X""");
Assert.AreEqual(false, result.IsValid);
Assert.AreEqual(null, result.Context.TableName);
Assert.AreEqual(3, result.Context.QueryLineNumber);
Assert.AreEqual("where {Expression}", result.Context.Usage);
Assert.AreEqual("[BadColumnName]", result.Context.InvalidValue);
Assert.AreEqual("[Column]", result.Context.InvalidValueCategory);
Assert.AreEqual("[ClientBrowser]|[ClientIP]|[ClientOs]|[ClientRegion]|[DataCenter]|[DaysSinceJoined]|[EventTime]|[HttpMethod]|[HttpStatus]|[ID]|[IsPremiumUser]|[Protocol]|[RequestBytes]|[ResponseBytes]|[ServerName]|[ServerPort]|[TimeTakenMs]|[UriStem]|[UserGuid]|[UserName]|[WasCachedResponse]|[WasEncrypted]", string.Join("|", result.Context.ValidValues));
}
public static void XTable_All(string configurationLine, int expectedRowCount, string[] requiredColumns = null)
{
SampleDatabase.EnsureBuilt();
int requiredColumnCount = (requiredColumns == null ? 0 : requiredColumns.Length);
long actualRowCount;
using (CancellationTokenSource source = new CancellationTokenSource())
{
IXTable pipeline = null;
ValidatingTable innerValidator = null;
CancellationToken token = source.Token;
try
{
pipeline = SampleDatabase.XDatabaseContext.Load("WebRequest");
innerValidator = new ValidatingTable(pipeline);
pipeline = SampleDatabase.XDatabaseContext.Query(configurationLine, innerValidator);
// Run without requesting any columns. Validate.
actualRowCount = pipeline.RunWithoutDispose(token).RowCount;
Assert.AreEqual(expectedRowCount, actualRowCount, "XTable should return correct count with no requested columns.");
// Reset; Request all columns. Validate.
pipeline.Reset();
pipeline = SampleDatabase.XDatabaseContext.Query("write \"Sample.output.csv\"", pipeline);
actualRowCount = pipeline.RunWithoutDispose(token).RowCount;
}
finally
{
if (pipeline != null)
{
pipeline.Dispose();
pipeline = null;
if (innerValidator != null)
{
Assert.IsTrue(innerValidator.DisposeCalled, "Source must call Dispose on nested sources.");
}
}
}
}
}
public void Suggest_NoErrorOnLastToken()
{
SampleDatabase.EnsureBuilt();
QuerySuggester suggester = new QuerySuggester(SampleDatabase.XDatabaseContext);
// Verify errors only when token is complete and you've moved on
Assert.AreEqual("", suggester.Suggest("read BadTable").Context.ErrorMessage);
Assert.AreNotEqual("", suggester.Suggest("read BadTable\r\n").Context.ErrorMessage);
Assert.AreEqual("", suggester.Suggest("read WebRequest\r\ncase").Context.ErrorMessage);
Assert.AreNotEqual("", suggester.Suggest("read WebRequest\r\ncase ").Context.ErrorMessage, "Bad verb 'case' is now complete");
Assert.AreEqual("", suggester.Suggest("read WebRequest\r\ncast ").Context.ErrorMessage);
Assert.AreEqual("", suggester.Suggest("read WebRequest\r\ncast [HttpStatur]").Context.ErrorMessage);
Assert.AreNotEqual("", suggester.Suggest("read WebRequest\r\ncast [HttpStatur] ").Context.ErrorMessage, "Bad column name now complete");
Assert.AreEqual("", suggester.Suggest("read WebRequest\r\ncast [HttpStatus] Int").Context.ErrorMessage);
Assert.AreEqual("", suggester.Suggest("read WebRequest\r\ncast [HttpStatus] Int33").Context.ErrorMessage);
Assert.AreNotEqual("", suggester.Suggest("read WebRequest\r\ncast [HttpStatus] Int33 ").Context.ErrorMessage, "Bad Type name now complete");
Assert.AreEqual("", suggester.Suggest("read WebRequest\r\ncast [HttpStatus] Int32 ").Context.ErrorMessage, "All Valid, optional not provided");
Assert.AreNotEqual("", suggester.Suggest("read WebRequest\r\ncast [HttpStatur] Int32").Context.ErrorMessage, "Bad column name isn't last argument");
Assert.AreNotEqual("", suggester.Suggest("read WebRequest\r\ncase [HttpStatus] Int32").Context.ErrorMessage, "Bad verb isn't last argument");
Assert.AreNotEqual("", suggester.Suggest("read BadTable\r\ncast [HttpStatus] Int32").Context.ErrorMessage, "Bad table isn't last argument");
}
void Awake()
{
//temp singleton init
SampleDatabase db = SampleDatabase.Instance;
}
static void Main(string[] args)
{
Console.WriteLine("Sample database usage");
Console.WriteLine("1st approach - SqlDataClient");
// I - SqlDataClient
string dbConnection = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Briska\source\repos\NET_BC_202102\NET_BC\MyFirstDb.mdf;Integrated Security=True;Connect Timeout=30";
using (SqlConnection connection = new SqlConnection(dbConnection))
{
connection.Open();
string query = "select * from Users ORDER BY RegisteredOn ASC";
using (SqlCommand command = new SqlCommand(query, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
// read row by row
while (reader.Read())
{
string firstName = Convert.ToString(reader["FirstName"]);
string lastName = Convert.ToString(reader["LastName"]);
DateTime registeredOn = Convert.ToDateTime(reader["RegisteredOn"]);
Console.WriteLine("User {0} {1}, created on {2}", firstName, lastName, registeredOn);
}
}
}
connection.Close();
}
Console.WriteLine("2nd approach - Entity Framework");
// II - Entity Framework
// 1. Add reference to Nuget Package "Microsoft.EntityFrameworkCore.Tools" 3.1.X version
// 2. Add reference to Nuget Package "Microsoft.EntityFrameworkCore.SqlServer" 3.1.X version
// 3. Use Package Manager Console (select correct active project) and scaffold database:
// Scaffold-DbContext "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\XX\MyFirstDb.mdf;Integrated Security=True;Connect Timeout=30" Microsoft.EntityFrameworkCore.SqlServer -OutputDir DB -Context SampleDatabase
// 4. Use generated classes
// To add data:
using (SampleDatabase db = new SampleDatabase())
{
db.Users.Add(new Users()
{
FirstName = "New",
LastName = "User",
RegisteredOn = DateTime.Now,
});
db.SaveChanges();
}
// To modify data:
using (SampleDatabase db = new SampleDatabase())
{
var user = db.Users.FirstOrDefault(u => u.LastName == "User");
if (user != null)
{
user.LastName = "updated user";
}
db.SaveChanges();
}
// To delete data:
using (SampleDatabase db = new SampleDatabase())
{
var user = db.Users.FirstOrDefault(u => u.LastName == "Smith");
if (user != null)
{
db.Users.Remove(user);
}
db.SaveChanges();
}
// To select data:
using (SampleDatabase db = new SampleDatabase())
{
List <Users> users = db.Users.OrderBy(u => u.RegisteredOn).ToList();
foreach (var u in users)
{
Console.WriteLine("User {0} {1}, created {2}", u.FirstName, u.LastName, u.RegisteredOn);
}
}
}
public void Suggest_Basics()
{
SampleDatabase.EnsureBuilt();
QuerySuggester suggester = new QuerySuggester(SampleDatabase.XDatabaseContext);
// Verbs
Assert.AreEqual(s_verbs, Values(suggester.Suggest("")));
Assert.AreEqual(string.Join("|", XqlParser.SupportedVerbs.Where((s) => s.IndexOf("re", System.StringComparison.OrdinalIgnoreCase) != -1).OrderBy((s) => s)), Values(suggester.Suggest("re")));
// Valid, other values available
Assert.AreEqual("read|readrange", Values(suggester.Suggest("read")));
// Tables
Assert.AreEqual(s_sources, Values(suggester.Suggest("read ")));
// Tables, filtered
Assert.AreEqual("WebRequest.BigServers|WebRequest.BigServers.Direct", Values(suggester.Suggest($"read Request.Big")));
// Valid, no alternatives
Assert.AreEqual(null, Values(suggester.Suggest($"read WebRequest.NullableHandling")));
// Verbs (newline)
Assert.AreEqual(s_verbs, Values(suggester.Suggest($"read WebRequest\r\n")));
Assert.AreEqual(s_verbs, Values(suggester.Suggest($"read WebRequest\r\n ")));
// Valid
Assert.AreEqual("!=|:|::||>|<|<=|<>|=|==|>|>||>=", Values(suggester.Suggest($@"
read WebRequest
where [HttpStatus]")));
// CompareOperator
Assert.AreEqual("!=|:|::||>|<|<=|<>|=|==|>|>||>=", Values(suggester.Suggest($@"
read WebRequest
where [HttpStatus] ")));
// CompareOperator [partially typed]
Assert.AreEqual("!=", Values(suggester.Suggest($@"
read WebRequest
where [HttpStatus] !")));
// Valid
Assert.AreEqual(null, Values(suggester.Suggest($@"
read WebRequest
where [HttpStatus] !=")));
// Value missing
Assert.AreEqual(s_selectListOptions, Values(suggester.Suggest($@"
read WebRequest
where [HttpStatus] != ")));
// Valid
Assert.AreEqual(null, Values(suggester.Suggest($@"
read WebRequest
select")));
// ColumnFunctionOrLiteral
Assert.AreEqual(s_selectListOptions, Values(suggester.Suggest($@"
read WebRequest
select ")));
// Function argument (type)
Assert.AreEqual(s_types, Values(suggester.Suggest($@"
read WebRequest
select Trim(Cast(Cast(5, Int32), ")));
// Function argument (ColumnFunctionOrLiteral)
Assert.AreEqual(s_stringSelectListOptions, Values(suggester.Suggest($@"
read WebRequest
select Trim(")));
Assert.AreEqual(s_stringSelectListOptions, Values(suggester.Suggest($@"
read WebRequest
select Trim(")));
// Nested Function argument (ColumnFunctionOrLiteral)
Assert.AreEqual(s_stringSelectListOptions, Values(suggester.Suggest($@"
read WebRequest
select Cast(Trim(")));
// Correct nested function use
Assert.AreEqual(true, suggester.Suggest($@"
read WebRequest
select Trim(Cast(Cast(5, Int32), String8)) AS [Fiver]").IsValid);
// Valid
Assert.AreEqual(s_selectListOptions, Values(suggester.Suggest($@"
read WebRequest
select [HttpStatus]")));
// Select next argument
Assert.AreEqual(s_selectListOptions, Values(suggester.Suggest($@"
read WebRequest
select [HttpStatus], ")));
}
