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)
51 if(rs.getSQL().toLowerCase().trim().equals("select 1")){
55 checkAndBreakSubQueries(rs, adapter);
56 if (rs.getSubQueries().size() == 0) {
57 parseTables(rs, adapter.getBluePrint());
58 parseFields(rs, adapter.getBluePrint());
59 parseCriteria(rs, adapter.getBluePrint());
62 } catch (Exception err) {
63 throw new SQLException("Error", err);
66 parseExternalQuery(rs);
70 public static void parseExternalQuery(JDBCResultSet rs) throws SQLException {
71 String sql = rs.getSQL();
72 for (Map.Entry<String, JDBCResultSet> entry : rs.getSubQueries()
74 int index = sql.toLowerCase().indexOf(entry.getValue().getSQL());
75 String extSql = sql.substring(0, index);
76 index = extSql.lastIndexOf("(");
77 extSql = extSql.substring(0, index);
78 System.out.println("External SQL=" + extSql);
79 parseLogicalFields(extSql, rs);
83 public static void parseLogicalFields(String sql, JDBCResultSet rs)
85 if(sql.trim().toLowerCase().equals("select * from")){
86 for (Map.Entry<String, JDBCResultSet> entry : rs.getSubQueries().entrySet()) {
87 for(XSQLBluePrintNode node:entry.getValue().getTables()){
88 rs.addTableToQuery(node);
90 rs.getFields().addAll(entry.getValue().getFields());
91 while (entry.getValue().next()) {
92 Map<String, Object> rec = entry.getValue().getCurrent();
93 Map<String, Object> newRec = new HashMap<>();
102 Map<String, XSQLBluePrintNode> logicalNameToNode = new HashMap<String, XSQLBluePrintNode>();
103 Map<String, String> origNameToName = new HashMap<String, String>();
104 List<XSQLColumn> columnOrder = new ArrayList<>();
105 int nextLogField = addNextLogicalField(sql, 0,
106 logicalNameToNode, origNameToName,columnOrder);
107 int next = sql.toLowerCase().indexOf(" as ", nextLogField);
109 nextLogField = addNextLogicalField(sql, nextLogField + 1,
110 logicalNameToNode, origNameToName,columnOrder);
111 next = sql.toLowerCase().indexOf(" as ", nextLogField + 1);
114 for (XSQLBluePrintNode node : logicalNameToNode.values()) {
115 rs.addTableToQuery(node);
117 rs.getFields().addAll(columnOrder);
118 for (Map.Entry<String, JDBCResultSet> entry : rs.getSubQueries().entrySet()) {
119 while (entry.getValue().next()) {
120 Map<String, Object> rec = entry.getValue().getCurrent();
121 Map<String, Object> newRec = new HashMap<>();
122 for (Map.Entry<String, Object> e : rec.entrySet()) {
123 Object value = e.getValue();
124 String logicalKey = origNameToName.get(e.getKey());
125 if (value != null && logicalKey != null) {
126 newRec.put(logicalKey, value);
129 rs.addRecord(newRec);
132 rs.setFinished(true);
135 public static void main(String args[]) {
136 String sql = "SELECT DISTINCT"
137 + "\"LOGICAL_TABLE_1\".\"nodes/node.id\" AS \"COL0\"\n"
138 + ",\"LOGICAL_TABLE_1\".\"nodes/node.id\" AS \"COL1\"\n"
139 + ",\"LOGICAL_TABLE_1\".\"nodes/node.id\" AS \"COL2\"\n"
141 + "(select * from nodes/node;) \"LOGICAL_TABLE_1\"\n";
142 JDBCResultSet rs = new JDBCResultSet(sql);
144 parseLogicalFields(sql, rs);
145 } catch (Exception err) {
146 err.printStackTrace();
150 public static int addNextLogicalField(String sql, int startIndex,
151 Map<String, XSQLBluePrintNode> logicalNameToNode,
152 Map<String, String> origNameToName, List<XSQLColumn> columnOrder) {
153 int index1 = sql.indexOf("\"", startIndex);
154 int index2 = sql.indexOf("\".\"", index1);
155 int index3 = sql.indexOf("\"", index2 + 3);
156 int index4 = sql.toLowerCase().indexOf(" as ", startIndex);
157 int index5 = sql.indexOf("\"", index4);
158 int index6 = sql.indexOf("\"", index5 + 1);
160 String tblName = sql.substring(index1 + 1, index2);
161 String origFieldNameFull = sql.substring(index2 + 3, index3);
162 String origTableName = "";
163 String origFieldName = "";
164 if (origFieldNameFull.indexOf(".") != -1) {
165 origTableName = origFieldNameFull.substring(0,origFieldNameFull.indexOf("."));
166 origFieldName = origFieldNameFull.substring(origFieldNameFull.indexOf(".") + 1);
168 String logicalFieldName = sql.substring(index5 + 1, index6);
169 XSQLBluePrintNode node = logicalNameToNode.get(tblName);
171 node = new XSQLBluePrintNode(tblName, origTableName, 0);
172 logicalNameToNode.put(tblName, node);
174 columnOrder.add(node.addColumn(logicalFieldName, tblName, origFieldName, origTableName));
175 origNameToName.put(origFieldNameFull, tblName + "." + logicalFieldName);
179 public static void checkAndBreakSubQueries(JDBCResultSet rs,XSQLAdapter adapter) throws SQLException {
180 String sql = rs.getSQL().toLowerCase();
181 int index = sql.indexOf("select");
183 throw new SQLException("Select statement is missing...");
184 int index2 = sql.indexOf("select", index + 6);
186 int startSubQuery = index2;
187 for (int i = startSubQuery; i >= 0; i--) {
188 if (sql.charAt(i) == '(') {
194 int endSubQuery = startSubQuery;
196 if (sql.charAt(endSubQuery) == '(') {
199 else if (sql.charAt(endSubQuery) == ')') {
203 } while (braketCount > 0 || endSubQuery == sql.length());
204 String subQuerySQL = sql.substring(startSubQuery + 1,endSubQuery - 1);
205 if(rs.getSQL().toLowerCase().substring(0,startSubQuery).trim().equals("select * from")){
206 rs.setSQL(subQuerySQL);
209 index = sql.indexOf("\"", endSubQuery);
210 index2 = sql.indexOf("\"", index + 1);
213 index2 = sql.length();
215 String logicalName = rs.getSQL().substring(index + 1, index2).trim();
216 JDBCResultSet subRS = rs.addSubQuery(subQuerySQL, logicalName);
217 JDBCServer.execute(subRS, adapter);
221 public static void parseTables(JDBCResultSet rs, XSQLBluePrint bp)
222 throws SQLException {
223 String lowSQL = rs.getSQL().toLowerCase();
224 int from = lowSQL.indexOf("from");
225 int where = lowSQL.indexOf("where");
226 int subQuery = lowSQL.indexOf("select", 2);
227 int fromTo = lowSQL.indexOf(";");
229 if (where != -1 && subQuery != -1 && where < subQuery) {
231 } else if (where != -1 && subQuery != -1 && where > subQuery) {
233 } else if (where != -1) {
235 } else if (subQuery != -1) {
240 throw new SQLException("Missing \"from\" statement.");
244 throw new SQLException("Missing terminating \";\".");
247 String tableNames = rs.getSQL().substring(from + 4, fromTo).trim();
248 StringTokenizer tokens = new StringTokenizer(tableNames, ",");
249 while (tokens.hasMoreTokens()) {
250 String tableName = tokens.nextToken().trim();
251 XSQLBluePrintNode table = bp.getBluePrintNodeByTableName(tableName);
253 throw new SQLException("Unknown table name \"" + tableName
256 rs.addTableToQuery(table);
260 public static void addCriteria(XSQLColumn col, XSQLCriteria c,
262 Map<XSQLColumn, List<XSQLCriteria>> tblCriteria = rs.getCriteria().get(
264 if (tblCriteria == null) {
265 tblCriteria = new ConcurrentHashMap<XSQLColumn, List<XSQLCriteria>>();
266 rs.getCriteria().put(col.getTableName(), tblCriteria);
268 List<XSQLCriteria> lstCriteria = tblCriteria.get(col);
269 if (lstCriteria == null) {
270 lstCriteria = new ArrayList<XSQLCriteria>();
271 tblCriteria.put(col, lstCriteria);
276 public static void parseFields(JDBCResultSet rs, XSQLBluePrint bp)
277 throws SQLException {
278 String lowSQL = rs.getSQL().toLowerCase();
279 if (!lowSQL.startsWith("select")) {
280 throw new SQLException("Missing 'select' statement.");
282 int from = lowSQL.indexOf("from");
284 throw new SQLException("Missing 'from' statement.");
286 String fields = rs.getSQL().substring(6, from).trim();
287 StringTokenizer tokens = new StringTokenizer(fields, ",");
288 while (tokens.hasMoreTokens()) {
289 String token = tokens.nextToken().trim();
290 if (token.equals("*")) {
291 for (XSQLBluePrintNode table : rs.getTables()) {
292 rs.getFields().addAll(table.getColumns());
296 if (token.indexOf(".") != -1) {
297 XSQLBluePrintNode tbl = bp.getBluePrintNodeByTableName(token
298 .substring(0, token.indexOf(".")).trim());
299 String p = token.substring(token.indexOf(".") + 1);
301 for (XSQLColumn c : tbl.getColumns()) {
302 rs.getFields().add(c);
305 XSQLColumn col = tbl.findColumnByName(p);
306 rs.getFields().add(col);
309 XSQLColumn col = null;
310 for (XSQLBluePrintNode table : rs.getTables()) {
312 col = table.findColumnByName(token);
313 } catch (Exception err) {
320 throw new SQLException("Unknown field name '" + token
324 rs.getFields().add(col);
329 public static void parseCriteria(JDBCResultSet rs, XSQLBluePrint bp) {
330 String lowSQL = rs.getSQL().toLowerCase();
331 int where = lowSQL.indexOf("where");
332 int order = lowSQL.indexOf("order");
333 int whereTo = lowSQL.indexOf(";");
344 whereTo=lowSQL.length();
347 String whereStatement = rs.getSQL().substring(where + 5, whereTo)
349 XSQLCriteria cr = new XSQLCriteria(whereStatement, -1);
350 for (XSQLBluePrintNode tbl : rs.getTables()) {
351 for (XSQLColumn col : tbl.getColumns()) {
352 String colCriteria = cr.getCriteriaForProperty(col);
353 if (colCriteria != null && !colCriteria.trim().equals("")) {
354 addCriteria(col, new XSQLCriteria(colCriteria, -1), rs);