Scrivere le query

Esistono molte funzioni che accettano una query come argomento, come queryContacts, queryContents, executeSimpleQuery, executeUpdate.
Tutte queste funzioni utilizzano il linguaggio MQL per esprimere i comandi di accesso ai dati. Questo linguaggio è un sotto-dialetto del linguaggio SQL standard, con alcune estensioni e alcune limitazioni.

Sintassi di base

La sintassi di base è: SELECT * FROM TABLENAME WHERE CONDITIONS ORDER BY ORDERBYEXPRESSIONS

Alcuni esempi:

 SELECT * FROM CONTACTS WHERE status='subscribed'

 SELECT * FROM CONTACTS WHERE name = ?

 SELECT * FROM CONTACTS WHERE relationfield in (SELECT myvalue FROM MYCUSTOMTABLE WHERE customfield=123)

 SELECT name,myfield FROM MYCUSTOMTABLE ORDER BY myorderfield

 SELECT name,myfield FROM MYCUSTOMTABLE INNER JOIN OTHERTABLE ON MYCUSTOMTABLE.a=OTHERTABLE.b ORDER BY myorderfield

 SELECT * FROM MYCUSTOMTABLE WHERE myfield in (SELECT idcontact from CONTACTS)

 SELECT * FROM MYCUSTOMTABLE WHERE myfield = strtonumber('100,66')

 SELECT max(myfield) FROM MYCUSTOMTABLE

 SELECT * FROM CONTACTS WHERE to_datetime(data_ultima_modifica) >= now(-3600)

 SELECT * FROM CONTACTS WHERE to_date(data_iscrizione) >= today(-2)

 SELECT * FROM CONTACTS WHERE to_date(data_iscrizione) >= strtodate('10/01/2020')

 SELECT * FROM CONTACTS WHERE to_datetime(data_iscrizione) >= strtodatetime('10/01/2020 00:05:00')

 SELECT email, data_eliminazione FROM contacts WHERE status='unsubscribed' AND to_date(data_eliminazione) = strtodate('05/08/2020 19:22:00')

 SELECT * FROM WEBPAGES WHERE name  LIKE 'mypagenamestartswith%'

 SELECT * FROM CONTACTS WHERE to_date(data_ultima_modifica) >= to_date(dateadd(data_ultima_modifica, -1, 'day'))

 SELECT * FROM CONTACTS WHERE STATS.TOTALCLICKS > 10

 SELECT * FROM CONTACTS WHERE STATS.IDCHANNEL = 0 AND STATS.TOTALCLICKS > 10

Funzioni SQL supportate

Questo è l'insieme delle funzioni SQL:

• count(*): come la funzione SQL
• max(columnname): come la funzione SQL
• min(columnname): come la funzione SQL
• avg(columnname): come la funzione SQL
• sum(columnname): come la funzione SQL
• lcase(columnname): come la funzione SQL (converte la stringa a lowercase)
• ucase(columnname): come la funzione SQL (converte la stringa a uppercase)
• to_date(columnname): converte un valore di colonna/scala in una data senza orario
• to_datetime(columnname): converte un valore di colonna/scala in una data con orario
• to_int (columnname): converte un valore (string, decimal...) in un numero intero (integer)
• to_string (columnname): converte un valore (date, decimal...) in una stringa (string)
• strtodate(date_literal): converte una stringa scritta come data nel formato dell'utente che sta eseguendo la query (utilizzando il fuso orario dell'utente)
• strtodatetime(datetime_literal): converte una stringa scritta come data-ora nel formato dell'utente che sta eseguendo la query (utilizzando il fuso orario dell'utente)
• strtonumber (number_literal): converte una stringa che contiene caratteri numerici in un numero
• now(offset_in_millis): ottiene la data-ora corrente, scaled by an offset in milliseconds
• today(offset_in_day): ottiene la data corrente senza orario (a mezzanotte), scaled by an offset in days
• year(columnname): come la funzione SQL
• dayofmonth(columnname): come la funzione SQL
• weekday(columnname): come la funzione SQL
• month(columnname): come la funzione SQL
• dateadd(columnname,number_of_days,'day'): aggiunge un numero di giorni alla data specificata
• fielddescription(fieldname): esegue la ricerca di un valore e restituisce la descrizione del valore di un campo elenco di valori

Gestione di date e orari

Quando si scrivono date e orari direttamente nelle query MQL, bisogna fare attenzione a utilizzare sempre le funzioni strtodate/strtodatetime, che garantiscono che il sistema li analizzi utilizzando il tuo formato della data e dell'orario e soprattutto il tuo fuso orario.

Joins e Subqueries

Puoi utilizzare INNER JOIN, RIGHT JOIN e LEFT JOIN quando scrivi le query sulle tabelle custom, ma non sulle tabelle di sistema.
Puoi anche usare l'operatore 'IN' con una sotto-selezione per ottenere lo stesso risultato.
Nelle sotto-selezioni non è possibile utilizzare i dati della query esterna.

Tabelle di sistema supportate

Puoi scrivere query sulle tabelle custom e su alcune tabelle di sistema.

È possibile eseguire query MQL solo per leggere i dati dalle tabelle di sistema e per aggiornare i dati è necessario utilizzare le funzioni specifiche (come mergeContact).

Tabelle di sistema supportate:

• CONTACTS: rappresenta i contatti del database;
  
  Devi avere il permesso di esportare i dati dal database per eseguire query su questa tabella.
• GROUPS: rappresenta gli insiemi di contatti, le audience;
• STATICGROUPMEMBERS: rappresenta i membri di una audience statica;
  
  Devi avere il permesso di esportare i dati dal database per eseguire query su questa tabella.
• CONTENTS: rappresenta il database dei contenuti;
• NEWSLETTERS: rappresenta le tue comunicazioni;
• WEBPAGES: rappresenta le tue pagine web;
• SURVEYS: rappresenta i tuoi survey;
• SURVEYSESSIONS: rappresenta le sessioni di un survey;
  
  Devi avere il permesso di esportare i dati dal database per eseguire query su questa tabella.
• WORKFLOWS: rappresenta tutti i workflow in ogni stato (attivo, in pausa o fermo);
• WORKFLOWSESSIONS: rappresenta le sessioni attive del workflow;
• WORKFLOWSESSIONSFINISHED: rappresenta le sessioni concluse del workflow;
  
  Devi avere il permesso di esportare i dati dal database per eseguire query su questa tabella.
• SIMPLEMESSAGES: rappresenta i messaggi transazionali (inviati o respinti/bounced);
  
  Devi avere il permesso di esportare i dati dal database per eseguire query su questa tabella.
• RECEIVEDMESSAGES: rappresenta i messaggi SMS e EMAIL ricevuti utilizzando il servizio Message Receiver;
  
  Devi avere il permesso di esportare i dati dal database per eseguire query su questa tabella.
• RECEIVEDMESSAGESFOLDERS: rappresenta l'insieme di cartelle per i messaggi ricevuti tramite il servizio Message Receiver;
• CONTENTTAGSTATS: statistiche dei contatti relative ai tag di contenuto;
• NEWSLETTERTAGSTATS: statistiche dei contatti relative ai tag di comunicazione;
• CONVERSIONEVENTS: rappresenta gli eventi di conversione.

Tabella di sistema: CONTACTS

La tabella di sistema contacts rappresenta tutti i tuoi contatti.
C'è una colonna per ogni "campo", come 'email', 'status', 'timezone'....
Colonne speciali:

Tabella di sistema: GROUPS

Rappresenta tutte le audience di contatti (dinamiche e statiche).

Colonne:

Tabella di sistema: STATICGROUPMEMBERS

Rappresenta tutti i membri delle audience statiche di contatti.

Colonne:

Tabella di sistema: CONTENTS

Rappresenta tutti i contenuti.

Colonne:

Tabella di sistema: NEWSLETTERS

Rappresenta tutte le comunicazioni

Colonne:

Tabella di sistema: WEBPAGES

Rappresenta tutte le pagine web (webpages)

Colonne:

Tabella di sistema: SURVEYS

Rappresenta tutti i survey.

Colonne:

Tabella di sistema: SURVEYSESSIONS

Rappresenta tutte le sessioni del survey.

Colonne:

Tabella di sistema: WORKFLOWS

Rappresenta tutti i workflow.

Colonne:

Tabella di sistema: WORKFLOWSESSIONS

Rappresenta tutte le sessioni attive di tutti i workflow.

Colonne:

Tabella di sistema: WORKFLOWSESSIONSFINISHED

Rappresenta tutte le sessioni concluse dei workflow esistenti.

Colonne:

Tabella di sistema: SIMPLEMESSAGES

Rappresenta tutti i messaggi transazionali che sono stati processati.

Colonne:

SELECT idsimplemessage from SIMPLEMESSAGES where status = 'notsent' or status='error'

Tabella di sistema: RECEIVEDMESSAGES

Represents all the received messages

Colonne:

Tabella di sistema: RECEIVEDMESSAGESFOLDERS

Rappresenta tutti i messaggi ricevuti.

Colonne:

Tabella di sistema: CONTENTTAGSTATS

Contact statistics related with content tags

Colonne:

Tabella di sistema: NEWSLETTERTAGSTATS

Statistiche dei contatti correlate con i tag di comunicazione.

Columns:

Tabella di sistema: CONVERSIONEVENTS

Rappresenta tutti gli eventi di conversione.

Colonne: