AudioFerret

Overview

AudioFerret is a client-server software system for managing audio files (it could also work with video 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; I have working clients written in MS Access 97 and Gambas, and a PHP client partly written; the audio server was originally written in Access 97 but I have now written one in Gambas which generally works better (although it needs some fixes and improvements).

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 `Album` (

 `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 `Artist` (

 `id` int(11) NOT NULL auto_increment,
 `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 `Title` (

 `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,
 `isUnavail` bool default FALSE COMMENT "TRUE = don't show this title as needing a file assigned",
 `WhenAdded` DATETIME  DEFAULT NULL COMMENT 'when the track was added to the database',
 `WhenChanged` DATETIME  DEFAULT NULL COMMENT 'when the track record was last modified',
 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>

qryCbx_Artists

<mysql>CREATE OR REPLACE VIEW qryCbx_Artists AS

 SELECT
   a1.ID,
   a1.Name AS Name
 FROM Artists AS a1

UNION

 SELECT
   a2.ID,
   a2.Sort AS Name
 FROM Artists AS a2
 WHERE (a2.Sort <> ) AND (a2.Sort <> a2.Name)

ORDER BY Name;</mysql>

qryCbx_Albums_byArtist

<mysql>CREATE OR REPLACE VIEW qryCbx_Albums_byArtist AS

 SELECT
   a.id,
   a.name,
   a.sort,
   a.idartist AS ID_Artist
 FROM Albums AS a

ORDER BY a.idartist, CONCAT(sort,name);</mysql>

qryCbx_Titles

<mysql>CREATE OR REPLACE VIEW qryCbx_Titles AS

 SELECT
   t.ID,
   CONCAT_WS(' - ',IF(t.name IS NULL,t.track,t.name),IF(t.Artist_ID IS NULL,aa.Name,at.Name),ab.Name) AS Track,
   t.album_id
 FROM
 (
   (Titles AS t LEFT JOIN Artists AS at ON t.Artist_ID=at.ID)
    LEFT JOIN Albums AS ab ON t.Album_ID=ab.ID
 ) LEFT JOIN Artists AS aa ON ab.idArtist=aa.ID

ORDER BY t.name,t.track;</mysql>

qryArtists_index

<mysql>CREATE OR REPLACE VIEW qryArtists_index AS SELECT

 id,
 name

FROM Artists UNION SELECT

 id,
 sort

FROM Artists WHERE NULLIF(sort,) IS NOT NULL ORDER BY name;</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>