<contact-CKba8aKGf+ewktNWMKzPnQ@public.gmane.org>
2004-Dec-17 08:06 UTC
ActiveRecord: It''s not only quoting
I''ve implemented the adapter for DB2 and now I''m trying to run
the
tests. The results look promising, but there are still a lot of subtle
things to fix.
First of all I''ve added a small hack to the value_list method in class
Fixture (fixtures.rb): Things that look like an int (/^\d+$/) will be
converted to an int now and ":00" is appended to all values, that look
like a timestamp (/\d{4}-\d\d-\d\d \d\d:\d\d/). Otherwise DB2 won''t
accept them.
As far as I know the "old" test fixtures will be replaced by
"typed"
ones (YAML) soon, won''t they?
But there are more difficult things: The find method(s) in
ActiveRecord::Base don''t work properly because the sanitize method
still does not quote id values correctly. In my opinion all find
methods that expect an int or a list of ints should convert their
arguments using to_i before doing anything. Would that cause any
problems?
Additionally, most of the "SELECT" statements will not work with NULL
values. In DB2 it''s not possible to run a statement like "SELECT *
FROM
x WHERE y = NULL". It has to be "SELECT * FROM x WHERE y IS
NULL".
I''ve found some SQL statements using the "LIMIT" clause. This
does not
exist in DB2 (at least not in Version 7) and it has to be replaced by
"fetch first <x> rows only". In Oracle I''ve always used
"WHERE ROWNUM <
x" and I do not know, if Oracle has a "LIMIT" keyword.
I think it''s not a good idea to clutter the code base with esoteric
DB2/Oracle/... knowledge. Any suggestions?
Cheers,
Maik
> I''ve implemented the adapter for DB2 and now I''m trying to run the > tests. The results look promising, but there are still a lot of subtle > things to fix. > > First of all I''ve added a small hack to the value_list method in class > Fixture (fixtures.rb): Things that look like an int (/^\d+$/) will be > converted to an int now and ":00" is appended to all values, that look > like a timestamp (/\d{4}-\d\d-\d\d \d\d:\d\d/). Otherwise DB2 won''t > accept them. > > As far as I know the "old" test fixtures will be replaced by "typed" > ones (YAML) soon, won''t they? > > But there are more difficult things: The find method(s) in > ActiveRecord::Base don''t work properly because the sanitize method > still does not quote id values correctly. In my opinion all find > methods that expect an int or a list of ints should convert their > arguments using to_i before doing anything. Would that cause any > problems?Well, it is a slight problem as the find methods don''t expect an int necessarily. It should be quoted correctly If you actually run: Whatever.find(@params["id"].to_i) I think that it should be possible to ensure that the primary key selects automatically have their arguments automagically converted. However for all the other find methods, it''d be up to the user.> Additionally, most of the "SELECT" statements will not work with NULL > values. In DB2 it''s not possible to run a statement like "SELECT * FROM > x WHERE y = NULL". It has to be "SELECT * FROM x WHERE y IS NULL". > > I''ve found some SQL statements using the "LIMIT" clause. This does not > exist in DB2 (at least not in Version 7) and it has to be replaced by > "fetch first <x> rows only". In Oracle I''ve always used "WHERE ROWNUM < > x" and I do not know, if Oracle has a "LIMIT" keyword. > > I think it''s not a good idea to clutter the code base with esoteric > DB2/Oracle/... knowledge. Any suggestions? > > Cheers, > > Maik > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Cheers Koz
On 17.12.2004, at 10:06, <contact-CKba8aKGf+ewktNWMKzPnQ@public.gmane.org> wrote:> > Additionally, most of the "SELECT" statements will not work with NULL > values. In DB2 it''s not possible to run a statement like "SELECT * FROM > x WHERE y = NULL". It has to be "SELECT * FROM x WHERE y IS NULL".I think the latter is the SQL standard and works for sure in Oracle and PostgreSQL.> > I''ve found some SQL statements using the "LIMIT" clause. This does not > exist in DB2 (at least not in Version 7) and it has to be replaced by > "fetch first <x> rows only". In Oracle I''ve always used "WHERE ROWNUM < > x" and I do not know, if Oracle has a "LIMIT" keyword.No, it doesn''t, at least in 8i. You have to use the rownum trick.> > I think it''s not a good idea to clutter the code base with esoteric > DB2/Oracle/... knowledge. Any suggestions?I think limiting the result set is crucial and the adapter needs to know how to handle that. //jarkko> > Cheers, > > Maik > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Jarkko Laine http://jlaine.net _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On Fri, 17 Dec 2004 10:25:42 +0200, Jarkko Laine <jarkko-k1O+Gnc6WpmsTnJN9+BGXg@public.gmane.org> wrote:> > On 17.12.2004, at 10:06, <contact-CKba8aKGf+ewktNWMKzPnQ@public.gmane.org> wrote: > > > > Additionally, most of the "SELECT" statements will not work with NULL > > values. In DB2 it''s not possible to run a statement like "SELECT * FROM > > x WHERE y = NULL". It has to be "SELECT * FROM x WHERE y IS NULL". > > I think the latter is the SQL standard and works for sure in Oracle and > PostgreSQL.Yes, is null is the standard, it works with mysql too.> > > > I''ve found some SQL statements using the "LIMIT" clause. This does not > > exist in DB2 (at least not in Version 7) and it has to be replaced by > > "fetch first <x> rows only". In Oracle I''ve always used "WHERE ROWNUM < > > x" and I do not know, if Oracle has a "LIMIT" keyword. > > No, it doesn''t, at least in 8i. You have to use the rownum trick. > > > > > I think it''s not a good idea to clutter the code base with esoteric > > DB2/Oracle/... knowledge. Any suggestions? > > I think limiting the result set is crucial and the adapter needs to > know how to handle that. > > //jarkko > > > > > Cheers, > > > > Maik > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > -- > Jarkko Laine > http://jlaine.net > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > > >-- Cheers Koz
On Fri, 17 Dec 2004, Jarkko Laine wrote:> On 17.12.2004, at 10:06, <contact-CKba8aKGf+ewktNWMKzPnQ@public.gmane.org> wrote: > > > > Additionally, most of the "SELECT" statements will not work with NULL > > values. In DB2 it''s not possible to run a statement like "SELECT * FROM > > x WHERE y = NULL". It has to be "SELECT * FROM x WHERE y IS NULL".It certainly is possible. It''s likely not a very useful query, since "<anything> = NULL" will always return NULL, not true or false. But you can do it.> I think the latter is the SQL standard and works for sure in Oracle and > PostgreSQL.This behavior is definitely the SQL standard, and MySQL conforms to it as well. I just did a quick test: mysql> SELECT 1 = 1, 1 = 2, 1 <> 2, 1 = NULL, 1 <> NULL, NULL = NULL, NULL != NULL; +-------+-------+--------+----------+-----------+-------------+--------------+ | 1 = 1 | 1 = 2 | 1 <> 2 | 1 = NULL | 1 <> NULL | NULL = NULL | NULL != NULL | +-------+-------+--------+----------+-----------+-------------+--------------+ | 1 | 0 | 1 | NULL | NULL | NULL | NULL | +-------+-------+--------+----------+-----------+-------------+--------------+ Of course, those first three values would be true, false, and true in a more reasonable DBMS.> > I think it''s not a good idea to clutter the code base with esoteric > > DB2/Oracle/... knowledge. Any suggestions?I think the adapater will just have to know how to DTRT if this is essential. Fortunately, it doesn''t seem difficult, though it could mean executing a couple of extra queries on some DBMSes. But is this just needed for user stuff, or is it used elsewhere? A good relational design shouldn''t really need to use stuff like LIMIT. cjs -- Curt Sampson <cjs-gHs2Wiolu3leoWH0uzbU5w@public.gmane.org> +81 90 7737 2974 http://www.NetBSD.org Make up enjoying your city life...produced by BIC CAMERA
<contact-CKba8aKGf+ewktNWMKzPnQ@public.gmane.org>
2004-Dec-17 09:02 UTC
Re: Re: ActiveRecord: It''s not only quoting
Curt Sampson <cjs-gHs2Wiolu3leoWH0uzbU5w@public.gmane.org> schrieb am 17.12.2004, 09:42:12:> > > I think it''s not a good idea to clutter the code base with esoteric > > > DB2/Oracle/... knowledge. Any suggestions? > > I think the adapater will just have to know how to DTRT if this is > essential. Fortunately, it doesn''t seem difficult, though it could mean > executing a couple of extra queries on some DBMSes. > > But is this just needed for user stuff, or is it used elsewhere? A good > relational design shouldn''t really need to use stuff like LIMIT.It''s used in ActiveRecord::Base (find_all, find_first). I think that it''s not needed in find_first, because it calls select_one, but find_all seems to need it. Of course, the adapter could offer a brute force solution, i.e. fetch all rows until the limit is reached, but this could unnecessarily slow down some database servers. Maik
On Dec 17, 2004, at 9:34 AM, Michael Koziarski wrote:> On Fri, 17 Dec 2004 10:25:42 +0200, Jarkko Laine <jarkko-k1O+Gnc6WpmsTnJN9+BGXg@public.gmane.org> > wrote: >> >> On 17.12.2004, at 10:06, <contact-CKba8aKGf+ewktNWMKzPnQ@public.gmane.org> wrote: >>> >>> Additionally, most of the "SELECT" statements will not work with NULL >>> values. In DB2 it''s not possible to run a statement like "SELECT * >>> FROM >>> x WHERE y = NULL". It has to be "SELECT * FROM x WHERE y IS NULL". >> >> I think the latter is the SQL standard and works for sure in Oracle >> and >> PostgreSQL. > > Yes, is null is the standard, it works with mysql too.Not only that, it is a different statement: mysql> select count(*) from X where SignoffDate = null; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.03 sec) mysql> select count(*) from X where SignoffDate is null; +----------+ | count(*) | +----------+ | 108 | +----------+ 1 row in set (0.00 sec) As you can see the results are completely different. S.
That is because the SQL standard says that two NULLs are not equal. So if you did a comparison with NULL you would not get a result. So ''is NULL'' is the right way to do it. Shyam On Fri, 17 Dec 2004 10:25:30 +0100, Stefan Arentz <stefan.arentz-Ec4imsIGGIrYtjvyW6yDsg@public.gmane.org> wrote:> > On Dec 17, 2004, at 9:34 AM, Michael Koziarski wrote: > > > On Fri, 17 Dec 2004 10:25:42 +0200, Jarkko Laine <jarkko-k1O+Gnc6WpmsTnJN9+BGXg@public.gmane.org> > > wrote: > >> > >> On 17.12.2004, at 10:06, <contact-CKba8aKGf+ewktNWMKzPnQ@public.gmane.org> wrote: > >>> > >>> Additionally, most of the "SELECT" statements will not work with NULL > >>> values. In DB2 it''s not possible to run a statement like "SELECT * > >>> FROM > >>> x WHERE y = NULL". It has to be "SELECT * FROM x WHERE y IS NULL". > >> > >> I think the latter is the SQL standard and works for sure in Oracle > >> and > >> PostgreSQL. > > > > Yes, is null is the standard, it works with mysql too. > > Not only that, it is a different statement: > > mysql> select count(*) from X where SignoffDate = null; > +----------+ > | count(*) | > +----------+ > | 0 | > +----------+ > 1 row in set (0.03 sec) > > mysql> select count(*) from X where SignoffDate is null; > +----------+ > | count(*) | > +----------+ > | 108 | > +----------+ > 1 row in set (0.00 sec) > > As you can see the results are completely different. > > S. > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- "The best way to predict the future is to invent it" -- Alan Kay
> First of all I''ve added a small hack to the value_list method in class > Fixture (fixtures.rb): Things that look like an int (/^\d+$/) will be > converted to an int now and ":00" is appended to all values, that look > like a timestamp (/\d{4}-\d\d-\d\d \d\d:\d\d/). Otherwise DB2 won''t > accept them.How would that deal with things like credit card codes such as "0450"? Otherwise, it sounds fine with these hacks.> As far as I know the "old" test fixtures will be replaced by "typed" > ones (YAML) soon, won''t they?In Rails 0.9, the yml (and csv) fixtures are now the recommended use for fixtures. The generators create yml files, so basically the old test fixtures are deprecated. If you need to make the AR unit tests pass, please do convert all the AR fixtures to yml. I''d be fine with the fact that new adapters doesn''t work with the deprecated fixture style.> But there are more difficult things: The find method(s) in > ActiveRecord::Base don''t work properly because the sanitize method > still does not quote id values correctly. In my opinion all find > methods that expect an int or a list of ints should convert their > arguments using to_i before doing anything. Would that cause any > problems?Yeah, as others have mentioned, we want to allow string-based primary keys. But at the time of find, we could already know what type the id SHOULD have (through the column information) and typecast accordingly. I think that''s the way to go.> Additionally, most of the "SELECT" statements will not work with NULL > values. In DB2 it''s not possible to run a statement like "SELECT * FROM > x WHERE y = NULL". It has to be "SELECT * FROM x WHERE y IS NULL".When those statements like this occur? We could look into making quoted_comma_pair_list aware of nils and use the IS NULL style instead.> I''ve found some SQL statements using the "LIMIT" clause. This does not > exist in DB2 (at least not in Version 7) and it has to be replaced by > "fetch first <x> rows only". In Oracle I''ve always used "WHERE ROWNUM < > x" and I do not know, if Oracle has a "LIMIT" keyword.There has been some talk about turning the limit clause into something that could be delegated to the database to figure out. Just like we do for quoting. I think that''s a good idea.> I think it''s not a good idea to clutter the code base with esoteric > DB2/Oracle/... knowledge. Any suggestions?As needed, we can start putting more responsibility into the adapters when there''s _no way_ it could work otherwise. We should try to find other options first, but there''s nothing religious about it. Great work, Maik. It''s exciting to see the new adapters move towards completion. -- David Heinemeier Hansson, http://www.basecamphq.com/ -- Web-based Project Management http://www.rubyonrails.org/ -- Web-application framework for Ruby http://macromates.com/ -- TextMate: Code and markup editor (OS X) http://www.loudthinking.com/ -- Broadcasting Brain
>>>>> "Shyam" == Shyam Gopale <sgopale-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> writes:Shyam> That is because the SQL standard says that two NULLs are Shyam> not equal. So if you did a comparison with NULL you would Shyam> not get a result. So ''is NULL'' is the right way to do it. To dig even further the result of condition x = NULL (for any x) is NULL. While NULL coerced to a boolean is FALSE. The theoretical basis for this is three-value logic. http://c2.com/cgi/wiki?ThreeValuedLogic Gleb