Sqlite custom collating sequence

General questions that have to do with plugins go here

Sqlite custom collating sequence

Postby 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
lazi
 
Posts: 268
Joined: Fri Feb 25, 2011 12:08 am

Re: Sqlite custom collating sequence

Postby airsoftsoftwair » Fri Dec 07, 2012 11:50 pm

I don't really know anything about SQLite. I just did the port :)
User avatar
airsoftsoftwair
 
Posts: 2233
Joined: Fri Feb 12, 2010 3:33 pm
Location: Germany

Re: Sqlite custom collating sequence

Postby 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: 268
Joined: Fri Feb 25, 2011 12:08 am

Re: Sqlite custom collating sequence

Postby 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
lazi
 
Posts: 268
Joined: Fri Feb 25, 2011 12:08 am

Re: Sqlite custom collating sequence

Postby 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 :)
User avatar
airsoftsoftwair
 
Posts: 2233
Joined: Fri Feb 12, 2010 3:33 pm
Location: Germany


Return to General plugin questions

Who is online

Users browsing this forum: No registered users and 2 guests