Postgres Pro
Downloads
Home   >   mailing lists

Thread: mac.c

mac.c

From
Larry Rosenman
Date:
Does anyone want to do the coding we've talked about?  I'm afraid I'll
break stuff....  (I also don't have the current dev sources checked out, 
I just have 7.0.2 running). 

I'd really like to see this in 7.1....

Larry
-- 
Larry Rosenman                      http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Re: mac.c

From
Thomas Lockhart
Date:
> Does anyone want to do the coding we've talked about?  I'm afraid I'll
> break stuff....  (I also don't have the current dev sources checked out,
> I just have 7.0.2 running).

OK, I'll work on it for a couple of hours this morning.

I like Alex's suggestion for a standalone macaddr_trunc() function, then
a query which can lookup the brand from the external table.

The enthusiastic (but negative ;) feedback on putting LIKE into the mix
suggests that we should not use that mechanism, so "manuf()" or
"brand()" for an explicit function may be the best way to go.

Comments?
                    - Thomas

Re: mac.c

From
Thomas Lockhart
Date:
> Does anyone want to do the coding we've talked about?

OK, here is what I have so far, based on the work of Larry, Alex, and
others:

o Two new functions, trunc(macaddr) and text(macaddr), where the former
returns the mac address with the low (non-manufacturer) fields zeroed
out, and the latter converts to a text string. The text conversion is
nice because capabilities such as LIKE can be used transparently :) Will
need to add macaddr(text) for symmetry.

o Two utilities for contrib/mac, createoui and updateoui. The former
creates a table "macoui" with the fields oui and manufacturer. The
latter populates it with the contents of the file oui.txt, fetched from
the IEEE web site and processed by a slightly modified version of
Larry's awk script.

o An sql definition file, manuf.sql, which defines a function
manuf(macaddr) along the lines Alex had suggested. It returns a text
string of the manufacturer's name, or NULL if none is matched. You can
use COALESCE() to return something other than NULL if you want.

Should we have updateoui use wget to fetch oui.txt from the IEEE web
site? Or perhaps better we could have that in a separate utility?

Comments?
                  - Thomas

Some examples are

myst> ./createoui
myst> ./updateoui

lockhart=# select trunc(macaddr '00:01:a0:aa:bb:cc');      trunc
-------------------00:01:a0:00:00:00
(1 row)

lockhart=# select manuf('01:02:03:00:00:00');manuf
-------

(1 row)

lockhart=# select manuf('00:01:a0:00:00:00');        manuf
------------------------Infinilink Corporation
(1 row)

lockhart=# select manuf('00:01:A0:00:00:00');        manuf
------------------------Infinilink Corporation
(1 row)

lockhart=# select manuf('00:01:A0:00:00:01');        manuf
------------------------Infinilink Corporation
(1 row)

lockhart=# select coalesce(manuf('01:02:03:00:00:00'), 'nada');case
------nada
(1 row)

lockhart=# select * from macoui where oui like '00:aa%';       oui        |         manufacturer          
-------------------+-------------------------------00:aa:00:00:00:00 | INTEL CORPORATION00:aa:01:00:00:00 | INTEL
CORPORATION00:aa:02:00:00:00| INTEL CORPORATION00:aa:3c:00:00:00 | OLIVETTI TELECOM SPA (OLTECO)
 
(4 rows)

lockhart=# select * from macoui where oui like '00:AA%';oui | manufacturer 
-----+--------------
(0 rows)

lockhart=# select * from macoui where oui ilike '00:AA%';       oui        |         manufacturer          
-------------------+-------------------------------00:aa:00:00:00:00 | INTEL CORPORATION00:aa:01:00:00:00 | INTEL
CORPORATION00:aa:02:00:00:00| INTEL CORPORATION00:aa:3c:00:00:00 | OLIVETTI TELECOM SPA (OLTECO)
 
(4 rows)

Re: mac.c

From
Larry Rosenman
Date:
Thanks Thomas!

I just didn't want the ideas to die. 

Larry

* Thomas Lockhart <lockhart@alumni.caltech.edu> [000820 14:28]:
> > Does anyone want to do the coding we've talked about?
> 
> OK, here is what I have so far, based on the work of Larry, Alex, and
> others:
> 
> o Two new functions, trunc(macaddr) and text(macaddr), where the former
> returns the mac address with the low (non-manufacturer) fields zeroed
> out, and the latter converts to a text string. The text conversion is
> nice because capabilities such as LIKE can be used transparently :) Will
> need to add macaddr(text) for symmetry.
> 
> o Two utilities for contrib/mac, createoui and updateoui. The former
> creates a table "macoui" with the fields oui and manufacturer. The
> latter populates it with the contents of the file oui.txt, fetched from
> the IEEE web site and processed by a slightly modified version of
> Larry's awk script.
> 
> o An sql definition file, manuf.sql, which defines a function
> manuf(macaddr) along the lines Alex had suggested. It returns a text
> string of the manufacturer's name, or NULL if none is matched. You can
> use COALESCE() to return something other than NULL if you want.
> 
> Should we have updateoui use wget to fetch oui.txt from the IEEE web
> site? Or perhaps better we could have that in a separate utility?
> 
> Comments?
> 
>                    - Thomas
> 
> Some examples are
> 
> myst> ./createoui
> myst> ./updateoui
> 
> lockhart=# select trunc(macaddr '00:01:a0:aa:bb:cc');
>        trunc
> -------------------
>  00:01:a0:00:00:00
> (1 row)
> 
> lockhart=# select manuf('01:02:03:00:00:00');
>  manuf
> -------
> 
> (1 row)
> 
> lockhart=# select manuf('00:01:a0:00:00:00');
>          manuf
> ------------------------
>  Infinilink Corporation
> (1 row)
> 
> lockhart=# select manuf('00:01:A0:00:00:00');
>          manuf
> ------------------------
>  Infinilink Corporation
> (1 row)
> 
> lockhart=# select manuf('00:01:A0:00:00:01');
>          manuf
> ------------------------
>  Infinilink Corporation
> (1 row)
> 
> lockhart=# select coalesce(manuf('01:02:03:00:00:00'), 'nada');
>  case
> ------
>  nada
> (1 row)
> 
> lockhart=# select * from macoui where oui like '00:aa%';
>         oui        |         manufacturer          
> -------------------+-------------------------------
>  00:aa:00:00:00:00 | INTEL CORPORATION
>  00:aa:01:00:00:00 | INTEL CORPORATION
>  00:aa:02:00:00:00 | INTEL CORPORATION
>  00:aa:3c:00:00:00 | OLIVETTI TELECOM SPA (OLTECO)
> (4 rows)
> 
> lockhart=# select * from macoui where oui like '00:AA%';
>  oui | manufacturer 
> -----+--------------
> (0 rows)
> 
> lockhart=# select * from macoui where oui ilike '00:AA%';
>         oui        |         manufacturer          
> -------------------+-------------------------------
>  00:aa:00:00:00:00 | INTEL CORPORATION
>  00:aa:01:00:00:00 | INTEL CORPORATION
>  00:aa:02:00:00:00 | INTEL CORPORATION
>  00:aa:3c:00:00:00 | OLIVETTI TELECOM SPA (OLTECO)
> (4 rows)
-- 
Larry Rosenman                      http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Re: mac.c

From
Thomas Lockhart
Date:
> > o Two new functions, trunc(macaddr) and text(macaddr), where the former
> > returns the mac address with the low (non-manufacturer) fields zeroed
> > out, and the latter converts to a text string. The text conversion is
> > nice because capabilities such as LIKE can be used transparently :) Will
> > need to add macaddr(text) for symmetry.
> A cast shouldn't alter the value of the, er, value. This is like
> text(55) returning 'fifty-five'. What if I want to use substr() on the
> macaddr?

?? Just go ahead and use it! :)

There isn't anything new here, other than having the feature available
for macaddr as it is for other data types.
                     - Thomas