modeling-users

[Modeling-users] fetch qualifier problem

[Mario R. <ma...@ru...>]

Hello,

I have encountered this problem: if i set a string property on an 
entity,
and this contains a "special" character such as _ or @, then fetch
does the following:

ec.EditingContext()
aprop = 'a_string' # contains a special characters, e.g. _ @
e = AnEntity()
e.setAprop(aprop)
ec.insert(e)
ec.saveChanges()

ec.fetch('AnEntity',qualifier='aprop == "%s"' %(aprop) ) # works fine
ec.fetch('AnEntity',qualifier='aprop ilike "*%s*"' %(aprop) ) # nada

i.e. if i use the str value for testing equality, the fetch results are 
ok,
but if i use it in ilike, then the fetch result list is zero length.
Is there something i miss here, or is this a bug?

Cheers, mario

Re: [Modeling-users] fetch qualifier problem

[Sebastien B. <sbi...@us...>]

        Hi,

  Could you please give an example string and the db server you use?

  It must be a bug. '_' is the sql equivalent for '?' in qualifier
strings (matches one character), but it should have been escaped. We had
such pbs with postgresql which were fixed, but it seems that the
pb. remains w/ other db adaptors.  AFAIK '@' shouldn't be a problem on
its own.

  It could also help if you could send the generated sql statements by
  setting MDL_ENABLE_DATABASE_LOGGING to true --possibly privately if
  you do not want to disclose the details.


-- S=E9bastien.


Mario Ruggier <ma...@ru...> wrote:
> I have encountered this problem: if i set a string property on an entity,
> and this contains a "special" character such as _ or @, then fetch
> does the following:
>=20
> ec.EditingContext()
> aprop =3D 'a_string' # contains a special characters, e.g. _ @
> e =3D AnEntity()
> e.setAprop(aprop)
> ec.insert(e)
> ec.saveChanges()
>=20
> ec.fetch('AnEntity',qualifier=3D'aprop =3D=3D "%s"' %(aprop) ) # works fi=
ne
> ec.fetch('AnEntity',qualifier=3D'aprop ilike "*%s*"' %(aprop) ) # nada
>=20
> i.e. if i use the str value for testing equality, the fetch results are o=
k,
> but if i use it in ilike, then the fetch result list is zero length.
> Is there something i miss here, or is this a bug?
>=20
> Cheers, mario

Re: [Modeling-users] fetch qualifier problem

[Mario R. <ma...@ru...>]

Sorry, I did not mention that this was running on sqlite.
The problem here occurs for string values that contain '_'
(for '@'. it actually works fine, sorry had it wrong the first time).

Here is a (touched-up, to remove stuff) SQL output:

 >>> ufirst =3D '_mario'
 >>> u =3D ec.fetch('User',qualifier=3D'firstname ilike "*%s*"' =
%(ufirst) )
Opening channel=20
<SQLiteAdaptorLayer.SQLiteAdaptorChannel.SQLiteAdaptorChannel instance=20=

at 0x7db8e0> (0x7db8e0)
Called
Opening connection to the DB with conn.Dict: {'password': 'xxxx',=20
'database': 'db_users'}
Evaluating: SELECT t0.ID, t0.FIRSTNAME, t0.etcetera, FROM User t0 WHERE=20=

UPPER(t0.FIRSTNAME) LIKE UPPER('%\_mario%')
rowcount: 0 Returning: None
Closing adaptorChannel=20
<SQLiteAdaptorLayer.SQLiteAdaptorChannel.SQLiteAdaptorChannel instance=20=

at 0x7db8e0> (0x7db8e0)
Adaptor channel=20
<SQLiteAdaptorLayer.SQLiteAdaptorChannel.SQLiteAdaptorChannel instance=20=

at 0x7db8e0> did close
Channels are now all closed
Closing the connection to the database
 >>>

Small question: when i try the aove from inside a py program
(a unittest testcase test), setting this envvar to 'YES', nothing=20
happens...
or so it seems. Where should i go to get stderr (osx) ?

mario



On Samedi, ao=FB 9, 2003, at 14:43, Sebastien Bigaret wrote:
>         Hi,
>
>   Could you please give an example string and the db server you use?
>
>   It must be a bug. '_' is the sql equivalent for '?' in qualifier
> strings (matches one character), but it should have been escaped. We=20=

> had
> such pbs with postgresql which were fixed, but it seems that the
> pb. remains w/ other db adaptors.  AFAIK '@' shouldn't be a problem on
> its own.
>
>   It could also help if you could send the generated sql statements by
>   setting MDL_ENABLE_DATABASE_LOGGING to true --possibly privately if
>   you do not want to disclose the details.
>
>
> -- S=E9bastien.
>
>
> Mario Ruggier <ma...@ru...> wrote:
>> I have encountered this problem: if i set a string property on an=20
>> entity,
>> and this contains a "special" character such as _ or @, then fetch
>> does the following:
>>
>> ec.EditingContext()
>> aprop =3D 'a_string' # contains a special characters, e.g. _ @
>> e =3D AnEntity()
>> e.setAprop(aprop)
>> ec.insert(e)
>> ec.saveChanges()
>>
>> ec.fetch('AnEntity',qualifier=3D'aprop =3D=3D "%s"' %(aprop) ) # =
works fine
>> ec.fetch('AnEntity',qualifier=3D'aprop ilike "*%s*"' %(aprop) ) # =
nada
>>
>> i.e. if i use the str value for testing equality, the fetch results=20=

>> are ok,
>> but if i use it in ilike, then the fetch result list is zero length.
>> Is there something i miss here, or is this a bug?
>>
>> Cheers, mario
>

Re: [Modeling-users] fetch qualifier problem

[Sebastien B. <sbi...@us...>]

Mario Ruggier <ma...@ru...> writes:

> Sorry, I did not mention that this was running on sqlite.
> The problem here occurs for string values that contain '_'
> (for '@'. it actually works fine, sorry had it wrong the first time).

This is a problem w/ sqlite. The problem happens for '_' and '%', and I
can't even find out to do this on the sqlite commandline. I've just sent
the question to the sqlite ml, we'll see then.

> Small question: when i try the aove from inside a py program
> (a unittest testcase test), setting this envvar to 'YES', nothing happens=
...
> or so it seems. Where should i go to get stderr (osx) ?

  How do you launch it? By dble-clicking on it? If yes, try to check the
  console (there's an app named Console somewhere, can't recall where
  but you'll probably find it).

-- S=E9bastien.

Re: [Modeling-users] fetch qualifier problem

[Sebastien B. <sbi...@us...>]

Sebastien Bigaret <sbi...@us...> wrote:
> This is a problem w/ sqlite. The problem happens for '_' and '%', and I
> can't even find out to do this on the sqlite commandline. I've just sent
> the question to the sqlite ml, we'll see then.

  Two notes:

    - I added bug #785913 for this,

    - the Postgresql layer had the same pb. w/ '_' (was okay for '%'); I
      noticed this when writing the related test. This is fixed on CVS, and
      you'll find attached a patch correcting the bug.

        Regards,

-- S=E9bastien.

------------------------------------------------------------------------
--- Modeling/DatabaseAdaptors/PostgresqlAdaptorLayer/PostgresqlSQLExpressio=
n.py3 Aug 2003 10:34:14 -0000        1.6
+++ Modeling/DatabaseAdaptors/PostgresqlAdaptorLayer/PostgresqlSQLExpressio=
n.py9 Aug 2003 15:10:11 -0000        1.7
@@ -27,11 +27,11 @@
=20
   CVS information
=20
-    $Id: PostgresqlSQLExpression.py,v 1.6 2003/08/03 10:34:14 sbigaret Exp=
 $
+    $Id: PostgresqlSQLExpression.py,v 1.7 2003/08/09 15:10:11 sbigaret Exp=
 $
=20=20=20
 """
=20
-__version__=3D'$Revision: 1.6 $'[11:-2]
+__version__=3D'$Revision: 1.7 $'[11:-2]
=20
 from Modeling.SQLExpression import SQLExpression, DateType, CharacterType
 import string
@@ -115,8 +115,8 @@
     Overrides default behaviour so that '%' is changed to '\\%' instead of
     '\%': postgresql interprets backslashes in strings
     """
-    pattern=3Dpercent.sub('\\\\\\\\%', pattern)
-    pattern=3Dunderscore.sub('\_', pattern)
+    pattern=3Dpercent.sub(r'\\\\%', pattern)
+    pattern=3Dunderscore.sub(r'\\\\_', pattern)
     pattern=3Descaped_question_mark.sub(esc_question_tmp_replct, pattern)
     pattern=3Dquestion_mark.sub('_', pattern)
     pattern=3Descaped_star.sub(esc_star_tmp_replct, pattern)

Re: [Modeling-users] fetch qualifier problem

[Sebastien B. <sbi...@us...>]

        Hi,

  While making the test suite for all this and roaming around sqlite
source code, I incidentally found an other bug
https://sf.net/tracker/index.php?func=3Ddetail&aid=3D786217&group_id=3D5893=
5&atid=3D489335
      [ 786217 ] Qualifier LIKE can be case-insensitive

  --> When using 'LIKE' in qualifier strings, the match for MySQL and
  SQLite is case-*insensitive*. I did not noticed it, but the fact is
  that sql 'LIKE' operator is by default case-insensitive for these two
  databases.

About SQLite: the fix for the above bug is expected to fix bug #785913:
"LIKE qualifier w/ raw '%' and '_' does not work w/ SQLite" as well. Now
for case insensitive match, sqlite lacks the ability to escape the
special characters --I've submitted a patch to the sqlite ml today,
we'll see what happens,
cf. http://groups.yahoo.com/group/sqlite/message/4605

-- S=E9bastien.


Sebastien Bigaret <sbi...@us...> writes:

> Mario Ruggier <ma...@ru...> writes:
>=20
> > Sorry, I did not mention that this was running on sqlite.
> > The problem here occurs for string values that contain '_'
> > (for '@'. it actually works fine, sorry had it wrong the first time).
>=20
> This is a problem w/ sqlite. The problem happens for '_' and '%', and I
> can't even find out to do this on the sqlite commandline. I've just sent
> the question to the sqlite ml, we'll see then.
>=20
> > Small question: when i try the aove from inside a py program
> > (a unittest testcase test), setting this envvar to 'YES', nothing happe=
ns...
> > or so it seems. Where should i go to get stderr (osx) ?
>=20
>   How do you launch it? By dble-clicking on it? If yes, try to check the
>   console (there's an app named Console somewhere, can't recall where
>   but you'll probably find it).
>=20
> -- S=E9bastien.

Re: [Modeling-users] fetch qualifier problem

[Sebastien B. <sbi...@us...>]

I wrote:
>   While making the test suite for all this and roaming around sqlite
> source code, I incidentally found an other bug
> https://sf.net/tracker/index.php?func=3Ddetail&aid=3D786217&group_id=3D58=
935&atid=3D489335
>       [ 786217 ] Qualifier LIKE can be case-insensitive
>=20
>   --> When using 'LIKE' in qualifier strings, the match for MySQL and
>   SQLite is case-*insensitive*. I did not noticed it, but the fact is
>   that sql 'LIKE' operator is by default case-insensitive for these two
>   databases.
>=20
> About SQLite: the fix for the above bug is expected to fix bug #785913:
> "LIKE qualifier w/ raw '%' and '_' does not work w/ SQLite" as well. Now
> for case insensitive match, sqlite lacks the ability to escape the
> special characters --I've submitted a patch to the sqlite ml today,
> we'll see what happens,
> cf. http://groups.yahoo.com/group/sqlite/message/4605

Both bugs have been fixed (for mysql and sqlite) and patches can be
found at:
https://sourceforge.net/tracker/index.php?func=3Ddetail&aid=3D786217&group_=
id=3D58935&atid=3D489335

  Note that the problem remains w/ sqlite and 'ilike', for the reason
  explained above.

Mario: your qualifiers with LIKE operators containing raw '%' or '_'
characters should now work as expected.

        Regards,

-- S=E9bastien.