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
