On Sep 12, 2008, at 8:23 PM, Gavin Kistner wrote:
> On Sep 12, 2008, at 6:53 PM, Jeremy Evans wrote:
>>
>> SQLite may not consider it a bug, but I do, as no sane database
>> should
>> operate that way. That said, I'd be willing to consider a patch for
>> the SQLite adapter that used AS for regular columns.
>
<snip>
> I'll peek into the patch idea, though, so Sequel will work as (I)
> expected for older installs of SQLite.
> Give me a hint. Am I correct in thinking that it will involve code in
> one of the sqlite.rb files, code which will supersede the definition
> of select_sql in sql_core/dataset/sql.rb in a way that changes the
> return value of column_list, but only for specific columns in the set?
> Or perhaps just a custom implementation of column_list?
>
> (I've not yet seen where all that is used.)
Hrm. Having peeked a bit, I'm backing off. Here's what I think I
found, in case someone braver than I wants to dive in:
The method that creates the SQL text for a select statement is:
Sequel::Dataset#select_sql (in sequel_core/dataset/sql.rb)
This can be overridden, if desired, by defining a similar method here:
Sequel::SQLite::DatasetMethods#select_sql (in sequel_core/adapters/
shared/sqlite.rb)
However, #select_sql leans on the (private) method
Sequel::Dataset#column_list to convert the array of column names
(symbols and other magic things) into the final sql syntax.
This method in turn leans on Sequel::Dataset#literal to convert the
great variety of ways to specify a value into something usable.
Yay! SQLite already overrides this method as
Sequel::SQLite::Dataset#literal (in sequel_core/adapters/sqlite.rb)
Oh, but boo...it punts back to the main #literal under most cases.
Sequel::Dataset#literal in turn leans on
Sequel::Dataset#symbol_to_column_ref to turn columns-as-symbols into
SQL code. It is this method that turns :foo into `foo`, but it also
turns :foo__bar into `foo`.`bar` and :foo___bar into `foo` AS `bar`.
(Aside: shouldn't that be `foo` AS 'bar' instead? Is it really kosher
to use quote_identifier on an alias name?)
Just patching symbol_to_column_ref to always add an alias, even if not
specified, breaks other locations, like:
INSERT INTO `items` AS `items` (`name` AS `name`, `price` AS `price`)
VALUES ('abc', 6)
The same is true to a slightly lesser extent for patching #literal to
always throw AS in:
INSERT INTO `items` (`name` AS 'name', `price` AS 'price') VALUES
('abc', 10)
The problem seems to be that items like symbols are being converted
into SQL without any semantic knowledge of where they're being used.
Thus, I can't figure out how to say ":table as a column should use one
SQL representation in one SQL statement, a different representation in
a different SQL statement, and a third representation in a different
part of the same SQL statement."
Which is actually represented in this bug I just filed:
http://code.google.com/p/ruby-sequel/issues/detail?id=241
items.group_and_count(:name___name).sql
#=> SELECT `name` AS `name`, count(*) AS `count` FROM `items`
GROUP BY `name` AS `name` ORDER BY `count`
Similar to my problem, symbol_to_column_ref doesn't realize that the
alias isn't appropriate or wanted inside the GROUP BY clause, but it's
so far removed from the bit of code that knows about GROUP BY that it
has no way of being told not to use aliases.
Right now the 'best' way I can think to fix SQLite to always use AS
for the column names in SELECT statements is full-on string munging of
the final SQL statement. Something like:
# in sqlite.rb
def select_sql
super
super.gsub /gross non-performant regexp hackery/, '\\1 AS \\1'
end
That's so horrible, I assume Jeremy would reject the patch on
principle alone. :p
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
