Well, se pare ca m-am prostit si am descoperit niste roti patrate. Noroc ca #mumu e vigilent si imi zice cand sunt prost. Cred ca trebui sa raman la consultanta, nu la chestii din astea de implementare.
SELECT city,country FROM geoip_v4 WHERE
ip_from <= inet_aton('10.20.30.40')
AND
ip_to >= inet_aton('10.20.30.40');
Dar pentru istorie, ca sa mai rada si altii de ce chestii debitez cateodata, ramane si textul initial, cu mentiunea:
<rpetre> ce credeai ca e formula aia magica de ip to number? :)
Exista niste unii, care se cheama IP2Location de vand informatii de IP GeoLocation, adica pe scurt iti zic in ce oras/tara este inregistrat un subnet. Ei datele astea le dau CSV sa le incarci in ce vrei tu si dupa aia sa faci cautari in ele.
Pentru ca nu toate bazele de date au IP/subnet ca tip de data, daca te uiti dupa o singura adresa IP si tu tii subnet-uri in DB trebuie sa faci niste programare la mijloc sa afli de al cui sunbnet apartine o adresa IP. Asa ca oamenii astia au inventat ei o chestie de se cheama IP number: adica transformi 1.2.3.4 intr-un numar dupa o formula si dupa aia vezi tu cam intre ce valori se situeaza numarul ala in baza de date si afli cum ii cheama pe aia de-l utilizeaza si unde sunt inregistrati.
In baza de date informatiile sunt tinute sub forma de IP_FROM, IP_TO, CITY, COUNTRY plus alte chestii irelevante pentru post-ul asta. IP_FROM e numarul de la care “pleaca” un subnet si IP_TO numarul unde se “termina” subnetul. Formula dupa care se calculeaza IP number este:
X = A x (256*256*256) + B x (256*256) + C x 256 + D
In SQL (PostgreSQL* compatibil), un lookup dupa 10.20.30.40 se transforma in:
SELECT city, country FROM geoip_v4
WHERE ip_from <=
((split_part('10.20.30.40','.',1)::INT * (256*256*256))
+
(split_part('10.20.30.40','.',2)::INT * (256*256))
+
(split_part('10.20.30.40','.',3)::INT * 256)
+
split_part('10.20.30.40','.',4)::INT)
AND ip_to >=
((split_part('10.20.30.40','.',1)::INT * (256*256*256))
+
(split_part('10.20.30.40','.',2)::INT * (256*256))
+
(split_part('10.20.30.40','.',3)::INT * 256)
+
split_part('10.20.30.40','.',4)::INT)
<= si >= ajuta sa faci match pe adresa de retea si pe broadcast daca stii exact unde ma-sa se termina subnet-ul pe care-l cauti si daca chiar vrei asta, altfel e bine doar cu < si >.
Cast-ul la INT l-am pus ca baza de date imi facea cast in FLOAT dupa care il compara cu INT si ca sa-l compare mai facea un cast din FLOAT in INT. Am sarit un pas, ca profilerul pentru acelasi query zicea ca dureaza ~500msec sa se uite in 11M de inregistrari (beware of my small data) pentru VARCHAR::INT si vreo 45sec sa se uite tot acolo dupa VARCHAR::FLOAT::INT. E un pic dubios rezultatul de la profiler pentru ca nu cred ca dureaza asa mult sa te faci ca n-ai nimic dupa virgula, dar si faptul ca daca rulezi un query cu cele doua variante diferenta ochiometrica intre ele e de maxim 2 secunde.
Pe de alta parte, nu-s mare fan ideii de “la mine pe laptop merge”, mai adaugati voi hardware sa mearga repede si la voi.
De ce tineam eu minte ca postgres are ipv4 ca tip de data, sa poti face cautari de-astea direct?
Ihi, stiam bine: http://www.postgresql.org/docs/9.4/static/functions-net.html
Daca ziceai mysql in loc de postgres te iertam ;)
Esti pe aproape cu maieschiuelul ala :)