// 2014 Winter MAT 259 Data Visualization
// Lab 4. 2D Space - saveQueryAsTextEasily
// Implemented by Joshua Dickinson, updated by Yoon Chung Han
// Works both Processing ver.1.5.1 & ver.2

//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.*;
import java.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

String [] manganames = {
  "naruto",
  "bleach", 
  "one piece", 
  "fairy tail", 
  "dengeki daisy", 
  "skip beat", 
  "vampire knight", 
  "black bird", 
  "soul eater", 
  "rosariovampire" 
 };

void setup() {
   // connecting to the TANGO database
   msql = new MySQL( this, "tango.mat.ucsb.edu", "spl0", "mat259", "V1sual1zat1on" ); //host, database, user, password
   //for (int i2=0;i2<manganames.length; i2++) {
    if ( msql.connect() )
    { 
     for (int i2=0;i2<manganames.length; i2++) {
      String[] textArray = new String[1]; 
      //String TheQuery="select month(cout), count(*) from inraw where cout > '2005-01-01' and title like '%politic%' group by year(cout), month(cout) order by year(cout), month(cout);";
      String TheQuery= String.format("SELECT year(cout),month(cout),count(*) FROM spl2.inraw where (title like '%%%s%%' and itemtype='acbk') and (year(cout)>='2011' and year(cout)<'2014') and deweyClass = '741.5952' group by year(cout),month(cout) limit 0,1000000",manganames[i2]);
          println(TheQuery); //print the query in the first line
          msql.query( TheQuery); // do mysql query

          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. 
              ResultSet rs = msql.result; // what is ResultSet?
              int numOfColumns = 0;
              String result = "";

              try{
                ResultSetMetaData rsmd = rs.getMetaData(); // ??
                numOfColumns = rsmd.getColumnCount(); // get the number of cols
              }
              catch(Exception e){
                println("error");
              }

              
              for(int i=1; i<= numOfColumns; i++){ // i=1 since 1st line already printed
                if(i != numOfColumns)
                  result = result + msql.getString(i) + DL; // when it hits the last col, add a DL ","
                else
                  result += msql.getString(i); // when it hits the last col, 
              }    
              
              textArray = append(textArray, result);  // append automatically does new line
          }
     String savestr = String.format("../query/%s.txt",manganames[i2]);
     saveStrings(savestr, textArray);
     }
     }
    else
    {
      println(" connection failed !");
    }
    //saveStrings("rosario.txt", textArray);
    //String savestr = String.format("%s.txt",manganames[i2]);
    //saveStrings(savestr, textArray);
   //}// i2
}

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], ",");
    println("we need to cast numbers as either int or float: " + Integer.parseInt(temp[0]));
    println("a string doesn't need to be cast: " + temp[1]);

  }   
  
*/




