core/conn/jdbc_type2/src/main/java/JdbcMxLobAdmin.java

// @@@ START COPYRIGHT @@@
//
// Licensed to the Apache Software Foundation (ASF) under one
// or more contributor license agreements.  See the NOTICE file
// distributed with this work for additional information
// regarding copyright ownership.  The ASF licenses this file
// to you under the Apache License, Version 2.0 (the
// "License"); you may not use this file except in compliance
// with the License.  You may obtain a copy of the License at
//
//   http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing,
// software distributed under the License is distributed on an
// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
// KIND, either express or implied.  See the License for the
// specific language governing permissions and limitations
// under the License.
//
// @@@ END COPYRIGHT @@@
/* -*-java-*-
 * Filename    : JdbcMxLobAdmin.java
 * Description : This program implements the java.sql.Blob interface
 *
 */
import java.sql.*;
import java.io.*;

public class JdbcMxLobAdmin
{
	public static void main(String args[])
	{
		String	s;
		boolean create = false;
		boolean trigger = false;
		boolean drop = false;
		boolean unicode = false;
		boolean bigblock = false;
		String  tableName = null;
		String fileName = null;
		JdbcMxLobAdmin lobAdmin;

		System.err.println(" JDBC/MX Lob Admin Utility 2.0");
		if (args.length == 0)
			doSql_ = false;
		else
		{
			for (int i = 0; i < args.length ; i++)
			{
				s = args[i];
				if (s.equals("-unicode"))
					unicode = true;
				else
				if (s.equals("-exec"))
					doSql_ = true;
				else
				if (s.equals("-create"))
					create = true;
				else
				if (s.equals("-trigger"))
					trigger = true;
				else
				if (s.equals("-drop"))
					drop = true;
				else
				if (s.equals("-out"))
				{
					i++;
					if (i < args.length)
						fileName = args[i];
				}
				else if(s.equals("-bigblock")){
					bigblock = true;
				}
				else
				if (! s.startsWith("-"))
					tableName = s;
				else
				{
					displayHelp();
					return;
				}
			}
		}
		try
		{
			if (create || trigger || drop)
				Class.forName("org.apache.trafodion.jdbc.t2.T2Driver");
			if (fileName != null)
				out_ = new PrintStream(new FileOutputStream(fileName, true), true);
			else
				out_ = System.out;
		}
		catch (Exception e1)
		{
			System.out.println(e1.getMessage());
			return;
		}
		try
		{
			clobTableName_ = getLobTableName("jdbcmx.clobTableName");
			blobTableName_ = getLobTableName("jdbcmx.blobTableName");
			out_.println("-- Script generated by Hewlett-Packard JDBC/MX Lob Admin Utility 2.0");
			out_.println("");
			if (create)
			{
				if (clobTableName_ == null && blobTableName_ == null)
				{
					throw new SQLException(	"Either jdbcmx.clobTableName or jdbcmx.blobTableName "
						+ "or both properties should be set");
				} else {
					createLobTable(clobTableName_, unicode,bigblock);
					createLobTable(blobTableName_, false,bigblock);
				}
			}
			if (trigger)
			{
				if (tableName == null)
					throw new SQLException("Table name containing the CLOB/BLOB column should be given "
						+ "when -trigger option is given");
				else
					createTriggers(tableName);
			}
			if (drop)
			{
				if (tableName == null)
					throw new SQLException("Table name containing the CLOB/BLOB column should be given "
						+ "when -trigger option is given");
				else
					dropTriggers(tableName);
			}
		}
		catch (SQLException e)
		{
			SQLException nextException;

            nextException = e;
            do
            {
                System.out.println(nextException.getMessage());
                System.out.println("SQLState   " + nextException.getSQLState());
                System.out.println("Error Code " + nextException.getErrorCode());
            } while ((nextException = nextException.getNextException()) != null);
		}
	}

	private static void displayHelp()
	{
		System.err.println("java [<java_options>] JdbcMxLobAdmin [<prog_options>] [<table_name>]");
		System.err.println("");
		System.err.println("<java_options> is:");
		System.err.println("    [-Djdbcmx.clobTableName=<clobTableName>]");
		System.err.println("    [-Djdbcmx.blobTableName=<blobTableName>]");
		System.err.println("    [-Djdbcmx.catalog=<catalog>]");
		System.err.println("    [-Djdbcmx.schema=<schema>]");
		System.err.println("");
		System.err.println("<prog_options> is:");
		System.err.println("    [-exec] [-create] [-trigger] [-unicode] [-help] [-drop] [-out <filename>] [-bigblock]");
		System.err.println("where -help      - Display this information.");
		System.err.println("      -exec      - Execute the SQL statements that are generated.");
		System.err.println("      -create    - Generate SQL statements to create LOB tables.");
		System.err.println("      -trigger   - Generate SQL statements to create triggers for <table_name>.");
		System.err.println("      -unicode   - Generate SQL statements to create unicode LOB tables");
		System.err.println("                 (CLOB only).");
		System.err.println("      -drop      - Generate SQL statements to drop triggers for <table_name>.");
		System.err.println("      -out       - Write the SQL statements to <filename>.");
		System.err.println("      -bigblock  - Generates SQL Statement to create Lob column size of 24K bytes and attribute block size of 32K.");
		//System.err.println("      -bigblock  - Lob data column created with size 24000 and attribute block with size 32000.");
		System.err.println("");
		System.err.println("<clobTableName> | <blobTableName> is:");
		System.err.println("    <catalogName>.<schemaName>.<lobTableName>");
		System.err.println("");
		System.err.println("<table_name> is:");
		System.err.println("    [<catalogName>.][<schemaName>.]<baseTableName>");
		System.err.println("");
		System.err.println("<baseTableName> is the table that contains LOB column(s).");
		System.err.println("<lobTableName> is the table that contains the LOB data.");
	}

	private static String getLobTableName(String prop)
		throws SQLException
	{
		String lobTableName;
		int count = 0;
		int fromIndex = -1;
		lobTableName = System.getProperty(prop);
		if (lobTableName != null)
		{
			while (((fromIndex = lobTableName.indexOf('.', fromIndex+1)) != -1) && count < 2)
				count++;
			if (count < 2)
				throw new SQLException("ClobTableName is not of the format catalog.schema.tablename");
		}
		return lobTableName;
	}

	private static void createLobTable(String lobTableName, boolean isUnicode,boolean isBigBlock)
		throws SQLException
	{
		String s;
		StringBuffer sqlString;
		Statement stmt;
		Connection conn = null;

		if (lobTableName == null)
			return ;
		sqlString = new StringBuffer();
		out_.println("-- SQL statement to create LOB table " + lobTableName);
		out_.println("");
		s = "CREATE TABLE " + lobTableName ;
		out_.println(s);
		sqlString.append(s);
		if (isUnicode)
			s =	" (table_name VARCHAR(128) NOT NULL NOT DROPPABLE,";
		else
			s =	" (table_name CHAR(128) NOT NULL NOT DROPPABLE,";
		out_.println(s);
		sqlString.append(s);
		s =	" data_locator LARGEINT NOT NULL NOT DROPPABLE,";
		out_.println(s);
		sqlString.append(s);
		s =	" chunk_no INT NOT NULL NOT DROPPABLE,";
		out_.println(s);
		sqlString.append(s);
/*
 * Note: If Unicode table is created with lob_data VARCHAR set to 1940
 *  or higher the following SQL/MX exception is generated:
 * 	*** ERROR[1140] Row-length 4038 exceeds the maximum allowed row-length of 4036 for table SWDEV1.SGREEN.CLOBUNI.
 * 	*** ERROR[1029] Object SWDEV1.SGREEN.CLOBUNI could not be created.
 *  This is as a result of the ATTRIBUTES line added overhead.
 */
 		//R3.1 Changes
		/*if (isUnicode)
			s =	" lob_data VARCHAR(1939) CHARACTER SET UCS2,";
		else
			s =	" lob_data VARCHAR(3886),";
			*/
		if (!isUnicode) {
			if(!isBigBlock){
				s = " lob_data VARCHAR(3880),";
			}else {
				s = " lob_data VARCHAR(24000),"; //modified for R3.1
			}
		}
		else {
			if(!isBigBlock){
				s = " lob_data VARCHAR(1939) CHARACTER SET UCS2,";
			}else {
				s = " lob_data VARCHAR(12000) CHARACTER SET UCS2,";//modified for R3.1

			}
		}
		out_.println(s);
		sqlString.append(s);
		s =	" PRIMARY KEY(table_name, data_locator, chunk_no)) ";
		out_.println(s);
		sqlString.append(s);
		if(!isBigBlock){
			s = " ATTRIBUTES EXTENT(1024), MAXEXTENTS 768 ;";
		}else {
			s = " ATTRIBUTES blocksize 32768, MAXEXTENTS 768 ;";
		}
		out_.println(s);
		sqlString.append(s);
		out_.println("");
		if (doSql_)
		{
			try {
				conn = DriverManager.getConnection("jdbc:sqlmx:");
				stmt = conn.createStatement();
				stmt.executeUpdate(sqlString.toString());
			} finally {
				if (conn != null) {
					conn.close();
				}
			}
		}
	}

	private static void createTriggers(String baseTableName)
			throws SQLException
	{
		int	columnType;
		int 	columnCount;
		String 	tableName;
		Statement stmt = null;
		StringBuffer sqlString;
		String 	s;
		String 	lobTableName;
		String 	columnName;
		String  catalogName;
		String  schemaName;
		Connection conn = null;

		int j,i;

		try {
			conn = DriverManager.getConnection("jdbc:sqlmx:");
			PreparedStatement pstmt = conn.prepareStatement("select * from " + baseTableName);
			ResultSetMetaData rsmd = pstmt.getMetaData();
			columnCount = rsmd.getColumnCount();
			if (doSql_)
				stmt = conn.createStatement();
			catalogName = rsmd.getCatalogName(1);
			tableName = rsmd.getTableName(1);
			schemaName = rsmd.getSchemaName(1);
			out_.println("-- Create triggers for " + tableName);
			out_.println("");
			for (i = 1, j =1 ; i <= columnCount ; i++)
			{
				columnType = rsmd.getColumnType(i);
				columnName = rsmd.getColumnName(i);
				if (columnType == Types.CLOB || columnType == Types.BLOB)
				{
					if (columnType == Types.CLOB)
					{
						if (clobTableName_ == null)
							throw new SQLException("jdbcmx.clobTableName property is not set");
						lobTableName = clobTableName_;
					}
					else
					{
						if (blobTableName_ == null)
							throw new SQLException("jdbcmx.blobTableName property is not set");
						lobTableName = blobTableName_;
					}
					sqlString = new StringBuffer();
					out_.println("-- Trigger for update of column " + columnName + " of " + tableName);
					out_.println("");
					s = "CREATE TRIGGER " + catalogName + "." + schemaName + "."+ "zzlobupd_" +
							tableName + "_" + j;
					out_.println(s);
					sqlString.append(s);
					s = " AFTER UPDATE OF (" + columnName + ") ON "+ baseTableName;
					out_.println(s);
					sqlString.append(s);
					s = " REFERENCING OLD AS old_base, NEW AS new_base FOR EACH ROW";
					out_.println(s);
					sqlString.append(s);
					s = " WHEN (old_base." + columnName + " != new_base." + columnName + ")";
					out_.println(s);
					sqlString.append(s);
					s = " DELETE FROM " + lobTableName + " WHERE table_name = '" +
							tableName +"'";
					out_.println(s);
					sqlString.append(s);
					s = " AND data_locator = old_base." + columnName + " ;" ;
					out_.println(s);
					sqlString.append(s);
					out_.println("");
					if (doSql_)
						stmt.executeUpdate(sqlString.toString());
					sqlString = new StringBuffer();
					out_.println("-- Trigger for delete row for column " + columnName + " of " + tableName);
					out_.println("");
					s = "CREATE TRIGGER " + catalogName + "." + schemaName + "."+ "zzlobdel_" +
							tableName + "_" + j ;
					out_.println(s);
					sqlString.append(s);
					s = " AFTER DELETE ON " + baseTableName;
					out_.println(s);
					sqlString.append(s);
					s = " REFERENCING OLD AS old_base FOR EACH ROW";
					out_.println(s);
					sqlString.append(s);
					s = " DELETE FROM " + lobTableName + " WHERE table_name = '" +
							tableName +"'";
					out_.println(s);
					sqlString.append(s);
					s = " AND data_locator = old_base." + columnName + " ;" ;
					out_.println(s);
					sqlString.append(s);
					out_.println("");
					if (doSql_)
						stmt.executeUpdate(sqlString.toString());
					j++;
				}
			}
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	private static void dropTriggers(String baseTableName)
			throws SQLException
	{
		int	columnType;
		int 	columnCount;
		String 	tableName;
		Statement stmt = null;
		StringBuffer sqlString;
		String 	s;
		String  catalogName;
		String  schemaName;
		boolean clobFound;
		boolean blobFound;
		Connection conn = null;

		try {
			conn = DriverManager.getConnection("jdbc:sqlmx:");
			PreparedStatement pstmt = conn.prepareStatement("select * from " + baseTableName);
			ResultSetMetaData rsmd = pstmt.getMetaData();
			columnCount = rsmd.getColumnCount();
			if (doSql_)
				stmt = conn.createStatement();
			catalogName = rsmd.getCatalogName(1);
			tableName = rsmd.getTableName(1);
			schemaName = rsmd.getSchemaName(1);
			out_.println("-- Drop triggers for " + tableName);
			out_.println("");
			for (int i = 1, j =1 ; i <= columnCount ; i++)
			{
				columnType = rsmd.getColumnType(i);
				if (columnType == Types.CLOB || columnType == Types.BLOB)
				{
					s = "DROP TRIGGER " + catalogName + "." + schemaName + "."+ "zzlobupd_" +
							tableName + "_" + j + " ;";
					out_.println(s);
					out_.println("");
					if (doSql_)
						stmt.executeUpdate(s);
					s = "DROP TRIGGER " + catalogName + "." + schemaName + "."+ "zzlobdel_" +
						tableName + "_" + j + " ;";
					out_.println(s);
					out_.println("");
					if (doSql_)
						stmt.executeUpdate(s);
					j++;
				}
			}
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}


	static PrintStream	out_;
	static boolean		doSql_;
	static String		clobTableName_;
	static String		blobTableName_;
}