Database » History » Version 2

Jeremy Gow, 2013-02-21 02:25 PM

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