The xtk:queryDef schema contains generic methods to query the database and retrieve any kind of entites, both out-of-the-box and custom.
This API is not meant for high concurrency. It is fit for a reasonble usage without the limits of the capacity you purchased.
List all accounts
const queryDef = {
schema: "nms:extAccount",
operation: "select",
select: {
node: [
{ expr: "@id" },
{ expr: "@name" }
]
}
};
const query = NLWS.xtkQueryDef.create(queryDef);
console.log(query);
const extAccounts = await query.executeQuery();
console.log(JSON.stringify(extAccounts));
Get a single record
const queryDef = {
schema: "nms:extAccount",
operation: "get",
select: {
node: [
{ expr: "@id" },
{ expr: "@name" },
{ expr: "@label" },
{ expr: "@type" },
{ expr: "@account" },
{ expr: "@password" },
{ expr: "@server" },
{ expr: "@provider" },
]
},
where: {
condition: [
{ expr: "@name='ffda'" }
]
}
}
const query = NLWS.xtkQueryDef.create(queryDef);
const extAccount = await query.executeQuery();
The operation attribute of a query indicates what kind of query operation to perform amongst the following. It is defined in the xtk:queryDef:operation enumeration.
It's common to use variables in query conditions. For instance, in the above example, you'll want to query an account by name instead of using the hardcoded ffda name. The expr attribute takes an XTK expression as a parameter, and ffda is a string literal in an xtk expression.
To prevent xtk ingestions vulnerabilities, you should not concatenate strings and write code such as expr: "@name = '" + name + "'": if the value of the name parameter contains single quotes, your code will not work, but could also cause vulnerabilities.
Find more details about escaping here.
Results can be retrieved in different pages, using the lineCount and startLine attributes. For instance, retrieves profiles 3 and 4 (skip 1 and 2)
const queryDef = {
schema: "nms:recipient",
operation: "select",
lineCount: 2,
startLine: 2,
select: {
node: [
{ expr: "@id" },
{ expr: "@email" }
]
}
}
const query = NLWS.xtkQueryDef.create(queryDef);
const recipients = await query.executeQuery();
console.log(JSON.stringify(recipients));
Campaign will automatically limit the number of row returned by a query to 200. The reason for this limit is that all the data returned by a query is stored in memory in the application server, but is also sent over the network to the SDK or API client, which also stores the data in memory. Storing more than a few hundred rows is generally not a good idea. Using the QueryDef API to handle large amounts of data is not a good idea either, it's better to use workflow instead. Worfklows are made to process large amounts of data, up to hundreds of millions of rows, whereas queries are not meant to handle more than a few hundred rows.
More advanced pagination also need a orderBy clause to ensure that results are consistent. If not using an orderBy clause, the query does not quarantee the ordering of the results, and subsequent calls are not quaranteed to return consistent pages. This example uses the name attribute to sort delivery mappings and returns the first 2 records.
const queryDef = {
schema: "nms:deliveryMapping",
operation: "select",
lineCount: 2,
select: {
node: [
{ expr: "@id" },
{ expr: "@name" }
]
},
orderBy: { node: [
{expr: "@name"}
]}
};
Some Campaign attributes depend on the installed packages. For instance, the mobile package will add attributes to various schemas so that Campaign can handle push notifications. I you need to write generic code that can adapt whether the mobile package is installed or not, you can use the hasPackage function to conditionally add nodes to the query.
if (client.application.hasPackage("nms:mobileApp")) {
queryDef.select.node.push({ expr: "@blackListAndroid" });
}
For some objects, such as deliveries and workflows, it can be painful to have to list all the attributes that we want to retreive, as there could be hundreds. The query provides a mechanism to select all attributes, a bit like a SELECT * would do in SQL. However, it involves an extra API call xtk:queryDef#SelectAll which means an additional round-trip to the server
const queryDef = {
schema: "xtk:option",
operation: "get",
where: {
condition: [
{ expr:`@name='XtkDatabaseId'` }
]
}
}
const query = NLWS.xtkQueryDef.create(queryDef);
await query.selectAll(false);
const databaseId = await query.executeQuery();
Querying all attributes of the xtk:option schema
The queryDef API also lets you generate the SQL for a query, using the BuildQuery.
const sql = await query.buildQuery();
console.log(">> SQL query: " + sql);
The BuildQueryEx methods will also return the SQL and also metadata (data type) about each select field.
const sql = await query.buildQueryEx();
console.log(`>> SQL queryEx: "${sql[0]}"`);
console.log(`>> Format string: "${sql[1]}"`);
The query uses the analyze option to return user friendly names for enumerations. In this example, we use the exclusionType attribute of the target mappings schema. Without the analyze flag, the query will return the numeric value of the attribute (for example 2). With the flag, the query will still return the numeric value, but will also return the string value of the attribute and its label. It will use addition JSON attributes named "exclusionTypeName" and "exclusionTypeLabel", using the "Name" and "Label" suffixes.`,
const queryDef = {
schema: "nms:deliveryMapping",
operation: "get",
select: {
node: [
{ expr: "@id" },
{ expr: "[storage/@exclusionType]", analyze: true },
]
},
where: {
condition: [
{ expr:`@name='mapRecipient'` }
]
}
}
query = NLWS.xtkQueryDef.create(queryDef);
mapping = await query.executeQuery();