Para que seja possível ter o controle de acesso e evitar injeção de SQL será utilizado JSON no formato da query Puca para realizar consultas ao banco.
No entanto, é possível informar a consulta em SQL na tela de SQL Parser ou no componente de SQL Parser, responsável por transformar PQL (Puca Query Language) em SQL e vice-versa.
Obs: SQL Parser é uma funcionalidade que está em desenvolvimento/beta e está sendo testada e aprimorada. É possível que haja bugs e erros não tratados na conversão de Query.
Exemplos de Query Puca:
1. Consulta Simples e Instruções para Informar os Campos
1. Consulta Simples: Neste caso, a busca envolve todos os registros na tabela de pessoas/empresas representada por “puca_crm_api_person”, recuperando os campos como “name” e “cpf_cnpj” de cada registro.
Para uma consulta simples, onde apenas uma tabela é referenciada, não é necessário especificar a origem dos campos. No entanto, é possível usar alias para maior clareza ou quando há a possibilidade de conflito entre os nomes dos campos.
Exemplo sem alias:
{
"from": "puca_crm_api_person",
"fields": [
"puca_key",
"name",
"cpf_cnpj"
]
}
Exemplo com alias:
{
"from": "puca_crm_api_person",
"fields": [
"puca_key",
"name",
{
"field": "cpf_cnpj",
"alias": "any_alias_here_cpf_cnpj"
}
]
}
Para evitar ambiguidades, especialmente em consultas envolvendo múltiplas tabelas (por exemplo, usando a cláusula JOIN), é recomendável especificar a origem dos campos.
Exemplos de especificação da origem dos campos:
{
"from": "puca_crm_api_person",
"fields": [
{
"table": "puca_crm_api_person",
"field": "cpf_cnpj"
}
]
}
{
"from": "puca_crm_api_person",
"fields": [
{
"table": "puca_crm_api_person",
"field": "cpf_cnpj",
"alias": "any_alias_cpf_cnpj"
}
]
}
{
"from": "puca_crm_api_person",
"fields": [
"puca_crm_api_person.cpf_cnpj"
]
}
{
"from": "puca_crm_api_person",
"fields": [
{
"field": "puca_crm_api_person.cpf_cnpj",
"alias": "any_alias_cpf_cnpj"
}
]
}
Observação: Qualquer uma das variações acima permite a substituição de ‘.’ por ‘__’.
2. Consulta Filtrada com a cláusula "Where" e Instruções do atributo "operator"
2. Select Específico: Neste caso, está sendo realizado uma busca filtrando os registros da tabela de pessoas/empresas, de acordo com os argumentos da query.
- Os “args” devem ser informados no formato semelhante ao que está presente no exemplo, ou seja, caso a chave do argumento for composto por mais de uma palavra, elas devem ser separadas utilizando o “_” (snake_case).
- Feito isso, é necessário informar a cláusula “where” e “and”, passando o objeto onde estará a condicional para o filtro. Note que o objeto é composto por um atributo “v1” (que representa a coluna/atributo da tabela que está sendo realizada a busca), o atributo “operator” (representa o operador da condição, nesse caso “equals”) e o atributo “v2” que está representando um dos “args” principais informado na query (note que deve ser passado o simbolo “$” no início para se referir ao “args”) .
- Dessa forma, essa query pode ser interpretada da seguinte maneira: Busque na tabela de pessoas/empresa, os registros de natureza igual a “J” (ou seja, de natureza jurídica), trazendo os campos “puca_key”, “name”, “cpf_cnpj” para cada registro encontrado.
{
"args": {
"args_qualquer_nome_aqui_em_snake_case_natureza_pessoa_empresa": "J"
},
"from": "puca_crm_api_person",
"fields": [
"puca_key",
"name",
"cpf_cnpj"
],
"where":{
"and": [
{
"v1": "natureza",
"operator": "equals",
"v2": "$args_qualquer_nome_aqui_em_snake_case_natureza_pessoa_empresa"
}
]
}
}
- É possível também filtrar a consulta verificando se um campo é nulo como no caso abaixo: a Query busca registros na tabela “puca_crm_api_person” onde o campo “pid_system_user” é nulo (operator = “isNull”).
{
"from": "puca_crm_api_person",
"fields": [
"puca_key",
"name",
"cpf_cnpj"
],
"where": {
"and": [
{
"v1": "pid_system_user",
"operator": "isNull"
}
]
}
}
Observação: O atributo "operator" informado na cláusula "and"
pode assumir os seguintes valores:
equals: `$v1 = $v2`,
less: `$v1 < $v2`,
lessOrEquals: `$v1 <= $v2`,
greater: `$v1 > $v2`,
greaterOrEquals: `$v1 >= $v2`,
iEquals: `$v1 ilike $v2`,
contains: `$v1 like '%' || $v2 || '%'`,
iContains: `$v1 ilike '%' || $v2 || '%'`,
startsWith: `$v1 like $v2 || '%'`,
endsWith: `$v1 like '%' || $v2`,
isNull: `nullif( $v1::varchar, '') is null`,
anyOf: `$v1 = any( $v2 )`
- Já neste exemplo abaixo, a consulta realizada pela Query busca registros na tabela “puca_crm_api_person” onde o nome não corresponde ao nome especificado nos argumentos, utilizando a cláusula “not” em “where”.
{
"args": {
"args_ignorar_nome": "Nome Qualquer de Pessoa/Empresa Cadastrada no PUCA"
},
"from": "puca_crm_api_person",
"fields": [
"puca_key",
"name",
"cpf_cnpj"
],
"where": {
"not": {
"v1": "puca_crm_api_person.name",
"operator": "equals",
"v2": "$args_ignorar_nome"
}
}
}
3. Select Simples utilizando funções para os campos do tipo Data (DATE_TRUNC)
3. Select Simples utilizando funções para os campos do tipo Data (DATE_TRUNC): Neste caso, está sendo realizada uma busca na tabela de pessoas/empresas com a aplicação de funções nos campos. Um novo campo é adicionado aos resultados. O campo “puca_created_at” está passando por uma função de truncamento de data (“date_trunc”), sendo truncado para o intervalo de dia (“day”).
- Isso resulta em uma consulta que recupera registros da tabela de pessoas/empresa, trazendo os campos “puca_key”, “name”, “cpf_cnpj”, e também adicionando um campo adicional chamado puca_created_at" que representa a data de criação truncada para o intervalo de dia.
- Observação: é possível truncar a data por “year”, “month”, “day”, “hour”, “minute”, “second”, “millisecond”
{
"args": {
"args_date_trunc_day": "day"
},
"from": "puca_crm_api_person",
"fields": [
"puca_key",
"name",
"cpf_cnpj",
{
"function": "date_trunc",
"args": [
"$args_date_trunc_day",
{
"table": "puca_crm_api_person",
"field": "puca_created_at"
}
],
"alias": "qualquer_nome_aqui_result_function_puca_created_at"
}
]
}
4. Select Simples utilizando funções para os campos
4. Select Simples utilizando funções para os campos: Neste caso, é realizada uma busca na tabela de pessoas/empresa, aplicando a função de multiplicação (“multiply”) ao campo “puca_key” (este campo representa a posição do registro na tabela).
- Ao invés de informar a coluna que deseja ser recuperada do registro da tabela passando apenas o nome dela, é informado um objeto com os atributos “function” (para informar qual função será aplicada no campo em questão), um atributo “args” (para informar os argumentos da função) e o atributo “alias” para representar o nome da coluna resultante da função aplicada.
- Observação: É possível informar as seguintes funções utilizando o mesmo formato: “add”, “subtract”, “multiply” e “divide”
{
"args": {
"args_multiply_puca_key": 1000
},
"from": "puca_crm_api_person",
"fields": [
{
"function": "multiply",
"args": [
"$args_multiply_puca_key",
{
"table": "puca_crm_api_person",
"field": "puca_key"
}
],
"alias": "coluna_result_function_multiply"
},
"name",
"cpf_cnpj",
"puca_created_at"
]
}
- Note que no exemplo, um dos argumentos da função é declarado no atributo “args” principal da query, e o outro argumento é a própria coluna da tabela em que deseja aplicar a função
5. Select Simples utilizando função de agregadores "count" e agrupadores
5. Select Simples utilizando função de agregadores “count” e agrupadores: Neste exemplo, uma consulta está sendo feita na tabela de pessoas/empresas, aplicando a função de agregação “count” ao campo “puca_key”. Além disso, a cláusula GROUP BY é utilizada para agrupar os resultados com base na coluna “natureza”. Isso resulta em uma contagem de registros para cada valor único presente na coluna “natureza”.
- Observação: As função de agregadores disponíveis (que seguem este mesmo formato do exemplo abaixo) são: sum, count, count_distinct, max, min, avg. Ao adicionar uma função de agregador, é necessário incluir a cláusula GROUP BY, especificando a tabela e o campo pelo qual os resultados devem ser agrupados.
{
"from": "puca_crm_api_person",
"fields": [
"natureza",
{
"args": [
"puca_crm_api_person.puca_key"
],
"alias": "count_person",
"function": "count"
}
],
"groupBy": [
{
"field": "natureza",
"table": "puca_crm_api_person"
}
]
}
6. Select utilizando a cláusula "join" e Instruções do modos suportados
6. Select utilizando a cláusula “join”: Neste exemplo, a consulta envolve a utilização da cláusula “join” para combinar informações de duas tabelas, a “puca_crm_api_person” e a “puca_base_api_system_user”. A tabela principal é renomeada como “person_table” usando o alias. Os campos desejados para exibição são provenientes tanto da tabela principal quanto da tabela de junção.
- A tabela “puca_crm_api_person” tem os campos “name” e “cpf_cnpj” que são selecionados diretamente da “person_table”.
- A tabela “puca_base_api_system_user” é vinculada através da cláusula “join”. O alias “user_table” é utilizado para referenciar essa tabela na seleção de campos.
- O vínculo entre as duas tabelas é estabelecido através da condição de igualdade entre os campos “pid_system_user” da tabela “person_table” e “puca_pid” da tabela “user_table”.
- O resultado final é uma consulta que retorna os campos “name” e “cpf_cnpj” da tabela de pessoas/empresa e o campo “username” da tabela de usuários do sistema, onde as condições de junção são satisfeitas.
- Observação: A cláusula “join” possui o atributo “mode” que pode assumir os valores: “inner”, “left”, “right”, “full”.
{
"from": {
"table":"puca_crm_api_person",
"alias": "person_table"
},
"fields": [
{
"table": "person_table",
"field": "name",
"alias": "person_name"
},
{
"table": "person_table",
"field": "cpf_cnpj",
"alias": "person_cpf_cnpj"
},
{
"table": "user_table",
"field": "username",
"alias": "user_username"
}
],
"join": [
{
"mode": "inner",
"tableName": "puca_base_api_system_user",
"alias": "user_table",
"on": {
"v1": "person_table.pid_system_user",
"operator": "equals",
"v2": "user_table.puca_pid"
}
}
]
}
7. Select utilizando a clausula "orderBy"
7. Select utilizando a clausula orderBy: Neste exemplo, a cláusula “orderBy” é utilizada para ordenar os resultados da consulta com base em um campo específico. No caso, a tabela de pessoas/empresa “puca_crm_api_person” está sendo consultada e os resultados estão sendo ordenados de forma descendente (“desc”) com base no campo “puca_key”. Isso significa que os registros serão apresentados em ordem decrescente de valores no campo “puca_key”.
{
"from": "puca_crm_api_person",
"fields": [
"puca_key",
"name",
"cpf_cnpj"
],
"orderBy": [
{
"sort": "desc",
"table": "puca_crm_api_person",
"field": "puca_key"
}
]
}
8. Select simples utilizando a cláusula "cast" e descrição dos tipos de dados suportados
8. Select simples utilizando a cláusula “cast”: Neste caso, a cláusula “cast” é utilizada para realizar a conversão de um campo específico para um tipo de dado diferente. A tabela de pessoas/empresa “puca_crm_api_person” está sendo consultada, e o campo “puca_key” está sendo selecionado com um alias “result_puca_key_cast” (coluna que representará o resultado da conversão entre os tipos de dados), sendo convertido do tipo de dado “number” para o “varchar” por meio da cláusula “cast”.
{
"from": "puca_crm_api_person",
"fields": [
{
"table": "puca_crm_api_person",
"field": "puca_key",
"alias": "result_puca_key_cast",
"cast": "varchar"
},
"name",
"cpf_cnpj"
]
}
- Observação: levando em consideração os tipo de dados suportados, o atributo “cast” pode assumir os seguintes valores:
varchar
numeric
boolean
date
timetz
timestamptz
uuid
jsonb
enum
*- Fique atento ao contexto e ao valor do campo para realizar conversões entre tipos de dados. É possível, por exemplo, realizar a conversão de um campo texto (varchar) que possui o valor no formato iso 8601 para um tipo “timestamptz”.
9. Select com um Campo Condicional
9. Select com um Campo Condicional: Neste exemplo, a tabela de pessoas/empresa “puca_crm_api_person” está sendo consultada e um campo adicional chamado “status” está sendo criado com base nas condições definidas.
- Na declaração do campo adicional “status” foi adicionado o atributo “conditions”, um array de objetos que possuem os atributos “when” (esse com formato de um objeto “Where”) e “then” (declaração do valor que o campo deve assumir com base na condição definida no atributo “when”).
- O campo “puca_key” é comparado com o valor fornecido na query através do argumento “$arg_case_conditions”. Dependendo da comparação, o campo “status” recebe valores diferentes (“Alto”, “Baixo” ou “Normal”).
- Note que os possíveis valores de “status” dependem da declaração no “args” principal da query.
{
"from": "puca_crm_api_person",
"args": {
"arg_case_conditions": 5,
"arg_case_low": "Baixo",
"arg_case_high": "Alto",
"arg_case_normal": "Normal"
},
"fields": [
"puca_key",
"name",
"cpf_cnpj",
{
"alias": "status",
"conditions": [
{
"when": {
"v1": {
"table": "puca_crm_api_person",
"field": "puca_key"
},
"operator": "greater",
"v2": "$arg_case_conditions"
},
"then": "$arg_case_high"
},
{
"when": {
"v1": {
"table": "puca_crm_api_person",
"field": "puca_key"
},
"operator": "lessOrEquals",
"v2": "$arg_case_conditions"
},
"then": "$arg_case_low"
}
],
"else": "$arg_case_normal"
}
]
}
10. Select utilizando a cláusula "WhereExists"
10. Select utilizando a cláusula “WhereExists”: Neste caso, a cláusula “WhereExists” é utilizada para verificar se existe pelo menos um registro na tabela “puca_base_api_system_user” que satisfaça as condições especificadas. A tabela de pessoas/empresa “puca_crm_api_person” está sendo consultada, e os registros são filtrados com base em uma condição de existência na tabela “puca_base_api_system_user”.
- A condição de existência é especificada usando a cláusula “exists”. Ela verifica se há algum registro na tabela “puca_base_api_system_user” que atenda às seguintes condições:
- O campo “puca_pid” da tabela “puca_base_api_system_user” é igual ao campo “pid_system_user” da tabela “puca_crm_api_person”.
- O campo “active_days” da tabela “puca_base_api_system_user” é maior ou igual ao valor fornecido na query através do argumento “$arg_exists_conditions_active_days”.
{
"args": {
"arg_exists_conditions_active_days": 5
},
"fields": [
"name",
"cpf_cnpj"
],
"from": "puca_crm_api_person",
"where": {
"exists": {
"from": "puca_base_api_system_user",
"where": {
"and": [
{
"v1": {
"table": "puca_base_api_system_user",
"field": "puca_pid"
},
"operator": "equals",
"v2": {
"table": "puca_crm_api_person",
"field": "pid_system_user"
}
},
{
"v1": {
"table": "puca_base_api_system_user",
"field": "active_days"
},
"operator": "greaterOrEquals",
"v2": "$arg_exists_conditions_active_days"
}
]
}
}
}
}
- Observação: Note que o atributo “exists” recebe um objeto no formato de um Select.
11. Select com um Subselect presente na cláusula "fields" da Query
11. Select com um Subselect presente na cláusula “fields” da Query: Neste caso, a cláusula “fields” inclui um subselect para contar a quantidade de e-mails associados a cada registro na tabela “puca_crm_api_person”. O subselect é realizado na tabela “puca_crm_api_email” e o resultado é representado por um alias “qnt_emails_associados”. A contagem é feita usando a função de agregação “count” no campo “puca_crm_api_email__puca_key”, agrupando por cada registro na tabela “puca_crm_api_person”.
{
"from": "puca_crm_api_person",
"fields": [
"puca_key",
"name",
"cpf_cnpj",
{
"alias": "qnt_emails_associados",
"from": "puca_crm_api_email",
"fields": [
{
"function": "count",
"args": [
"puca_crm_api_email.puca_key"
]
}
],
"where": {
"and": [
{
"v1":"puca_crm_api_email.owner" ,
"operator": "equals",
"v2": "puca_crm_api_person.puca_pid"
}
]
}
}
]
}
12. Select limitando a quantidade de registros
12. Select limitando a quantidade de registros: Neste caso, a consulta está sendo limitada para recuperar registros da tabela “puca_crm_api_person” apenas da segunda página, levando em consideração o tamanho de página de 10 registros.
{
"from": "puca_crm_api_person",
"fields": [
"puca_key",
"name",
"cpf_cnpj"
],
"page":2,
"pageSize": 10
}
- Observação: A cláusula “page” define a página desejada, e “pageSize” especifica o número máximo de registros a serem retornados por página.
13. Select com função condicional "COALESCE"
13. Select com função condicional “COALESCE”: Neste caso, a função condicional “COALESCE” é utilizada para lidar com valores nulos ou ausentes em campos específicos da tabela “puca_crm_api_person”. A consulta retorna os campos “puca_key”, “name”, “shortname”, “natureza”, “birthday” (tratado pela função COALESCE) e “contact_email” (tratado pela função COALESCE).
- A função COALESCE permite definir valores padrão caso o campo seja nulo ou ausente. No exemplo, os valores padrão são definidos nos argumentos “$arg_func_coalesce_birthday” e “$arg_func_coalesce_email”. Se o campo “birthday” for nulo, será retornado o valor padrão “Não existe aniversário informado”. Se o campo “contact_email” for nulo, será retornado o valor padrão “Não existe email informado”.
{
"args": {
"arg_func_coalesce_birthday": "Não existe aniversário informado",
"arg_func_coalesce_email": "Não existe email informado"
},
"from": "puca_crm_api_person",
"fields": [
"puca_key",
"name",
"shortname",
"natureza",
{
"function": "coalesce",
"args": [
{
"table": "puca_crm_api_person",
"field": "birthday",
"cast": "varchar"
},
"$arg_func_coalesce_birthday"
],
"alias": "there_is_birthday"
},
{
"function": "coalesce",
"args": [
{
"table": "puca_crm_api_person",
"field": "contact_email",
"cast": "varchar"
},
"$arg_func_coalesce_email"
],
"alias": "there_is_contact_email"
}
]
}
14. Select com função de agregação "string_agg"
14. Select com função de agregação “string_agg”: Neste exemplo, a função de agregação ‘string_agg’ é utilizada para concatenar os nomes da tabela “puca_crm_api_person” com base na coluna “natureza”. A cláusula “groupBy” é empregada para agrupar os resultados levando em conta a coluna “natureza”, e a função ‘string_agg’ concatena os nomes separados por vírgula.
{
"args": {
"virgula": ", "
},
"from": "puca_crm_api_person",
"fields": [
"natureza",
{
"function": "string_agg",
"args": [
"puca_crm_api_person.name",
"$virgula"
],
"alias": "nomes_concatenados"
}
],
"groupBy": [
{
"table": "puca_crm_api_person",
"field": "natureza"
}
]
}
Observação: O argumento “$virgula” é utilizado para especificar o separador entre os nomes, que neste caso é a vírgula.
15. Select com a clausula "with"
15. Select com a clausula “with”: Neste caso, a cláusula “with” é utilizada para criar uma expressão comum de tabela (CTE) chamada “cte_select_email”. Essa CTE seleciona os campos “puca_key” e “owner” da tabela “puca_crm_api_email”.
A consulta principal, em seguida, utiliza a CTE “cte_select_email” para contar a quantidade de e-mails associados a cada pessoa/empresa (o atributo owner representa a FK para a tabela de pessoa/empresa). Os resultados são agrupados pelo campo “owner”.
{
"with": [
{
"alias": "cte_select_email",
"select": {
"from": "puca_crm_api_email",
"fields": [
"puca_key",
"owner"
]
}
}
],
"from": "cte_select_email",
"fields": [
{
"table":"cte_select_email",
"field": "owner"
},
{
"alias": "contagem_email",
"function": "count",
"args": [
"cte_select_email.puca_key"
]
}
],
"groupBy": [
"cte_select_email.owner"
]
}
16. Select com a clausula "union" e "unionAll"
16. Select com a cláusula “union” e “unionAll”: Neste exemplo, a cláusula “union” é utilizada para combinar os resultados de duas consultas distintas na tabela “puca_crm_api_person”. A primeira consulta seleciona registros onde a natureza é jurídica (“J”), enquanto a segunda consulta seleciona registros com natureza física (“F”). A cláusula “fields” define os campos a serem recuperados, e a cláusula “where” estabelece as condições para cada parte da união.
- Observação: A cláusula “union” é usada para combinar os resultados de duas ou mais consultas, removendo automaticamente as duplicatas, o que significa que apenas valores únicos são incluídos no resultado final. Já a cláusula “unionAll” também é usada para combinar os resultados de duas ou mais consultas, mas não remove duplicatas. Ele inclui todas as linhas resultantes, mesmo se houver duplicatas.
{
"args": {
"arg_where_person_natureza_juridica": "J",
"arg_where_person_natureza_fisica": "F"
},
"fields": [
"puca_key",
"shortname",
"natureza",
"name",
"cpf_cnpj"
],
"from": "puca_crm_api_person",
"where": {
"and": [
{
"v1": "puca_crm_api_person.natureza",
"operator": "equals",
"v2": "$arg_where_person_natureza_juridica"
}
]
},
"union": [
{
"fields": [
"puca_key",
"shortname",
"natureza",
"name",
"cpf_cnpj"
],
"from": "puca_crm_api_person",
"where": {
"and": [
{
"v1": "puca_crm_api_person.natureza",
"operator": "equals",
"v2": "$arg_where_person_natureza_fisica"
}
]
}
}
]
}
- Note que tanto o atributo da query “union” como a “unionAll” receberá um array de objetos no formato de um Select.