1 package org.opendaylight.controller.md.sal.dom.xsql.jdbc;
3 import java.net.ServerSocket;
4 import java.net.Socket;
5 import java.sql.SQLException;
6 import java.util.ArrayList;
7 import java.util.HashMap;
10 import java.util.StringTokenizer;
11 import java.util.concurrent.ConcurrentHashMap;
12 import org.opendaylight.controller.md.sal.dom.xsql.XSQLAdapter;
13 import org.opendaylight.controller.md.sal.dom.xsql.XSQLBluePrint;
14 import org.opendaylight.controller.md.sal.dom.xsql.XSQLBluePrintNode;
15 import org.opendaylight.controller.md.sal.dom.xsql.XSQLColumn;
16 import org.opendaylight.controller.md.sal.dom.xsql.XSQLCriteria;
18 public class JDBCServer extends Thread {
19 private ServerSocket socket = null;
20 private XSQLAdapter adapter = null;
22 public JDBCServer(XSQLAdapter a) {
30 socket = new ServerSocket(40004);
31 while (!adapter.stopped) {
32 Socket s = socket.accept();
33 new JDBCConnection(s, adapter);
35 } catch (Exception err) {
36 err.printStackTrace();
40 public void connectToClient(String addr) {
42 Socket s = new Socket(addr, 50003);
43 new JDBCConnection(s, adapter);
44 } catch (Exception err) {
45 err.printStackTrace();
49 public static void execute(JDBCResultSet rs, XSQLAdapter adapter)throws SQLException {
50 if(rs.getSQL().toLowerCase().trim().equals("select 1")){
54 checkAndBreakSubQueries(rs, adapter);
55 if (rs.getSubQueries().size() == 0) {
56 parseTables(rs, adapter.getBluePrint());
57 parseFields(rs, adapter.getBluePrint());
58 parseCriteria(rs, adapter.getBluePrint());
61 } catch (Exception err) {
62 throw new SQLException("Error", err);
65 parseExternalQuery(rs);
69 public static void parseExternalQuery(JDBCResultSet rs) throws SQLException {
70 String sql = rs.getSQL();
71 for (Map.Entry<String, JDBCResultSet> entry : rs.getSubQueries()
73 int index = sql.toLowerCase().indexOf(entry.getValue().getSQL());
74 String extSql = sql.substring(0, index);
75 index = extSql.lastIndexOf("(");
76 extSql = extSql.substring(0, index);
77 System.out.println("External SQL=" + extSql);
78 parseLogicalFields(extSql, rs);
82 public static void parseLogicalFields(String sql, JDBCResultSet rs)
84 if(sql.trim().toLowerCase().equals("select * from")){
85 for (Map.Entry<String, JDBCResultSet> entry : rs.getSubQueries().entrySet()) {
86 for(XSQLBluePrintNode node:entry.getValue().getTables()){
87 rs.addTableToQuery(node);
89 rs.getFields().addAll(entry.getValue().getFields());
90 while (entry.getValue().next()) {
91 Map<String, Object> rec = entry.getValue().getCurrent();
92 Map<String, Object> newRec = new HashMap<>();
101 Map<String, XSQLBluePrintNode> logicalNameToNode = new HashMap<String, XSQLBluePrintNode>();
102 Map<String, String> origNameToName = new HashMap<String, String>();
103 List<XSQLColumn> columnOrder = new ArrayList<>();
104 int nextLogField = addNextLogicalField(sql, 0,
105 logicalNameToNode, origNameToName,columnOrder);
106 int next = sql.toLowerCase().indexOf(" as ", nextLogField);
108 nextLogField = addNextLogicalField(sql, nextLogField + 1,
109 logicalNameToNode, origNameToName,columnOrder);
110 next = sql.toLowerCase().indexOf(" as ", nextLogField + 1);
113 for (XSQLBluePrintNode node : logicalNameToNode.values()) {
114 rs.addTableToQuery(node);
116 rs.getFields().addAll(columnOrder);
117 for (Map.Entry<String, JDBCResultSet> entry : rs.getSubQueries().entrySet()) {
118 while (entry.getValue().next()) {
119 Map<String, Object> rec = entry.getValue().getCurrent();
120 Map<String, Object> newRec = new HashMap<>();
121 for (Map.Entry<String, Object> e : rec.entrySet()) {
122 Object value = e.getValue();
123 String logicalKey = origNameToName.get(e.getKey());
124 if (value != null && logicalKey != null) {
125 newRec.put(logicalKey, value);
128 rs.addRecord(newRec);
131 rs.setFinished(true);
134 public static void main(String args[]) {
135 String sql = "SELECT DISTINCT"
136 + "\"LOGICAL_TABLE_1\".\"nodes/node.id\" AS \"COL0\"\n"
137 + ",\"LOGICAL_TABLE_1\".\"nodes/node.id\" AS \"COL1\"\n"
138 + ",\"LOGICAL_TABLE_1\".\"nodes/node.id\" AS \"COL2\"\n"
140 + "(select * from nodes/node;) \"LOGICAL_TABLE_1\"\n";
141 JDBCResultSet rs = new JDBCResultSet(sql);
143 parseLogicalFields(sql, rs);
144 } catch (Exception err) {
145 err.printStackTrace();
149 public static int addNextLogicalField(String sql, int startIndex,
150 Map<String, XSQLBluePrintNode> logicalNameToNode,
151 Map<String, String> origNameToName, List<XSQLColumn> columnOrder) {
152 int index1 = sql.indexOf("\"", startIndex);
153 int index2 = sql.indexOf("\".\"", index1);
154 int index3 = sql.indexOf("\"", index2 + 3);
155 int index4 = sql.toLowerCase().indexOf(" as ", startIndex);
156 int index5 = sql.indexOf("\"", index4);
157 int index6 = sql.indexOf("\"", index5 + 1);
159 String tblName = sql.substring(index1 + 1, index2);
160 String origFieldNameFull = sql.substring(index2 + 3, index3);
161 String origTableName = "";
162 String origFieldName = "";
163 if (origFieldNameFull.indexOf(".") != -1) {
164 origTableName = origFieldNameFull.substring(0,origFieldNameFull.indexOf("."));
165 origFieldName = origFieldNameFull.substring(origFieldNameFull.indexOf(".") + 1);
167 String logicalFieldName = sql.substring(index5 + 1, index6);
168 XSQLBluePrintNode node = logicalNameToNode.get(tblName);
170 node = new XSQLBluePrintNode(tblName, origTableName, 0);
171 logicalNameToNode.put(tblName, node);
173 columnOrder.add(node.addColumn(logicalFieldName, tblName, origFieldName, origTableName));
174 origNameToName.put(origFieldNameFull, tblName + "." + logicalFieldName);
178 public static void checkAndBreakSubQueries(JDBCResultSet rs,XSQLAdapter adapter) throws SQLException {
179 String sql = rs.getSQL().toLowerCase();
180 int index = sql.indexOf("select");
182 throw new SQLException("Select statement is missing...");
183 int index2 = sql.indexOf("select", index + 6);
185 int startSubQuery = index2;
186 for (int i = startSubQuery; i >= 0; i--) {
187 if (sql.charAt(i) == '(') {
193 int endSubQuery = startSubQuery;
195 if (sql.charAt(endSubQuery) == '(') {
198 else if (sql.charAt(endSubQuery) == ')') {
202 } while (braketCount > 0 || endSubQuery == sql.length());
203 String subQuerySQL = sql.substring(startSubQuery + 1,endSubQuery - 1);
204 if(rs.getSQL().toLowerCase().substring(0,startSubQuery).trim().equals("select * from")){
205 rs.setSQL(subQuerySQL);
208 index = sql.indexOf("\"", endSubQuery);
209 index2 = sql.indexOf("\"", index + 1);
212 index2 = sql.length();
214 String logicalName = rs.getSQL().substring(index + 1, index2).trim();
215 JDBCResultSet subRS = rs.addSubQuery(subQuerySQL, logicalName);
216 JDBCServer.execute(subRS, adapter);
220 public static void parseTables(JDBCResultSet rs, XSQLBluePrint bp)
221 throws SQLException {
222 String lowSQL = rs.getSQL().toLowerCase();
223 int from = lowSQL.indexOf("from");
224 int where = lowSQL.indexOf("where");
225 int subQuery = lowSQL.indexOf("select", 2);
226 int fromTo = lowSQL.indexOf(";");
228 if (where != -1 && subQuery != -1 && where < subQuery) {
230 } else if (where != -1 && subQuery != -1 && where > subQuery) {
232 } else if (where != -1) {
234 } else if (subQuery != -1) {
239 throw new SQLException("Missing \"from\" statement.");
243 throw new SQLException("Missing terminating \";\".");
246 String tableNames = rs.getSQL().substring(from + 4, fromTo).trim();
247 StringTokenizer tokens = new StringTokenizer(tableNames, ",");
248 while (tokens.hasMoreTokens()) {
249 String tableName = tokens.nextToken().trim();
250 XSQLBluePrintNode table = bp.getBluePrintNodeByTableName(tableName);
252 throw new SQLException("Unknown table name \"" + tableName
255 rs.addTableToQuery(table);
259 public static void addCriteria(XSQLColumn col, XSQLCriteria c,
261 Map<XSQLColumn, List<XSQLCriteria>> tblCriteria = rs.getCriteria().get(
263 if (tblCriteria == null) {
264 tblCriteria = new ConcurrentHashMap<XSQLColumn, List<XSQLCriteria>>();
265 rs.getCriteria().put(col.getTableName(), tblCriteria);
267 List<XSQLCriteria> lstCriteria = tblCriteria.get(col);
268 if (lstCriteria == null) {
269 lstCriteria = new ArrayList<XSQLCriteria>();
270 tblCriteria.put(col, lstCriteria);
275 public static void parseFields(JDBCResultSet rs, XSQLBluePrint bp)
276 throws SQLException {
277 String lowSQL = rs.getSQL().toLowerCase();
278 if (!lowSQL.startsWith("select")) {
279 throw new SQLException("Missing 'select' statement.");
281 int from = lowSQL.indexOf("from");
283 throw new SQLException("Missing 'from' statement.");
285 String fields = rs.getSQL().substring(6, from).trim();
286 StringTokenizer tokens = new StringTokenizer(fields, ",");
287 while (tokens.hasMoreTokens()) {
288 String token = tokens.nextToken().trim();
289 if (token.equals("*")) {
290 for (XSQLBluePrintNode table : rs.getTables()) {
291 rs.getFields().addAll(table.getColumns());
295 if (token.indexOf(".") != -1) {
296 XSQLBluePrintNode tbl = bp.getBluePrintNodeByTableName(token
297 .substring(0, token.indexOf(".")).trim());
298 String p = token.substring(token.indexOf(".") + 1);
300 for (XSQLColumn c : tbl.getColumns()) {
301 rs.getFields().add(c);
304 XSQLColumn col = tbl.findColumnByName(p);
305 rs.getFields().add(col);
308 XSQLColumn col = null;
309 for (XSQLBluePrintNode table : rs.getTables()) {
311 col = table.findColumnByName(token);
312 } catch (Exception err) {
319 throw new SQLException("Unknown field name '" + token
323 rs.getFields().add(col);
328 public static void parseCriteria(JDBCResultSet rs, XSQLBluePrint bp) {
329 String lowSQL = rs.getSQL().toLowerCase();
330 int where = lowSQL.indexOf("where");
331 int order = lowSQL.indexOf("order");
332 int whereTo = lowSQL.indexOf(";");
343 whereTo=lowSQL.length();
346 String whereStatement = rs.getSQL().substring(where + 5, whereTo)
348 XSQLCriteria cr = new XSQLCriteria(whereStatement, -1);
349 for (XSQLBluePrintNode tbl : rs.getTables()) {
350 for (XSQLColumn col : tbl.getColumns()) {
351 String colCriteria = cr.getCriteriaForProperty(col);
352 if (colCriteria != null && !colCriteria.trim().equals("")) {
353 addCriteria(col, new XSQLCriteria(colCriteria, -1), rs);