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:

db = sqlite("/tmp/database.sql")

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.:

db.table.create(
  "metadata",
  preserve=true,
  [
    (
      "filename",
      "STRING PRIMARY KEY"
    ),
    ("artist", "STRING"),
    ("title", "STRING"),
    ("year", "INT")
  ]
)

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:

db.insert(
  table="metadata",
  {
    artist="Naps",
    title=
      "Best life",
    year=2021,
    filename="naps.mp3"
  }
)
db.insert(
  table="metadata",
  {
    artist="Orelsan",
    title=
      "L'odeur de l'essence",
    year=2021,
    filename="orelsan.mp3"
  }
)

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

l =
  db.select(
    table="metadata",
    where=
      "year >= 2000"
  )

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:

find_artist = "Brassens"
l' =
  db.select(
    table="metadata",
    where=
      "artist = #{sqlite.escape(find_artist)}"
  )

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:

files =
  list.map(fun (row) -> null.get(list.assoc("filename", row.to_list())), l)
s = playlist.list(files)
output(s)

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

def f(row) =
  let sqlite.row (r :
    {filename: string, artist: string, title: string, year: int}
  ) = row
  r.filename
end
files = list.map(f, l)
s = playlist.list(files)
output(s)

Other useful methods include

count to count the number of rows satisfying a condition
```
n = db.count(table="metadata", where="year=2023")
```

delete to delete rows from a table

files =
  list.map(fun (row) -> null.get(list.assoc("filename", row.to_list())), l)
s = playlist.list(files)
output(s)

table.drop to delete tables from the database
```
db.table.drop("metadata")
```
exec to execute an arbitrary SQL query which does not return anything:
```
db.exec(
  "DROP TABLE IF EXISTS metadata"
)
```

query to execute an arbitrary SQL query returning rows

l'' =
  db.query(
    "SELECT * FROM metadata WHERE artist = 'bla'"
  )

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).