[sqlite-dev] Adding ipv4 string conversion as built-in functions

[Paul Stuart]

I know that one can add custom functions through extensions, but that also limits portability of the SQL (those functions only work on systems that have the extensions loaded).

I have a system using sqlite that does IP management and requires conversion of the IPv4 address to an integer for proper sorting (lexical sorting won't work here).

MySQL has  INET_ATON/INET_NTOA

PostgreSQL has Network Address Types

Oracle / MS SQL have custom functions/procedures to allow functionality within sql code itself.

Would there be an interest in adding the INET_ATON/INET_NTOA functions (with whatever names work) as built-in functions? I would put it's helpfulness as being at least equal to "soundex"; a Google search for "sqlite convert ip to integer" shows that I'm not the only one interested in this functionality.

I would be honored to contribute the code to do this if there's any interest by the core team.

Cheers,

Paul Stuart

[David Butler]

I store IPs as 16 byte blobs (IPv4 in IPv6 syntax). You may do it differently.

And, sorry, I don’t care about Windows.

This is contributed under the same license as SQLite.

ntop.h:

void ntop(sqlite3_context *, int, sqlite3_value **);

ntop.c:

#include <stdio.h>

#include <string.h>

#include <sys/socket.h>

#include <arpa/inet.h>

#include "sqlite3.h"

#include "ntop.h"

void

ntop(

  sqlite3_context *context

 ,int argc

 ,sqlite3_value **argv

){

  static unsigned char i4[] = {0x00,0x00,0x00,0x00

                              ,0x00,0x00,0x00,0x00

                              ,0x00,0x00,0xff,0xff};

  const unsigned char *b;

  char *r;

  int i;

  (void)argc;

  i = sqlite3_value_bytes(argv[0]);

  if ((i == 4 || i == 16)

   && (b = sqlite3_value_blob(argv[0]))

   && (r = sqlite3_malloc(40))) {

    if (i == 4)

      b = (const unsigned char *)inet_ntop(AF_INET, b, r, 40);

    else if (!memcmp(b, i4, sizeof(i4)))

      b = (const unsigned char *)inet_ntop(AF_INET, b + 12, r, 40);

    else

      b = (const unsigned char *)inet_ntop(AF_INET6, b, r, 40);

    if (b) {

      r[39] = '\0'; /* strlen safety */

      sqlite3_result_text(context, r, strlen(r), sqlite3_free);

    } else

      sqlite3_free(r);

  }

}

To use them do this after init:

sqlite3_create_function(db, "ntop", 1, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, ntop, 0, 0);

_______________________________________________
sqlite-dev mailing list
sqlit...@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-dev

[Bob Friesenhahn]

For my own work, I have created IPv4 and IPv6 collation functions.
There are also IPv6 address and IPv6 prefix normalization (standard
compaction) functions plus functions to test if a supplied value is a
valid IPv4 address, IPv4 netmask, IPv6 address, and IPv6 prefix (for
use in triggers).

When dealing with addresses it is useful to have a wide variety of
support functions.

Bob
--
Bob Friesenhahn
bfri...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer, http://www.GraphicsMagick.org/