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
(→‎qryArtists_index: artists->artist)
(moved all SQL to subpages)
Line 1: Line 1:
−
==Overview==
+
<hide>
−
[[category:software/incomplete]][[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).
+
[[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; 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).
 
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==
−
==Code==
+
* [[/v1]] - the first schema, with Albums and Artists in separate tables
−
===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 Artist
 
−
UNION
 
−
SELECT
 
−
  id,
 
−
  sort
 
−
FROM Artist 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 "[[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>
 

Revision as of 02:52, 10 January 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
Retrieved from "https://htyp.org/mw/index.php?title=AudioFerret&oldid=16654"