Tutorial 3 – Extended 1:1 View Generator (Xtend)
In this tutorial we build a JAR file with a client generator implementing the Java interface. A client generator runs in the same JVM as SQL Developer and does not require a server side installation. Hence a client generator is immediately ready for use within all database connections.
In tutorial 2 we’ve written a generator in PL/SQL. We rewrite this generator now in Xtend. In this tutorial we use command line tools and simple text editors as common denominators. However, feel free to use the Java IDE of your choice.
1. Download Maven
Download Apache Maven 3.5.0 or later from here.
2. Install Maven
Install Maven as described here.
3. New Xtend generator
To generate an initial Xtend generator using a standard Maven directory layout, select Xtend generator from the New… context menu of an oddgen node.
In the generator dialog enter ExtendedView for class name and choose an output directory for the generated files and press the Generate to worksheet button.
This will produce an output similar to the following:
4. Replace generator
Open the ExtendedView.xtend file created in the previous step and replace with the following content (please ensure that the editor uses UTF-8 encoding without BOM).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 |
/* * Copyright 2017 Philipp Salvisberg <philipp.salvisberg@trivadis.com> * * Licensed 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.oddgen.plugin import java.sql.Connection import java.util.HashMap import java.util.LinkedHashMap import java.util.List import org.oddgen.sqldev.generators.OddgenGenerator2 import org.oddgen.sqldev.generators.model.Node import org.oddgen.sqldev.generators.model.NodeTools import org.springframework.jdbc.core.BeanPropertyRowMapper import org.springframework.jdbc.core.JdbcTemplate import org.springframework.jdbc.datasource.SingleConnectionDataSource class ExtendedView implements OddgenGenerator2 { private extension NodeTools nodeTools = new NodeTools static val SELECT_STAR = 'Select * ?' static val VIEW_SUFFIX = 'View suffix' static val ORDER_COLUMNS = 'Order columns?' private def getColumnNames(Connection conn, String tableName, LinkedHashMap<String, String> params) { if(params.get(SELECT_STAR) == "Yes") { return #["*"] } else { val sortColumnName = if(params.get(ORDER_COLUMNS) == "Yes") "column_name" else "column_id" val sql = ''' SELECT column_name FROM user_tab_columns WHERE table_name = ? ORDER BY «sortColumnName» ''' val jdbcTemplate = new JdbcTemplate(new SingleConnectionDataSource(conn, true)) val columnNames = jdbcTemplate.queryForList(sql, String, tableName.toUpperCase) return columnNames } } override isSupported(Connection conn) { var ret = false if (conn !== null) { if (conn.metaData.databaseProductName.startsWith("Oracle")) { if (conn.metaData.databaseMajorVersion == 9) { if (conn.metaData.databaseMinorVersion >= 2) { ret = true } } else if (conn.metaData.databaseMajorVersion > 9) { ret = true } } } return ret } override getName(Connection conn) { return "Extended 1:1 View Generator" } override getDescription(Connection conn) { return "Generates a 1:1 view based on an existing table and various generator parameters." } override getFolders(Connection conn) { return #["Client Generators"] } override getHelp(Connection conn) { return "<p>not yet available</p>" } override getNodes(Connection conn, String parentNodeId) { val params = new LinkedHashMap<String, String>() params.put(SELECT_STAR, "No") params.put(VIEW_SUFFIX, "_v") params.put(ORDER_COLUMNS, "No") if (parentNodeId === null || parentNodeId.empty) { val tableNode = new Node tableNode.id = "TABLE" tableNode.params = params tableNode.leaf = false tableNode.generatable = true tableNode.multiselectable = true return #[tableNode] } else { val sql = ''' SELECT object_type || '.' || object_name AS id, object_type AS parent_id, 1 AS leaf, 1 AS generatable, 1 AS multiselectable FROM user_objects WHERE object_type = ? AND generated = 'N' ''' val jdbcTemplate = new JdbcTemplate(new SingleConnectionDataSource(conn, true)) val nodes = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Node>(Node), #[parentNodeId]) for (node : nodes) { node.params = params } return nodes } } override HashMap<String, List<String>> getLov(Connection conn, LinkedHashMap<String, String> params, List<Node> nodes) { val lov = new HashMap<String, List<String>>() if(params.get(SELECT_STAR) == "Yes") { lov.put(ORDER_COLUMNS, #["No"]) } else { lov.put(ORDER_COLUMNS, #["Yes", "No"]) } if(params.get(ORDER_COLUMNS) == "Yes") { lov.put(SELECT_STAR, #["No"]) } else { lov.put(SELECT_STAR, #["Yes", "No"]) } return lov } override getParamStates(Connection conn, LinkedHashMap<String, String> params, List<Node> nodes) { return new HashMap<String, Boolean>() } override generateProlog(Connection conn, List<Node> nodes) { return "" } override generateSeparator(Connection conn) { return "\n--" } override generateEpilog(Connection conn, List<Node> nodes) { return "" } override generate(Connection conn, Node node) { try { val tableName = node.toObjectName.toLowerCase val viewName = '''«tableName»«node.params.get(VIEW_SUFFIX).toLowerCase»''' val columnNames = getColumnNames(conn, tableName, node.params) val result = ''' CREATE OR REPLACE VIEW «viewName» AS SELECT «FOR col : columnNames SEPARATOR ", "»«col.toLowerCase»«ENDFOR» FROM «tableName»; ''' return result } catch(Exception e) { return '''Cannot create view statement, got: «e.message».''' } } } |
The implementation looks very similar to the final PL/SQL variant of tutorial-2. In difference to the PL/SQL interface we have to implement the Java interface completely. There is no default behaviour for getName, getDescription, getFolders, etc.
Another difference is, that every method gets the active database connection as parameter conn. See lines 110 and 111 how this connection is used to access the database via Spring’s JdbcTemplate. There is also a isSupported method to ensure that an Oracle Database version >= 9.2 is used (see lines 54-68).
A further difference is that the that parameters are based on a LinkedHashMap and therefore sorted by entry (See line 87). Hence no additional method to sort parameters is required.
But the most significant difference is the use of template expressions. The following screenshot shows the lines 156 to 160 in the Xtend editor within the Eclipse IDE:
On line 156 the template is initiated with tripple single quotes and terminated on line 160 with tripple single quotes. Within the template boilerplate text such as “CREATE OR REPLACE VIEW” is shown in grey background colour. Please note that the whitespaces before “CREATE OR REPLACE VIEW” are ignored. This allows a reasonable formatting of code. Xtend expressions are put in guillemets. For example «col.toLowerCase» on line 158. In this case the col variable of the for loop is converted to lower case using the standard java.lang.String method toLowerCase(). Xtend expressions give you access to the full Java stack and are compiled, this means better performance and less runtime errors.
5. Build JAR
Open a *nix terminal window or a Windows command window and run the commands in the To build the plugin section. In my case these are:
|
1 2 |
cd "/Users/phs/oddgen/xtend" mvn clean package |
It will take a while to download all the dependencies into your local Maven repository. However, it will be faster for subsequent calls.
At the end of the build you should see a success message similar to the following:
|
1 2 3 4 5 6 7 8 9 |
[INFO] --- maven-jar-plugin:2.4:jar (default-jar) @ org.oddgen.plugin.extendedview --- [INFO] Building jar: /Users/phs/oddgen/xtend/target/org.oddgen.plugin.extendedview-1.0.0-SNAPSHOT.jar [INFO] ------------------------------------------------------------------------ [INFO] BUILD SUCCESS [INFO] ------------------------------------------------------------------------ [INFO] Total time: 7.087 s [INFO] Finished at: 2017-09-13T23:16:07+02:00 [INFO] Final Memory: 51M/475M [INFO] ------------------------------------------------------------------------ |
6. Install JAR
Open a *nix terminal window or a Windows command window and run the command in the To install the plugin section. In my case this is:
|
1 |
cp "/Users/phs/oddgen/xtend/target/org.oddgen.plugin.extendedview-1.0.0-SNAPSHOT.jar" "/Applications/SQLDeveloper17.2.0.app/Contents/Resources/sqldeveloper/sqldeveloper/extensions" |
This will copy the plugin into the SQL Developer extension directory.
You do not need to restart SQL Developer, just open or refresh a connection in the Generators window and the new client generator should show up as in the following screenshot:
7. Generate from user HR
Establish a connection to any user in the database such as the demo user HR and generate the views as shown in the next images.
