IP címek tárolása az adattárházban

Lassan minden cég eljut oda, hogy az interneten is jelen van. Vagy csak jelen van egy statikus honlappal, vagy már értékesít is. S ha már van egy weboldala akkor előbb utóbb kíváncsi lesz rá, hogy honnan, melyik országból vagy városból regisztrálnak/vásárolnak/ vagy csak böngésznek a látogatók.

Egy vállalkozás tehát előbb vagy utóbb elkezdi valamilyen adatbázisban idősorosan gyűjteni a látogatók IP címét. Az interneten lehet kapni olyan adatbázist, amelyből lekérdezhető, hogy egy konkrét IP cím melyik városhoz vagy országhoz tartozik. Így összekapcsolva az IP címet a város adatbázissal, már azt is meg tudja majd mondani, hogy mely városból vagy országból érkeztek a látogatók

A kérdés már csak az, hogy hogyan tárolja el az IP címet. Ön hogyan tárolná? Simán string-ként vagy egész számként?

Mielőtt megválaszolnánk a kérdést, nézzük meg hogy milyen követelményeknek kell megfelelnie az adattárházban tárolt IP címnek.

  • Legyen hatékonyan tárolva. (Ez talán evidens, hiszen nagy mennyiségű adatokról van szó)
  • Lehessen közte IP tartomány alapján keresni, azaz ki tudjuk választani azokat az IP címeket, amelyek egy bizonyos tartományba esnek.
  • Legyen a felhasználó számára olvasható.

Most nézzük meg a módszereket:

A legolvashatóbb IP cím tárolás

A felhasználó számára akkor legolvashatóbb az IP cím, ha azt Varchar(15)-ön vagy bigint-en tároljuk. Az előbbi esetben a 192.168.0.5 pont úgy néz ki ahogy most leírtam, az utóbbi esetben pedig így: 192168000005.

Míg a varchar(15)-ön tárolás 7-15 bájtba kerül IP címenként addig a bigint csak 8 bájtba. De létezik ennél sokkal hatékonyabb megoldás is.

A leghatékonyabb még olvasható IP cím tárolás

Hatékonyabban úgy tárolhatjuk IP címeinket, ha feldaraboljuk őket 4 részre, és négy tinyint oszlopban tároljuk őket. Tehát a 192.168.0.5 IP címet a pontok mentén feldaraboljuk négy részre és rendre betöltjük őket négy tinyint oszlopba. Így egy IP cím összesen csak 4 bájtot foglal el cím és még mindig olvasható marad. De onnantól kezdve, hogy keresni kell az IP Címek között, a szeparált tárolás már nehezen kezelhető. (nehéz például azokat az IP címeket kiválasztani, amelyek a 73.10.20.1 - 74.10.2.1 tartományba esnek.)

Létezik azonban egy olyan módszer amely segítségével, hatékonyan és kereshetően tudjuk tárolni az IP címeket:

A kereshető IP cím tárolási módszer

Tegyük fel, hogy A.B.C.D egy IP cím. Egy IP cím 4 bájton tárolható és könnyen kereshető marad, ha az alábbi képlet szerint alakítjuk át egész számmá:

ahol X az egész számként tárolt IP Cím.

Tegyük fel, hogy IP Címünk 192.168.0.5 Ekkor az egész számmá alakított IP cím: X=

192 * (256*256*256) +

168 * (256*256) +

0 * (256) +

5 * 1 - 2147483648, azaz X = 1084751877

Miért kell kivonni a kapott egész számból a 2147483648-at?

Azért, hogy beleférjünk az int értelmezési tartományába. Hiszen ha a levonjuk az egész számmá alakított 255.255.255.255 IP címből a 2147483648 akkor pont 2147483647-et, azaz 2^31-1 –et kapunk, ami épp az int felső határa. De ha kivonjuk a 0.0.0.0 IP címből 2147483648-at akkor is az int értelmezési tartományának alsó határán, azaz (-2)^31 –en belül maradunk.

Hogyan kapom vissza az egész számmá konvertált IP címből az olvasható (eredeti) IP Címet?

Egyszerű. Kell írni egy függvényt amely vissza tudja alakítani. Erre talál is példát a How should I store an IP address in SQL Server? című, Még SQL 2000-hez írt cikkben.

Összefoglalás

Az IP címek tárolására a legjobb megoldást akkor kapjuk, ha átalakítjuk az IP címeket egész számmá. Igaz, hogy így elvesztjük az olvashatóságot, de egy olyan kódot kapunk, amely

  • A lehető legkisebb helyet foglalja és
  • Könnyen kereshetünk benne IP cím tartomány alapján:

Select * from Tábla

Where IP between @IP_From And @IP2_To

 

Felhasznált irodalom:

Kővári Attila - BI projekt

Új hozzászólás