| /* |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, |
| * software distributed under the License is distributed on an |
| * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| * KIND, either express or implied. See the License for the |
| * specific language governing permissions and limitations |
| * under the License. |
| */ |
| package org.apache.empire.samples.db.advanced; |
| |
| import org.apache.empire.commons.StringUtils; |
| import org.apache.empire.db.DBColumn; |
| import org.apache.empire.db.DBDatabase; |
| import org.apache.empire.db.DBTable; |
| |
| /** |
| * Temporary class for HTML-generation of code and SQL |
| * Will be removed again later |
| * @author rainer |
| */ |
| public class HtmlGenUtil |
| { |
| public static final String codeModelQuery = "// create command\r\n" + |
| "DBCommand cmd = context.createCommand()\r\n" + |
| " .select (BRAND.NAME, MODEL.CONFIG_NAME, MODEL.BASE_PRICE)\r\n" + |
| " .select (SALES.MODEL_ID.count(), SALES.PRICE.avg())\r\n" + |
| " .select (SALES.PRICE.avg().minus(MODEL.BASE_PRICE.avg()).round(2).as(\"DIFFERENCE\"))\r\n" + |
| " .join (MODEL.WMI, BRAND.WMI)\r\n" + |
| " .joinLeft(MODEL.ID, SALES.MODEL_ID, SALES.YEAR.is(2021)) // only year 2021\r\n" + |
| " .where (MODEL.ENGINE_TYPE.in(EngineType.P, EngineType.H, EngineType.E)) // Petrol, Hybrid, Electric\r\n" + |
| " .where (MODEL.BASE_PRICE.isGreaterThan(30000))\r\n" + |
| " .groupBy (BRAND.NAME, MODEL.CONFIG_NAME, MODEL.BASE_PRICE)\r\n" + |
| " .having (SALES.MODEL_ID.count().isGreaterThan(5)) // more than 5 sales\r\n" + |
| " .orderBy (BRAND.NAME.desc(), MODEL.CONFIG_NAME.asc());\r\n" + |
| " \r\n" + |
| "// Returns a list of Java beans (needs matching fields constructor or setter methods) \r\n" + |
| "// This is just one of several options to obtain an process query results \r\n" + |
| "List<QueryResult> list = context.getUtils().queryBeanList(cmd, QueryResult.class, null);\r\n" + |
| "log.info(\"queryBeanList returnes {} items\", list.size());"; |
| |
| public static final String codePriceUpdate = "// create command\r\n" + |
| "DBCommand cmd = context.createCommand()\r\n" + |
| " // increase model base prices by 5%\r\n" + |
| " .set (MODEL.BASE_PRICE.to(MODEL.BASE_PRICE.multiplyWith(105).divideBy(100).round(0)))\r\n" + |
| " .join (MODEL.WMI, BRAND.WMI)\r\n" + |
| " // on all Volkswagen Tiguan with Diesel engine\r\n" + |
| " .where(BRAND.NAME.is(\"Volkswagen\"))\r\n" + |
| " .where(MODEL.NAME.is(\"Tiguan\").and(MODEL.ENGINE_TYPE.is(EngineType.D)));\r\n" + |
| "\r\n" + |
| "// execute Update statement\r\n" + |
| "int count = context.executeUpdate(cmd);\r\n" + |
| "log.info(\"{} models affected\", count);"; |
| |
| public static final String codeCodegen = "// create the config\r\n" + |
| "CodeGenConfig config = new CodeGenConfig();\r\n" + |
| "// use API or load from file using \r\n" + |
| "config.setPackageName(\"com.mycompany.myproject.database\");\r\n" + |
| "config.set(...)\r\n" + |
| "// get the DBMS\r\n" + |
| "DBMSHandler dbms = new DBMSHandlerOracle();\r\n" + |
| "// get the JDBC-Connection\r\n" + |
| "Connection conn = getJDBCConnection(config);\r\n" + |
| "// Generate code from database\r\n" + |
| "CodeGenerator app = new CodeGenerator();\r\n" + |
| "app.generate(dbms, conn, config);\r\n"; |
| |
| public static String codeToHtml(DBDatabase db, String code, Object... literals) |
| { |
| code = prepareHtml(code); |
| // replace literals |
| for (int i=0; i<literals.length; i++) |
| { |
| String literal; |
| if (literals[i] instanceof String) |
| literal = "\""+((String)literals[i])+"\""; |
| else |
| literal = String.valueOf(literals[i]); |
| code = replaceWord(code, literal, ' ', "<span class=\"literal\">", "</span>"); |
| } |
| // null |
| code = replaceWord(code, "null", ' ', "<span class=\"literal\">", "</span>"); |
| code = replaceWord(code, "new", ' ', "<span class=\"new\">", "</span>"); |
| // types |
| String[] types = new String[] { "CarSalesDB", "CodeGenerator", "CodeGenConfig", "DBMSHandlerOracle", "DBMSHandler ", "Connection ", "DBCommand", "QueryResult", "EngineType", "int ", "long ", "String " }; |
| for (int i=0; i<types.length; i++) |
| code = replaceWord(code, types[i], ' ', "<span class=\"type\">", "</span>"); |
| // Tables and columns |
| if (db!=null) |
| { for (DBTable t : db.getTables()) |
| { |
| code = replaceWord(code, t.getName(), ' ', "<span class=\"obj\">", "</span>"); |
| for (DBColumn c : t.getColumns()) |
| { |
| code = replaceWord(code, "."+c.getName(), '.', "<span class=\"var\">", "</span>"); |
| } |
| } |
| } |
| // functions |
| code = replaceFragment(code, '.', '(', false, "<span class=\"func\">", "</span>", new char[] { 'a', 'z' }); |
| // literals |
| return replaceComment(code, "<span class=\"comment\">", "</span>"); |
| } |
| |
| public static String sqlToHtml(DBDatabase db, String sql, Number... literals) |
| { |
| sql = prepareHtml(sql); |
| /* |
| UPDATE t2 |
| SET BASE_PRICE=round(t2.BASE_PRICE*105/100,0) |
| FROM MODEL t2 INNER JOIN BRAND t1 ON t1.WMI = t2.WMI |
| WHERE t1.NAME='Volkswagen' AND t2.NAME='Tiguan' AND t2.ENGINE_TYPE='D' |
| */ |
| String[] words = new String[] { "SELECT ", "UPDATE ", "INSERT ", "SET" , "FROM ", "WHERE ", "GROUP BY ", "HAVING ", "ORDER BY", " IN ", " ON ", " AND ", " INNER JOIN ", " LEFT JOIN ", " RIGHT_JOIN " }; |
| for (int i=0; i<words.length; i++) |
| sql = replaceWord(sql, words[i], ' ', "<span class=\"word\">", "</span>"); |
| for (DBTable t : db.getTables()) |
| sql = replaceWord(sql, t.getAlias(), ' ', "<span class=\"alias\">", "</span>"); |
| // functions |
| String[] func = new String[] { "count", "round", "avg" }; |
| for (int i=0; i<func.length; i++) |
| sql = replaceWord(sql, func[i], ' ', "<span class=\"func\">", "</span>"); |
| // finally literals |
| for (int i=0; i<literals.length; i++) |
| sql = replaceWord(sql, literals[i].toString(), ' ', "<span class=\"literal\">", "</span>"); |
| // String literals |
| sql = sql.replace("N'", "'"); |
| return replaceFragment(sql, '\'', '\'', true, "<span class=\"param\"><span class=\"literal\">", "</span></span>", null); |
| } |
| |
| /* |
| * helpers |
| */ |
| |
| public static String prepareHtml(String str) |
| { |
| str = StringUtils.replace(str, "\t", " "); |
| str = StringUtils.replace(str, "&", "&"); |
| str = StringUtils.replace(str, "<", "<"); |
| return StringUtils.replace(str, ">", ">"); |
| } |
| |
| public static String replaceWord(String str, String word, char intro, String htmlBeg, String htmlEnd) |
| { |
| // not present |
| if (str.indexOf(word)<0) |
| return str; |
| // replace |
| String wtrim = word.trim(); |
| if (wtrim.charAt(0)==intro) |
| wtrim = wtrim.substring(1); |
| StringBuilder s = new StringBuilder(); |
| int i = 0; |
| int p = 0; |
| while ((i=str.indexOf(word, p))>=0) |
| { |
| if (word.charAt(0)==intro) |
| i++; |
| s.append(str.substring(p, i)); |
| s.append(htmlBeg); |
| s.append(wtrim); |
| s.append(htmlEnd); |
| // next |
| p = i + wtrim.length(); |
| } |
| s.append(str.substring(p)); |
| return s.toString(); |
| } |
| |
| public static String replaceFragment(String str, char beg, char end, boolean include, String htmlBeg, String htmlEnd, char[] nextRange) |
| { |
| StringBuilder s = new StringBuilder(); |
| int i = 0; |
| int p = 0; |
| while ((i=str.indexOf(beg, p))>=0) |
| { |
| // function special |
| if (nextRange!=null) |
| { // check range of next char |
| char next = str.charAt(i+1); |
| if (next<nextRange[0] || next>nextRange[1]) |
| { // ignore |
| s.append(str.substring(p, ++i)); |
| p = i; |
| continue; |
| } |
| } |
| int n = str.indexOf('\n', i+1); |
| int j = str.indexOf(end, ++i); |
| if (n<j) |
| { // line-break: ignore |
| s.append(str.substring(p, n+1)); |
| p = n+1; |
| continue; |
| } |
| if (!include) |
| { // remove whitespace |
| while(str.charAt(j-1)==' ') j--; |
| } |
| int o = (include) ? 1 : 0; |
| s.append(str.substring(p, i-o)); |
| s.append(htmlBeg); |
| s.append(str.substring(i-o, j+o)); |
| s.append(htmlEnd); |
| // next |
| p = j + o; |
| } |
| s.append(str.substring(p)); |
| return s.toString(); |
| } |
| |
| public static String replaceComment(String str, String htmlBeg, String htmlEnd) |
| { |
| StringBuilder s = new StringBuilder(); |
| String beg = "//"; |
| String end ="\r\n"; |
| int i = 0; |
| int p = 0; |
| while ((i=str.indexOf(beg, p))>=0) |
| { |
| int j = str.indexOf(end, i+1); |
| s.append(str.substring(p, i)); |
| s.append(htmlBeg); |
| s.append(str.substring(i, j)); |
| s.append(htmlEnd); |
| // next |
| p = j; |
| } |
| s.append(str.substring(p)); |
| return s.toString(); |
| } |
| |
| /* |
| public static String replaceSqlWord(String sql, String word, String htmlBeg, String htmlEnd) |
| { |
| // not present |
| if (sql.indexOf(word)<0) |
| return sql; |
| // replace |
| String wtrim = word.trim(); |
| StringBuilder s = new StringBuilder(); |
| int i = 0; |
| int p = 0; |
| while ((i=sql.indexOf(word, p))>=0) |
| { |
| if (word.charAt(0)==' ') |
| i++; |
| s.append(sql.substring(p, i)); |
| s.append(htmlBeg); |
| s.append(wtrim); |
| s.append(htmlEnd); |
| // next |
| p = i + wtrim.length(); |
| } |
| s.append(sql.substring(p)); |
| return s.toString(); |
| } |
| */ |
| |
| /* |
| public static String replaceSqlStringLiteral(String sql, String htmlBeg, String htmlEnd) |
| { |
| StringBuilder s = new StringBuilder(); |
| int i = 0; |
| int p = 0; |
| while ((i=sql.indexOf('\'', p))>=0) |
| { |
| int j = sql.indexOf('\'', ++i); |
| s.append(sql.substring(p, i-1)); |
| s.append(htmlBeg); |
| s.append(sql.substring(i-1, j+1)); |
| s.append(htmlEnd); |
| // next |
| p = j + 1; |
| } |
| s.append(sql.substring(p)); |
| return s.toString(); |
| } |
| */ |
| |
| } |