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);
}
//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 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);
}
