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;
8 import java.util.Iterator;
11 import java.util.StringTokenizer;
12 import java.util.concurrent.ConcurrentHashMap;
14 import org.opendaylight.controller.md.sal.dom.xsql.XSQLAdapter;
15 import org.opendaylight.controller.md.sal.dom.xsql.XSQLBluePrint;
16 import org.opendaylight.controller.md.sal.dom.xsql.XSQLBluePrintNode;
17 import org.opendaylight.controller.md.sal.dom.xsql.XSQLColumn;
18 import org.opendaylight.controller.md.sal.dom.xsql.XSQLCriteria;
20 public class JDBCServer extends Thread {
21 private ServerSocket socket = null;
22 private XSQLAdapter adapter = null;
24 public JDBCServer(XSQLAdapter a) {
32 socket = new ServerSocket(40004);
33 while (!adapter.stopped) {
34 Socket s = socket.accept();
35 new JDBCConnection(s, adapter);
37 } catch (Exception err) {
38 err.printStackTrace();
42 public void connectToClient(String addr) {
44 Socket s = new Socket(addr, 50003);
45 new JDBCConnection(s, adapter);
46 } catch (Exception err) {
47 err.printStackTrace();
51 public static void execute(JDBCResultSet rs, XSQLAdapter adapter)
53 if(rs.getSQL().toLowerCase().trim().equals("select 1")){
57 checkAndBreakSubQueries(rs, adapter);
58 if (rs.getSubQueries().size() == 0) {
59 parseTables(rs, adapter.getBluePrint());
60 parseFields(rs, adapter.getBluePrint());
61 parseCriteria(rs, adapter.getBluePrint());
64 } catch (Exception err) {
65 throw new SQLException("Error", err);
68 parseExternalQuery(rs);
72 public static void parseExternalQuery(JDBCResultSet rs) throws SQLException {
73 String sql = rs.getSQL();
74 for (Map.Entry<String, JDBCResultSet> entry : rs.getSubQueries()
76 int index = sql.toLowerCase().indexOf(entry.getValue().getSQL());
77 String extSql = sql.substring(0, index);
78 index = extSql.lastIndexOf("(");
79 extSql = extSql.substring(0, index);
80 System.out.println("External SQL=" + extSql);
81 parseLogicalFields(extSql, rs);
85 public static void parseLogicalFields(String sql, JDBCResultSet rs)
87 if(sql.trim().toLowerCase().equals("select * from")){
88 for (Map.Entry<String, JDBCResultSet> entry : rs.getSubQueries().entrySet()) {
89 for(XSQLBluePrintNode node:entry.getValue().getTables()){
90 rs.addTableToQuery(node);
92 rs.getFields().addAll(entry.getValue().getFields());
93 while (entry.getValue().next()) {
94 Map rec = entry.getValue().getCurrent();
95 Map newRec = new HashMap();
100 rs.setFinished(true);
104 Map<String, XSQLBluePrintNode> logicalNameToNode = new HashMap<String, XSQLBluePrintNode>();
105 Map<String, String> origNameToName = new HashMap<String, String>();
106 List<XSQLColumn> columnOrder = new ArrayList<>();
107 int nextLogField = addNextLogicalField(sql, 0,
108 logicalNameToNode, origNameToName,columnOrder);
109 int next = sql.toLowerCase().indexOf(" as ", nextLogField);
111 nextLogField = addNextLogicalField(sql, nextLogField + 1,
112 logicalNameToNode, origNameToName,columnOrder);
113 next = sql.toLowerCase().indexOf(" as ", nextLogField + 1);
116 for (XSQLBluePrintNode node : logicalNameToNode.values()) {
117 rs.addTableToQuery(node);
119 rs.getFields().addAll(columnOrder);
120 for (Map.Entry<String, JDBCResultSet> entry : rs.getSubQueries().entrySet()) {
121 while (entry.getValue().next()) {
122 Map rec = entry.getValue().getCurrent();
123 Map newRec = new HashMap();
124 for (Iterator iter = rec.entrySet().iterator(); iter.hasNext();) {
125 Map.Entry e = (Map.Entry) iter.next();
126 String key = (String) e.getKey();
127 Object value = e.getValue();
128 String logicalKey = origNameToName.get(key);
129 if (value != null && logicalKey != null) {
130 newRec.put(logicalKey, value);
133 rs.addRecord(newRec);
136 rs.setFinished(true);
139 public static void main(String args[]) {
140 String sql = "SELECT DISTINCT"
141 + "\"LOGICAL_TABLE_1\".\"nodes/node.id\" AS \"COL0\"\n"
142 + ",\"LOGICAL_TABLE_1\".\"nodes/node.id\" AS \"COL1\"\n"
143 + ",\"LOGICAL_TABLE_1\".\"nodes/node.id\" AS \"COL2\"\n"
145 + "(select * from nodes/node;) \"LOGICAL_TABLE_1\"\n";
146 JDBCResultSet rs = new JDBCResultSet(sql);
148 parseLogicalFields(sql, rs);
149 } catch (Exception err) {
150 err.printStackTrace();
154 public static int addNextLogicalField(String sql, int startIndex,
155 Map<String, XSQLBluePrintNode> logicalNameToNode,
156 Map<String, String> origNameToName, List<XSQLColumn> columnOrder) {
157 int index1 = sql.indexOf("\"", startIndex);
158 int index2 = sql.indexOf("\".\"", index1);
159 int index3 = sql.indexOf("\"", index2 + 3);
160 int index4 = sql.toLowerCase().indexOf(" as ", startIndex);
161 int index5 = sql.indexOf("\"", index4);
162 int index6 = sql.indexOf("\"", index5 + 1);
164 String tblName = sql.substring(index1 + 1, index2);
165 String origFieldNameFull = sql.substring(index2 + 3, index3);
166 String origTableName = "";
167 String origFieldName = "";
168 if (origFieldNameFull.indexOf(".") != -1) {
169 origTableName = origFieldNameFull.substring(0,origFieldNameFull.indexOf("."));
170 origFieldName = origFieldNameFull.substring(origFieldNameFull.indexOf(".") + 1);
172 String logicalFieldName = sql.substring(index5 + 1, index6);
173 XSQLBluePrintNode node = logicalNameToNode.get(tblName);
175 node = new XSQLBluePrintNode(tblName, origTableName, 0);
176 logicalNameToNode.put(tblName, node);
178 columnOrder.add(node.addColumn(logicalFieldName, tblName, origFieldName, origTableName));
179 origNameToName.put(origFieldNameFull, tblName + "." + logicalFieldName);
183 public static void checkAndBreakSubQueries(JDBCResultSet rs,XSQLAdapter adapter) throws SQLException {
184 String sql = rs.getSQL().toLowerCase();
185 int index = sql.indexOf("select");
187 throw new SQLException("Select statement is missing...");
188 int index2 = sql.indexOf("select", index + 6);
190 int startSubQuery = index2;
191 for (int i = startSubQuery; i >= 0; i--) {
192 if (sql.charAt(i) == '(') {
198 int endSubQuery = startSubQuery;
200 if (sql.charAt(endSubQuery) == '(')
202 else if (sql.charAt(endSubQuery) == ')')
205 } while (braketCount > 0 || endSubQuery == sql.length());
206 String subQuerySQL = sql.substring(startSubQuery + 1,endSubQuery - 1);
207 if(rs.getSQL().toLowerCase().substring(0,startSubQuery).trim().equals("select * from")){
208 rs.setSQL(subQuerySQL);
211 index = sql.indexOf("\"", endSubQuery);
212 index2 = sql.indexOf("\"", index + 1);
215 index2 = sql.length();
217 String logicalName = rs.getSQL().substring(index + 1, index2).trim();
218 JDBCResultSet subRS = rs.addSubQuery(subQuerySQL, logicalName);
219 JDBCServer.execute(subRS, adapter);
223 public static void parseTables(JDBCResultSet rs, XSQLBluePrint bp)
224 throws SQLException {
225 String lowSQL = rs.getSQL().toLowerCase();
226 int from = lowSQL.indexOf("from");
227 int where = lowSQL.indexOf("where");
228 int subQuery = lowSQL.indexOf("select", 2);
229 int fromTo = lowSQL.indexOf(";");
231 if (where != -1 && subQuery != -1 && where < subQuery) {
233 } else if (where != -1 && subQuery != -1 && where > subQuery) {
235 } else if (where != -1) {
237 } else if (subQuery != -1) {
242 throw new SQLException("Missing \"from\" statement.");
246 throw new SQLException("Missing terminating \";\".");
249 String tableNames = rs.getSQL().substring(from + 4, fromTo).trim();
250 StringTokenizer tokens = new StringTokenizer(tableNames, ",");
251 while (tokens.hasMoreTokens()) {
252 String tableName = tokens.nextToken().trim();
253 XSQLBluePrintNode table = bp.getBluePrintNodeByTableName(tableName);
255 throw new SQLException("Unknown table name \"" + tableName
258 rs.addTableToQuery(table);
262 public static void addCriteria(XSQLColumn col, XSQLCriteria c,
264 Map<XSQLColumn, List<XSQLCriteria>> tblCriteria = rs.getCriteria().get(
266 if (tblCriteria == null) {
267 tblCriteria = new ConcurrentHashMap<XSQLColumn, List<XSQLCriteria>>();
268 rs.getCriteria().put(col.getTableName(), tblCriteria);
270 List<XSQLCriteria> lstCriteria = tblCriteria.get(col);
271 if (lstCriteria == null) {
272 lstCriteria = new ArrayList<XSQLCriteria>();
273 tblCriteria.put(col, lstCriteria);
278 public static void parseFields(JDBCResultSet rs, XSQLBluePrint bp)
279 throws SQLException {
280 String lowSQL = rs.getSQL().toLowerCase();
281 if (!lowSQL.startsWith("select")) {
282 throw new SQLException("Missing 'select' statement.");
284 int from = lowSQL.indexOf("from");
286 throw new SQLException("Missing 'from' statement.");
288 String fields = rs.getSQL().substring(6, from).trim();
289 StringTokenizer tokens = new StringTokenizer(fields, ",");
290 while (tokens.hasMoreTokens()) {
291 String token = tokens.nextToken().trim();
292 if (token.equals("*")) {
293 for (XSQLBluePrintNode table : rs.getTables()) {
294 rs.getFields().addAll(table.getColumns());
298 if (token.indexOf(".") != -1) {
299 XSQLBluePrintNode tbl = bp.getBluePrintNodeByTableName(token
300 .substring(0, token.indexOf(".")).trim());
301 String p = token.substring(token.indexOf(".") + 1);
303 for (XSQLColumn c : tbl.getColumns()) {
304 rs.getFields().add(c);
307 XSQLColumn col = tbl.findColumnByName(p);
308 rs.getFields().add(col);
311 XSQLColumn col = null;
312 for (XSQLBluePrintNode table : rs.getTables()) {
314 col = table.findColumnByName(token);
315 } catch (Exception err) {
322 throw new SQLException("Unknown field name '" + token
326 rs.getFields().add(col);
331 public static void parseCriteria(JDBCResultSet rs, XSQLBluePrint bp) {
332 String lowSQL = rs.getSQL().toLowerCase();
333 int where = lowSQL.indexOf("where");
334 int order = lowSQL.indexOf("order");
335 int whereTo = lowSQL.indexOf(";");
346 whereTo=lowSQL.length();
348 String whereStatement = rs.getSQL().substring(where + 5, whereTo)
350 XSQLCriteria cr = new XSQLCriteria(whereStatement, -1);
351 for (XSQLBluePrintNode tbl : rs.getTables()) {
352 for (XSQLColumn col : tbl.getColumns()) {
353 String colCriteria = cr.getCriteriaForProperty(col);
354 if (colCriteria != null && !colCriteria.trim().equals("")) {
355 addCriteria(col, new XSQLCriteria(colCriteria, -1), rs);