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

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;

    //project id - it should be required right?
    //if they omit, the query would come up empty.
    @Option(names = {"--projectID"}, description = "ID number of the project")
    String projectID;

    @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, projectID, 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 projectID, 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


        /* Step 1: retrieve all the content items associated with the given projectId */
        
            String sql = "SELECT name, id, typeId, created from td_ContentItem where projectId="+projectID;

        // consider usingLIMIT to get the returned results in controled batch size, rather than getting
        // all the results at once.
        
            ResultSet myResult = stmt.executeQuery(sql);
            ResultSetMetaData resultMetaData = myResult.getMetaData();

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


        /* Step 2: Iterate through each content item, and retrieve the media files that content item has */
        
        /* The following is an example SELECT statement (for a hard-wired contentId = 333) that retrieves all the
           images (both the original name, and the hashed 'path' that maps to the media files on the file
           system (the contentItem itself at the time of writing was in projectID=4) */

        /*
SELECT mFile.filename, mFile.path 

FROM td_ManagedFile AS mFile, td_ContentItem AS cItem, td_ContentItem_MediaItem AS cMediaItem, td_MediaItem AS mItem

WHERE

cItem.id = 333

And cMediaItem.contentItemId = cItem.id

and cMediaItem.mediaItemId = mItem.id

And mItem.fileId = mFile.id
        */


        
            //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);
        
        
                //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;
    }
}