public void TestFilteredRelationshipQueryBuilder() { // Use the real RelationshipFilter because CompiledFilter is hard coded to look for it MockCore.AddMediaItemAspectStorage(RelationshipAspect.Metadata); SingleTestMIA mia1 = TestBackendUtils.CreateSingleMIA("Meta1", Cardinality.Inline, true, true); SingleTestMIA mia2 = TestBackendUtils.CreateSingleMIA("Meta2", Cardinality.Inline, true, true); SingleTestMIA mia3 = TestBackendUtils.CreateSingleMIA("Meta3", Cardinality.Inline, true, true); ICollection <MediaItemAspectMetadata> requiredMIATypes = new List <MediaItemAspectMetadata>(); requiredMIATypes.Add(mia1.Metadata); requiredMIATypes.Add(mia2.Metadata); IFilter linkedMovieFilter = BooleanCombinationFilter.CombineFilters( BooleanOperator.And, new RelationalFilter(mia3.ATTR_INTEGER, RelationalOperator.EQ, 1), new RelationalFilter(mia3.ATTR_STRING, RelationalOperator.EQ, "test")); Guid movieType = new Guid("bbbbbbbb-2222-2222-2222-bbbbbbbbbbbb"); Guid actorType = new Guid("cccccccc-3333-3333-3333-cccccccccccc"); IFilter filter = new FilteredRelationshipFilter(actorType, movieType, linkedMovieFilter); MIAQueryBuilder builder = new MIAQueryBuilder(MockCore.Management, new List <QueryAttribute>(), null, requiredMIATypes, new List <MediaItemAspectMetadata>(), filter, null, null); string mediaItemIdAlias = null; IDictionary <MediaItemAspectMetadata, string> miamAliases = null; IDictionary <QueryAttribute, string> attributeAliases = null; string statementStr = null; IList <BindVar> bindVars = null; builder.GenerateSqlStatement(out mediaItemIdAlias, out miamAliases, out attributeAliases, out statementStr, out bindVars); Console.WriteLine("mediaItemIdAlias: {0}", mediaItemIdAlias); Console.WriteLine("miamAliases: [{0}]", string.Join(",", miamAliases)); Console.WriteLine("attributeAliases: [{0}]", string.Join(",", attributeAliases)); Console.WriteLine("statementStr: {0}", statementStr); Console.WriteLine("bindVars: [{0}]", string.Join(",", bindVars)); Assert.AreEqual("A0", mediaItemIdAlias, "Media item ID alias"); Assert.AreEqual(CreateMIAMAliases(mia1.Metadata, "A1", mia2.Metadata, "A2"), miamAliases, "MIAM aliases"); Assert.AreEqual(new Dictionary <QueryAttribute, string>(), attributeAliases, "Attribute aliases"); Assert.AreEqual("SELECT T0.MEDIA_ITEM_ID A0, T0.MEDIA_ITEM_ID A1, T1.MEDIA_ITEM_ID A2 FROM M_META1 T0 INNER JOIN M_META2 T1 ON T1.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID " + " WHERE T0.MEDIA_ITEM_ID IN(SELECT R1.MEDIA_ITEM_ID FROM M_RELATIONSHIP R1 WHERE R1.ROLE=@V0 AND R1.LINKEDROLE=@V1 AND R1.LINKEDID IN( " + "SELECT TS.A0 FROM (SELECT T0.MEDIA_ITEM_ID A0, T1.MEDIA_ITEM_ID A1 FROM MEDIA_ITEMS T0 LEFT OUTER JOIN M_META3 T1 ON T0.MEDIA_ITEM_ID = T1.MEDIA_ITEM_ID " + " WHERE (T1.ATTR_INTEGER = @V2 AND T1.ATTR_STRING = @V3)) TS) " + "UNION SELECT R1.LINKEDID FROM M_RELATIONSHIP R1 WHERE R1.LINKEDROLE=@V0 AND R1.ROLE=@V1 AND R1.MEDIA_ITEM_ID IN( SELECT TS.A0 FROM (" + "SELECT T0.MEDIA_ITEM_ID A0, T1.MEDIA_ITEM_ID A1 FROM MEDIA_ITEMS T0 LEFT OUTER JOIN M_META3 T1 ON T0.MEDIA_ITEM_ID = T1.MEDIA_ITEM_ID " + " WHERE (T1.ATTR_INTEGER = @V2 AND T1.ATTR_STRING = @V3)) TS))", statementStr, "Statement"); Assert.AreEqual(new List <BindVar> { new BindVar("V0", actorType, typeof(Guid)), new BindVar("V1", movieType, typeof(Guid)), new BindVar("V2", 1, typeof(int)), new BindVar("V3", "test", typeof(string)) }, bindVars, "Bind vars"); }
public void TestRelationshipQueryBuilder() { // Use the real RelationshipFilter because CompiledFilter is hard coded to look for it MockCore.AddMediaItemAspectStorage(RelationshipAspect.Metadata); SingleTestMIA mia1 = TestBackendUtils.CreateSingleMIA("Meta1", Cardinality.Inline, true, true); SingleTestMIA mia2 = TestBackendUtils.CreateSingleMIA("Meta2", Cardinality.Inline, true, true); ICollection <MediaItemAspectMetadata> requiredMIATypes = new List <MediaItemAspectMetadata>(); requiredMIATypes.Add(mia1.Metadata); requiredMIATypes.Add(mia2.Metadata); Guid movieId = new Guid("aaaaaaaa-1111-1111-1111-aaaaaaaaaaaa"); Guid movieType = new Guid("bbbbbbbb-2222-2222-2222-bbbbbbbbbbbb"); Guid actorType = new Guid("cccccccc-3333-3333-3333-cccccccccccc"); IFilter filter = new RelationshipFilter(actorType, movieType, movieId); MIAQueryBuilder builder = new MIAQueryBuilder(MockCore.Management, new List <QueryAttribute>(), null, requiredMIATypes, new List <MediaItemAspectMetadata>(), filter, null); string mediaItemIdAlias = null; IDictionary <MediaItemAspectMetadata, string> miamAliases = null; IDictionary <QueryAttribute, string> attributeAliases = null; string statementStr = null; IList <BindVar> bindVars = null; builder.GenerateSqlStatement(out mediaItemIdAlias, out miamAliases, out attributeAliases, out statementStr, out bindVars); Console.WriteLine("mediaItemIdAlias: {0}", mediaItemIdAlias); Console.WriteLine("miamAliases: [{0}]", string.Join(",", miamAliases)); Console.WriteLine("attributeAliases: [{0}]", string.Join(",", attributeAliases)); Console.WriteLine("statementStr: {0}", statementStr); Console.WriteLine("bindVars: [{0}]", string.Join(",", bindVars)); Assert.AreEqual("A0", mediaItemIdAlias, "Media item ID alias"); Assert.AreEqual(CreateMIAMAliases(mia1.Metadata, "A1", mia2.Metadata, "A2"), miamAliases, "MIAM aliases"); Assert.AreEqual(new Dictionary <QueryAttribute, string>(), attributeAliases, "Attribute aliases"); Assert.AreEqual("SELECT T0.MEDIA_ITEM_ID A0, T0.MEDIA_ITEM_ID A1, T1.MEDIA_ITEM_ID A2 FROM M_META1 T0 INNER JOIN M_META2 T1 ON T1.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID " + " WHERE T0.MEDIA_ITEM_ID IN(SELECT R1.MEDIA_ITEM_ID FROM M_RELATIONSHIP R1 WHERE R1.LINKEDID=@V0 AND R1.ROLE=@V1 AND R1.LINKEDROLE=@V2 UNION SELECT R2.LINKEDID " + "FROM M_RELATIONSHIP R2 WHERE R2.MEDIA_ITEM_ID=@V0 AND R2.LINKEDROLE=@V1 AND R2.ROLE=@V2)", statementStr, "Statement"); Assert.AreEqual(new List <BindVar> { new BindVar("V0", movieId, typeof(Guid)), new BindVar("V1", actorType, typeof(Guid)), new BindVar("V2", movieType, typeof(Guid)) }, bindVars, "Bind vars"); }
public void TestAndQueryBuilder() { SingleTestMIA mia1 = TestBackendUtils.CreateSingleMIA("Meta1", Cardinality.Inline, true, true); SingleTestMIA mia2 = TestBackendUtils.CreateSingleMIA("Meta2", Cardinality.Inline, true, true); IList <IFilter> filters = new List <IFilter>(); filters.Add(new LikeFilter(mia1.ATTR_STRING, "%", null)); filters.Add(new LikeFilter(mia2.ATTR_STRING, "%", null)); IFilter filter = new BooleanCombinationFilter(BooleanOperator.And, filters); ICollection <MediaItemAspectMetadata> requiredMIATypes = new List <MediaItemAspectMetadata>(); requiredMIATypes.Add(mia1.Metadata); requiredMIATypes.Add(mia2.Metadata); MIAQueryBuilder builder = new MIAQueryBuilder(MockCore.Management, new List <QueryAttribute>(), null, requiredMIATypes, new List <MediaItemAspectMetadata>(), filter, null, null); string mediaItemIdAlias = null; IDictionary <MediaItemAspectMetadata, string> miamAliases = null; IDictionary <QueryAttribute, string> attributeAliases = null; string statementStr = null; IList <BindVar> bindVars = null; builder.GenerateSqlStatement(out mediaItemIdAlias, out miamAliases, out attributeAliases, out statementStr, out bindVars); //Console.WriteLine("mediaItemIdAlias: {0}", mediaItemIdAlias); //Console.WriteLine("miamAliases: [{0}]", string.Join(",", miamAliases)); //Console.WriteLine("attributeAliases: [{0}]", string.Join(",", attributeAliases)); //Console.WriteLine("statementStr: {0}", statementStr); //Console.WriteLine("bindVars: [{0}]", string.Join(",", bindVars)); Assert.AreEqual("A0", mediaItemIdAlias, "Media item ID alias"); Assert.AreEqual(CreateMIAMAliases(mia1.Metadata, "A1", mia2.Metadata, "A2"), miamAliases, "MIAM aliases"); Assert.AreEqual(new string[] { }, attributeAliases, "Attribute aliases"); Assert.AreEqual("SELECT T0.MEDIA_ITEM_ID A0, T0.MEDIA_ITEM_ID A1, T1.MEDIA_ITEM_ID A2 FROM M_META1 T0 INNER JOIN M_META2 T1 ON T1.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID " + "WHERE (T0.ATTR_STRING LIKE @V0 AND T1.ATTR_STRING LIKE @V1)", statementStr, "Statement"); Assert.AreEqual(new List <BindVar> { new BindVar("V0", "%", typeof(string)), new BindVar("V1", "%", typeof(string)) }, bindVars, "Bind vars"); }
public void TestExternalQueryBuilder() { SingleTestMIA mia1 = TestBackendUtils.CreateSingleMIA("Meta1", Cardinality.Inline, true, true); SingleTestMIA mia2 = TestBackendUtils.CreateSingleMIA("Meta2", Cardinality.Inline, true, true); MockCore.AddMediaItemAspectStorage(ExternalIdentifierAspect.Metadata); string source = "test"; string type = "series"; string id = "123"; // Search using external identifiers BooleanCombinationFilter filter = new BooleanCombinationFilter(BooleanOperator.And, new[] { new RelationalFilter(ExternalIdentifierAspect.ATTR_SOURCE, RelationalOperator.EQ, source), new RelationalFilter(ExternalIdentifierAspect.ATTR_TYPE, RelationalOperator.EQ, type), new RelationalFilter(ExternalIdentifierAspect.ATTR_ID, RelationalOperator.EQ, id), }); ICollection <MediaItemAspectMetadata> requiredMIATypes = new List <MediaItemAspectMetadata>(); requiredMIATypes.Add(mia1.Metadata); requiredMIATypes.Add(mia2.Metadata); MIAQueryBuilder builder = new MIAQueryBuilder(MockCore.Management, new List <QueryAttribute>(), null, requiredMIATypes, new List <MediaItemAspectMetadata>(), filter, null, null); string mediaItemIdAlias = null; IDictionary <MediaItemAspectMetadata, string> miamAliases = null; IDictionary <QueryAttribute, string> attributeAliases = null; string statementStr = null; IList <BindVar> bindVars = null; builder.GenerateSqlStatement(out mediaItemIdAlias, out miamAliases, out attributeAliases, out statementStr, out bindVars); Console.WriteLine("mediaItemIdAlias: {0}", mediaItemIdAlias); Console.WriteLine("miamAliases: [{0}]", string.Join(",", miamAliases)); Console.WriteLine("attributeAliases: [{0}]", string.Join(",", attributeAliases)); Console.WriteLine("statementStr: {0}", statementStr); Console.WriteLine("bindVars: [{0}]", string.Join(",", bindVars)); }