[web2py] Re: LIKE case sensitivity: Postgres differs to MySQL = DAL not portable
On 29 янв, 04:53, Thadeus Burgess thade...@thadeusb.com wrote: IMHO This breaks backwards compatibility... If .like() should match case-insensitive then this is bug fix. At http://web2py.com/book/ this does not specified explicitly. PS. .ilike for case insensitive? I think the question is, to make it available for all supported databases.
[web2py] Re: LIKE case sensitivity: Postgres differs to MySQL = DAL not portable
On 7 дек 2010, 00:31, Fran francisb...@gmail.com wrote: - minimally it should be in a FAQ (ideally in the next Book) ideally we could have a case_sensitive=True option for the DAL like() operator...to ensure that both pgsql mysql/sqlite existing apps didn't break, it could default differently depending on the db type? +1 vote sqlite has some issue with not ascii chars compare, but work in progress pgsql has ilike which works like mysql like (fix me) I' just patch dal to use ilike in .like() query for postgres, but it more cheat then solution.
[web2py] Re: LIKE case sensitivity: Postgres differs to MySQL = DAL not portable
I agree the behavior should be uniform. The easiest way is to make the LIKE always case insensitive. I am patching trunk to use ILIKE with postgresql. On Jan 28, 3:01 am, KMax mkostri...@gmail.com wrote: On 7 дек 2010, 00:31, Fran francisb...@gmail.com wrote: - minimally it should be in a FAQ (ideally in the next Book) ideally we could have a case_sensitive=True option for the DAL like() operator...to ensure that both pgsql mysql/sqlite existing apps didn't break, it could default differently depending on the db type? +1 vote sqlite has some issue with not ascii chars compare, but work in progress pgsql has ilike which works like mysql like (fix me) I' just patch dal to use ilike in .like() query for postgres, but it more cheat then solution.
Re: [web2py] Re: LIKE case sensitivity: Postgres differs to MySQL = DAL not portable
I disagree! Your playing with things that shouldn't be played with.
Not to mention that now you have just broken some of my apps that perform
case-sensitive queries in postgres this is just plain wrong in so many
ways.
Add a new identifier to DAL... give me
db(db.table.name.like('%printer%'))
and then for case insensitive
db(db.table.name.ilike('%printer%')).
like would perform the actual operation that would happen from the RDBMS,
and ilike can be a web2py playing god version that makes sure all rdbmses
act the same.
Case sensitive search is one of the benefits of using postgres instead of
mysql!
--
Thadeus
On Fri, Jan 28, 2011 at 8:40 AM, Massimo Di Pierro
massimo.dipie...@gmail.com wrote:
I agree the behavior should be uniform. The easiest way is to make the
LIKE always case insensitive. I am patching trunk to use ILIKE with
postgresql.
On Jan 28, 3:01 am, KMax mkostri...@gmail.com wrote:
On 7 дек 2010, 00:31, Fran francisb...@gmail.com wrote:
- minimally it should be in a FAQ (ideally in the next Book) ideally
we could have a case_sensitive=True option for the DAL like()
operator...to ensure that both pgsql mysql/sqlite existing apps
didn't break, it could default differently depending on the db type?
+1 vote
sqlite has some issue with not ascii chars compare, but work in
progress
pgsql has ilike which works like mysql like (fix me)
I' just patch dal to use ilike in .like() query for postgres, but it
more cheat then solution.
[web2py] Re: LIKE case sensitivity: Postgres differs to MySQL = DAL not portable
I agree with Thadeus here that like should be what it means in each
case. Changing the default meaning of like in each RDBMS will cause
confusion. ilike can be a web2py thing, but like should be
specific to each RDMS.
On Jan 28, 9:46 am, Thadeus Burgess thade...@thadeusb.com wrote:
I disagree! Your playing with things that shouldn't be played with.
Not to mention that now you have just broken some of my apps that perform
case-sensitive queries in postgres this is just plain wrong in so many
ways.
Add a new identifier to DAL... give me
db(db.table.name.like('%printer%'))
and then for case insensitive
db(db.table.name.ilike('%printer%')).
like would perform the actual operation that would happen from the RDBMS,
and ilike can be a web2py playing god version that makes sure all rdbmses
act the same.
Case sensitive search is one of the benefits of using postgres instead of
mysql!
--
Thadeus
On Fri, Jan 28, 2011 at 8:40 AM, Massimo Di Pierro
massimo.dipie...@gmail.com wrote:
I agree the behavior should be uniform. The easiest way is to make the
LIKE always case insensitive. I am patching trunk to use ILIKE with
postgresql.
On Jan 28, 3:01 am, KMax mkostri...@gmail.com wrote:
On 7 дек 2010, 00:31, Fran francisb...@gmail.com wrote:
- minimally it should be in a FAQ (ideally in the next Book) ideally
we could have a case_sensitive=True option for the DAL like()
operator...to ensure that both pgsql mysql/sqlite existing apps
didn't break, it could default differently depending on the db type?
+1 vote
sqlite has some issue with not ascii chars compare, but work in
progress
pgsql has ilike which works like mysql like (fix me)
I' just patch dal to use ilike in .like() query for postgres, but it
more cheat then solution.
[web2py] Re: LIKE case sensitivity: Postgres differs to MySQL = DAL not portable
What if like() had something like a 'case' argument, with three possible
values: sensitive, insensitive, and rdbms_default (defaulting to
rdbms_default)?
We obviously need to maintain backward compatibility, but like() is a web2py
operator, not a specific RDBMS operator, so it would be nice if there's any
easy way to make sure like() calls are as portable as possible without
requiring code changes.
Anthony
On Friday, January 28, 2011 11:43:58 AM UTC-5, VP wrote:
I agree with Thadeus here that like should be what it means in each
case. Changing the default meaning of like in each RDBMS will cause
confusion. ilike can be a web2py thing, but like should be
specific to each RDMS.
On Jan 28, 9:46 am, Thadeus Burgess thad...@thadeusb.com wrote:
I disagree! Your playing with things that shouldn't be played with.
Not to mention that now you have just broken some of my apps that perform
case-sensitive queries in postgres this is just plain wrong in so
many
ways.
Add a new identifier to DAL... give me
db(db.table.name.like('%printer%'))
and then for case insensitive
db(db.table.name.ilike('%printer%')).
like would perform the actual operation that would happen from the RDBMS,
and ilike can be a web2py playing god version that makes sure all rdbmses
act the same.
Case sensitive search is one of the benefits of using postgres instead of
mysql!
--
Thadeus
On Fri, Jan 28, 2011 at 8:40 AM, Massimo Di Pierro
massimo@gmail.com wrote:
I agree the behavior should be uniform. The easiest way is to make the
LIKE always case insensitive. I am patching trunk to use ILIKE with
postgresql.
On Jan 28, 3:01 am, KMax mkost...@gmail.com wrote:
On 7 дек 2010, 00:31, Fran franc...@gmail.com wrote:
- minimally it should be in a FAQ (ideally in the next Book)
ideally
we could have a case_sensitive=True option for the DAL like()
operator...to ensure that both pgsql mysql/sqlite existing apps
didn't break, it could default differently depending on the db
type?
+1 vote
sqlite has some issue with not ascii chars compare, but work in
progress
pgsql has ilike which works like mysql like (fix me)
I' just patch dal to use ilike in .like() query for postgres, but it
more cheat then solution.
[web2py] Re: LIKE case sensitivity: Postgres differs to MySQL = DAL not portable
We need two steps:
1) make it behave the same (which means case insensitive, ilike on
postgresql, now in trunk)
2) yes we can add a case_sensitive arg that defaults to True (not done
yet but I would take a patch).
On Jan 28, 11:37 am, Anthony abasta...@gmail.com wrote:
What if like() had something like a 'case' argument, with three possible
values: sensitive, insensitive, and rdbms_default (defaulting to
rdbms_default)?
We obviously need to maintain backward compatibility, but like() is a web2py
operator, not a specific RDBMS operator, so it would be nice if there's any
easy way to make sure like() calls are as portable as possible without
requiring code changes.
Anthony
On Friday, January 28, 2011 11:43:58 AM UTC-5, VP wrote:
I agree with Thadeus here that like should be what it means in each
case. Changing the default meaning of like in each RDBMS will cause
confusion. ilike can be a web2py thing, but like should be
specific to each RDMS.
On Jan 28, 9:46 am, Thadeus Burgess thad...@thadeusb.com wrote:
I disagree! Your playing with things that shouldn't be played with.
Not to mention that now you have just broken some of my apps that perform
case-sensitive queries in postgres this is just plain wrong in so
many
ways.
Add a new identifier to DAL... give me
db(db.table.name.like('%printer%'))
and then for case insensitive
db(db.table.name.ilike('%printer%')).
like would perform the actual operation that would happen from the RDBMS,
and ilike can be a web2py playing god version that makes sure all rdbmses
act the same.
Case sensitive search is one of the benefits of using postgres instead of
mysql!
--
Thadeus
On Fri, Jan 28, 2011 at 8:40 AM, Massimo Di Pierro
massimo@gmail.com wrote:
I agree the behavior should be uniform. The easiest way is to make the
LIKE always case insensitive. I am patching trunk to use ILIKE with
postgresql.
On Jan 28, 3:01 am, KMax mkost...@gmail.com wrote:
On 7 дек 2010, 00:31, Fran franc...@gmail.com wrote:
- minimally it should be in a FAQ (ideally in the next Book)
ideally
we could have a case_sensitive=True option for the DAL like()
operator...to ensure that both pgsql mysql/sqlite existing apps
didn't break, it could default differently depending on the db
type?
+1 vote
sqlite has some issue with not ascii chars compare, but work in
progress
pgsql has ilike which works like mysql like (fix me)
I' just patch dal to use ilike in .like() query for postgres, but it
more cheat then solution.
[web2py] Re: LIKE case sensitivity: Postgres differs to MySQL = DAL not portable
I suppose 'ilike' in PostgreSQL is similar to 'containing' in Firebird
(except you don't use wildcards in FB).
On Jan 28, 5:58 pm, Massimo Di Pierro massimo.dipie...@gmail.com
wrote:
We need two steps:
1) make it behave the same (which means case insensitive, ilike on
postgresql, now in trunk)
2) yes we can add a case_sensitive arg that defaults to True (not done
yet but I would take a patch).
On Jan 28, 11:37 am, Anthony abasta...@gmail.com wrote:
What if like() had something like a 'case' argument, with three possible
values: sensitive, insensitive, and rdbms_default (defaulting to
rdbms_default)?
We obviously need to maintain backward compatibility, but like() is a web2py
operator, not a specific RDBMS operator, so it would be nice if there's any
easy way to make sure like() calls are as portable as possible without
requiring code changes.
Anthony
On Friday, January 28, 2011 11:43:58 AM UTC-5, VP wrote:
I agree with Thadeus here that like should be what it means in each
case. Changing the default meaning of like in each RDBMS will cause
confusion. ilike can be a web2py thing, but like should be
specific to each RDMS.
On Jan 28, 9:46 am, Thadeus Burgess thad...@thadeusb.com wrote:
I disagree! Your playing with things that shouldn't be played with.
Not to mention that now you have just broken some of my apps that
perform
case-sensitive queries in postgres this is just plain wrong in so
many
ways.
Add a new identifier to DAL... give me
db(db.table.name.like('%printer%'))
and then for case insensitive
db(db.table.name.ilike('%printer%')).
like would perform the actual operation that would happen from the
RDBMS,
and ilike can be a web2py playing god version that makes sure all
rdbmses
act the same.
Case sensitive search is one of the benefits of using postgres instead
of
mysql!
--
Thadeus
On Fri, Jan 28, 2011 at 8:40 AM, Massimo Di Pierro
massimo@gmail.com wrote:
I agree the behavior should be uniform. The easiest way is to make the
LIKE always case insensitive. I am patching trunk to use ILIKE with
postgresql.
On Jan 28, 3:01 am, KMax mkost...@gmail.com wrote:
On 7 дек 2010, 00:31, Fran franc...@gmail.com wrote:
- minimally it should be in a FAQ (ideally in the next Book)
ideally
we could have a case_sensitive=True option for the DAL like()
operator...to ensure that both pgsql mysql/sqlite existing apps
didn't break, it could default differently depending on the db
type?
+1 vote
sqlite has some issue with not ascii chars compare, but work in
progress
pgsql has ilike which works like mysql like (fix me)
I' just patch dal to use ilike in .like() query for postgres, but it
more cheat then solution.
[web2py] Re: LIKE case sensitivity: Postgres differs to MySQL = DAL not portable
On Friday, January 28, 2011 12:58:30 PM UTC-5, Massimo Di Pierro wrote: We need two steps: 1) make it behave the same (which means case insensitive, ilike on postgresql, now in trunk) Doesn't this change break backward compatibility, or are you treating this as a bug fix? 2) yes we can add a case_sensitive arg that defaults to True (not done yet but I would take a patch). If we do want to maintain backward compatibility, wouldn't the new case arg have to default to something like let the RDBMS decide -- it couldn't just default to True (or False) because different databases have different defaults, no? On the other hand, if that's not a concern, do we want case_sensitive to default to True -- it sounds like not all databases even offer that option? On Jan 28, 11:37 am, Anthony abas...@gmail.com wrote: What if like() had something like a 'case' argument, with three possible values: sensitive, insensitive, and rdbms_default (defaulting to rdbms_default)? We obviously need to maintain backward compatibility, but like() is a web2py operator, not a specific RDBMS operator, so it would be nice if there's any easy way to make sure like() calls are as portable as possible without requiring code changes. Anthony
[web2py] Re: LIKE case sensitivity: Postgres differs to MySQL = DAL not portable
ILIKE is not the same as containing. It is a case insensitive LIKE
On Jan 28, 12:23 pm, villas villa...@gmail.com wrote:
I suppose 'ilike' in PostgreSQL is similar to 'containing' in Firebird
(except you don't use wildcards in FB).
On Jan 28, 5:58 pm, Massimo Di Pierro massimo.dipie...@gmail.com
wrote:
We need two steps:
1) make it behave the same (which means case insensitive, ilike on
postgresql, now in trunk)
2) yes we can add a case_sensitive arg that defaults to True (not done
yet but I would take a patch).
On Jan 28, 11:37 am, Anthony abasta...@gmail.com wrote:
What if like() had something like a 'case' argument, with three possible
values: sensitive, insensitive, and rdbms_default (defaulting to
rdbms_default)?
We obviously need to maintain backward compatibility, but like() is a
web2py
operator, not a specific RDBMS operator, so it would be nice if there's
any
easy way to make sure like() calls are as portable as possible without
requiring code changes.
Anthony
On Friday, January 28, 2011 11:43:58 AM UTC-5, VP wrote:
I agree with Thadeus here that like should be what it means in each
case. Changing the default meaning of like in each RDBMS will cause
confusion. ilike can be a web2py thing, but like should be
specific to each RDMS.
On Jan 28, 9:46 am, Thadeus Burgess thad...@thadeusb.com wrote:
I disagree! Your playing with things that shouldn't be played with.
Not to mention that now you have just broken some of my apps that
perform
case-sensitive queries in postgres this is just plain wrong in so
many
ways.
Add a new identifier to DAL... give me
db(db.table.name.like('%printer%'))
and then for case insensitive
db(db.table.name.ilike('%printer%')).
like would perform the actual operation that would happen from the
RDBMS,
and ilike can be a web2py playing god version that makes sure all
rdbmses
act the same.
Case sensitive search is one of the benefits of using postgres
instead of
mysql!
--
Thadeus
On Fri, Jan 28, 2011 at 8:40 AM, Massimo Di Pierro
massimo@gmail.com wrote:
I agree the behavior should be uniform. The easiest way is to make
the
LIKE always case insensitive. I am patching trunk to use ILIKE with
postgresql.
On Jan 28, 3:01 am, KMax mkost...@gmail.com wrote:
On 7 дек 2010, 00:31, Fran franc...@gmail.com wrote:
- minimally it should be in a FAQ (ideally in the next Book)
ideally
we could have a case_sensitive=True option for the DAL like()
operator...to ensure that both pgsql mysql/sqlite existing
apps
didn't break, it could default differently depending on the db
type?
+1 vote
sqlite has some issue with not ascii chars compare, but work in
progress
pgsql has ilike which works like mysql like (fix me)
I' just patch dal to use ilike in .like() query for postgres, but
it
more cheat then solution.
[web2py] Re: LIKE case sensitivity: Postgres differs to MySQL = DAL not portable
I treat this as a bug fix. On Jan 28, 12:52 pm, Anthony abasta...@gmail.com wrote: On Friday, January 28, 2011 12:58:30 PM UTC-5, Massimo Di Pierro wrote: We need two steps: 1) make it behave the same (which means case insensitive, ilike on postgresql, now in trunk) Doesn't this change break backward compatibility, or are you treating this as a bug fix? 2) yes we can add a case_sensitive arg that defaults to True (not done yet but I would take a patch). If we do want to maintain backward compatibility, wouldn't the new case arg have to default to something like let the RDBMS decide -- it couldn't just default to True (or False) because different databases have different defaults, no? On the other hand, if that's not a concern, do we want case_sensitive to default to True -- it sounds like not all databases even offer that option? On Jan 28, 11:37 am, Anthony abas...@gmail.com wrote: What if like() had something like a 'case' argument, with three possible values: sensitive, insensitive, and rdbms_default (defaulting to rdbms_default)? We obviously need to maintain backward compatibility, but like() is a web2py operator, not a specific RDBMS operator, so it would be nice if there's any easy way to make sure like() calls are as portable as possible without requiring code changes. Anthony
Re: [web2py] Re: LIKE case sensitivity: Postgres differs to MySQL = DAL not portable
IMHO This breaks backwards compatibility... -- Thadeus On Sat, Jan 29, 2011 at 6:31 AM, Massimo Di Pierro massimo.dipie...@gmail.com wrote: I treat this as a bug fix. On Jan 28, 12:52 pm, Anthony abasta...@gmail.com wrote: On Friday, January 28, 2011 12:58:30 PM UTC-5, Massimo Di Pierro wrote: We need two steps: 1) make it behave the same (which means case insensitive, ilike on postgresql, now in trunk) Doesn't this change break backward compatibility, or are you treating this as a bug fix? 2) yes we can add a case_sensitive arg that defaults to True (not done yet but I would take a patch). If we do want to maintain backward compatibility, wouldn't the new case arg have to default to something like let the RDBMS decide -- it couldn't just default to True (or False) because different databases have different defaults, no? On the other hand, if that's not a concern, do we want case_sensitive to default to True -- it sounds like not all databases even offer that option? On Jan 28, 11:37 am, Anthony abas...@gmail.com wrote: What if like() had something like a 'case' argument, with three possible values: sensitive, insensitive, and rdbms_default (defaulting to rdbms_default)? We obviously need to maintain backward compatibility, but like() is a web2py operator, not a specific RDBMS operator, so it would be nice if there's any easy way to make sure like() calls are as portable as possible without requiring code changes. Anthony
[web2py] Re: LIKE case sensitivity: Postgres differs to MySQL = DAL not portable
On 28 Jan., 16:46, Thadeus Burgess thade...@thadeusb.com wrote: Case sensitive search is one of the benefits of using postgres instead of mysql! As Fran wrote case insensitive LIKE is just a default for MySQL and sqlite. MySQL supports case sensitive search: # SELECT * FROM person WHERE name LIKE BINARY '%Pi%'; sqlite3's LIKE can be configured with PRAGMA case_sensitive_like=yes.
[web2py] Re: LIKE case sensitivity: Postgres differs to MySQL = DAL not portable
On 29 янв, 05:39, Bernd Rothert roth...@googlemail.com wrote: On 28 Jan., 16:46, Thadeus Burgess thade...@thadeusb.com wrote: Case sensitive search is one of the benefits of using postgres instead of mysql! As Fran wrote case insensitive LIKE is just a default for MySQL and sqlite. MySQL supports case sensitive search: This make .like() for mysql work not same as PG, etc... # SELECT * FROM person WHERE name LIKE BINARY '%Pi%'; sqlite3's LIKE can be configured with PRAGMA case_sensitive_like=yes. Is't a way to add .ilike() for case insensitive select? Anyway .like() for mysql should be adjusted to be case sensitive, if .ilike() will be implemented. Is'nt it?
