John Kopanas
2005-Jun-28 23:11 UTC
Moving From mySQL to Postgresql and problems with CaSe-SensiTIvity :-)
I thought I was finished moving my web app from mySQL to postgresql when I realized that there was a change in behaviour from mysql to postgresql. When I would search for a postal code if the user wrote ''H9h 3y2'' it would not be found because in the DB it is stored as ''H9H 3Y2''. And my problem is not only there but throughout the application. What is everyone else doing to solve this problem? John Kopanas http://www.thedatingguy.com - Online Dating the way it should be. http://blog.thedatingguy.com
Jason Foreman
2005-Jun-28 23:23 UTC
Re: Moving From mySQL to Postgresql and problems with CaSe-SensiTIvity :-)
On 6/28/05, John Kopanas <john.kopanas-O1KSuMybMhqBUy7/sJONFg@public.gmane.org> wrote:> I thought I was finished moving my web app from mySQL to postgresql > when I realized that there was a change in behaviour from mysql to > postgresql. When I would search for a postal code if the user wrote > ''H9h 3y2'' it would not be found because in the DB it is stored as > ''H9H 3Y2''. And my problem is not only there but throughout the > application. > > What is everyone else doing to solve this problem? >Well, if you know you only ever need H9H 3Y2 and not h9h 3y2 or any other variation, then pick lower or upper case and always convert values to that when putting in the db, and always convert parameters to the appropriate case when searching. Otherwise, put big "Seach is case sensitive" disclaimers everywhere :) I go through this every day, but with Oracle vs MS SQL Server. PostgreSQL also has some regex support, so you could use find_by_sql and use that: select * from postal_codes where code ~* ''H9H 3Y2'' would do a case insensitive search. Jason> John Kopanas > http://www.thedatingguy.com - Online Dating the way it should be. > http://blog.thedatingguy.com > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Adam Majer
2005-Jun-28 23:28 UTC
Re: Moving From mySQL to Postgresql and problems with CaSe-SensiTIvity :-)
John Kopanas wrote:> I thought I was finished moving my web app from mySQL to postgresql > when I realized that there was a change in behaviour from mysql to > postgresql. When I would search for a postal code if the user wrote > ''H9h 3y2'' it would not be found because in the DB it is stored as > ''H9H 3Y2''. And my problem is not only there but throughout the > application. > > What is everyone else doing to solve this problem? >user_input_string.upcase is your Ruby friend or, use upper(...) PostgreSQL function if you write your own SQL and want to use it. - Adam PS. You probably should enforce in the database that certain data is always, like postal codes, uppercase. You can do that with triggers on INSERT and UPDATE.
John Kopanas
2005-Jun-28 23:39 UTC
Re: Moving From mySQL to Postgresql and problems with CaSe-SensiTIvity :-)
But that means I am going to never be able to use the built in find_by_nickname_and_sex like features in rails anymore. Seems so anti-rails. Or am I missing something? On 28-Jun-05, at 7:23 PM, Jason Foreman wrote:> On 6/28/05, John Kopanas <john.kopanas-O1KSuMybMhqBUy7/sJONFg@public.gmane.org> wrote: > >> I thought I was finished moving my web app from mySQL to postgresql >> when I realized that there was a change in behaviour from mysql to >> postgresql. When I would search for a postal code if the user wrote >> ''H9h 3y2'' it would not be found because in the DB it is stored as >> ''H9H 3Y2''. And my problem is not only there but throughout the >> application. >> >> What is everyone else doing to solve this problem? >> >> > > Well, if you know you only ever need H9H 3Y2 and not h9h 3y2 or any > other variation, then pick lower or upper case and always convert > values to that when putting in the db, and always convert parameters > to the appropriate case when searching. > > Otherwise, put big "Seach is case sensitive" disclaimers everywhere :) > > I go through this every day, but with Oracle vs MS SQL Server. > > PostgreSQL also has some regex support, so you could use find_by_sql > and use that: > > select * from postal_codes where code ~* ''H9H 3Y2'' > > would do a case insensitive search. > > > Jason > > >> John Kopanas >> http://www.thedatingguy.com - Online Dating the way it should be. >> http://blog.thedatingguy.com >> >> >> _______________________________________________ >> Rails mailing list >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> >> > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >John Kopanas http://www.thedatingguy.com - Online Dating the way it should be. http://blog.thedatingguy.com
John Kopanas
2005-Jun-28 23:40 UTC
Re: Moving From mySQL to Postgresql and problems with CaSe-SensiTIvity :-)
That also means all my validation in my models that depend on things not being case-insensitive are for the garbage. No? On 28-Jun-05, at 7:23 PM, Jason Foreman wrote:> On 6/28/05, John Kopanas <john.kopanas-O1KSuMybMhqBUy7/sJONFg@public.gmane.org> wrote: > >> I thought I was finished moving my web app from mySQL to postgresql >> when I realized that there was a change in behaviour from mysql to >> postgresql. When I would search for a postal code if the user wrote >> ''H9h 3y2'' it would not be found because in the DB it is stored as >> ''H9H 3Y2''. And my problem is not only there but throughout the >> application. >> >> What is everyone else doing to solve this problem? >> >> > > Well, if you know you only ever need H9H 3Y2 and not h9h 3y2 or any > other variation, then pick lower or upper case and always convert > values to that when putting in the db, and always convert parameters > to the appropriate case when searching. > > Otherwise, put big "Seach is case sensitive" disclaimers everywhere :) > > I go through this every day, but with Oracle vs MS SQL Server. > > PostgreSQL also has some regex support, so you could use find_by_sql > and use that: > > select * from postal_codes where code ~* ''H9H 3Y2'' > > would do a case insensitive search. > > > Jason > > >> John Kopanas >> http://www.thedatingguy.com - Online Dating the way it should be. >> http://blog.thedatingguy.com >> >> >> _______________________________________________ >> Rails mailing list >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> >> > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >John Kopanas http://www.thedatingguy.com - Online Dating the way it should be. http://blog.thedatingguy.com
Ezra Zygmuntowicz
2005-Jun-29 02:05 UTC
Re: Moving From mySQL to Postgresql and problems with CaSe-SensiTIvity :-)
On Jun 28, 2005, at 4:11 PM, John Kopanas wrote:> I thought I was finished moving my web app from mySQL to postgresql > when I realized that there was a change in behaviour from mysql to > postgresql. When I would search for a postal code if the user > wrote ''H9h 3y2'' it would not be found because in the DB it is > stored as ''H9H 3Y2''. And my problem is not only there but > throughout the application. > > What is everyone else doing to solve this problem? > > John Kopanas > http://www.thedatingguy.com - Online Dating the way it should be. > http://blog.thedatingguy.com > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >> ''H9h 3y2''"H9h3y2".upcase! -Ezra Zygmuntowicz WebMaster Yakima Herald-Republic Newspaper ezra-gdxLOakOTQ9oetBuM9ipNAC/G2K4zDHf@public.gmane.org 509-577-7732
John Kopanas
2005-Jun-29 05:58 UTC
Re: Moving From mySQL to Postgresql and problems with CaSe-SensiTIvity :-)
How about with validation... what would I do with for example: validates_uniqueness_of :nickname JOhnBaku and Johnbaku in my eyes are the same. But for the system they will not be the same. So how do I handle this? Thanks for your help. On 28-Jun-05, at 10:05 PM, Ezra Zygmuntowicz wrote:> > On Jun 28, 2005, at 4:11 PM, John Kopanas wrote: > > >> I thought I was finished moving my web app from mySQL to >> postgresql when I realized that there was a change in behaviour >> from mysql to postgresql. When I would search for a postal code >> if the user wrote ''H9h 3y2'' it would not be found because in the >> DB it is stored as ''H9H 3Y2''. And my problem is not only there >> but throughout the application. >> >> What is everyone else doing to solve this problem? >> >> John Kopanas >> http://www.thedatingguy.com - Online Dating the way it should be. >> http://blog.thedatingguy.com >> >> >> _______________________________________________ >> Rails mailing list >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> >> > > >> ''H9h 3y2'' >> > > "H9h3y2".upcase! > > > -Ezra Zygmuntowicz > WebMaster > Yakima Herald-Republic Newspaper > ezra-gdxLOakOTQ9oetBuM9ipNAC/G2K4zDHf@public.gmane.org > 509-577-7732 > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >John Kopanas http://www.thedatingguy.com - Online Dating the way it should be. http://blog.thedatingguy.com
Dick Davies
2005-Jun-29 08:43 UTC
Re: Moving From mySQL to Postgresql and problems with CaSe-SensiTIvity :-)
I did this in my model class - seems to work transparently with
validation etc.
class Player < ActiveRecord::Base
# force lowercase into the db and strip whitespace without making a fuss
def username=(u)
write_attribute(''username'', u.downcase.gsub(/\s+/,
'''') )
end
end
* John Kopanas <john.kopanas-O1KSuMybMhqBUy7/sJONFg@public.gmane.org>
[0658 06:58]:> How about with validation... what would I do with for example:
>
> validates_uniqueness_of :nickname
>
> JOhnBaku and Johnbaku in my eyes are the same. But for the system
> they will not be the same. So how do I handle this?
>
> Thanks for your help.
>
>
> On 28-Jun-05, at 10:05 PM, Ezra Zygmuntowicz wrote:
>
> >
> >On Jun 28, 2005, at 4:11 PM, John Kopanas wrote:
> >
> >
> >>I thought I was finished moving my web app from mySQL to
> >>postgresql when I realized that there was a change in behaviour
> >>from mysql to postgresql. When I would search for a postal code
> >>if the user wrote ''H9h 3y2'' it would not be found
because in the
> >>DB it is stored as ''H9H 3Y2''. And my problem is
not only there
> >>but throughout the application.
> >>
> >>What is everyone else doing to solve this problem?
> >>
> >>John Kopanas
> >>http://www.thedatingguy.com - Online Dating the way it should be.
> >>http://blog.thedatingguy.com
> >>
> >>
> >>_______________________________________________
> >>Rails mailing list
> >>Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> >>http://lists.rubyonrails.org/mailman/listinfo/rails
> >>
> >>
> >
> >
> >>''H9h 3y2''
> >>
> >
> >"H9h3y2".upcase!
> >
> >
> >-Ezra Zygmuntowicz
> >WebMaster
> >Yakima Herald-Republic Newspaper
> >ezra-gdxLOakOTQ9oetBuM9ipNAC/G2K4zDHf@public.gmane.org
> >509-577-7732
> >
> >_______________________________________________
> >Rails mailing list
> >Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> >http://lists.rubyonrails.org/mailman/listinfo/rails
> >
>
>
> John Kopanas
> http://www.thedatingguy.com - Online Dating the way it should be.
> http://blog.thedatingguy.com
>
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
--
''And if you think you''re going to bleed all over me
you''re even wronger than you normally be''
-- The Specials, ''Little Bitch''
Rasputin :: Jack of All Trades - Master of Nuns
John Kopanas
2005-Jun-29 13:24 UTC
Re: Moving From mySQL to Postgresql and problems with CaSe-SensiTIvity :-)
The whole thing is that I don''t want to force people''s nickname''s to lower case. I want to keep them as they entered them. If validation does not work out of the box with postgresql then is postgresql not a recommended DB for Rails? On 29-Jun-05, at 4:43 AM, Dick Davies wrote:> I did this in my model class - seems to work transparently with > validation etc. > > > class Player < ActiveRecord::Base > > # force lowercase into the db and strip whitespace without making > a fuss > def username=(u) > write_attribute(''username'', u.downcase.gsub(/\s+/, '''') ) > end > > end > > * John Kopanas <john.kopanas-O1KSuMybMhqBUy7/sJONFg@public.gmane.org> [0658 06:58]: > >> How about with validation... what would I do with for example: >> >> validates_uniqueness_of :nickname >> >> JOhnBaku and Johnbaku in my eyes are the same. But for the system >> they will not be the same. So how do I handle this? >> >> Thanks for your help. >> >> >> On 28-Jun-05, at 10:05 PM, Ezra Zygmuntowicz wrote: >> >> >>> >>> On Jun 28, 2005, at 4:11 PM, John Kopanas wrote: >>> >>> >>> >>>> I thought I was finished moving my web app from mySQL to >>>> postgresql when I realized that there was a change in behaviour >>>> from mysql to postgresql. When I would search for a postal code >>>> if the user wrote ''H9h 3y2'' it would not be found because in the >>>> DB it is stored as ''H9H 3Y2''. And my problem is not only there >>>> but throughout the application. >>>> >>>> What is everyone else doing to solve this problem? >>>> >>>> John Kopanas >>>> http://www.thedatingguy.com - Online Dating the way it should be. >>>> http://blog.thedatingguy.com >>>> >>>> >>>> _______________________________________________ >>>> Rails mailing list >>>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >>>> http://lists.rubyonrails.org/mailman/listinfo/rails >>>> >>>> >>>> >>> >>> >>> >>>> ''H9h 3y2'' >>>> >>>> >>> >>> "H9h3y2".upcase! >>> >>> >>> -Ezra Zygmuntowicz >>> WebMaster >>> Yakima Herald-Republic Newspaper >>> ezra-gdxLOakOTQ9oetBuM9ipNAC/G2K4zDHf@public.gmane.org >>> 509-577-7732 >>> >>> _______________________________________________ >>> Rails mailing list >>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >>> http://lists.rubyonrails.org/mailman/listinfo/rails >>> >>> >> >> >> John Kopanas >> http://www.thedatingguy.com - Online Dating the way it should be. >> http://blog.thedatingguy.com >> >> >> _______________________________________________ >> Rails mailing list >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> > > -- > ''And if you think you''re going to bleed all over me > you''re even wronger than you normally be'' > -- The Specials, ''Little Bitch'' > Rasputin :: Jack of All Trades - Master of Nuns > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >John Kopanas http://www.thedatingguy.com - Online Dating the way it should be. http://blog.thedatingguy.com
John Higgins
2005-Jun-29 14:08 UTC
Re: Moving From mySQL to Postgresql and problems with CaSe-SensiTIvity :-)
Validation works just fine out of the box - A and a are NOT the same in any way shape or form. Just because mysql allows case-insensitive searches doesn''t mean that it''s correct or that everyone else is wrong. You sometimes have to be a little creative in solutions. Everything doesn''t work exactly as you want or exactly how you would like. You can''t have your cake and eat it to - you have to make some decisions here. You want two versions of the nickname stored in the database a display version and a "search/validation" version. And yes, this breaks most database rules - HOWEVER, if you want your cake and you wish to eat as well, something has to give at some point. You cannot gain all the benefits of rails in this case without giving up something. You are sort of doing a rather niche thing in that most items either aren''t case-sensitive or if they are they aren''t looked much at for uniqueness or such on top of the fact that you are also trying to use a lot of the "pre-packaged" rails tools which aren''t case-insensitve/sensitive aware. Having the double field will enable you to display what you wish and store what you wish without any problems. Just override the display field''s = method and have it store a upper/lower case version in the validation version. Make the validation version''s = method private so you don''t accidentally get them out of sync. It this more complicated - yes - but I''m sure at some point you would have been in the reverse situation where you wanted a case-sensitive search and mysql would have given you grief in that scenario. You moved to postgresql for a reason - and I''m sure that reason is more important then adding a field to the database and modifying a couple of methods to get whatever advantages you initially saw in Postgresql. -- John W Higgins wishdev-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
Jason Foreman
2005-Jun-29 14:12 UTC
Re: Moving From mySQL to Postgresql and problems with CaSe-SensiTIvity :-)
On 6/29/05, John Kopanas <john.kopanas-O1KSuMybMhqBUy7/sJONFg@public.gmane.org> wrote:> The whole thing is that I don''t want to force people''s nickname''s to > lower case. I want to keep them as they entered them. >It may not be the answer you''re looking for, but you can create a unique constraint in the database to enforce this rule: create table foo (id int primary key, name varchar(20)); create unique index foo_unique_name on foo (lower(name)); insert into foo values (1, ''foo''); insert into foo values (2, ''FOO''); -- this will give you a duplicate key violation... This should accomplish what you want, but you won''t know until you hit the database. I''m sure there may be a way to override the validation method in your model to do it that way as well.> If validation does not work out of the box with postgresql then is > postgresql not a recommended DB for Rails? >I think having a case sensitive database is a good thing, people just get used to anomolies like MySQL and MS SQL that have oddball behaviors </flamebait> :) In all seriousness though, a lot of people use PostgreSQL with Rails with great success, so don''t get discouraged too quickly! hth, Jason> > On 29-Jun-05, at 4:43 AM, Dick Davies wrote: > > > I did this in my model class - seems to work transparently with > > validation etc. > > > > > > class Player < ActiveRecord::Base > > > > # force lowercase into the db and strip whitespace without making > > a fuss > > def username=(u) > > write_attribute(''username'', u.downcase.gsub(/\s+/, '''') ) > > end > > > > end > > > > * John Kopanas <john.kopanas-O1KSuMybMhqBUy7/sJONFg@public.gmane.org> [0658 06:58]: > > > >> How about with validation... what would I do with for example: > >> > >> validates_uniqueness_of :nickname > >> > >> JOhnBaku and Johnbaku in my eyes are the same. But for the system > >> they will not be the same. So how do I handle this? > >> > >> Thanks for your help. > >> > >> > >> On 28-Jun-05, at 10:05 PM, Ezra Zygmuntowicz wrote: > >> > >> > >>> > >>> On Jun 28, 2005, at 4:11 PM, John Kopanas wrote: > >>> > >>> > >>> > >>>> I thought I was finished moving my web app from mySQL to > >>>> postgresql when I realized that there was a change in behaviour > >>>> from mysql to postgresql. When I would search for a postal code > >>>> if the user wrote ''H9h 3y2'' it would not be found because in the > >>>> DB it is stored as ''H9H 3Y2''. And my problem is not only there > >>>> but throughout the application. > >>>> > >>>> What is everyone else doing to solve this problem? > >>>> > >>>> John Kopanas > >>>> http://www.thedatingguy.com - Online Dating the way it should be. > >>>> http://blog.thedatingguy.com > >>>> > >>>> > >>>> _______________________________________________ > >>>> Rails mailing list > >>>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > >>>> http://lists.rubyonrails.org/mailman/listinfo/rails > >>>> > >>>> > >>>> > >>> > >>> > >>> > >>>> ''H9h 3y2'' > >>>> > >>>> > >>> > >>> "H9h3y2".upcase! > >>> > >>> > >>> -Ezra Zygmuntowicz > >>> WebMaster > >>> Yakima Herald-Republic Newspaper > >>> ezra-gdxLOakOTQ9oetBuM9ipNAC/G2K4zDHf@public.gmane.org > >>> 509-577-7732 > >>> > >>> _______________________________________________ > >>> Rails mailing list > >>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > >>> http://lists.rubyonrails.org/mailman/listinfo/rails > >>> > >>> > >> > >> > >> John Kopanas > >> http://www.thedatingguy.com - Online Dating the way it should be. > >> http://blog.thedatingguy.com > >> > >> > >> _______________________________________________ > >> Rails mailing list > >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > >> http://lists.rubyonrails.org/mailman/listinfo/rails > >> > > > > -- > > ''And if you think you''re going to bleed all over me > > you''re even wronger than you normally be'' > > -- The Specials, ''Little Bitch'' > > Rasputin :: Jack of All Trades - Master of Nuns > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > John Kopanas > http://www.thedatingguy.com - Online Dating the way it should be. > http://blog.thedatingguy.com > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Ezra Zygmuntowicz
2005-Jun-29 14:20 UTC
Re: Moving From mySQL to Postgresql and problems with CaSe-SensiTIvity :-)
Yep and then you can call username.capitalize! if you want to display it with the first letter capitalized. -Ezra On Jun 29, 2005, at 1:43 AM, Dick Davies wrote:> I did this in my model class - seems to work transparently with > validation etc. > > > class Player < ActiveRecord::Base > > # force lowercase into the db and strip whitespace without making > a fuss > def username=(u) > write_attribute(''username'', u.downcase.gsub(/\s+/, '''') ) > end > > end > > * John Kopanas <john.kopanas-O1KSuMybMhqBUy7/sJONFg@public.gmane.org> [0658 06:58]: > >> How about with validation... what would I do with for example: >> >> validates_uniqueness_of :nickname >> >> JOhnBaku and Johnbaku in my eyes are the same. But for the system >> they will not be the same. So how do I handle this? >> >> Thanks for your help. >> >> >> On 28-Jun-05, at 10:05 PM, Ezra Zygmuntowicz wrote: >> >> >>> >>> On Jun 28, 2005, at 4:11 PM, John Kopanas wrote: >>> >>> >>> >>>> I thought I was finished moving my web app from mySQL to >>>> postgresql when I realized that there was a change in behaviour >>>> from mysql to postgresql. When I would search for a postal code >>>> if the user wrote ''H9h 3y2'' it would not be found because in the >>>> DB it is stored as ''H9H 3Y2''. And my problem is not only there >>>> but throughout the application. >>>> >>>> What is everyone else doing to solve this problem? >>>> >>>> John Kopanas >>>> http://www.thedatingguy.com - Online Dating the way it should be. >>>> http://blog.thedatingguy.com >>>> >>>> >>>> _______________________________________________ >>>> Rails mailing list >>>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >>>> http://lists.rubyonrails.org/mailman/listinfo/rails >>>> >>>> >>>> >>> >>> >>> >>>> ''H9h 3y2'' >>>> >>>> >>> >>> "H9h3y2".upcase! >>> >>> >>> -Ezra Zygmuntowicz >>> WebMaster >>> Yakima Herald-Republic Newspaper >>> ezra-gdxLOakOTQ9oetBuM9ipNAC/G2K4zDHf@public.gmane.org >>> 509-577-7732 >>> >>> _______________________________________________ >>> Rails mailing list >>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >>> http://lists.rubyonrails.org/mailman/listinfo/rails >>> >>> >> >> >> John Kopanas >> http://www.thedatingguy.com - Online Dating the way it should be. >> http://blog.thedatingguy.com >> >> >> _______________________________________________ >> Rails mailing list >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> > > -- > ''And if you think you''re going to bleed all over me > you''re even wronger than you normally be'' > -- The Specials, ''Little Bitch'' > Rasputin :: Jack of All Trades - Master of Nuns > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-Ezra Zygmuntowicz WebMaster Yakima Herald-Republic Newspaper ezra-gdxLOakOTQ9oetBuM9ipNAC/G2K4zDHf@public.gmane.org 509-577-7732
John Kopanas
2005-Jun-29 14:28 UTC
Re: Moving From mySQL to Postgresql and problems with CaSe-SensiTIvity :-)
I guess you are right. I am just discouraged. I thought I had finally finished moving everything over and had everything working until I realized this problem during tests. I switched over mainly because I knew Postgresql supported stored procedures but for some odd reason I can''t find any good documentation on them. Do you know of any good references for stored procedures? Thanks again for all your help. On 29-Jun-05, at 10:12 AM, Jason Foreman wrote:> On 6/29/05, John Kopanas <john.kopanas-O1KSuMybMhqBUy7/sJONFg@public.gmane.org> wrote: > >> The whole thing is that I don''t want to force people''s nickname''s to >> lower case. I want to keep them as they entered them. >> >> > > It may not be the answer you''re looking for, but you can create a > unique constraint in the database to enforce this rule: > > create table foo (id int primary key, name varchar(20)); > create unique index foo_unique_name on foo (lower(name)); > > insert into foo values (1, ''foo''); > insert into foo values (2, ''FOO''); -- this will give you a duplicate > key violation... > > > This should accomplish what you want, but you won''t know until you hit > the database. I''m sure there may be a way to override the validation > method in your model to do it that way as well. > > > >> If validation does not work out of the box with postgresql then is >> postgresql not a recommended DB for Rails? >> >> > > I think having a case sensitive database is a good thing, people just > get used to anomolies like MySQL and MS SQL that have oddball > behaviors </flamebait> :) > > In all seriousness though, a lot of people use PostgreSQL with Rails > with great success, so don''t get discouraged too quickly! > > hth, > > Jason > > > >> >> On 29-Jun-05, at 4:43 AM, Dick Davies wrote: >> >> >>> I did this in my model class - seems to work transparently with >>> validation etc. >>> >>> >>> class Player < ActiveRecord::Base >>> >>> # force lowercase into the db and strip whitespace without making >>> a fuss >>> def username=(u) >>> write_attribute(''username'', u.downcase.gsub(/\s+/, '''') ) >>> end >>> >>> end >>> >>> * John Kopanas <john.kopanas-O1KSuMybMhqBUy7/sJONFg@public.gmane.org> [0658 06:58]: >>> >>> >>>> How about with validation... what would I do with for example: >>>> >>>> validates_uniqueness_of :nickname >>>> >>>> JOhnBaku and Johnbaku in my eyes are the same. But for the system >>>> they will not be the same. So how do I handle this? >>>> >>>> Thanks for your help. >>>> >>>> >>>> On 28-Jun-05, at 10:05 PM, Ezra Zygmuntowicz wrote: >>>> >>>> >>>> >>>>> >>>>> On Jun 28, 2005, at 4:11 PM, John Kopanas wrote: >>>>> >>>>> >>>>> >>>>> >>>>>> I thought I was finished moving my web app from mySQL to >>>>>> postgresql when I realized that there was a change in behaviour >>>>>> from mysql to postgresql. When I would search for a postal code >>>>>> if the user wrote ''H9h 3y2'' it would not be found because in the >>>>>> DB it is stored as ''H9H 3Y2''. And my problem is not only there >>>>>> but throughout the application. >>>>>> >>>>>> What is everyone else doing to solve this problem? >>>>>> >>>>>> John Kopanas >>>>>> http://www.thedatingguy.com - Online Dating the way it should be. >>>>>> http://blog.thedatingguy.com >>>>>> >>>>>> >>>>>> _______________________________________________ >>>>>> Rails mailing list >>>>>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >>>>>> http://lists.rubyonrails.org/mailman/listinfo/rails >>>>>> >>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>>> >>>>> >>>>>> ''H9h 3y2'' >>>>>> >>>>>> >>>>>> >>>>> >>>>> "H9h3y2".upcase! >>>>> >>>>> >>>>> -Ezra Zygmuntowicz >>>>> WebMaster >>>>> Yakima Herald-Republic Newspaper >>>>> ezra-gdxLOakOTQ9oetBuM9ipNAC/G2K4zDHf@public.gmane.org >>>>> 509-577-7732 >>>>> >>>>> _______________________________________________ >>>>> Rails mailing list >>>>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >>>>> http://lists.rubyonrails.org/mailman/listinfo/rails >>>>> >>>>> >>>>> >>>> >>>> >>>> John Kopanas >>>> http://www.thedatingguy.com - Online Dating the way it should be. >>>> http://blog.thedatingguy.com >>>> >>>> >>>> _______________________________________________ >>>> Rails mailing list >>>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >>>> http://lists.rubyonrails.org/mailman/listinfo/rails >>>> >>>> >>> >>> -- >>> ''And if you think you''re going to bleed all over me >>> you''re even wronger than you normally be'' >>> -- The Specials, ''Little Bitch'' >>> Rasputin :: Jack of All Trades - Master of Nuns >>> _______________________________________________ >>> Rails mailing list >>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >>> http://lists.rubyonrails.org/mailman/listinfo/rails >>> >>> >> >> >> John Kopanas >> http://www.thedatingguy.com - Online Dating the way it should be. >> http://blog.thedatingguy.com >> >> >> _______________________________________________ >> Rails mailing list >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> >> > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >John Kopanas http://www.thedatingguy.com - Online Dating the way it should be. http://blog.thedatingguy.com
BigSmoke
2005-Jun-29 15:46 UTC
Re: Moving From mySQL to Postgresql and problems with CaSe-SensiTIvity :-)
You might want to take a look at the following chapter in Postgres'' excellent online manual: http://www.postgresql.org/docs/8.0/interactive/server-programming.html. I''ve started by learning a lot from the online edition of O''Reilly''s "Practical PostgreSQL" [http://www.commandprompt.com/ppbook/] Another book that available online for free is "PostgreSQL: Introduction and Concepts" by Bruce Momjian [http://www.postgresql.org/files/documentation/books/aw_pgsql/index.html] If I remember correctly, I learned some of the more advanced topics from this book. More pointers to the (online) manuals and various books can be found at http://www.postgresql.org/docs/ - Rowan On 6/29/05, John Kopanas <john.kopanas-O1KSuMybMhqBUy7/sJONFg@public.gmane.org> wrote:> I guess you are right. I am just discouraged. I thought I had > finally finished moving everything over and had everything working > until I realized this problem during tests. > > I switched over mainly because I knew Postgresql supported stored > procedures but for some odd reason I can''t find any good > documentation on them. Do you know of any good references for stored > procedures? > > Thanks again for all your help. > > On 29-Jun-05, at 10:12 AM, Jason Foreman wrote: > > > On 6/29/05, John Kopanas <john.kopanas-O1KSuMybMhqBUy7/sJONFg@public.gmane.org> wrote: > > > >> The whole thing is that I don''t want to force people''s nickname''s to > >> lower case. I want to keep them as they entered them. > >> > >> > > > > It may not be the answer you''re looking for, but you can create a > > unique constraint in the database to enforce this rule: > > > > create table foo (id int primary key, name varchar(20)); > > create unique index foo_unique_name on foo (lower(name)); > > > > insert into foo values (1, ''foo''); > > insert into foo values (2, ''FOO''); -- this will give you a duplicate > > key violation... > > > > > > This should accomplish what you want, but you won''t know until you hit > > the database. I''m sure there may be a way to override the validation > > method in your model to do it that way as well. > > > > > > > >> If validation does not work out of the box with postgresql then is > >> postgresql not a recommended DB for Rails? > >> > >> > > > > I think having a case sensitive database is a good thing, people just > > get used to anomolies like MySQL and MS SQL that have oddball > > behaviors </flamebait> :) > > > > In all seriousness though, a lot of people use PostgreSQL with Rails > > with great success, so don''t get discouraged too quickly! > > > > hth, > > > > Jason > > > > > > > >> > >> On 29-Jun-05, at 4:43 AM, Dick Davies wrote: > >> > >> > >>> I did this in my model class - seems to work transparently with > >>> validation etc. > >>> > >>> > >>> class Player < ActiveRecord::Base > >>> > >>> # force lowercase into the db and strip whitespace without making > >>> a fuss > >>> def username=(u) > >>> write_attribute(''username'', u.downcase.gsub(/\s+/, '''') ) > >>> end > >>> > >>> end > >>> > >>> * John Kopanas <john.kopanas-O1KSuMybMhqBUy7/sJONFg@public.gmane.org> [0658 06:58]: > >>> > >>> > >>>> How about with validation... what would I do with for example: > >>>> > >>>> validates_uniqueness_of :nickname > >>>> > >>>> JOhnBaku and Johnbaku in my eyes are the same. But for the system > >>>> they will not be the same. So how do I handle this? > >>>> > >>>> Thanks for your help. > >>>> > >>>> > >>>> On 28-Jun-05, at 10:05 PM, Ezra Zygmuntowicz wrote: > >>>> > >>>> > >>>> > >>>>> > >>>>> On Jun 28, 2005, at 4:11 PM, John Kopanas wrote: > >>>>> > >>>>> > >>>>> > >>>>> > >>>>>> I thought I was finished moving my web app from mySQL to > >>>>>> postgresql when I realized that there was a change in behaviour > >>>>>> from mysql to postgresql. When I would search for a postal code > >>>>>> if the user wrote ''H9h 3y2'' it would not be found because in the > >>>>>> DB it is stored as ''H9H 3Y2''. And my problem is not only there > >>>>>> but throughout the application. > >>>>>> > >>>>>> What is everyone else doing to solve this problem? > >>>>>> > >>>>>> John Kopanas > >>>>>> http://www.thedatingguy.com - Online Dating the way it should be. > >>>>>> http://blog.thedatingguy.com > >>>>>> > >>>>>> > >>>>>> _______________________________________________ > >>>>>> Rails mailing list > >>>>>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > >>>>>> http://lists.rubyonrails.org/mailman/listinfo/rails > >>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>>> > >>>>> > >>>>> > >>>>> > >>>>>> ''H9h 3y2'' > >>>>>> > >>>>>> > >>>>>> > >>>>> > >>>>> "H9h3y2".upcase! > >>>>> > >>>>> > >>>>> -Ezra Zygmuntowicz > >>>>> WebMaster > >>>>> Yakima Herald-Republic Newspaper > >>>>> ezra-gdxLOakOTQ9oetBuM9ipNAC/G2K4zDHf@public.gmane.org > >>>>> 509-577-7732 > >>>>> > >>>>> _______________________________________________ > >>>>> Rails mailing list > >>>>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > >>>>> http://lists.rubyonrails.org/mailman/listinfo/rails > >>>>> > >>>>> > >>>>> > >>>> > >>>> > >>>> John Kopanas > >>>> http://www.thedatingguy.com - Online Dating the way it should be. > >>>> http://blog.thedatingguy.com > >>>> > >>>> > >>>> _______________________________________________ > >>>> Rails mailing list > >>>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > >>>> http://lists.rubyonrails.org/mailman/listinfo/rails > >>>> > >>>> > >>> > >>> -- > >>> ''And if you think you''re going to bleed all over me > >>> you''re even wronger than you normally be'' > >>> -- The Specials, ''Little Bitch'' > >>> Rasputin :: Jack of All Trades - Master of Nuns > >>> _______________________________________________ > >>> Rails mailing list > >>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > >>> http://lists.rubyonrails.org/mailman/listinfo/rails > >>> > >>> > >> > >> > >> John Kopanas > >> http://www.thedatingguy.com - Online Dating the way it should be. > >> http://blog.thedatingguy.com > >> > >> > >> _______________________________________________ > >> Rails mailing list > >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > >> http://lists.rubyonrails.org/mailman/listinfo/rails > >> > >> > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > John Kopanas > http://www.thedatingguy.com - Online Dating the way it should be. > http://blog.thedatingguy.com > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Morality is usually taught by the immoral.
Mike Payson
2005-Jun-29 17:41 UTC
Re: Moving From mySQL to Postgresql and problems with CaSe-SensiTIvity :-)
On 6/29/05, John Kopanas <john.kopanas-O1KSuMybMhqBUy7/sJONFg@public.gmane.org> wrote:> The whole thing is that I don''t want to force people''s nickname''s to > lower case. I want to keep them as they entered them. > > If validation does not work out of the box with postgresql then is > postgresql not a recommended DB for Rails?You may want to save the capitalization, but you probably don''t want to allow two different users to have the same username other then case. I don''t know exactly what you''re doing, but quite frequently, people will try to use confusingly similar usernames in order to pass themselves off as the real user. This sort of thing happens all the time on sites like slashdot.
John Kopanas
2005-Jun-30 00:22 UTC
Re: Moving From mySQL to Postgresql and problems with CaSe-SensiTIvity :-)
I just wanted to notify everyone that we successfully moved our website from mysql to postgresql thanks to all your help. To be very honest if it was not for your help I would probably be still trying to install postgresql on my laptop. So I just want to take the opportunity to thank everyone. How do I take care of my issues with case-sensitivity you ask. Well, it was much easier then I could of imagined. I just moved the validation that I did not want to be case-sensitive to the validation and validation_on_create methods in my model. I did not even have to store the original and lowercase versions of a nickname. It was smooth. Thanks again everyone.