// 2014 Winter MAT 259 Data Visualization
// Lab 3. SaveQueryAsText
// Works fine with both Processing ver. 1.5.1 & 2.
// Implemented by Joshua Dickinson, updated by Yoon Chung Han

//import SQL Library. Please download here(http://bezier.de/processing/libs/sql/)
//and move the SQLibrary folder into your libraries folder. 
// (Please follow the detailed instruction from the website) 
import de.bezier.data.sql.*;

//THIS is a simple sketch that saves the result of a MySQL query as a text file so that we can load into a visualization sketch to avoid having to query the server every time we start it.  
//It will make things run much faster so I suggest doing this whenever you've decided on the exact query you want to use for a visualization.  

MySQL msql;
String[] textArray = new String[1];
String DL = ","; //the DELIMITER

void setup() {
    // connecting to the TANGO database
    msql = new MySQL( this, "tango.mat.ucsb.edu", "spl2", "mat259", "V1sual1zat1on" ); //host, database, user, password
    if ( msql.connect() )
    { 
      String TheQuery="select floor(deweyClass/10) as dewey, count(*) from inraw where deweyClass < 1000 and cout > '2011-08-11' and cout < '2011-08-12' group by dewey order by dewey;";
          println(TheQuery);
          msql.query( TheQuery);

          textArray[0] = TheQuery;
          while( msql.next() ){
              //always use msql.getString instead of getInt or getFloat, because we're just saving this to a text file.  
              //We'll cast them as floats or ints later when we import it into our actual visualization. 
              
              textArray = append(textArray, msql.getString(1) + DL + msql.getString(2)); //separate each column by the "^^" delimiter so that we can parse the file later on.  
          }     
     }
    else
    {
      println(" connection failed !");
    }
    saveStrings("savedData.txt", textArray);
}

void draw() {
  exit();
}


/* This is how you can retrieve the data.  Put this code into your visualization file probably in the setup.  Make sure the savedData.txt file is also in its folder:
 
  String savedData[] = loadStrings("savedData.txt");
  println("QUERY: " + savedData[0]);
  for (int i=1; i < savedData.length; i++) {
    
    String temp[] = split(savedData[i], "^^");
    transactionsPerHour[Integer.parseInt(temp[0])] = Integer.parseInt(temp[1]);
    
    //transactionsPerHour[msql.getInt(1)] = msql.getInt(2);
  }   
  
*/