1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 package org.synchronoss.cpo.plugin;
22
23 import org.apache.maven.plugin.*;
24 import org.synchronoss.cpo.core.cpoCoreMeta.CpoMetaDataDocument;
25 import org.synchronoss.cpo.exporter.MetaXmlObjectExporter;
26 import org.synchronoss.cpo.helper.*;
27 import org.synchronoss.cpo.jdbc.*;
28 import org.synchronoss.cpo.jdbc.exporter.JdbcMetaXmlObjectExporter;
29 import org.synchronoss.cpo.jdbc.meta.JdbcCpoMetaDescriptor;
30 import org.synchronoss.cpo.meta.domain.*;
31
32 import java.io.File;
33 import java.sql.*;
34 import java.util.*;
35
36
37
38
39 public class ConvertSQLToXML extends AbstractMojo {
40
41
42
43
44
45 private String dbUrl;
46
47
48
49
50 private String dbTablePrefix;
51
52
53
54
55 private String dbDriver;
56
57
58
59
60 private String dbParams;
61
62
63
64
65 private String filter;
66
67 public static final File TARGET = new File("target");
68 public static final String CPO_META_DATA_XML = "CpoMetaData.xml";
69
70 private JdbcCpoMetaDescriptor metaDescriptor;
71
72 public void execute(String dbUrl, String dbTablePrefix, String dbDriver, String dbParams, String filter) throws MojoExecutionException {
73 this.dbUrl = dbUrl;
74 this.dbTablePrefix = dbTablePrefix;
75 this.dbDriver = dbDriver;
76 this.dbParams = dbParams;
77 this.filter = filter;
78 execute();
79 }
80
81 @Override
82 public void execute() throws MojoExecutionException {
83 getLog().info("Converting SQL to XML...");
84
85 getLog().info("dbUrl: " + dbUrl);
86 getLog().info("dbTablePrefix: " + dbTablePrefix);
87 getLog().info("Class: " + dbDriver);
88
89 try {
90 metaDescriptor = new JdbcCpoMetaDescriptor("Converter", true);
91 } catch (Exception e) {
92 throw new MojoExecutionException("Couldn't load the MetaDescriptor");
93 }
94
95 try {
96 Class<?> driverClass = CpoClassLoader.forName(dbDriver);
97 } catch (Exception e) {
98 throw new MojoExecutionException("Couldn't location driver class");
99 }
100
101 Properties connectionProperties = new Properties();
102 if (dbParams != null && !dbParams.equals("")) {
103 StringTokenizer st = new StringTokenizer(dbParams, ";");
104 while (st.hasMoreTokens()) {
105 String token = st.nextToken();
106 StringTokenizer stNameValue = new StringTokenizer(token, "=");
107 String name = null, value = null;
108 if (stNameValue.hasMoreTokens())
109 name = stNameValue.nextToken();
110 if (stNameValue.hasMoreTokens())
111 value = stNameValue.nextToken();
112 connectionProperties.setProperty(name, value);
113 }
114 }
115
116 Connection conn = null;
117 try {
118 conn = DriverManager.getConnection(dbUrl, connectionProperties);
119 conn.setAutoCommit(false);
120
121 List<CpoClass> classes = getClasses(conn);
122
123 for (CpoClass cpoClass : classes) {
124 for (CpoAttribute att : getAttributes(cpoClass, conn)) {
125 cpoClass.addAttribute(att);
126 }
127
128 for (CpoFunctionGroup fg : getFunctionGroups(cpoClass, conn)) {
129 cpoClass.addFunctionGroup(fg);
130 }
131 }
132
133
134 MetaXmlObjectExporter exporter = new JdbcMetaXmlObjectExporter(metaDescriptor);
135 for (CpoClass cpoClass : classes) {
136 cpoClass.acceptMetaDFVisitor(exporter);
137 }
138 CpoMetaDataDocument cpoMetaDataDocument = exporter.getCpoMetaDataDocument();
139
140
141 if (!TARGET.exists()) {
142 TARGET.mkdir();
143 }
144
145
146 cpoMetaDataDocument.save(new File(TARGET, CPO_META_DATA_XML), XmlBeansHelper.getXmlOptions());
147
148 } catch (Exception ex) {
149 getLog().error("Exception caught", ex);
150 throw new MojoExecutionException(ex.getMessage(), ex);
151 } finally {
152 if (conn != null) {
153 try {
154 conn.close();
155 } catch (SQLException ex) {
156
157 if (getLog().isDebugEnabled()) {
158 getLog().debug(ex.getMessage());
159 }
160 }
161 }
162 }
163 }
164
165 private List<CpoClass> getClasses(Connection conn) throws SQLException {
166 List<CpoClass> classes = new ArrayList<CpoClass>();
167
168 StringBuilder sql = new StringBuilder();
169 sql.append("select name from ");
170 sql.append(dbTablePrefix);
171 sql.append("cpo_class order by name");
172 getLog().debug("getClasses() SQL: " + sql);
173
174 PreparedStatement ps = null;
175 ResultSet rs = null;
176 try {
177 ps = conn.prepareStatement(sql.toString());
178 rs = ps.executeQuery();
179 while (rs.next()) {
180 String className = rs.getString(1);
181
182
183 if (filter != null && className.matches(filter)) {
184 CpoClass cpoClass = new CpoClassCaseSensitive();
185 cpoClass.setName(className);
186 classes.add(cpoClass);
187 }
188 }
189 } finally {
190 if (rs != null) {
191 try {
192 rs.close();
193 } catch (Exception e) {
194
195 if (getLog().isDebugEnabled()) {
196 getLog().debug(e.getMessage());
197 }
198 }
199 }
200 if (ps != null) {
201 try {
202 ps.close();
203 } catch (Exception e) {
204
205 if (getLog().isDebugEnabled()) {
206 getLog().debug(e.getMessage());
207 }
208 }
209 }
210 }
211 return classes;
212 }
213
214 private List<CpoAttribute> getAttributes(CpoClass cpoClass, Connection conn) throws Exception {
215 List<CpoAttribute> attributes = new ArrayList<CpoAttribute>();
216
217 StringBuilder sql = new StringBuilder();
218 sql.append("select cam.column_name, cam.attribute, cam.column_type, cam.db_table, cam.db_column, cam.transform_class from ");
219 sql.append(dbTablePrefix);
220 sql.append("cpo_attribute_map cam, ");
221 sql.append(dbTablePrefix);
222 sql.append("cpo_class cc where cc.name = ? and cam.class_id = cc.class_id ");
223
224 PreparedStatement ps = null;
225 ResultSet rs = null;
226
227 getLog().debug("loadAttribute Sql <" + sql.toString() + ">");
228
229 try {
230 ps = conn.prepareStatement(sql.toString());
231 ps.setString(1, cpoClass.getName());
232 rs = ps.executeQuery();
233
234 while (rs.next()) {
235 JdbcCpoAttribute cpoAttribute = new JdbcCpoAttribute();
236 cpoAttribute.setDataName(rs.getString(1));
237 cpoAttribute.setJavaName(rs.getString(2));
238 cpoAttribute.setDataType(rs.getString(3));
239 cpoAttribute.setDbTable(rs.getString(4));
240 cpoAttribute.setDbColumn(rs.getString(5));
241 cpoAttribute.setTransformClassName(rs.getString(6));
242
243 if (cpoAttribute.getTransformClassName()!=null) {
244 try {
245 cpoAttribute.loadRunTimeInfo(metaDescriptor, null);
246 } catch (Exception e) {
247
248 }
249 }
250
251 cpoAttribute.setJavaType(metaDescriptor.getJavaTypeName(cpoAttribute));
252
253 attributes.add(cpoAttribute);
254 }
255 } catch (Exception ex) {
256 String msg = "loadAttributeMap() failed:'" + sql + "' classname:" + cpoClass.getName();
257 getLog().error(msg, ex);
258 throw ex;
259 } finally {
260 if (rs != null) {
261 try {
262 rs.close();
263 } catch (Exception e) {
264
265 if (getLog().isDebugEnabled()) {
266 getLog().debug(e.getMessage());
267 }
268 }
269 }
270
271 if (ps != null) {
272 try {
273 ps.close();
274 } catch (Exception e) {
275
276 if (getLog().isDebugEnabled()) {
277 getLog().debug(e.getMessage());
278 }
279 }
280 }
281 }
282
283 return attributes;
284 }
285
286 private List<CpoFunctionGroup> getFunctionGroups(CpoClass cpoClass, Connection conn) {
287 List<CpoFunctionGroup> functionGroups = new ArrayList<CpoFunctionGroup>();
288
289 StringBuilder sql = new StringBuilder();
290 sql.append("select cqg.group_type, cqg.name, cqt.sql_text, cqt.description, cam.attribute, cqp.param_type ");
291 sql.append("from " + dbTablePrefix + "cpo_query_group cqg ");
292 sql.append("join " + dbTablePrefix + "cpo_class cc on cqg.class_id = cc.class_id and cc.name = ? ");
293 sql.append("left outer join " + dbTablePrefix + "cpo_query cq on cqg.group_id = cq.group_id ");
294 sql.append("left outer join " + dbTablePrefix + "cpo_query_text cqt on cq.text_id = cqt.text_id ");
295 sql.append("left outer join " + dbTablePrefix + "cpo_query_parameter cqp on cq.query_id = cqp.query_id ");
296 sql.append("left outer join " + dbTablePrefix + "cpo_attribute_map cam on cqp.attribute_id = cam.attribute_id ");
297 sql.append("order by cqg.group_type, cqg.name, cqg.group_id, cq.seq_no, cqp.seq_no ");
298
299 PreparedStatement ps = null;
300 ResultSet rs = null;
301
302 getLog().debug("functionGroup Sql <" + sql.toString() + ">");
303
304 try {
305 ps = conn.prepareStatement(sql.toString());
306 ps.setString(1, cpoClass.getName());
307 rs = ps.executeQuery();
308
309 String lastFunctionGroupName = null;
310
311 CpoFunctionGroup functionGroup = null;
312 CpoFunction function = null;
313
314 while (rs.next()) {
315 String groupType = rs.getString(1);
316 String groupName = rs.getString(2);
317 String expression = rs.getString(3);
318 String functionName = rs.getString(4);
319 String attribute = rs.getString(5);
320 String paramType = rs.getString(6);
321
322 StringBuilder fgNamebuf = new StringBuilder();
323 fgNamebuf.append(groupType);
324 fgNamebuf.append("@");
325 if (groupName != null) {
326 fgNamebuf.append(groupName);
327 }
328
329
330 if (functionGroup == null || !fgNamebuf.toString().equals(lastFunctionGroupName)) {
331 functionGroup = new CpoFunctionGroup();
332 functionGroup.setType(groupType);
333 functionGroup.setName(groupName);
334
335 functionGroups.add(functionGroup);
336
337
338 function = null;
339
340 lastFunctionGroupName = fgNamebuf.toString();
341 }
342
343
344 if (function == null || !function.getExpression().equals(expression)) {
345
346 if (expression == null || expression.isEmpty()) {
347
348 getLog().warn("Query Group[" + groupName + "] contained no sql, so ignoring it");
349 } else {
350 if (functionName == null || functionName.isEmpty()) {
351
352 getLog().warn("Function Name [" + functionName + "] was null, so using group name [" + groupName + "]");
353 functionName = groupName;
354 }
355 function = new CpoFunction();
356 function.setName(functionName);
357 function.setExpression(expression);
358
359 functionGroup.addFunction(function);
360 }
361 }
362
363 if (attribute != null && function != null) {
364 JdbcCpoArgument argument = new JdbcCpoArgument();
365 argument.setAttributeName(attribute);
366 argument.setScope(paramType);
367
368 function.addArgument(argument);
369 }
370 }
371 } catch (SQLException ex) {
372 String msg = "loadAttributeMap() failed:'" + sql + "' classname:" + cpoClass.getName();
373 getLog().error(msg, ex);
374 } finally {
375 if (rs != null) {
376 try {
377 rs.close();
378 } catch (Exception e) {
379
380 if (getLog().isDebugEnabled()) {
381 getLog().debug(e.getMessage());
382 }
383 }
384 }
385
386 if (ps != null) {
387 try {
388 ps.close();
389 } catch (Exception e) {
390
391 if (getLog().isDebugEnabled()) {
392 getLog().debug(e.getMessage());
393 }
394 }
395 }
396 }
397
398 return functionGroups;
399 }
400 }
401