> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
