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