patches to make token= match on regular expressions

Mike Wohlgemuth <[email protected]> 30 Aug 2002 19:09:11 -0400

Newsgroups	gmane.comp.apache.mod-mp3
Message-ID	<[email protected]>

A bit back, someone was asking on the list if it was possible to match
on multiple tokens and randomly stream from them.  These patches allow
for that.  I have included 2 files, one to patch mysql_dispatch.h and
one to patch pgsql_dispatch.h.  I've only tested with mysql, since that
is all I have locally, but baring any typos, the pqsql changes should
work fine as well.

These patches do 2 things:

1.  Remove the LIMIT 1 from any random select statement.  I'm not sure
what the reason is for having it there, but I always want more than one
file back when randomly streaming.

2.  Change any SQL statement that has token = \"%s\" in the where clause
to do a regex match instead of an equality check.  For mysql a regex
looks like token REGEXP \"%s\", for pgsql, it looks like token ~*
\"%s\".  I've used case insensitive regexes.  To do case sensitive
regexes, it would be token REGEXP BINARY \"%s\" for mysql and token ~
\"%s\" for pgsql.  This lets us specify things like

token=^rock

to play all files with a token beginning with rock, or

token=^(rock|jazz)$ to play all files with tokens of rock or jazz.

Woogie

pgsql_dispatch.h.patch (text/x-patch, 3.2 KB)

*** pgsql_dispatch.h.orig	Fri Aug 30 17:40:44 2002
--- pgsql_dispatch.h	Fri Aug 30 17:41:20 2002
***************
*** 3,18 ****
  */
  
  #define PG_MP3_GET "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s WHERE signature = '%s'"
! #define PG_MP3_GET_RANDOM "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s ORDER BY round(random()*1000) LIMIT 1"
  #define PG_MP3_INSERT "INSERT IGNORE INTO %s (name,filename,signature,artist,album,comment,track,year,genre) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s')"
  
  #define PG_MP3_GETS "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s"
  #define PG_MP3_GET_LIST "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s WHERE signature IN (%s)"
! #define PG_MP3_GETS_TOKEN "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s,%s WHERE token = \"%s\" AND signature = for_signature"
  
! #define PG_MP3_GETS_RANDOM "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s ORDER BY round(random()*1000) LIMIT 1"
! #define PG_MP3_GET_LIST_RANDOM "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s WHERE signature IN (%s) ORDER BY round(random()*1000) LIMIT 1"
! #define PG_MP3_GETS_TOKEN_RANDOM "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s,%s WHERE token = \"%s\" AND signature = for_signature ORDER BY round(random()*1000) LIMIT 1"
  
  #define PG_MP3_GETS_COUNT "SELECT count(signature) FROM %s"
  #define PG_MP3_GET_LIST_COUNT "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s WHERE signature IN (%s)"
--- 3,18 ----
  */
  
  #define PG_MP3_GET "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s WHERE signature = '%s'"
! #define PG_MP3_GET_RANDOM "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s ORDER BY round(random()*1000)"
  #define PG_MP3_INSERT "INSERT IGNORE INTO %s (name,filename,signature,artist,album,comment,track,year,genre) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s')"
  
  #define PG_MP3_GETS "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s"
  #define PG_MP3_GET_LIST "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s WHERE signature IN (%s)"
! #define PG_MP3_GETS_TOKEN "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s,%s WHERE token ~* \"%s\" AND signature = for_signature"
  
! #define PG_MP3_GETS_RANDOM "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s ORDER BY round(random()*1000)"
! #define PG_MP3_GET_LIST_RANDOM "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s WHERE signature IN (%s) ORDER BY round(random()*1000)"
! #define PG_MP3_GETS_TOKEN_RANDOM "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s,%s WHERE token ~* \"%s\" AND signature = for_signature ORDER BY round(random()*1000)"
  
  #define PG_MP3_GETS_COUNT "SELECT count(signature) FROM %s"
  #define PG_MP3_GET_LIST_COUNT "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s WHERE signature IN (%s)"

mysql_dispatch.h.patch (text/x-patch, 3.2 KB)

*** mysql_dispatch.h.orig	Fri Aug 30 17:40:30 2002
--- mysql_dispatch.h	Fri Aug 30 17:41:20 2002
***************
*** 6,21 ****
  #define MP3_MAX_VARCHAR 255
  
  #define MYSQL_MP3_GET "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s WHERE signature = '%s'"
! #define MYSQL_MP3_GET_RANDOM "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s ORDER BY RAND() LIMIT 1"
  #define MYSQL_MP3_INSERT "INSERT IGNORE INTO %s (name,filename,signature,artist,album,comment,track,year,genre) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s')"
  
  #define MYSQL_MP3_GETS "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s"
  #define MYSQL_MP3_GET_LIST "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s WHERE signature IN (%s)"
! #define MYSQL_MP3_GETS_TOKEN "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s,%s WHERE token = \"%s\" AND signature = for_signature"
  
! #define MYSQL_MP3_GETS_RANDOM "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s ORDER BY RAND() LIMIT 1"
! #define MYSQL_MP3_GET_LIST_RANDOM "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s WHERE signature IN (%s) ORDER BY RAND() LIMIT 1"
! #define MYSQL_MP3_GETS_TOKEN_RANDOM "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s,%s WHERE token = \"%s\" AND signature = for_signature ORDER BY RAND() LIMIT 1"
  
  #define MYSQL_MP3_GETS_COUNT "SELECT count(signature) FROM %s"
  #define MYSQL_MP3_GET_LIST_COUNT "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s WHERE signature IN (%s)"
--- 6,21 ----
  #define MP3_MAX_VARCHAR 255
  
  #define MYSQL_MP3_GET "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s WHERE signature = '%s'"
! #define MYSQL_MP3_GET_RANDOM "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s ORDER BY RAND()"
  #define MYSQL_MP3_INSERT "INSERT IGNORE INTO %s (name,filename,signature,artist,album,comment,track,year,genre) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s')"
  
  #define MYSQL_MP3_GETS "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s"
  #define MYSQL_MP3_GET_LIST "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s WHERE signature IN (%s)"
! #define MYSQL_MP3_GETS_TOKEN "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s,%s WHERE token REGEXP \"%s\" AND signature = for_signature"
  
! #define MYSQL_MP3_GETS_RANDOM "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s ORDER BY RAND()"
! #define MYSQL_MP3_GET_LIST_RANDOM "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s WHERE signature IN (%s) ORDER BY RAND()"
! #define MYSQL_MP3_GETS_TOKEN_RANDOM "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s,%s WHERE token REGEXP \"%s\" AND signature = for_signature ORDER BY RAND()"
  
  #define MYSQL_MP3_GETS_COUNT "SELECT count(signature) FROM %s"
  #define MYSQL_MP3_GET_LIST_COUNT "SELECT name,filename,signature,artist,album,comment,track,year,genre FROM %s WHERE signature IN (%s)"