001package bradleyross.library.database; 002import bradleyross.library.database.DatabaseProperties; 003import bradleyross.library.helpers.StringHelpers; 004import java.sql.ResultSet; 005import java.sql.DatabaseMetaData; 006import java.sql.SQLException; 007import java.io.File; 008import java.io.IOException; 009import java.io.FileWriter; 010/** 011 * Quick and dirty program to make files out of procedures and views 012 * in the database. 013 * <p>This only works for SQL Server.</p> 014 * <p>It was called by com.amtrak.eng.vtr.research.VTRListProcedures</p> 015 * @author Bradley Ross 016 * 017 */ 018public class ShowObjects 019{ 020 DatabaseProperties data = null; 021 DatabaseMetaData schema = null; 022 File rootDirectory1 = null; 023 File rootDirectory2 = null; 024 public ShowObjects(DatabaseProperties value) throws SQLException 025 { 026 data = value; 027 schema = data.getConnection().getMetaData(); 028 rootDirectory1 = 029 new File("/Users/bradleyross/AmtrakTest/Procedures"); 030 rootDirectory2 = 031 new File("/Users/bradleyross/AmtrakTest/Views"); 032 } 033 public void listProcedures() 034 { 035 ResultSet rs = null; 036 ResultSet rs2 = null; 037 038 String sqlQuery = null; 039 int counter = 0; 040 041 try 042 { 043 rs = schema.getProcedures((String) null, (String) null, (String) null); 044 while (rs.next()) 045 { 046 047 counter++; 048 049 String schemaName = rs.getString("PROCEDURE_SCHEM"); 050 String working = rs.getString("PROCEDURE_NAME"); 051 String procedureName = working.substring(0, working.indexOf(";")); 052 /* 053 * This gets the number after the semicolon. It appears that 054 * version numbers are not supported in SQL Server 055 */ 056 String value = working.substring(working.indexOf(";") + 1); 057 if (schemaName == null) { continue; } 058 if (schemaName.equalsIgnoreCase("sys")) { continue; } 059 if (schemaName.equalsIgnoreCase("INFORMATION_SCHEMA")) { continue; } 060 System.out.println(working); 061 System.out.println(StringHelpers.padLeft(Integer.toString(counter), 5) + " " + 062 StringHelpers.padLeft(value, 5) + " " + 063 StringHelpers.padRight(schemaName, 8) + " " + 064 StringHelpers.padRight(procedureName, 30) + " " + 065 " " + rs.getString("PROCEDURE_TYPE")); 066 067 068 sqlQuery = "SELECT A.NAME AS PROCEDURE_NAME, A.TYPE_DESC AS TYPE, " + 069 " B.NAME AS SCHEMA_NAME, C.DEFINITION AS SOURCE " + 070 " FROM VTR.SYS.PROCEDURES A, VTR.SYS.SCHEMAS B, VTR.SYS.SQL_MODULES C " + 071 " WHERE A.SCHEMA_ID=B.SCHEMA_ID AND A.OBJECT_ID=C.OBJECT_ID AND " + 072 " A.NAME=\'" + procedureName + "\'"; 073 System.out.println(" Trying " + sqlQuery); 074 rs2 = data.executeQuery(sqlQuery); 075 int check = 0; 076 while (rs2.next()) 077 { 078 check++; 079 if (check >1) 080 { 081 System.out.println("Too many rows"); 082 } 083 File output = new File (rootDirectory1, 084 rs2.getString("SCHEMA_NAME") + "_" + 085 rs2.getString("PROCEDURE_NAME") + ".txt"); 086 FileWriter writer = new FileWriter(output); 087 String text = rs2.getString("SOURCE"); 088 System.out.println(rs.getString("PROCEDURE_NAME") + " " 089 + rs2.getString("SCHEMA_NAME") ); 090 writer.write(text); 091 writer.close(); 092 } 093 094 sqlQuery = null; 095 096 } 097 098 } 099 catch (SQLException e) 100 { 101 System.out.println(e.getClass().getName() + " " + e.getMessage()); 102 if (sqlQuery != null) 103 { 104 System.out.println(sqlQuery); 105 } 106 } 107 catch (IOException e) 108 { 109 System.out.println(e.getClass().getName() + " " + e.getMessage()); 110 } 111 } 112 public void listViews() 113 { 114 ResultSet rs = null; 115 ResultSet rs2 = null; 116 String permissions[] = {"VIEW"}; 117 int counter = 0; 118 String sqlQuery = null; 119 try 120 { 121 rs = schema.getTables((String) null, (String) null, (String) null,permissions); 122 while (rs.next()) 123 { 124 125 counter++; 126 127 String schemaName = rs.getString("TABLE_SCHEM"); 128 String tableName = rs.getString("TABLE_NAME"); 129 130 if (schemaName == null) { continue; } 131 if (schemaName.equalsIgnoreCase("sys")) { continue; } 132 if (schemaName.equalsIgnoreCase("INFORMATION_SCHEMA")) { continue; } 133 134 System.out.println(StringHelpers.padLeft(Integer.toString(counter), 5) + " " + 135 StringHelpers.padRight(schemaName, 8) + " " + 136 StringHelpers.padRight(tableName, 30) + " " + 137 " " + rs.getString("TABLE_TYPE")); 138 139 140 sqlQuery = "SELECT A.NAME AS TABLE_NAME, A.TYPE_DESC AS TYPE, " + 141 " B.NAME AS SCHEMA_NAME, C.DEFINITION AS SOURCE " + 142 " FROM VTR.SYS.VIEWS A, VTR.SYS.SCHEMAS B, VTR.SYS.SQL_MODULES C " + 143 " WHERE A.SCHEMA_ID=B.SCHEMA_ID AND A.OBJECT_ID=C.OBJECT_ID AND " + 144 " A.NAME=\'" + tableName + "\'"; 145 System.out.println(" Trying " + sqlQuery); 146 rs2 = data.executeQuery(sqlQuery); 147 int check = 0; 148 while (rs2.next()) 149 { 150 check++; 151 if (check >1) 152 { 153 System.out.println("Too many rows"); 154 } 155 File output = new File (rootDirectory2, 156 rs2.getString("SCHEMA_NAME") + "_" + 157 rs2.getString("TABLE_NAME") + ".txt"); 158 FileWriter writer = new FileWriter(output); 159 String text = rs2.getString("SOURCE"); 160 System.out.println(rs.getString("TABLE_NAME") + " " 161 + rs2.getString("SCHEMA_NAME") ); 162 writer.write(text); 163 writer.close(); 164 } 165 166 sqlQuery = null; 167 168 } 169 170 } 171 catch (SQLException e) 172 { 173 System.out.println(e.getClass().getName() + " " + e.getMessage()); 174 if (sqlQuery != null) 175 { 176 System.out.println(sqlQuery); 177 } 178 } 179 catch (IOException e) 180 { 181 System.out.println(e.getClass().getName() + " " + e.getMessage()); 182 } 183 } 184 185}