Yes, I was proposing a trigger, 2 in fact. The opposition isn't to doing it in the application per se, because your regex solution isn't done in the application it's still done in the RDBMS, hence your scalability argument is moot. The application enters into it in that the query written by the application has to include the regex. If another application, such as an ISP management tool is written that accesses the data, then it too would have to include the regex code. My solution is more elegent in that no special functionality has to be written into the query. The domain would be coerced to lower case on insert (and update) no matter if it's a DBMAIL utility or some other outside source. Once it's coerced to lower case there is no need to worry about it ever again.
> -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf > Of Curtis Maurand > Sent: Thursday, March 04, 2004 10:17 PM > To: [email protected] > Subject: RE: [Dbmail] CAP domain results in "no such user" mail bounce > > > > I'm not taking anything personally. I think that this is a good > and important discussion. The column could be queried quite often. I'm > only proposing solutions that might get folks to where they need to be. > The problem is that MySQL fits the bill when it comes to case > insensitivity WRT domain names. PostgreSQL does not. In order to comply > with the RFC's, domain names must be case insensitive. There are only > two places to make that happen; the query engine (dbmail) or at the > RDBMS. Since the mountain isn't coming to Mohammed, its time for > Mohammed to go to the mountain. I guess a stored procedure is OK, but it > really needs to be a triggered stored procedure and triggers can be like > setting a ping-pong ball loose in a room full of mousetraps. I'm very > leary of them (I know they are a proven technology.) > > I don't understand the opposition to having the application perform the > conversion rather than the RDBMS. The prior will scale better, > because it > can be distributed accross several mail servers accessing the > same message > store. > > > Curtis > > > On Thu, 4 Mar 2004, Rod K wrote: > > > Nothing personal, but I don't like your solution. First, how > often is the > > column queried? With each incoming email and each connection > by that user > > to check their mail at a minimum. That's a lot of selects > where a regular > > expression match would have to be performed. My solution > involves only a > > stored procedure call (via a trigger so that it is automatic) > on each update > > or insert which obviously are less in occurance and thereby place a > > significant decrease in load on the server. > > > > Also, your solution places the responsibility outside the RDBMS > and in the > > application. Other applications manipulating the data would not have a > > consistent view (unless you create and restrict access to views > which would > > require corresponding rules for inserts/updates). > > > > That said, I don't know why you bring up a comparison of regex to LIKE, > > ILIKE, etc in your reply. I never espoused those solutions > since they have > > the same problems as yours does regarding the points I'm trying > to address. > > > > > > > > > -----Original Message----- > > > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf > > > Of Curtis Maurand > > > Sent: Thursday, March 04, 2004 10:27 AM > > > To: [email protected] > > > Subject: RE: [Dbmail] CAP domain results in "no such user" mail bounce > > > > > > > > > > > > > > > This much is true. However, it would make the behavior of the two > > > databases consistent so that whatever solution you come up with can be > > > consistent and work without modification on either platform. > > > > > > However, PostgreSQL allows for regular expressions in the > queries. you > > > could alter your PostgreSQL queries. I have an example from: > > > > > > http://www.varlena.com/GeneralBits/57.php > > > > > > Regular Expressions in Queries > > > [GENERAL] Select Non Alpha Contents of a Column 29-Dec-2003 > > > > > > You can use POSIX regular expression pattern matching in PostgreSQL > > > queries. Regular expressions give you a much wider range of pattern > > > matching than either LIKE or SIMILAR TO. > > > > > > Regular expressions are the familiar ones you use with grep and > > > other unix > > > tools. There is a nice explanation of using regular expressions > > > in the 7.4 > > > PostgreSQL manual in 9.6.3. POSIX Regular Expressions. And a quick web > > > surf can give you more explanations and tutorials. > > > > > > To match to regular expressions in a PostgreSQL query use the > appropriate > > > operator: > > > > > > ~ Matches regular expression, case sensitive > > > ~* Matches regular expression, case insensitive > > > !~ Does not match regular expression, case sensitive > > > !~* Does not match regular expression, case insensitive > > > > > > This is very handy when you would want to do something like > selecting the > > > rows with a column beginning with non-alphabetic characters. > > > These queries > > > give identical results. The first does a case insensitive search > > > on values > > > which do not start with characters a through z. The second > does it in a > > > more traditional case sensitive way. > > > > > > SELECT * FROM table_name WHERE col !~* '^[a-z]' > > > SELECT * FROM table_name WHERE col !~ '^[a-zA-Z]'; > > > > > > Curtis > > > > > > > > > On Thu, 4 Mar 2004, Rod K wrote: > > > > > > > True. But totally irrelevent to the discussion. Making MySQL > > > treat domain > > > > names as case-sensitive doesn't help when domain names are > > > case-insensitive. > > > > > > > > > -----Original Message----- > > > > > From: [EMAIL PROTECTED] > > > [mailto:[EMAIL PROTECTED] Behalf > > > > > Of Curtis Maurand > > > > > Sent: Thursday, March 04, 2004 9:40 AM > > > > > To: [email protected] > > > > > Subject: RE: [Dbmail] CAP domain results in "no such > user" mail bounce > > > > > > > > > > > > > > > > > > > > if you want MySQL to be case sensitive: > > > > > > > > > > create table tablename ( > > > > > name varchar(120) binary, > > > > > ..., > > > > > ); > > > > > > > > > > Curtis > > > > > > > > > > > > > > > On Wed, 3 Mar 2004, Rod K wrote: > > > > > > > > > > > If I may make a suggestion. > > > > > > > > > > > > Since MySQL is case-insensitive by default, there shouldn't > > > be a need to > > > > > > worry about the case of the text in the field when using MySQL. > > > > > In the case > > > > > > of Postgres and the concern that an app other than dbmail > > > is inserting a > > > > > > user, you can use INSERT/UPDATE triggers to coerce the domain > > > > > to lower case. > > > > > > Since this is enforced at the db level it wouldn't > matter where the > > > > > > insert/update came from. > > > > > > > > > > > > > -----Original Message----- > > > > > > > From: [EMAIL PROTECTED] > > > > > [mailto:[EMAIL PROTECTED] Behalf > > > > > > > Of Ilja Booij > > > > > > > Sent: Wednesday, March 03, 2004 9:58 AM > > > > > > > To: [email protected] > > > > > > > Subject: Re: [Dbmail] CAP domain results in "no such > > > user" mail bounce > > > > > > > > > > > > > > > > > > > > > The solution I can think of now is the following: > > > > > > > > > > > > > > make sure all aliases (or at least their domain part) > go into the > > > > > > > database in lowercase. Whenever we check a domain > alias, we first > > > > > > > convert the domain string to lowercase. We can then feed > > > it to a query > > > > > > > without using lower(). > > > > > > > > > > > > > > I do see a problem with this though: What if somebody uses > > > > > another tool > > > > > > > to insert aliases into the database? Then a non-lowercase > > > domain can > > > > > > > slip in, which will not be found in a case-sensitive search. > > > > > > > > > > > > > > Ilja > > > > > > > > > > > > > > > > > > > > > Ilja Booij wrote: > > > > > > > > > > > > > > > Just when I was ready to commit the changes, I found > > > out that MySQL > > > > > > > > cannot have an index like: > > > > > > > > > > > > > > > > INDEX (lower(alias)), because it cannot have expressions in > > > > > an index.. > > > > > > > > > > > > > > > > So, the whole plan is a no-go. Unless we can come > up with some > > > > > > > other way > > > > > > > > to do the same thing. We could change all strings that we > > > > > need to check > > > > > > > > case insensitively to lowercase before feeding it > them to the > > > > > > > query for > > > > > > > > instance. Are there any convenient functions functions for > > > > > this (that > > > > > > > > also work with characters other than A-Z? > > > > > > > > > > > > > > > > Ilja > > > > > > > > > > > > > > > > > > > > > > > >>> > > > > > > > >>> -----Original Message----- > > > > > > > >>> From: [EMAIL PROTECTED] > > > > > > > [mailto:[EMAIL PROTECTED] On Behalf > > > > > > > >>> Of Ilja Booij > > > > > > > >>> Sent: Wednesday, March 03, 2004 11:30 PM > > > > > > > >>> To: [email protected] > > > > > > > >>> Subject: Re: [Dbmail] CAP domain results in "no such > > > > > user" mail bounce > > > > > > > >>> > > > > > > > >>> well, after some more reading, I guess it's a bit > different: > > > > > > > >>> > > > > > > > >>> IMAP RFC3501 takes no position on case > sensitivity in mailbox > > > > > > > names. So, > > > > > > > >>> we can support either case sensitive or case > > > insensitive mailbox > > > > > > > >>> names. Currently, mailboxes are case sensitive when using > > > > > PostgreSQL, > > > > > > > >>> and case > > > > > > > >>> insensitive when using MySQL. This might not be what we > > > > > want. We would > > > > > > > >>> probably like to have case sensitivity to be > > > consistent, whichever > > > > > > > >>> database backend we use. > > > > > > > >>> > > > > > > > >>> any opinions? > > > > > > > >>> > > > > > > > >>> Ilja > > > > > > > >>> > > > > > > > >>> John Hansen wrote: > > > > > > > >>> > > > > > > > >>> > > > > > > > >>>> Ilja, > > > > > > > >>>> > > > > > > > >>>> If you wan't RFC'ism, you should put code in the > > > > > software to do the > > > > > > > >>>> comparison, not in the SQL, mysql is not case > > > sensitive,. So on > > > > > > > >>>> mysql dbmail would not conform to the RFC in this case. > > > > > > > >>>> > > > > > > > >>>> ... John > > > > > > > >>>> > > > > > > > >>>> -----Original Message----- > > > > > > > >>>> From: [EMAIL PROTECTED] > > > [mailto:[EMAIL PROTECTED] On > > > > > > > >>>> Behalf Of Ilja Booij > > > > > > > >>>> Sent: Wednesday, March 03, 2004 10:05 PM > > > > > > > >>>> To: [email protected] > > > > > > > >>>> Subject: Re: [Dbmail] CAP domain results in "no > such user" > > > > > > > mail bounce > > > > > > > >>>> > > > > > > > >>>> In DBMail 1.2.3 the queries were all defined in > the backend > > > > > > > driver. In > > > > > > > >>> > > > > > > > >>> > > > > > > > >>> > > > > > > > >>> > > > > > > > >>>> DBMail 2.0, the backend driver has become much smaller > > > > > and simpler, > > > > > > > >>>> and most functionality has been moved to db.c, which is > > > > > in use by all > > > > > > > >>> > > > > > > > >>> > > > > > > > >>> > > > > > > > >>> (read: > > > > > > > >>> > > > > > > > >>>> both) backends. > > > > > > > >>>> > > > > > > > >>>> Anyway, your suggestions should work. > > > > > > > >>>> > > > > > > > >>>> By the way, I don't think we should lowercase the > > > > > mailboxes, as RFC > > > > > > > >>>> 3501 takes no position on case-sensitivity of mailbox > > > > > names, except > > > > > > > >>>> for "INBOX", which should always be case insensitive. > > > > > > > >>>> > > > > > > > >>>> Case insensitivity is limited to aliases (including > > > > > domain aliases) > > > > > > > >>>> and usernames, I guess. > > > > > > > >>>> > > > > > > > >>>> Ilja > > > > > > > >>>> > > > > > > > >>>> John Hansen wrote: > > > > > > > >>>> > > > > > > > >>>> > > > > > > > >>>> > > > > > > > >>>>> I'm confused,.... aren't the sql queries defined in > > > each backend > > > > > > > >>>> > > > > > > > >>>> > > > > > > > >>>> > > > > > > > >>>> driver? > > > > > > > >>>> > > > > > > > >>>> > > > > > > > >>>>> If not, > > > > > > > >>>>> lower(column)=lower("value%"), and > > > > > > > >>>>> lower(column) like lower("value%") > > > > > > > >>>>> > > > > > > > >>>>> should do the trick. > > > > > > > >>>>> > > > > > > > >>>>> ... John > > > > > > > >>>>> -----Original Message----- > > > > > > > >>>>> From: [EMAIL PROTECTED] > > > > > [mailto:[EMAIL PROTECTED] On > > > > > > > >>>>> Behalf Of Ilja Booij > > > > > > > >>>>> Sent: Wednesday, March 03, 2004 8:47 PM > > > > > > > >>>>> To: [email protected] > > > > > > > >>>>> Subject: Re: [Dbmail] CAP domain results in "no > such user" > > > > > > > mail bounce > > > > > > > >>>>> > > > > > > > >>>>> We can't use ILIKE, as it's not supported by MySQL. > > > > > It's also not a > > > > > > > >>>>> part of SQL92, is it? I'll look for a way around this. > > > > > > > >>>>> > > > > > > > >>>>> Ilja > > > > > > > >>>>> > > > > > > > >>>>> John Hansen wrote: > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>>> Probably a bug from being ported to postgres, as mysql > > > > > is not case > > > > > > > >>>>>> sensitive, but postgresql is. > > > > > > > >>>>>> > > > > > > > >>>>>> As such, all comparisons in where clauses should > > > be cast using > > > > > > > >>>>>> lower() > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>>> on both sides of the comparison sign. Or in the case > > > > > of LIKE, use > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>> ILIKE. > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>>> Regards, > > > > > > > >>>>>> > > > > > > > >>>>>> John > > > > > > > >>>>>> > > > > > > > >>>>>> -----Original Message----- > > > > > > > >>>>>> From: [EMAIL PROTECTED] > > > > > [mailto:[EMAIL PROTECTED] On > > > > > > > >>>>>> Behalf Of David > > > > > > > >>>>>> Sent: Tuesday, February 24, 2004 9:47 AM > > > > > > > >>>>>> To: [email protected] > > > > > > > >>>>>> Subject: [Dbmail] CAP domain results in "no such user" > > > > > mail bounce > > > > > > > >>>>>> > > > > > > > >>>>>> > > > > > > > >>>>>> I noticed that when dbmail checks for a > delivery point, if > > > > > > > the domain > > > > > > > >>> > > > > > > > >>> > > > > > > > >>> > > > > > > > >>> > > > > > > > >>>>>> name does not match one listed in the aliases > table case > > > > > > > for case, it > > > > > > > >>> > > > > > > > >>> > > > > > > > >>> > > > > > > > >>> > > > > > > > >>>>>> will bounced the mail with "so such user". Has > > > this comparison > > > > > > > >>>>>> always > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>>> been case sensitive? For example, if > > > [EMAIL PROTECTED] is in the > > > > > > > >>>>>> aliases > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>>> table and then I get a mail address to [EMAIL PROTECTED], > > > > > > > dbmail rejects > > > > > > > >>> > > > > > > > >>> > > > > > > > >>> > > > > > > > >>> > > > > > > > >>>>>> it. Actually it will reject anything not spelled > > > exactly as > > > > > > > >>>>>> "example.com". This is the error message generated. > > > > > > > >>>>>> > > > > > > > >>>>>> dbmail/smtp[31935]: bounce.c,bounce: sending 'no such > > > > > user' bounce > > > > > > > >>>>>> for > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>>> destination [EMAIL PROTECTED] > > > > > > > >>>>>> > > > > > > > >>>>>> Is this a postfix problem, PostgreSQL problem, or a > > > > > dbmail problem? > > > > > > > >>>>>> > > > > > > > >>>>>> __________________________________ > > > > > > > >>>>>> Do you Yahoo!? > > > > > > > >>>>>> Yahoo! Mail SpamGuard - Read only the mail you want. > > > > > > > >>>>>> http://antispam.yahoo.com/tools > > > > > > > >>>>>> _______________________________________________ > > > > > > > >>>>>> Dbmail mailing list > > > > > > > >>>>>> [email protected] > > > > > https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > > > > >>>>>> _______________________________________________ > > > > > > > >>>>>> Dbmail mailing list > > > > > > > >>>>>> [email protected] > > > > > > > >>>>>> https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>> _______________________________________________ > > > > > > > >>>>> Dbmail mailing list > > > > > > > >>>>> [email protected] > > > > > > > >>>>> https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > > > > >>>>> > > > > > > > >>>>> > > > > > > > >>>>> _______________________________________________ > > > > > > > >>>>> Dbmail mailing list > > > > > > > >>>>> [email protected] > > > > > > > >>>>> https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > > > > >>>> > > > > > > > >>>> > > > > > > > >>>> > > > > > > > >>>> _______________________________________________ > > > > > > > >>>> Dbmail mailing list > > > > > > > >>>> [email protected] > > > > > > > >>>> https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > > > > >>>> > > > > > > > >>>> > > > > > > > >>>> _______________________________________________ > > > > > > > >>>> Dbmail mailing list > > > > > > > >>>> [email protected] > > > > > > > >>>> https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > > > > >>> > > > > > > > >>> > > > > > > > >>> > > > > > > > >>> _______________________________________________ > > > > > > > >>> Dbmail mailing list > > > > > > > >>> [email protected] > > > > > > > >>> https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > > > > >>> > > > > > > > >>> > > > > > > > >>> _______________________________________________ > > > > > > > >>> Dbmail mailing list > > > > > > > >>> [email protected] > > > > > > > >>> https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > > > > >> > > > > > > > >> > > > > > > > >> _______________________________________________ > > > > > > > >> Dbmail mailing list > > > > > > > >> [email protected] > > > > > > > >> https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > > > > > > > > > > > > > _______________________________________________ > > > > > > > > Dbmail mailing list > > > > > > > > [email protected] > > > > > > > > https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > > > > _______________________________________________ > > > > > > > Dbmail mailing list > > > > > > > [email protected] > > > > > > > https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > _______________________________________________ > > > > > > Dbmail mailing list > > > > > > [email protected] > > > > > > https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > > > > > > > > > > > > > -- > > > > > -- > > > > > Curtis Maurand > > > > > mailto:[EMAIL PROTECTED] > > > > > http://www.maurand.com > > > > > > > > > > > > > > > _______________________________________________ > > > > > Dbmail mailing list > > > > > [email protected] > > > > > https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > > > > > > > > > > > > > > > > > > > _______________________________________________ > > > > Dbmail mailing list > > > > [email protected] > > > > https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > > > > > > > -- > > > -- > > > Curtis Maurand > > > mailto:[EMAIL PROTECTED] > > > http://www.maurand.com > > > > > > > > > _______________________________________________ > > > Dbmail mailing list > > > [email protected] > > > https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > > > > > > > > > _______________________________________________ > > Dbmail mailing list > > [email protected] > > https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > -- > -- > Curtis Maurand > mailto:[EMAIL PROTECTED] > http://www.maurand.com > > > _______________________________________________ > Dbmail mailing list > [email protected] > https://mailman.fastxs.nl/mailman/listinfo/dbmail > >
