Database » History » Version 3

« Previous - Version 3/19 (diff) - Next » - Current version
Jeremy Gow, 2013-02-21 03:32 PM


Database management

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:

(clsql:connect '("DIR/example.db") :if-exists :old :database-type :sqlite3)

The first time you connect to a new database, you need to setup the database tables before you can import any data:
CL-USER> (mtp-admin:initialise-database)

Describing the contents of the database


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

A quick way of listing the database contents is:

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

Importing Data

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

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

Exporting Data

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

Produces a midi file.

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

Produces a lisp readable representation of the dataset.

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

Produces a lilypond file.

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

Produces a pdf score (via lilypond).

Deleting a dataset

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

deletes dataset 735 from the database.