Export SQL used in the SMART Query
User would like an export of the SQL used in any given query so they can run it on the database themselves.
Leave a comment
This is probably not really feasible as most of the Queries actually involve a number of temporary tables and multiple SQL queries. So they would need to have a list of all the table creates, queries and table clean up commands. I just don't see how anyone will find it useful to pull all the SQL out of the logic that already runs it all in the right order and gives the results.
If they can describe why they want this, we may be able to suggest a better approach.
If they can describe why they want this, we may be able to suggest a better approach.
This is not likely going to be useful. In addition to the points Jeff points out (multiple tables, multiple parameters etc) the results displayed to the user and often not loaded until the user scrolls through the same. The query SQL just generates a table with with a bunch of UUID's which are more or less meaningless to the users unless they join them with the appropriate tables.
We can log most of the query statements and they look something like this:
CREATE TABLE query_temp_i2_1(entity_uuid char(16) for bit data, entity_type_key varchar(128))
8f7fbe1b201a4ef4bda814f5581e65ce
INSERT INTO query_temp_i2_1 SELECT l.uuid, o.keyid FROM smart.i_entity l JOIN smart.i_entity_type o on l.entity_type_uuid = o.uuid WHERE l.ca_uuid in (:cas)
CREATE INDEX query_temp_i2_1_entity_uuid_idx on query_temp_i2_1 (entity_uuid)
CREATE TABLE query_temp_i2_2 (entity_uuid char(16) for bit data, entity_type_key varchar(128), filter_1 boolean )
CREATE TABLE query_temp_i2_3(entity_uuid char(16) for bit data)
INSERT INTO query_temp_i2_3 SELECT distinct a.entity_uuid FROM query_temp_i2_1 a JOIN smart.i_entity e on a.entity_uuid = e.uuid JOIN smart.i_entity_type t on e.entity_type_uuid = t.uuid WHERE t.keyId = :typeKey
monster
CREATE INDEX entity_uuid_1_idx on query_temp_i2_3 (entity_uuid)
INSERT INTO query_temp_i2_2 SELECT a.*, CASE WHEN b.entity_uuid is null then null else true end FROM query_temp_i2_1 a LEFT JOIN query_temp_i2_3 b on a.entity_uuid = b.entity_uuid
DROP TABLE query_temp_i2_3
DROP TABLE query_temp_i2_1
RENAME TABLE query_temp_i2_2 TO query_temp_i2_1
CREATE INDEX query_temp_i2_1_entity_uuid_idx on query_temp_i2_1 (entity_uuid)
CREATE INDEX query_temp_i2_1_entity_uuid_idx on query_temp_i2_1 (entity_uuid)
CREATE TABLE query_temp_i2_4(entity_uuid char(16) for bit data, entity_type_key varchar(128), filter_1 boolean )
INSERT INTO query_temp_i2_4 SELECT * FROM query_temp_i2_1 WHERE ( filter_1 is not null )
DROP TABLE query_temp_i2_1
RENAME TABLE query_temp_i2_4 TO query_temp_i2_1
CREATE INDEX query_temp_i2_1_entity_uuid_idx on query_temp_i2_1 (entity_uuid)
CREATE INDEX query_temp_i2_1_entity_uuid_idx on query_temp_i2_1 (entity_uuid)
ALTER TABLE query_temp_i2_1 add column entity_type varchar(1024)
SELECT * FROM query_temp_i2_1