Database » History » Version 6

Version 5 (Jeremy Gow, 2013-02-21 03:51 PM) → Version 6/19 (Jeremy Gow, 2013-02-21 04:15 PM)

h1. Database management

Having [[start|started IDyOM]] and connected During [[Installation]] you should have set up the database that IDyOM will use to store musical data. To recap, every time you run the system you will need to connect to the database, e.g. as follows:
<pre>
(clsql:connect '("DIR/example.db") :if-exists :old :database-type :sqlite3)
</pre>
The first time you connect to
a new database, you need to setup the database tables before you can examine read and edit its contents. import any data:
<pre>
CL-USER> (mtp-admin:initialise-database)
</pre>


{{>toc}}

h2. Describing the contents of the database

A quick way of listing the database contents is:

<pre>
CL-USER> (mtp-admin:list-contents)
0 Songs and ballads from Nova Scotia, Canada.
1 Chorale melodies harmonised by J.S. Bach.
2 Chorale soprano melodies harmonised by J.S. Bach.
3 Alsatian folk songs from the Essen Folk Song Collection.
...
</pre>

Datasets can be examine in more detail using <code>describe-database</code> and <code>describe-dataset</code>:
<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>

h2. Importing data 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 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.