import java.sql.DatabaseMetaData;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSetMetaData;
import java.util.Properties;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.*;
import java.util.*;
/**
* This class connects to a database and dumps all the tables and contents out to stdout in the form of
* a set of SQL executable statements
*/
public class Oracle2sql {
public static List<String> insList=new ArrayList<String>();
/** Dump the whole database to an SQL string */
public static String dumpDB(Properties props) {
String driverClassName = props.getProperty("driver.class");
String driverURL = props.getProperty("driver.url");
System.out.println("driverClassName:"+driverClassName);
System.out.println("driver.url:"+driverURL);
// Default to not having a quote character
String columnNameQuote = props.getProperty("columnName.quoteChar", "");
DatabaseMetaData dbMetaData = null;
Connection dbConn = null;
// StringBuffer result=null;
try {
Class.forName(driverClassName);
dbConn = DriverManager.getConnection(driverURL, props);
System.out.println("DB Connection Established.");
dbMetaData = dbConn.getMetaData();
}
catch( Exception e ) {
System.err.println("Unable to connect to database: "+e);
return null;
}
try {
// result = new StringBuffer();
String catalog = props.getProperty("catalog");
String schema = props.getProperty("schemaPattern");
String tables = props.getProperty("tableName");
ResultSet rs = dbMetaData.getTables(catalog, schema, tables, null);
if (! rs.next()) {
System.out.println("Unable to find any tables matching: catalog="+catalog+" schema="+schema+" tables="+tables);
rs.close();
} else {
System.out.println("catalog:"+catalog+" schema:"+schema+" tables:"+tables);
// Right, we have some tables, so we can go to work.
// the details we have are
// TABLE_CAT String => table catalog (may be null)
// TABLE_SCHEM String => table schema (may be null)
// TABLE_NAME String => table name
// TABLE_TYPE String => table type. Typical types are "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
// REMARKS String => explanatory comment on the table
// TYPE_CAT String => the types catalog (may be null)
// TYPE_SCHEM String => the types schema (may be null)
// TYPE_NAME String => type name (may be null)
// SELF_REFERENCING_COL_NAME String => name of the designated "identifier" column of a typed table (may be null)
// REF_GENERATION String => specifies how values in SELF_REFERENCING_COL_NAME are created. Values are "SYSTEM", "USER", "DERIVED". (may be null)
// We will ignore the schema and stuff, because people might want to import it somewhere else
// We will also ignore any tables that aren't of type TABLE for now.
// We use a do-while because we've already caled rs.next to see if there are any rows
do {
String tableName = rs.getString("TABLE_NAME");
String tableType = rs.getString("TABLE_TYPE");
if ("TABLE".equalsIgnoreCase(tableType)&& "CHANNEL_MASTER_VIEW,PACKAGE_MASTER_VIEW,PACKAGE_CHANNEL_MAP_VIEW,LANGUAGE_MASTER,VIEW_CATEGORY_MASTER,VIEW_SUBCAT_MASTER,VIEW_VOD_MASTER,VOD_METADATA,SONY_LIV_IMAGE,ASSETS_URL_DETAILS,CLIENT_WEB_URL".contains(tableName)) {
// if ("TABLE".equalsIgnoreCase(tableType)&& "PACKAGE_CHANNEL_MAP_VIEW".contains(tableName)) {
System.out.println("tablename ="+tableName+"] and tableType="+tableType);
//result.append("\n\n-- "+tableName);
//result.append("\nCREATE TABLE "+tableName+" (\n");
ResultSet tableMetaData = dbMetaData.getColumns(null, null, tableName, "%");
boolean firstLine = true;
while (tableMetaData.next()) {
if (firstLine) {
firstLine = false;
} else {
// If we're not the first line, then finish the previous line with a comma
// result.append(",\n");
}
String columnName = tableMetaData.getString("COLUMN_NAME");
String columnType = tableMetaData.getString("TYPE_NAME");
// WARNING: this may give daft answers for some types on some databases (eg JDBC-ODBC link)
int columnSize = tableMetaData.getInt("COLUMN_SIZE");
String nullable = tableMetaData.getString("IS_NULLABLE");
String nullString = "NULL";
if ("NO".equalsIgnoreCase(nullable)) {
nullString = "NOT NULL";
}
//System.out.println("columnName :: "+columnName+" :: columnType :: "+columnType);
//result.append(" "+columnNameQuote+columnName+columnNameQuote+" "+columnType+" ("+columnSize+")"+" "+nullString);
}
tableMetaData.close();
// Now we need to put the primary key constraint
try {
ResultSet primaryKeys = dbMetaData.getPrimaryKeys(catalog, schema, tableName);
// What we might get:
// TABLE_CAT String => table catalog (may be null)
// TABLE_SCHEM String => table schema (may be null)
// TABLE_NAME String => table name
// COLUMN_NAME String => column name
// KEY_SEQ short => sequence number within primary key
// PK_NAME String => primary key name (may be null)
String primaryKeyName = null;
StringBuffer primaryKeyColumns = new StringBuffer();
while (primaryKeys.next()) {
String thisKeyName = primaryKeys.getString("PK_NAME");
if ((thisKeyName != null && primaryKeyName == null)
|| (thisKeyName == null && primaryKeyName != null)
|| (thisKeyName != null && ! thisKeyName.equals(primaryKeyName))
|| (primaryKeyName != null && ! primaryKeyName.equals(thisKeyName))) {
// the keynames aren't the same, so output all that we have so far (if anything)
// and start a new primary key entry
if (primaryKeyColumns.length() > 0) {
// There's something to output
// result.append(",\n PRIMARY KEY ");
if (primaryKeyName != null) {
//result.append(primaryKeyName);
}
// result.append("("+primaryKeyColumns.toString()+")");
}
// Start again with the new name
primaryKeyColumns = new StringBuffer();
primaryKeyName = thisKeyName;
}
// Now append the column
if (primaryKeyColumns.length() > 0) {
primaryKeyColumns.append(", ");
}
primaryKeyColumns.append(primaryKeys.getString("COLUMN_NAME"));
}
if (primaryKeyColumns.length() > 0) {
// There's something to output
// result.append(",\n PRIMARY KEY ");
if (primaryKeyName != null) {
//result.append(primaryKeyName);
}
//result.append(" ("+primaryKeyColumns.toString()+")");
}
} catch (SQLException e) {
// NB you will get this exception with the JDBC-ODBC link because it says
// [Microsoft][ODBC Driver Manager] Driver does not support this function
System.err.println("Unable to get primary keys for table "+tableName+" because "+e);
}
//result.append("\n);\n");
// Right, we have a table, so we can go and dump it
// System.out.println("RESULT ["+result.toString()+"]");
dumpTable(dbConn,tableName);
}
} while (rs.next());
rs.close();
}
dbConn.close();
// return result.toString();
} catch (SQLException e) {
e.printStackTrace(); //To change body of catch statement use Options | File Templates.
}finally{
// result=null;
}
return null;
}
/** dump this particular table to the string buffer */
private static void dumpTable(Connection dbConn,String tableName) {
// StringBuffer result=new StringBuffer();
try {
// System.out.println("Inside dumpTable: TableName="+tableName);
// First we output the create table stuff
PreparedStatement stmt = dbConn.prepareStatement("SELECT * FROM "+tableName);
ResultSet rs = stmt.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
// Now we can output the actual data
// result.append("\n\n-- Data for "+tableName+"\n");
while (rs.next()) {
StringBuffer result=new StringBuffer();
if(tableName.equals("PACKAGE_CHANNEL_MAP_VIEW")){
result.append("INSERT INTO PACKAGE_CHANNEL_MAP_VIEW_REMOTE VALUES (");
}else if(tableName.equals("VOD_METADATA")){
result.append("INSERT INTO VOD_METADATA_REMOTE VALUES (");
}else if(tableName.equals("CLIENT_WEB_URL")){
result.append("INSERT INTO CLIENT_WEB_URL_REMOTE VALUES (");
}else if(tableName.equals("PACKAGE_MASTER_VIEW")){
result.append("INSERT INTO PACKAGE_MASTER_VIEW_REMOTE VALUES (");
}else if(tableName.equals("LANGUAGE_MASTER")){
result.append("INSERT INTO LANGUAGE_MASTER_REMOTE VALUES (");
} else {
result.append("INSERT INTO "+tableName+" VALUES (");
}
for (int i=0; i<columnCount; i++) {
if (i > 0) {
result.append(", ");
}
Object value = rs.getObject(i+1);
if (value == null) {
result.append("NULL");
} else {
String outputValue = value.toString();
outputValue = outputValue.replaceAll("'","''");
result.append("'"+outputValue+"'");
}
}
result.append(");\n");
//result.append(");");
//if(tableName.equals("VIEW_CATEGORY_MASTER"))
//System.out.println("RESULT ["+result.toString()+"]");
insList.add(result.toString());
result=null;
}
//System.err.println("list size--------- "+insList.size());
// result=null;
rs.close();
stmt.close();
} catch (SQLException e) {
System.err.println("Unable to dump table "+tableName+" because: "+e);
}finally{
//if(result!=null)result=null;
}
}
public static void batchInsert(List iList){
System.out.println("batchInsert -----iList @@@@@"+iList.size());
Connection mySqlConn = null;
Statement st=null;
try {
Class.forName("com.mysql.jdbc.Driver");
mySqlConn = DriverManager.getConnection("jdbc:mysql://172.31.22.59:3306/unified","neo", "admin123");
if(mySqlConn!=null){
mySqlConn.setAutoCommit(false);// Disables auto-commit.
st = mySqlConn.createStatement();
st.addBatch("TRUNCATE TABLE CHANNEL_MASTER_VIEW;");
st.addBatch("TRUNCATE TABLE PACKAGE_MASTER_VIEW_REMOTE;");
st.addBatch("TRUNCATE TABLE PACKAGE_CHANNEL_MAP_VIEW_REMOTE;");
st.addBatch("TRUNCATE TABLE LANGUAGE_MASTER_REMOTE;");
st.addBatch("TRUNCATE TABLE VIEW_CATEGORY_MASTER;\n");
st.addBatch("TRUNCATE TABLE VIEW_SUBCAT_MASTER;\n");
st.addBatch("TRUNCATE TABLE VIEW_VOD_MASTER;\n");
st.addBatch("TRUNCATE TABLE ASSETS_URL_DETAILS;\n");
st.addBatch("TRUNCATE TABLE SONY_LIV_IMAGE;\n");
st.addBatch("TRUNCATE TABLE VOD_METADATA_REMOTE;\n");
st.addBatch("TRUNCATE TABLE CLIENT_WEB_URL_REMOTE;\n");
System.out.println("SIZE:"+iList.size());
try{
//FileWriter fstream = new FileWriter("out.txt");
//BufferedWriter out = new BufferedWriter(fstream);
for(int i=0;i<iList.size();i++){
//System.out.println("--["+iList.get(i)+"]");
st.addBatch((String)iList.get(i));
//System.out.println(iList.get(i)+"\n");
//out.write((String)iList.get(i));
}
//Close the output stream
//out.close();
}catch(Exception ex){
ex.printStackTrace();
}
/*String tmpList=(String)iList.get(iList.size()-1);
String[] strs=tmpList.split(";");
System.out.println("Total:"+strs.length);
for(int j=0;j<strs.length;j++){
if(!strs[j].trim().equals("")){
st.addBatch(strs[j].trim()+";");
}
}*/
//st.addBatch("UPDATE version set CHHANNEL_VERSION=CHHANNEL_VERSION+1;");
st.addBatch("UPDATE version set PKG_VERSION=PKG_VERSION+1;");
//st.addBatch("UPDATE version set VOD_VERSION=VOD_VERSION+1;");
int [] counts=null;
try{
counts = st.executeBatch();
// counts =10;
System.out.println("count"+counts.length);
mySqlConn.commit();
System.out.println("Catalog Sync Process has been done now updating version summary");
}catch(Exception e){
e.printStackTrace();
}
if(counts!=null && counts.length>0){
ResultSet rs=st.executeQuery("select max(ver_version) from version_summary where ver_type='CatalogSync'");
String mVersion=null;
if(rs.next())
mVersion=rs.getString(1);
rs.close();
System.out.println("Recent version was ["+mVersion+"]");
int ver=0;
if(mVersion!=null){
ver= Integer.parseInt(mVersion);
}
ver=ver+1;
st.executeUpdate("insert into version_summary(ver_type,ver_version,ver_date) values ('CatalogSync','"+ver+"',now())");
st.executeUpdate("UPDATE sync_info SET sync_status = 'c',last_sync_time= now()");
System.out.println("=====CATALOGSYNC=====:SUCCESS");
System.out.println("REname Tables start Time =======["+new java.util.Date()+"]=====");
st.executeUpdate("rename table unified.VOD_METADATA_REMOTE to unified.VOD_METADATA_tmp");
st.executeUpdate("rename table unified.VOD_METADATA to unified.VOD_METADATA_REMOTE");
st.executeUpdate("rename table unified.VOD_METADATA_tmp to unified.VOD_METADATA");
st.executeUpdate("rename table unified.LANGUAGE_MASTER_REMOTE to unified.LANGUAGE_MASTER_tmp");
st.executeUpdate("rename table unified.LANGUAGE_MASTER to unified.LANGUAGE_MASTER_REMOTE");
st.executeUpdate("rename table unified.LANGUAGE_MASTER_tmp to unified.LANGUAGE_MASTER");
st.executeUpdate("rename table unified.PACKAGE_MASTER_VIEW_REMOTE to unified.PACKAGE_MASTER_VIEW_tmp");
st.executeUpdate("rename table unified.PACKAGE_MASTER_VIEW to unified.PACKAGE_MASTER_VIEW_REMOTE");
st.executeUpdate("rename table unified.PACKAGE_MASTER_VIEW_tmp to unified.PACKAGE_MASTER_VIEW");
st.executeUpdate("rename table unified.PACKAGE_CHANNEL_MAP_VIEW_REMOTE to unified.PACKAGE_CHANNEL_MAP_VIEW_tmp");
st.executeUpdate("rename table unified.PACKAGE_CHANNEL_MAP_VIEW to unified.PACKAGE_CHANNEL_MAP_VIEW_REMOTE");
st.executeUpdate("rename table unified.PACKAGE_CHANNEL_MAP_VIEW_tmp to unified.PACKAGE_CHANNEL_MAP_VIEW");
st.executeUpdate("rename table unified.CLIENT_WEB_URL_REMOTE to unified.CLIENT_WEB_URL_tmp");
st.executeUpdate("rename table unified.CLIENT_WEB_URL to unified.CLIENT_WEB_URL_REMOTE");
st.executeUpdate("rename table unified.CLIENT_WEB_URL_tmp to unified.CLIENT_WEB_URL");
st.executeUpdate("rename table unified.ASSETS_URL_DETAILS to unified.ASSETS_URL_DETAILS_tmp");
st.executeUpdate("rename table unified.CHANNEL_RTSP_DETAILS_VIEW to unified.ASSETS_URL_DETAILS");
st.executeUpdate("rename table unified.ASSETS_URL_DETAILS_tmp to unified.CHANNEL_RTSP_DETAILS_VIEW");
System.out.println("REname Tables END Time =======["+new java.util.Date()+"]=====");
}else{
System.out.println("CATALOGSYNC:FAILED");
}
}
}
catch( Exception e ) {
System.err.println("Unable to connect to MySQL database: "+e);
}finally{
if(st!=null){
try{
st.close();
}catch(Exception e){
System.out.println("Exception occured while closing SQL Statement");
}
}
}
}
/** Main method takes arguments for connection to JDBC etc. */
public static void main(String[] args) {
/*if (args.length != 1) {
System.err.println("usage: db2sql <property file>");
}*/
// Right so there's one argument, we assume it's a property file
// so lets open it
System.out.println("==========================================================================================================================");
Properties props = new Properties();
try {
System.out.println("Catalog SYNC Started at:["+new java.util.Date()+"] sync variable ::: "+System.getenv("SYNC"));
//props.load(new FileInputStream(args[0]));
//props.load(new FileInputStream(new java.io.File("D:\\Vineetworks\\Dev\\Java\\MSSQL2sql.properties")));
props.load(new FileInputStream(new java.io.File("/usr/syncservice/props/Oracle2sql.properties")));
System.out.println("properties loaded");
//System.out.println(dumpDB(props));
StringBuffer backupBuf=new StringBuffer();
//backupBuf.insert(0, "TRUNCATE TABLE Program_Details;\n");
backupBuf.append("TRUNCATE TABLE CHANNEL_MASTER_VIEW;\n");
backupBuf.append("TRUNCATE TABLE PACKAGE_MASTER_VIEW_REMOTE;\n");
backupBuf.append("TRUNCATE TABLE PACKAGE_CHANNEL_MAP_VIEW_REMOTE;\n");
backupBuf.append("TRUNCATE TABLE LANGUAGE_MASTER_REMOTE;\n");
backupBuf.append("TRUNCATE TABLE SONY_LIV_IMAGE;\n");
backupBuf.append("TRUNCATE TABLE VOD_METADATA_REMOTE;\n");
backupBuf.append("TRUNCATE TABLE CLIENT_WEB_URL_REMOTE;\n");
backupBuf.append("TRUNCATE TABLE ASSETS_URL_DETAILS;\n");
System.out.println("Configuring ###########TEST########### Environment for Catalog SYNC :["+new java.util.Date()+"]");
backupBuf.append(dumpDB(props));
//for(int i=0;i<insList.size();i++){
/* System.out.println("1 ["+insList.get(0)+"]");
System.out.println("2 ["+insList.get(1)+"]");
System.out.println("3 ["+insList.get(2)+"]");
System.out.println("4 ["+insList.get(3)+"]");
System.out.println("5 ["+insList.get(433)+"]");*/
//}
backupBuf.append("UPDATE version set CHHANNEL_VERSION=CHHANNEL_VERSION+1;\n");
backupBuf.append("UPDATE version set PKG_VERSION=PKG_VERSION+1;\n");
//System.out.println("DUMP CREATED@@@@@"+backupBuf.toString());
//insList.size();
System.out.println("-----Inlist @@@@@"+insList.size());
batchInsert(insList);
insList.clear();
insList=null;
/*FileOutputStream out=null;
try{
out=new FileOutputStream(new java.io.File("/usr/syncservice/dump/BackupCatalog.sql"));
out.write(backupBuf.toString().getBytes());
}catch(Exception e){
System.out.println("Exception occured while writing Catalog data to file stream"+e);
}finally{
if(out!=null){
out.flush();
out.close();
}
}
System.out.println("Backup File has been generated Now need to dump to mysql database");
execShellCmd();
System.out.println("Completed at:["+new java.util.Date()+"]");
*/
System.out.println("Completed at:["+new java.util.Date()+"]");
System.out.println("==========================================================================================================================");
} catch (IOException e) {
System.err.println("Unable to open property file: "+args[0]+" exception: "+e);
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
