<?php
/*
 * This is a program demonstrating PDO for MySQL and SQLITE3
 * © COPYRIGHT 2012 Lanny Rosicky
 *
 * For this program to work, the databases and tables must exist
 */
 
 /* 
  * Modify these variables to suit your taste
  */
// Sqlite data base location
$sqltdbname = "/Users/lanny/Desktop/RSQLITE/itunes.db";
// dete base name and table assumed same for both
 
$mysqdbname = "itunes";
$tname = "allsongs";
// $whichDB is either sqlite3 or mysql - commemnt and uncomment to switch
$whichDB = "sqlite3";
// $whichDB = "mysql";
/* ******** stop modifying unless you insist ********* */
echo "<H1>$whichDB</H1>";
 
if ($whichDB == "mysql") {
    $connection = new pdo('mysql:host=localhost;dbname=itunes', 'rmysql', 'expert');
} else {
    $connection = new pdo("sqlite:$sqltdbname");
}
 
 
// House keep - initialize control variables . control control alternate line column and store column names.
$colorSwitch = false;
$cnames = '';
 
// start display table definition
echo '<table cellpadding=3 border=1>';
 
/* 
 * Formulate select statement.  The table columns are 
 
 
  Name varchar(255) ,
  Artist varchar(255) ,
  Composer varchar(255) ,
  Album varchar(255) ,
  Grouping varchar(255) ,
  Genre varchar(255) ,
  Size varchar(255) ,
  Time varchar(255) ,
  Disc Number varchar(255) ,
  Disc Count varchar(255) ,
  Track Number varchar(255) ,
  Track Count varchar(255) ,
  Year varchar(255) ,
  Date Modified datetime NOT NULL,
  Date Added datetime NOT NULL,
  Bit Rate varchar(255) ,
  Sample Rate varchar(255) ,
  Volume Adjustment varchar(255) ,
  Kind varchar(255) ,
  Equalizer varchar(255) ,
  Comments varchar(255) ,
  Plays varchar(255) ,
  Last Played varchar(255) ,
  Skips varchar(255) ,
  Last Skipped varchar(255) ,
  My Rating varchar(255) ,
  Location` varchar(255) 
 * 
 * In the sample SQL we use first few xolumns .
 
 */
$query = 'SELECT Name, Artist, Composer, Album, Genre, Size FROM allsongs order by Size DESC';
// execute sql
$result = $connection->query($query) or die("Error");
// loop through result
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
 
// This is perhaps not the best way to get a list of columns but it works for both db's    
    if (!$cnames) {
    // we do not have column names yet, get them    
        $cnames = array_keys($row);
        $colcount = count($cnames);
        // display a header row for our table
        echo '<tr style="background:lightblue">';
 
        for ($j = 0; $j < $colcount; $j += 1) {
            echo '<th>', $cnames[$j], '</th>';
        }
        echo '</tr>';
    }
    // data display - alternate row background color
    if ($colorSwitch) {
        echo '<tr style="background:lightblue">';
        $colorSwitch = false;
    } else {
        echo '<tr style="background:lightgrey">';
        $colorSwitch = true;
    }
 
    /*
     * $row which we gor above in
     * $row = $result->fetch(PDO::FETCH_ASSOC)
     * is an array in which the array key is the column name 
     * and the value is the column content. This is because we used FETCH_ASSOC
     * 
     * $cname is an array of column names with numeric key
     * So to get a column content we need to use its key. So we loop through the
     * list of columns in $cname and use each name in there to get data from $row
     */
 
    for ($j = 0; $j < $colcount; $j += 1) {
        // just a silly example of minimalistic db content processing
        // The original size is in bytes and I convert it to megabytes
        if ($cnames[$j] == 'Size') {
			$row[$cnames[$j]] = round($row[$cnames[$j]]/1048576,0).'M';
		}
                // Display column content into a table cell
		echo '<td>', $row[$cnames[$j]], '</td>';
    }
    echo '</tr>';
}
 
echo '</table>';
?>

# The following explains the use of commands in Terminal. The commands used 
# are sed, tr, and rm. sqlite3 is a program.
#
# The following  command does the following:
#      - runs sqlite3 and creates itunes.db if not present
#      - executes an sqlite3 command stored in all songs.ddl
#        which are commands to create empty table allsongs
 
sqlite3 itunes.db < allsongs.ddl

 
# The following command translates all carriage returns to new lines 
# to provide line ending understood by sqlite3 and creates a
# temporary file foo.txt which contains the result
tr '\r' '\n'  < Classical.txt > foo.txt
 
# The following command deletes the first line of the text file created by iTunes
# containing the column headers which we do not need. It puts the result back 
# into Classical.txt
 overriding the previous data

sed '1d' foo.txt > Classical.txt

 
# The following command deletes the temporary file
rm foo.txt
 
# The following command executes sqlite3 and populates table all songs in database iTunes.db
# The data is in file Classical.txt
 which was created by previous steps
#
#
# -separator is set to tab because that is the way iTunes generates the file
# .import is an sqlite3 command to load data from Classical.txt to table allsongs
sqlite3 -separator $'\t' itunes.db '.import Classical.txt allsongs'