package test;

import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import java.io.IOException;
import util.JdbcUtil;

public class SparkSQLTest {
   static int i = 0;

   public static void main(String[] args) throws IOException {
      String content = "{\n      \"ip_port\": \"192.168.245.30:10000\",\n      \"url\": \"jdbc:hive2://192.168.245.30:10000\",\n      \"username\": \"root\",\n      \"password\": \"root\",\n      \"dbType\": \"sparksql\",\n      \"excludeSchema\": []\n    }";
      JdbcUtil.initData(content);
      System.in.read();
      testCacheTable();
   }

   static void testCacheTable() {
      print("CACHE TABLE testCache OPTIONS ('storageLevel' 'DISK_ONLY') SELECT * FROM testData;");
   }

   static void testAlterDatabase() {
      print("ALTER DATABASE inventory SET DBPROPERTIES ('Edited-by' = 'John', 'Edit-date' = '01/01/2001');");
      print("ALTER DATABASE inventory SET LOCATION 'file:/temp/spark-warehouse/new_inventory.db';");
      print("ALTER DATABASE inventory SET LOCATION 'file:/temp/spark-warehouse/new_inventory.db';");
   }

   static void testAlterTable() {
      print("ALTER TABLE default.StudentInfo PARTITION (age='10') RENAME TO PARTITION (age='15');");
      print("ALTER TABLE StudentInfo ADD columns (LastName string, DOB timestamp);");
      print("ALTER TABLE StudentInfo DROP columns (LastName, DOB);");
      print("ALTER TABLE StudentInfo RENAME COLUMN name TO FirstName;");
      print("ALTER TABLE StudentInfo ALTER COLUMN FirstName COMMENT \"new comment\";");
      print("ALTER TABLE StudentInfo REPLACE COLUMNS (name string, ID int COMMENT 'new comment');");
      print("ALTER TABLE StudentInfo ADD IF NOT EXISTS PARTITION (age=18);");
      print("ALTER TABLE StudentInfo DROP IF EXISTS PARTITION (age=18);");
      print("ALTER TABLE StudentInfo ADD IF NOT EXISTS PARTITION (age=18) PARTITION (age=20);");
      print("ALTER TABLE loc_orc SET fileformat orc;\n");
      print("ALTER TABLE p1 partition (month=2, day=2) SET fileformat parquet;");
      print("ALTER TABLE dbx.tab1 PARTITION (a='1', b='2') SET LOCATION '/path/to/part/ways'");
      print("ALTER TABLE test_tab SET SERDE 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe';");
      print("ALTER TABLE dbx.tab1 SET SERDE 'org.apache.hadoop' WITH SERDEPROPERTIES ('k' = 'v', 'kay' = 'vee')");
      print("ALTER TABLE dbx.tab1 SET TBLPROPERTIES ('winner' = 'loser');");
      print("ALTER TABLE dbx.tab1 SET TBLPROPERTIES ('comment' = 'A table comment.');");
      print("ALTER TABLE dbx.tab1 SET TBLPROPERTIES ('comment' = 'This is a new comment.');");
      print("ALTER TABLE dbx.tab1 UNSET TBLPROPERTIES ('winner');");
      print("ALTER TABLE dbx.tab1 RECOVER PARTITIONS;");
      print("");
      print("");
      print("");
      print("");
      print("");
      print("");
      print("");
      print("");
      print("");
      print("");
      print("");
      print("");
      print("");
   }

   static void testAlterview() {
      print("ALTER VIEW tempdb1.v1 RENAME TO tempdb1.v2;");
      print("ALTER VIEW tempdb1.v2 SET TBLPROPERTIES ('created.by.user' = \"John\", 'created.date' = '01-01-2001' );");
      print("ALTER VIEW tempdb1.v2 UNSET TBLPROPERTIES ('created.by.user', 'created.date');");
      print("ALTER VIEW tempdb1.v2 AS SELECT * FROM tempdb1.v1;");
   }

   static void testCreateDatabase() {
      print("CREATE DATABASE customer_db;");
      print("CREATE DATABASE IF NOT EXISTS customer_db;");
      print("CREATE DATABASE IF NOT EXISTS customer_db LOCATION '/user'    WITH DBPROPERTIES (ID=001, Name='John');");
      print("CREATE DATABASE IF NOT EXISTS customer_db LOCATION '/user'    ");
      print("CREATE DATABASE IF NOT EXISTS customer_db COMMENT 'This is customer database' LOCATION '/user'    WITH DBPROPERTIES (ID=001, Name='John');");
      print("CREATE DATABASE IF NOT EXISTS customer_db COMMENT 'This is customer database' LOCATION '/user'   ");
      print("CREATE DATABASE IF NOT EXISTS customer_db COMMENT 'This is customer database'   WITH DBPROPERTIES (ID=001, Name='John');");
      print("CREATE DATABASE IF NOT EXISTS customer_db COMMENT 'This is customer database' ");
   }

   static void otherTest() {
      print("EXPLAIN select k, sum(v) from values (1, 2), (1, 3) t(k, v) group by k;");
      print("EXPLAIN EXTENDED select k, sum(v) from values (1, 2), (1, 3) t(k, v) group by k;");
      print("");
      print("");
      print("");
      print("");
      print("");
   }

   static void ddlTest() {
   }

   static void testIdentifier() {
      print("SELECT 123.BD AS col;");
      print("SELECT -5BD AS col;");
      print("SELECT 12.578e-2d AS col;");
      print("SELECT -.1234567E+2BD AS col;");
      print("");
      print("");
      print("");
      print("");
      print("");
   }

   static void testLoad() {
      print("LOAD DATA LOCAL INPATH '/user/hive/warehouse/students' OVERWRITE INTO TABLE test_load;");
      print("LOAD DATA LOCAL INPATH '/user/hive/warehouse/test_partition/c2=2/c3=3'\n    OVERWRITE INTO TABLE test_load_partition PARTITION (c2=2, c3=3)");
      print("");
      print("");
      print("");
   }

   static void testInsertOverriteDirectory() {
      print("INSERT OVERWRITE DIRECTORY '/tmp/destination'\n    USING parquet\n    OPTIONS (col1 1, col2 2, col3 'test')\n    SELECT * FROM test_table");
      print("INSERT OVERWRITE DIRECTORY\n    USING parquet\n    OPTIONS ('path' '/tmp/destination', col1 1, col2 2, col3 'test')\n    SELECT * FROM test_table;");
      print("INSERT OVERWRITE LOCAL DIRECTORY '/tmp/destination'\n    STORED AS orc\n    SELECT * FROM test_table;");
      print("INSERT OVERWRITE LOCAL DIRECTORY '/tmp/destination'\n    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','\n    SELECT * FROM test_table;");
      print("");
      print("");
   }

   static void testInsert() {
      print("INSERT INTO students VALUES ('Amy Smith', '123 Park Ave, San Jose', 111111);");
      print("INSERT INTO students VALUES\n    ('Bob Brown', '456 Taylor St, Cupertino', 222222),\n    ('Cathy Johnson', '789 Race Ave, Palo Alto', 333333);");
      print("INSERT INTO students PARTITION (student_id = 444444) SELECT name, address FROM persons WHERE name = \"Dora Williams\";");
      print("INSERT INTO students PARTITION (student_id = 444444, name='1111') SELECT name, address FROM persons WHERE name = \"Dora Williams\";");
      print("INSERT INTO students TABLE visiting_students;");
      print("INSERT INTO students FROM applicants SELECT name, address, id applicants WHERE qualified = true;");
      print("INSERT INTO students PARTITION (birthday = date'2019-01-02') VALUES ('Amy Smith', '123 Park Ave, San Jose');");
      print("INSERT INTO students (address, name, student_id) VALUES ('Hangzhou, China', 'Kent Yao', 11215016);");
      print("INSERT INTO students PARTITION (student_id = 11215017) (address, name) VALUES  ('Hangzhou, China', 'Kent Yao Jr.');");
      print("INSERT OVERWRITE students VALUES\n    ('Ashua Hill', '456 Erica Ct, Cupertino', 111111),\n    ('Brian Reed', '723 Kern Ave, Palo Alto', 222222);");
      print("INSERT OVERWRITE students PARTITION (student_id = 222222)  SELECT name, address FROM persons WHERE name = \"Dora Williams\";");
      print("INSERT OVERWRITE students TABLE visiting_students;");
      print("INSERT OVERWRITE students   FROM applicants SELECT name, address, id applicants WHERE qualified = true");
      print("INSERT INTO students PARTITION (birthday = date'2019-01-02') VALUES ('Amy Smith', '123 Park Ave, San Jose');");
      print("INSERT INTO students PARTITION (birthday = date'2019-01-02')  VALUES('Jason Wang', '908 Bird St, Saratoga');");
      print("INSERT OVERWRITE students (address, name, student_id) VALUES  ('Hangzhou, China', 'Kent Yao', 11215016);");
      print("INSERT OVERWRITE students PARTITION (student_id = 11215016) (address, name) VALUES  ('Hangzhou, China', 'Kent Yao Jr.');\n");
      print("");
   }

   static void testSelect() {
      print("SELECT * FROM test TABLESAMPLE (50 PERCENT)");
      print("SELECT * FROM test TABLESAMPLE (5 ROWS)");
      print("SELECT * FROM test TABLESAMPLE (BUCKET 4 OUT OF 10)");
      print("");
      print("");
   }

   static boolean print(String sql) {
      if (sql != null && sql.length() != 0) {
         ++i;
         System.out.println(i + "<<<<======================================================");
         System.out.println(sql);
         System.out.println("--------------------------------------------------------");
         boolean flag = true;

         try {
            for(SQLStatement statement : SQLUtils.parseStatements(sql, DbType.spark)) {
               statement.toString();
               System.out.println(statement);
            }
         } catch (Exception e) {
            flag = false;
            System.out.println(i + "" + e + "\n" + sql);
            e.printStackTrace();
         }

         System.out.println(i + ">>>>>======================================================");
         return flag;
      } else {
         return true;
      }
   }
}
