import java.io.*;
import java.util.*;
import javax.swing.*;
import javax.swing.table.*;
import java.awt.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.*;
import java.util.Vector; 
   
// This class reads, writes and plot data into/from a given SQL Database table
// 		        
public class SQLite
{
// Reads given data column from a specified SQLite database table
// databasename : name of the database file program adds ".db" to the name
// tablei database table name	
// column[]: database column names

public static String[][] query_String(String databasename,String query) throws IOException,ClassNotFoundException
{  
    // load the sqlite-JDBC driver using the current class loader
    String a[][];
    int n=1,m=1;
    Class.forName("org.sqlite.JDBC");   
    Connection connection = null;
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:"+databasename+".db");
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30);  // set timeout to 30 sec.      
      //statement.executeUpdate("drop table if exists H9Ex3");
      ResultSet rs = statement.executeQuery(query);
      int i=0;
      ResultSetMetaData rsmd = rs.getMetaData();
      n=rsmd.getColumnCount();
      String column[]=new String[n];
      for (int h=0;h<n;h++)
      {column[h]=rsmd.getColumnName(h+1);}
      String x;
      while(rs.next())
      {x=rs.getString(column[0]);i++;}
      m=i;
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }    
    finally
    {
      try
      {
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
    a=new String[n][m];
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:"+databasename+".db");
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30);  // set timeout to 30 sec.      
      //statement.executeUpdate("drop table if exists H9Ex3");
      String s="select ";
      ResultSet rs = statement.executeQuery(query);
      ResultSetMetaData rsmd = rs.getMetaData();
      String column[]=new String[n];
      for (int h=0;h<n;h++)
      {
      column[h]=rsmd.getColumnName(h+1);
      }
      for(int i=0;i<m;i++)
      {rs.next();
       for(int j=0;j<n;j++)
        {   
	        a[j][i]=rs.getString(column[j]);
        }        
      }
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }    
    finally
    {
      try
      {
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
    return a;
}

public static double[][] query_double(String databasename,String query,boolean print) throws IOException,ClassNotFoundException
{  
    // load the sqlite-JDBC driver using the current class loader
    double a[][];
    String column[];
    int n=1,m=1;
    Class.forName("org.sqlite.JDBC");   
    Connection connection = null;
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:"+databasename+".db");
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30);  // set timeout to 30 sec.      
      //statement.executeUpdate("drop table if exists H9Ex3");
      ResultSet rs = statement.executeQuery(query);
      int i=0;
      ResultSetMetaData rsmd = rs.getMetaData();
      n=rsmd.getColumnCount();
      String column1[]=new String[n];
      for (int h=0;h<n;h++)
      {column1[h]=rsmd.getColumnName(h+1);}
      double x;
      while(rs.next())
      {x=rs.getDouble(column1[0]);i++;}
      m=i;
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }    
    finally
    {
      try
      {
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
    a=new double[n][m];
    column=new String[n];
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:"+databasename+".db");
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30);  // set timeout to 30 sec.      
      //statement.executeUpdate("drop table if exists H9Ex3");
      String s="select ";
      ResultSet rs = statement.executeQuery(query);
      ResultSetMetaData rsmd = rs.getMetaData();
      String column1[]=new String[n];
      for (int h=0;h<n;h++)
      {
      column1[h]=rsmd.getColumnName(h+1);
      }
      column=column1;
      for(int i=0;i<m;i++)
      {rs.next();
       for(int j=0;j<n;j++)
        {   
	        a[j][i]=rs.getDouble(column[j]);
        }        
      }
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }    
    finally
    {
      try
      {
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
    if(print) Text.printT(a,column);
    return a;
}

public static String[][] read_String(String databasename, String tablei,String column[]) throws IOException,ClassNotFoundException
{  
    // load the sqlite-JDBC driver using the current class loader
    String a[][];
    int n=1,m=1;
    Class.forName("org.sqlite.JDBC");   
    Connection connection = null;
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:"+databasename+".db");
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30);  // set timeout to 30 sec.      
      //statement.executeUpdate("drop table if exists H9Ex3");
      ResultSet rs = statement.executeQuery("select * from "+tablei);
      int i=0;
      n=column.length;
      String x;
      while(rs.next())
      {
        x=rs.getString(column[i]);
        i++;
      }
      m=i;
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }    
    finally
    {
      try
      {
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
    a=new String[n][m];
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:"+databasename+".db");
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30);  // set timeout to 30 sec.      
      //statement.executeUpdate("drop table if exists H9Ex3");
      String s="select ";
      ResultSet rs = statement.executeQuery("select * from "+tablei);
      for(int i=0;i<m;i++)
      {rs.next();
       for(int j=0;j<n;j++)
        {   
	        a[j][i]=rs.getString(column[j]);
        }        
      }
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }    
    finally
    {
      try
      {
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
    return a;
}

public static double[][] read_double(String databasename, String tablei,String column[]) throws IOException,ClassNotFoundException
{  
    // load the sqlite-JDBC driver using the current class loader
    double a[][];
    int n=1,m=1;
    Class.forName("org.sqlite.JDBC");   
    Connection connection = null;
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:"+databasename+".db");
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30);  // set timeout to 30 sec.      
      //statement.executeUpdate("drop table if exists H9Ex3");
      ResultSet rs = statement.executeQuery("select * from "+tablei);
      int i=0;
      n=column.length;
      double x;
      while(rs.next())
      {
        x=rs.getDouble(column[0]);
        i++;
      }
      m=i;
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }    
    finally
    {
      try
      {
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
    a=new double[n][m];
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:"+databasename+".db");
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30);  // set timeout to 30 sec.      
      //statement.executeUpdate("drop table if exists H9Ex3");
      String s="select ";
      ResultSet rs = statement.executeQuery("select * from "+tablei);
      for(int i=0;i<m;i++)
      {rs.next();
       for(int j=0;j<n;j++)
        {   
	        a[j][i]=rs.getDouble(column[j]);
        }        
      }
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }    
    finally
    {
      try
      {
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
    return a;
}

//Reads all data columns from a specified Lite database table
// databasename : name of the database file program adds ".db" to the name
// tablei database table name		
public static double[][] read_double(String databasename, String tablei) throws IOException,ClassNotFoundException
{  
	// load the sqlite-JDBC driver using the current class loader
    double a[][];
    int n=1,m=1;
    Class.forName("org.sqlite.JDBC");   
    Connection connection = null;
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:"+databasename+".db");
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30);  // set timeout to 30 sec.      
      //statement.executeUpdate("drop table if exists H9Ex3");
      ResultSet rs = statement.executeQuery("select * from "+tablei);
      ResultSetMetaData meta=rs.getMetaData();
      int colCount=meta.getColumnCount();
      String column[]=new String[colCount];
      for (int h=0;h<colCount;h++)
      {
      column[h]=meta.getColumnName(h+1);
      }
      int i=0;
      n=colCount;
      double x;
      while(rs.next())
      {
        x=rs.getDouble(column[0]);
        i++;
      }
      m=i;
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }    
    finally
    {
      try
      {
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
    a=new double[n][m];
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:"+databasename+".db");
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30);  // set timeout to 30 sec.      
      //statement.executeUpdate("drop table if exists H9Ex3");
      String s="select ";
      ResultSet rs = statement.executeQuery("select * from "+tablei);
      ResultSetMetaData meta=rs.getMetaData();
      int colCount=meta.getColumnCount();
      String column[]=new String[colCount];
      for (int h=0;h<colCount;h++)
      {
      column[h]=meta.getColumnName(h+1);
      }
      for(int i=0;i<m;i++)
      {rs.next();
       for(int j=0;j<n;j++)
        {   
	        a[j][i]=rs.getDouble(column[j]);
        }        
      }
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }    
    finally
    {
      try
      {
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
    return a;
}

//read given column numbers
public static double[][] read_double(String databasename, String tablei,int column_no[]) throws IOException,ClassNotFoundException
{   String column[]=new String[column_no.length];
    // load the sqlite-JDBC driver using the current class loader
    double a[][];
    int n=1,m=1;
    Class.forName("org.sqlite.JDBC");   
    Connection connection = null;
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:"+databasename+".db");
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30);  // set timeout to 30 sec.      
      //statement.executeUpdate("drop table if exists H9Ex3");
      ResultSet rs = statement.executeQuery("select * from "+tablei);
       ResultSetMetaData meta=rs.getMetaData();
      int i=0;
      n=column.length;
      double x;
      for (int h=0;h<column_no.length;h++)
      {column[h]=meta.getColumnName(column_no[h]);}
      
      while(rs.next())
      {
        x=rs.getDouble(column[0]);
        i++;
      }
      m=i;
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }    
    finally
    {
      try
      {
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
    a=new double[n][m];
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:"+databasename+".db");
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30);  // set timeout to 30 sec.      
      //statement.executeUpdate("drop table if exists H9Ex3");
      String s="select ";
      ResultSet rs = statement.executeQuery("select * from "+tablei);
      for(int i=0;i<m;i++)
      {rs.next();
       for(int j=0;j<n;j++)
        {   
	        a[j][i]=rs.getDouble(column[j]);
        }        
      }
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }    
    finally
    {
      try
      {
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
    return a;
}

//Reads all data columns from a specified Lite database table
// databasename : name of the database file program adds ".db" to the name
// tablei database table name		


public static void write_double(String databasename, String tablei,String column[],double a[][]) throws IOException,ClassNotFoundException
{  
    // load the sqlite-JDBC driver using the current class loader
    int n=1,m=1;
    int i=0,j=0;
    Class.forName("org.sqlite.JDBC");   
    Connection connection = null;
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:"+databasename+".db");
      Statement statement = connection.createStatement();
      String s="create table "+tablei+"(";
      n=column.length;
      m=a[0].length;
      for(i=0;i<n-1;i++)
      {s+=column[i]+" double, ";}
      s+=column[i]+" double)";
      statement.setQueryTimeout(30);  // set timeout to 30 sec.      
      statement.executeUpdate("drop table if exists "+tablei);
      statement.executeUpdate(s);      
      String s2="";
      for(j=0;j<a[0].length;j++)
      {s2="";
       for(i=0;i<a.length-1;i++)
       {s2+=""+a[i][j]+",";}
       s2+=""+a[i][j];
       String s3="insert into "+tablei+" values("+s2+");";
       statement.executeUpdate(s3);
      }  
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }    
    finally
    {
      try
      {
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
}

public static void write_double(String databasename, String tablei,double a[][]) throws IOException,ClassNotFoundException
{  
    // load the sqlite-JDBC driver using the current class loader
    int n=1,m=1;
    int i=0,j=0;
    Class.forName("org.sqlite.JDBC");   
    Connection connection = null;
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:"+databasename+".db");
      Statement statement = connection.createStatement();
      String s="create table "+tablei+"(";
      n=a.length;
      m=a[0].length;
      for(i=0;i<n-1;i++)
      {s+=" x"+i+" double, ";}
      s+=" x"+i+" double)";
      statement.setQueryTimeout(30);  // set timeout to 30 sec.      
      statement.executeUpdate("drop table if exists "+tablei);
      statement.executeUpdate(s);      
      String s2="";
      for(j=0;j<a[0].length;j++)
      {s2="";
       for(i=0;i<a.length-1;i++)
       {s2+=""+a[i][j]+",";}
       s2+=""+a[i][j];
       String s3="insert into "+tablei+" values("+s2+");";
       statement.executeUpdate(s3);
      }  
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }    
    finally
    {
      try
      {
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
}

// write data into a given database table, table is already existed previously
// a new table is not created
// databasename : name of the database file program adds ".db" to the name
// tablei database table name	
// column[]: database column names
// a[][] double data

public static void add_double(String databasename, String tablei,String column[],double a[][]) throws IOException,ClassNotFoundException
{  
    // load the sqlite-JDBC driver using the current class loader
    int n=1,m=1;
    int i=0,j=0;
    Class.forName("org.sqlite.JDBC");   
    Connection connection = null;
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:"+databasename+".db");
      Statement statement = connection.createStatement();     
      String s2="";
      for(j=0;j<a[0].length;j++)
      {s2="";
       for(i=0;i<a.length-1;i++)
       {s2+=""+a[i][j]+",";}
       s2+=""+a[i][j];
       String s3="insert into "+tablei+" values("+s2+");";
       statement.executeUpdate(s3);
      }  
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }    
    finally
    {
      try
      {
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
}

public static Plot plot(String databasename, String tablei) throws IOException,ClassNotFoundException
{
double a[][]=read_double(databasename,tablei);
Plot pp=new Plot(a);
return pp;
}
public static Plot plot(String databasename, String tablei,String column[],int ix,int iy) throws IOException,ClassNotFoundException
{
double a[][]=read_double(databasename,tablei);
double x[]=a[ix];
double y[]=a[iy];
Plot pp=new Plot(x,y);
pp.setPlabel(tablei);  
pp.setXlabel(column[ix]);  
pp.setYlabel(column[iy]);  
return pp;
}

public static Plot plot(String databasename, String tablei,String column[]) throws IOException,ClassNotFoundException
{
double a[][]=read_double(databasename,tablei,column);
Plot pp=new Plot(a);
return pp;
}

}
