//public static string SelectDetections(bool useCountForm) //{ // string phrase = useCountForm // //? Sql.SelectCountStarFrom + Sql.OpenParenthesis + Sql.SelectStar // ? Sql.SelectCountStarFrom + Sql.OpenParenthesis + Sql.SelectDistinct + Constant.DBTables.FileData + Sql.DotStar // : Sql.Select + Constant.DBTables.FileData + Sql.DotStar; // return phrase + Sql.From + Constant.DBTables.Detections + Sql.InnerJoin + Constant.DBTables.FileData + // Sql.On + Constant.DBTables.FileData + Sql.Dot + Constant.DatabaseColumn.ID + Sql.Equal + Constant.DBTables.Detections + "." + Constant.DetectionColumns.ImageID; //} /// <summary> /// /// </summary> /// <param name="useCountForm"></param> /// <returns> /// Count Form: Select COUNT ( * ) FROM (SELECT DISTINCT DataTable.* FROM Classifications INNER JOIN DataTable ON DataTable.Id = Detections.Id INNER JOIN Detections ON Detections.detectionID = Classifications.detectionID /// Star Form: SELECT DISTINCT DataTable.* FROM Classifications INNER JOIN DataTable ON DataTable.Id = Detections.Id INNER JOIN Detections ON Detections.detectionID = Classifications.detectionID /// One Form SELECT ONE FROM (SELECT DISTINCT DataTable.* FROM Classifications INNER JOIN DataTable ON DataTable.Id = Detections.Id INNER JOIN Detections ON Detections.detectionID = Classifications.detectionID /// /// </returns> public static string SelectClassifications(SelectTypesEnum selectType) { string phrase = String.Empty; if (selectType == SelectTypesEnum.Count) { phrase = Sql.SelectCountStarFrom + Sql.OpenParenthesis + Sql.SelectDistinct; } else if (selectType == SelectTypesEnum.Star) { phrase = Sql.SelectDistinct; } else if (selectType == SelectTypesEnum.One) { phrase = Sql.SelectOne + Sql.From + Sql.OpenParenthesis + Sql.SelectDistinct; } phrase += Constant.DBTables.FileData + Sql.DotStar + Sql.From + Constant.DBTables.Classifications + Sql.InnerJoin + Constant.DBTables.FileData + Sql.On + Constant.DBTables.FileData + Sql.Dot + Constant.DatabaseColumn.ID + Sql.Equal + Constant.DBTables.Detections + "." + Constant.DetectionColumns.ImageID; // and now append INNER JOIN Detections ON Detections.detectionID = Classifications.detectionID phrase += Sql.InnerJoin + Constant.DBTables.Detections + Sql.On + Constant.DBTables.Detections + Sql.Dot + Constant.DetectionColumns.DetectionID + Sql.Equal + Constant.DBTables.Classifications + "." + Constant.DetectionColumns.DetectionID; return(phrase); }
/// <summary> /// Sql Phrase - Create partial query to return all missing detections /// </summary> /// <param name="useCountForm">If true, return a SELECT COUNT vs a SELECT from</param> /// <returns> /// Count Form: SELECT COUNT ( DataTable.Id ) FROM DataTable LEFT JOIN Detections ON DataTable.ID = Detections.Id WHERE Detections.Id IS NULL /// Star Form: SELECT DataTable.* FROM DataTable LEFT JOIN Detections ON DataTable.ID = Detections.Id WHERE Detections.Id IS NULL /// One Form: SELECT 1 FROM DataTable LEFT JOIN Detections ON DataTable.ID = Detections.Id WHERE Detections.Id IS NULL public static string SelectMissingDetections(SelectTypesEnum selectType) { string phrase = String.Empty; if (selectType == SelectTypesEnum.Count) { phrase = Sql.SelectCount + Sql.OpenParenthesis + Constant.DBTables.FileData + Sql.Dot + Constant.DatabaseColumn.ID + Sql.CloseParenthesis; } else if (selectType == SelectTypesEnum.Star) { phrase = Sql.Select + Constant.DBTables.FileData + Sql.DotStar; } else if (selectType == SelectTypesEnum.One) { phrase = Sql.SelectOne; } //string phrase = useCountForm // ? Sql.SelectCount + Sql.OpenParenthesis + Constant.DBTables.FileData + Sql.Dot + Constant.DatabaseColumn.ID + Sql.CloseParenthesis // : Sql.Select + Constant.DBTables.FileData + Sql.DotStar; return(phrase + Sql.From + Constant.DBTables.FileData + Sql.LeftJoin + Constant.DBTables.Detections + Sql.On + Constant.DBTables.FileData + Sql.Dot + Constant.DatabaseColumn.ID + Sql.Equal + Constant.DBTables.Detections + Sql.Dot + Constant.DatabaseColumn.ID + Sql.Where + Constant.DBTables.Detections + Sql.Dot + Constant.DatabaseColumn.ID + Sql.IsNull); }
//public static string SelectMissingDetections(bool useCountForm) //{ // string phrase = useCountForm // ? Sql.SelectCount + Sql.OpenParenthesis + Constant.DBTables.FileData + Sql.Dot + Constant.DatabaseColumn.ID + Sql.CloseParenthesis // : Sql.Select + Constant.DBTables.FileData + Sql.DotStar; // return phrase + Sql.From + Constant.DBTables.FileData + // Sql.LeftJoin + Constant.DBTables.Detections + // Sql.On + Constant.DBTables.FileData + Sql.Dot + Constant.DatabaseColumn.ID + // Sql.Equal + Constant.DBTables.Detections + Sql.Dot + Constant.DatabaseColumn.ID + // Sql.Where + Constant.DBTables.Detections + Sql.Dot + Constant.DatabaseColumn.ID + Sql.IsNull; //} /// <summary> /// Sql Phrase - Create partial query to return detections /// </summary> /// <param name="useCountForm">If true, form is SELECT COUNT vs SELECT</param> /// <returns> /// Count Form: SELECT COUNT ( * ) FROM ( SELECT * FROM Detections INNER JOIN DataTable ON DataTable.Id = Detections.Id /// Star Form: SELECT DataTable.* FROM Detections INNER JOIN DataTable ON DataTable.Id = Detections.Id /// One Form: SELECT 1 FROM Detections INNER JOIN DataTable ON DataTable.Id = Detections.Id /// </returns> public static string SelectDetections(SelectTypesEnum selectType) { string phrase = String.Empty; if (selectType == SelectTypesEnum.Count) { phrase = Sql.SelectCountStarFrom + Sql.OpenParenthesis + Sql.SelectDistinct + Constant.DBTables.FileData + Sql.DotStar; } else if (selectType == SelectTypesEnum.Star) { phrase = Sql.Select + Constant.DBTables.FileData + Sql.DotStar; } else if (selectType == SelectTypesEnum.One) { phrase = Sql.SelectOne; } return(phrase + Sql.From + Constant.DBTables.Detections + Sql.InnerJoin + Constant.DBTables.FileData + Sql.On + Constant.DBTables.FileData + Sql.Dot + Constant.DatabaseColumn.ID + Sql.Equal + Constant.DBTables.Detections + "." + Constant.DetectionColumns.ImageID); }