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