Sqlite custom collating sequence
Posted: Wed Dec 05, 2012 10:15 pm
Hi!
Could somebody with sqlite knlowledge explain me the following snippet from the tests-sqlite3.hws?
I'd like to able to sort international strings with correct order.
Currently I got the order like this:
A
B
C
[...]
Á
the correct order would be:
A
Á
B
[...]
Could somebody with sqlite knlowledge explain me the following snippet from the tests-sqlite3.hws?
I'd like to able to sort international strings with correct order.
Currently I got the order like this:
A
B
C
[...]
Á
the correct order would be:
A
Á
B
[...]
Code: Select all
;----------------------------
;-- Test for collation fun --
;----------------------------
colla = {}
function colla:setup()
local function collate(s1,s2)
; if p then print("collation callback: ",s1,s2) end
s1=lowerstr(s1)
s2=lowerstr(s2)
if s1=s2
return(0)
elseif s1<s2
return(-1)
else
return(1)
endif
endfunction
self.db = my_assert( sqlite3.open_memory() )
assert_nil(self.db:create_collation("CINSENS",collate))
self.db:exec([[
CREATE TABLE test(id INTEGER PRIMARY KEY,content COLLATE CINSENS);
INSERT INTO test VALUES(NULL,'hello world');
INSERT INTO test VALUES(NULL,'Buenos dias');
INSERT INTO test VALUES(NULL,'HELLO WORLD');
INSERT INTO test VALUES(NULL,'Guten Tag');
INSERT INTO test VALUES(NULL,'HeLlO WoRlD');
INSERT INTO test VALUES(NULL,'Bye for now');
]])
endfunction
function colla:teardown()
assert_number( self.db:close() )
endfunction
function colla:test()
;--for row in db:nrows('SELECT * FROM test') do
;-- print(row.id,row.content)
;--end
local n = 0
for row in self.db:nrows("SELECT * FROM test WHERE content=\"hElLo wOrLd\"")
;-- print(row.id,row.content)
assert_equal (lowerstr(row.content), "hello world")
n = n + 1
next
assert_equal (n, 3)
endfunction
debugprint("#### Running 'Collation Tests' (1 Tests)...")
colla:setup()
colla:test()
colla:teardown()