IDyOM Project

h1. Database management

During [[Installation]] h2. Loading the system and connecting to the database

<pre>
CL-USER> (asdf:oos 'asdf:load-op 'mtp-admin)
...
</pre>

Then you should have set up need to connect to the SQL database that IDyOM will use to store musical data. To recap, every time you run have created. You do this using <code>clsql:connect</code> which is documented "here":http://clsql.b9.com/manual/connect.html. The function <code>mtp-admin:connect-to-database</code> in the system you will need file "music-data.lisp":https://code.soundsoftware.ac.uk/projects/mtp-admin/repository/entry/music-data.lisp gives an example of how to connect to the database, e.g. as follows:
an SQLite database.

<pre>
(clsql:connect '("DIR/example.db") :if-exists :old :database-type :sqlite3) CL-USER&gt; (mtp-admin:connect-to-database)
...
</pre>


h2. Initializing a new database

The first time you connect to a new newly created database, you need to setup the database tables before you can import any data:
data. You do this as follows:

<pre>
CL-USER> (mtp-admin:initialise-database)
</pre>

h2. Describing the contents of the database

<pre>

CL-USER> (mtp-admin:describe-database)

Songs and ballads from Nova Scotia, Canada.
-----------------------------------------------------------------------------
ID No. Compositions No. events Mean events/composition
0 152 8552 56.263157
-----------------------------------------------------------------------------

Chorale melodies harmonised by J.S. Bach.
-----------------------------------------------------------------------------
ID No. Compositions No. events Mean events/composition
1 185 9227 49.875675
-----------------------------------------------------------------------------

Chorale soprano melodies harmonised by J.S. Bach.
-----------------------------------------------------------------------------
ID No. Compositions No. events Mean events/composition
2 100 4693 46.93
-----------------------------------------------------------------------------

Alsatian folk songs from the Essen Folk Song Collection.
-----------------------------------------------------------------------------
ID No. Compositions No. events Mean events/composition
3 91 4496 49.406593
-----------------------------------------------------------------------------

...

Pop melodies used in a segmentation experiment by Daniel Muellensiefen.
-----------------------------------------------------------------------------
ID No. Compositions No. events Mean events/composition
29 15 1265 84.333336
-----------------------------------------------------------------------------

Hymn melodies from Hymns Ancient and Modern.
-----------------------------------------------------------------------------
ID No. Compositions No. events Mean events/composition
30 120 4280 35.666668
-----------------------------------------------------------------------------
(0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30)
</pre>

<pre>
CL-USER> (mtp-admin:describe-dataset 0 :verbose t)

Songs and ballads from Nova Scotia, Canada.
-----------------------------------------------------------------------------
ID No. Compositions No. events Mean events/composition
0 152 8552 56.263157
-----------------------------------------------------------------------------
CPITCH (26): (54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
72 73 74 75 76 77 79 81)
MPITCH (17): (31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47)
ACCIDENTAL (3): (-1 0 1)
DUR (14): (3 4 6 8 12 16 18 24 36 48 60 72 96 120)
DELTAST (7): (0 12 24 36 48 60 72)
BIOI (17): (1 3 4 6 8 12 16 18 24 30 36 48 60 72 84 96 120)
KEYSIG (9): (-4 -3 -2 -1 0 1 2 3 4)
MODE (2): (0 9)
BARLENGTH (7): (36 48 72 96 120 144 168)
PULSES (6): (2 3 4 5 6 7)
PHRASE (3): (-1 0 1)
DYN (1): (NIL)
TEMPO (19): (52 54 60 64 76 80 82 84 92 96 100 108 110 112 120 132
144 152 172)
VOICE (1): (1)
-----------------------------------------------------------------------------NIL
CL-USER>

</pre>

A quick way of listing the database contents is:

<pre>
(clsql:enable-sql-reader-syntax)
(clsql:print-query [select [dataset-id][description] :from [mtp-dataset] :order-by [dataset-id]])
...
</pre>

h2. Importing Data

Before you can do this you will need to import some data. Methods exist for **kern files and midi files.

<pre>
CL-USER> (mtp-admin:import-data :krn "/home/mas01mtp/research/data/kern/shanxi237/" "A test dataset" 31)
Inserting data into database: dataset 31.
NIL
CL-USER> (mtp-admin:describe-dataset 31)

A test dataset
-----------------------------------------------------------------------------
ID No. Compositions No. events Mean events/composition
31 237 11056 46.649788
-----------------------------------------------------------------------------NIL
CL-USER>
</pre>

<pre>
CL-USER> (mtp-admin:import-data :mid "/home/marcusp/research/data/midi/Witchcraft/" "Melodies used in de Noojier et al. (2008)" 32)

Inserting data into database: dataset 32.NIL
CL-USER> (mtp-admin:describe-dataset 32)

Melodies used in de Noojier et al. (2008)
-----------------------------------------------------------------------------
ID No. Compositions No. events Mean events/composition
32 10 313 31.3
-----------------------------------------------------------------------------
</pre>

h2. Exporting Data

<pre>
CL-USER> (mtp-admin:export-data (mtp-admin:get-composition 31 0) :mid "/tmp/")
NIL
</pre>

Produces a midi file.

<pre>
CL-USER> (mtp-admin:export-data (mtp-admin:get-composition 31 0) :lisp "/tmp/")
NIL
</pre>

Produces a lisp readable representation of the dataset.

<pre>
CL-USER> (mtp-admin:export-data (mtp-admin:get-composition 31 0) :ly "/tmp/")
NIL
</pre>

Produces a lilypond file.

<pre>
CL-USER> (mtp-admin:export-data (mtp-admin:get-composition 31 0) :pdf "/tmp/")
NIL
</pre>

Produces a pdf score (via lilypond).

h2. Deleting a dataset

<pre>
CL-USER> (mtp-admin:delete-dataset 735)
</pre>

deletes dataset 735 from the database.