{*******************************************************************
This Script is designed to enable DJs with no scripting experience
to query the database and output the results to text files and to
give examples of the data held within the database and the format
*** THIS SCRIPT SHOULD ONLY BE RUN WHEN SAM BROADCASTER IS IDLE ***
*** THIS WILL BY DESIGN CAUSE SAM BROADCASTER TO STOP IF RUNNING ***
*******************************************************************}
PAL.LockExecution; // Speed up the Script processing
// Declare Constants
Const Option1 : string = 'Select 1 to Search by Artist';
Const Option2 : string = 'Select 2 to Search by Track';
Const Option3 : string = 'Select 3 to Search by Album';
Const Option4 : string = 'Select 4 to Search by a single Year';
Const Option5 : string = 'Select 5 to Search by Year Range';
Const Option6 : string = 'Select 6 to Search by Track Type';
// Declare Fixed Variables
var Results : TDataSet;
var TrackName : string;
var TrkCount : integer = 1;
var TrkTotal : integer = 0;
var StartYear : integer;
var EndYear : integer;
var ReqYear : integer;
var SearchArtist : string = '';
var SearchAlbum : string = '';
var SearchTrack : string = '';
var TempList : TStringList;
var Option : integer = 0;
var FilePath : string;
var CatType : string = '';
// MAIN -------------------------------------------------------------------------------------------
TempList := TStringList.Create;
FilePath := GetCurrentDir+'\Output\';
CreateDir(Filepath);
WriteLn('Output Directory: '+FilePath);
WriteLn(' ');
Option := StrToInt(InputBox('Options',Option1+#13#10+Option2+#13#10+Option3+#13#10+Option4+#13#10+Option5+#13#10+Option6+#13#10,''));
// Query based on artist (Option 1) ---------------------------------------------------------------
If Option = 1 then
begin
SearchArtist := InputBox('Artist Search','Enter the artist name to search for...','');
Filepath := Filepath+SearchArtist+'.txt';
WriteLn('Tracks for Artist: '+SearchArtist);
WriteLn(' ');
if SearchArtist <> '' then
begin
TempList.Add('Tracks for Artist: '+SearchArtist);
TempList.Add(' ');
Results := Query('SELECT * FROM songlist WHERE artist = :ArtistName',[SearchArtist],False);
while not Results.EOF do
begin
writeln(FloatToSTr(TrkCount)+'. '+Results['title']);
TrackName := Results['artist']+' - '+Results['title'];
TrackName := FloatToStr(TrkCount)+'. '+TrackName;
TempList.Add(TrackName);
TrkCount := TrkCount + 1;
TrkTotal := TrkTotal + 1;
Results.Next;
end;
end;
WriteLn(' ');
WriteLn('Total Tracks found for album '+SearchArtist+': '+FloatToStr(TrkTotal));
WriteLn(' ');
WriteLn('- - - - - End of Record - - - - -');
WriteLn(' ');
TempList.Add(' ');
TempList.Add('Total Number of Tracks Found: '+FloatToStr(TrkTotal));
TempList.Add(' ');
TempList.Add('- - - - - End of Record - - - - -');
TempList.Add(' ');
TempList.SaveToFile(FilePath);
TempList.Clear;
end;
// Query based on track (Option 2) ----------------------------------------------------------------
If Option = 2 then
begin
SearchTrack := InputBox('Track Search','Enter the track name to search for...','');
Filepath := Filepath+SearchTrack+'.txt';
WriteLn('Track Record for: '+SearchTrack);
WriteLn(' ');
TempList.Add('Track Record for: '+SearchTrack);
TempList.Add(' ');
if SearchTrack <> '' then
begin
Results := Query('SELECT * FROM songlist WHERE title = :TrackName',[SearchTrack],False);
while not Results.EOF do
begin
// Write Record to PAL IDE Output
WriteLn('Track ID: '+FloatToStr(Results['ID']));
WriteLn('Filename: '+Results['filename']);
WriteLn('Disk ID: '+FloatToStr(Results['DiskID']));
WriteLn('Flags: '+Results['flags']);
WriteLn('Song Type: '+Results['songtype']);
WriteLn('Status: '+FloatToStr(Results['status']));
WriteLn('Weight: '+FloatToStr(Results['weight']));
WriteLn('Balance: '+FloatToStr(Results['balance']));
WriteLn('Date Added: '+DateTimeToStr(Results['date_added']));
WriteLn('Date Played: '+DateTimeToStr(Results['date_played']));
WriteLn('Date Artist Played: '+DateTimeToStr(Results['date_artist_played']));
WriteLn('Date Album Played: '+DateTimeToStr(Results['date_album_played']));
WriteLn('Date Title Played: '+DateTimeToStr(Results['date_title_played']));
WriteLn('Duration: '+FloatToStr(Results['Duration']));
WriteLn('Artist: '+Results['artist']);
WriteLn('Title: '+Results['title']);
WriteLn('Album: '+Results['album']);
WriteLn('Label: '+Results['label']);
WriteLn('PLine: '+Results['pline']);
WriteLn('Track No: '+FloatToStr(Results['trackno']));
WriteLn('Composer: '+Results['composer']);
WriteLn('ISRC: '+Results['ISRC']);
WriteLn('Catalog: '+Results['Catalog']);
WriteLn('UPC: '+Results['UPC']);
WriteLn('Fee Agency: '+Results['feeagency']);
WriteLn('Album Year: '+Results['albumyear']);
WriteLn('Genre: '+Results['genre']);
WriteLn('Website: '+Results['website']);
WriteLn('Buy CD: '+Results['buycd']);
WriteLn('Info: '+Results['info']);
WriteLn('Lyics: '+Results['lyrics']);
WriteLn('Picture: '+Results['picture']);
WriteLn('Count Played: '+FloatToStr(Results['count_played']));
WriteLn('Count Requested: '+FloatToStr(Results['count_requested']));
WriteLn('Last Requested: '+DateTimeToStr(Results['last_requested']));
WriteLn('Count Performances: '+FloatToStr(Results['count_performances']));
WriteLn('Crossfade: '+Results['xfade']);
WriteLn('BPM: '+FloatToStr(Results['bpm']));
WriteLn('Mood: '+Results['mood']);
WriteLn('Rating: '+FloatToStr(Results['rating']));
WriteLn('Overlay: '+Results['overlay']);
WriteLn('Playlimit Count: '+FloatToStr(Results['playlimit_count']));
WriteLn('Playlimit Action: '+Results['playlimit_action']);
WriteLn('Songrights: '+Results['songrights']);
WriteLn(' ');
WriteLn('Total Tracks found for album '+SearchTrack+': '+FloatToStr(TrkTotal));
WriteLn(' ');
WriteLn('- - - - - End of Record - - - - -');
WriteLn(' ');
// Write Record to Text File
TempList.Add('Track ID: '+FloatToStr(Results['ID']));
TempList.Add('Filename: '+Results['filename']);
TempList.Add('Disk ID: '+FloatToStr(Results['DiskID']));
TempList.Add('Flags: '+Results['flags']);
TempList.Add('Song Type: '+Results['songtype']);
TempList.Add('Status: '+FloatToStr(Results['status']));
TempList.Add('Weight: '+FloatToStr(Results['weight']));
TempList.Add('Balance: '+FloatToStr(Results['balance']));
TempList.Add('Date Added: '+DateTimeToStr(Results['date_added']));
TempList.Add('Date Played: '+DateTimeToStr(Results['date_played']));
TempList.Add('Date Artist Played: '+DateTimeToStr(Results['date_artist_played']));
TempList.Add('Date Album Played: '+DateTimeToStr(Results['date_album_played']));
TempList.Add('Date Title Played: '+DateTimeToStr(Results['date_title_played']));
TempList.Add('Duration: '+FloatToStr(Results['Duration']));
TempList.Add('Artist: '+Results['artist']);
TempList.Add('Title: '+Results['title']);
TempList.Add('Album: '+Results['album']);
TempList.Add('Label: '+Results['label']);
TempList.Add('PLine: '+Results['pline']);
TempList.Add('Track No: '+FloatToStr(Results['trackno']));
TempList.Add('Composer: '+Results['composer']);
TempList.Add('ISRC: '+Results['ISRC']);
TempList.Add('Catalog: '+Results['Catalog']);
TempList.Add('UPC: '+Results['UPC']);
TempList.Add('Fee Agency: '+Results['feeagency']);
TempList.Add('Album Year: '+Results['albumyear']);
TempList.Add('Genre: '+Results['genre']);
TempList.Add('Website: '+Results['website']);
TempList.Add('Buy CD: '+Results['buycd']);
TempList.Add('Info: '+Results['info']);
TempList.Add('Lyics: '+Results['lyrics']);
TempList.Add('Picture: '+Results['picture']);
TempList.Add('Count Played: '+FloatToStr(Results['count_played']));
TempList.Add('Count Requested: '+FloatToStr(Results['count_requested']));
TempList.Add('Last Requested: '+DateTimeToStr(Results['last_requested']));
TempList.Add('Count Performances: '+FloatToStr(Results['count_performances']));
TempList.Add('Crossfade: '+Results['xfade']);
TempList.Add('BPM: '+FloatToStr(Results['bpm']));
TempList.Add('Mood: '+Results['mood']);
TempList.Add('Rating: '+FloatToStr(Results['rating']));
TempList.Add('Overlay: '+Results['overlay']);
TempList.Add('Playlimit Count: '+FloatToStr(Results['playlimit_count']));
TempList.Add('Playlimit Action: '+Results['playlimit_action']);
TempList.Add('Songrights: '+Results['songrights']);
TempList.Add(' ');
TempList.Add('- - - - - End of Record - - - - -');
TempList.SaveToFile(FilePath);
TempList.Clear;
Results.Next;
end;
end;
end;
// Query based on album (Option 3) ----------------------------------------------------------------
If Option = 3 then
begin
SearchAlbum := InputBox('Album Search','Enter the album name to search for...','');
Filepath := Filepath+SearchAlbum+'.txt';
WriteLn('Tracks for Album: '+SearchAlbum);
WriteLn(' ');
TempList.Add('Tracks for Album: '+SearchAlbum);
TempList.Add(' ');
if SearchAlbum <> '' then
begin
Results := Query('SELECT * FROM songlist WHERE album = :AlbumName',[SearchAlbum],False);
while not Results.EOF do
begin
TrackName := Results['artist']+' - '+Results['title'];
TrackName := FloatToStr(TrkCount)+'. '+TrackName;
WriteLn(TrackName);
TempList.Add(TrackName);
TrkCount := TrkCount + 1;
TrkTotal := TrkTotal + 1;
Results.Next;
end;
end;
WriteLn(' ');
WriteLn('Total Tracks found for album '+SearchAlbum+': '+FloatToStr(TrkTotal));
WriteLn(' ');
WriteLn('- - - - - End of Record - - - - -');
WriteLn(' ');
TempList.Add(' ');
TempList.Add('Total Tracks found for album '+SearchAlbum+': '+FloatToStr(TrkTotal));
TempList.Add(' ');
TempList.Add('- - - - - End of Record - - - - -');
TempList.SaveToFile(FilePath);
TempList.Clear;
end;
// Query based on single year (Option 4) ----------------------------------------------------------
If Option = 4 then
begin
StartYear := StrToInt(InputBox('Search Year','Please enter the year to search',''));
ReqYear := StartYear;
WriteLn('Tracks for Year: '+FloatToStr(ReqYear));
WriteLn(' ');
TempList.Add('Tracks for Year: '+FloatToStr(ReqYear));
TempList.Add(' ');
Filepath := Filepath+FloatToStr(ReqYear)+'.txt';
Results := Query('SELECT * FROM songlist WHERE albumyear = :Dated',[ReqYear],False);
Results.First;
while not Results.EOF do
begin
TrackName := Results['artist']+' - '+Results['title'];
TrackName := FloatToStr(TrkCount)+'. '+TrackName;
WriteLn(TrackName);
TempList.Add(TrackName);
TrkCount := TrkCount + 1;
TrkTotal := TrkTotal + 1;
Results.Next;
end;
WriteLn(' ');
WriteLn('Total Number of Tracks Found for Year '+FloatToStr(ReqYear)+': '+FloatToStr(TrkTotal));
WriteLn(' ');
WriteLn('- - - - - End of Record - - - - -');
WriteLn(' ');
TempList.Add(' ');
TempList.Add('Total Number of Tracks Found: '+FloatToStr(TrkTotal));
TempList.Add(' ');
TempList.Add('- - - - - End of Record - - - - -');
TempList.SaveToFile(FilePath);
TempList.Clear;
end;
// Query based on a range of years (Option 5) -----------------------------------------------------
If Option = 5 then
begin
StartYear := StrToInt(InputBox('Starting Year','Please enter the year to search from',''));
EndYear := StrToInt(InputBox('End Year','Please enter the year to search up to',''));
ReqYear := StartYear;
While (EndYear > StartYear) AND (ReqYear <= EndYear) do
begin
Filepath := Filepath+intToStr(ReqYear)+'.txt';
WriteLn('Tracks for Year: '+FloatToStr(ReqYear));
WriteLn(' ');
TempList.Add('Tracks for Year: '+FloatToStr(ReqYear));
TempList.Add(' ');
Results := Query('SELECT * FROM songlist WHERE albumyear = :Dated',[ReqYear],False);
Results.First;
while not Results.EOF do
begin
TrackName := Results['artist']+' - '+Results['title'];
TrackName := FloatToSTr(TrkCount)+'. '+TrackName;
WriteLn(TrackName);
TempList.Add(TrackName);
TrkCount := TrkCount + 1;
TrkTotal := TrkTotal + 1;
Results.Next;
end;
WriteLn(' ');
WriteLn('Total Number of Tracks Found for '+FloatToStr(ReqYear)+': '+FloatToStr(TrkCount - 1));
WriteLn(' ');
WriteLn('- - - - - End of Record - - - - -');
WriteLn(' ');
TempList.Add(' ');
TempList.Add('Total Number of Tracks Found for '+FloatToStr(ReqYear)+': '+FloatToStr(TrkCount - 1));
TempList.Add(' ');
TempList.Add('- - - - - End of Record - - - - -');
TempList.Add(' ');
TempList.SaveToFile(FilePath);
TempList.Clear;
ReqYear := ReqYear + 1;
TrkCount := 1;
FilePath := GetCurrentDir+'\Output\';
end;
end;
// Query to List the tracks in the library based on category type (Option 6)----------------------------------
If Option = 6 then
begin
CatType := InputBox('Category Search','Enter the category type to search for...(S, I, P, J, N, V, X, C, ?)','');
Filepath := Filepath+CatType+'.txt';
If CatType='?' Then
FilePath := GetCurrentDir+'\Output\QuestionMark'+'.txt';
WriteLn('Tracks for Category: '+CatType);
WriteLn(' ');
TempList.Add('Tracks for Category: '+CatType);
TempList.Add(' ');
if CatType <> '' then
begin
Results := Query('SELECT * FROM songlist WHERE songtype = :CatType',[CatType],False);
while not Results.EOF do
begin
TrackName := FloatToStr(Results['ID'])+' - '+Results['artist']+' - '+Results['title'];
TrackName := FloatToStr(TrkCount)+'. '+TrackName;
WriteLn(TrackName);
TempList.Add(TrackName);
TrkCount := TrkCount + 1;
TrkTotal := TrkTotal + 1;
Results.Next;
end;
end;
WriteLn(' ');
WriteLn('Total Tracks found for Category '+CatType+': '+FloatToStr(TrkTotal));
WriteLn(' ');
WriteLn('- - - - - End of Record - - - - -');
WriteLn(' ');
TempList.Add(' ');
TempList.Add('Total Tracks found for Category '+CatType+': '+FloatToStr(TrkTotal));
TempList.Add(' ');
TempList.Add('- - - - - End of Record - - - - -');
TempList.SaveToFile(FilePath);
TempList.Clear;
end;
// End of Main ------------------------------------------------------------------------------------
PAL.UnLockExecution; // Return PAL Script processing to normal speed
// Housekeeping
Results.Free;
So let's break this script down in to the various sections to make it simpler to understand:
The Opening Remarks
It is always a good idea to add some comments to a script to give an indication of the script's purpose. You could also consider including dates and code revision dates:
{*******************************************************************
This Script is designed to enable DJs with no scripting experience
to query the database and output the results to text files and to
give examples of the data held within the database and the format
*** THIS SCRIPT SHOULD ONLY BE RUN WHEN SAM BROADCASTER IS IDLE ***
*** THIS WILL BY DESIGN CAUSE SAM BROADCASTER TO STOP IF RUNNING ***
*******************************************************************}
As you can see, the comments are very simple but they do include a warning not to run this script unless SAM Broadcaster is idle.
Script Speed
PAL.LockExecution; // Speed up the Script processing
Because SAM Broadcaster is not streaming and because querying the database can provide a lot of search results, we want the script running as quickly as possible so we add this line as the first line of code after the opening comments.
Constant Declarations
Instead of using Variables, we have chosen to use Const to hold specific data strings.
// Declare Constants
Const Option1 : string = 'Select 1 to Search by Artist';
Const Option2 : string = 'Select 2 to Search by Track';
Const Option3 : string = 'Select 3 to Search by Album';
Const Option4 : string = 'Select 4 to Search by a single Year';
Const Option5 : string = 'Select 5 to Search by Year Range';
Const Option6 : string = 'Select 6 to Search by Track Type';
These can be referred to throughout the script but these are only used at the start of this script in conjunction with the user prompt dialog box which gives the user the option to select the type of query to run against the database.
Variable Declarations
It is usual to declare variables at the start of any script or program. They are easier to find which can simplify troubleshooting.
// Declare Fixed Variables
var Results : TDataSet;
var TrackName : string;
var TrkCount : integer = 1;
var TrkTotal : integer = 0;
var StartYear : integer;
var EndYear : integer;
var ReqYear : integer;
var SearchArtist : string = '';
var SearchAlbum : string = '';
var SearchTrack : string = '';
var TempList : TStringList;
var Option : integer = 0;
var FilePath : string;
You will note we declare a variable of type TDataSet to hold the data from the database queries, we declare a number of both integer and string type variables, as well as a TStringList which is used to hold lists of strings. This will become more obvious a little later.
Program Main
The start of the program.
// MAIN -------------------------------------------------------------------------------------------
TempList := TStringList.Create;
FilePath := GetCurrentDir+'\Output\';
CreateDir(Filepath);
WriteLn('Output Directory: '+FilePath);
WriteLn(' ');
Option := StrToInt(InputBox('Options',Option1+#13#10+Option2+#13#10+Option3+#13#10+Option4+#13#10+Option5+#13#10,''));
The first line creates the TStringList container with the name TempList, we also get the current directory path in use, add the folder name Output to the end of the folder path and then we create the directory. This is where we will output all of the search results in text file format. You will note that to ensure we know where this folder is located, we write the folder path to the PAL Script IDE Window Output.
We also make use of the InputBox which in this case, prompts the user to make a selection and enter their choice depending upon the type of query they wish to run be it a search by Artist, Track, Album, a single Year or a range of Years.
The +#13#10 includes a carriage return and a new line which forces the different options to display on separate lines. We could also have written +#13+#10 but the short form works just as well.
The purpose of this script is to run different queries against the database and as such, the rest of this article is broken down in to the 5 options available.
Option 1 - Searching by Artist
You will note that a large percentage of the code under the different options is repeated from this section of code. Essentially, querying the database is the same or similar process just using different search words with some variation in the way the results are written.
// Query based on artist (Option 1) ---------------------------------------------------------------
If Option = 1 then
begin
SearchArtist := InputBox('Artist Search','Enter the artist name to search for...','');
Filepath := Filepath+SearchArtist+'.txt';
WriteLn('Tracks for Artist: '+SearchArtist);
WriteLn(' ');
if SearchArtist <> '' then
begin
TempList.Add('Tracks for Artist: '+SearchArtist);
TempList.Add(' ');
Results := Query('SELECT * FROM songlist WHERE artist = :ArtistName',[SearchArtist],False);
while not Results.EOF do
begin
writeln(FloatToSTr(TrkCount)+'. '+Results['title']);
TrackName := Results['artist']+' - '+Results['title'];
TrackName := FloatToStr(TrkCount)+'. '+TrackName;
TempList.Add(TrackName);
TrkCount := TrkCount + 1;
TrkTotal := TrkTotal + 1;
Results.Next;
end;
end;
WriteLn(' ');
WriteLn('Total Tracks found for album '+SearchArtist+': '+FloatToStr(TrkTotal));
WriteLn(' ');
WriteLn('- - - - - End of Record - - - - -');
WriteLn(' ');
TempList.Add(' ');
TempList.Add('Total Number of Tracks Found: '+FloatToStr(TrkTotal));
TempList.Add(' ');
TempList.Add('- - - - - End of Record - - - - -');
TempList.Add(' ');
TempList.SaveToFile(FilePath);
TempList.Clear;
end;
To help explain the purpose of each line, we have this image with annoctations for each line or section. A larger version is available to download at the bottom of the page entitled Option 1.png:
Option 2 - Searching by Track
Whilst the method of capturing the search term is the same as Option 1, this section outputs every record field for the track which is hoped, will show the customer exactly what data is stored and how to acess this. You could amend the Query statement to seach for any of the fields shown below and this would return the corresponding results.
There are no new keywords in this section but as you will see, there are a large number of records written to both the IDE output window and the text file.
// Query based on track (Option 2) ----------------------------------------------------------------
If Option = 2 then
begin
SearchTrack := InputBox('Track Search','Enter the track name to search for...','');
Filepath := Filepath+SearchTrack+'.txt';
WriteLn('Track Record for: '+SearchTrack);
WriteLn(' ');
TempList.Add('Track Record for: '+SearchTrack);
TempList.Add(' ');
if SearchTrack <> '' then
begin
Results := Query('SELECT * FROM songlist WHERE title = :TrackName',[SearchTrack],False);
while not Results.EOF do
begin
// Write Record to PAL IDE Output
WriteLn('Track ID: '+FloatToStr(Results['ID']));
WriteLn('Filename: '+Results['filename']);
WriteLn('Disk ID: '+FloatToStr(Results['DiskID']));
WriteLn('Flags: '+Results['flags']);
WriteLn('Song Type: '+Results['songtype']);
WriteLn('Status: '+FloatToStr(Results['status']));
WriteLn('Weight: '+FloatToStr(Results['weight']));
WriteLn('Balance: '+FloatToStr(Results['balance']));
WriteLn('Date Added: '+DateTimeToStr(Results['date_added']));
WriteLn('Date Played: '+DateTimeToStr(Results['date_played']));
WriteLn('Date Artist Played: '+DateTimeToStr(Results['date_artist_played']));
WriteLn('Date Album Played: '+DateTimeToStr(Results['date_album_played']));
WriteLn('Date Title Played: '+DateTimeToStr(Results['date_title_played']));
WriteLn('Duration: '+FloatToStr(Results['Duration']));
WriteLn('Artist: '+Results['artist']);
WriteLn('Title: '+Results['title']);
WriteLn('Album: '+Results['album']);
WriteLn('Label: '+Results['label']);
WriteLn('PLine: '+Results['pline']);
WriteLn('Track No: '+FloatToStr(Results['trackno']));
WriteLn('Composer: '+Results['composer']);
WriteLn('ISRC: '+Results['ISRC']);
WriteLn('Catalog: '+Results['Catalog']);
WriteLn('UPC: '+Results['UPC']);
WriteLn('Fee Agency: '+Results['feeagency']);
WriteLn('Album Year: '+Results['albumyear']);
WriteLn('Genre: '+Results['genre']);
WriteLn('Website: '+Results['website']);
WriteLn('Buy CD: '+Results['buycd']);
WriteLn('Info: '+Results['info']);
WriteLn('Lyics: '+Results['lyrics']);
WriteLn('Picture: '+Results['picture']);
WriteLn('Count Played: '+FloatToStr(Results['count_played']));
WriteLn('Count Requested: '+FloatToStr(Results['count_requested']));
WriteLn('Last Requested: '+DateTimeToStr(Results['last_requested']));
WriteLn('Count Performances: '+FloatToStr(Results['count_performances']));
WriteLn('Crossfade: '+Results['xfade']);
WriteLn('BPM: '+FloatToStr(Results['bpm']));
WriteLn('Mood: '+Results['mood']);
WriteLn('Rating: '+FloatToStr(Results['rating']));
WriteLn('Overlay: '+Results['overlay']);
WriteLn('Playlimit Count: '+FloatToStr(Results['playlimit_count']));
WriteLn('Playlimit Action: '+Results['playlimit_action']);
WriteLn('Songrights: '+Results['songrights']);
WriteLn(' ');
WriteLn('Total Tracks found for album '+SearchTrack+': '+FloatToStr(TrkTotal));
WriteLn(' ');
WriteLn('- - - - - End of Record - - - - -');
WriteLn(' ')
// Write Record to Text File
TempList.Add('Track ID: '+FloatToStr(Results['ID']));
TempList.Add('Filename: '+Results['filename']);
TempList.Add('Disk ID: '+FloatToStr(Results['DiskID']));
TempList.Add('Flags: '+Results['flags']);
TempList.Add('Song Type: '+Results['songtype']);
TempList.Add('Status: '+FloatToStr(Results['status']));
TempList.Add('Weight: '+FloatToStr(Results['weight']));
TempList.Add('Balance: '+FloatToStr(Results['balance']));
TempList.Add('Date Added: '+DateTimeToStr(Results['date_added']));
TempList.Add('Date Played: '+DateTimeToStr(Results['date_played']));
TempList.Add('Date Artist Played: '+DateTimeToStr(Results['date_artist_played']));
TempList.Add('Date Album Played: '+DateTimeToStr(Results['date_album_played']));
TempList.Add('Date Title Played: '+DateTimeToStr(Results['date_title_played']));
TempList.Add('Duration: '+FloatToStr(Results['Duration']));
TempList.Add('Artist: '+Results['artist']);
TempList.Add('Title: '+Results['title']);
TempList.Add('Album: '+Results['album']);
TempList.Add('Label: '+Results['label']);
TempList.Add('PLine: '+Results['pline']);
TempList.Add('Track No: '+FloatToStr(Results['trackno']));
TempList.Add('Composer: '+Results['composer']);
TempList.Add('ISRC: '+Results['ISRC']);
TempList.Add('Catalog: '+Results['Catalog']);
TempList.Add('UPC: '+Results['UPC']);
TempList.Add('Fee Agency: '+Results['feeagency']);
TempList.Add('Album Year: '+Results['albumyear']);
TempList.Add('Genre: '+Results['genre']);
TempList.Add('Website: '+Results['website']);
TempList.Add('Buy CD: '+Results['buycd']);
TempList.Add('Info: '+Results['info']);
TempList.Add('Lyics: '+Results['lyrics']);
TempList.Add('Picture: '+Results['picture']);
TempList.Add('Count Played: '+FloatToStr(Results['count_played']));
TempList.Add('Count Requested: '+FloatToStr(Results['count_requested']));
TempList.Add('Last Requested: '+DateTimeToStr(Results['last_requested']));
TempList.Add('Count Performances: '+FloatToStr(Results['count_performances']));
TempList.Add('Crossfade: '+Results['xfade']);
TempList.Add('BPM: '+FloatToStr(Results['bpm']));
TempList.Add('Mood: '+Results['mood']);
TempList.Add('Rating: '+FloatToStr(Results['rating']));
TempList.Add('Overlay: '+Results['overlay']);
TempList.Add('Playlimit Count: '+FloatToStr(Results['playlimit_count']));
TempList.Add('Playlimit Action: '+Results['playlimit_action']);
TempList.Add('Songrights: '+Results['songrights']);
TempList.Add(' ');
TempList.Add('- - - - - End of Record - - - - -');
TempList.SaveToFile(FilePath);
TempList.Clear;
Results.Next;
end;
end;
end;
Option 3 - Searching by Album
Again, no new keywords are introduced int Option 3 and the only difference is the output which lists to both the IDE output window and the text file, the names of the tracks found from the album searched for:
// Query based on album (Option 3) ----------------------------------------------------------------
If Option = 3 then
begin
SearchAlbum := InputBox('Album Search','Enter the album name to search for...','');
Filepath := Filepath+SearchAlbum+'.txt';
WriteLn('Tracks for Album: '+SearchAlbum);
WriteLn(' ');
TempList.Add('Tracks for Album: '+SearchAlbum);
TempList.Add(' ');
if SearchAlbum <> '' then
begin
Results := Query('SELECT * FROM songlist WHERE album = :AlbumName',[SearchAlbum],False);
while not Results.EOF do
begin
TrackName := Results['artist']+' - '+Results['title'];
TrackName := FloatToStr(TrkCount)+'. '+TrackName;
WriteLn(TrackName);
TempList.Add(TrackName);
TrkCount := TrkCount + 1;
TrkTotal := TrkTotal + 1;
Results.Next;
end;
end;
WriteLn(' ');
WriteLn('Total Tracks found for album '+SearchAlbum+': '+FloatToStr(TrkTotal));
WriteLn(' ');
WriteLn('- - - - - End of Record - - - - -');
WriteLn(' ');
TempList.Add(' ');
TempList.Add('Total Tracks found for album '+SearchAlbum+': '+FloatToStr(TrkTotal));
TempList.Add(' ');
TempList.Add('- - - - - End of Record - - - - -');
TempList.SaveToFile(FilePath);
TempList.Clear;
end;
Option 4 - Searching by a Single Year
The same code is reused to list all tracks which have the required album year:
// Query based on single year (Option 4) ----------------------------------------------------------
If Option = 4 then
begin
StartYear := StrToInt(InputBox('Search Year','Please enter the year to search',''));
ReqYear := StartYear;
WriteLn('Tracks for Year: '+FloatToStr(ReqYear));
WriteLn(' ');
TempList.Add('Tracks for Year: '+FloatToStr(ReqYear));
TempList.Add(' ');
Filepath := Filepath+FloatToStr(ReqYear)+'.txt';
Results := Query('SELECT * FROM songlist WHERE albumyear = :Dated',[ReqYear],False);
Results.First;
while not Results.EOF do
begin
TrackName := Results['artist']+' - '+Results['title'];
TrackName := FloatToStr(TrkCount)+'. '+TrackName;
WriteLn(TrackName);
TempList.Add(TrackName);
TrkCount := TrkCount + 1;
TrkTotal := TrkTotal + 1;
Results.Next;
end;
WriteLn(' ');
WriteLn('Total Number of Tracks Found for Year '+FloatToStr(ReqYear)+': '+FloatToStr(TrkTotal));
WriteLn(' ');
WriteLn('- - - - - End of Record - - - - -');
WriteLn(' ');
TempList.Add(' ');
TempList.Add('Total Number of Tracks Found: '+FloatToStr(TrkTotal));
TempList.Add(' ');
TempList.Add('- - - - - End of Record - - - - -');
TempList.SaveToFile(FilePath);
TempList.Clear;
end;
Option 5 - Searching by a Range of Years
This section differs slightly in it runs Option 4 for each of the required years. It also prompts the user for two years, a year to start searching from and a year to search up to. For each of these years, in simple terms, Option 4 is run however, there are a number of lines which either increment or reset a specific variable. The results from each year is written to its own text file. There is very little error checking and if you search for the same years multiple times, the existing files will be overwritten:
// Query based on a range of years (Option 5) -----------------------------------------------------
If Option = 5 then
begin
StartYear := StrToInt(InputBox('Starting Year','Please enter the year to search from',''));
EndYear := StrToInt(InputBox('End Year','Please enter the year to search up to',''));
ReqYear := StartYear;
While (EndYear > StartYear) AND (ReqYear <= EndYear) do
begin
WriteLn('Tracks for Year: '+FloatToStr(ReqYear));
WriteLn(' ');
TempList.Add('Tracks for Year: '+FloatToStr(ReqYear));
TempList.Add(' ');
Filepath := Filepath+FloatToStr(ReqYear)+'.txt';
Results := Query('SELECT * FROM songlist WHERE albumyear = :Dated',[ReqYear],False);
Results.First;
while not Results.EOF do
begin
TrackName := Results['artist']+' - '+Results['title'];
TrackName := FloatToSTr(TrkCount)+'. '+TrackName;
WriteLn(TrackName);
TempList.Add(TrackName);
TrkCount := TrkCount + 1;
TrkTotal := TrkTotal + 1;
Results.Next;
end;
WriteLn(' ');
WriteLn('Total Number of Tracks Found for '+FloatToStr(ReqYear)+': '+FloatToStr(TrkCount - 1));
WriteLn(' ');
WriteLn('- - - - - End of Record - - - - -');
WriteLn(' ');
TempList.Add(' ');
TempList.Add('Total Number of Tracks Found for '+FloatToStr(ReqYear)+': '+FloatToStr(TrkCount - 1));
TempList.Add(' ');
TempList.Add('- - - - - End of Record - - - - -');
TempList.Add(' ');
TempList.SaveToFile(FilePath);
TempList.Clear;
ReqYear := ReqYear + 1;
TrkCount := 1;
end;
end;
Option 6 - Searching by Track Type
This option will produce a list of all tracks, including their unique identifier based on the track category. This could easily be augmented to output additional track data fields as necessary.
// Query to List the tracks in the library based on category type (Option 6) -----------------------------------------------------
If Option = 6 then
begin
CatType := InputBox('Category Search','Enter the category type to search for...(S, I, P, J, N, V, X, C, ?)','');
Filepath := Filepath+CatType+'.txt';
If CatType='?' Then
FilePath := GetCurrentDir+'\Output\QuestionMark'+'.txt';
WriteLn('Tracks for Category: '+CatType);
WriteLn(' ');
TempList.Add('Tracks for Category: '+CatType);
TempList.Add(' ');
if CatType <> '' then
begin
Results := Query('SELECT * FROM songlist WHERE songtype = :CatType',[CatType],False);
while not Results.EOF do
begin
TrackName := FloatToStr(Results['ID'])+' - '+Results['artist']+' - '+Results['title'];
TrackName := FloatToStr(TrkCount)+'. '+TrackName;
WriteLn(TrackName);
TempList.Add(TrackName);
TrkCount := TrkCount + 1;
TrkTotal := TrkTotal + 1;
Results.Next;
end;
end;
WriteLn(' ');
WriteLn('Total Tracks found for Category '+CatType+': '+FloatToStr(TrkTotal));
WriteLn(' ');
WriteLn('- - - - - End of Record - - - - -');
WriteLn(' ');
TempList.Add(' ');
TempList.Add('Total Tracks found for Category '+CatType+': '+FloatToStr(TrkTotal));
TempList.Add(' ');
TempList.Add('- - - - - End of Record - - - - -');
TempList.SaveToFile(FilePath);
TempList.Clear;
end;
End
The end of a program or script is usually where we tidy up any loose ends. You can see that we return the PAL Script speed to its normal 1 line per second speed and we release or free up the memory used by the database query. As we Clear the list of strings at the end of each option, there is no need to also add TempList.Free to the end of the script.
// End of Main ------------------------------------------------------------------------------------
PAL.UnLockExecution; // Return PAL Script processing to normal speed
// Housekeeping
Results.Free;
Comments