Database » History » Version 4

Jeremy Gow, 2013-02-21 03:50 PM

1 2 Jeremy Gow
h1. Database management
2 1 Jeremy Gow
3 3 Jeremy Gow
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:
4 1 Jeremy Gow
<pre>
5 3 Jeremy Gow
(clsql:connect '("DIR/example.db") :if-exists :old :database-type :sqlite3)
6 1 Jeremy Gow
</pre>
7 3 Jeremy Gow
The first time you connect to a new database, you need to setup the database tables before you can import any data:
8 1 Jeremy Gow
<pre>
9 1 Jeremy Gow
CL-USER> (mtp-admin:initialise-database)
10 1 Jeremy Gow
</pre>
11 1 Jeremy Gow
12 1 Jeremy Gow
h2. Describing the contents of the database  
13 1 Jeremy Gow
14 4 Jeremy Gow
A quick way of listing the database contents is:
15 4 Jeremy Gow
16 1 Jeremy Gow
<pre>
17 4 Jeremy Gow
CL-USER> (mtp-admin:list-contents)
18 4 Jeremy Gow
0    Songs and ballads from Nova Scotia, Canada.                                                             
19 4 Jeremy Gow
1    Chorale melodies harmonised by J.S. Bach.                                                               
20 4 Jeremy Gow
2    Chorale soprano melodies harmonised by J.S. Bach.                                                       
21 4 Jeremy Gow
3    Alsatian folk songs from the Essen Folk Song Collection.                                                
22 4 Jeremy Gow
...
23 4 Jeremy Gow
</pre>
24 1 Jeremy Gow
25 4 Jeremy Gow
Datasets can be examine in more detail using <code>describe-database</code> and <code>describe-dataset</code>:
26 4 Jeremy Gow
<pre>
27 1 Jeremy Gow
CL-USER> (mtp-admin:describe-database)
28 1 Jeremy Gow
29 1 Jeremy Gow
 Songs and ballads from Nova Scotia, Canada.
30 1 Jeremy Gow
 -----------------------------------------------------------------------------
31 1 Jeremy Gow
 ID         No. Compositions     No. events           Mean events/composition
32 1 Jeremy Gow
 0          152                  8552                 56.263157           
33 1 Jeremy Gow
 -----------------------------------------------------------------------------
34 1 Jeremy Gow
35 1 Jeremy Gow
 Chorale melodies harmonised by J.S. Bach.
36 1 Jeremy Gow
 -----------------------------------------------------------------------------
37 1 Jeremy Gow
 ID         No. Compositions     No. events           Mean events/composition
38 1 Jeremy Gow
 1          185                  9227                 49.875675           
39 1 Jeremy Gow
 -----------------------------------------------------------------------------
40 1 Jeremy Gow
41 1 Jeremy Gow
 Chorale soprano melodies harmonised by J.S. Bach.
42 1 Jeremy Gow
 -----------------------------------------------------------------------------
43 1 Jeremy Gow
 ID         No. Compositions     No. events           Mean events/composition
44 1 Jeremy Gow
 2          100                  4693                 46.93               
45 1 Jeremy Gow
 -----------------------------------------------------------------------------
46 1 Jeremy Gow
47 1 Jeremy Gow
 Alsatian folk songs from the Essen Folk Song Collection.
48 1 Jeremy Gow
 -----------------------------------------------------------------------------
49 1 Jeremy Gow
 ID         No. Compositions     No. events           Mean events/composition
50 1 Jeremy Gow
 3          91                   4496                 49.406593           
51 1 Jeremy Gow
 -----------------------------------------------------------------------------
52 1 Jeremy Gow
53 1 Jeremy Gow
... 
54 1 Jeremy Gow
55 1 Jeremy Gow
 Pop melodies used in a segmentation experiment by Daniel Muellensiefen.
56 1 Jeremy Gow
 -----------------------------------------------------------------------------
57 1 Jeremy Gow
 ID         No. Compositions     No. events           Mean events/composition
58 1 Jeremy Gow
 29         15                   1265                 84.333336           
59 1 Jeremy Gow
 -----------------------------------------------------------------------------
60 1 Jeremy Gow
61 1 Jeremy Gow
 Hymn melodies from Hymns Ancient and Modern.
62 1 Jeremy Gow
 -----------------------------------------------------------------------------
63 1 Jeremy Gow
 ID         No. Compositions     No. events           Mean events/composition
64 1 Jeremy Gow
 30         120                  4280                 35.666668           
65 1 Jeremy Gow
 -----------------------------------------------------------------------------
66 1 Jeremy Gow
(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)
67 1 Jeremy Gow
</pre> 
68 1 Jeremy Gow
69 1 Jeremy Gow
<pre> 
70 1 Jeremy Gow
CL-USER> (mtp-admin:describe-dataset 0 :verbose t)
71 1 Jeremy Gow
72 1 Jeremy Gow
 Songs and ballads from Nova Scotia, Canada.
73 1 Jeremy Gow
 -----------------------------------------------------------------------------
74 1 Jeremy Gow
 ID         No. Compositions     No. events           Mean events/composition
75 1 Jeremy Gow
 0          152                  8552                 56.263157           
76 1 Jeremy Gow
 -----------------------------------------------------------------------------
77 1 Jeremy Gow
 CPITCH (26):     (54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
78 1 Jeremy Gow
                   72 73 74 75 76 77 79 81)
79 1 Jeremy Gow
 MPITCH (17):     (31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47)
80 1 Jeremy Gow
 ACCIDENTAL (3):  (-1 0 1)
81 1 Jeremy Gow
 DUR (14):        (3 4 6 8 12 16 18 24 36 48 60 72 96 120)
82 1 Jeremy Gow
 DELTAST (7):     (0 12 24 36 48 60 72)
83 1 Jeremy Gow
 BIOI (17):       (1 3 4 6 8 12 16 18 24 30 36 48 60 72 84 96 120)
84 1 Jeremy Gow
 KEYSIG (9):      (-4 -3 -2 -1 0 1 2 3 4)
85 1 Jeremy Gow
 MODE (2):        (0 9)
86 1 Jeremy Gow
 BARLENGTH (7):   (36 48 72 96 120 144 168)
87 1 Jeremy Gow
 PULSES (6):      (2 3 4 5 6 7)
88 1 Jeremy Gow
 PHRASE (3):      (-1 0 1)
89 1 Jeremy Gow
 DYN (1):         (NIL)
90 1 Jeremy Gow
 TEMPO (19):      (52 54 60 64 76 80 82 84 92 96 100 108 110 112 120 132
91 1 Jeremy Gow
                   144 152 172)
92 1 Jeremy Gow
 VOICE (1):       (1)
93 1 Jeremy Gow
 -----------------------------------------------------------------------------NIL
94 1 Jeremy Gow
CL-USER> 
95 1 Jeremy Gow
96 1 Jeremy Gow
</pre> 
97 1 Jeremy Gow
98 1 Jeremy Gow
99 1 Jeremy Gow
h2. Importing Data  
100 1 Jeremy Gow
101 1 Jeremy Gow
Before you can do this you will need to import some data. Methods exist for **kern files and midi files.
102 1 Jeremy Gow
103 1 Jeremy Gow
<pre> 
104 1 Jeremy Gow
CL-USER> (mtp-admin:import-data :krn "/home/mas01mtp/research/data/kern/shanxi237/" "A test dataset" 31)
105 1 Jeremy Gow
Inserting data into database: dataset 31.
106 1 Jeremy Gow
NIL
107 1 Jeremy Gow
CL-USER> (mtp-admin:describe-dataset 31)
108 1 Jeremy Gow
109 1 Jeremy Gow
 A test dataset
110 1 Jeremy Gow
 -----------------------------------------------------------------------------
111 1 Jeremy Gow
 ID         No. Compositions     No. events           Mean events/composition
112 1 Jeremy Gow
 31         237                  11056                46.649788           
113 1 Jeremy Gow
 -----------------------------------------------------------------------------NIL
114 1 Jeremy Gow
CL-USER> 
115 1 Jeremy Gow
</pre>
116 1 Jeremy Gow
117 1 Jeremy Gow
<pre> 
118 1 Jeremy Gow
CL-USER> (mtp-admin:import-data :mid "/home/marcusp/research/data/midi/Witchcraft/" "Melodies used in de Noojier et al. (2008)" 32)
119 1 Jeremy Gow
120 1 Jeremy Gow
Inserting data into database: dataset 32.NIL
121 1 Jeremy Gow
CL-USER> (mtp-admin:describe-dataset 32)
122 1 Jeremy Gow
123 1 Jeremy Gow
 Melodies used in de Noojier et al. (2008)
124 1 Jeremy Gow
 -----------------------------------------------------------------------------
125 1 Jeremy Gow
 ID         No. Compositions     No. events           Mean events/composition
126 1 Jeremy Gow
 32         10                   313                  31.3                
127 1 Jeremy Gow
 -----------------------------------------------------------------------------
128 1 Jeremy Gow
</pre> 
129 1 Jeremy Gow
130 1 Jeremy Gow
h2. Exporting Data  
131 1 Jeremy Gow
132 1 Jeremy Gow
<pre>
133 1 Jeremy Gow
CL-USER> (mtp-admin:export-data (mtp-admin:get-composition 31 0) :mid "/tmp/")
134 1 Jeremy Gow
NIL
135 1 Jeremy Gow
</pre> 
136 1 Jeremy Gow
137 1 Jeremy Gow
Produces a midi file.
138 1 Jeremy Gow
139 1 Jeremy Gow
<pre>
140 1 Jeremy Gow
CL-USER> (mtp-admin:export-data (mtp-admin:get-composition 31 0) :lisp "/tmp/")
141 1 Jeremy Gow
NIL
142 1 Jeremy Gow
</pre> 
143 1 Jeremy Gow
144 1 Jeremy Gow
Produces a lisp readable representation of the dataset.
145 1 Jeremy Gow
146 1 Jeremy Gow
<pre>
147 1 Jeremy Gow
CL-USER> (mtp-admin:export-data (mtp-admin:get-composition 31 0) :ly "/tmp/")
148 1 Jeremy Gow
NIL
149 1 Jeremy Gow
</pre> 
150 1 Jeremy Gow
151 1 Jeremy Gow
Produces a lilypond file.
152 1 Jeremy Gow
153 1 Jeremy Gow
<pre>
154 1 Jeremy Gow
CL-USER> (mtp-admin:export-data (mtp-admin:get-composition 31 0) :pdf "/tmp/")
155 1 Jeremy Gow
NIL
156 1 Jeremy Gow
</pre> 
157 1 Jeremy Gow
158 1 Jeremy Gow
Produces a pdf score (via lilypond).
159 1 Jeremy Gow
160 1 Jeremy Gow
h2. Deleting a dataset  
161 1 Jeremy Gow
162 1 Jeremy Gow
<pre>
163 1 Jeremy Gow
CL-USER> (mtp-admin:delete-dataset 735)
164 1 Jeremy Gow
</pre>
165 1 Jeremy Gow
166 1 Jeremy Gow
deletes dataset 735 from the database.