On Nov 10, 12:29 am, Ciaran Archer <[email protected]> wrote:
> Hi there
>
> First up, thanks for all the hard work on a great database library and
> abstraction layer. We're really going to enjoy using it on our new
> JRuby / SQLServer application.
>
> Our database admin guys often run profiling jobs on the database which
> would throw up a list of SQL statements that run long repeatedly. They
> come to us and we would often have a tough time working out where in
> code the SQL statement resides because of the Sequel abstraction
> layer. This of course is not unique to Sequel.
>
> For hand written SQL we sometimes provide a comment to link it back to
> the code, so:
>
> /* Clients query */ SELECT * FROM CLIENTS
>
> We can then search our code base for the string /* Clients query */ in
> order to quickly find the correct line.
>
> So I was going to propose a way to 'tag' a Sequel statement with an
> identifier that gets built into the string executed:
>
> @results = settings.databases[:my_database][:clients]
> .join(:clientsettings, :clientid => :clientid)
> .where(:firstname.like('Ciaran%'))
> .select(:clients__clientid, :firstname, :isinternalaccount, :status)
> .tag('clients query')
> .all
>
> Which would execute:
>
> /* clients query */ SELECT [CLIENTS].[CLIENTID], [FIRSTNAME],
> [ISINTERNALACCOUNT], [STATUS] FROM [CLIENTS] INNER JOIN
> [CLIENTSETTINGS] ON ([CLIENTSETTINGS].[CLIENTID] = [CLIENTS].
> [CLIENTID]
> ) WHERE (([FIRSTNAME] COLLATE Latin1_General_CS_AS) LIKE (N'Ciaran%'
> COLLATE Latin1_General_CS_AS))
>
> If there is another way to do this, or a fundamental reason why this
> is a bad idea, please let me know.
The easiest way to do this is to add a Database#tag method, make
Database#select_clause_methods have :select_tag_sql as the first
entry, and have select tag_sql add the tag to the SQL string. I would
recommend using the master branch if you plan to do this, since
literalization has changed significantly since 3.29.0, and should be
fairly easy to implement. This should work:
DB.extend_datasets do
def tag(t)
clone(:tag=>t)
end
private
def select_clause_methods
[:select_tag_sql] + super
end
def select_tag_sql(sql)
sql << "/* #{opts[:tag]} */ " if opts[:tag]
end
end
As the implementation is trivial and I doubt many Sequel users would
need this (plus the tag syntax to use would be database dependent),
it's not something I plan to add to Sequel. But using the above code
should cause no problems.
Jeremy
--
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.
