MySQL i IP adrese

Treba da u tabeli smeštam podatke o ip adresnim opsezima i omogućim razne upite nad tim podacima. Nisam baš siguran kako to da izvedem na najbolji način.

Oznaka IP opsega se sastoji od dva elementa: same IP adrese i mrežne maske. U mom slučaju, ip adrese su uvek u obilku nnn.nnn.nnn.nnn/mm gde je nn.nn.nn.nn IP adresa a mm je CIDR maska.

Upiti koje treba da izvedem nad podacima su:

- proveriti da li se dati IP opseg (takođe naveden u obliku nnn.nnn.nnn.nnn/mm) nalazi u tabeli ili je podopseg nekom od opsega koji su upisani u bazu
- prikazati IP opsege iz tabele tako da se vidi njihova hijerarhijska struktura

Svako rešenje koje sam smislio zahteva redudantna polja i dodatnu programsku obradu podataka pre upisa ili nakon čitanja, tj. ne mogu da izvedem sve samo kroz SQL.

Iskustva ili sveže ideje su dobrodošli.

Potpis

http://pedja.supurovic.net - http://wireless.uzice.net - Besplatni .RS domeni - Mikrotik uputstva - YT9TP - OpenStreetMap Srbija

Pa u jednoj koloni čuvaš IP adresu u drugoj CIDR masku. A za računanje opsega, fora je samo da dođeš do poslednje adrese u opsegu:

Naravno, podrazumevam da je IP adresa u int formatu. I posle je lako da proveriš da li je IP adresa u nekom opsegu, itd.

obrati samo paznju na IPV6 posto polako pocinje da "bas radi" .. tako da ti je mozda iskusno da krenes odma sa full ipv6 implementacijom

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Bavim se ovih dana sličnom problematikom i ono što mi je trenutno rešenje je da opseg čuvam kao dva inta (ili biginta), tj, kao početna i krajnja adresa. Znači slično ovome što je Nikola predložio samo što ne računam svaki put krajnju adresu, već prilikom inserta.

Prvi upit bi onda trebalo da bude jednostavan, za drugi nisam siguran... Meni ovo doduše treba da proveravam da li određena IP adresa pripada opsegu i za to mi je ovakva tabela idealna....

Baš me zanima kako bi izgledao SELECT svih slogova u kojima je upisan IPv6 unutar nekog opsega pretrage, korišćenjem 2 biginta.

Nije mi trebalo do sada, ali kad sam razmišljao kako ga sastaviti uvek sam završavao s komplikovanim WHERE delom.
Korišćenjem Char(32) se to rešava jednostavno sa BETWEEN

I inače, Bogdane, šta predlažeš za skladištenje IPv6 adrese sa stanovišta brzine pretrage, dali ići na 2 biginta ili char ?

Potpis

_{"The quieter you become, the more you are able to hear."}
Blog | PowerCMS

mnogo zavisi .. ja sam do sada ipv6 koristio tako sto sam pamtio samo pola adrese .. sta je fora, onih 128 bita je podeljeno u dva segmenta po 64, jedan segment (prvi ako se secam dobro) je "mreza" a drugi segment je "id masine u toj mrezi", sad sta je cim, taj drugi segment se generise obicno od mac adrese ili kao 64bitni crc ssh kljuca .. i kao takav daje prilican "uniqueness" svakoj masini (generalno isti nacin na koji UUID daje uniqueness po masini) ... sad posto je meni trebalo da pamtim IP samo zato da bi mogao da vadim "unique usere" onda je meni ovaj deo adrese dovoljan (bas me briga za deo sa mrezom) tako da ja nemam probleme koje ti hoces da reses... sa druge strane ako hoces da radis geoip na primer, geoip bi trebalo da je vezan za onaj "mrezni" 64bitni segment i tu ne bi drugi segment trebalo da utice uopste.... sad ... ja sam tako skapirao ipv6, moguce da sam ga ozbiljno omasio .. kao sto rekoh, ja sam cuvao samo pola, ovaj deo sa mrezom nisam ni probao a vezano za geoip imam maxmind bazu na gradskom nivou i ona je ipv4 .. nisam video da neko prodaje bazu sa ipv6 tako da se sve zasniva na pretpostavkama tako da ne bih smeo da kazem da znam koje je najbolje resenje posto nisam upoznat ni ja jos uvek kompletno sa problemom

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

da, zaboravih, spominjali smo IP tip podataka pre par meseci na nekom sastanku ali ako sam dobro skapirao nije usao u 5.6.x drvo ... mozda 5.7 .. malo sam jos uvek pogubljen sa novim procedurama posto su nam zamenili sve alate koje smo do sada koristili tako da 80% stvari sada ide telefonom posto niko ne zna da koristi nove alate ... verovatno cemo se jos jedno 6 meseci "privikavati" .. meni pripadne muka kada treba da updateujem neki bug .. (iliti "defect" kako se to sada zove ?!)

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Heh, zamisli samo upite nad tabelom sa takvom kobasicom koja se računa svaki put.

Mnogo je efikasnije u tabelu staviti ip kao int, i mreznu masku (u ip obilku a ne cidr) isto kao int i eventualno jos dodati i krajnju adresu takodje kao int. Tako je makar moguce sam jednom uraditi konverziju adresa u pogodan oblik pa sam tim vršiti upit.

Nečitkost bi se mogla rešiti nekom stored procedurom koja bi primala parameter u ljudski čitkom obliku a zatim se sama pobrinula da to konvertuje kako joj odgovara.

Redudantna polja bi se takođe morala rešiti nekom stored procedurom za upis.

Sve u svemu, prilično bagljavo rešenjea nažalost, ne mogu da upotrebim postgresql koji ima namenske tipove polja za ovakve stvari.

Potpis

http://pedja.supurovic.net - http://wireless.uzice.net - Besplatni .RS domeni - Mikrotik uputstva - YT9TP - OpenStreetMap Srbija

spominju se namenska polja ali .. izgleda nista od njih u 5.6 .. (ja sam se isto ponadao ali zadnji put kada sam pricao sa tomasom o tome bila je varijanta "prestani da me smaras i napravi ako imas dovoljno slobodnog vremena")

elem .. vezano za ovu kobaju

INET_NTOA(ipaddr + (POW(2, (32-mask))-1))

ako se nalazi u SELECT delu nije preterano strasno INET_NTOA je vrlo brza funkcija, neces je ti u app layeru napisati nista znacajno brze ...

mada, ne kapiram znacaj ove funkcije ... sta dobijas ovim sabiranjem posto ces u 99% slucajeva dobiti rezultat NULL jerbo ti je u 99% slucajeva prvi bajt maske 255 sto znaci:

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

btw, razmisljam za sta ti treba to sto si pitao ... ja na primer imam bazu (kupljena od maxmind-a) sa ip adresama po gradovima ... ja tu ne koristim netmask uopste imam bukvalno "startIP" i "endIP" polje .. oba su unsigned integer i to je to .. mnogo je brze da kada punis bazu ono sto dobijes na ulazu kao "net+mask" pretvoris u startIP+endIP posto tako vrlo lako posle nadjes slog koji odgovara ip adresi koju dobijes od klijenta .. jedan obican where a>= and a<= a key ti je startIP, endIP i bog da te vidi :)

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

uh nesto gledam sad optimizer kod ... nisam siguran da ce kompozitni index ovde da pomogne, moram da proverim, mozda je brze koristiti dva odvojena ... aj bas cu sad da pogledam pa javljam, pada mi napamet da bi cak mozda bilo brze da se cuva IP + offset (ja to nisam radio al mi palo sad na pamet) ... idem da jurim malo po kodu pa se javljam :D

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Tabela treba da sadrži opis strukture mreže. To je za projekat SrbijaWireless (http://www.srbijawireless.net). Treba da napravimo online aplikaciju za registar IP opsega i još štošta.

Suština je da se u bazu smesti struktura ip opsega i podopsega sve do nivoa jednog uređaja (/32). Naravno, tabela ne sadrži samo ip adrese nego još štošta podataka koji su za njih vezani. Dodatno, druge tabele sadrže veze prema ovoj, tako da bi valjalo da se koristi neki prost i smislen primarni ključ za sve to.

Najprostiji način je da se naprato napravi veza tabele same u sebe pa da se ručno povezuje hijerarhija, ali gledam da nađem rešenje da se to može uraditi i samo na osnovu ip adresa i maski jer se hijerarhija u njima već sadrži.

Potpis

http://pedja.supurovic.net - http://wireless.uzice.net - Besplatni .RS domeni - Mikrotik uputstva - YT9TP - OpenStreetMap Srbija

dobio sam upravo super ideju, mozda tebi nece da radi posao ali meni bogami hoce pa cu bas da menjam svoj kod uskoro :D

elem, meni je znacajno da vidim "odakle" neko dolazi da bi mu uvalio odgovarajuci "sadrzaj" tako da ako "ne znam" odakle dolazi, bas me briga, opet ocu da mu dam neki sadrzaj i bas me briga koji ...

tako da cu ja sebi ovako

u tabeli imam SAMO startIP !!! nemam endIP uopste (ni range ni network .. nista) .. i key na to polje

i onda radim

select * from t1 where mojip >= startIP order by startIP ASC LIIMIT 1;

brze od toga ne da moze :D

a odo sad da citam kod za optimizer da vidim za ono drugo

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

tebi ova moja ideja sigurno nece da radi posao ... ali tebi upit nece da trazi "u koji range spada ovaj IP" jel tako .. tebi bas treba network kao entitet u tabeli zar ne .. i treba ti "parent network" i "child networks" da mozes "brzo da nadjes" jel tako ... posto ces imati i 10.1.1.0/24 i 10.1.1.1.0/29 ....

sve zavisi sta ce biti upiti... ako ti treba da za IP adresu dobijes network-e koji je pokrivaju onda bi ja tu radio start/endIP + neki trigger koji bi brinuo na insert/update da su vrednosti "kako treba" .. ako ce upit po samom ip-u biti redak onda IP i mask, mask kao tiny posto ti je realno isti djavo u kom formatu ga cuvas, onda bolje da ustedis ta 3 bajta ... mada, nisam siguran, mora da razmislim

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Zar ne bi trebalo da se sortira opadajuće? Ako sam dobro shvatio, treba nam najveći startIP koji je manji zadate ip adrese...
Mada meni ni ta varajanta ne bi radila posao pošto ne garantuje da se dobije pravi opseg...

jest desc, u pravu si oce to kad ne spavas dvadesetak sati .. a i to mi je palo na pamet dok sam otvarao optimizer kod .. nije bas resenje za svakoga

kao sto rekoh, to moze da se koristi SAMO u slucaju da su ili SVI moguci opsezi pokriveni ili u slucaju da te za one opsege koji nisu pokriveni "bas briga" ... u slucaju da hoces da ti vrati "nista" ako ne nadje tacan opseg onda ne moze... kao sto rekoh, ja cu to da turim sebi posto mene tako bas briga dal ce onome ko ne znam iz kog je grada da se prikaze "ovaj" ili "onaj" oglas .. posto sada ako ne dobijem opseg dobija "random" oglas svejedno .. mada je ova maxmind baza extra, logovao sam jedno mesec dana te koji dodju sa "nepoznatih" lokacija, mislim da je od oko 60 miliona poseta bilo ispod 200 ip adresa "bez lokacije"

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/