-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi all, this is the alpha version for the new email data type,
is not written as built in type but as plug in, Tome Lane and others
drove me in this direction.

The type is indexable and provide also conversion methods:

text <--> email
and the operator >>, is possible use it in select like:

select * from my_user where email >> 'hotmail.com';

this select will extract all records with an email inside
the domain 'hotmail.com'.

The validation routine is very simple, right now test only
the presence of character '@' (it's an alpha version), I'd like
to put it on pgfoundry but apparently my DNS are unable to resolve
www.pgfoundry.org. I'll put this version as soon the address become
available.

This is the first time that I wrote code for postgresql so please, if
you can, do a sort of code revision on it.

Comments are welcomed.

Regards
Gaetano Mendola.











-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFAptPG7UpzwH2SGd4RAhGYAKDMCfPhsBCwGAIFFTH2YHD/fHK2VACfZeUU
Y2nIWu56nvwhpZDTq1ARueo=
=b84w
-----END PGP SIGNATURE-----
Sun, 16 May 2004 04:36:55 +0200
Gaetano Mendola <mendola@bigfoot.com>
Email data type
Gaetano Mendola wrote:

> Hi all, this is the alpha version for the new email data type,
> is not written as built in type but as plug in, Tome Lane and others
> drove me in this direction.
> 
> The type is indexable and provide also conversion methods:
> 
> text <--> email
> and the operator >>, is possible use it in select like:
> 
> select * from my_user where email >> 'hotmail.com';
> 
> this select will extract all records with an email inside
> the domain 'hotmail.com'.
> 
> The validation routine is very simple, right now test only
> the presence of character '@' (it's an alpha version), I'd like
> to put it on pgfoundry but apparently my DNS are unable to resolve
> www.pgfoundry.org. I'll put this version as soon the address become
> available.
> 
> This is the first time that I wrote code for postgresql so please, if
> you can, do a sort of code revision on it.
> 
> Comments are welcomed.

Forget the version attached and use the version on pgfoundry.


Regards
Gaetano Mendola






Sun, 16 May 2004 23:39:33 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: Email data type
On Sun, May 16, 2004 at 04:36:55 +0200,
  Gaetano Mendola <mendola@bigfoot.com> wrote:
> 
> The type is indexable and provide also conversion methods:
> 
> text <--> email
> and the operator >>, is possible use it in select like:

When you are converting between text and email data, what format are the
text version of the address going to be? For example you might be using
rfc2821 encoding, rfc2822 encoding or concatenating the local part,
an @ sign and the domain name. Don't forget about domain literals.

Sun, 16 May 2004 23:25:29 -0500
Bruno Wolff III <bruno@wolff.to>
Re: Email data type
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Bruno Wolff III wrote:

| On Sun, May 16, 2004 at 04:36:55 +0200,
|   Gaetano Mendola <mendola@bigfoot.com> wrote:
|
|>The type is indexable and provide also conversion methods:
|>
|>text <--> email
|>and the operator >>, is possible use it in select like:
|
|
| When you are converting between text and email data, what format are the
| text version of the address going to be? For example you might be using
| rfc2821 encoding, rfc2822 encoding or concatenating the local part,
| an @ sign and the domain name. Don't forget about domain literals.

Actually I use <local_part>@<domain_name>

Also the validator will validate emails in this form, if you are thinking to
validate emails as:

"Gaetano M. Public"(junior)<gmendola@(new account)bigfoot.com>

that are perfectly valid I think that it's a valid option to consider.

About the domain literals, I think to validate it in the near future,
rejecting private subnet according to this list:

10.0.0.0 - 10.255.255.255
172.16.0.0 - 172.31.255.255
192.168.0.0 - 192.168.255.255
169.254.0.0 -169.254.255.255

I think I have to discard also the addresses with last octet equal to 256.

Any comments ?


Regards
Gaetano Mendola




-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFAqNPP7UpzwH2SGd4RAgqxAJ9UCPOOTpPN6Tl9w1748inn7t4a/QCg7pl1
8O3lHDwEKsCuCOJYQAo0vHM=
=wFo+
-----END PGP SIGNATURE-----


Mon, 17 May 2004 17:01:36 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: Email data type
On Mon, May 17, 2004 at 05:01:36PM +0200, Gaetano Mendola wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Bruno Wolff III wrote:
> 
> | On Sun, May 16, 2004 at 04:36:55 +0200,
> |   Gaetano Mendola <mendola@bigfoot.com> wrote:
> |
> |>The type is indexable and provide also conversion methods:
> |>
> |>text <--> email
> |>and the operator >>, is possible use it in select like:
> |
> |
> | When you are converting between text and email data, what format are the
> | text version of the address going to be? For example you might be using
> | rfc2821 encoding, rfc2822 encoding or concatenating the local part,
> | an @ sign and the domain name. Don't forget about domain literals.
> 
> Actually I use <local_part>@<domain_name>
> 
> Also the validator will validate emails in this form, if you are thinking to
> validate emails as:
> 
> "Gaetano M. Public"(junior)<gmendola@(new account)bigfoot.com>
> 
> that are perfectly valid I think that it's a valid option to consider.
> 
> About the domain literals, I think to validate it in the near future,
> rejecting private subnet according to this list:
> 
> 10.0.0.0 - 10.255.255.255
> 172.16.0.0 - 172.31.255.255
> 192.168.0.0 - 192.168.255.255
> 169.254.0.0 -169.254.255.255

Bad idea. If I'm testing I'm likely to be testing with addresses on my
local network. My local network is in 10/8. The middleware or the
application should be making those decisions. But if you are going to
filter on IP space, also consider class D & E space.

Also, a@10.11.12.13 is a syntactically valid email address, in the .13
TLD. It does not deliver to 10.11.12.13, or anywhere else, as of
today, unless the MTA or local recursive resolver is broken (a common
case). a@[10.11.12.13] is a whole other thing. As is a@[::10.11.12.13]
and various other IPv6 variants.

a@foo.bar is syntactically valid. a@foo.invalid is syntactically valid, but
should be immediately rejected. a@example.com is valid, even if known to
be non-deliverable.

> I think I have to discard also the addresses with last octet equal to 256.

Or equal to 4872014, come to that. Any reason you're looking at 256
in particular? If you mean .255 (or .0) then don't fall into that trap -
there are perfectly valid, routable addresses ending in both .0 and
.255, despite what some folks in Redmond would have you believe.

> Any comments ?

Parsing email addresses is a significant part of my day job, and email
address validation is a lot harder than it looks at first sight.

Don't forget quoting, whitespace, escaping and nesting parenthetical
comments. Also, remember that A@b.com and a@b.com are different email
addresses, while a@b.com and a@B.com are the same email
address. POSTMASTER@b.com and postmaster@b.com are the same email
address. ABUse@b.com and abuse@b.com may be the same address or
different email addresses, depending on which religious faction you
belong to.

In some contexts the empty string is a valid email address. In some
contexts "Postmaster" is a valid email address.

Also, one persons definition of a valid email address will be very
different from another persons definition of such. Many of those
definitions require some DNS resolution to make the decision.

I'm not entirely convinced that an email address is a simple and
well-defined enough datatype to handle comprehensively within the
DB. The validation decisions are complex and vary from application to
application.

(I use two text columns - localpart and domainpart, with an index
 on reverse(lower(domainpart)) and leave validation to the application,
 myself).

Cheers,
  Steve


Mon, 17 May 2004 08:45:47 -0700
Steve Atkins <steve@blighty.com>
Re: Email data type
On Monday 17 May 2004 8:45 am, Steve Atkins wrote:
> Also, a@10.11.12.13 is a syntactically valid email address, in the
> .13 TLD. It does not deliver to 10.11.12.13, or anywhere else, as
> of today, unless the MTA or local recursive resolver is broken (a
> common case). a@[10.11.12.13] is a whole other thing. As is
> a@[::10.11.12.13] and various other IPv6 variants.

Along those lines a@foo.bar.com and a@[foo.bar.com] are valid but they 
don't necessarily refer to the same mailbox (depends on the mx for 
foo.bar.com).

> Parsing email addresses is a significant part of my day job, and
> email address validation is a lot harder than it looks at first
> sight.

Yes, indeed.

> Don't forget quoting, whitespace, escaping and nesting
> parenthetical comments....

The "just looking for an @" breaks pretty quickly, say with 
"joe@"some.dom or a myriad of other variations.

> In some contexts the empty string is a valid email address. In some
> contexts "Postmaster" is a valid email address.

As are postmaster and pOsTmaSTeR and POSTmaster and they are all the 
same address.

> I'm not entirely convinced that an email address is a simple and
> well-defined enough datatype to handle comprehensively within the
> DB. The validation decisions are complex and vary from application
> to application.
>
> (I use two text columns - localpart and domainpart, with an index
>  on reverse(lower(domainpart)) and leave validation to the
> application, myself).

Indeed. A problem with the "email address" datatype is that it hinders 
normalization:

"Joe User" <joe@user.dom> is valid but a database designer would 
probably prefer columns for name and email, or if the addresses were 
all people, firstname, middlename, lastname, email.

As you mentioned, the email can be broken into localpart and 
domainpart but if the app requires it, the domainpart could be 
further rendered into toplevel (so you could find all the .gov or 
.edu), secondlevel (at least corresponds to a registrant) and 
subdomain(s) as necessary.

Cheers,
Steve


Mon, 17 May 2004 09:21:54 -0700
Steve Crawford <scrawford@pinpointresearch.com>
Re: Email data type
On Mon, May 17, 2004 at 09:21:54AM -0700, Steve Crawford wrote:

> Along those lines a@foo.bar.com and a@[foo.bar.com] are valid but they 
> don't necessarily refer to the same mailbox (depends on the mx for 
> foo.bar.com).

I don't believe the latter is actually valid, as it has to be an
address literal inside the square brackets. Until that's extended
by RFC that means IPv4 or IPv6 literals.

It's another example of "this is neither trivial nor well-defined",
though.

> > I'm not entirely convinced that an email address is a simple and
> > well-defined enough datatype to handle comprehensively within the
> > DB. The validation decisions are complex and vary from application
> > to application.
> >
> > (I use two text columns - localpart and domainpart, with an index
> >  on reverse(lower(domainpart)) and leave validation to the
> > application, myself).
> 
> Indeed. A problem with the "email address" datatype is that it hinders 
> normalization:

Yup. That's a bigger concern than the overall vagueness of the problem.

> "Joe User" <joe@user.dom> is valid but a database designer would 
> probably prefer columns for name and email, or if the addresses were 
> all people, firstname, middlename, lastname, email.
> 
> As you mentioned, the email can be broken into localpart and 
> domainpart but if the app requires it, the domainpart could be 
> further rendered into toplevel (so you could find all the .gov or 
> .edu), secondlevel (at least corresponds to a registrant) and 
> subdomain(s) as necessary.

That's why I index the domainpart on reverse(lower()) - that way I
can say WHERE reverse(lower(domainpart)) LIKE reverse('%.com')

Cheers,
  Steve

Mon, 17 May 2004 09:37:46 -0700
Steve Atkins <steve@blighty.com>
Re: Email data type
On Mon, May 17, 2004 at 17:01:36 +0200,
  Gaetano Mendola <mendola@bigfoot.com> wrote:
> 
> Also the validator will validate emails in this form, if you are thinking to
> validate emails as:
> 
> "Gaetano M. Public"(junior)<gmendola@(new account)bigfoot.com>

That appears to be an rfc 2822 address. RFC 2821 addresses are more limited
in that they can't have comments but still escape special characters.

> 
> that are perfectly valid I think that it's a valid option to consider.

What you want to store depends on what you are going to do with the address.
If you don't need the comments, storing it as a local part and a domain
is probably best.

> About the domain literals, I think to validate it in the near future,
> rejecting private subnet according to this list:
> 
> 10.0.0.0 - 10.255.255.255
> 172.16.0.0 - 172.31.255.255
> 192.168.0.0 - 192.168.255.255
> 169.254.0.0 -169.254.255.255

That would depend on whether or not sending to those addresses would make
sense in your case.

> 
> I think I have to discard also the addresses with last octet equal to 256.

Email addresses are in ascii and hence bytes should only contain characters
with the codes 0 through 127.

Mon, 17 May 2004 14:30:12 -0500
Bruno Wolff III <bruno@wolff.to>
Re: Email data type