Does mysql support constraints that call a function, if so, Add a constraint where column = lower(column).
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ilja Booij Sent: Thursday, March 04, 2004 1: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
