!------------------------------------------------------------------------------!
! Stand-alone macro test for NMDB.
!
! Arguments: dbtype - Type of database to test with
!
! Note: Default mode only uses the HSQLDB database, since that is the JAR file
!       that is included with this option tree.
!
! Note2: To test with Apache Derby (also rebranded as JavaDB by Sun),
!        add derby.jar to the jars area or add it to the CLASSPATH.
!
! Note3: To test with MySQL, you will need to updated the database connection
!        settings for your environment and mysql-connector.jar under jars area
!        or in CLASSPATH.
!
! @bug     NXM-1239 DATABASE: Need easy Oracle DB Interface
! @bug     NXM-1755 DATABASE: Derby jar throwing build warnings
! @bug     NXM-1825 NMDB;DATABASE TABLE DESCRIBE, EXIT and other enhancements
! @bug     NXM-1967 NMDB: Valid SQL does not work (ROUND)
! @since   NeXtMidas 2.7.0
! @author: J. Bauer / Neon Ngo
!------------------------------------------------------------------------------!
startmacro s:dbtype[""]
  switch INTERACTIVE interAct GET DEF=TRUE  SDEF=TRUE  ! for normal interactive test(s)
  switch DEBUG       debug    GET DEF=FALSE SDEF=TRUE

  set z:classDbDriverFound false
  set expectedRootCauseHsqldb "org/hsqldb/jdbcDriver has been compiled by a more recent version of the Java Runtime"

  if dbtype NEQS "" then
    if dbtype EQS "derby" then
      invoke classDbDriver nxm.sys.lib.ReflectUtil.getClassForName(org.apache.derby.jdbc.EmbeddedDriver)
    elseif dbtype EQS "hsqldb" or dbtype EQS "hsqldb_file" then
      invoke classDbDriver nxm.sys.lib.ReflectUtil.getClassForName(org.hsqldb.jdbcDriver)
    elseif dbtype EQS "mysql" then
      invoke classDbDriver nxm.sys.lib.ReflectUtil.getClassForName(com.mysql.jdbc.Driver)
    else
      assert/text="^reg.main.name: Testing with ^{dbtype} DB not supported" classDbDriverFound
      stop
    endif

    assert/stat=classDbDriverFound/text="^reg.main.name: Found the driver Java Class for the ^{dbtype} DB test" classDbDriver NEQS "NULL"

    if classDbDriverFound then
      if "hsqldb" SUBS dbtype then
        invoke wasReversed nxm.database.prim.nmdb.isReversedCSVOrder4hsqldb()
        invoke ,, nxm.database.prim.nmdb.setReverseCSVOrder4hsqldb(true)
        call test_^{dbtype}
        invoke ,, nxm.database.prim.nmdb.setReverseCSVOrder4hsqldb(wasReversed)
      else
        call test_^{dbtype}
      endif
    else
      warn "^reg.main.name: Skipping the ^{dbtype} DB test since the driver Java Class for the ^{dbtype} DB test could not be found on the Java classpath."
    endif
  else
    invoke javaMajVer nxm.sys.lib.Shell.getJavaMajorVersion()
    res z:rightVer true
    if javaMajVer EQ 8 then
      if interAct then
        popup/blocking="Mouse|Macro" z:rightVer message="Have you switched the hsqldb jar to be the jdk8 version?"
      else
        info "^reg.main.name: Skipping test because jdk8 in non-interactive mode"
        res rightVer false
      endif
    endif
    if rightVer then
      invoke wasReversed nxm.database.prim.nmdb.isReversedCSVOrder4hsqldb()
      invoke ,, nxm.database.prim.nmdb.setReverseCSVOrder4hsqldb(true)
      call test_hsqldb
      invoke ,, nxm.database.prim.nmdb.setReverseCSVOrder4hsqldb(wasReversed)
    endif
  endif

endmacro

!******************************************************************************!
procedure processMessage m:msg
  if debug then say "Got Message ^msg data: ^msg.data"
return

!******************************************************************************!
procedure processException m:emsg
  if debug then say "===> Got Exception: emsg=^emsg data=^emsg.data"
  if expectException REXISTS AND expectException SUBS emsg.data.message then
    assert/text="^reg.main.name: Expected ERROR contains: ^{expectException}" expectException SUBS emsg.data.message
    remove expectException
  else
    ! Otherwise we should NOT get any exceptions (mark as test failure)
    assert/text="^reg.main.name: Unexpected ^emsg.data" "FALSE"
    if debug AND emsg.data instanceof java.lang.Exception then
      invoke ,, emsg.data.printStacktrace()
    endif

    if emsg.data.rootcause REXISTS then
      invoke rootCauseStr emsg.data.rootcause.toString()
      if "org/hsqldb/jdbcDriver has been compiled" SUBS rootCauseStr then stop
    endif
  endif
return

!******************************************************************************!
! test case for using Apache Derby (derby.jar)
! Derby is relational database implemented entirely in Java
! Requires: derby.jar to be in CLASSPATH or in jars area.
!******************************************************************************!
procedure test_derby
  ! Setup connection info for Apache Derby
  set derbyProps {}
  set derbyProps.dbhost   "localhost"
  set derbyProps.dbname   "derbyDemoDB"
  set derbyProps.dbconnect "jdbc:derby:^{derbyProps.dbname};create=true"
  set derbyProps.dbdriver  "org.apache.derby.jdbc.EmbeddedDriver"

  ! Table creation SQL for Derby
  set createTableSQL "CREATE TABLE wish_list  &
       (id         INT         NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT WISH_PK PRIMARY KEY, &
        entry_date TIMESTAMP   DEFAULT CURRENT_TIMESTAMP, &
        wish_item  VARCHAR(32) NOT NULL) "

  call nmdb_tests derbyProps createTableSQL

  ! Cleanup database files that we created
  invoke delcount nxm.sys.lib.BaseFile.delete(^{derbyProps.dbname})
  INFO "Cleaned up ^delcount files from Apache Derby test (file) database."

  files/e derby.*
  INFO "Cleaned up additional log files from Apache Derby test (file) database."
return

!******************************************************************************!
! test case for using HSQLDB (HyperSQL DataBase) (hsqldb.jar) in memory database
! HSQLDB is a Lightweight 100% Java SQL Database Engine
!******************************************************************************!
procedure test_hsqldb
  ! Table creation SQL for HSQLDB
  set S:createTableSQL "CREATE TABLE wish_list  &
       (id         INT         GENERATED BY DEFAULT AS IDENTITY, &
        entry_date TIMESTAMP   DEFAULT CURRENT_TIMESTAMP, &
        wish_item  VARCHAR(32) NOT NULL) "

  ! Setup JDBC (database) connection info for HSQLDB
  !-----------------------------------------------------------------------------
  ! Test In-Process (Standalone) Mode memory only database via "mem:" protocol.
  ! Note in this mode the database is not persistent and exists entirely in
  ! random access memory, so when application exits, the database is gone.
  set dbProps            {}
  set dbProps.dbhost     ""
  set dbProps.dbport     ""
  set dbProps.dbaccount  "sa"
  set dbProps.dbpassword ""
  set dbProps.dbdriver   "org.hsqldb.jdbcDriver"
  set dbProps.dbname     "hsqldb_InMemoryTestDB"
  set dbProps.dbconnect  "jdbc:hsqldb:mem:^{dbProps.dbname}"

  info "Testing HSQLDB in-memory database..."
  ! run standard NMDB test cases
  call nmdb_tests dbProps createTableSQL

return

!******************************************************************************!
! test case for using HSQLDB (HyperSQL DataBase) (hsqldb.jar) in a file database
! HSQLDB is a Lightweight 100% Java SQL Database Engine
!******************************************************************************!
procedure test_hsqldb_file
  ! Table creation SQL for HSQLDB
  set S:createTableSQL "CREATE TABLE wish_list  &
       (id         INT         GENERATED BY DEFAULT AS IDENTITY, &
        entry_date TIMESTAMP   DEFAULT CURRENT_TIMESTAMP, &
        wish_item  VARCHAR(32) NOT NULL) "

  ! Setup JDBC (database) connection info for HSQLDB
  !-----------------------------------------------------------------------------
  ! Test In-Process (Standalone) Mode file (current directory) database via "file:" protocol.
  ! Connection c = DriverManager.getConnection("jdbc:hsqldb:file:testdb", "sa", "passwd");
  ! Using forward slashes here works for both Linux and Windows machines to specify absolute paths.
  ! Connection c = DriverManager.getConnection("jdbc:hsqldb:file:/opt/db/testdb", "sa", "passwd");
  set dbProps            {}
  set dbProps.dbhost     ""
  set dbProps.dbport     ""
  set dbProps.dbaccount  "sa"
  set dbProps.dbpassword ""
  set dbProps.dbdriver   "org.hsqldb.jdbcDriver"
  set dbProps.dbname     "hsqldb_TestDB"
  set dbProps.dbconnect  "jdbc:hsqldb:file:^{dbProps.dbname}"

  info "Testing HSQLDB file database..."
  ! run standard NMDB test cases
  call nmdb_tests dbProps createTableSQL

  ! Cleanup database files that we created
  invoke delcount nxm.sys.lib.BaseFile.delete(^{dbProps.dbname}.tmp)
  INFO "Cleaned up ^delcount files from HSQLDB test (file) database."

  files/e ^{dbProps.dbname}.*
  INFO "Cleaned up additional log files from HSQLDB test (file) database."
return

!******************************************************************************!
! test case for using MySQL database
! Requires: mysql-connector-java.jar to be in CLASSPATH or in jars area.
!           a MySQL instance
!******************************************************************************!
procedure test_mysql
  ! Setup connection info for MySQL
  set mysqlProps {}
  set mysqlProps.dbhost   "10.10.1.59"
  set mysqlProps.dbport   "3306"
  set mysqlProps.dbname   "test"
  set mysqlProps.dbaccount  "nxm"
  set mysqlProps.dbpassword "nextmidas"
  set mysqlProps.dbconnect "jdbc:mysql://^{mysqlProps.dbhost}:^{mysqlProps.dbport}/^{mysqlProps.dbname}"
  set mysqlProps.dbdriver  "com.mysql.jdbc.Driver"

  ! Table creation SQL for MySQL NTN TODO
  set createTableSQL "CREATE TABLE wish_list  &
       (id         INT         NOT NULL AUTO_INCREMENT PRIMARY KEY, &
        entry_date TIMESTAMP   DEFAULT CURRENT_TIMESTAMP, &
        wish_item  VARCHAR(32) NOT NULL) "

  call nmdb_tests mysqlProps createTableSQL
return

!******************************************************************************!
! Test case for calling NMDB for all database types
! The SQL here should run across all database types.
!******************************************************************************!
procedure nmdb_tests T:dbProps S:createTableSQL
  ! SQL to insert, select, update, and drop things from table
  set insertSQL1 "insert into wish_list (wish_item) values('firstValue')
  set insertSQL2 "insert into wish_list (wish_item) values('secondValue')
  set selectSQL  "select * from wish_list"
  set updateSQL  "update wish_list set wish_item = 'newValue' where wish_item = 'firstValue'"
  set deleteSQL  "delete from wish_list where wish_item = 'secondValue'"
  set dropSQL    "drop table wish_list"
  ! NXM-4044 id/0.1 give divide by 0 with new hsqldb, so use id/1e-1
  set selectSQLforBug2055_1 "select distinct round(id/1e-1,2) as "round(id/0.01,2)" from wish_list" ! only testing with hsqldb
  set selectSQLforBug2055_2 "select distinct id/1e-1 from wish_list"

  ! Create NMDB instance and connect to database
  nmdb/id=nmdb/bg props=dbProps resname=dbResults /VERBOSE=^debug

  assert/text="^reg.main.name: Connection established to ^{dbProps.dbname}?" dbResults eq 1

  ! Create table
  if debug then say "Creating table WISH_LIST."
  message func=sendw id=nmdb name=create data="^{createTableSQL}"

  ! Insert Data into the new table
  if debug then say "Inserting two rows into WISH_LIST..."
  message func=sendw id=nmdb name=INSERT data="^{insertSQL1}"
  assert/text="^reg.main.name: Inserting row 1 with WISH_ITEM='firstValue'" dbResults eq 1
  message func=sendw id=nmdb name=INSERT data="^{insertSQL2}"
  assert/text="^reg.main.name: Inserting row 2 with WISH_ITEM='secondValue'" dbResults eq 1

  ! Query the table entries
  if debug then say "Querying the table WISH_LIST...."
  message func=sendw id=nmdb name=QUERY data="^{selectSQL}"
  assert/text="^reg.main.name: Query <RESNAME> has number of rows" dbResults eq 2

  message func=sendw id=nmdb name=GETROWS
  assert/text="^reg.main.name: GETROWS for current query results" dbResults eq 2

  message func=sendw id=nmdb name=GETCOLUMNNAMES
  assert/text="^reg.main.name: GETCOLUMNNAMES for current query results" dbResults eqs "ID,ENTRY_DATE,WISH_ITEM"

  ! Get and Display the query results
  message func=sendw id=nmdb name=getcsv
  say "Results in CSV form: "
  say "^{dbResults}"
  say ""
  ! TODO: assert/text="^reg.main.name: GETCSV for current query results" dbResults ...
  message func=sendw id=nmdb name=getxml
  if debug then
    say "Results in XML form: "
    say "^{dbResults}"
    say ""
  endif
  assert/text="^reg.main.name: GETXML for current query results has <Results><Row><ID>" dbResults eqss "<Results><Row><ID>"
  invoke idx1 dbResults.indexOf("</ID><ENTRY_DATE>")
  assert/text="^reg.main.name: GETXML for current query results has ..<ENTRY_DATE>" idx1 gt 1
  invoke idx2 dbResults.indexOf("</ENTRY_DATE><WISH_ITEM>")
  assert/text="^reg.main.name: GETXML for current query results has ..<WISH_ITEM>" idx2 gt 2 AND idx2 gt idx1
  invoke testresult dbResults.endsWith("</WISH_ITEM></Row></Results>")
  assert/text="^reg.main.name: GETXML for current query results ending tag " testresult isTrue
  message func=sendw id=nmdb name=gettable
  if debug then
    TABLE myTable CREATE dbResults
    say "Results in TABLE form: "
    res/all myTable
  endif
  assert/text="^reg.main.name: GETTABLE for current query results - size = 2" dbResults.getsize eq 2
  foreach row IN/V dbResults
    assert/text="^reg.main.name: GETTABLE for current query results - has ID"         row.id         REXIST
    assert/text="^reg.main.name: GETTABLE for current query results - has ENTRY_DATE" row.entry_date REXIST
    assert/text="^reg.main.name: GETTABLE for current query results - has WISH_ITEM"  row.wish_item  REXIST
  endfor

  ! Update a row in the table
  if debug then say "Updating WISH_ITEM in Row 1 to 'newValue'..."
  message func=sendw id=nmdb name=UPDATE data="^{updateSQL}"
  if debug then  say "Rows updated: ^{dbResults}"
  assert/text="^reg.main.name: Rows updated should = 1" ^{dbResults} eq 1

  ! Display table again
  if debug then
    say ""
    say "Query table WISH_LIST again..."
  endif
  message func=sendw id=nmdb name=QUERY data="^{selectSQL}"
  message func=sendw id=nmdb name=GETCSV
  SEDIT dbResults val SELECT "newValue"
  if debug then
    say "Results in CSV form (should see the value 'newValue' at the end of first line): "
    say "^{dbResults}"
    say ""
  endif
  assert/text="^reg.main.name: Updated value of wish_item = 'newValue'" val eq 2

  ! test DESCRIBE table "wish_list"
  message func=sendw id=nmdb name=DESC data="wish_list"
  if debug then
    say "WISH_LIST table description: "
    say "^dbResults"
    say ""
  endif
  assert/text="^reg.main.name: DESCRIBE wish_item table # columns" dbResults.getsize eq 3
  assert/text="^reg.main.name: DESCRIBE wish_item table have info on column id"         dbResults.id         rexist
  assert/text="^reg.main.name: DESCRIBE wish_item table have info on column entry_date" dbResults.entry_date rexist
  assert/text="^reg.main.name: DESCRIBE wish_item table have info on column wish_item"  dbResults.wish_item  rexist
  foreach row IN/V dbResults
    assert/text="^reg.main.name: DESCRIBE wish_item table have TABLE_NAME"  row.table_name rexist
    assert/text="^reg.main.name: DESCRIBE wish_item table have TYPE"        row.column_name rexist
    assert/text="^reg.main.name: DESCRIBE wish_item table have COLUMN_SIZE" row.column_size ge 0
    assert/text="^reg.main.name: DESCRIBE wish_item table have NULLABLE"    row.nullable rexist
    assert/text="^reg.main.name: DESCRIBE wish_item table have REMARKS"     row.remarks rexist
  endfor

  ! test DESCRIBE all tables in database
  message func=sendw id=nmdb name=DESC
  if debug then
    say "All tables in database: "
    say "^dbResults"
    say ""
  endif
  assert/text="^reg.main.name: DESCRIBE all tables - # tables" dbResults.getsize ge 1
  assert/text="^reg.main.name: DESCRIBE all tables have table wish_list" dbResults.wish_list rexist
  assert/text="^reg.main.name: DESCRIBE all tables have table NAME" dbResults.wish_list.name eqs "WISH_LIST"
  assert/text="^reg.main.name: DESCRIBE all tables have table TYPE" dbResults.wish_list.type eqs "TABLE"
  assert/text="^reg.main.name: DESCRIBE all tables have table REMARKS" dbResults.wish_list.remarks rexist

  ! Test for Bug 2055
  INFO "It is ok to see a warning message that column name contains invalid chars on the next line."
  if dbType eqs "" then
    message func=sendw id=nmdb name=QUERY data="^{selectSQLforBug2055_1}"
    assert/text="^reg.main.name: Query with / operator with 'AS' modifier on wish_list should have one+ row " dbResults.getsize ge 1
  endif
  message func=sendw id=nmdb name=GETCOLUMNNAMES
  assert/text="^reg.main.name: GETCOLUMNNAMES for current query results: ^dbResults" dbResults.length gt 0
  message func=sendw id=nmdb name=gettable
  assert/text="^reg.main.name: GETTABLE for current query results - size = 2" dbResults.getsize eq 2

  ! Test for Bug 2055 for when column is blank (HSQLDB 1.8.0.10 has this behavior)
  INFO "It is ok to see a warning message that database driver did not return column name or contains invalid chars on next line."
  message func=sendw id=nmdb name=QUERY data="^{selectSQLforBug2055_2}"
  assert/text="^reg.main.name: Query with / operator that returns empty column name on wish_list should have one+ row " dbResults.getsize ge 1

  message func=sendw id=nmdb name=GETCOLUMNNAMES
  assert/text="^reg.main.name: GETCOLUMNNAMES for current query results: ^dbResults" dbResults.length gt 0
  message func=sendw id=nmdb name=gettable
  assert/text="^reg.main.name: GETTABLE for current query results - size = 2" dbResults.getsize eq 2

  ! Remove a row from the table
  if debug then say "Removing table WISH_LIST..."
  message func=sendw id=nmdb name=DELETE data="^{deleteSQL}"
  assert/text="^reg.main.name: Removing from wish_list table - rows effected ^dbResults" dbResults eq 1
  message func=sendw id=nmdb name=QUERY data="^{selectSQL}"
  assert/text="^reg.main.name: Query of wish_list should only have one row left" dbResults.getsize eq 1

  ! Drop the wish_list table
  if debug then say "Removing table WISH_LIST..."
  message func=sendw id=nmdb name=DELETE data="^{dropSQL}"

  ! Display table again
  if debug then
    say ""
    say "Query table WISH_LIST again. YOU SHOULD SEE A SQL EXCEPTION FOR TABLE NOT FOUND."
  endif
  res expectException "Unable to execute query"
  message func=sendw id=nmdb name=QUERY data="^{selectSQL}"
  assert/text="^reg.main.name: Query <RESNAME> has number of rows = -1 for ERROR" dbResults eq -1

  ! Disconnect and close connection
  message func=sendw id=nmdb name=DISCONNECT
  assert/text="^reg.main.name: Disconnect from database ^{dbProps.dbname}" ^{dbResults} eq 0

  ! tell NMDB to exit
  message func=sendw id=nmdb name=EXIT

return
