//
//   Source File Name: Create.java  1.3 
//  
//   Licensed Materials -- Property of IBM 
//  
//   (c) Copyright International Business Machines Corporation, 1999. 
//       All Rights Reserved. 
//  
//   US Government Users Restricted Rights - 
//   Use, duplication or disclosure restricted by 
//   GSA ADP Schedule Contract with IBM Corp. 
//  
//   PURPOSE : 
//   Creates all tables and issues CREATE statements for the  
//   User Defined Types (UDTs) & Functions (UDFs) for the Order scenario. 

//   PREREQUISITE: 
//   You must build the CLI sample UDF library "order.c" and copy the  
//   resulting library to the function directory. 

//  For more information about this sample, refer to the README file. 

//  For more information on Programming in Java, refer to the  
//  "Programming in Java" section of the Application Development Guide. 

//  For more information on programming and using UDFs and UDTs, refer  
//  to the "Object-Relational Programming" section of the  
//  Application Development Guide. 

//  For more information on building and running Java programs for DB2, 
//  refer to the "Building Java Applets and Applications" section of the  
//  Application Building Guide. 

//  For more information on the SQL language, refer to the SQL Reference. 

import java.io.*;
import java.lang.*;
import java.sql.*;
import java.util.*;

class Create {

   static {
      try {
         //  register the driver with DriverManager 
         //  The newInstance() call is needed for the sample to work with 
         //  JDK 1.1.1 on OS/2, where the Class.forName() method does not 
         //  run the static initializer. For other JDKs, the newInstance 
         //  call can be omitted. 
         Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
      } catch (Exception e) {
         e.printStackTrace();
      }
   }

   public static void main (String[] argv) {
      Connection con = null;
      try {
         //  connect to the server of user's choice 
         //  see Tools.class on Tools.DBConnect method 
         if (argv.length == 3)
         {  //  connect using command line arguments 
            con = Tools.DBConnect(argv[0], argv[1], argv[2]); 
         }
         else
         {  //  prompt user for database name, user ID, password 
            con = Tools.DBConnect(); 
         }
         con.setAutoCommit (false);

         Vector allStatements = createStatements();
         String[] statements = new String[allStatements.size()];
         allStatements.copyInto (statements);
         executeStatements (con, statements, allStatements.size());

         //  disconnect from the connection made 
         System.out.println (">Disconnecting...");
         con.commit();
         con.close();
      } catch (Exception e) { e.printStackTrace(); }
   }

   //  
   //  createStatements 
   //     - adds a number of SQL statements to a vector 
   //  

   public static Vector createStatements() {
      Vector statements = new Vector();
      try {
        statements.addElement ("CREATE DISTINCT TYPE CNUM AS INTEGER WITH COMPARISONS");
        statements.addElement ("CREATE DISTINCT TYPE PUNIT AS CHAR(2) WITH COMPARISONS");
        statements.addElement ("CREATE DISTINCT TYPE UPRICE AS DECIMAL(10, 2) WITH COMPARISONS");
        statements.addElement ("CREATE DISTINCT TYPE PRICE AS DECIMAL(10, 2) WITH COMPARISONS");
        statements.addElement ("CREATE FUNCTION PRICE( CHAR(12), PUNIT, char(16) ) " +
           "returns char(12) " +
           "NOT FENCED EXTERNAL NAME 'order!price' " +
           "NOT VARIANT NO SQL LANGUAGE C PARAMETER STYLE DB2SQL " +
           "NO EXTERNAL ACTION");

        statements.addElement ("CREATE DISTINCT TYPE PNUM AS INTEGER WITH COMPARISONS");
        statements.addElement ("CREATE FUNCTION \"+\"(PNUM, INTEGER) RETURNS PNUM " +
           "source sysibm.\"+\"(integer, integer)");

        statements.addElement ("CREATE FUNCTION MAX(PNUM) RETURNS PNUM source max(integer)");
        statements.addElement ("CREATE DISTINCT TYPE ONUM AS INTEGER WITH COMPARISONS");

        statements.addElement ("CREATE TABLE CUSTOMER ( " +
           "Cust_Num     CNUM NOT NULL, " +
           "First_Name   CHAR(30) NOT NULL, " +
           "Last_Name    CHAR(30) NOT NULL, " +
           "Street       CHAR(128) WITH DEFAULT, " +
           "City         CHAR(30) WITH DEFAULT, " +
           "Prov_State   CHAR(30) WITH DEFAULT, " +
           "PZ_Code      CHAR(9) WITH DEFAULT, " +
           "Country      CHAR(30) WITH DEFAULT, " +
           "Phone_Num    CHAR(20) WITH DEFAULT, " +
           "PRIMARY KEY  (Cust_Num) )");

        statements.addElement ("CREATE TABLE PRODUCT ( " +
           "Prod_Num     PNUM NOT NULL, " +
           "Description  VARCHAR(256) NOT NULL, " +
           "Price        DECIMAL(10,2) WITH DEFAULT , " +
           "Units        PUNIT NOT NULL, " +
           "Combo        CHAR(1) WITH DEFAULT, " +
           "PRIMARY KEY (Prod_Num), " +
           "CHECK (Units in (PUNIT('m'), PUNIT('l'), PUNIT('g'), PUNIT('kg'), " +
           "PUNIT(' ')))  )");

        statements.addElement ("CREATE TABLE PROD_PARTS ( " +
           "Prod_Num     PNUM NOT NULL, " +
           "Part_Num     PNUM NOT NULL, " +
           "Quantity     DECIMAL(14,7), " +
           "PRIMARY KEY (Prod_Num, Part_Num), " +
           "FOREIGN KEY (Prod_Num) REFERENCES Product, " +
           "FOREIGN KEY (Part_Num) REFERENCES Product, " +
           "CHECK (Prod_Num <> Part_Num) )");

        statements.addElement ("CREATE TABLE ORD_CUST ( " +
           "Ord_Num      ONUM NOT NULL, " +
           "Cust_Num     CNUM NOT NULL, " +
           "Ord_Date     DATE NOT NULL, " +
           "PRIMARY KEY (Ord_Num), " +
           "FOREIGN KEY (Cust_Num) REFERENCES Customer )");

        statements.addElement ("CREATE TABLE ORD_LINE ( " +
           "Ord_Num      ONUM NOT NULL, " +
           "Prod_Num     PNUM NOT NULL, " +
           "Quantity     DECIMAL(14,7), " +
           "PRIMARY KEY (Ord_Num, Prod_Num), " +
           "FOREIGN KEY (Prod_Num) REFERENCES Product, " +
           "FOREIGN KEY (Ord_Num) REFERENCES Ord_Cust )");
      } catch (Exception e) { e.printStackTrace(); }
      return statements;
   }

   //  
   //  executeStatements 
   //     - executes all statements found in the array of statements 
   //  

   public static void executeStatements (Connection con, String[] statement, int total) {
      try {
         for (int cnt = 1; cnt <= total; cnt = cnt + 1) {
            Statement stmt = con.createStatement();
            System.out.println (">Executing Statement " + cnt);
            //  executes the current statement 
            stmt.executeUpdate (statement[cnt-1]);
            stmt.close();
         }
         con.commit();
      } catch (Exception e) { e.printStackTrace(); }
   }
}