public void TestConstructors() { // Check that we still conform to the original behavior. DaoCriteria sc = new DaoCriteria(); Assert.AreEqual(-1, sc.Start, "Wrong start value."); Assert.AreEqual(-1, sc.Limit, "Wrong limit value."); }
/// <summary> /// Create the data reader. /// </summary> /// <param name="layer">Layer creating it.</param> /// <param name="mapping">Mapping for the class stored in the data store.</param> /// <param name="criteria">Criteria for which instances you want.</param> /// <param name="objects">Iterator over the list of objects.</param> public MemoryDataReader(UnqueryableDaLayer layer, ClassMapping mapping, DaoCriteria criteria, IEnumerator<MemoryObject> objects) : base(layer, mapping, criteria, GetConfig(mapping)) { _objects = objects; PreProcessSorts(); }
/// <summary> /// Used when nesting another criteria. /// </summary> /// <param name="crit">Another whole criteria to be a nested expression. /// May not be null.</param> /// <param name="trueOrNot">True means look for matches (I.E. ==), /// false means look for non-matches (I.E. !=)</param> public CriteriaExpression(DaoCriteria crit, bool trueOrNot) : base(trueOrNot) { if (crit == null) { throw new ArgumentNullException("crit", "Criteria parameter cannot be null."); } NestedCriteria = crit; }
/// <summary> /// Create the data reader. /// </summary> /// <param name="layer">Data access layer that will give us the TextReader we need.</param> /// <param name="mapping">ClassMapping for the type we're returning.</param> /// <param name="criteria">Since there is no way to filter before we read the file, /// the reader checks each row read to see if it matches the /// criteria, if not, it is skipped.</param> public CsvDataReader(CsvDaLayer layer, ClassMapping mapping, DaoCriteria criteria) : base(layer, mapping, criteria, GetConfig(layer, mapping)) { _reader = ((CsvDataReaderConfig) _config).Reader; try { PreProcessSorts(); } catch (Exception e) { // Close the reader on error, since if the constructor throws // it isn't very reasonable to expect anyone else to call Close. if (_reader != null) { layer.DoneWithReader(_reader); } throw new LoggingException("Unable to read from CSV file and process sorts.", e); } }
public void TestSerialize() { DaoCriteria sc = new DaoCriteria(); sc.Orders.Add(new SortOrder("X", SortType.Asc)); sc.Expressions.Add(new EqualExpression("X", "1", true)); try { MemoryStream ms = new MemoryStream(); BinaryFormatter formatter = new BinaryFormatter(); formatter.Serialize(ms, sc); ms.Close(); ms.Dispose(); } catch (Exception ex) { Assert.Fail(ex.Message); } }
public void TestOrders() { DaoCriteria sc = new DaoCriteria(); Assert.AreEqual(0, sc.Orders.Count, "Didn't start with none."); sc.Orders.Add(new SortOrder("X", SortType.Asc)); Assert.AreEqual(1, sc.Orders.Count, "Didn't add the asc one."); sc.Orders.Add(new SortOrder("A", SortType.Desc)); Assert.AreEqual(2, sc.Orders.Count, "Didn't add the desc one."); sc.Orders.Add(new SortOrder("Z", SortType.Computed)); Assert.AreEqual(3, sc.Orders.Count, "Didn't add the computed one."); Assert.AreEqual("X", sc.Orders[0].Property, "Wrong order first"); Assert.AreEqual("A", sc.Orders[1].Property, "Wrong order second"); Assert.AreEqual("Z", sc.Orders[2].Property, "Wrong order third"); Assert.AreEqual(SortType.Asc, sc.Orders[0].Direction, "Wrong sort dir first"); Assert.AreEqual(SortType.Desc, sc.Orders[1].Direction, "Wrong sort dir second"); Assert.AreEqual(SortType.Computed, sc.Orders[2].Direction, "Wrong sort dir third"); sc.Orders.Clear(); Assert.AreEqual(0, sc.Orders.Count, "Didn't clear."); sc.Orders.Add(new SortOrder("X", SortType.Asc)); Assert.AreEqual(1, sc.Orders.Count, "Didn't add one after clearing."); }
/// <exclude/> public override IDaQuery CreateQuery(ClassMapping mapping, DaoCriteria crit) { // Overridden to handle IGeometry types, and to always fully qualify column names. SqlDaQuery retVal = _sqlQueryCache.Get(); retVal.Sql.Append("SELECT "); foreach (string attName in mapping.AllDataColsByObjAttrs.Keys) { string colName = mapping.AllDataColsByObjAttrs[attName]; Type colType = mapping.DataColTypesByObjAttr[attName]; if (typeof(IGeometry).IsAssignableFrom(colType)) { retVal.Sql.Append("ST_AsEWKT("); retVal.Sql.Append(colName); retVal.Sql.Append(") AS "); retVal.Sql.Append(colName); } else { if (!colName.Contains(".")) { retVal.Sql.Append(mapping.Table); retVal.Sql.Append("."); } retVal.Sql.Append(colName); } retVal.Sql.Append(", "); } retVal.Sql.Remove(retVal.Sql.Length - 2, 2); retVal.Sql.Append(" FROM "); retVal.Sql.Append(mapping.Table); ExpressionsToQuery(retVal, crit, mapping); OrdersToQuery(retVal, crit, mapping); // Don't return the query, we'll do that in DisposeOfQuery. return retVal; }
/// <summary> /// Takes a DaoCriteria, converts it to a " WHERE ..." chunk of SQL. /// The SQL will begin with a space if non-empty. /// </summary> /// <param name="queryToAddTo">Query we're adding the expression to.</param> /// <param name="crit">Serializable critera to get the expressions from.</param> /// <param name="mapping">Class mapping for the class we're dealing with.</param> public virtual void ExpressionsToQuery(SqlDaQuery queryToAddTo, DaoCriteria crit, ClassMapping mapping) { if (crit != null) { if (crit.Expressions.Count > 0) { string colPrefix = _fullyQualifyColumnNames ? mapping.Table + "." : null; queryToAddTo.Sql.Append(" WHERE "); ExpressionListToQuery(queryToAddTo, crit.BoolType, crit.Expressions, mapping, colPrefix); } } }
/// <exclude/> public override int GetCount(ITransaction transaction, ClassMapping mapping, DaoCriteria crit) { SqlDaQuery query = _sqlQueryCache.Get(); query.Sql.Append("SELECT COUNT(*) FROM "); query.Sql.Append(mapping.Table); ExpressionsToQuery(query, crit, mapping); int retVal = SqlConnectionUtilities.XSafeIntQuery(_connDesc, (SqlTransaction)transaction, query.Sql.ToString(), query.Params); DisposeOfQuery(query); return retVal; }
/// <summary> /// Gets a count of records for the given criteria. /// </summary> /// <param name="transaction">The transaction to do this as part of.</param> /// <param name="mapping">The mapping of the table for which to build the query string.</param> /// <param name="crit">The criteria to use for "where" comparisons.</param> /// <returns>The number of results found that matched the criteria.</returns> public abstract int GetCount(ITransaction transaction, ClassMapping mapping, DaoCriteria crit);
/// <summary> /// Builds the query based on a serializable criteria. /// </summary> /// <param name="crit">The criteria to use for "where" comparisons.</param> /// <param name="mapping">The mapping of the table for which to build the query string.</param> /// <returns>A query that can be run by ExecureQuery.</returns> public override IDaQuery CreateQuery(ClassMapping mapping, DaoCriteria crit) { SqlDaQuery retVal = _sqlQueryCache.Get(); retVal.Sql.Append("SELECT * FROM "); retVal.Sql.Append(mapping.Table); ExpressionsToQuery(retVal, crit, mapping); OrdersToQuery(retVal, crit, mapping); // Don't return the objects, we'll do that in DisposeOfQuery. return retVal; }
/// <summary> /// Makes this DaoCriteria into a copy of the other one. Any existing /// orders, expressions, etc. on this one are lost. /// </summary> /// <param name="other">Criteria to copy everything from.</param> public void CopyFrom(DaoCriteria other) { if (other == null) { Clear(); } else { BoolType = other.BoolType; Expressions.Clear(); Expressions.AddRange(other.Expressions); Orders.Clear(); Orders.AddRange(other.Orders); Start = other.Start; Limit = other.Limit; } }
/// <summary> /// Gets a count of records for the given criteria, /// aggregated by the given grouping expressions. This matches "GROUP BY" behavior /// in SQL. /// </summary> /// <param name="transaction">The transaction to do this as part of.</param> /// <param name="mapping">The mapping of the table for which to build the query string.</param> /// <param name="crit">The criteria to use for "where" comparisons.</param> /// <param name="groupExpressions">The fields/expressions to aggregate on when counting.</param> /// <returns>The number of objects that match the criteria, plus the values of those objects /// for the fields that were aggregated on.</returns> public override List<GroupCountResult> GetCount(ITransaction transaction, ClassMapping mapping, DaoCriteria crit, ICollection<AbstractGroupExpression> groupExpressions) { throw new NotImplementedException(); }
public void TestSubExpressions() { DaoCriteria sc = new DaoCriteria(); Assert.AreEqual(0, new List<IExpression>(sc.Expressions).Count, "Didn't start with none."); sc.Expressions.Add(new CriteriaExpression(new DaoCriteria(), true)); Assert.AreEqual(1, new List<IExpression>(sc.Expressions).Count, "Added a blank sub-expr."); sc.Clear(); DaoCriteria sub1 = new DaoCriteria(); sub1.Expressions.Add(new EqualExpression("x", 5, true)); sub1.Expressions.Add(new BetweenExpression("y", 1, 4, true)); Assert.AreEqual(2, new List<IExpression>(sub1.Expressions).Count, "Sub-expr didn't have 2 exprs."); sc.Expressions.Add(new CriteriaExpression(sub1, true)); Assert.AreEqual(1, new List<IExpression>(sc.Expressions).Count, "Should be 1 sub-expr."); }
/// <exclude/> public override int Update(ITransaction transaction, ClassMapping mapping, DaoCriteria crit, IDictionary<string, object> propValues) { // This must be run BEFORE PreProcessPropertyValues, since PreProcessPropertyValues can mutate the collection var valueStrings = GetValueStrings(mapping.Table, propValues); PreProcessPropertyValues(mapping.Table, propValues); SqlDaQuery query = _sqlQueryCache.Get(); try { // Make the "update table set blah = something" part. query.Sql.Append("UPDATE "); query.Sql.Append(mapping.Table); query.Sql.Append(" SET "); bool first = true; foreach (string key in propValues.Keys) { if (!first) { query.Sql.Append(", "); } else { first = false; } query.Sql.Append(key); if (valueStrings != null && valueStrings.ContainsKey(key)) { query.Sql.Append(" = " + valueStrings[key]); } else { query.Sql.Append(" = ?"); } query.Params.Add(propValues[key]); } // Add the "where blah, blah, blah" part. ExpressionsToQuery(query, crit, mapping); return SqlConnectionUtilities.XSafeCommand(_connDesc, (SqlTransaction)transaction, query.Sql.ToString(), query.Params); } finally { _sqlQueryCache.Return(query); } }
public void TestGetIntersects() { DaoCriteria crit = new DaoCriteria(); Coordinate[] coords = new Coordinate[5]; coords[0] = new Coordinate(100, 100); coords[1] = new Coordinate(200, 100); coords[2] = new Coordinate(200, 150); coords[3] = new Coordinate(100, 150); coords[4] = new Coordinate(100, 100); Geometry poly = new Polygon(new LinearRing(coords)); crit.Expressions.Add(new IntersectsExpression("Shape", poly)); IList<PointClass> points = _pointDao.Get(crit); Assert.AreEqual(6, points.Count, "Wrong number of points."); IList<LineClass> lines = _lineDao.Get(crit); Assert.AreEqual(2, lines.Count, "Wrong number of lines."); IList<PolyClass> polys = _polyDao.Get(crit); Assert.AreEqual(2, polys.Count, "Wrong number of polygons."); }
public void TestGetMultipleCriteria() { DaoCriteria crit = new DaoCriteria(BooleanOperator.Or); crit.Expressions.Add(new EqualExpression("Double", 10000)); crit.Expressions.Add(new GreaterExpression("Int", 300)); IList<PointClass> points = _pointDao.Get(crit); Assert.AreEqual(5, points.Count, "Wrong number of points."); IList<LineClass> lines = _lineDao.Get(crit); Assert.AreEqual(4, lines.Count, "Wrong number of lines."); IList<PolyClass> polys = _polyDao.Get(crit); Assert.AreEqual(4, polys.Count, "Wrong number of polygons."); }
/// <summary> /// Clears the contents of the query, allowing the object to be reused. /// </summary> public void Clear() { Criteria = null; }
/// <summary> /// Deletes a data object record using the mapping and criteria for what's deleted. /// </summary> /// <param name="transaction">Should be null, transactions are not supported.</param> /// <param name="crit">Criteria for deletion. NOTE: Only the expressions are observed, /// other things (like "order" or start / limit) are ignored. /// WARNING: A null or empty (no expression) criteria will /// delete ALL records!</param> /// <param name="mapping">The mapping of the table from which to delete.</param> /// <returns>The number of records affected.</returns> public override int Delete(ITransaction transaction, ClassMapping mapping, DaoCriteria crit) { switch (_connDesc.Type) { case CsvConnectionType.Directory: case CsvConnectionType.FileName: // These are OK. break; default: throw new LoggingException("Connection does not support deleting: " + _connDesc); } // No way to selectively delete text from a text file, so instead we copy all // the rows that don't match the criteria into a new file. string existingFile = GetFileName(mapping); string newFile = existingFile + ".new"; DaoCriteria inverseCrit = new DaoCriteria(); foreach (IExpression expr in crit.Expressions) { inverseCrit.Expressions.Add(expr.Invert()); } CsvDataReader reader = new CsvDataReader(this, mapping, inverseCrit); try { TextWriter newWriter = new StreamWriter(newFile, false); try { newWriter.WriteLine(MakeHeaderRow(mapping)); int numCols = reader.FieldCount; while (reader.Read()) { for (int x = 0; x < numCols; x++) { if (x > 0) { newWriter.Write(","); } newWriter.Write(QuoteValue(reader.GetValue(x))); } newWriter.WriteLine(); } } finally { newWriter.Close(); } } finally { reader.Close(); } // Now move the old file out of the way and replace it with the new one. File.Replace(newFile, existingFile, existingFile + ".old", true); return FastDAO<object>.UNKNOWN_NUM_ROWS; }
/// <summary> /// Updates a data object record using the "table" and a list of column/value pairs. /// </summary> /// <param name="transaction">Should be null, transactions are not supported.</param> /// <param name="mapping">The mapping of the table or other data container we're dealing with.</param> /// <param name="crit">All records matching this criteria will be updated per the dictionary of /// values.</param> /// <param name="propValues">A dictionary of column/value pairs for all non-ID columns to be updated.</param> /// <returns>The number of records affected.</returns> public override int Update(ITransaction transaction, ClassMapping mapping, DaoCriteria crit, IDictionary<string, object> propValues) { switch (_connDesc.Type) { case CsvConnectionType.Directory: case CsvConnectionType.FileName: // These are OK. break; default: throw new LoggingException("Connection does not support updating: " + _connDesc); } // No way to selectively update text from a text file, so instead we first copy all // the rows that don't match the criteria into a new file. string existingFile = GetFileName(mapping); string newFile = existingFile + ".new"; DaoCriteria inverseCrit = new DaoCriteria(); foreach (IExpression expr in crit.Expressions) { inverseCrit.Expressions.Add(expr.Invert()); } TextWriter newWriter = new StreamWriter(newFile, false); int rowsUpdated = 0; try { newWriter.WriteLine(MakeHeaderRow(mapping)); // Copy the rows that don't match... CsvDataReader reader = new CsvDataReader(this, mapping, inverseCrit); try { int numCols = reader.FieldCount; while (reader.Read()) { for (int x = 0; x < numCols; x++) { if (x > 0) { newWriter.Write(","); } newWriter.Write(QuoteValue(reader.GetValue(x))); } newWriter.WriteLine(); } } finally { reader.Close(); } // Copy (modified) the rows that do match... reader = new CsvDataReader(this, mapping, crit); try { IDictionary<int, object> replacements = new CheckedDictionary<int, object>(); foreach (KeyValuePair<string, object> kvp in propValues) { replacements[reader.GetColumnIndex(kvp.Key)] = kvp.Value; } int numCols = reader.FieldCount; while (reader.Read()) { rowsUpdated++; for (int x = 0; x < numCols; x++) { if (x > 0) { newWriter.Write(","); } // Use the updated value if one was provided. object val = replacements.ContainsKey(x) ? replacements[x] : reader.GetValue(x); newWriter.Write(QuoteValue(val)); } newWriter.WriteLine(); } } finally { reader.Close(); } } finally { newWriter.Close(); } // Now move the old file out of the way and replace it with the new one. File.Replace(newFile, existingFile, existingFile + ".old", true); return rowsUpdated; }
/// <exclude/> public override List<GroupCountResult> GetCount(ITransaction transaction, ClassMapping mapping, DaoCriteria crit, ICollection<AbstractGroupExpression> groupExpressions) { SqlDaQuery query = _sqlQueryCache.Get(); query.Sql.Append("SELECT COUNT(*) "); if (_connDesc.NeedAsForColumnAliases()) { query.Sql.Append("AS "); } query.Sql.Append(_connDesc.ColumnAliasPrefix()). Append(COUNT_COL_ALIAS).Append(_connDesc.ColumnAliasSuffix()); GroupBysToStartOfQuery(query, groupExpressions, mapping); query.Sql.Append(" FROM "); query.Sql.Append(mapping.Table); ExpressionsToQuery(query, crit, mapping); GroupBysToEndOfQuery(query, groupExpressions, mapping); OrdersToQuery(query, crit, mapping); Hashtable parameters = DbCaches.Hashtables.Get(); parameters["groupBys"] = groupExpressions; parameters["mapping"] = mapping; SqlConnectionUtilities.XSafeQuery(_connDesc, (SqlTransaction)transaction, query.Sql.ToString(), query.Params, ReadGroupByCount, parameters); List<GroupCountResult> retVal = (List<GroupCountResult>) parameters["results"]; DisposeOfQuery(query); DbCaches.Hashtables.Return(parameters); return retVal; }
/// <summary> /// Used when nesting another criteria. /// </summary> /// <param name="crit">Another whole criteria to be a nested expression. /// May not be null.</param> public CriteriaExpression(DaoCriteria crit) : this(crit, true) { }
/// <summary> /// Takes a DaoCriteria, converts it to an " ORDER BY ..." chunk of SQL. /// The SQL will begin with a space if non-empty. /// </summary> public virtual void OrdersToQuery(SqlDaQuery queryToAddTo, DaoCriteria crit, ClassMapping mapping) { if (crit != null) { if (crit.Orders.Count > 0) { queryToAddTo.Sql.Append(" ORDER BY "); OrderListToSql(queryToAddTo.Sql, crit, mapping); } } }
/// <summary> /// Updates a data object record using the "table" and a list of column/value pairs. /// </summary> /// <param name="transaction">The transaction to do this as part of.</param> /// <param name="mapping">The mapping of the table or other data container we're dealing with.</param> /// <param name="crit">All records matching this criteria will be updated per the dictionary of /// values.</param> /// <param name="propValues">A dictionary of column/value pairs for all non-ID columns to be updated.</param> /// <returns>The number of records affected.</returns> public abstract int Update(ITransaction transaction, ClassMapping mapping, DaoCriteria crit, IDictionary<string, object> propValues);
/// <summary> /// Converts the list of SortOrders from this criteria into SQL, and appends to the /// given string builder. /// </summary> protected void OrderListToSql(StringBuilder orderClauseToAddTo, DaoCriteria crit, ClassMapping mapping) { bool first = true; foreach (SortOrder order in crit.Orders) { if (first) { first = false; } else { orderClauseToAddTo.Append(", "); } switch (order.Direction) { case SortType.Asc: orderClauseToAddTo.Append(order is GroupCountSortOrder ? (_connDesc.CanUseAliasInOrderClause() ? (_connDesc.ColumnAliasPrefix() + COUNT_COL_ALIAS + _connDesc.ColumnAliasSuffix()) : "COUNT(*)") : mapping.AllDataColsByObjAttrs[order.Property]); orderClauseToAddTo.Append(" ASC"); break; case SortType.Desc: orderClauseToAddTo.Append(order is GroupCountSortOrder ? (_connDesc.CanUseAliasInOrderClause() ? (_connDesc.ColumnAliasPrefix() + COUNT_COL_ALIAS + _connDesc.ColumnAliasSuffix()) : "COUNT(*)") : mapping.AllDataColsByObjAttrs[order.Property]); orderClauseToAddTo.Append(" DESC"); break; case SortType.Computed: orderClauseToAddTo.Append(order.Property); break; default: throw new NotSupportedException("Sort type '" + order.Direction + "' not supported."); } } }
/// <summary> /// Builds the query based on a serializable criteria. The Query object is particular to /// the implementation, but may contain things like the parameters parsed out, or whatever /// makes sense to this FastDaoLayer. You can think of this method as a method to convert /// from the generic DaoCriteria into the specific details necessary for querying. /// </summary> /// <param name="crit">The criteria to use to find the desired objects.</param> /// <param name="mapping">The mapping of the table for which to build the query string.</param> /// <returns>A query that can be run by ExecureQuery.</returns> public override IDaQuery CreateQuery(ClassMapping mapping, DaoCriteria crit) { UnqueryableQuery retVal = _queryCache.Get(); retVal.Criteria = crit; return retVal; }
/// <summary> /// Deletes a data object record using the mapping and criteria for what's deleted. /// </summary> /// <param name="transaction">The transaction to do this as part of.</param> /// <param name="mapping">The mapping of the table from which to delete.</param> /// <param name="crit">Criteria for deletion. NOTE: Only the expressions are observed, /// other things (like "order" or start / limit) are ignored. /// WARNING: A null or empty (no expression) criteria will /// delete ALL records!</param> /// <returns>The number of records affected.</returns> public abstract int Delete(ITransaction transaction, ClassMapping mapping, DaoCriteria crit);
/// <summary> /// Gets a count of records for the given criteria. /// </summary> /// <param name="transaction">Should be null, transactions are not supported.</param> /// <param name="crit">The criteria to use for "where" comparisons.</param> /// <param name="mapping">The mapping of the table for which to build the query string.</param> /// <returns>The number of results found that matched the criteria.</returns> public override int GetCount(ITransaction transaction, ClassMapping mapping, DaoCriteria crit) { switch (_connDesc.Type) { case CsvConnectionType.Directory: case CsvConnectionType.FileName: case CsvConnectionType.Reader: // These are OK. break; default: throw new LoggingException("Connection does not support counting records: " + _connDesc); } CsvDataReader reader = new CsvDataReader(this, mapping, crit); int retVal = 0; try { while (reader.Read()) { retVal++; } } finally { reader.Close(); } return retVal; }
/// <summary> /// Builds the query based on a serializable criteria. The Query object is particular to /// the implementation, but may contain things like the parameters parsed out, or whatever /// makes sense to this FastDaoLayer. You can think of this method as a method to convert /// from the generic DaoCriteria into the specific details necessary for querying. /// </summary> /// <param name="mapping">The mapping of the table for which to build the query string.</param> /// <param name="crit">The criteria to use to find the desired objects.</param> /// <returns>A query that can be run by ExecureQuery.</returns> public abstract IDaQuery CreateQuery(ClassMapping mapping, DaoCriteria crit);
/// <summary> /// Gets a count of records for the given criteria, /// aggregated by the given grouping expressions. This matches "GROUP BY" behavior /// in SQL. /// </summary> /// <param name="transaction">The transaction to do this as part of.</param> /// <param name="mapping">The mapping of the table for which to build the query string.</param> /// <param name="crit">The criteria to use for "where" comparisons.</param> /// <param name="groupExpressions">The fields/expressions to aggregate on when counting.</param> /// <returns>The number of objects that match the criteria, plus the values of those objects /// for the fields that were aggregated on.</returns> public abstract List<GroupCountResult> GetCount(ITransaction transaction, ClassMapping mapping, DaoCriteria crit, ICollection<AbstractGroupExpression> groupExpressions);