meta données pour cette page
  •  

Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

Les deux révisions précédentes Révision précédente
Prochaine révision 		Révision précédente
schplurtziel:sqlite3-ipv4-ext [2011/06/12 12:46]
127.0.0.1 modification externe 		schplurtziel:sqlite3-ipv4-ext [2020/12/28 08:02] (Version actuelle)
ztrulphcs [sqlite ipv4 extension]
Ligne 1: Ligne 1:
 ====== sqlite ipv4 extension ====== ====== sqlite ipv4 extension ======
  
-date this document was last edited : 2011-06-12+date this document was last edited : Mon Apr 16 2018. and minor edition (links to sqlite.org) on 2020/12/28
  
-date the C source was last edited : 2008-09-24+date the C source was last edited : 2011-06-19
  
-you should find the latest version of this file here : http://schplurtz.free.fr/wiki/schplurtziel/sqlite3-ipv4-ext+You should find the latest version of this file here : http://schplurtz.free.fr/wiki/schplurtziel/sqlite3-ipv4-ext
  
 ===== What is it ? ===== ===== 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.+This is an **extension** for [[http://www.sqlite.org/|sqlite3]] that adds two simple functions named ''isinnet'' and ''isinrange'' to the set of SQL functions. those functions deal with IP v4 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 :+With the ''isinnet'' function, you can test if an ip address is within a network, or even if two ips are in the same subnet, just by using SQL commands like these :
  
-<file sql try-me.sql>+<file sql try-net.sql>
 SELECT ISINNET( '172.16.1.193', '172.16.1.0', 24 ); SELECT ISINNET( '172.16.1.193', '172.16.1.0', 24 );
 SELECT ISINNET( '172.16.1.193', '172.16.1.0/24' ); SELECT ISINNET( '172.16.1.193', '172.16.1.0/24' );
Ligne 28: Ligne 28:
  
 CREATE TABLE ip_add ( CREATE TABLE ip_add (
- ip      varchar( 15 )+        ip      varchar( 15 )
 ); );
 INSERT INTO ip_add VALUES('172.16.1.40'); INSERT INTO ip_add VALUES('172.16.1.40');
Ligne 66: Ligne 66:
 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 );
 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
 +</file>
 +
 +With the ''isinrange'' function, you can also test if an address is within a certain range. A range is defined by a start address and a stop address. Note that the start address may be //smaller// or //greater// than the stop address.
 +
 +<file sql try-range.sql>
 +SELECT ISINRANGE( '172.16.1.132', '172.16.1.19', '172.16.1.154' );
 +SELECT ISINRANGE( '172.16.1.132', '172.16.1.19-172.16.1.154' );
 +SELECT ISINRANGE( '172.16.1.132', '172.16.1.154', '172.16.1.19' );
 +SELECT ISINRANGE( '172.16.1.132', '172.16.1.154-172.16.1.19' );
 +-- ==> 1
 +SELECT ISINRANGE( '172.16.1.132', '172.16.1.19', '172.16.1.132' );
 +SELECT ISINRANGE( '172.16.1.132', '172.16.1.19-172.16.1.132' );
 +SELECT ISINRANGE( '172.16.1.132', '172.16.1.132', '172.16.1.19' );
 +SELECT ISINRANGE( '172.16.1.132', '172.16.1.132-172.16.1.19' );
 +-- ==> 1
 +SELECT ISINRANGE( '172.16.1.132', '10.0.0.19', '10.255.254.132' );
 +SELECT ISINRANGE( '172.16.1.132', '10.0.0.19-10.255.254.132' );
 +SELECT ISINRANGE( '172.16.1.132', '10.0.0.132', '10.255.254.19' );
 +SELECT ISINRANGE( '172.16.1.132', '10.0.0.132-10.255.254.19' );
 +-- ==> 0
 +SELECT ISINRANGE( '172.16.1.193', '172.14.1.0', '198.23.43.17' );
 +SELECT ISINRANGE( '172.16.1.193', '172.14.1.0-198.23.43.17' );
 +SELECT ISINRANGE( '172.16.1.193', '198.23.43.17', '172.14.1.0' );
 +SELECT ISINRANGE( '172.16.1.193', '198.23.43.17-172.14.1.0' );
 +-- ==> 1
 +
 +CREATE TABLE ip_add (
 +        ip      varchar( 15 )
 +);
 +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 ISINRANGE( ip, '172.16.1.0', '172.16.255.255' );
 +SELECT ip FROM ip_add WHERE ISINRANGE( ip, '172.16.1.0-172.16.255.255' );
 +-- 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 ISINRANGE( ip, '172.16.1.0', '172.16.1.230' );
 +SELECT ip FROM ip_add WHERE ISINRANGE( ip, '172.16.1.0-172.16.1.230' );
 +-- 172.16.1.40
 +-- 172.16.1.93
 +-- 172.16.1.204
 +
 +SELECT * FROM ip_add WHERE NOT ISINRANGE( ip, '0.0.0.0', '172.16.1.255' );
 +SELECT * FROM ip_add WHERE NOT ISINRANGE( ip, '0.0.0.0-172.16.1.255' );
 +-- 172.16.4.203
 +-- 172.16.4.205
 +-- 172.16.4.69
 +-- 192.168.1.5
 +-- 192.168.1.7
 +-- 192.168.1.19
 +
 +DELETE FROM ip_add WHERE NOT ISINRANGE( ip, '30.0.0.0', '200.0.0.0' );
 +DELETE FROM ip_add WHERE NOT ISINRANGE( ip, '30.0.0.0-200.0.0.0' );
  
 SELECT * FROM ip_add; SELECT * FROM ip_add;
Ligne 85: Ligne 164:
 ===== what version of sqlite is it for ? ===== ===== 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).+This extension was first written for sqlite 3.5.9, and it works for me with sqlite3 3.23.1 (current [[https://brew.sh/|brew]] sqlite3 version as of Mon Apr 16 2018).
  
 Now, there are 2 things in the archive file : 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).+  - a dynamic extension that you can load from within your programs or from within the CLI sqlite3 itself (if it has been compiled with the <<load extension>> feature enabled).
     * It should work with **any** sqlite3 version that supports loadable extensions.     * 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.+  - some patches for sqlite source files. When the patch is applied, the functions ''isinnet'' and ''isinrange'' are  added to the core functions that sqlite understands. I don't update these patches 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 patches are against the autoconf //amalgamation// source code.
  
 Please, note that in either case, this little extension is only distributed as source code. You will have to compile it. Please, note that in either case, this little extension is only distributed as source code. You will have to compile it.
Ligne 99: Ligne 178:
   - ip addresses are simply stored as strings   - ip addresses are simply stored as strings
   - netmasks are stored as strings, too   - netmasks are stored as strings, too
-  - netmask, if specified as a number of bits, may be stored as strings OR integer +  - netmask, if specified as a number of bits, may be stored as strings OR integer (sqlite does not really care anyway). 
-  - each time the funtion isinnet is called it tries to parse the ip and netmask, into 32 bits numbers +  - each time the function ''isinnet'' or ''isinrange'' is called it tries to parse the ips 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 +  - If there is an error : numbers are too big, or the format is not correct, then the functions return NULL 
-  - Otherwise it returns 1 if (address & mask) = (network & mask),\\ it returns 0 (zero) if not. +  - Otherwise 
 +    * ''isinnet'' returns 1 if (address & mask) = (network & mask),\\ it returns 0 (zero) if not.  
 +    * ''isinrange'' %%returns 1 if (start <= address && address <= stop ) or (stop <= address && address <= start),%%\\ 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+since isinnet computes (address & mask) = (network & mask), it is possible to check if two addresses are in the same subnet with this function.
  
 for example, to check if 172.16.132.4 is in the same /25 subnet as 172.16.192.45, you can use : for example, to check if 172.16.132.4 is in the same /25 subnet as 172.16.192.45, you can use :
Ligne 130: Ligne 211:
 </file> </file>
  
-The latter is of course a bad idea, but it should work (not tested)+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. the first two are the fastest because they require less parsing than the others.
Ligne 140: Ligne 221:
 ===== What are all those files in the archive and how to use them ? ===== ===== 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.+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'' and ''isinrange'' functions.
  
 Once you have decided, read the list below and do whatever you think is appropriate. Once you have decided, read the list below and do whatever you think is appropriate.
Ligne 146: Ligne 227:
 ==== Makefile.MacOSX ==== ==== Makefile.MacOSX ====
  
-This is a makefile for building the dynamic extension under MacOSX (tested under 10.5 and 10.6)+This is a makefile for building the dynamic extension under MacOSX (tested under 10.5 and 10.6 10.9 and recently 10.13.4)
  
-Fisrt edit this file and set the INCLUDE=-I/... line to a directory that contains the file sqlite3ext.h+Fisrt edit this file and set the INCLUDE=-I/... line to a directory that contains the file ''sqlite3ext.h''. For example, if you installed sqlite3 via [[https://brew.sh/|brew]], the line could read ''INCLUDE = -I/usr/local/opt/sqlite/include''
  
 Then, run Then, run
Ligne 154: Ligne 235:
   make -f Makefile.MacOSX   make -f Makefile.MacOSX
  
-and you should obtain a file named libsqliteipv4.dylib. This file is the extension you are going to load.+and you should obtain a file named libsqliteipv4.dylib. This file is the extension you are going to load. Feel free to copy this file wherever you see fit.
  
 ==== Makefile.linux ==== ==== Makefile.linux ====
Ligne 160: Ligne 241:
 This is a makefile for building the dynamic extension under GNU/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+Fisrt edit the file and set the INCLUDE=-I/... line to a directory that contains the file ''sqlite3ext.h''
  
 Then, run Then, run
Ligne 173: Ligne 254:
 This is the source file of the extension. This is the source file of the extension.
  
-==== ipv4-sqlite-3.7.6.3-amalgamation.patch ====+==== ipv4-sqlite-3.7.10.0-amalgamation.patch ==== 
 + 
 +^This file outdated and probably completely unusable ^
  
-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.+This is a patch for the file sqlite-autoconf-3071000/sqlite3.c of sqlite-3.7.10.0. Once applied, it allows to build sqlite with the ''isinnet'' and ''isinrange'' functions. There is then no need to load an extension. If you want to incorporate SQLite code into a wider program, then the functions ''isinnet'' and ''isinrange'' 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. +Here are the steps you must follow to build sqlite with builtin ''isinnet'' and ''isinrange'' SQL functions. 
-  * download sqlite-autoconf-3070603.tar.gz from http://sqlite.org/download.html +  * download sqlite-autoconf-3071000.tar.gz from http://sqlite.org/download.html 
-  * untar the archive with something like gzip -cd sqlite-autoconf-3070603.tar.gz | tar xf - +  * untar the archive with something like <code bash>gzip -cd sqlite-autoconf-3071000.tar.gz | tar xf -</code> 
-  * put the patch file in the directory sqlite-autoconf-3070603 +  * put the patch file in the newly created directory ''sqlite-autoconf-3071000'' 
-  * then cd into the newly created directory named sqlite-autoconf-3070603 and apply the patch.<code bash> +  * then cd into the ''sqlite-autoconf-3071000'' directory and apply the patch.<code bash> 
-cd sqlite-autoconf-3070603 +cd sqlite-autoconf-3071000 
-patch -p1 <ipv4-sqlite-3.7.6.3-amalgamation.patch+patch -p1 <ipv4-sqlite-3.7.10.0-amalgamation.patch
 </code> </code>
-  * Then configure using -DSQLITE_IPV4_EXT_FUNC=1, make, and install the software as you would normally. for example: <code bash>+  * Configure using ''-DSQLITE_IPV4_EXT_FUNC=1'', make, and install the software as you would normally. for example: <code bash>
 env CFLAGS=-DSQLITE_IPV4_EXT_FUNC=1 ./configure --prefix=/usr/local --enable-readline env CFLAGS=-DSQLITE_IPV4_EXT_FUNC=1 ./configure --prefix=/usr/local --enable-readline
 make make
Ligne 197: Ligne 280:
 sudo make install sudo make install
 </code> </code>
 +
 +
 +==== ipv4-sqlite-3.7.6.3-amalgamation.patch ====
 +
 +^This file more outdated and probably completely unusable ^
 +
 +This is a patch for the file sqlite3.c of sqlite-3.7.6.3. Once applied, it allows to build sqlite with the ''isinnet'' and ''isinrange'' functions...
 +
 +See description for version 3.7.10.0 for details and substitute 3.7.10.0 with 3.7.6.3 .
 +
  
 ==== ipv4-sqlite-3.6.23.1-amalgamation.patch ==== ==== ipv4-sqlite-3.6.23.1-amalgamation.patch ====
  
 +^This file really outdated and probably definitely completely unusable ^
  
-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.+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'' and ''isinrange'' functions...
  
-See description for version 3.7.6.3 for details and substitute 3.7.6.3 with 3.6.23.1 .+See description for version 3.7.10.0 for details and substitute 3.7.10.0 with 3.6.23.1 .
  
 ==== sqlite3-ipv4-ext.txt ==== ==== sqlite3-ipv4-ext.txt ====
 +
 the file you are reading now, with dokuwiki markup the file you are reading now, with dokuwiki markup
  
Ligne 211: Ligne 306:
 ===== How to load the extension ===== ===== How to load the extension =====
  
-Please, see http://sqlite.org/cvstrac/wiki?p=LoadableExtensions. But, here follow some brief explanations+Please, see https://www.sqlite.org/loadext.html. 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. +  * In your application,\\ call [[https://www.sqlite.org/c3ref/enable_load_extension.html|sqlite3_enable_load_extension(db,1)]] to allow loading external libraries. Then load the library libsqliteipv4 using [[https://www.sqlite.org/c3ref/load_extension.html|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 :<WRAP>
-from within the sqlite3 CLI, **if it has been built with load extension enabled**, then try this :+
 <cli> <cli>
 sqlite> .load './libsqliteipv4.so' sqlite> .load './libsqliteipv4.so'
Ligne 225: Ligne 319:
  
 On a Macintosh, you would use ''.dylib'' instead of ''.so'', on windows, I guess you would use ''.dll'' instead of ''.so''.  On a Macintosh, you would use ''.dylib'' instead of ''.so'', on windows, I guess you would use ''.dll'' instead of ''.so''. 
 +</WRAP>
 +===== BUGS =====
 +
 +  - ''isinrange'' probably works only on little endian machines ([[wp>x86]] and the like).
  
 ===== Questions and answers ===== ===== Questions and answers =====
Ligne 230: Ligne 328:
 ==== Can I use the patch for other versions ? ==== ==== Can I use the patch for other versions ? ====
  
-Yes, probably.+Yes, probably. Well in fact, this true back in 2012. nowadays (2018) this is probably wrong. but giveit a try, you may be lucky.
  
 Since the extension is such a simple and small change to sqlite, it is reasonable to try to patch a version with a patch made for another version. Since the extension is such a simple and small change to sqlite, it is reasonable to try to patch a version with a patch made for another version.