public static void CreateTable(this SurlyDatabase database, string tableName, string line)
{
Set(Yellow);
if (database.Tables.Any(x => x.Name == tableName))
{
WriteLine($"\n\tTable {tableName} already exists. Please select a different table name.", Red);
return;
}
var tuples = string.Empty;
try
{
tuples = line.Substring(line.IndexOf("(", StringComparison.Ordinal) + 1,
line.IndexOf(")", StringComparison.Ordinal) - line.IndexOf("(", StringComparison.Ordinal) - 1);
}
catch (Exception)
{
WriteLine("\n\tSyntax error", Red);
}
Set(Green);
var tableCreated = false;
foreach (var tuple in tuples.Split(','))
{
var parts = tuple.Trim().Split(' ');
int numMax;
if (!database.ValidTuple(tableName, parts))
{
return;
}
if (!tableCreated)
{
database.Tables.AddLast(new SurlyTable(tableName));
tableCreated = true;
WriteLine($"\n\tCreating table: {tableName}", Cyan);
}
database.Tables.Last.Value.Schema.AddLast(new SurlyAttributeSchema
{
Name = parts[0],
Type = parts[1].ToSurlyType(),
Maximum = int.TryParse(parts[2], out numMax) ? numMax : 0
});
}
database.Tables.Last.Value.Schema.AddFirst(new SurlyAttributeSchema
{
Name = "Id",
Type = typeof(int),
Maximum = 3
});
Console.WriteLine();
}
public static SurlyTableResponse GetTable(this SurlyDatabase database, string tableName)
{
var table = database.Tables.SingleOrDefault(x => x.Name == tableName.ToUpper());
if (table != null)
{
return new SurlyTableResponse {
Table = table
}
}
;
var projection = SurlyProjections.GetInstance().Projections
.SingleOrDefault(x => x.ProjectionName == tableName.ToUpper());
if (projection != null)
{
return(new SurlyTableResponse
{
Table = new SurlyTable
{
Name = projection.ProjectionName,
Schema = projection.AttributeNames,
Tuples = projection.Tuples,
},
IsProjection = true,
HideIndexes = projection.HideIndex
});
}
WriteLine($"\n\t{tableName.ToUpper()} was not found.", Red);
return(null);
}
public static void ExecuteQuery(this SurlyDatabase database, string line)
{
Set(Cyan);
if (line.ToUpper().Contains("PROJECT"))
{
database.Project(line);
return;
}
if (line.ToUpper().Contains("VIEW"))
{
database.CreateView(line);
return;
}
if (line.ToUpper().Contains("SELECT"))
{
database.Select(line);
return;
}
if (line.ToUpper().Contains("JOIN"))
{
database.Join(line);
return;
}
if (line.ToUpper().Contains("PRINT CATALOG"))
{
database.PrintCatalog();
return;
}
var steps = line.Split(' ').ToList();
switch (steps[0].ToUpper())
{
case "RELATION":
database.CreateTable(steps[1].ToUpper(), line);
break;
case "INSERT":
database.AddTuples(steps[1].ToUpper(), line);
break;
case "PRINT":
database.Print(line);
break;
case "DELETE":
database.Delete(steps[1], line);
break;
case "DESTROY":
database.DestroyTable(steps[1].Replace(";", "").ToUpper(), line);
break;
default:
WriteLine($"\n\tUnknown command: {steps[0].ToUpper()}, please see help for recognized commands", Red);
break;
}
}
public static void PrintTables(this SurlyDatabase database, IList <SurlyTableResponse> tables)
{
foreach (var response in tables)
{
if (response == null)
{
continue;
}
WriteLine($"\n\t{response.Table.Name}");
Console.WriteLine();
Console.Write($" {(response.IsProjection ? Id.PadRight(8) : "")}");
var widthReferences = new List <int>();
foreach (var schema in response.Table.Schema)
{
var tableWidth = Math.Max(schema.Maximum + 2, schema.Name.Length + 2);
Console.Write($"{schema.Name.PadRight(tableWidth)}");
widthReferences.Add(tableWidth);
}
var count = 1;
WriteLine("\n" + string.Empty.PadRight(widthReferences.Sum() + 10, '='), Green);
Set(Yellow);
foreach (var tableTuple in response.Table.Tuples)
{
var index = 0;
Console.Write($" {(response.IsProjection ? count.ToString().PadRight(8) : "")}");
foreach (var attribute in tableTuple)
{
try
{
Console.Write($"{attribute.Value.ToString().PadRight(widthReferences[index])}");
index++;
}
catch (Exception)
{
Console.Write($"{attribute.Value.ToString().PadRight(8)}");
return;
}
}
Console.WriteLine();
count++;
}
Console.WriteLine();
}
}
public static void Delete(this SurlyDatabase database, string tableName, string line)
{
if (line.ToUpper().Contains("WHERE"))
{
database.DeleteTableWithConditions(tableName, line);
return;
}
database.DeleteTable(tableName.Replace(";", "").ToUpper(), line);
}
public static SurlyProjection Validate(SurlyDatabase database, SurlyProjection projection)
{
var tableResponse = database.GetTable(projection.TableName);
if (tableResponse == null)
{
return(null);
}
var validAttributes =
projection.AttributeNames.All(attributeName => tableResponse.Table.Schema.Any(x => x.Name == attributeName.Name));
if (!validAttributes)
{
WriteLine("\tColumn name(s) not found.\n", Red);
return(null);
}
bool existingProjection;
do
{
existingProjection = Projections.Projections.Any(x => x.ProjectionName == projection.ProjectionName);
//Rename projection
if (!existingProjection)
{
continue;
}
Write(
$"\nProjection {projection.ProjectionName.ToUpper()} already exists, enter new projection name: ",
Yellow);
string newProjectionName;
do
{
newProjectionName = Console.ReadLine();
if (string.IsNullOrWhiteSpace(newProjectionName))
{
Write("Please enter a valid projection name: ", Red);
}
} while (string.IsNullOrWhiteSpace(newProjectionName));
projection.ProjectionName = newProjectionName.ToUpper();
existingProjection = Projections.Projections.Any(x => x.ProjectionName == projection.ProjectionName);
} while (existingProjection);
return(projection);
}
public static void DeleteTable(this SurlyDatabase database, string tableName, string line)
{
var tableResponse = database.GetTable(tableName);
if (tableResponse.Table == null)
{
return;
}
tableResponse.Table.Tuples.Clear();
WriteLine($"\n\tDeleted {tableName.ToUpper()}", Green);
}
public static void PrintDatabase(this SurlyDatabase database)
{
const string id = "Id";
WriteLine("\n\n\t*** FULL DATABASE ***\n");
if (database.Tables.Count == 0)
{
WriteLine("\t<--EMPTY-->\n\n", Red);
}
foreach (var table in database.Tables)
{
WriteLine($"\n\tTable: {table.Name}\n");
Console.Write($" ");//{id.PadRight(8)}");
var widthReferences = new List <int>();
foreach (var schema in table.Schema)
{
var tableWidth = Math.Max(schema.Maximum + 2, schema.Name.Length + 2);
Console.Write($"{schema.Name.PadRight(tableWidth)}");
widthReferences.Add(tableWidth);
}
var count = 1;
WriteLine("\n" + string.Empty.PadRight(100, '='), Green);
Set(Yellow);
foreach (var tableTuple in table.Tuples)
{
var index = 0;
Console.Write($" ");//{count.ToString().PadRight(8)}");
foreach (var attribute in tableTuple)
{
Console.Write($"{attribute.Value.ToString().PadRight(widthReferences[index])}");
index++;
}
Console.WriteLine();
count++;
}
Console.WriteLine();
}
}
public static void DeleteTableWithConditions(this SurlyDatabase database, string tableName, string line)
{
var tableResponse = database.GetTable(tableName);
if (tableResponse.Table == null)
{
return;
}
string[] conditions = null;
try
{
conditions = new Regex("where (.+);", RegexOptions.IgnoreCase)
.Match(line)
.Groups[1]
.Captures[0]
.ToString()
.ToUpper()
.Split(' ');
}
catch (Exception)
{
WriteLine("Invalid syntax, please see help.", Red);
}
if (conditions == null)
{
return;
}
var success = false;
tableResponse.Table.Tuples.ToList().ForEach(tableRow =>
{
var match = OperatorHelper.Chain(tableRow, true, conditions, 0);
if (match)
{
tableResponse.Table.Tuples.Remove(tableRow);
success = true;
}
});
if (success)
{
WriteLine("\n\tSuccess", Green);
}
else
{
WriteLine("No rows affected.");
}
}
public static void DestroyTable(this SurlyDatabase database, string tableName, string line)
{
var tableResponse = database.GetTable(tableName);
if (tableResponse.IsProjection)
{
var projection = SurlyProjections.GetInstance().Projections.Single(x => x.ProjectionName == tableName);
SurlyProjections.GetInstance().Projections.Remove(projection);
}
else
{
database.Tables.Remove(tableResponse.Table);
}
WriteLine($"\n\tDestroyed {tableName.ToUpper()}", Green);
}
public static void Print(this SurlyDatabase database, string line)
{
var query = line
.Replace(",", "")
.Replace(";", "")
.Split(' ')
.ToList();
query.RemoveAt(0);
var tablesQueryResponse = query
.Select(database.GetTable)
.ToList();
database.PrintTables(tablesQueryResponse);
}
public static void AddTuples(this SurlyDatabase database, string tableName, string line)
{
if (database.Tables.All(x => x.Name != tableName))
{
WriteLine($"\n\tTable {tableName.ToUpper()} was not found.", Red);
return;
}
var tuples = string.Format(new SurlyFormatter(), "{0:insert}", line).SplitValues();
var table = database.Tables.Single(x => x.Name == tableName);
var schema = table.Schema.ToArray();
var newTuple = new LinkedList <SurlyAttribute>();
newTuple.AddLast(new SurlyAttribute
{
Name = "Id",
Value = table.Tuples.Count + 1
});
for (var i = 0; i < schema.Length; i++)
{
if (schema[i].Name == "Id")
{
continue;
}
newTuple.AddLast(new SurlyAttribute
{
Value = tuples[i - 1].To(schema[i].Type, schema[i].Maximum),
Name = schema[i].Name
});
}
if (newTuple.Count <= 0)
{
return;
}
table.Tuples.AddLast(newTuple);
WriteLine($"\tRow added to {tableName.ToUpper()}", Green);
}
public static void Join(this SurlyDatabase database, string query)
{
var projection = CreateJoinProjection(database, query);
if (projection == null)
{
WriteLine("\n\tError adding projection", Red);
return;
}
if (ProjectionsContainer.Projections.Any(x => x.ProjectionName == projection.ProjectionName))
{
WriteLine(
$"\n\tProjection {projection.ProjectionName.ToUpper()} already exists, please try a different name.",
Red);
return;
}
ProjectionsContainer.Projections.AddLast(projection);
WriteLine($"\n\t{projection.ProjectionName.ToUpper()} build successful", Green);
}
public static bool ValidTuple(this SurlyDatabase database, string tableName, string[] parts)
{
if (parts.Any(string.IsNullOrWhiteSpace))
{
WriteLine("Invalid syntax in schema definition", Red);
return(false);
}
var table = database.Tables.SingleOrDefault(x => x.Name == tableName);
if (table != null && table.Schema
.Any(x => x.Name == parts?[0]))
{
WriteLine($"{parts[0]} already exists. Please select a different attribute name.", Red);
return(false);
}
try
{
if (parts[1].ToSurlyType() == null)
{
WriteLine($"{parts[1]} is not a recognized type.", Red);
return(false);
}
}
catch (Exception)
{
if (parts.Length < 2)
{
WriteLine("Invalid syntax, please reference Help section for correct syntax", Red);
return(false);
}
WriteLine($"{parts[1]} is not a recognized type.", Red);
return(false);
}
return(true);
}
public static void PrintCatalog(this SurlyDatabase database)
{
const string idHeader = "Id";
const string nameHeader = "Name";
const string typeHeader = "Type";
const string maxHeader = "Maximum";
WriteLine("\n\n\t*** CURRENT CATALOG ***\n");
if (database.Tables.Count == 0)
{
WriteLine("\t<--EMPTY-->\n\n", Red);
}
foreach (var table in database.Tables)
{
var count = 1;
WriteLine($"\n\tTable: {table.Name}", Blue);
Console.WriteLine();
WriteLine(
$" {idHeader.PadRight(5)}{nameHeader.PadRight(20)}{typeHeader.PadRight(20)}{maxHeader}",
Yellow);
WriteLine($"{string.Empty.PadRight(100, '=')}");
foreach (var schema in table.Schema)
{
WriteLine(
$" {count.ToString().PadRight(5)}{schema.Name.PadRight(20)}{schema.Type.Name.PadRight(20)}{schema.Maximum}",
Green);
count++;
}
}
}
public static void Select(this SurlyDatabase database, string query)
{
_resultSet = new LinkedList <LinkedList <SurlyAttribute> >();
string tableName, conditions, projectionName = null;
var printProjection = false;
try
{
try
{
projectionName = new Regex("(\\w+) = select", RegexOptions.IgnoreCase)
.Match(query)
.Groups[1]
.Captures[0]
.ToString()
.ToUpper();
}
catch (Exception)
{
printProjection = true;
}
tableName = new Regex("select (\\w+) where", RegexOptions.IgnoreCase)
.Match(query)
.Groups[1]
.Captures[0]
.ToString()
.ToUpper();
conditions = new Regex("where (.+);", RegexOptions.IgnoreCase)
.Match(query)
.Groups[1]
.Captures[0]
.ToString()
.ToUpper();
}
catch (Exception)
{
WriteLine("Invalid SELECT syntax, please see help", Red);
return;
}
var tableResponse = database.GetTable(tableName);
if (tableResponse.Table == null)
{
WriteLine($"{tableName.ToUpper()} not found.", Red);
return;
}
if (!printProjection &&
SurlyProjections.GetInstance().Projections.Any(x => x.ProjectionName.ToUpper() == projectionName?.ToUpper()))
{
WriteLine($"\n\t{projectionName?.ToUpper()} already exists, please choose a different name", Red);
return;
}
var conditionSteps = conditions.Split(' ').ToList();
tableResponse.Table.Tuples.ToList().ForEach(tableRow =>
{
var valid = OperatorHelper.Chain(tableRow, true, conditionSteps.ToArray(), 0);
if (valid)
{
var trimmedRow = new LinkedList <SurlyAttribute>(tableRow);
var rowId = trimmedRow.SingleOrDefault(x => x.Name == "Id");
trimmedRow.Remove(rowId);
_resultSet.AddLast(trimmedRow);
}
});
if (_resultSet.Count == 0)
{
WriteLine("\n\tQuery yielded no results.", Yellow);
return;
}
var schema = new LinkedList <SurlyAttributeSchema>(tableResponse.Table.Schema);
var id = schema.SingleOrDefault(x => x.Name == "Id");
schema.Remove(id);
if (printProjection)
{
var response = new SurlyTableResponse
{
Table = new SurlyTable
{
Schema = schema,
Name = "Results",
Tuples = _resultSet
},
//HideIndexes = true
};
database.PrintTables(new List <SurlyTableResponse> {
response
});
return;
}
SurlyProjections.GetInstance().Projections.AddLast(new SurlyProjection
{
AttributeNames = schema,
HideIndex = true,
ProjectionName = projectionName,
TableName = tableResponse.Table.Name,
Tuples = _resultSet
});
WriteLine($"\n\t{projectionName.ToUpper()} build successful.", Green);
}
//In Development...
public static void CreateView(this SurlyDatabase database, string query)
{
WriteLine("The VIEW command is still in development, please try again later.", Yellow);
return;
//If the syntax is wrong, the regex with throw an exception
try
{
var projectionNameRegex = new Regex("(\\w+) =").Match(query);
var projectionName = projectionNameRegex
.Groups[1]
.Captures[0]
.ToString()
.ToUpper()
.Trim();
var attributeNamesRegex = new Regex("view (.+) from", RegexOptions.IgnoreCase)
.Match(query)
.Groups[1]
.Captures[0]
.ToString()
.ToUpper()
.Split(',')
.ToList();
var attributeNames = new LinkedList <string>();
foreach (var name in attributeNamesRegex)
{
attributeNames.AddLast(name.Trim());
}
var tableName = new Regex("(\\w+);", RegexOptions.IgnoreCase)
.Match(query)
.Groups[1]
.Captures[0]
.ToString()
.ToUpper()
.Trim();
//Verify tables/attributes exist
var projection = new SurlyProjection
{
ProjectionName = projectionName,
TableName = tableName,
// AttributeNames = attributeNames
};
projection = Validate(database, projection);
if (projection == null)
{
return;
}
//Add projection definition
Projections.Projections.AddLast(projection);
WriteLine($"\n\tNew view added: {projection.ProjectionName}", Green);
}
catch (Exception)
{
Console.WriteLine("Invalid syntax for VIEW, see help.");
}
}
public static SurlyProjection CreateJoinProjection(SurlyDatabase database, string query)
{
string projectionName;
List <string> tableNamesRegex;
string[] joinCondition;
try
{
var projectionNameRegex = new Regex("(\\w+) =").Match(query);
projectionName = projectionNameRegex
.Groups[1]
.Captures[0]
.ToString()
.ToUpper()
.Trim();
tableNamesRegex = new Regex("join (.+) on", RegexOptions.IgnoreCase)
.Match(query)
.Groups[1]
.Captures[0]
.ToString()
.ToUpper()
.Split(',')
.ToList();
joinCondition = new Regex(" on (.+);", RegexOptions.IgnoreCase)
.Match(query)
.Groups[1]
.Captures[0]
.ToString()
.ToUpper()
.Trim()
.Split(' ');
}
catch (Exception)
{
Console.WriteLine("\n\tInvalid syntax for JOIN, see help.");
return(null);
}
var tableNames = new LinkedList <string>();
var tables = new List <SurlyTable>();
var attributeNames = new LinkedList <SurlyAttributeSchema>();
var resultSet = new LinkedList <LinkedList <SurlyAttribute> >();
foreach (var tableName in tableNamesRegex)
{
tableNames.AddLast(tableName.Trim());
var tempTableResponse = database.GetTable(tableName.Trim());
if (tempTableResponse.Table == null)
{
WriteLine($"{tableName.Trim()} not found", Red);
return(null);
}
attributeNames.Combine(new LinkedList <SurlyAttributeSchema>(tempTableResponse.Table.Schema));
tables.Add(tempTableResponse.Table);
}
var leftTableRows = new LinkedList <LinkedList <SurlyAttribute> >(tables[0].Tuples).ToList();
var rightTableRows = new LinkedList <LinkedList <SurlyAttribute> >(tables[1].Tuples);
leftTableRows.ForEach(
row => resultSet = resultSet.Combine(row.ApplyCondition(rightTableRows, joinCondition)));
var projection = new SurlyProjection
{
ProjectionName = projectionName.ToUpper(),
TableName = projectionName.ToUpper(),
AttributeNames = attributeNames,
Tuples = resultSet,
HideIndex = true
};
return(projection);
}
public static void Project(this SurlyDatabase database, string query)
{
try
{
var projectionNameRegex = new Regex("(\\w+) =").Match(query);
var projectionName = projectionNameRegex
.Groups[1]
.Captures[0]
.ToString()
.ToUpper()
.Trim();
var attributeNamesRegex = new Regex("project (.+) from", RegexOptions.IgnoreCase)
.Match(query)
.Groups[1]
.Captures[0]
.ToString()
.ToUpper()
.Split(',')
.ToList();
var attributeNames = new LinkedList <string>();
foreach (var name in attributeNamesRegex)
{
attributeNames.AddLast(name.Trim());
}
var tableName = new Regex("(\\w+);", RegexOptions.IgnoreCase)
.Match(query)
.Groups[1]
.Captures[0]
.ToString()
.ToUpper()
.Trim();
//Verify tables/attributes exist
var projection = new SurlyProjection
{
ProjectionName = projectionName,
TableName = tableName,
AttributeNames = new LinkedList <SurlyAttributeSchema>(),
Tuples = new LinkedList <LinkedList <SurlyAttribute> >()
};
projection = Validate(database, projection);
//Clone selected data to new projection
var schemaDefinition = new LinkedList <SurlyAttributeSchema>();
var castedList = new LinkedList <LinkedList <SurlyAttribute> >();
var tableResponse = database.GetTable(tableName);
foreach (var attributeName in attributeNames)
{
var selectedTuplesSchemata = tableResponse.Table.Schema.Single(x => x.Name == attributeName);
schemaDefinition.AddLast(new SurlyAttributeSchema
{
Maximum = selectedTuplesSchemata.Maximum,
Name = selectedTuplesSchemata.Name
});
}
var selectedTuples = tableResponse.Table.Tuples
.Select(x => x
.Where(y => attributeNames
.Any(a => a == y.Name)));
foreach (var tupleList in selectedTuples)
{
var list = new LinkedList <SurlyAttribute>();
foreach (var attribute in tupleList)
{
list.AddLast(attribute);
}
castedList.AddLast(list);
}
projection.AttributeNames = schemaDefinition;
projection.Tuples = castedList;
//Add projection
Projections.Projections.AddLast(projection);
WriteLine($"\n\tNew projection added: {projection.ProjectionName}", Green);
}
catch (Exception)
{
Console.WriteLine("Invalid syntax for PROJECT, see help.");
}
}