---
title: "Querying an EMU-SDMS database"
output: html_document
---

Initially please make sure the `course_data_dir` and `course_data_url` variables are set:

```{r,echo=T}
course_data_dir = "./myEMURdata" # change to valid dir path on your system
course_data_url = "http://www.phonetik.uni-muenchen.de/~jmh/lehre/Rdf"
```

```{r, echo=F}
# clean up 
dir = file.path(course_data_dir, "emuR_demoData")
if(dir.exists(dir)){
  res = unlink(dir, recursive = T, force = T)
}
```

We will use a demo emuDB in this lecture that comes with the `emuR` package; we will 'create' this database by using the function `create_emuRdemoData`; the data base will be saved at `course_data_dir`:

```{r, echo=TRUE,results="hide",message=FALSE}
# load packages
library(emuR)
library(tidyverse)
```

```{r, echo=TRUE,results="hide",message=FALSE}
# create demo data in directory
create_emuRdemoData(dir = course_data_dir)
# create path to demo database
path2ae = file.path(course_data_dir, "emuR_demoData", "ae_emuDB")
# load database
ae = load_emuDB(path2ae, verbose = F)
# get summary of loaded emuDB
summary(ae)
```

In the `level definitions`, we see one `EVENT` level ("Tone", one point in time), one `SEGMENT` level ("Phonetic", with start and end times), and several `ITEM` levels, e.g. "Syllabe" or "Word", which inherit time information from the level "Phonetic". In the `link definitions` summary section, we can see a very rich annotation structure, which results in the following tree-like structure for the first utterance (note that only a single path through the hierarchy is shown):

```{r, rexample:tutorial_serve, eval=FALSE}
serve(ae)
```

![Figure 1: Hierarchy of the first utterance of the database *ae*](pics/annot_struct.png)

We can also see so-called `SSFF track definitions`, which means in this case that - amongst other things - pre-calculated formants are available.

It is worth noting that all seven utterances were read by the same speaker, so there will be no concerns about things like vowel normalisation. The male is a speaker of Australian English (therefore the database's name `ae`).

## Example of an analysis

We will now present a little example of how such a database could be analysed. To do so, we will use the function `query()` to query certain segments, `get_trackdata()` and other functions to read formants into `R`, and `requery_hier()` for further re-analysis.

First of all, we want to plot the edges of the Australian English vowel space. To do so, we will query back and front closed, mid, and open vowels.

```{r,results='hide',message=FALSE}
# query A and V(front and back open vowels),
# i: and u: (front and back closed vowels), and
# E and o: (front and back mid vowels)
ae_vowels = query(emuDBhandle = ae,
                  query = "[Phonetic== V|A|i:|u:|o:|E]")
# get the formants that belong to the queried segments:
ae_formants = get_trackdata(ae, 
                            seglist = ae_vowels,
                            ssffTrackName = "fm")
# get the formants at the vowels' temporal midpoints:
ae_formants_norm = normalize_length(ae_formants)
ae_midpoints = ae_formants_norm %>% 
  filter(times_norm==0.5)
# plot the vowel space:
ggplot(ae_midpoints) +
  aes(x = T2, y = T1, label = labels, col = labels) +
  geom_text() +
  scale_y_reverse() + scale_x_reverse() +
  labs(x = "F2 (Hz)", y = "F1 (Hz)") +
  theme(legend.position = "none")
```

This figure shows a vowel space as one would expect it: open vowels are near the bottom,
closed vowels are on the top, mid vowels in the mid. Front vowels are on the left side of the plot, and back vowels are on the right-hand side. However, there is an exception: only one out of four /u:/s is actually really back, the other three are extremely fronted.

In order to re-inspect the data, we will now concentrate on /u:/:

```{r,results='hide',message=FALSE}
ggplot(ae_midpoints %>% 
         filter(labels == "u:")) +
  aes(x = T2, y = T1, label = labels, col = labels) +
  geom_text() +
  scale_y_reverse() + scale_x_reverse() +
  labs(x = "F2 (Hz)", y = "F1 (Hz)") +
  theme(legend.position = "none")
```

In order to find out why three out of four /u:/ are so front, we should find out the words; this can be done by examining to which words the four /u:/s were linked (by means of `requery_hier()`):

```{r,results='hide',message=FALSE}
ae_midpoints$Word = requery_hier(ae, 
                                 seglist = ae_vowels, 
                                 level = "Text")$labels
ggplot(ae_midpoints %>% filter(labels == "u:")) +
  aes(x = T2, y = T1, label = Word, col = labels) +
  geom_text() +
  scale_y_reverse() + scale_x_reverse() +
  labs(x = "F2 (Hz)", y = "F1 (Hz)") +
  theme(legend.position="none")
```

As we can see, the back /u:/ comes from the word "to", whereas the front vowels are linked to the words "new", "beautiful", and "futile". All three words have in common that /u:/ should be preceded by /j/. This could cause the fronting of /u:/.

However, we should test whether our assumption is true. We will now query the sequences of the preceding consonant and /u:/, and analyse these sequences' F2 trajectories:

```{r,results='hide',message=FALSE}
Cu = query(emuDBhandle = ae,
           query = "[Phonetic =~ .* -> Phonetic == u:]")
Cu_formants = get_trackdata(ae, 
                            seglist = Cu,
                            ssffTrackName = "fm")
ggplot(Cu_formants) +
  aes(x = times_rel, y = T2, col = labels, group = sl_rowIdx) +
  geom_line() +
  labs(x = "Duration (ms)", y = "F2 (Hz)")
```

In the word "to", the preceding segment is labelled "H", i.e. the aspiration of /t/.
You can clearly see in the plot that the F2 trajectory is coming from a relatively high F2 locus, however, this locus is still much lower than F2 in /j/ (which is, of course, very similar to F2 in an /i:/ vowel). Therefore, we can conclude that the preceding /j/ is causing /u:/ to front in that context.

This little analysis was very dependent on several different kinds of queries and re-queries, and we would like to introduce you to the main concepts of these functions:


## Simple queries with `query()`

We will start with very basic queries. The function for conducting queries is simply called `query`; this functions needs at least two arguments, `emuDBhandle` and `query`, e.g.:

```{r}
V = query(emuDBhandle = ae,
          query = "[Phonetic == V]")
```


The expression `["Phonetic == V"]` is a legal expression in the `EMU Query Language (EQL)` (details see below) and could be translated into **"which labels in the level *Phonetic* are equal to the label 'V'"** (and 'V' is the [*SAMPA for English*](https://www.phon.ucl.ac.uk/home/sampa/english.htm) equivalent to *IPA* /ʌ/, i.e. the vowel in words like <*cut*>).

### Results of `query()`: segment lists

https://www.phonetik.uni-muenchen.de/~jmh/lehre/sem/ws1819/emuR/LESSON3/pics/annot_struct.png An emuR segment list is a list of segment descriptors. Each segment descriptor describes a sequence of annotation elements. The list is usually a result of an emuDB query using the `query` function like in the present example. `query` has found three tokens of [V]:

```{r}
V
```

As of emuR version 2.0.0 this object of the type tibble with one row per segment descriptor:

**Data frame columns**

+ *labels*: labels or sequenced labels of segments concatenated by '->'

+ *start*: onset time in milliseconds

+ *end*: offset time in milliseconds

+ *db_uuid*: UUID of emuDB (= a unique identifier)

+ *session*: session name

+ *bundle*: bundle name (= utterance name)

+ *start_item_id*: item ID of first element of sequence

+ *end_item_id*: item ID of last element of sequence

+ *level*: name of the level that has been searched

+ *attribute*: name of attribute that has been searched

+ *start_item_seq_idx*: sequence index of start item

+ *end_item_seq_idx*: sequence index of end item

+ *type*: type of "segment" row: `ITEM`: symbolic item, `EVENT`: event item, `SEGMENT`: segment

+ *sample_start*: start sample position

+ *sample_end*: end sample position

+ *sample_rate*: sample rate


This makes it easy to access certain informations, e.g.
```{r}
# get labels:
V$labels

# get start times:
V$start

# get end times:
V$end

# calculate durations of the [V]s
V$end - V$start
```

### Inherited times

What happens, if we were looking for a timeless `ITEM`?

```{r}
# Phonetic of type SEGMENT, Phoneme of type ITEM
list_levelDefinitions(ae)
V_phoneme = query(emuDBhandle = ae, 
                  query = "[Phoneme == V]")
V_phoneme
V
```

As you can see, `V` and `V_phoneme` both present times, although `Phoneme` is a timeless `ITEM` level. Times are inheritet from the `SEGMENT` level `Phonetic`. This, of course, will only work if Phoneme and Phonetic levels are linked (and they are linked, see also Figure 1):

```{r}
list_linkDefinitions(ae)
```

If the `ITEM` we are interested in was linked to several time-aligned segments, we would have to use `query`'s parameter `timeRefSegmentLevel` to choose the segment level from which `query` derives time information. However, this is not the case here.

The calculation of inherited times can be time-consuming. In many cases, we may not be interested in time information, but only in the labels; we therefore can turn off the calculation of inherited times with an additional parameter: `calcTimes = FALSE`:

```{r}
# Phonetic of type SEGEMNT, Phoneme of type ITEM
list_levelDefinitions(ae)
V_phoneme2 = query(emuDBhandle = ae,
                   query = "[Phoneme == V]",
                   calcTimes = FALSE)
V_phoneme2
```

In this case, all entries in `start` and `end` are `NA` (== `N`ot `A`vailable).

### `requery_hier()` and `requery_seq()`

#### Relation types

There are two (self-explaining) types of relations in the EMU-SDMS:

+ **dominance**

+ **sequence**

##### Dominance

By which words are the "V"s dominated?
We could find out by a **hierarchical** re-query:

```{r}
#find all "V"-labels in `ae`
V = query(emuDBhandle = ae,
          query = "[Phonetic == V]")
```

Now put this segment list into requery_hier() and look for the linked `ITEM` in level `Word`, attribute `Text`:

```{r}
V_Text = requery_hier(emuDBhandle = ae,
                      seglist = V,
                      level = "Text")
```

Your result will be the `ITEM` labels and calculated times (for the corresponding words).

##### Sequence

You could also wish to know what "V"s sequential contexts are, e.g. the subsequent segments. We use the sequential structure of the database, and the command `requery_seq()`, with `offset = 1` (`offset = -1` would find the sound the precedes 'V'):

```{r}
requery_seq(emuDBhandle = ae,
            seglist = V,
            offset = 1)
```

We will discuss both commands more extensively later in the seminar, but wanted to show that it is possible to use the annotation structure and a given segment list to retrieve additional information afterwards. We could use both commands to express more complex queries: e.g. we could look for all "V" within the word "amongst" by querying "V", then requery all linked words, and then deletin all "V" that are not linked to "amongst". However, this would be rather cumbersome. A much easier way to conduct more complicated queries is the use of all possibilities of emuR's query language `EQL` within the command `query`. However, before we can use more **complex queries**, we will have to learn the Emu Query Language.

## The **E**mu **Q**uery **L**anguage `EQL`

To learn more about the functionality of the `EQL`, you can always see the following manual chapter: https://ips-lmu.github.io/The-EMU-SDMS-Manual/chap-querysys.html

As we have seen above, any query must be placed within `" "`, and any query *can* be placed within `[ ]`. You minimally have to give a level, and some sort of representation for a label (this may be a *regular expression*), unless you do not use one of the `position` and `count` functions (see below).

### Single argument queries

#### Equality/inequality/matching/non-matching

##### Equality

In the examples above, we had looked for the equality of the labels to "V" on the level "Phonetic" (in the database `ae`):

```{r}
query(emuDBhandle = ae, 
      query = "Phonetic == V")
```

So "==" is the equality operator. For backward compatibility with earlier versions of emuR, a single "=" is also allowed (but we ask you to prefer "==" instead):

```{r}
query(emuDBhandle = ae, 
      query = "Phonetic == V")
```

##### Inequality

We can also search everything *except* "V" by the use of `!=`

```{r}
query(emuDBhandle = ae, 
      query = "Phonetic != V")
```

So one way to get 'everything' would be to query something that is probably not in your database, like "xyz". However, there is a much better way: Using so-called [*regular expressions*](https://medium.com/factory-mind/regex-tutorial-a-simple-cheatsheet-by-examples-649dc1c3f285). To use these, you have to type "=~", followed by the regular expression, in this case `.*` (meaning: any character (`.`) zero or more times (`*`) ). Although not the focus of this seminar (this example will probably be the only one we will use), knowing the basics of regular expressions can be a very useful tool and it is advisable that you familiarize yourself with them:

```{r}
everything1 = query(emuDBhandle = ae, 
                    query = "Phonetic != xyz")
everything2 = query(emuDBhandle = ae, 
                    query = "Phonetic =~ .*")
any(everything1 != everything2) # should result in FALSE if both are equal everywhere
```

You can also negate the latter operator by "!~". An example would be:

```{r, eval=TRUE}
# What is the query to retrieve all ITEMs in the “Text” level that don’t begin with ‘a’?
query(emuDBhandle = ae, query = "Text !~ a.*")
```

So, there are four similar operators, two for equality matching, and two for inequalitiy:

| Symbol | Meaning |
| :------------------ | :--------------------------------------------------------------------------------------|
|`==`|	equality|
|`=~`|	regular expression matching|
|`!=`|	inequality|
|`!~`|	regular expression non-matching|

##### The `OR` operator

Use `|` to look for one label and another one(s), e.g. 'm' or 'n' can be retrieved via:

```{r}
query(emuDBhandle = ae, 
      query = "Phonetic == m | n")
```

You can expand this as well:

```{r}
query(emuDBhandle = ae, 
            query = "Phonetic == m | n | N")
```

This functionality can also be achieved using regular expressions.

### Complex queries

#### Sequencial and dominance queries

##### Bracketing

In all hierarchical queries, bracketing with `[ ]` is required to structure your query. In simple queries, however, brackets are optional.

```{r, error=FALSE}
mnN = query(emuDBhandle = ae, 
            query = "[Phonetic == m | n | N]")
```

However, this sequential query will fail, because of missing brackets:

```{r, eval = FALSE}
query(ae, "Phonetic == V -> Phonetic == m")
```

`"[Phonetic == V -> Phonetic == m]"` would be the correct EQL expression in this case.

##### Sequential queries

Use the `->` operator to find sequences of segments:

```{r, eval = TRUE}
query(ae, "[Phonetic == V -> Phonetic == m]")
```

Note: all row entries in the resulting segment list have the start time of "V", the end time of "m" and their labels will be "V->m". You can change this with the so-called `result modifier` hash tag "#":

```{r, eval = TRUE}
query(ae, "[#Phonetic == V -> Phonetic == m]") # finds V, if V is followed by m
query(ae, "[Phonetic == V -> #Phonetic == m]") # finds m, if m is preceded by V
```

Keep in mind that only one hash tag per query is allowed.

You can search sequences of sequences, however, you have to use bracketing; otherwise, you get an error like in

```{r, eval=FALSE}
query(ae, "[Phonetic == @ -> Phonetic == n  -> Phonetic == s]")
```

The correct code would be:

```{r}
query(ae, "[[Phonetic == @ -> Phonetic == n ] -> Phonetic == s]")
```

A much more complex example would be:

```{r}
## What is the query to retrieve all sequences of ITEMs containing labels “offer” followed by two arbitrary labels followed by “resistance”?
query(ae, "[[[Text == offer -> Text =~ .*] -> Text =~ .* ] -> Text == resistance]")
```

##### Domination queries

Use the operator `^` for all queries, in which two linked levels are involved; e.g.

```{r}
list_linkDefinitions(ae)

## What is the query to retrieve all ITEMs containing the label “p” in the “Phoneme” level that occur in strong syllables (i.e. dominated by / linked to ITEMs of the level “Syllable” that contain the label “S” (== STRONG, as opposed to "W" == WEAK))?
query(ae, "[Phoneme == p ^ Syllable == S]")
```

However, the operator is *not* directional; although "Syllable" dominates "Phoneme", you could have asked

```{r}
query(ae, "[Syllable == S ^ #Phoneme == p]")
```

So, "^" should not be translated with "is dominated by", but rather into "is linked to". However, you have to use the hash tag in order to get labels and times of the Phoneme level here. You can leave out the hash tag if the level you are interested in is the first one in your question.

You can query multiple dominations, however, like in the sequencing case, you have to use brackets:

```{r}
# What is the query to retrieve all ITEMs on the “Phonetic” level that are part of a strong syllable (labeled “S”) and belong to the words “amongst” or “beautiful”?
query(ae, "[[Phonetic =~ .* ^ Syllable == S] ^ Text == amongst | beautiful]")
```

```{r, eval=FALSE}
# same as
query(ae, "[[#Phonetic =~ .* ^ Syllable == S] ^ Text == amongst | beautiful]")
```

```{r}
## to get the "Text"-items instead, use
query(ae, "[[Phonetic =~ .* ^ Syllable == S] ^ #Text == amongst | beautiful]")
```




##### Functions

The are three **position** functions and one **count** function. As the latter function results in a number, queries involve a comparison with a number (by using one of "==", "!=", ">", ">=", "<", "<=", see below); The result of the position functions is logical; we therefore ask, whether a certain condition is `TRUE` or `FALSE`.

##### Position functions

There are three position functions, `Start()`, `Medial()`, and `End()`. Example queries are:

```{r}
## What is the query to retrieve all word-initial syllables?
## (NB: syllable labels are either "W" or "S")
query(ae, "[Start(Word, Syllable) == TRUE]")
```

Examples for Medial() and End() are:

```{r}
## What is the query to retrieve all word-medial syllables?
query(ae, "[Medial(Word, Syllable) == TRUE]")
## What is the query to retrieve all word-final syllables?
query(ae, "[End(Word, Syllable) == TRUE]")
```

```{r}
## What is the query to retrieve all word-final syllables?
head(query(ae, "[End(Word, Syllable) == TRUE]"))
```

Everything not being first or last element is medial:

```{r}
query(ae, "[Medial(Word, Phoneme) == TRUE]")
```

```{r}
head(query(ae, "[Medial(Word, Phoneme) == TRUE]"))
```
###### Count function

The count function's name is `Num()`. `Num(x,y)` counts how many y are in x. You can therefore ask things like the following:

```{r}
## What is the query to retrieve all words that contain two syllables?
query(ae, "[Num(Text, Syllable) == 2]")
## What is the query to retrieve all syllables that contain more than four phonemes?
query(ae, "[Num(Syllable, Phoneme) > 4]")
```

###### Conjunction

You can use `&` to search within several attribute definitions on the same level. For example, the level Word in `ae` has several attribute definitions

```{r}
list_attributeDefinitions(ae, level = "Word")
```

We could, therefore, look for all accented ("S") words 

```{r}
query(ae, "[Text =~.* & Accent == S]")
```

Another usage of "&" is to combine a basic query with a function, e.g.

```{r}
## What is the query to retrieve all non-word-final “S” syllables?
query(ae, "[[Syllable == S  &  End(Word, Syllable) == FALSE] ^ #Text=~.*]")
```


```{r,echo=F}
# clean up 
DBI::dbDisconnect(ae$connection)
ae = NULL
dir = file.path(course_data_dir, "emuR_demoData")
if(dir.exists(dir)){
  unlink(dir, recursive = T)
}
```

