|
1
16 package scriptella.tools.template;
17
18 import scriptella.jdbc.GenericDriver;
19 import scriptella.jdbc.JdbcException;
20 import scriptella.spi.ConnectionParameters;
21
22 import java.sql.Connection  ;
23 import java.sql.DatabaseMetaData ;
24 import java.sql.ResultSet ;
25 import java.sql.SQLException ;
26 import java.util.ArrayList ;
27 import java.util.Arrays ;
28 import java.util.HashSet ;
29 import java.util.Iterator ;
30 import java.util.LinkedHashSet ;
31 import java.util.List ;
32 import java.util.Set ;
33
34
35
41 public class DatabaseMigrator {
42 public static void main(final String args[]) {
43
46 ConnectionParameters params = new ConnectionParameters(null,null);
47 GenericDriver jdbcDriver = new GenericDriver();
48
49
50 final Connection con = jdbcDriver.connect(params).getNativeConnection();
51 final Set <String > tables = sortTables(con, params);
52 StringBuilder o = new StringBuilder ();
53 o.append("<etl>\n" +
54 " <connection id=\"in\" driver=\"com.sybase.jdbc2.jdbc.SybDriver\" url=\"jdbc:sybase:Tds:localhost:2638\" user=\"DBA\" password=\"SQL\"/>\n" +
55 " <connection id=\"out\" driver=\"org.hsqldb.jdbcDriver\" url=\"jdbc:hsqldb:file:D:/tools/hsqldb/data/dbm\" user=\"sa\" password=\"\"/>\n");
56
57 for (String t : tables) {
58 o.append(" <query connection-id=\"in\">\n")
59 .append(" select ");
60 appendColumnNames(con, params, t, o).append(" from ").append(t);
61
62 o.append(" <script connection-id=\"out\">\n")
63 .append(" insert into ").append(t).append("(");
64 appendColumnNames(con, params, t, o).append(") VALUES (");
65 appendColumnNames(con, params, t, o, ", ", "?").append(")")
66 .append("\n </script>\n").
67 append("\n </query>\n");
68 }
69
70 o.append("</etl>\n");
71 System.out.println("o = " + o);
72
73 }
75
76 private static StringBuilder appendColumnNames(
77 final Connection con, ConnectionParameters params, final String table, final StringBuilder sql) {
78 return appendColumnNames(con, params, table, sql, ", ", "");
79 }
80
81 private static StringBuilder appendColumnNames(
82 final Connection con, ConnectionParameters params, final String table,
83 final StringBuilder sql, final String separator, final String prefix) {
84 final Set <String > tableColumns = getTableColumns(con, params, table);
85
86 for (Iterator <String > it = tableColumns.iterator(); it.hasNext();) {
87 String s = it.next();
88 sql.append(prefix);
89 sql.append(s);
90
91 if (it.hasNext()) {
92 sql.append(separator);
93 }
94 }
95
96 return sql;
97 }
98
99 private static Set <String > sortTables(final Connection con, ConnectionParameters params) {
100 List <String > tables = getTables(con, params);
101 System.out.println("Sorting " + tables);
102
103 int n = tables.size();
104 int m[][] = new int[n][n];
105
106 for (int i = 0; i < m.length; i++) {
107 Arrays.fill(m[i], 0);
108 }
109
110 String tbls[] = tables.toArray(new String [n]);
111
112 try {
113 final DatabaseMetaData metaData = con.getMetaData();
114
115 for (int i = 0; i < n; i++) {
116 String t = tbls[i];
117 final ResultSet rs = metaData.getExportedKeys(params.getCatalog(),
118 params.getSchema(), t);
119
120 while (rs.next()) {
121 String t2 = rs.getString(7);
123 int i2 = indexOf(tbls, t2);
124
125 if (i2 >= 0) {
126 m[i][i2] += ((rs.getInt(11) != 2) ? 10 : 1);
127 }
128
129 }
131
132 rs.close();
133 }
134
135 for (int i = 0; i < n; i++) {
136 for (int j = 0; j < n; j++) {
137 System.out.print(m[i][j]);
138 System.out.print((m[i][j] >= 10) ? " " : " ");
139 }
140
141 System.out.println(tbls[i]);
142 }
143
144 boolean free[] = new boolean[n];
145 Arrays.fill(free, true);
146
147 Set <String > res = new LinkedHashSet <String >();
148
149 for (int i = 0; i < n; i++) {
150 int min = Integer.MAX_VALUE;
152 int minI = -1;
153
154 for (int j = 0; j < n; j++) {
156 int s = 0;
157
158 if (free[j]) {
159 for (int k = 0; k < n; k++) {
161 if ((k != j) && free[k]) {
162 s += m[k][j];
163 }
164 }
165
166 if (s < min) {
167 min = s;
168 minI = j;
169 }
170 }
171 }
172
173 if (minI >= 0) {
174 free[minI] = false;
175 res.add(tbls[minI]);
176 }
177 }
178
179 return res;
180 } catch (SQLException e) {
181 throw new JdbcException(e.getMessage(), e);
182 }
183 }
184
185 private static int indexOf(final String list[], final String element) {
186 for (int i = 0; i < list.length; i++) {
187 if (list[i].equalsIgnoreCase(element)) {
188 return i;
189 }
190 }
191
192 return -1;
193 }
194
195 private static List <String > getTables(Connection con, ConnectionParameters params) {
196 try {
197 return getColumn(con.getMetaData()
198 .getTables(con.getCatalog(),
199 params.getSchema(), null, new String []{"TABLE"}), 3);
200 } catch (SQLException e) {
201 throw new JdbcException(e.getMessage(), e);
202 }
203 }
204
205 private static Set <String > getTableColumns(Connection con, ConnectionParameters params, final String tableName) {
206 try {
207 return new HashSet <String >(getColumn(
208 con.getMetaData()
209 .getColumns(params.getCatalog(), params.getSchema(), tableName, null),
210 4));
211 } catch (SQLException e) {
212 throw new JdbcException(e.getMessage(), e);
213 }
214 }
215
216
222 public static List <String > getColumn(final ResultSet rs, final int columnPos) {
223 List <String > l = new ArrayList <String >();
224
225 try {
226 while (rs.next()) {
227 l.add(rs.getString(columnPos));
228 }
229 } catch (SQLException e) {
230 throw new JdbcException("Unable to get column #" + columnPos, e);
231 }
232
233 return l;
234 }
235
236 }
237 |
Popular Tags |
Java API By Example, From Geeks To Geeks.