public void TestStaticQueries() { Session session = new Session(new Dictionary <string, string>(), null, null, null); String query; IQueryStatement st; query = "SELECT cmis:name FROM cmis:folder"; st = new QueryStatement(session, query); Assert.AreEqual(query, st.ToQueryString()); query = "SELECT * FROM cmis:document WHERE cmis:createdBy = \'admin\' AND abc:int = 42"; st = new QueryStatement(session, query); Assert.AreEqual(query, st.ToQueryString()); query = "SELECT * FROM cmis:document WHERE abc:test = 'x?z'"; st = new QueryStatement(session, query); st.SetString(1, "y"); Assert.AreEqual(query, st.ToQueryString()); }
public void TestWherePlacholder() { Session session = new Session(new Dictionary <string, string>(), null, null, null); String query; IQueryStatement st; // strings query = "SELECT * FROM cmis:document WHERE abc:string = ?"; st = new QueryStatement(session, query); st.SetString(1, "test"); Assert.AreEqual("SELECT * FROM cmis:document WHERE abc:string = 'test'", st.ToQueryString()); query = "SELECT * FROM cmis:document WHERE abc:string = ?"; st = new QueryStatement(session, query); st.SetString(1, "te'st"); Assert.AreEqual("SELECT * FROM cmis:document WHERE abc:string = 'te\\'st'", st.ToQueryString()); // likes query = "SELECT * FROM cmis:document WHERE abc:string LIKE ?"; st = new QueryStatement(session, query); st.SetStringLike(1, "%test%"); Assert.AreEqual("SELECT * FROM cmis:document WHERE abc:string LIKE '%test%'", st.ToQueryString()); query = "SELECT * FROM cmis:document WHERE abc:string LIKE ?"; st = new QueryStatement(session, query); st.SetStringLike(1, "\\_test\\%blah\\\\blah"); Assert.AreEqual("SELECT * FROM cmis:document WHERE abc:string LIKE '\\_test\\%blah\\\\\\\\blah'", st.ToQueryString()); // contains // *, ? and - are treated as text search operators: 1st level escaping: // none, 2nd level escaping: none // \*, \? and \- are used as literals, 1st level escaping: none, 2nd // level escaping: \\*, \\?, \\- // ' and " are used as literals, 1st level escaping: \', \", 2nd level // escaping: \\\', \\\", // \ plus any other character, 1st level escaping \\ plus character, 2nd // level: \\\\ plus character query = "SELECT * FROM cmis:document WHERE CONTAINS(?)"; st = new QueryStatement(session, query); st.SetStringContains(1, "John's"); Assert.AreEqual("SELECT * FROM cmis:document WHERE CONTAINS('John\\\\\\'s')", st.ToQueryString()); st.SetStringContains(1, "foo -bar"); Assert.AreEqual("SELECT * FROM cmis:document WHERE CONTAINS('foo -bar')", st.ToQueryString()); st.SetStringContains(1, "foo*"); Assert.AreEqual("SELECT * FROM cmis:document WHERE CONTAINS('foo*')", st.ToQueryString()); st.SetStringContains(1, "foo?"); Assert.AreEqual("SELECT * FROM cmis:document WHERE CONTAINS('foo?')", st.ToQueryString()); st.SetStringContains(1, "foo\\-bar"); Assert.AreEqual("SELECT * FROM cmis:document WHERE CONTAINS('foo\\\\-bar')", st.ToQueryString()); st.SetStringContains(1, "foo\\*"); Assert.AreEqual("SELECT * FROM cmis:document WHERE CONTAINS('foo\\\\*')", st.ToQueryString()); st.SetStringContains(1, "foo\\?"); Assert.AreEqual("SELECT * FROM cmis:document WHERE CONTAINS('foo\\\\?')", st.ToQueryString()); st.SetStringContains(1, "\"Cool\""); Assert.AreEqual("SELECT * FROM cmis:document WHERE CONTAINS('\\\\\\\"Cool\\\\\\\"')", st.ToQueryString()); st.SetStringContains(1, "c:\\MyDcuments"); Assert.AreEqual("SELECT * FROM cmis:document WHERE CONTAINS('c:\\\\MyDcuments')", st.ToQueryString()); // ids query = "SELECT * FROM cmis:document WHERE abc:id = ?"; st = new QueryStatement(session, query); st.SetId(1, new ObjectId("123")); Assert.AreEqual("SELECT * FROM cmis:document WHERE abc:id = '123'", st.ToQueryString()); // booleans query = "SELECT * FROM cmis:document WHERE abc:bool = ?"; st = new QueryStatement(session, query); st.SetBoolean(1, true); Assert.AreEqual("SELECT * FROM cmis:document WHERE abc:bool = TRUE", st.ToQueryString()); // numbers query = "SELECT * FROM cmis:document WHERE abc:int = ? AND abc:int2 = 123"; st = new QueryStatement(session, query); st.SetInteger(1, 42); Assert.AreEqual("SELECT * FROM cmis:document WHERE abc:int = 42 AND abc:int2 = 123", st.ToQueryString()); // dateTime query = "SELECT * FROM cmis:document WHERE abc:dateTime = TIMESTAMP ?"; DateTime cal = new DateTime(2012, 2, 2, 3, 4, 5, DateTimeKind.Utc); st = new QueryStatement(session, query); st.SetDateTime(1, cal); Assert.AreEqual("SELECT * FROM cmis:document WHERE abc:dateTime = TIMESTAMP '2012-02-02T03:04:05.000Z'", st.ToQueryString()); st = new QueryStatement(session, query); st.SetDateTime(1, DateTimeHelper.ConvertDateTimeToMillis(cal)); Assert.AreEqual("SELECT * FROM cmis:document WHERE abc:dateTime = TIMESTAMP '2012-02-02T03:04:05.000Z'", st.ToQueryString()); // dateTime Timestamp query = "SELECT * FROM cmis:document WHERE abc:dateTime = ?"; st = new QueryStatement(session, query); st.SetDateTimeTimestamp(1, cal); Assert.AreEqual("SELECT * FROM cmis:document WHERE abc:dateTime = TIMESTAMP '2012-02-02T03:04:05.000Z'", st.ToQueryString()); st = new QueryStatement(session, query); st.SetDateTimeTimestamp(1, DateTimeHelper.ConvertDateTimeToMillis(cal)); Assert.AreEqual("SELECT * FROM cmis:document WHERE abc:dateTime = TIMESTAMP '2012-02-02T03:04:05.000Z'", st.ToQueryString()); query = "SELECT * FROM cmis:document WHERE abc:dateTime IN (?)"; st = new QueryStatement(session, query); st.SetDateTimeTimestamp(1, cal, cal); Assert.AreEqual("SELECT * FROM cmis:document WHERE abc:dateTime " + "IN (TIMESTAMP '2012-02-02T03:04:05.000Z',TIMESTAMP '2012-02-02T03:04:05.000Z')", st.ToQueryString()); }