# Database support

Liquidsoap supports SQL databases through the sqlite library. If you build Liquidsoap by yourself, you should install the SQLite3-OCaml library, e.g. with `opam install sqlite3`.

In order to create or open a database, you should use the `sqlite` function, which takes as argument the file where the database is stored and returns an object whose methods can be used to modify or query the database:

table in the database can then be created by calling the `table.create` method on the object with as arguments the table name (labeled by `table`) and the list of columns specified by pairs consisting of the column name, and its type. Setting the `preserve` argument to `true` allows not creating the table if one already exists under this name. In our example, we want to use our database to store metadata for files so that we create a table named `"metadata"` with columns corresponding to the artist, title, etc.:

Inserting a row is then performed using the `insert` method, which takes as argument the table and a record containing the data for the row:

Since the field `filename` is a primary key, it has to be unique (two rows cannot have the same file name), so that inserting two files with the same filename in the database will result in an error. If we want that the second insertion replace the first one, we can pass the `replace=true` argument to the `insert` function.

We can query the database with the `select` method. For instance, to obtain all the files whose year is posterior to 2000, we can write

In the case where you want to use strings in your queries, you should always use `sqlite.escape` to properly escape it and avoid injections:

The `select` function, returns a list of rows. To each row will correspond a list of pairs strings consisting of

- a string: the name of the column,
- a nullable string: its value (this is nullable because the contents of a column can be NULL in databases).

We could thus extract the filenames from the above queries and use those in order to build a playlist as follows:

This can be read as follows: for each row (by `list.map`), we convert the row to a list of pairs of strings as described above (by calling the `to_list` method), we replace take the field labeled `"filename"` (by `list.assoc`) and take its value, assuming that it is not null (by `null.get`).

Since manipulating rows as lists of pairs of strings is not convenient, Liquidsoap offers the possibility to represent them as records with constructions of the form

    let sqlite.row (r : {a : string; b : int}) = row

which instructs to parse the row `row` as a record `r` with fields `a` and `b` of respective types `string` and `int`. The above filename extraction is thus more conveniently written as

Other useful methods include

- `count` to count the number of rows satisfying a condition

- `delete` to delete rows from a table

- `table.drop` to delete tables from the database

- `exec` to execute an arbitrary SQL query which does not return anything:

- `query` to execute an arbitrary SQL query returning rows

Finally, if your aim is to index file metadata, you might be interested in the `medialib.sqlite` operator which is implemented in the standard library as described above (see the cookbook).