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 > >
