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 conditiondelete
to delete rows from a tabletable.drop
to delete tables from the databaseexec
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).