In this article we'll give you a run-down of the key things you need to know before you dive into writing DTL.
The chances are you already know a few scripting languages - so you'll know that different languages have a lot of similarities, but some crucial differences too.
Laying out your code
Some languages require a semicolon or other symbol at the end of each statement. DTL doesn't. Just end each statement with a newline.
You can also put newlines or other whitespace within a statement, to help with layout or formatting.
Comments can start with // (and end at the end of the line), or be enclosed with /* ... */(and span several lines).
// This is the first type of comment.
// The "//" needs to go at the start of every line.
This is the second type of comment.
It can span multiple lines.
You can't nest /* ... */ comments inside each other, though a // comment inside a /* ... */ comment is fine.
// This is fine....
/* But this is an error */
Variables and assignments
Variable names always start with a $ symbol. The first character after the $ needs to be a letter. After that you can use letters, digits and underscores. $var, $var1 and $var_2 are all possible variable names.
Variable names are case-sensitive, so $myvar and $MyVar are different variables.
You don't need to declare variables in DTL. Just start using them:
$greeting = "Hello, world!"
As you can see, to assign a value to a variable in DTL, you use a plain equals sign =. (In statements like if $a == $b you use a double equals sign ==, but we'll come back to that later.)
DTL is designed to process a row from a database, so there's a special syntax which makes it easy to access individual columns. Simply write a column's name in `backticks` to get its value for the current row.
For example, if the current database row contains these columns and values:
Then you can use these expressions to access the values:
As you can see, it's OK for the column name to contain spaces. Column names are also case-sensitive.
The backtick syntax lets you read values, but not write them. To change the value of a column you need to use the set transformation, which we'll cover in a later tutorial.
If the column name contains no spaces, then in fact the backticks are optional. In the table above, `Name` and `Gender` could also be written just Name and Gender. For clarity, we use backticks throughout these tutorials, even when they could be left out.
Joining (concatenating) strings
In DTL, you can join (or concatenate) two strings with a + sign. For example:
$greeting = "Hello " + "world!"
Obviously, that's a silly example. More realistically, you might use the + operator together with the backtick syntax we discussed above, to combine the value of one or more columns into a string variable.
$display_age = `Age` + " years"
$full_name = `First name` + " " + `Surname`
There's more on working with strings in a later article.
There are no arithmetic operators in DTL! The + operator only works to join strings, and you can't do -, * or / at all. If you need to convert between measurement systems (miles to km for example), then you need a linear_transform, which we'll cover in a later tutorial.
if ... else statements
if ... else statements work like you expect them to. The syntax is:
[else if test-expression
- You don't need to put parentheses (...) around the test-expression
- The braces aren't optional, even when there's only one statement
Just in case you're wondering, there are no loop commands like for or while in DTL. The only control flow commands are if statements.
Here are DTL's comparison operators - you can use these in the test in an if statement.
You'll notice that we use a function called to_int in many of these examples. We'll explain why we have to do that in the next section.
|==||Strings and numbers||if $answer == "yes"|
|!=||Strings and numbers||if `Title` != "Dr."|
|<||Only numbers||if to_int (`Year`) < 1980|
|<=||Only numbers||if to_int ($a) <= to_int ($b)|
|>||Only numbers||if to_int ($size) > to_int (`Capacity`)|
|>=||Only numbers||if to_int (`Age`) >= 18|
There won't be any surprises here, but do note that:
- it's ==, not =
- it's !=, not <>.
Using to_int in comparisons
You can't compare a string to a number in DTL. For example, "42" == 42 is neither true nor false, it's just an error.
You need to be aware of this when you're using the `backtick` syntax to read a data column. The column's value may be a string, even if it looks like a number. In particular, if you load data into your Bunker from a CSV spreadsheet, every value will be imported as a string.
To get round this problem, use the to_int function to convert the string to an integer. For example, this code extract tests whether the column called Age contains a value strictly less than 18.
if to_int (`Age`) < 18
$child = "yes"
$child = "no"
If you apply to_int to a value which is already an integer, you just get the same integer back. So it's always safe to use this technique, even if you're not sure whether the original column is an integer or a string.
As you might have guessed, there's another built-in function called to_string which goes the other way.
Testing for NULL values
The value of a column, retrieved using the backtick syntax, may be NULL. As in most databases, NULL is a special value which is distinct from the integer zero or the empty string.
DTL offers two special operators to test for NULL values.
|IS NULL||Values from columns||if `Postcode` IS NULL|
|IS NOT NULL||Values from columns||if `Firstname` IS NOT NULL|
The keywords IS NULL and IS NOT NULL aren't case-sensitive - but for consistency, we write them in capitals throughout this documentation.
A variable can also have a NULL value:
$postcode = `Postcode`
// $postcode may now be NULL (if it's possible for the column
// Postcode to contain a NULL in the original database schema).
Comparisons using ==, !=, <, <=, > or >= are always false if either of the values being compared is NULL.
If you don't work with database systems from day to day, you may find that this rule has surprising effects. For example, it means that $a == "hello" and $a != "hello" may both be false, if $a is NULL.
Using AND, OR and NOT
As you'd expect, you can use logical operators to build more complex if tests.
|NOT||if NOT $answer == "yes"|
|AND||if to_int ($age) >= 30 AND to_int ($children) == 0|
|OR||if $firstname == "" OR $surname == ""|
Once again, these operators aren't case sensitive - but for consistency, we write them in capitals throughout this documentation.
Logical operators have the usual precedence: first NOT, then AND, finally OR. To override that, use parentheses:
if $surname != "" AND ($firstname == "" OR $title == "")
Functions and transformations
Finally, let's look at two features which we'll be using a lot in these tutorials: functions and transformations.
Functions and transformations look very similar. You invoke them both with a syntax that will seem familiar from many other languages:
name (parameter, parameter, parameter...)
However, what they do is quite different.
Functions return a value. For example, we've already seen that the to_int takes a string as a parameter, and returns an integer value. You use functions in the middle of expressions, like we did in the example:
if to_int (`Age`) < 18
Transformations don't return a value. Instead, they make a change to the row of data currently being processed (that is, they transform the data).
Because transformations don't return a value, they stand alone. For example:
convert_case ("Name", "uppercase")
This transformation converts the value of the Name column to upper case. We'll cover the details of this in a later tutorial (and for the eagle-eyed, we'll explain why "Name" is written in double-quotes rather than backticks). For now, note that this is a complete statement, which can't be added to or used in a larger expression.
DTL comes with a number of built-in functions and transformations, which we cover in these tutorials. Unlike many languages, you can't define your own functions or transformations within DTL itself.
We'll explain three golden rules for in the input and output of your DTL scripts.