Eduard Lebedyuk · Dec 19, 2016 3m read

Tips & Tricks - SQL queries management

If you have a lot of SQL queries to external systems (or even internal dynamic ones), you can encounter something like this:

Set SQL =    "SELECT "_
                "c.cid                        AS Id,"_
                "c.nid                        AS Nid, "_
                "FROM_UNIXTIME(c.created)     AS Created,"_
                "c.uid                        AS Uid,"_
                "IFNULL(vv.average,0)         AS AvgVote,"_
                "IFNULL(vv.amount,0)          AS VotesAmount,"_
                "body.comment_body_value      AS Text,"_
                "'comment'                    AS Type "_
            "FROM comment AS c "_
                "LEFT OUTER JOIN node ON node.nid = c.nid "_  
                "INNER JOIN field_data_field_forum_ref AS ref ON ref.entity_id = node.nid "_
                "LEFT OUTER JOIN field_data_comment_body AS body ON c.cid = body.entity_id "_
                "LEFT OUTER JOIN (SELECT entity_id , SUM(value) AS average, COUNT(1) AS amount "_
                                  "FROM votingapi_vote "_
                                  "WHERE entity_type = 'comment' "_
                                  "GROUP BY entity_id) AS vv ON vv.entity_id = c.cid "_
                + 100 more lines of SQL code omitted, but you get the idea

Looks awful?


But that's not the only problem. Queries in class code also:

  • Make it harder to read and understand source code
  • No SQL code highlighting
  • If you want to execute the query in any other tool, you'll need to copy it to external editor and do a bunch of Find&Replaces and probably reformat the query after that

I was plagued by this problem, before developing the solution that I'd like to share.

Class definition can have a Query element declared and it can be a basic class query or a custom class query (more on that). Basic class query text gets checked during compilation, but you can write anything in custom class query body and it would compile.

Here I created a separate class to hold custom queries and a method to return query text by it's name:

Class Utils.SQL [ Abstract ]

/// Return query text by name. Removes linebreaks if removeNL is 1
/// write ##class(Utils.SQL).getSQL("Comments")
ClassMethod getSQL(name As %String, removeNL = {$$$NO}) As %String
    #dim sc As %Status = $$$OK
    set query = ##class(%Dictionary.QueryDefinition).IDKEYOpen($classname(), name,, .sc)
    throw:$$$ISERR(sc) ##class(%Exception.StatusException).CreateFromStatus(sc)

    set sql = query.SqlQuery
    set:(removeNL = $$$YES) sql = $replace(sql, $$$NL, " ")
    return sql

/// Get comments from mysql db
Query Comments() As %Query
  c.cid                    AS Id,
  c.nid                    AS Nid,
  FROM_UNIXTIME(c.created) AS Created,
  c.uid                    AS Uid,
  IFNULL(vv.average, 0)    AS AvgVote,
  IFNULL(vv.amount, 0)     AS VotesAmount,
  body.comment_body_value  AS Text,
  'comment'                AS Type
FROM comment AS c
  LEFT OUTER JOIN node ON node.nid = c.nid
  INNER JOIN field_data_field_forum_ref AS ref ON ref.entity_id = node.nid
  LEFT OUTER JOIN field_data_comment_body AS body ON c.cid = body.entity_id
                     SUM(value) AS average,
                     COUNT (1)  AS amount
                   FROM votingapi_vote
                   WHERE entity_type = 'comment'
                   GROUP BY entity_id) AS vv ON vv.entity_id = c.cid
  node.status = 1
  AND node.type IN ('code_package', 'documentation', 'learning_track', 'video', 'post')
GROUP BY c.cid

And in code, you can get the query text by executing:

Set SQL = ##class(Utils.SQL).getSQL("Comments")

Also, there is SQL code highlighting. And, of course, you can just copy/paste this sql to any other tool without any hassle.

How do you manage your SQL queries?


0 1,158
Discussion (2)2
Log in or sign up to continue

Note that any time you build a query as a string if you allow users to insert parameter values or control the string you are building in any way into you need to be aware of SQL injection attacks. This is not a problem with '?' query argument substitution as it is designed to avoid injection attacks, but if you have say:

Set sql="select Name from MyTable where Age > "_userage

And the user supplies 'userage' then they could provide "100; drop table MyTable;" or worse.

Prescribed locations for SQL and XML is a great feature of COS/Studio.