Sqlite custom collating sequence

General questions that have to do with plugins go here
Post Reply
User avatar
lazi
Posts: 286
Joined: Fri Feb 25, 2011 12:08 am

Sqlite custom collating sequence

Post by lazi » Wed Dec 05, 2012 11: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
[...]

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()                                

User avatar
airsoftsoftwair
Posts: 2482
Joined: Fri Feb 12, 2010 3:33 pm
Location: Germany
Contact:

Re: Sqlite custom collating sequence

Post by airsoftsoftwair » Fri Dec 07, 2012 11:50 pm

I don't really know anything about SQLite. I just did the port :)

User avatar
lazi
Posts: 286
Joined: Fri Feb 25, 2011 12:08 am

Re: Sqlite custom collating sequence

Post by lazi » Sat Dec 15, 2012 1:06 am

Andreas wrote:I don't really know anything about SQLite. I just did the port :)
This is what I feared ;)

User avatar
lazi
Posts: 286
Joined: Fri Feb 25, 2011 12:08 am

Re: Sqlite custom collating sequence

Post by lazi » Sat Dec 15, 2012 1:07 am

Here is the solution for the custon collation:

Code: Select all

;--------------------------------
;-- Hungarian Custom collation --
;--------------------------------

@REQUIRE "sqlite3"






Function collate(s1,s2)
	s1=LowerStr(s1)
	s2=LowerStr(s2)
 
    s1=ReplaceStr(s1,"a","aa")
    s1=ReplaceStr(s1,"e","ea")
    s1=ReplaceStr(s1,"u","ua")
    s1=ReplaceStr(s1,"o","oa")
    s1=ReplaceStr(s1,"i","ia")

    s2=ReplaceStr(s2,"a","aa")
    s2=ReplaceStr(s2,"e","ea")
    s2=ReplaceStr(s2,"u","ua")
    s2=ReplaceStr(s2,"o","oa")
    s2=ReplaceStr(s2,"i","ia")

    s1=ReplaceStr(s1,"á","ab")
    s1=ReplaceStr(s1,"ö","oc")
    s1=ReplaceStr(s1,"é","eb")
    s1=ReplaceStr(s1,"ü","uc")
    s1=ReplaceStr(s1,"ú","ub")
    s1=ReplaceStr(s1,"û","ud")
    s1=ReplaceStr(s1,"õ","od")
    s1=ReplaceStr(s1,"ó","ob")
    s1=ReplaceStr(s1,"í","ib")


    s2=ReplaceStr(s2,"á","ab")
    s2=ReplaceStr(s2,"ö","oc")
    s2=ReplaceStr(s2,"é","eb")
    s2=ReplaceStr(s2,"ü","uc")
    s2=ReplaceStr(s2,"ú","ub")
    s2=ReplaceStr(s2,"û","ud")
    s2=ReplaceStr(s2,"õ","od")
    s2=ReplaceStr(s2,"ó","ob")
    s2=ReplaceStr(s2,"í","ib")


	if s1=s2
	  return(0)
	ElseIf s1<s2
	  return(-1)
	else
	  return(1)
	endif
EndFunction

    db = sqlite3.open_memory()
    db:create_collation("CINSENS",collate)
    db:exec([[
      CREATE TABLE partner(
      part_id INTEGER PRIMARY KEY,
      part_name text COLLATE CINSENS,
      part_csopid,
      part_date);
      
    ]])


p={
  "Abrafax Kft",
  "Zöldség Butik",
  "Fókabél",
  "Sarki fûszeres",
  "Ûzöt vadként",
  "Értlemetlen programsorok",
  "Elmentek hazulról bt.",
  "Borzalmas Bt",
  "Ábraterv Bt",
  "Fõkábel",
  "Asztalkezelõ"
  }

For i=0 To ListItems(p)-1
   stmt = db:prepare([[ INSERT INTO partner VALUES (NULL, :name, :csopid, :date) ]])

          stmt:bind_names({ name = p[i]})
          stmt:dostep()
          stmt:reset()
Next


    For row in db:nrows("SELECT * FROM partner ORDER by part_name")
      NPrint(row.part_name)
    Next


WaitLeftMouse                            


User avatar
airsoftsoftwair
Posts: 2482
Joined: Fri Feb 12, 2010 3:33 pm
Location: Germany
Contact:

Re: Sqlite custom collating sequence

Post by airsoftsoftwair » Sat Dec 15, 2012 12:28 pm

This is what I feared ;)
But no worries. If you need help with the plugin, just ask the authors of luasqlite3... they should be able to help :) Also there are many people who know how to use it on the lua mailing list. You just have to adapt the syntax a little so that it fits to Hollywood :)

Post Reply