2 * Copyright (c) 2014, 2015 Cisco Systems, Inc. and others. All rights reserved.
4 * This program and the accompanying materials are made available under the
5 * terms of the Eclipse Public License v1.0 which accompanies this distribution,
6 * and is available at http://www.eclipse.org/legal/epl-v10.html
9 package org.opendaylight.controller.md.sal.dom.xsql.jdbc;
11 import java.net.ServerSocket;
12 import java.net.Socket;
13 import java.sql.SQLException;
14 import java.util.ArrayList;
15 import java.util.HashMap;
16 import java.util.List;
18 import java.util.StringTokenizer;
19 import java.util.concurrent.ConcurrentHashMap;
20 import org.opendaylight.controller.md.sal.dom.xsql.XSQLAdapter;
21 import org.opendaylight.controller.md.sal.dom.xsql.XSQLBluePrint;
22 import org.opendaylight.controller.md.sal.dom.xsql.XSQLBluePrintNode;
23 import org.opendaylight.controller.md.sal.dom.xsql.XSQLColumn;
24 import org.opendaylight.controller.md.sal.dom.xsql.XSQLCriteria;
27 * To be removed in Nitrogen
30 public class JDBCServer extends Thread {
31 private ServerSocket socket = null;
32 private XSQLAdapter adapter = null;
34 public JDBCServer(XSQLAdapter a) {
42 socket = new ServerSocket(40004);
43 while (!adapter.stopped) {
44 Socket s = socket.accept();
45 new JDBCConnection(s, adapter);
47 } catch (Exception err) {
48 err.printStackTrace();
52 public void connectToClient(String addr) {
54 Socket s = new Socket(addr, 50003);
55 new JDBCConnection(s, adapter);
56 } catch (Exception err) {
57 err.printStackTrace();
61 public static void execute(JDBCResultSet rs, XSQLAdapter adapter)throws SQLException {
62 if(rs.getSQL().toLowerCase().trim().equals("select 1")){
66 checkAndBreakSubQueries(rs, adapter);
67 if (rs.getSubQueries().size() == 0) {
68 parseTables(rs, adapter.getBluePrint());
69 parseFields(rs, adapter.getBluePrint());
70 parseCriteria(rs, adapter.getBluePrint());
73 } catch (Exception err) {
74 throw new SQLException("Error", err);
77 parseExternalQuery(rs);
81 public static void parseExternalQuery(JDBCResultSet rs) throws SQLException {
82 String sql = rs.getSQL();
83 for (Map.Entry<String, JDBCResultSet> entry : rs.getSubQueries()
85 int index = sql.toLowerCase().indexOf(entry.getValue().getSQL());
86 String extSql = sql.substring(0, index);
87 index = extSql.lastIndexOf("(");
88 extSql = extSql.substring(0, index);
89 System.out.println("External SQL=" + extSql);
90 parseLogicalFields(extSql, rs);
94 public static void parseLogicalFields(String sql, JDBCResultSet rs)
96 if(sql.trim().toLowerCase().equals("select * from")){
97 for (Map.Entry<String, JDBCResultSet> entry : rs.getSubQueries().entrySet()) {
98 for(XSQLBluePrintNode node:entry.getValue().getTables()){
99 rs.addTableToQuery(node);
101 rs.getFields().addAll(entry.getValue().getFields());
102 while (entry.getValue().next()) {
103 Map<String, Object> rec = entry.getValue().getCurrent();
104 Map<String, Object> newRec = new HashMap<>();
106 rs.addRecord(newRec);
109 rs.setFinished(true);
113 Map<String, XSQLBluePrintNode> logicalNameToNode = new HashMap<>();
114 Map<String, String> origNameToName = new HashMap<>();
115 List<XSQLColumn> columnOrder = new ArrayList<>();
116 int nextLogField = addNextLogicalField(sql, 0,
117 logicalNameToNode, origNameToName,columnOrder);
118 int next = sql.toLowerCase().indexOf(" as ", nextLogField);
120 nextLogField = addNextLogicalField(sql, nextLogField + 1,
121 logicalNameToNode, origNameToName,columnOrder);
122 next = sql.toLowerCase().indexOf(" as ", nextLogField + 1);
125 for (XSQLBluePrintNode node : logicalNameToNode.values()) {
126 rs.addTableToQuery(node);
128 rs.getFields().addAll(columnOrder);
129 for (Map.Entry<String, JDBCResultSet> entry : rs.getSubQueries().entrySet()) {
130 while (entry.getValue().next()) {
131 Map<String, Object> rec = entry.getValue().getCurrent();
132 Map<String, Object> newRec = new HashMap<>();
133 for (Map.Entry<String, Object> e : rec.entrySet()) {
134 Object value = e.getValue();
135 String logicalKey = origNameToName.get(e.getKey());
136 if (value != null && logicalKey != null) {
137 newRec.put(logicalKey, value);
140 rs.addRecord(newRec);
143 rs.setFinished(true);
146 public static void main(String args[]) {
147 String sql = "SELECT DISTINCT"
148 + "\"LOGICAL_TABLE_1\".\"nodes/node.id\" AS \"COL0\"\n"
149 + ",\"LOGICAL_TABLE_1\".\"nodes/node.id\" AS \"COL1\"\n"
150 + ",\"LOGICAL_TABLE_1\".\"nodes/node.id\" AS \"COL2\"\n"
152 + "(select * from nodes/node;) \"LOGICAL_TABLE_1\"\n";
153 JDBCResultSet rs = new JDBCResultSet(sql);
155 parseLogicalFields(sql, rs);
156 } catch (Exception err) {
157 err.printStackTrace();
161 public static int addNextLogicalField(String sql, int startIndex,
162 Map<String, XSQLBluePrintNode> logicalNameToNode,
163 Map<String, String> origNameToName, List<XSQLColumn> columnOrder) {
164 int index1 = sql.indexOf("\"", startIndex);
165 int index2 = sql.indexOf("\".\"", index1);
166 int index3 = sql.indexOf("\"", index2 + 3);
167 int index4 = sql.toLowerCase().indexOf(" as ", startIndex);
168 int index5 = sql.indexOf("\"", index4);
169 int index6 = sql.indexOf("\"", index5 + 1);
171 String tblName = sql.substring(index1 + 1, index2);
172 String origFieldNameFull = sql.substring(index2 + 3, index3);
173 String origTableName = "";
174 String origFieldName = "";
175 if (origFieldNameFull.indexOf(".") != -1) {
176 origTableName = origFieldNameFull.substring(0,origFieldNameFull.indexOf("."));
177 origFieldName = origFieldNameFull.substring(origFieldNameFull.indexOf(".") + 1);
179 String logicalFieldName = sql.substring(index5 + 1, index6);
180 XSQLBluePrintNode node = logicalNameToNode.get(tblName);
182 node = new XSQLBluePrintNode(tblName, origTableName, 0);
183 logicalNameToNode.put(tblName, node);
185 columnOrder.add(node.addColumn(logicalFieldName, tblName, origFieldName, origTableName));
186 origNameToName.put(origFieldNameFull, tblName + "." + logicalFieldName);
190 public static void checkAndBreakSubQueries(JDBCResultSet rs,XSQLAdapter adapter) throws SQLException {
191 String sql = rs.getSQL().toLowerCase();
192 int index = sql.indexOf("select");
194 throw new SQLException("Select statement is missing...");
195 int index2 = sql.indexOf("select", index + 6);
197 int startSubQuery = index2;
198 for (int i = startSubQuery; i >= 0; i--) {
199 if (sql.charAt(i) == '(') {
205 int endSubQuery = startSubQuery;
207 if (sql.charAt(endSubQuery) == '(') {
210 else if (sql.charAt(endSubQuery) == ')') {
214 } while (braketCount > 0 || endSubQuery == sql.length());
215 String subQuerySQL = sql.substring(startSubQuery + 1,endSubQuery - 1);
216 if(rs.getSQL().toLowerCase().substring(0,startSubQuery).trim().equals("select * from")){
217 rs.setSQL(subQuerySQL);
220 index = sql.indexOf("\"", endSubQuery);
221 index2 = sql.indexOf("\"", index + 1);
224 index2 = sql.length();
226 String logicalName = rs.getSQL().substring(index + 1, index2).trim();
227 JDBCResultSet subRS = rs.addSubQuery(subQuerySQL, logicalName);
228 JDBCServer.execute(subRS, adapter);
232 public static void parseTables(JDBCResultSet rs, XSQLBluePrint bp)
233 throws SQLException {
234 String lowSQL = rs.getSQL().toLowerCase();
235 int from = lowSQL.indexOf("from");
236 int where = lowSQL.indexOf("where");
237 int subQuery = lowSQL.indexOf("select", 2);
238 int fromTo = lowSQL.indexOf(";");
240 if (where != -1 && subQuery != -1 && where < subQuery) {
242 } else if (where != -1 && subQuery != -1 && where > subQuery) {
244 } else if (where != -1) {
246 } else if (subQuery != -1) {
251 throw new SQLException("Missing \"from\" statement.");
255 throw new SQLException("Missing terminating \";\".");
258 String tableNames = rs.getSQL().substring(from + 4, fromTo).trim();
259 StringTokenizer tokens = new StringTokenizer(tableNames, ",");
260 while (tokens.hasMoreTokens()) {
261 String tableName = tokens.nextToken().trim();
262 XSQLBluePrintNode table = bp.getBluePrintNodeByTableName(tableName);
264 throw new SQLException("Unknown table name \"" + tableName
267 rs.addTableToQuery(table);
271 public static void addCriteria(XSQLColumn col, XSQLCriteria c,
273 Map<XSQLColumn, List<XSQLCriteria>> tblCriteria = rs.getCriteria().get(
275 if (tblCriteria == null) {
276 tblCriteria = new ConcurrentHashMap<>();
277 rs.getCriteria().put(col.getTableName(), tblCriteria);
279 List<XSQLCriteria> lstCriteria = tblCriteria.get(col);
280 if (lstCriteria == null) {
281 lstCriteria = new ArrayList<>();
282 tblCriteria.put(col, lstCriteria);
287 public static void parseFields(JDBCResultSet rs, XSQLBluePrint bp)
288 throws SQLException {
289 String lowSQL = rs.getSQL().toLowerCase();
290 if (!lowSQL.startsWith("select")) {
291 throw new SQLException("Missing 'select' statement.");
293 int from = lowSQL.indexOf("from");
295 throw new SQLException("Missing 'from' statement.");
297 String fields = rs.getSQL().substring(6, from).trim();
298 StringTokenizer tokens = new StringTokenizer(fields, ",");
299 while (tokens.hasMoreTokens()) {
300 String token = tokens.nextToken().trim();
301 if (token.equals("*")) {
302 for (XSQLBluePrintNode table : rs.getTables()) {
303 rs.getFields().addAll(table.getColumns());
307 if (token.indexOf(".") != -1) {
308 XSQLBluePrintNode tbl = bp.getBluePrintNodeByTableName(token
309 .substring(0, token.indexOf(".")).trim());
310 String p = token.substring(token.indexOf(".") + 1);
312 for (XSQLColumn c : tbl.getColumns()) {
313 rs.getFields().add(c);
316 XSQLColumn col = tbl.findColumnByName(p);
317 rs.getFields().add(col);
320 XSQLColumn col = null;
321 for (XSQLBluePrintNode table : rs.getTables()) {
323 col = table.findColumnByName(token);
324 } catch (Exception err) {
331 throw new SQLException("Unknown field name '" + token
335 rs.getFields().add(col);
340 public static void parseCriteria(JDBCResultSet rs, XSQLBluePrint bp) {
341 String lowSQL = rs.getSQL().toLowerCase();
342 int where = lowSQL.indexOf("where");
343 int order = lowSQL.indexOf("order");
344 int whereTo = lowSQL.indexOf(";");
355 whereTo=lowSQL.length();
358 String whereStatement = rs.getSQL().substring(where + 5, whereTo)
360 XSQLCriteria cr = new XSQLCriteria(whereStatement, -1);
361 for (XSQLBluePrintNode tbl : rs.getTables()) {
362 for (XSQLColumn col : tbl.getColumns()) {
363 String colCriteria = cr.getCriteriaForProperty(col);
364 if (colCriteria != null && !colCriteria.trim().equals("")) {
365 addCriteria(col, new XSQLCriteria(colCriteria, -1), rs);