source: gs2-extensions/tipple-bridge/trunk/src/src/main/java/sqlCli.java@ 34901

import com.mysql.cj.exceptions.MysqlErrorNumbers;
import picocli.CommandLine;
import picocli.CommandLine.Command;
import picocli.CommandLine.Option;
import picocli.CommandLine.Parameters;

import java.sql.*;
import java.util.HashMap;
import com.google.gson.Gson;
import java.io.FileWriter;
import java.util.concurrent.Callable;

/*
TODO:

 */

//-u su41Tipple -p --port 3306 --dbname koicarp_test
//@/home/su41/configFileTest

/*
The mixinStandardHelpOptions = true adds in --help and --version options
 */
@Command (name="sqlCli", description = "Arguments for database connection", mixinStandardHelpOptions = true)
public class sqlCli implements Callable<Integer> {

    //our cli arguments
    @Option(names = {"-u", "--user"}, description = "The user name",
            required = true)
    String userName;

    //interactive allows the user to omit the argument value and instead enter it as a prompt
    /*
    NOTE: if you are running an interactive option on an IDE, most likely the IDE will be using
    the javaw.exe library which tries to mimic the terminal.
    The point is that interactive option would usually make whatever you enter hidden (it doesn't
    echo back) but on the IDE it will echo back.
     */
    @Option(names = {"-p", "--password"}, description = "User's password", interactive = true,
            required = true)
    String password;

    @Option(names = {"--port"},  description="Port number", defaultValue="3306")
    int port;

    @Option(names = {"--dbname"}, description="Name of the database", defaultValue="koicarp_test")
    String dbName;

    @Option(names = {"--host"}, description = "Type of host e.g. localhost ",
            required = true, defaultValue = "localhost")
    String host;

    /*
        this parameter can be read in at any point on the options line.
        but that's only because it's the only parameter at the moment.
        If we add more parameters, will have to start indexing
        https://picocli.info/quick-guide.html#_options_and_parameters
         */
    @Parameters(description = "Name of the output JSON file. E.g. yourFileName.json")
    String jsonOutputFile;

    public Integer call(){
        //if it doesn't end with ...
        if(!jsonOutputFile.endsWith(".json")){
            System.out.println("Invalid file format.");
            System.out.println("The file to save the output to must be a .json file.");
            //exit code other than 0 means it's not fine.
            return 1;
        }
        //else we're good to go.
       // databaseConnect(userName, password, port, dbName, projectID, jsonOutputFile, host);
        int status = databaseConnect(userName, password, port, dbName, jsonOutputFile, host);
        return status;
    }

    public static void main(String... args) {
        int exitCode = new CommandLine(new sqlCli()).execute(args);
        System.exit(exitCode);
    }


    private static int databaseConnect(String user, String pass, int port, String dbName,
                                         String fileName, String host){
        Connection conn;
        Statement stmt;
        //return status for code to send back to commandLine.execute
        int returnStatus = 0;
       // String dbUrl = "jdbc:mysql://localhost:" + port +"/"+ dbName;

        //TODO: are we changing / putting the type of database into a variable too?
        String dbUrl = "jdbc:mysql://" + host + ":" + port +"/"+ dbName;

        //originally in the try ()
        // FileWriter file = new FileWriter("tippleData.json")
    System.out.println("Saving output to:");
    System.out.println("  " + fileName);

        try (FileWriter file = new FileWriter(fileName)){
            //register the jdbc driver
            Class.forName("com.mysql.cj.jdbc.Driver");
            //opening the connection
            System.out.println("Connecting to database:");
            System.out.println("  " + dbUrl);
        
            conn = DriverManager.getConnection(dbUrl, user, pass);

            //create and execute query
            stmt = conn.createStatement();
            //remember to put in the projectId parameter instead of hardcoding it

            /*
            if we get up to the point where we allow boundary setting, i.e  user specifies
            between x and y number of records
            could use an if statement to check
            if the paramter exists or not, if it does then run a statement with those x and y
            if not, then run a different statement without them
             */

           // String sql = "SELECT name, id, typeId, created from td_ContentItem where projectId="+projectID;

            String sql = "SELECT  cItem.name, cItem.created, mFile.path\n" +
                    "FROM td_ManagedFile AS mFile, td_ContentItem AS cItem, td_ContentItem_MediaItem AS cMediaItem, td_MediaItem AS mItem\n" +
                    "WHERE\n" +
                    "cItem.id = cMediaItem.contentItemId \n" +
                    "and cMediaItem.mediaItemId = mItem.id\n" +
                    "And mItem.fileId = mFile.id\n" +
                    "ORDER BY mFile.projectId;\n";
            ResultSet myResult = stmt.executeQuery(sql);
            ResultSetMetaData resultMetaData = myResult.getMetaData();

            int dbColumn = resultMetaData.getColumnCount();
            System.out.println(" Number of columns in table retrieved: " + dbColumn);

            //create gson object and have the file write the opening bracket for valid json
            Gson myGSon = new Gson();
            file.write("[");

            while(myResult.next()){
                //string/object as key/value pair
                HashMap<String, Object> dbRow = new HashMap<String, Object>(dbColumn);

                for(int i = 1; i <= dbColumn; i++){
                    /*
                    doing checking this way could mean we have to have a case
                    for every data type,
                    if a data type changes in the database that's probably fine, but
                    if a new data type is sent in, we'll have update the statements
                     */
                    /*
                    Without this kind of check though, the output would throw a pretty nasty
                    warning about illegal reflective access. Though it would still print the
                    date out fine. The warning:

                    WARNING: An illegal reflective access operation has occurred
                    WARNING: Illegal reflective access by com.google.gson.internal.bind.ReflectiveTypeAdapterFactory (file:/home/su41/.gradle/caches/modules-2/files-2.1/com.google.code.gson/gson/2.7/751f548c85fa49f330cecbb1875893f971b33c4e/gson-2.7.jar) to field java.time.LocalDateTime.date
                    WARNING: Please consider reporting this to the maintainers of com.google.gson.internal.bind.ReflectiveTypeAdapterFactory
                    WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
                    WARNING: All illegal access operations will be denied in a future release
                     */
                    if(resultMetaData.getColumnTypeName(i).equals("DATETIME")){
                        dbRow.put(resultMetaData.getColumnName(i), myResult.getDate(i));
                    }
                    else{
                        dbRow.put(resultMetaData.getColumnName(i), myResult.getObject(i));
                    }
                }
               String dbRowAsJson = myGSon.toJson(dbRow);

                    //printing statements for checking output
               // System.out.println("As normal: " + dbRow);
               // System.out.println("====");
                //System.out.println("As JSON: " + anObject);

                //saving to json file

                file.write(dbRowAsJson);

                //Adding commas between each row that we write to our file, as we need it to be valid json
                if(!myResult.isLast()){
                    file.write(",");
                }
            }
            //write the closing bracket, and flush the writer
            file.write("]");
            file.flush();
            file.close();
            //System.out.println(myList);
            System.out.println("Closing connection...");
            myResult.close();
            stmt.close();
            conn.close();
            System.out.println("Connection closed");
        }
        catch(SQLException sqlError){
            // looking at the sql error code is good for debugging.
            // can ref: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-error-sqlstates.html
            // to see what they're for

           // System.out.println("MySQL error code: " + sqlError.getErrorCode());


            //prints out the error message from sql, then check to see which error code
            // came back and what the code matches.
            // if it's access denied, usually something to do with username, password or db name
            System.out.println(sqlError.getMessage());

           if(sqlError.getErrorCode() == MysqlErrorNumbers.ER_ACCESS_DENIED_ERROR){
               System.out.println("Error with database credentials. Double check your: ");
               System.out.println("    -'Username'");
               System.out.println("    -'Password' ");
            }

            if(sqlError.getErrorCode() == MysqlErrorNumbers.ER_DBACCESS_DENIED_ERROR){
                System.out.println("Error with database credentials. Double check your: ");
                System.out.println("    -'Database name' ");
            }
           returnStatus = 1;

        }
        catch(Exception e){
            //catch for class.name
            e.printStackTrace();
            //print out wrong username or password instead of the stack trace?
            System.out.println("encountered error");
            returnStatus = 2;
        }
        return returnStatus;
    }
}