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 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 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.");
}
}
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 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);
}