another typo in comment
[paraslash.git] / README.mysql
1 README.mysql
2 ============
3
4 This file describes how to use the mysql database tool which comes
5 with the paraslash package.
6
7 It assumes you have already installed mysql and paraslash as described
8 in INSTALL, so read README and INSTALL before proceeding.
9
10 First of all, make sure that
11
12 - mysqld is running
13 - para_server is running and compiled with mysql support (type
14 "para_client si" to find out)
15 - the user who runs para_client has the paraslash DB_WRITE and DB_READ
16 permissions set in server.users
17 - the user who runs para_server has create privileges on the mysql
18 server.
19
20 Remember: If something doesn't work as expected, look at the server log file
21 and/or increase output verbosity by using the -l switch for server and client.
22
23
24 Specify mysql data (port, passwd,...)
25 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
26
27 Type
28
29 para_server -h
30
31 and look at the mysql options. You may either specify these options
32 in ~/.paraslash/server.conf or directly at the command line (not
33 recommended for passwd option). Don't forget to do
34
35 chmod 600 ~/.paraslash/server.conf
36
37 as this file contains the mysql passwd. To make these changes take
38 effect you'll need to do
39
40 para_client hup
41
42 Or, restart the server.
43
44 Switch to the mysql dbtool
45 ~~~~~~~~~~~~~~~~~~~~~~~~~~
46
47 Actually, the mysql database tool should already be selected (just
48 ignore the warning about the missing database). To verify that it is
49 indeed activated, type
50
51 para_client cdt
52
53 which prints the name of the current database tool. If the mysql
54 dbtool is not selected, try
55
56 para_client cdt mysql
57
58 If this doesn't work either, it means that some required config options
59 were not specified (check the log for more info) or that para_server
60 was built without mysql support. Type
61
62 para_client si
63
64 to find out. If mysql is not mentioned as a supported database tool,
65 you'll have to recompile.
66
67
68 Create a new database
69 ~~~~~~~~~~~~~~~~~~~~~
70
71 Once the mysql database tool is selected, create the database:
72
73 para_client cdb
74 para_client cdt mysql
75
76 The second command forces para_server to re-init the mysql dbtool.
77 Check the log. There should not be any warnings or errors.
78
79
80 Fill your database with content
81 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
82
83 para_client upd
84
85 If this command fails, it most likely means the audio file directory
86 (given in the server configuration file) does not exist, is empty,
87 not readable, or contains different files with identical basenames. Fix
88 this problem before proceeding.
89
90 The command
91
92 para_client ls
93
94 prints the list of all files known by the mysql dbtool. If the list
95 is empty, double check the mysql_audio_file_dir option.
96
97
98 Create a stream which selects all songs
99 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
100
101 To keep it simple, let's only define the stream "all_songs". See below for
102 advanced stream usage.
103
104 para_client stradd all_songs < /dev/null
105 para_client sl 10 all_songs
106
107 The latter command should show you ten filenames.
108
109
110 Change to the all_songs stream
111 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
112
113 para_client cs all_songs
114
115 You should now be able to start streaming with
116
117 para_client play
118
119
120 Attribute usage
121 ~~~~~~~~~~~~~~~
122
123 An attribute is simply a bit which can be set for each sound file
124 individually. You may have as many attributes as you like. A new
125 attribute "test" is created by
126
127 para_client na test
128
129 and
130 para_client laa
131
132 lists all available attributes. You can set the "test" attribute for
133 the current song by executing
134
135 para_client sa test+
136
137 or for any particular song by
138
139 para_client sa test+ filename
140
141 You can unset the attribute "test" for the current song with
142
143 para_client sa test-
144
145 and you can drop the test attribute entirely from the database with
146
147 para_client da test
148
149 Stream usage
150 ~~~~~~~~~~~~
151
152 A stream is a pair of expressions in terms of attributes and other
153 meta data contained in the database. The first, boolian, expression
154 determines the set of songs which are permitted in this stream. The
155 second, integer, expression determines the order in which permitted
156 songs are going to be fed to the audio file sender(s).
157
158 To create a new stream called "my_stream", put arbitrary many (including
159 none) accept or deny lines and one or zero score lines into some
160 temporary file, say tmpfile. An accept/deny/score line consists of
161 an identifier ("accept:", "deny:", or "score:"), followed by an
162 expression. The command
163
164 para_client stradd my_stream < tmpfile
165
166 adds the stream "my_stream" to dbtool's stream database.
167
168 If the stream definition is really short, you may also just pipe it to
169 the client rather than using temporary files. Like this:
170
171 echo "$MYSTREAMDEF" | para_client stradd my_stream
172
173
174 Example:
175
176 Assume you already have an attribute "test" and you'd like to
177 to restrict the set of songs being played to those having the
178 "test" attribute set. Define a new stream "only_test" by
179
180 echo 'accept: IS_SET(test)' | para_client stradd only_test
181
182 Then, after switching to the "only_test" stream with
183
184 para_client cs only_test
185
186 only the desired songs are going to be played.
187
188 There is no need to keep the temporary files containing the stream
189 definition since you can always query the database to get it back:
190
191 para_client strq only_test
192
193 The accept/deny expressions are used to find out which songs are
194 permitted. The following four cases are all possible and valid:
195
196 o Neither accept nor deny lines: This selects all songs.
197
198 o Only accept lines: Songs that match at least one accept
199 expression are accepted, all others are denied:
200
201 accept_expr1 or accept_expr2 or ...
202
203 o Only deny lines: Songs that match at least one deny expression are
204 denied, all others are accepted:
205
206 not (deny_expr1 or deny_expr2 ...)
207
208 o Both accept and deny lines: A song is accepted if it matches
209 at least one accept expression, but no deny expression, i.e.
210
211 (accept_expr1 or accept_expr2 or ..) and not
212 (deny_expr1 or deny_expr2 ..)
213
214 The command
215
216 para_client streams
217
218 lists all available streams and
219
220 para_client strdel streamname
221
222 removes the stream "streamname".
223
224 There are more sophisticated ways to define a stream than just using
225 one IS_SET() macro as in the example above. Of course, IS_SET(foo)
226 is true for a given audio file if and only if it has the attribute
227 "foo" set. Here are some more macros you can use:
228
229 o IS_N_SET(attr): True if attribute attr is not set
230
231 o NAME_LIKE(string): True if basename is like (in the sense
232 of mysql) "string"
233
234 o LASTPLAYED(): Expands to number of minutes that are gone
235 since this audio file has been played (by paraslash).
236
237 o NUMPLAYED(): Expands to number of times, the file has
238 been played.
239
240 o PICID(): Expands to the number of the picture which is
241 associated with this song.
242
243 To give a real-life example, suppose you have already added the
244 attributes "pop", "rock" with the "na" command. Assume also that you
245 have set these attributes for some or all of your songs having the
246 corresponding properties.
247
248 If you like to be waked up in the morning by poprock songs, but you
249 have some strange feeling telling you that just a few seconds of
250 Madonna's voice will be enough to mess up your whole day, just write
251 the lines
252
253 accept: IS_SET(pop) and IS_SET(rock)
254 deny: NAME_LIKE(%Madonna%)
255
256 to some temporary file "tmp" and do
257
258 para_client stradd wake < tmp
259
260 You can then switch to the new stream with
261
262 para_client cs wake
263
264 or you can let cron do this for you on a daily basis..
265
266 Accept/deny lines affect only the set of admissible songs, but not
267 the order in which these songs are played. That's where the score
268 expression comes into play.
269
270 Scoring
271 ~~~~~~~
272 You may put a single score line anywhere in the stream definition. If
273 omitted, the default scoring rule specified in the configuration file
274 applies. If there is no default scoring rule in the config file either,
275 the compiled in default is going to be used (see para_server -h).
276
277 Simple examples of scoring rules (either specified in a stream
278 definition or as the default scoring rule in the config file) include:
279
280 LASTPLAYED()/1440
281
282 This means that each song's score is just the number of days that went
283 by since this song has been played (one day is 1440 minutes). This
284 is fine in many cases since the dbtool then always chooses that
285 admissible song, which wasn't played for the longest time.
286
287 However, one disadvantage of this scoring sheme is that new songs,
288 once played, are going to be deferred for a possibly very long period
289 depending on the size of your collection of (admissible) songs. Hence
290 the following scoring rule comes into mind:
291
292 score: -NUMPLAYED()
293
294 since this gives newer songs, i.e. songs to which you haven't listen to
295 that often, a higher score than older songs you already know by heart.
296
297 You can also use a combination of these two methods:
298
299 score: LASTPLAYED()/1440 - 10 * NUMPLAYED()
300
301 which subtracts 10 score points for each time paraslash has played
302 this song.
303
304 Another useful feature for scoring is due to the fact that "true"
305 expands to one and "false" to zero. So you can also use the
306 IS_SET/IS_N_SET/NAME_LIKE macros in a score line to give
307 your favorite band "bar" some extra points:
308
309 score: 40 * IS_SET(foo) + 20 * NAME_LIKE(%bar%) + LASTPLAYED()/1440
310
311
312 Pictures
313 ~~~~~~~~
314
315 dbtool can also magage images that, when associated with certain songs,
316 can be displayed by para_sdl_gui and para_krell when one of these songs
317 is playing. It is also possible to just retrieve the current image via
318
319 para_client pic > filename
320
321 in order to feed it to your favorite tool. Try
322
323 para_client help | grep ^pic
324
325 and read the online help of the shown commands for more information.