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) == '(')
198 else if (sql.charAt(endSubQuery) == ')')
201 } while (braketCount > 0 || endSubQuery == sql.length());
202 String subQuerySQL = sql.substring(startSubQuery + 1,endSubQuery - 1);
203 if(rs.getSQL().toLowerCase().substring(0,startSubQuery).trim().equals("select * from")){
204 rs.setSQL(subQuerySQL);
207 index = sql.indexOf("\"", endSubQuery);
208 index2 = sql.indexOf("\"", index + 1);
211 index2 = sql.length();
213 String logicalName = rs.getSQL().substring(index + 1, index2).trim();
214 JDBCResultSet subRS = rs.addSubQuery(subQuerySQL, logicalName);
215 JDBCServer.execute(subRS, adapter);
219 public static void parseTables(JDBCResultSet rs, XSQLBluePrint bp)
220 throws SQLException {
221 String lowSQL = rs.getSQL().toLowerCase();
222 int from = lowSQL.indexOf("from");
223 int where = lowSQL.indexOf("where");
224 int subQuery = lowSQL.indexOf("select", 2);
225 int fromTo = lowSQL.indexOf(";");
227 if (where != -1 && subQuery != -1 && where < subQuery) {
229 } else if (where != -1 && subQuery != -1 && where > subQuery) {
231 } else if (where != -1) {
233 } else if (subQuery != -1) {
238 throw new SQLException("Missing \"from\" statement.");
242 throw new SQLException("Missing terminating \";\".");
245 String tableNames = rs.getSQL().substring(from + 4, fromTo).trim();
246 StringTokenizer tokens = new StringTokenizer(tableNames, ",");
247 while (tokens.hasMoreTokens()) {
248 String tableName = tokens.nextToken().trim();
249 XSQLBluePrintNode table = bp.getBluePrintNodeByTableName(tableName);
251 throw new SQLException("Unknown table name \"" + tableName
254 rs.addTableToQuery(table);
258 public static void addCriteria(XSQLColumn col, XSQLCriteria c,
260 Map<XSQLColumn, List<XSQLCriteria>> tblCriteria = rs.getCriteria().get(
262 if (tblCriteria == null) {
263 tblCriteria = new ConcurrentHashMap<XSQLColumn, List<XSQLCriteria>>();
264 rs.getCriteria().put(col.getTableName(), tblCriteria);
266 List<XSQLCriteria> lstCriteria = tblCriteria.get(col);
267 if (lstCriteria == null) {
268 lstCriteria = new ArrayList<XSQLCriteria>();
269 tblCriteria.put(col, lstCriteria);
274 public static void parseFields(JDBCResultSet rs, XSQLBluePrint bp)
275 throws SQLException {
276 String lowSQL = rs.getSQL().toLowerCase();
277 if (!lowSQL.startsWith("select")) {
278 throw new SQLException("Missing 'select' statement.");
280 int from = lowSQL.indexOf("from");
282 throw new SQLException("Missing 'from' statement.");
284 String fields = rs.getSQL().substring(6, from).trim();
285 StringTokenizer tokens = new StringTokenizer(fields, ",");
286 while (tokens.hasMoreTokens()) {
287 String token = tokens.nextToken().trim();
288 if (token.equals("*")) {
289 for (XSQLBluePrintNode table : rs.getTables()) {
290 rs.getFields().addAll(table.getColumns());
294 if (token.indexOf(".") != -1) {
295 XSQLBluePrintNode tbl = bp.getBluePrintNodeByTableName(token
296 .substring(0, token.indexOf(".")).trim());
297 String p = token.substring(token.indexOf(".") + 1);
299 for (XSQLColumn c : tbl.getColumns()) {
300 rs.getFields().add(c);
303 XSQLColumn col = tbl.findColumnByName(p);
304 rs.getFields().add(col);
307 XSQLColumn col = null;
308 for (XSQLBluePrintNode table : rs.getTables()) {
310 col = table.findColumnByName(token);
311 } catch (Exception err) {
318 throw new SQLException("Unknown field name '" + token
322 rs.getFields().add(col);
327 public static void parseCriteria(JDBCResultSet rs, XSQLBluePrint bp) {
328 String lowSQL = rs.getSQL().toLowerCase();
329 int where = lowSQL.indexOf("where");
330 int order = lowSQL.indexOf("order");
331 int whereTo = lowSQL.indexOf(";");
342 whereTo=lowSQL.length();
344 String whereStatement = rs.getSQL().substring(where + 5, whereTo)
346 XSQLCriteria cr = new XSQLCriteria(whereStatement, -1);
347 for (XSQLBluePrintNode tbl : rs.getTables()) {
348 for (XSQLColumn col : tbl.getColumns()) {
349 String colCriteria = cr.getCriteriaForProperty(col);
350 if (colCriteria != null && !colCriteria.trim().equals("")) {
351 addCriteria(col, new XSQLCriteria(colCriteria, -1), rs);