> Unit Tests: > I would isolate the comparison in a core function, and primarily test that > core function
Main application is written i C#. Sqlite and extensions are in dll, hence the main code cannot directly access the collation function. While we have low-level tests that are occasionally run, we need something that can be run and supervised regularly. That's way I came with the idea to test the outcome of high-level SQL command such as "SELECT ? < ? COLLATE NOCASE". At the bottom there is the class used for that purpose. You have to call TestCollation() with supplied string array. The function tests basic properties - reflexivity, symmetry, transitivity - and throws an exception in case of any violation. There are 2 problems with this attitude: a) String selection b) The algoritm is heavily computationally demanding I decided to apply this systematical approach when we got a sample database with a corrupted index. However, the test did not discover anything. >assert( cmp("Ape", "monkey") < 0); Perhaps I should add tests comparing specific string instances as you suggest. I would invite any advice concerning good test samples. This is the above mentioned code: internal class MyComparer : IDisposable { private SqliteCommand cmdLT, cmdGT, cmdEQ; private DbParameter parLT1, parLT2, parGT1, parGT2, parEQ1, parEQ2; public MyComparer(SqliteConnection cnn) { cmdLT = cnn.CreateCommand(); cmdLT.CommandText = "SELECT ? < ? COLLATE NOCASE;"; parLT1 = cmdLT.CreateParameter(); parLT1.DbType = DbType.String; cmdLT.Parameters.Add(parLT1); parLT2 = cmdLT.CreateParameter(); parLT2.DbType = DbType.String; cmdLT.Parameters.Add(parLT2); cmdGT = cnn.CreateCommand(); cmdGT.CommandText = "SELECT ? > ? COLLATE NOCASE;"; parGT1 = cmdGT.CreateParameter(); parGT1.DbType = DbType.String; cmdGT.Parameters.Add(parGT1); parGT2 = cmdGT.CreateParameter(); parGT2.DbType = DbType.String; cmdGT.Parameters.Add(parGT2); cmdEQ = cnn.CreateCommand(); cmdEQ.CommandText = "SELECT ? == ? COLLATE NOCASE;"; parEQ1 = cmdEQ.CreateParameter(); parEQ1.DbType = DbType.String; cmdEQ.Parameters.Add(parEQ1); parEQ2 = cmdEQ.CreateParameter(); parEQ2.DbType = DbType.String; cmdEQ.Parameters.Add(parEQ2); } bool LT(string s1, string s2) { parLT1.Value = s1; parLT2.Value = s2; return (Int64)cmdLT.ExecuteScalar() == 1; } bool GT(string s1, string s2) { parGT1.Value = s1; parGT2.Value = s2; return (Int64)cmdGT.ExecuteScalar() == 1; } bool EQ(string s1, string s2) { parEQ1.Value = s1; parEQ2.Value = s2; return (Int64)cmdEQ.ExecuteScalar() == 1; } public void Test(string s) { // Test s==s if (!EQ(s, s)) throw new Exception(String.Format("NOCASE collate ['{0}', '{0}'] fails", s)); } public void Test(string s1, string s2) { // Exactly one of these relations must happen: s1<s2, s1==s2, s1>s2. int x = 0; if( LT(s1, s2)) x++; if( GT(s1, s2)) x++; if( EQ(s1, s2)) x++; if (x != 1) throw new Exception(String.Format("NOCASE collate ['{0}', '{1}'] fails", s1, s2)); } public void Test(string s1, string s2, string s3) { if( EQ(s1,s2) || EQ(s1,s3) || EQ(s1,s3)) return; // cant test transitivity bool lt12 = LT(s1, s2); bool lt13 = LT(s1, s3); bool lt23 = LT(s2, s3); bool lt21 = !lt12; bool lt31 = !lt13; bool lt32 = !lt23; if (lt12 && lt23) { if (!lt13) goto labelError; } if (lt13 && lt32) { if (!lt12) goto labelError; } if (lt21 && lt13) { if (!lt23) goto labelError; } if (lt23 && lt31) { if (!lt21) goto labelError; } if (lt31 && lt12) { if (!lt32) goto labelError; } if (lt32 && lt21) { if (!lt31) goto labelError; } return; labelError: throw new Exception(String.Format("NOCASE collation not transitive for ['{0}', '{1}', '{2}']", s1, s2, s3)); } public void Dispose() { if (cmdLT != null) { cmdLT.Dispose(); cmdLT = null; } if (cmdGT != null) { cmdGT.Dispose(); cmdGT = null; } if (cmdEQ != null) { cmdEQ.Dispose(); cmdEQ = null; } } } void TestCollation( string[] texts ) { using (MyComparer comparer = new MyComparer(_cnn)) { int n = texts.Length; foreach (string t in texts) comparer.Test(t); for (int i = 0; i < n; ++i) for (int j = i + 1; j < n; ++j) comparer.Test(texts[i], texts[j]); for (int i = 0; i < n; ++i) for (int j = i + 1; j < n; ++j) for (int k = j + 1; k < n; ++k) comparer.Test(texts[i], texts[j], texts[k]); } } -- View this message in context: http://sqlite.1065341.n5.nabble.com/Collation-advice-tp70668p70698.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users