AudioFerret

Overview

AudioFerret is a client-server software system for managing audio files for playback. It is intended to replicate the functions of an old-style distributed jukebox, where multiple locations could request songs which were then queued for play on the main "server" unit audible to everyone in the location, but without any unnecessary limitations (e.g. the old units could only host so many songs, and there was no way to view information about what was being played) and making the best use of now-available tools (e.g. wiki pages for more information).

This project is still under development; the only working client is currently written in MS Access 97. I had written a PHP client that could display what was currently playing, but the hard drive it was on seems to have bit the dust and I hadn't gotten around to backing it up yet. Still, proof-of-principle has been established, and it shouldn't be too difficult to reconstruct and finish (so that songs can be requested as well).

Code

SQL

Some significant tidying of the data schema is called for, but it's on low priority at the moment.

<mysql>CREATE DATABASE /*!32312 IF NOT EXISTS*/ audioferret; USE audioferret; CREATE TABLE `Albums` (

 `id` int(11) NOT NULL auto_increment,
 `idartist` int(11) default NULL,
 `name` varchar(128) default NULL,
 `sort` varchar(50) default NULL,
 `filekey` varchar(50) default NULL,
 `isalbum` varchar(1) default NULL,
 `iscomplete` varchar(1) default NULL,
 PRIMARY KEY  (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `Artists` (

 `id` int(11) NOT NULL,
 `name` varchar(50) default NULL,
 `filekey` varchar(10) default NULL,
 `sort` varchar(50) default NULL,
 PRIMARY KEY  (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `Files` (

 `id` int(11) NOT NULL auto_increment,
 `filespec` varchar(255) default NULL,
 `id_sourcetype` int(11) default NULL,
 PRIMARY KEY  (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=9068 DEFAULT CHARSET=latin1;

CREATE TABLE `Titles` (

 `id` int(11) NOT NULL auto_increment,
 `album_id` int(11) default NULL,
 `track` varchar(50) default NULL,
 `artist_id` varchar(50) default NULL,
 `name` varchar(255) default NULL,
 `id_file` int(11) default NULL,
 PRIMARY KEY  (`id`),
 KEY `titles_index1` (`id`),
 KEY `titles_index2` (`id_file`)

) ENGINE=MyISAM AUTO_INCREMENT=6421 DEFAULT CHARSET=latin1;

/* This is the record of everything ever played on the jukebox.

  There should probably be a mechanism for archiving this to a separate database.

• /

CREATE TABLE `Tracks_Played` (

 `id` int(11) NOT NULL auto_increment,
 `id_title` int(11) default NULL,
 `when_requested` datetime default NULL,
 `when_started` datetime default NULL,
 `when_finished` datetime default NULL,
 `user` varchar(50) default NULL,
 `station` varchar(50) default NULL,
 PRIMARY KEY  (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=5602 DEFAULT CHARSET=latin1;

/* This is the list of tracks being requested; it is normally empty. */ CREATE TABLE `Tracks_Queued` (

 `id` int(11) NOT NULL auto_increment,
 `id_title` varchar(50) default NULL,
 `when_requested` datetime default NULL,
 `user` varchar(50) default NULL,
 `station` varchar(50) default NULL,
 PRIMARY KEY  (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=37 DEFAULT CHARSET=latin1;

/* Stored playlist management */

CREATE TABLE `Playlist_Tracks` (

 `id_playlist` int(11) NOT NULL,
 `id_track` int(11) NOT NULL,
 `sort` double default NULL,
 PRIMARY KEY  (`id_playlist`,`id_track`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `Playlists` (

 `id` int(11) NOT NULL,
 `name` varchar(50) default NULL,
 `author` varchar(50) default NULL,
 PRIMARY KEY  (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*

  This is a table for providing read/write globals so clients can see what's going on.
  It might be better done through a web API, but this design doesn't require the server to also provide a web interface.
  It might also be better done through stored procedures.

• /

CREATE TABLE `Shared` (

 `name` varchar(50) NOT NULL,
 `val` varchar(255) default NULL,
 `whenset` datetime default NULL,
 PRIMARY KEY  (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE OR REPLACE VIEW qryTrack_Playing AS

 SELECT * from Tracks_Played ORDER BY ID DESC LIMIT 1;</mysql>

Optional SQL

This was eventually going to be part of a subsystem for encouraging legit copies of "pirated" tracks. It isn't necessary in order to make the thing work, but I do plan to continue working with it for the next version. <mysql>CREATE TABLE `Source_Types` (

 `id` int(11) NOT NULL,
 `descr` varchar(50) default NULL,
 `islegalcopy` tinyint(1) default NULL,
 PRIMARY KEY  (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `Source_Types` VALUES (1,'CD rip of owned CD',1),

(2,'CD rip of owned content (other media)',1),
(3,'Skimmed from LP',1),
(4,'Skimmed from 45',1),
(5,'Legal free download',1),
(6,'Public Filesharing',0),
(7,'Copied by 3rd party',0);</mysql>

Discarded SQL

These tables had not been used yet, and I think there's probably a better way to provide this functionality using a hierarchical topic/value tree.

The basic idea was to provide data on everything known about the album -- the names of everyone who worked on it, the studio where it was recorded, dates, etc. so that these could be cross-referenced and other works recorded at the same place, produced by the same person, etc. could also be looked up. <mysql>CREATE TABLE `Locations` (

 `id` int(11) NOT NULL,
 `path` varchar(255) default NULL,
 `descr` varchar(50) default NULL,
 PRIMARY KEY  (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `Personnel` (

 `id` int(11) NOT NULL,
 `name` varchar(255) default NULL,
 `sort` varchar(255) default NULL,
 PRIMARY KEY  (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `Release_Data` (

 `id` int(11) NOT NULL,
 `id_release` int(11) default NULL,
 `id_type` int(11) default NULL,
 `id_person` int(11) default NULL,
 `notes` varchar(255) default NULL,
 PRIMARY KEY  (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `Release_Data_Types` (

 `id` int(11) NOT NULL,
 `descr` varchar(50) default NULL,
 PRIMARY KEY  (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `Releases` (

 `id` int(11) NOT NULL,
 `id_artist` int(11) default NULL,
 `name` varchar(255) default NULL,
 `label` varchar(50) default NULL,
 `catnum` varchar(50) default NULL,
 `notes` varchar(255) default NULL,
 PRIMARY KEY  (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `Song_Data` (

 `id` int(11) NOT NULL,
 `id_song` int(11) default NULL,
 `id_type` varchar(50) default NULL,
 `id_person` int(11) default NULL,
 `notes` varchar(255) default NULL,
 PRIMARY KEY  (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `Song_Data_Types` (

 `id` varchar(50) NOT NULL,
 `descr` varchar(50) default NULL,
 PRIMARY KEY  (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `Songs` (

 `id` int(11) NOT NULL,
 `name` varchar(255) default NULL,
 `which` varchar(50) default NULL,
 `notes` varchar(255) default NULL,
 PRIMARY KEY  (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `Take_Data` (

 `id` int(11) NOT NULL,
 `id_take` int(11) default NULL,
 `id_type` varchar(50) default NULL,
 `id_person` int(11) default NULL,
 `value` varchar(255) default NULL,
 `notes` varchar(255) default NULL,
 PRIMARY KEY  (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `Take_Data_Types` (

 `id` varchar(50) NOT NULL,
 `descr` varchar(50) default NULL,
 PRIMARY KEY  (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `Takes` (

 `id` int(11) NOT NULL,
 `id_song` int(11) default NULL,
 `which` varchar(50) default NULL,
 `descr` varchar(255) default NULL,
 PRIMARY KEY  (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;</mysql>