public ObjectMetadata(string nameSpace, string objectName, ObjectIndexMetadata[] indexes, string[] dependencies) { NameSpace = nameSpace; ObjectName = objectName; Indexes = indexes; Dependencies = dependencies; }
public override int[] Find(string objectFullName, string constraint, ObjectIndexMetadata[] indexes) { return Find(objectFullName, constraint, 0, null, indexes); }
public override bool Exists(string objectFullName, string constraint, ObjectIndexMetadata[] indexes) { using (var db = OpenData()) { var sqlConstraint = CreateSQLConstraint(db, indexes, constraint); var tableName = _CreateTableName(db, objectFullName); return 1 == db.ExecuteScalar<int>(string.Format(SQLStatements.RowsExist, tableName, sqlConstraint.SQL), 0, sqlConstraint.Parameters.ToArray()); } }
private static string _CreateColumnDef(IDatabaseService db, ObjectIndexMetadata indexMetadata) { string name = db.EscapeCommandText(indexMetadata.Name); string type = "nvarchar"; string length = "(30)"; string nullable = (indexMetadata.Nullable) ? "NULL" : "NOT NULL"; switch (indexMetadata.DataType) { case ObjectIndexType.Integer: type = "int"; length = ""; break; case ObjectIndexType.Binary: type = "varbinary"; length = "(" + indexMetadata.Precision + ")"; break; case ObjectIndexType.DateTime: type = "datetime"; length = ""; break; case ObjectIndexType.Decimal: type = "decimal"; length = "(" + indexMetadata.Precision + "," + indexMetadata.Scale + ")"; break; default: length = "(" + indexMetadata.Precision + ")"; break; } return string.Format("[{0}] [{1}]{2} {3}", name, type, length, nullable); }
public void Order() { var provider = IndexProvider; var indexMetadata = new ObjectIndexMetadata[] { new ObjectIndexMetadata("TestCol1", ObjectIndexType.Integer), new ObjectIndexMetadata("TestCol2", ObjectIndexType.String, 15) }; provider.ProvisionIndex( new ObjectMetadata(NameSpace1, ObjectName1, indexMetadata)); provider.UpsertIndexValues(ObjectFullName1, 1, ObjectIndex.Create("TestCol1", 100), ObjectIndex.Create("TestCol2", "A")); provider.UpsertIndexValues(ObjectFullName1, 2, ObjectIndex.Create("TestCol1", 105), ObjectIndex.Create("TestCol2", "A")); provider.UpsertIndexValues(ObjectFullName1, 3, ObjectIndex.Create("TestCol1", 500), ObjectIndex.Create("TestCol2", "B")); provider.UpsertIndexValues(ObjectFullName1, 4, ObjectIndex.Create("TestCol1", 500), ObjectIndex.Create("TestCol2", "C")); var vals = provider.Find(ObjectFullName1, @"{ ""TestCol2"" : ""A"", ""Op"" : ""="" }", 0, new OrderOptions() { Descending = true, Indexes = new string[] { "TestCol1" } }, indexMetadata); Assert.AreEqual(2, vals.Length); Assert.AreEqual(2, vals[0]); Assert.AreEqual(1, vals[1]); vals = provider.Find(ObjectFullName1, @"{ ""TestCol2"" : ""A"", ""Op"" : ""="" }", 0, new OrderOptions() { Descending = false, Indexes = new string[] { "TestCol1" } }, indexMetadata); Assert.AreEqual(2, vals.Length); Assert.AreEqual(1, vals[0]); Assert.AreEqual(2, vals[1]); vals = provider.Find(ObjectFullName1, new ObjectFindOptions() { Logic = ObjectFindLogic.And, Operator = ObjectFindOperator.Equals, Limit = 0, Order = new OrderOptions() { Descending = true, Indexes = new string[] { "TestCol1" } } }, ObjectIndex.Create("TestCol2", "A")); Assert.AreEqual(2, vals.Length); Assert.AreEqual(2, vals[0]); Assert.AreEqual(1, vals[1]); vals = provider.Find(ObjectFullName1, new ObjectFindOptions() { Logic = ObjectFindLogic.And, Operator = ObjectFindOperator.Equals, Limit = 0, Order = new OrderOptions() { Descending = false, Indexes = new string[] { "TestCol1" } } }, ObjectIndex.Create("TestCol2", "A")); Assert.AreEqual(2, vals.Length); Assert.AreEqual(1, vals[0]); Assert.AreEqual(2, vals[1]); // Test and Order with Limit vals = provider.Find(ObjectFullName1, new ObjectFindOptions() { Logic = ObjectFindLogic.And, Operator = ObjectFindOperator.Equals, Limit = 1, Order = new OrderOptions() { Descending = true, Indexes = new string[] { "TestCol1" } } }, ObjectIndex.Create("TestCol2", "A")); Assert.AreEqual(1, vals.Length); Assert.AreEqual(2, vals[0]); vals = provider.Find(ObjectFullName1, new ObjectFindOptions() { Logic = ObjectFindLogic.And, Operator = ObjectFindOperator.Equals, Limit = 1, Order = new OrderOptions() { Descending = false, Indexes = new string[] { "TestCol1" } } }, ObjectIndex.Create("TestCol2", "A")); Assert.AreEqual(1, vals.Length); Assert.AreEqual(1, vals[0]); }
public void Limit() { var provider = IndexProvider; var indexMetadata = new ObjectIndexMetadata[] { new ObjectIndexMetadata("TestCol1", ObjectIndexType.Integer), new ObjectIndexMetadata("TestCol2", ObjectIndexType.String, 15) }; provider.ProvisionIndex( new ObjectMetadata(NameSpace1, ObjectName1, indexMetadata)); provider.UpsertIndexValues(ObjectFullName1, 1, ObjectIndex.Create("TestCol1", 100), ObjectIndex.Create("TestCol2", "A")); provider.UpsertIndexValues(ObjectFullName1, 2, ObjectIndex.Create("TestCol1", 105), ObjectIndex.Create("TestCol2", "A")); provider.UpsertIndexValues(ObjectFullName1, 3, ObjectIndex.Create("TestCol1", 500), ObjectIndex.Create("TestCol2", "A")); provider.UpsertIndexValues(ObjectFullName1, 4, ObjectIndex.Create("TestCol1", 500), ObjectIndex.Create("TestCol2", "B")); var ids = provider.Find(ObjectFullName1, new ObjectFindOptions() { Logic = ObjectFindLogic.And, Operator = ObjectFindOperator.Equals, Limit = 0 }, ObjectIndex.Create("TestCol2", "A")); Assert.AreEqual(3, ids.Length); ids = provider.Find(ObjectFullName1, new ObjectFindOptions() { Logic = ObjectFindLogic.And, Operator = ObjectFindOperator.Equals, Limit = 1 }, ObjectIndex.Create("TestCol2", "A")); Assert.AreEqual(1, ids.Length); ids = provider.Find(ObjectFullName1, new ObjectFindOptions() { Logic = ObjectFindLogic.And, Operator = ObjectFindOperator.Equals, Limit = 2 }, ObjectIndex.Create("TestCol2", "A")); Assert.AreEqual(2, ids.Length); ids = provider.Find(ObjectFullName1, new ObjectFindOptions() { Logic = ObjectFindLogic.And, Operator = ObjectFindOperator.Equals, Limit = 10 }, ObjectIndex.Create("TestCol2", "A")); Assert.AreEqual(3, ids.Length); ids = provider.Find(ObjectFullName1, @"{ ""TestCol2"" : ""a"", ""Op"" : ""LIKE"" }", 0, null, indexMetadata); Assert.AreEqual(3, ids.Length); ids = provider.Find(ObjectFullName1, @"{ ""TestCol2"" : ""a"", ""Op"" : ""LIKE"" }", 1, null, indexMetadata); Assert.AreEqual(1, ids.Length); ids = provider.Find(ObjectFullName1, @"{ ""TestCol2"" : ""a"", ""Op"" : ""LIKE"" }", 2, null, indexMetadata); Assert.AreEqual(2, ids.Length); ids = provider.Find(ObjectFullName1, @"{ ""TestCol2"" : ""a"", ""Op"" : ""LIKE"" }", 10, null, indexMetadata); Assert.AreEqual(3, ids.Length); }
public ObjectMetadata(string nameSpace, string objectName, ObjectIndexMetadata[] indexes) : this(nameSpace, objectName, indexes, null) { }
private static string _CreateColumnDef(IDatabaseService db, ObjectIndexMetadata indexMetadata) { string name = db.EscapeCommandText(indexMetadata.Name); string type = "VARCHAR"; string length = "(30)"; string nullable = " NOT NULL"; if (indexMetadata.Nullable) { nullable = " NULL"; } switch (indexMetadata.DataType) { case ObjectIndexType.Integer: type = "INT"; length = ""; break; case ObjectIndexType.Binary: type = "VARBINARY"; length = "(" + indexMetadata.Precision + ")"; break; case ObjectIndexType.DateTime: type = "DATETIME"; length = ""; break; case ObjectIndexType.Decimal: type = "DECIMAL"; length = "(" + indexMetadata.Precision + "," + indexMetadata.Scale + ")"; break; default: length = "(" + indexMetadata.Precision + ")"; break; } return string.Format("`{0}` {1}{2} {3}", name, type, length, nullable); }
public void Iterate() { var provider = IndexProvider; var indexMetadata = new ObjectIndexMetadata[] { new ObjectIndexMetadata("TestCol1", ObjectIndexType.Integer), new ObjectIndexMetadata("TestCol2", ObjectIndexType.String, 15) }; provider.ProvisionIndex( new ObjectMetadata(NameSpace1, ObjectName1, indexMetadata)); provider.UpsertIndexValues(ObjectFullName1, 1, ObjectIndex.Create("TestCol1", 100), ObjectIndex.Create("TestCol2", "A")); provider.UpsertIndexValues(ObjectFullName1, 2, ObjectIndex.Create("TestCol1", 105), ObjectIndex.Create("TestCol2", "A")); provider.UpsertIndexValues(ObjectFullName1, 3, ObjectIndex.Create("TestCol1", 500), ObjectIndex.Create("TestCol2", "B")); provider.UpsertIndexValues(ObjectFullName1, 4, ObjectIndex.Create("TestCol1", 500), ObjectIndex.Create("TestCol2", "C")); IEnumerator<IDataRecord> records; using (records = provider.Iterate(ObjectFullName1, null, 0, null, null, indexMetadata).GetEnumerator()) { if (records.MoveNext()) { Assert.AreEqual(3, records.Current.FieldCount); // ID Assert.AreEqual(1, records.Current[0]); Assert.AreEqual(1, records.Current["ID"]); // TestCol1 Assert.AreEqual(100, records.Current[1]); Assert.AreEqual(100, records.Current["TestCol1"]); // TestCol2 Assert.AreEqual("A", records.Current[2]); Assert.AreEqual("A", records.Current["TestCol2"]); } if (records.MoveNext()) { // ID Assert.AreEqual(2, records.Current[0]); Assert.AreEqual(2, records.Current["ID"]); // TestCol1 Assert.AreEqual(105, records.Current[1]); Assert.AreEqual(105, records.Current["TestCol1"]); // TestCol2 Assert.AreEqual("A", records.Current[2]); Assert.AreEqual("A", records.Current["TestCol2"]); } } using (records = provider.Iterate(ObjectFullName1, indexMetadata).GetEnumerator()) { if (records.MoveNext()) { Assert.AreEqual(3, records.Current.FieldCount); // ID Assert.AreEqual(1, records.Current[0]); Assert.AreEqual(1, records.Current["ID"]); // TestCol1 Assert.AreEqual(100, records.Current[1]); Assert.AreEqual(100, records.Current["TestCol1"]); // TestCol2 Assert.AreEqual("A", records.Current[2]); Assert.AreEqual("A", records.Current["TestCol2"]); } if (records.MoveNext()) { // ID Assert.AreEqual(2, records.Current[0]); Assert.AreEqual(2, records.Current["ID"]); // TestCol1 Assert.AreEqual(105, records.Current[1]); Assert.AreEqual(105, records.Current["TestCol1"]); // TestCol2 Assert.AreEqual("A", records.Current[2]); Assert.AreEqual("A", records.Current["TestCol2"]); } } // test with constraint, limit, and order using (records = provider.Iterate(ObjectFullName1, @"{ ""TestCol2"" : ""b"", ""Op"" : ""LIKE"", ""OR"" : { ""TestCol2"" : ""c"", ""Op"" : ""LIKE"" } }", 2, new OrderOptions() { Descending = true, Indexes = new string[] { "TestCol2" } }, null, indexMetadata).GetEnumerator()) { if (records.MoveNext()) { Assert.AreEqual(3, records.Current.FieldCount); // ID Assert.AreEqual(4, records.Current[0]); Assert.AreEqual(4, records.Current["ID"]); // TestCol1 Assert.AreEqual(500, records.Current[1]); Assert.AreEqual(500, records.Current["TestCol1"]); // TestCol2 Assert.AreEqual("C", records.Current[2]); Assert.AreEqual("C", records.Current["TestCol2"]); } if (records.MoveNext()) { // ID Assert.AreEqual(3, records.Current[0]); Assert.AreEqual(3, records.Current["ID"]); // TestCol1 Assert.AreEqual(500, records.Current[1]); Assert.AreEqual(500, records.Current["TestCol1"]); // TestCol2 Assert.AreEqual("B", records.Current[2]); Assert.AreEqual("B", records.Current["TestCol2"]); } } // iterate only one index using (records = provider.Iterate(ObjectFullName1, @"{ ""TestCol2"" : ""b"", ""Op"" : ""LIKE"", ""OR"" : { ""TestCol1"" : 105, ""Op"" : ""="" } }", 0, null, new string[] { "TestCol1" }, indexMetadata).GetEnumerator()) { if (records.MoveNext()) { Assert.AreEqual(1, records.Current.FieldCount); // TestCol1 Assert.AreEqual(105, records.Current[0]); Assert.AreEqual(105, records.Current["TestCol1"]); } if (records.MoveNext()) { // TestCol1 Assert.AreEqual(500, records.Current[0]); Assert.AreEqual(500, records.Current["TestCol1"]); } } // iterate only two indexes in order using (records = provider.Iterate(ObjectFullName1, @"{ ""TestCol2"" : ""b"", ""Op"" : ""LIKE"", ""OR"" : { ""TestCol2"" : ""c"", ""Op"" : ""LIKE"" } }", 2, new OrderOptions() { Descending = false, Indexes = new string[] { "TestCol2" } }, new string[] { "ID", "TestCol1" }, indexMetadata).GetEnumerator()) { if (records.MoveNext()) { Assert.AreEqual(2, records.Current.FieldCount); // ID Assert.AreEqual(3, records.Current[0]); Assert.AreEqual(3, records.Current["ID"]); // TestCol1 Assert.AreEqual(500, records.Current[1]); Assert.AreEqual(500, records.Current["TestCol1"]); } if (records.MoveNext()) { // ID Assert.AreEqual(4, records.Current[0]); Assert.AreEqual(4, records.Current["ID"]); // TestCol1 Assert.AreEqual(500, records.Current[1]); Assert.AreEqual(500, records.Current["TestCol1"]); } } }
public void Exists() { var provider = IndexProvider; var indexMetadata = new ObjectIndexMetadata[] { new ObjectIndexMetadata("TestCol1", ObjectIndexType.Integer), new ObjectIndexMetadata("TestCol2", ObjectIndexType.String, 15) }; provider.ProvisionIndex( new ObjectMetadata(NameSpace1, ObjectName1, indexMetadata)); provider.UpsertIndexValues(ObjectFullName1, 1, ObjectIndex.Create("TestCol1", 100), ObjectIndex.Create("TestCol2", "A")); provider.UpsertIndexValues(ObjectFullName1, 2, ObjectIndex.Create("TestCol1", 105), ObjectIndex.Create("TestCol2", "A")); provider.UpsertIndexValues(ObjectFullName1, 3, ObjectIndex.Create("TestCol1", 500), ObjectIndex.Create("TestCol2", "B")); provider.UpsertIndexValues(ObjectFullName1, 4, ObjectIndex.Create("TestCol1", 500), ObjectIndex.Create("TestCol2", "C")); Assert.IsTrue(provider.Exists(ObjectFullName1, @"{ ""TestCol1"" : 100, ""Op"" : ""="" }", indexMetadata)); Assert.IsFalse(provider.Exists(ObjectFullName1, @"{ ""TestCol1"" : 102, ""Op"" : ""="" }", indexMetadata)); }
public void CountByFindOptions() { var provider = IndexProvider; var indexMetadata = new ObjectIndexMetadata[] { new ObjectIndexMetadata("TestCol1", ObjectIndexType.Integer), new ObjectIndexMetadata("TestCol2", ObjectIndexType.String, 15, true) }; provider.ProvisionIndex( new ObjectMetadata(NameSpace1, ObjectName1, indexMetadata)); provider.UpsertIndexValues(ObjectFullName1, 1, ObjectIndex.Create("TestCol1", 100), ObjectIndex.Create("TestCol2", "A")); provider.UpsertIndexValues(ObjectFullName1, 2, ObjectIndex.Create("TestCol1", 105), ObjectIndex.Create("TestCol2", "A")); provider.UpsertIndexValues(ObjectFullName1, 3, ObjectIndex.Create("TestCol1", 500), ObjectIndex.Create("TestCol2", "B")); provider.UpsertIndexValues(ObjectFullName1, 4, ObjectIndex.Create("TestCol1", 500), ObjectIndex.Create("TestCol2", null)); Assert.AreEqual(1, provider.Count(ObjectFullName1, new ObjectFindOptions() { Operator = ObjectFindOperator.Equals }, new ObjectIndex[] { ObjectIndex.Create("TestCol1", 100) })); Assert.AreEqual(0, provider.Count(ObjectFullName1, new ObjectFindOptions() { Operator = ObjectFindOperator.Equals }, new ObjectIndex[] { ObjectIndex.Create("TestCol1", 102) })); Assert.AreEqual(1, provider.Count(ObjectFullName1, new ObjectFindOptions() { Operator = ObjectFindOperator.IsNull }, new ObjectIndex[] { ObjectIndex.Create("TestCol2", null) })); Assert.AreEqual(2, provider.Count(ObjectFullName1, new ObjectFindOptions() { Operator = ObjectFindOperator.Like }, new ObjectIndex[] { ObjectIndex.Create("TestCol2", "a") })); Assert.AreEqual(3, provider.Count(ObjectFullName1, new ObjectFindOptions() { Operator = ObjectFindOperator.Like, Logic = ObjectFindLogic.Or }, new ObjectIndex[] { ObjectIndex.Create("TestCol2", "a"), ObjectIndex.Create("TestCol2", "B") })); }
public void CountByConstraint() { var provider = IndexProvider; var indexMetadata = new ObjectIndexMetadata[] { new ObjectIndexMetadata("TestCol1", ObjectIndexType.Integer), new ObjectIndexMetadata("TestCol2", ObjectIndexType.String, 15) }; provider.ProvisionIndex( new ObjectMetadata(NameSpace1, ObjectName1, indexMetadata)); provider.UpsertIndexValues(ObjectFullName1, 1, ObjectIndex.Create("TestCol1", 100), ObjectIndex.Create("TestCol2", "A")); provider.UpsertIndexValues(ObjectFullName1, 2, ObjectIndex.Create("TestCol1", 105), ObjectIndex.Create("TestCol2", "A")); provider.UpsertIndexValues(ObjectFullName1, 3, ObjectIndex.Create("TestCol1", 500), ObjectIndex.Create("TestCol2", "B")); provider.UpsertIndexValues(ObjectFullName1, 4, ObjectIndex.Create("TestCol1", 500), ObjectIndex.Create("TestCol2", "C")); Assert.AreEqual(1, provider.Count(ObjectFullName1, @"{ ""TestCol1"" : 100, ""Op"" : ""="" }", indexMetadata)); Assert.AreEqual(0, provider.Count(ObjectFullName1, @"{ ""TestCol1"" : 102, ""Op"" : ""="" }", indexMetadata)); Assert.AreEqual(4, provider.Count(ObjectFullName1, @"{ ""TestCol1"" : 0, ""Op"" : "">"" }", indexMetadata)); Assert.AreEqual(2, provider.Count(ObjectFullName1, @"{ ""TestCol2"" : ""a"", ""Op"" : ""LIKE"" }", indexMetadata)); }
public void TopByFind() { var provider = IndexProvider; var indexMetadata = new ObjectIndexMetadata[] { new ObjectIndexMetadata("TestCol1", ObjectIndexType.Integer), new ObjectIndexMetadata("TestCol2", ObjectIndexType.String, 15, true) }; provider.ProvisionIndex( new ObjectMetadata(NameSpace1, ObjectName1, indexMetadata)); provider.UpsertIndexValues(ObjectFullName1, 1, ObjectIndex.Create("TestCol1", 100), ObjectIndex.Create("TestCol2", "A")); provider.UpsertIndexValues(ObjectFullName1, 2, ObjectIndex.Create("TestCol1", 105), ObjectIndex.Create("TestCol2", null)); var result = provider.Find(ObjectFullName1, new ObjectFindOptions() { Limit = 1, Order = new OrderOptions() { Descending = true, Indexes = new string[] { "TestCol1" } } }); Assert.AreEqual(1, result.Length); Assert.AreEqual(2, result[0]); }
public override int[] Find(string objectFullName, string constraint, uint limit, OrderOptions order, ObjectIndexMetadata[] indexes) { using (var db = OpenData()) { var tableName = _CreateTableName(db, objectFullName); var compiledConstraint = CreateSQLConstraint(db, indexes, constraint); var sqlConstraint = compiledConstraint.SQL; if (!string.IsNullOrEmpty(sqlConstraint)) { sqlConstraint = "WHERE " + sqlConstraint; } var orderBySql = _CreateOrderBySQL(db, order); var limitSql = (0 == limit) ? SQLStatements.NoLimit : string.Format(SQLStatements.Limit, limit); return db.GetValues<int>(string.Format(SQLStatements.Find, tableName, sqlConstraint, orderBySql, limitSql), compiledConstraint.Parameters.ToArray()); } }
public void JSONConstraintFind() { var provider = IndexProvider; var indexMetadata = new ObjectIndexMetadata[] { new ObjectIndexMetadata("TestCol1", ObjectIndexType.Integer), new ObjectIndexMetadata("TestCol2", ObjectIndexType.String, 15) }; provider.ProvisionIndex( new ObjectMetadata(NameSpace1, ObjectName1, indexMetadata)); provider.UpsertIndexValues(ObjectFullName1, 1, ObjectIndex.Create("TestCol1", 100), ObjectIndex.Create("TestCol2", "A")); provider.UpsertIndexValues(ObjectFullName1, 2, ObjectIndex.Create("TestCol1", 105), ObjectIndex.Create("TestCol2", "A")); provider.UpsertIndexValues(ObjectFullName1, 3, ObjectIndex.Create("TestCol1", 500), ObjectIndex.Create("TestCol2", "B")); provider.UpsertIndexValues(ObjectFullName1, 4, ObjectIndex.Create("TestCol1", 500), ObjectIndex.Create("TestCol2", "C")); // test single constraint value that should return a single result int[] ids = provider.Find(ObjectFullName1, @"{ ""TestCol1"" : 100, ""Op"" : ""="" }", indexMetadata); Assert.IsNotNull(ids); Assert.AreEqual(1, ids.Length); Assert.AreEqual(1, ids[0]); // test two constraint values that should return a single result ids = provider.Find(ObjectFullName1, @"{ ""TestCol1"" : 100, ""Op"" : ""="", ""AND"" : { ""TestCol2"" : ""A"", ""Op"" : ""=""}}", indexMetadata); Assert.IsNotNull(ids); Assert.AreEqual(1, ids.Length); Assert.AreEqual(1, ids[0]); // test single constraint value that should return two results ids = provider.Find(ObjectFullName1, @"{ ""TestCol1"" : 500, ""Op"" : ""="" }", indexMetadata); Assert.AreEqual(2, ids.Length); Assert.AreEqual(3, ids[0]); Assert.AreEqual(4, ids[1]); // test single constraint value that should return zero results ids = provider.Find(ObjectFullName1, @"{ ""TestCol1"" : 105, ""Op"" : ""="", ""AND"" : { ""TestCol2"" : ""B"", ""Op"" : ""=""}}", indexMetadata); Assert.AreEqual(0, ids.Length); }
public override IEnumerable<IDataRecord> Iterate(string objectFullName, string constraint, uint limit, OrderOptions order, string[] iterateIndexes, ObjectIndexMetadata[] indexes) { using (var db = OpenData()) { var tableName = _CreateTableName(db, objectFullName); var whereSql = string.Empty; IDataParameter[] parameters = null; if (!string.IsNullOrEmpty(constraint)) { var sqlConstraint = CreateSQLConstraint(db, indexes, constraint); whereSql = " WHERE " + sqlConstraint.SQL; parameters = sqlConstraint.Parameters.ToArray(); } var orderBySql = _CreateOrderBySQL(db, order); var limitSql = (0 == limit) ? SQLStatements.NoLimit : string.Format(SQLStatements.Limit, limit); string[] selectNames = null; // if no iterate indexes are specified, then iterate overall index names from the metadata if (null == iterateIndexes || 0 == iterateIndexes.Length) { if (null == indexes || 0 == indexes.Length) { // If not iterate indexes or metadataColumns provided, then only select the ID column selectNames = new string[] { db.MakeQuotedName(IDColumn) }; } else { selectNames = new string[indexes.Length + 1]; selectNames[0] = db.MakeQuotedName(IDColumn); for (int i = 0; indexes.Length > i; i++) { selectNames[i + 1] = db.MakeQuotedName(indexes[i].Name); } } } else { selectNames = new string[iterateIndexes.Length]; for (int i = 0; iterateIndexes.Length > i; i++) { selectNames[i] = db.MakeQuotedName(iterateIndexes[i]); } } string selectNamesSql = string.Join(",", selectNames); using (var reader = db.ExecuteReader(string.Format(SQLStatements.Iterate, tableName, selectNamesSql, whereSql, orderBySql, limitSql), parameters)) { while (reader.Read()) { yield return reader; } } } }
public void JSONConstraintOperators() { var provider = IndexProvider; var indexMetadata = new ObjectIndexMetadata[] { new ObjectIndexMetadata("TestCol1", ObjectIndexType.Integer), new ObjectIndexMetadata("TestCol2", ObjectIndexType.String, 15) }; provider.ProvisionIndex( new ObjectMetadata(NameSpace1, ObjectName1, indexMetadata)); provider.UpsertIndexValues(ObjectFullName1, 1, ObjectIndex.Create("TestCol1", 100), ObjectIndex.Create("TestCol2", "asdf")); provider.UpsertIndexValues(ObjectFullName1, 2, ObjectIndex.Create("TestCol1", 200), ObjectIndex.Create("TestCol2", "zxzy")); // test LIKE operator int[] ids = provider.Find(ObjectFullName1, @"{ ""TestCol2"" : ""%sd%"", ""Op"" : ""LIKE"" }", indexMetadata); Assert.IsNotNull(ids); Assert.AreEqual(1, ids.Length); Assert.AreEqual(1, ids[0]); // test NOT LIKE operator ids = provider.Find(ObjectFullName1, @"{ ""TestCol2"" : ""as%"", ""Op"" : ""NOT LIKE"" }", indexMetadata); Assert.IsNotNull(ids); Assert.AreEqual(1, ids.Length); Assert.AreEqual(2, ids[0]); // test EQUALS operator ids = provider.Find(ObjectFullName1, @"{ ""TestCol1"" : 100, ""Op"" : ""="" }", indexMetadata); Assert.IsNotNull(ids); Assert.AreEqual(1, ids.Length); Assert.AreEqual(1, ids[0]); // test NOT EQUALS operator ids = provider.Find(ObjectFullName1, @"{ ""TestCol1"" : 100, ""Op"" : ""<>"" }", indexMetadata); Assert.IsNotNull(ids); Assert.AreEqual(1, ids.Length); Assert.AreEqual(2, ids[0]); // test IN operator ids = provider.Find(ObjectFullName1, @"{ ""TestCol1"" : [100], ""Op"" : ""IN"" }", indexMetadata); Assert.IsNotNull(ids); Assert.AreEqual(1, ids.Length); Assert.AreEqual(1, ids[0]); // test NOT IN operator ids = provider.Find(ObjectFullName1, @"{ ""TestCol1"" : [100], ""Op"" : ""NOT IN"" }", indexMetadata); Assert.IsNotNull(ids); Assert.AreEqual(1, ids.Length); Assert.AreEqual(2, ids[0]); // test LESS THAN operator ids = provider.Find(ObjectFullName1, @"{ ""TestCol1"" : 200, ""Op"" : ""<"" }", indexMetadata); Assert.IsNotNull(ids); Assert.AreEqual(1, ids.Length); Assert.AreEqual(1, ids[0]); // test LESS THAN OR EQUALS operator ids = provider.Find(ObjectFullName1, @"{ ""TestCol1"" : 100, ""Op"" : ""<="" }", indexMetadata); Assert.IsNotNull(ids); Assert.AreEqual(1, ids.Length); Assert.AreEqual(1, ids[0]); // test GREATER THAN operator ids = provider.Find(ObjectFullName1, @"{ ""TestCol1"" : 100, ""Op"" : "">"" }", indexMetadata); Assert.IsNotNull(ids); Assert.AreEqual(1, ids.Length); Assert.AreEqual(2, ids[0]); // test GREATER THAN OR EQUALS operator ids = provider.Find(ObjectFullName1, @"{ ""TestCol1"" : 200, ""Op"" : "">="" }", indexMetadata); Assert.IsNotNull(ids); Assert.AreEqual(1, ids.Length); Assert.AreEqual(2, ids[0]); }
public override int Count(string objectFullName, string constraint, ObjectIndexMetadata[] indexes) { using (var db = OpenData()) { var compiledConstraint = CreateSQLConstraint(db, indexes, constraint); var sqlConstraint = compiledConstraint.SQL; if (!string.IsNullOrEmpty(sqlConstraint)) { sqlConstraint = "WHERE " + sqlConstraint; } var tableName = _CreateTableName(db, objectFullName); return db.ExecuteScalar<int>(string.Format(SQLStatements.RowsCount, tableName, sqlConstraint, SQLStatements.NoOrder, SQLStatements.NoLimit), 0, compiledConstraint.Parameters.ToArray()); } }
public void CreateFromRecord() { var provider = IndexProvider; var indexMetadata = new ObjectIndexMetadata[] { new ObjectIndexMetadata("TestCol1", ObjectIndexType.Integer), new ObjectIndexMetadata("TestCol2", ObjectIndexType.String, 15), new ObjectIndexMetadata("TestCol3", ObjectIndexType.Binary, 16), new ObjectIndexMetadata("TestCol4", ObjectIndexType.DateTime) }; provider.ProvisionIndex( new ObjectMetadata(NameSpace1, ObjectName1, indexMetadata)); var guid1 = new Guid("{36EB4248-6835-4CDC-9FDC-B7E7678AAE5A}"); var guid2 = new Guid("{0BF33DA5-0D39-4189-8CC2-3DC8DDC122A7}"); var dt1 = new DateTime(2008, 8, 1, 22, 0, 5); var dt2 = new DateTime(2014, 1, 20, 1, 30, 0); provider.UpsertIndexValues(ObjectFullName1, 1, ObjectIndex.Create("TestCol1", 100), ObjectIndex.Create("TestCol2", "A"), ObjectIndex.Create("TestCol3", SerializerHelper.Serialize(guid1)), ObjectIndex.Create("TestCol4", dt1)); provider.UpsertIndexValues(ObjectFullName1, 2, ObjectIndex.Create("TestCol1", 105), ObjectIndex.Create("TestCol2", "A"), ObjectIndex.Create("TestCol3", SerializerHelper.Serialize(guid2)), ObjectIndex.Create("TestCol4", dt2)); var records = provider.Iterate(ObjectFullName1, indexMetadata); var recordEnum = records.GetEnumerator(); recordEnum.MoveNext(); var indexRecord = ObjectIndexRecord.CreateFromDataRecord(recordEnum.Current); var vals = indexRecord.Values; // 5 instead of 4 because ID is included when all values are iterated Assert.AreEqual(5, vals.Length); Assert.AreEqual("ID", vals[0].Name); Assert.AreEqual(1, vals[0].GetObjectValue()); Assert.AreEqual("TestCol1", vals[1].Name); Assert.AreEqual(100, vals[1].GetObjectValue()); Assert.AreEqual("TestCol2", vals[2].Name); Assert.AreEqual("A", vals[2].GetObjectValue()); Assert.AreEqual("TestCol3", vals[3].Name); Assert.AreEqual(guid1, new Guid(vals[3].Value)); Assert.AreEqual("TestCol4", vals[4].Name); Assert.AreEqual(dt1, vals[4].GetObjectValue()); recordEnum.MoveNext(); indexRecord = ObjectIndexRecord.CreateFromDataRecord(recordEnum.Current); vals = indexRecord.Values; // 5 instead of 4 because ID is included when all values are iterated Assert.AreEqual(5, vals.Length); Assert.AreEqual("ID", vals[0].Name); Assert.AreEqual(2, vals[0].GetObjectValue()); Assert.AreEqual("TestCol1", vals[1].Name); Assert.AreEqual(105, vals[1].GetObjectValue()); Assert.AreEqual("TestCol2", vals[2].Name); Assert.AreEqual("A", vals[2].GetObjectValue()); Assert.AreEqual("TestCol3", vals[3].Name); Assert.AreEqual(guid2, new Guid(vals[3].Value)); Assert.AreEqual("TestCol4", vals[4].Name); Assert.AreEqual(dt2, vals[4].GetObjectValue()); Assert.IsFalse(recordEnum.MoveNext()); }