meta données pour cette page
Différences
Ci-dessous, les différences entre deux révisions de la page.
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/14 10:18] ztrulphcs [What is it ?] remove tabs in code so sqlite3 cli won't try to complete when dong copy/paste |
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:// | + | You should find the latest version of this file here : http:// |
===== What is it ? ===== | ===== What is it ? ===== | ||
- | This is an **extension** for sqlite3. that adds one single function | + | This is an **extension** for [[http:// |
- | 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 '' |
- | <file sql try-me.sql> | + | <file sql try-net.sql> |
SELECT ISINNET( ' | SELECT ISINNET( ' | ||
SELECT ISINNET( ' | SELECT ISINNET( ' | ||
- | -- | + | -- ==> 1 |
SELECT ISINNET( ' | SELECT ISINNET( ' | ||
SELECT ISINNET( ' | SELECT ISINNET( ' | ||
- | -- | + | -- ==> 0 |
SELECT ISINNET( ' | SELECT ISINNET( ' | ||
SELECT ISINNET( ' | SELECT ISINNET( ' | ||
- | -- | + | -- ==> 1 |
SELECT ISINNET( ' | SELECT ISINNET( ' | ||
SELECT ISINNET( ' | SELECT ISINNET( ' | ||
- | -- | + | -- ==> 0 |
CREATE TABLE ip_add ( | CREATE TABLE ip_add ( | ||
Ligne 45: | Ligne 45: | ||
SELECT ip FROM ip_add WHERE ISINNET( ip, ' | SELECT ip FROM ip_add WHERE ISINNET( ip, ' | ||
SELECT ip FROM ip_add WHERE ISINNET( ip, ' | SELECT ip FROM ip_add WHERE ISINNET( ip, ' | ||
- | -- 172.16.1.40 | + | -- 172.16.1.40 |
- | -- 172.16.1.93 | + | -- 172.16.1.93 |
- | -- 172.16.1.204 | + | -- 172.16.1.204 |
- | -- 172.16.4.203 | + | -- 172.16.4.203 |
- | -- 172.16.4.205 | + | -- 172.16.4.205 |
- | -- 172.16.4.69 | + | -- 172.16.4.69 |
SELECT ip FROM ip_add WHERE ISINNET( ip, ' | SELECT ip FROM ip_add WHERE ISINNET( ip, ' | ||
SELECT ip FROM ip_add WHERE ISINNET( ip, ' | SELECT ip FROM ip_add WHERE ISINNET( ip, ' | ||
- | -- 172.16.1.40 | + | -- 172.16.1.40 |
- | -- 172.16.1.93 | + | -- 172.16.1.93 |
- | -- 172.16.1.204 | + | -- 172.16.1.204 |
SELECT * FROM ip_add WHERE NOT ISINNET( ip, ' | SELECT * FROM ip_add WHERE NOT ISINNET( ip, ' | ||
SELECT * FROM ip_add WHERE NOT ISINNET( ip, ' | SELECT * FROM ip_add WHERE NOT ISINNET( ip, ' | ||
- | -- 10.0.1.204 | + | -- 10.0.1.204 |
- | -- 10.0.1.16 | + | -- 10.0.1.16 |
- | -- 10.1.0.16 | + | -- 10.1.0.16 |
DELETE FROM ip_add WHERE NOT ISINNET( ip, ' | DELETE FROM ip_add WHERE NOT ISINNET( ip, ' | ||
Ligne 68: | Ligne 68: | ||
SELECT * FROM ip_add; | SELECT * FROM ip_add; | ||
- | -- 172.16.1.40 | + | -- 172.16.1.40 |
- | -- 172.16.1.93 | + | -- 172.16.1.93 |
- | -- 172.16.1.204 | + | -- 172.16.1.204 |
- | -- 172.16.4.203 | + | -- 172.16.4.203 |
- | -- 172.16.4.205 | + | -- 172.16.4.205 |
- | -- 172.16.4.69 | + | -- 172.16.4.69 |
- | -- 192.168.1.5 | + | -- 192.168.1.5 |
- | -- 192.168.1.7 | + | -- 192.168.1.7 |
- | -- 192.168.1.19 | + | -- 192.168.1.19 |
+ | </ | ||
+ | |||
+ | With the '' | ||
+ | |||
+ | <file sql try-range.sql> | ||
+ | SELECT ISINRANGE( ' | ||
+ | SELECT ISINRANGE( ' | ||
+ | SELECT ISINRANGE( ' | ||
+ | SELECT ISINRANGE( ' | ||
+ | -- ==> 1 | ||
+ | SELECT ISINRANGE( ' | ||
+ | SELECT ISINRANGE( ' | ||
+ | SELECT ISINRANGE( ' | ||
+ | SELECT ISINRANGE( ' | ||
+ | -- ==> 1 | ||
+ | SELECT ISINRANGE( ' | ||
+ | SELECT ISINRANGE( ' | ||
+ | SELECT ISINRANGE( ' | ||
+ | SELECT ISINRANGE( ' | ||
+ | -- ==> 0 | ||
+ | SELECT ISINRANGE( ' | ||
+ | SELECT ISINRANGE( ' | ||
+ | SELECT ISINRANGE( ' | ||
+ | SELECT ISINRANGE( ' | ||
+ | -- ==> 1 | ||
+ | |||
+ | CREATE TABLE ip_add ( | ||
+ | ip varchar( 15 ) | ||
+ | ); | ||
+ | INSERT INTO ip_add VALUES(' | ||
+ | INSERT INTO ip_add VALUES(' | ||
+ | INSERT INTO ip_add VALUES(' | ||
+ | INSERT INTO ip_add VALUES(' | ||
+ | INSERT INTO ip_add VALUES(' | ||
+ | INSERT INTO ip_add VALUES(' | ||
+ | INSERT INTO ip_add VALUES(' | ||
+ | INSERT INTO ip_add VALUES(' | ||
+ | INSERT INTO ip_add VALUES(' | ||
+ | INSERT INTO ip_add VALUES(' | ||
+ | INSERT INTO ip_add VALUES(' | ||
+ | INSERT INTO ip_add VALUES(' | ||
+ | |||
+ | SELECT ip FROM ip_add WHERE ISINRANGE( ip, ' | ||
+ | SELECT ip FROM ip_add WHERE ISINRANGE( ip, ' | ||
+ | -- 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, ' | ||
+ | SELECT ip FROM ip_add WHERE ISINRANGE( ip, ' | ||
+ | -- 172.16.1.40 | ||
+ | -- 172.16.1.93 | ||
+ | -- 172.16.1.204 | ||
+ | |||
+ | SELECT * FROM ip_add WHERE NOT ISINRANGE( ip, ' | ||
+ | SELECT * FROM ip_add WHERE NOT ISINRANGE( ip, ' | ||
+ | -- 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, ' | ||
+ | DELETE FROM ip_add WHERE NOT ISINRANGE( ip, ' | ||
+ | |||
+ | 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 | ||
</ | </ | ||
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 | + | This extension was first written for sqlite 3.5.9, and it works for me with sqlite3 3.23.1 (current |
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 | + | - a dynamic extension that you can load from within your programs or from within the CLI sqlite3 itself (if it has been compiled |
* 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 | + | - some patches for sqlite source files. When the patch is applied, the functions |
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 |
- | - each time the funtion | + | - each time the function '' |
- | - If there is an error : numbers are too big, or the format is not correct, then isinnet returns | + | - If there is an error : numbers are too big, or the format is not correct, then the functions return |
- | - Otherwise | + | - Otherwise |
+ | * '' | ||
+ | * '' | ||
- | 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 |
for example, to check if 172.16.132.4 is in the same /25 subnet as 172.16.192.45, | for example, to check if 172.16.132.4 is in the same /25 subnet as 172.16.192.45, | ||
Ligne 130: | Ligne 211: | ||
</ | </ | ||
- | 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 | + | 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 '' |
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/ | + | Fisrt edit this file and set the INCLUDE=-I/ |
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/ | + | Fisrt edit the file and set the INCLUDE=-I/ |
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 | + | 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 '' |
- | 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 |
- | * download sqlite-autoconf-3070603.tar.gz from http:// | + | * download sqlite-autoconf-3071000.tar.gz from http:// |
- | * 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 -</ |
- | * put the patch file in the directory sqlite-autoconf-3070603 | + | * put the patch file in the newly created |
- | * then cd into the newly created directory named sqlite-autoconf-3070603 | + | * then cd into the '' |
- | cd sqlite-autoconf-3070603 | + | cd sqlite-autoconf-3071000 |
- | patch -p1 < | + | patch -p1 < |
</ | </ | ||
- | * Then configure | + | * Configure |
env CFLAGS=-DSQLITE_IPV4_EXT_FUNC=1 ./configure --prefix=/ | env CFLAGS=-DSQLITE_IPV4_EXT_FUNC=1 ./configure --prefix=/ | ||
make | make | ||
Ligne 197: | Ligne 280: | ||
sudo make install | sudo make install | ||
</ | </ | ||
+ | |||
+ | |||
+ | ==== 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 '' | ||
+ | |||
+ | 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 | + | This is a patch for the file sqlite3.c of sqlite-3.6.23.1. Once applied, it allows to build sqlite with the '' |
- | 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:// | + | Please, see https://www.sqlite.org/loadext.html. But, here follow some brief explanations |
- | In your application, | + | * In your application, |
- | + | | |
- | from within the sqlite3 CLI, **if it has been built with load extension enabled**, then try this : | + | |
<cli> | <cli> | ||
sqlite> .load ' | sqlite> .load ' | ||
Ligne 225: | Ligne 319: | ||
On a Macintosh, you would use '' | On a Macintosh, you would use '' | ||
+ | </ | ||
+ | ===== BUGS ===== | ||
+ | |||
+ | - '' | ||
===== 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. |