Re: [sqlite] Custom collation method works for comparison, not for ORDER BY

[Niall O'Reilly]

On 6 Dec 2012, at 14:14, Igor Tandetnik wrote:

> Your code assumes, in several places, that strings passed to collation 
> function are NUL-terminated. They don't have to be - that's why lengths are 
> also passed. I think this may be causing the problem you are seeing: when the 
> string comes from a literal (as in x < '' ) it just may happen to be 
> NUL-terminated, but when it comes straight from the database, it may not be, 
> and you are cheerfully reading garbage past the end of buffer.

Thanks for your analysis and helpful comments.

I'll need to take care to make a NUL-terminated copy of each source 
string,
as inet_pton doesn't take a count argument.

> Your test program, of course, always happens to pass NUL-terminated strings.

Doh! 8-)

/Niall

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

Re: [sqlite] Custom collation method works for comparison, not for ORDER BY

[Igor Tandetnik]

Niall O'Reilly  wrote:
> This and my Makefile follow below, as I understand attachments
> are not supported on this list.

Your code assumes, in several places, that strings passed to collation function 
are NUL-terminated. They don't have to be - that's why lengths are also passed. 
I think this may be causing the problem you are seeing: when the string comes 
from a literal (as in x < '' ) it just may happen to be NUL-terminated, but 
when it comes straight from the database, it may not be, and you are cheerfully 
reading garbage past the end of buffer.

Your test program, of course, always happens to pass NUL-terminated strings.
-- 
Igor Tandetnik

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

Re: [sqlite] Custom collation method works for comparison, not for ORDER BY

[Niall O'Reilly]

On 6 Dec 2012, at 05:21, Dan Kennedy wrote:

> It still seems likely that the collation returns the wrong result
> some circumstances. Posting the code for it is probably the right
> thing to do.

Thanks for the encouragement!

I've used conditionally compiled sections so that the same
code can be used to generate either an SQLite3 extension
or a stand-alone executable for testing and demonstration.
This demo compiles and runs under Ubuntu and OSX, 
and presents the sorted strings in the expected order.
So far, I haven't tried to compile the extension under OSX.

This and my Makefile follow below, as I understand attachments
are not supported on this list.

/Niall

-- Makefile -- beware conversion of TABs --
# inlude for sqlite3
# replace by the directory that contains sqlite3ext.h
INCLUDE = -I/usr/include

ARCH= 

SOEXT   = so

CFLAGS  = 

so_files = libsqliteipv6.$(SOEXT)

all: $(so_files)

.PHONY: extend demo clean

sqlite3-ipv6-ext.o : sqlite3-ipv6-ext.c

demo: ip-extension-demo
ip-extension-demo: ip-extension.c
$(CC) $< -o $@ -lsqlite3

extend: ip-extension.so
ip-extension.so: CFLAGS=$(INCLUDE) -DEXTEND_SQLITE3 -fPIC -fno-stack-protector 
$(ARCH)
ip-extension.so: ip-extension.o
$(LD) -shared -o $@ $<

clean :
rm -f *.o a.out core core.* *% *~ *.$(SOEXT)

-- ip-extension.c --
/*
** Parse an IP address, prefix, or range
*/

#include 
#include 
#include 
#include 
#include 

#ifdef EXTEND_SQLITE3
#include 
SQLITE_EXTENSION_INIT1
#endif

#define WO_DATA_SZ 256

struct work_object 
{
  size_t size;
  struct {size_t length; unsigned char *data;} string;
  struct {size_t length; unsigned char *data;} source;
  unsigned char wire[sizeof(struct in6_addr)];
  unsigned char type;
};

struct work_object*
new_work_object() 
{
  struct work_object *this;
  unsigned char *p;
  p = sqlite3_malloc(WO_DATA_SZ + sizeof(struct work_object));
  this = p;
  this->type = 255;
  this->size = WO_DATA_SZ;
  this->string.length = 0;
  this->string.data = p + sizeof(struct work_object);
  this->string.data[0] = '\0';
  this->string.data[WO_DATA_SZ - 1] = '\0';
  this->source.length = 0;
  this->source.data = NULL;
  return this;
}

struct work_object*
prime_work_object(struct work_object *this,
 const int n,
 const void *source
 )
{
  struct work_object *p;
  size_t sz;
  p = this ? this : new_work_object();
  p->source.data = source;
  p->source.length = n;
  p->type = 255;
  if (inet_pton(AF_INET6, p->source.data, p->wire) > 0) {
p->type = 6;
  }
  else if (inet_pton(AF_INET, p->source.data, p->wire) > 0) {
p->type = 4;
  }
  return p;
}

void
stringify_work_object(struct work_object *this)
{
  this->string.length = 0;
  this->string.data[0] = '\0';
  this->string.data[WO_DATA_SZ - 1] = '\0';
  if (this->type == 6) {
inet_ntop(AF_INET6, this->wire, this->string.data, this->size - 1);
this->string.length = strlen(this->string.data);
  }
  else if (this->type == 4) {
inet_ntop(AF_INET, this->wire, this->string.data, this->size - 1);
this->string.length = strlen(this->string.data);
  }
}

int
compare_work_objects(struct work_object *this, struct work_object *that) 
{
  if (this->type != that->type) 
return this->type - that->type;
  if (this->type == 6)
return memcmp(this->wire, that->wire, sizeof(struct in6_addr));
  if (this->type == 4)
return memcmp(this->wire, that->wire, sizeof(struct in_addr));
  return strncmp(this->source.data, that->source.data,
 1 +
 (this->source.length < that->source.length) ?
 this->source.length : that->source.length);
}

#ifdef EXTEND_SQLITE3

/* SQLite3 extension interface here */

int 
compare_ipaddrs
(void *q,   /* required by API: not used */
 int na, const void *pa,/* a: length, string */
 int nb, const void *pb /* b: length, string */
)
{
  struct work_object *a, *b;
  int v;

  a = prime_work_object(NULL, na, pa);
  b = prime_work_object(NULL, nb, pb);
  v = compare_work_objects(a, b);
  sqlite3_free(a);
  sqlite3_free(b);
  
  return v;
}

/* SQLite invokes this routine once when it loads the extension.
** Create new functions, collating sequences, and virtual table
** modules here.  This is usually the only exported symbol in
** the shared library.
*/
int 
sqlite3_extension_init
(
 sqlite3 *db,
 char **pzErrMsg,
 const sqlite3_api_routines *pApi)
{
  SQLITE_EXTENSION_INIT2(pApi)
;
  /*
sqlite3_create_function(db, "displayip", 1,
  SQLITE_UTF8, 0, display1, 0, 0);
  */

  sqlite3_create_collation(
   db,  /* sqlite3* */
   "ipaddress", /* const char *zName */
   SQLITE_UTF8, /* int eTextRep */
   NULL,/* void *pArg -- not used */
   compare_ipaddrs
 

Re: [sqlite] Custom collation method works for comparison, not for ORDER BY

[Dan Kennedy]

On 12/06/2012 06:11 AM, Niall O'Reilly wrote:

On 05/12/12 21:12, Clemens Ladisch wrote:

Do these queries give the correct result?

 select '100A' collate ipaddress<  '127.0.0.1';
 select '100A' collate ipaddress<  ' ABCD';

I.e., does the collation function actually work?


Thanks for the helpful suggestions.
I wish I had thought of something so simple.

Here's what I get:

sqlite>  select '100A' collate ipaddress<  '127.0.0.1';
0
sqlite>  select '100A' collate ipaddress<  ' ABCD';
0
sqlite>  select '127.0.0.1' collate ipaddress<  '::1';
1
sqlite>  select '::1' collate ipaddress = '0::1';
1
sqlite>  select 'dead:beef::' collate ipaddress = 'DEAD:BEEF::';
1
sqlite>

These results match what I intended the collation
function to do.  It appears to work, including
recognizing alternative notations for the same IPv6
address as equivalent.

Similar tests using BETWEEN also work as intended.


It still seems likely that the collation returns the wrong result
some circumstances. Posting the code for it is probably the right
thing to do.




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

Re: [sqlite] Custom collation method works for comparison, not for ORDER BY

[Niall O'Reilly]

On 05/12/12 21:12, Clemens Ladisch wrote:
> Do these queries give the correct result?
> 
> select '100A' collate ipaddress < '127.0.0.1';
> select '100A' collate ipaddress < ' ABCD';
> 
> I.e., does the collation function actually work?

Thanks for the helpful suggestions.
I wish I had thought of something so simple.

Here's what I get:

sqlite> select '100A' collate ipaddress < '127.0.0.1';
0
sqlite> select '100A' collate ipaddress < ' ABCD';
0
sqlite> select '127.0.0.1' collate ipaddress < '::1';
1
sqlite> select '::1' collate ipaddress = '0::1';
1
sqlite> select 'dead:beef::' collate ipaddress = 'DEAD:BEEF::';
1
sqlite>

These results match what I intended the collation
function to do.  It appears to work, including
recognizing alternative notations for the same IPv6
address as equivalent.

Similar tests using BETWEEN also work as intended.


Thanks again.
/Niall

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

Re: [sqlite] Custom collation method works for comparison, not for ORDER BY

[Clemens Ladisch]

Niall O'Reilly wrote:
>   I've coded up a custom collation method, and am seeing
>   what looks like strange behaviour.  The method is
>   intended to sort IPv4 addresses before IPv6 addresses,
>   and IPv6 addresses before other text strings.
>
> select rowid, * from foo order by x collate ipaddress;
> 6|
> 5| ABCD
> 3|100A
> 2|127.0.0.1
> 4|128A
> 1|::1

Do these queries give the correct result?

select '100A' collate ipaddress < '127.0.0.1';
select '100A' collate ipaddress < ' ABCD';

I.e., does the collation function actually work?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Custom collation method works for comparison, not for ORDER BY

[Niall O'Reilly]

Hello.

I've coded up a custom collation method, and am seeing
what looks like strange behaviour.  The method is
intended to sort IPv4 addresses before IPv6 addresses,
and IPv6 addresses before other text strings.  It's
just a little wrapping around inet_ptoa and memcmp.

I'm running a back version of sqlite3, which is
apparently the latest bundled version known to the
standard software maintenance utility on the platform
I'm using (apt-get on Ubuntu precise).

I'ld like some advice, please.

First, does what follows appear strange to anyone else?

.version
SQLite 3.7.9 2011-11-01 00:52:41 c7c6050ef060877ebe77b41d959e9df13f8c9b5e
select load_extension('./ip-extension.so');

create table foo (x collate ipaddress);
insert into foo values('::1');
insert into foo values('127.0.0.1');
select rowid, * from foo;
1|::1
2|127.0.0.1
select rowid, * from foo order by x;
2|127.0.0.1
1|::1
insert into foo values('100A');
insert into foo values('128A');
insert into foo values(' ABCD');
insert into foo values('');
select rowid, * from foo;
1|::1
2|127.0.0.1
3|100A
4|128A
5| ABCD
6|
select rowid, * from foo where x < '' order by x;
2|127.0.0.1
1|::1
select rowid, * from foo where x > '' order by x;
5| ABCD
3|100A
4|128A
select rowid, * from foo order by x;
6|
5| ABCD
3|100A
2|127.0.0.1
4|128A
1|::1
select rowid, * from foo order by x collate ipaddress;
6|
5| ABCD
3|100A
2|127.0.0.1
4|128A
1|::1

What I find strange is that comparisons against the
empty string behave as expected, but ORDER BY sorts
the values as if no custom collation had been specified,
even where this is explicit in the SELECT statement.

Next, should I best just download the 3.7.14 tarball
and build an up-to-date library before anything else?

I feel that sending my code at this stage would be
to presume too much on people's interest.


Thanks in advance.
Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users