Difference between revisions of "AudioFerret"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
(CREATE OR REPLACE VIEW qryTrack_Playing AS qryTrackPlaying; split optional and discarded tables off into separate sections)
(starting in on v2)
 
(14 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
==Overview==
+
<hide>
−
[[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).
+
[[page type::project]]
 +
[[thing type::software]]
 +
[[category:software/incomplete]]
 +
</hide>
 +
==About==
 +
[[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; 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).
+
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==
+
==Versions==
−
===SQL===
+
* [[/v1]] - the first schema, with Albums and Artists in separate tables
−
Some significant tidying of the data schema is called for, but it's on low priority at the moment.
+
* [[/v2]] - a redesign that is less stupid
−
 
 
−
<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 "[[issupedia:media piracy|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>
 

Latest revision as of 03:17, 24 December 2012

About

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).

Versions

  • /v1 - the first schema, with Albums and Artists in separate tables
  • /v2 - a redesign that is less stupid
Retrieved from "https://htyp.org/mw/index.php?title=AudioFerret&oldid=19178"