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