[sqlite] Order by creating function is not sorting for input order and alphabet

[ChingChang Hsiao]

The purpose of function antok is that solve the problem of "order by cli_id".

Function antok converts,

X86-1  -> x186-01
X86-2  -> x186-02
X86-10 -> x186-110
X86-111 -> x186-2111
X86-2222 -> x186-32222
1.1/12-ds3 -> 01.01/112-ds03

Examples,
Input cli_id   x86-2, x86-10, x86-1

Order by cli_id
X86-1
X86-10
X86-2

Order by antok(cli_id)
X86-2
X86-1
X86-10

Antok solves the problem of x86-1,x86-10 sorting, but it didn't sort x86-2, 
x86-1(input order).

My solution is,

order by antok(cli_id) asc,cli_id asc

X86-1
X86-2
X86-10


But It solves the same category, there are still problems for alphabet shown as 
below.

0.1
0.2
0.3
0.4
0.5
x86-1
x86-2
chdlc-1
chdlc-2
chdlc-3
x86-10

It should be,

0.1
0.2
0.3
0.4
0.5
chdlc-1
chdlc-2
chdlc-3
x86-1
x86-2
x86-10

Do you know why x86-1,x86-2 jump ahead chdlc-1 when I use "order by 
antok(cli_id)" or "order by antok(cli_id) asc,cli_id asc"?


ChingChang

Here are the source code shown as below.

int32
DbHandle::registerOvnFuncs( sqlite3* db ) {

  // Add a alpha numeric token generator
  sqlite3_create_function(db, "antok", 1, SQLITE_UTF8, NULL,
                                                  &dbAnTokenFunc, NULL, NULL);
  return 0;
}

static void
dbAnTokenFunc( sqlite3_context *context, int argc, sqlite3_value **argv )
{
  assert( argc==1 );
  switch( sqlite3_value_type(argv[0]) ){
  case SQLITE_INTEGER:
    sqlite3_result_int64( context, sqlite3_value_int64(argv[0]) );
    break;
  case SQLITE_NULL:
    sqlite3_result_null( context );
    break;
  case SQLITE_TEXT:
  default:
    SqlSortHelper sqlSortHelper;
    string token;
    token = sqlSortHelper.GetAlphaNumericOrderToken( 
(char*)sqlite3_value_text(argv[0]) );
    sqlite3_result_text( context, token.c_str(), token.length(), NULL );
    break;
  }
}

#define POTENTIAL_MATCHES 1

string SqlSortHelper::GetAlphaNumericOrderToken( string input ) {
  regex_t re;
  regmatch_t pmatch[POTENTIAL_MATCHES];
  string token;
  uint32 pos = 0;
  char digits[10] = "012345678";

  memset( pmatch, -1, sizeof(pmatch) );
  regcomp( &re, "[0-9]+", REG_EXTENDED);
  while (pos < input.length())
  {
    regexec( &re, input.c_str()+pos, 1, pmatch, 0);
    if ( pmatch[0].rm_so != -1 )
    {
        token.append( (input.c_str() + pos), pmatch[0].rm_so);
        token.push_back( digits[((pmatch[0].rm_eo-pmatch[0].rm_so)-1)] );
        token.append( (input.c_str() + pos + pmatch[0].rm_so), pmatch[0].rm_eo 
- pmatch[0].rm_so );
        pos = pos + pmatch[0].rm_eo;
    }
    else
    {
        if (pos == 0) {
            regfree( &re);
            return input;
                }
        else
            break;
    }
  }
  if (pos < input.length())
    token.append( (input.c_str() + pos), input.length()-pos );
  regfree( &re);
  return token;
}

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users