sql queries templating (to handle params)

[xavier]

Tim suggested to have a self contained file containing both the sql query and defining what params are allowed (and their types)
http://forum.civicrm.org/index.php/topic,33165.msg141121.html#msg141121

It might be as well needed to have different queries based on a parameter. as an example for ep2014, I could either get all the candidates that signed a specific pledge (so join on group and where group=%groupid) or all candidates (so no join on group, but where contact_sub_type like "%candidate%")

I'm wondering if adding more cleverness to a mysql.tpl is the best way, shouldn't we simply allow {crmsql} to call a php function, and that function gets all the params. Basically what {crmAPI} does for the api, but it returns a sql instead of the list of results.

I'm looking for ideas for improving the implementation compared to what we have done for api (and who knows, use it as playground for api4)

1) I want to use Tim new sql builder magic, but not sure: is this in the core already? Can we use it for that?

2) instead of relying on a naming convention (function civicrm_api3_entity_action is into the file Entity.php...) can't we use something different? I somehow think about the file returning an anonymous function, but not quite sure how it's going to help

Might be simplier to simply define a namespace per extension and simply have a class that implements the query+param handling

X+

[totten]

I'm responding a little out-of-order. Sometimes out-of-order execution is more efficient...

I'm wondering if adding more cleverness to a mysql.tpl is the best way, shouldn't we simply allow {crmsql} to call a php function, and that function gets all the params. Basically what {crmAPI} does for the api, but it returns a sql instead of the list of results.

...

1) I want to use Tim new sql builder magic, but not sure: is this in the core already? Can we use it for that?

I'm not sure what this would look like, but yes -- yes, the query-builder was released with 4.4.5 -- https://github.com/civicrm/civicrm-core/blob/4.4.5/CRM/Utils/SQL/Select.php . It's also a single-file, so if you backward-compat, you could bundle it with an extension and devise some conditional loading.

I'm looking for ideas for improving the implementation compared to what we have done for api (and who knows, use it as playground for api4)

...

2) instead of relying on a naming convention (function civicrm_api3_entity_action is into the file Entity.php...) can't we use something different? I somehow think about the file returning an anonymous function, but not quite sure how it's going to help

If we're talking about Civi v4.5+ (with API kernel), then there are a lot of things we could do. Some off-the-wall ideas (with no particular filtering for good/bad):

Code: [Select]

// IDEA 1: Each named query is its own API. APIs are defined in Smarty using basic SQL templates.
// +
// IDEA 2: Put all the named-queries in a single tpl file.

// FILE: com.example.mymodule/templates/myvisualization.tpl
var data = {crmAPI entity=Foo action=bar prefix_id=3};

// FILE: com.example.mymodule/api/queries.tpl
{sqlAPI entity="Foo" action="bar}
  {sqlVar name="prefix_id" type="integer"}
  {sqlQuery} SELECT count(*) from civicrm_contact where prefix_id = {$prefix_id} {/sqlQuery}
{/sqlAPI}
{sqlAPI entity="Foo" action="whiz}
  {sqlVar name="contact_type" type="string"}
  {sqlQuery} SELECT count(*) from civicrm_contact where contact_type = {$contact_type} {/sqlQuery}
{/sqlAPI}



Code: [Select]

// IDEA 3: Each named query is its own API. APIs are defined in json.
// +
// IDEA 4: Use query-builder. Define baseline (that's always part of the query)
// and define white-list of options (group-by, where, etc) that can be
// chosen by API caller.

// FILE: template/my-visualization.tpl
var data = {crmAPI entity=Activity action=getsql groupby="c.contact_type,a.activity_type_id"};

// FILE: api/v4/Activity/getsql.sql-json
{
  permission: "access all contacts",
  select: "count(*)",
  from: "civicrm_contact c",
  join: [
   "inner join civicrm_activity_contact ac on ac.contact_id = c.id",
   "inner join civicrm_activity a on a.id = ac.activity_id"
  ],
  allow_group_by: ["c.contact_type", "c.contact_sub_type", "a.activity_type_id", "month(a.activity_date_time)"],
  allow_where: ["c.contact_type", "c.contact_sub_type", "a.activity_type_id", "month(a.activity_date_time)"]
}


[xavier]

I like the direction it's getting. I'm warming up to the api idea, but still not 100% sold yet.

It seems we are mixing two different issues (param for sql with the select builder and permission). I'm looking at the first one: what about having

query/activities.json that contains:

Code: [Select]

{
  select: "count(*)",
  from: "civicrm_contact c",
  join: [
   "inner join civicrm_activity_contact ac on ac.contact_id = c.id",
   "inner join civicrm_activity a on a.id = ac.activity_id"
  ],
  group_by: "c.contact_type, c.contact_sub_type,a.activity_type_id,month(a.activity_date_time)",
  where:  ["c.contact_type = @contact_type"]
}
and be used from

Code: [Select]

{crmsql jsonfile=activities contact_type="Individual"}
?

[Eileen]

X, my main argument for an api-centric approach is that, at least on contact api, the api respects ACLs as well as permissions (if you pass the check_permissions flag). I think it's inevitable we will add acls to the participants & contributions api - so if you are leveraging the api you can leave adding ACLs to your queries & treat it as something that 'will come when added to the respective api parts'

[s0014]

Hey,

Can someone please help me with http://forum.civicrm.org/index.php/topic,33537.0.html.