meta données pour cette page
Ceci est une ancienne révision du document !
sqlite ipv4 extension
date this document was last edited : 2008-09-25
date the C source was last edited : 2008-09-24
you should find the latest version of this file here : http://schplurtz.free.fr/schplurtziel/sqlite3-ipv4-ext
What is it ?
This is an extension for sqlite3. that adds one single function named isinnet
to the set of SQL functions. The isinnet
function deals with ipv4 addresses.
When you use that extension, you can test if an ip address is within a network, or even if two ip are in the same subnet, just by using SQL commands like these :
- try-me.sql
SELECT ISINNET( '172.16.1.193', '172.16.1.0', 24 ); SELECT ISINNET( '172.16.1.193', '172.16.1.0/24' ); -- ==> 1 SELECT ISINNET( '172.16.1.193', '172.16.1.0', 25 ); SELECT ISINNET( '172.16.1.193', '172.16.1.0/25' ); -- ==> 0 SELECT ISINNET( '172.16.1.193', '172.16.1.0', '255.255.255.0' ); SELECT ISINNET( '172.16.1.193', '172.16.1.0/255.255.255.0' ); -- ==> 1 SELECT ISINNET( '172.16.1.193', '172.16.1.0', '255.255.255.128' ); SELECT ISINNET( '172.16.1.193', '172.16.1.0/255.255.255.128' ); -- ==> 0 CREATE TABLE ip_add ( ip VARCHAR( 16 ) ); INSERT INTO ip_add VALUES('172.16.1.40'); INSERT INTO ip_add VALUES('172.16.1.93'); INSERT INTO ip_add VALUES('172.16.1.204'); INSERT INTO ip_add VALUES('172.16.4.203'); INSERT INTO ip_add VALUES('172.16.4.205'); INSERT INTO ip_add VALUES('172.16.4.69'); INSERT INTO ip_add VALUES('10.0.1.204'); INSERT INTO ip_add VALUES('10.0.1.16'); INSERT INTO ip_add VALUES('10.1.0.16'); INSERT INTO ip_add VALUES('192.168.1.5'); INSERT INTO ip_add VALUES('192.168.1.7'); INSERT INTO ip_add VALUES('192.168.1.19'); SELECT ip FROM ip_add WHERE ISINNET( ip, '172.16.1.0', 16 ); SELECT ip FROM ip_add WHERE ISINNET( ip, '172.16.1.0/16' ); -- 172.16.1.40 -- 172.16.1.93 -- 172.16.1.204 -- 172.16.4.203 -- 172.16.4.205 -- 172.16.4.69 SELECT ip FROM ip_add WHERE ISINNET( ip, '172.16.1.0', 24 ); SELECT ip FROM ip_add WHERE ISINNET( ip, '172.16.1.0/24' ); -- 172.16.1.40 -- 172.16.1.93 -- 172.16.1.204 SELECT * FROM ip_add WHERE NOT ISINNET( ip, '128.0.0.0', 1 ); SELECT * FROM ip_add WHERE NOT ISINNET( ip, '128.0.0.0/1' ); -- 10.0.1.204 -- 10.0.1.16 -- 10.1.0.16 DELETE FROM ip_add WHERE NOT ISINNET( ip, '128.0.0.0', 1 ); DELETE FROM ip_add WHERE NOT ISINNET( ip, '128.0.0.0/1' ); SELECT * FROM ip_add; -- 172.16.1.40 -- 172.16.1.93 -- 172.16.1.204 -- 172.16.4.203 -- 172.16.4.205 -- 172.16.4.69 -- 192.168.1.5 -- 192.168.1.7 -- 192.168.1.19
what version of sqlite is it for ?
This extension was first written for sqlite 3.5.9, and it works for me with sqlite3 to 3.7.6.3 (current sqlite3 version on Sun Jun 12 2011).
Now, there are 2 things in the archive file :
- a dynamic extension that you can load fom within your programs or from within the CLI sqlite3 itself (if it has been compile with the load extension enabled).
- It should work with any sqlite3 version that supports loadable extensions.
- some patches for sqlite source files. When the patch is applied, the function
isinnet
is added to the core functions that sqlite understands. I don't update these patch very often because i don't really need them (the loadable extension suits my need). So, there are only a few patches available. To use this, you will have to download the entire sqlite source archive and completely rebuild it. But once it is done, there is no need to load an extension. The patch are against the so called amalgamation source code.
Please, note that in either case, this little extension is only distributed as source code. You will have to compile it.
How does it work internally ?
- ip addresses are simply stored as strings
- netmasks are stored as strings, too
- netmask, if specified as a number of bits, may be stored as strings OR integer
- each time the funtion isinnet is called it tries to parse the ip and netmask, into 32 bits numbers
- If there is an error : numbers are too big, or the format is not correct, then isinnet returns NULL
- Otherwise it returns 1 if (address & mask) = (network & mask),
it returns 0 (zero) if not.
since it computes (address & mask) = (network & mask), it is possible to check if two addresses are in the same subnet with the same function
for example, to check if 172.16.132.4 is in the same /25 subnet as 172.16.192.45, you can use :
SELECT isinnet( '172.16.132.4', '172.16.192.45', 25 )
To find all ip in the same /16 subnet as 172.16.132.4 you can use something like :
SELECT ip FROM TABLE WHERE isinnet( ip, '172.16.132.4', 16 ); SELECT ip FROM TABLE WHERE isinnet( '172.16.132.4', ip, 16 );
or even
SELECT ip FROM TABLE WHERE isinnet( ip, '172.16.132.4', '16' ); SELECT ip FROM TABLE WHERE isinnet( ip, '172.16.132.4', '255.255.0.0' ); SELECT ip FROM TABLE WHERE isinnet( '172.16.132.4', ip, '255.255.0.0' ); SELECT ip FROM TABLE WHERE isinnet( ip, '172.16.132.4/16' ); SELECT ip FROM TABLE WHERE isinnet( ip, '172.16.132.4/255.255.0.0' ); SELECT ip FROM TABLE WHERE isinnet( '172.16.132.4', ip || '/16' );
The latter is of course a bad idea, but it should work (not tested)
the first two are the fastest because they require less parsing than the others.
What to download ?
Just download ipv4-ext.tar.bz2
What are all those files in the archive and how to use them ?
First you have to decide wether you want to build the extension, or if you want to entirely re build sqlite, modified to include the isinnet function.
Once you have decided, read the list below and do whatever you think is appropriate.
Makefile.MacOSX
This is a makefile for building the dynamic extension under MacOSX (10.5)
Fisrt edit this file and set the INCLUDE=-I/… line to a directory that contains the file sqlite3ext.h
Then, run
make -f Makefile.MacOSX
and you should obtain a file named libsqliteipv4.dylib. This file is the extension you are going to load.
Makefile.linux
This is a makefile for building the dynamic extension under GNU/Linux
Fisrt edit the file and set the INCLUDE=-I/… line to a directory that contains the file sqlite3ext.h
Then, run
make -f Makefile.linux
and you should obtain a file named libsqliteipv4.so. This file is the extension you are going to load.
ipv4-ext.c
This is the source file of the extension.
ipv4-sqlite-3.7.6.3-amalgamation.patch
This is a patch for the file sqlite-autoconf-3070603/sqlite3.c of sqlite-3.7.6.3. Once applied, it allows to build sqlite with the isinnet function. There is then no need to load an extension. If you want to incorporate SQLite code into a wider program, then the function isinnet will also go into you wider program, provided you #define SQLITE_IPV4_EXT_FUNC 1
somewhere in your source.
Here are the steps you must follow to build sqlite with builtin isinnet SQL function.
- download sqlite-autoconf-3070603.tar.gz from http://sqlite.org/download.html
- untar the archive with something like gzip -cd sqlite-autoconf-3070603.tar.gz | tar xf -
- put the patch file in the directory sqlite-autoconf-3070603
- then cd into the newly created directory named sqlite-autoconf-3070603 and apply the patch.
cd sqlite-autoconf-3070603 patch -p1 </../ipv4-sqlite-3.7.6.3-amalgamation.patch
- Then configure using -DSQLITE_IPV4_EXT_FUNC=1, make, and install the software as you would normally. for example:
env CFLAGS=-DSQLITE_IPV4_EXT_FUNC=1 ./configure --prefix=/usr/local --enable-readline make sudo make install
or, something like this
env LDFLAGS=-ldl CFLAGS='-DSQLITE_IPV4_EXT_FUNC=1 -DSQLITE_ENABLE_RTREE=1'\ ' -DSQLITE_ENABLE_FTS3=1' \ ./configure --prefix=/usr/local --enable-readline --enable-threadsafe \ --enable-load-extension make sudo make install
ipv4-sqlite-3.6.23.1-amalgamation.patch
This is a patch for the file sqlite3.c of sqlite-3.6.23.1. Once applied, it allows to build sqlite with the isinnet function.
See description for version 3.7.6.3 for details and substitute 3.7.6.3 with 3.6.23.1 .
ipv4ext.txt
the file you are reading now, with dokuwiki markup
How to load the extension
Please, see http://sqlite.org/cvstrac/wiki?p=LoadableExtensions. But, here follow some brief explanations
In your application, call sqlite3_enable_load_extension(db,1) to allow loading external libraries. Then load the library libsqliteipv4 using sqlite3_load_extension; the third argument should be 0.
from within the sqlite3 CLI, if it has been built with load extension enabled, then try this :
sqlite> .load './libsqliteipv4.so'
or
sqlite> SELECT load_extension('/path/to/libsqliteipv4.so');
On a Macintosh, you would use .dylib
instead of .so
, on windows, I guess you would use .dll
instead of .so
.