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