On 5 August 2011 10:55, Dan Kennedy <danielk1...@gmail.com> wrote: > > On 08/05/2011 02:58 PM, Martin Gill wrote: > > > > CREATE VIRTUAL TABLE full_text USING FTS3 (id,text) > > INSERT INTO full_text VALUES ('1', 'I have a foréign character.') > > SQLite does not recognize upper/lower-case mappings for non-ASCII > characters. That is why querying for "HAVE" works but "FORÉIGN" > does not. If you need upper/lower case mapping for non-ASCII characters, > you will need to use the ICU extension. >
That may be part of the problem. System.Data.SQlite isn't compiled with ICU, so I cannot check that quickly. I do though get exactly the same odd behaviour using the porter tokenizer. > > SELECT id, text FROM full_text WHERE NOT(text = @p1) AND text MATCH @p2 > > > > p1 = "foréign" > > p2 = "FORÉIGN" > > > > Not using parametrized SQL also returns a correct result. > > I don't see how either the query above or a version that uses SQL > literals could be returning any rows.. Not with the "CREATE VIRTUAL > TABLE" as it stands above. Unless you specify the ICU tokenizer (or > some other tokenizer that understands non-ASCII upper/lower case), > "FORÉIGN" should not match the row in table "full_text". > Except that it does. While I gave the unicode value, e-acute is ASCII 130, and capital e-acute is ASCII 144. That puts them on the extended ascii chart ( 128 < c <= 255 ) and I believe all western European codepages will have the characters. Create the database as indicated and run SELECT id, text FROM full_text WHERE NOT(text = 'foréign') AND text MATCH 'FORÉIGN' and you'll get a result. At least on my British installation of Windows. Here's the C# code that demonstrates the issue. My initial attachment seems to have been cut off: ============================================ using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SQLite; using System.Reflection; using System.IO; namespace sqlitetests { class Program { static void Main(string[] args) { SQLiteConnection connection = CreateOpenDB(); Test1(connection); Test2(connection); Test3(connection); Test4(connection); Console.ReadKey(); } static SQLiteConnection CreateOpenDB() { if (File.Exists("test.sq3")) { File.Delete("test.sq3"); } SQLiteConnectionStringBuilder csb = new SQLiteConnectionStringBuilder(); csb.DataSource = @"test.sq3"; csb.Version = 3; SQLiteConnection connection = new SQLiteConnection(csb.ToString()); connection.Open(); SQLiteCommand command = connection.CreateCommand(); command.CommandText = "CREATE VIRTUAL TABLE full_text USING FTS3 (id,text, tokenize=porter)"; command.ExecuteNonQuery(); command = connection.CreateCommand(); command.CommandText = "INSERT INTO full_text VALUES ('1', 'I have a foréign character.')"; command.ExecuteNonQuery(); return connection; } static void Test1(SQLiteConnection connection) { SQLiteCommand theCommand = new SQLiteCommand(connection); string tableName = "full_text"; theCommand.CommandText = string.Format("SELECT id, text FROM {0} WHERE NOT(text = @source) AND text MATCH @query", tableName); StringBuilder sb = new StringBuilder(); string query = "FORÉIGN"; string sourceText = "foréign"; SQLiteParameter param = theCommand.CreateParameter(); param.Value = query; param.ParameterName = "query"; param.DbType = System.Data.DbType.String; theCommand.Parameters.Add(param); param = theCommand.CreateParameter(); param.Value = sourceText.TrimEnd(); param.ParameterName = "source"; param.DbType = System.Data.DbType.String; theCommand.Parameters.Add(param); SQLiteDataReader reader = theCommand.ExecuteReader(); Console.WriteLine("{0} : {1}", MethodInfo.GetCurrentMethod().Name, reader.HasRows); } static void Test2(SQLiteConnection connection) { SQLiteCommand theCommand = new SQLiteCommand(connection); string tableName = "full_text"; theCommand.CommandText = string.Format("SELECT id, text FROM {0} WHERE NOT(text = @s) AND text MATCH @q", tableName); StringBuilder sb = new StringBuilder(); string query = "FORÉIGN"; string sourceText = "foréign"; SQLiteParameter param = theCommand.CreateParameter(); param.Value = query; param.ParameterName = "q"; param.DbType = System.Data.DbType.String; theCommand.Parameters.Add(param); param = theCommand.CreateParameter(); param.Value = sourceText.TrimEnd(); param.ParameterName = "s"; param.DbType = System.Data.DbType.String; theCommand.Parameters.Add(param); SQLiteDataReader reader = theCommand.ExecuteReader(); Console.WriteLine("{0} : {1}", MethodInfo.GetCurrentMethod().Name, reader.HasRows); } static void Test3(SQLiteConnection connection) { SQLiteCommand command = new SQLiteCommand(connection); command.CommandText = string.Format("SELECT id, text FROM {0} WHERE NOT(text = @p1) AND text MATCH @p2", "full_text"); SQLiteParameter param1 = command.CreateParameter(); param1.Value = "FORÉIGN"; param1.ParameterName = "p1"; param1.DbType = System.Data.DbType.String; command.Parameters.Add(param1); SQLiteParameter param2 = command.CreateParameter(); param2.Value = "foréign"; param2.ParameterName = "p2"; param2.DbType = System.Data.DbType.String; command.Parameters.Add(param2); SQLiteDataReader reader = command.ExecuteReader(); Console.WriteLine("{0} : {1}", MethodInfo.GetCurrentMethod().Name, reader.HasRows); } static void Test4(SQLiteConnection connection) { SQLiteCommand theCommand = new SQLiteCommand(connection); string tableName = "full_text"; theCommand.CommandText = string.Format("SELECT id, text FROM {0} WHERE NOT(text = @source) AND text MATCH @query", tableName); StringBuilder sb = new StringBuilder(); string query = "HAVE"; string sourceText = "have"; SQLiteParameter param = theCommand.CreateParameter(); param.Value = query; param.ParameterName = "query"; param.DbType = System.Data.DbType.String; theCommand.Parameters.Add(param); param = theCommand.CreateParameter(); param.Value = sourceText.TrimEnd(); param.ParameterName = "source"; param.DbType = System.Data.DbType.String; theCommand.Parameters.Add(param); SQLiteDataReader reader = theCommand.ExecuteReader(); Console.WriteLine("{0} : {1}", MethodInfo.GetCurrentMethod().Name, reader.HasRows); } } } ============================================ -- Regards, Martin _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users