001: package net.sourceforge.squirrel_sql.plugins.oracle.tokenizer;
002:
003: /*
004: * Copyright (C) 2007 Rob Manning
005: * manningr@users.sourceforge.net
006: *
007: * Based on initial work from Johan Compagner.
008: *
009: * This library is free software; you can redistribute it and/or
010: * modify it under the terms of the GNU Lesser General Public
011: * License as published by the Free Software Foundation; either
012: * version 2.1 of the License, or (at your option) any later version.
013: *
014: * This library is distributed in the hope that it will be useful,
015: * but WITHOUT ANY WARRANTY; without even the implied warranty of
016: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
017: * Lesser General Public License for more details.
018: *
019: * You should have received a copy of the GNU Lesser General Public
020: * License along with this library; if not, write to the Free Software
021: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
022: */
023: import java.util.ArrayList;
024: import java.util.Iterator;
025: import java.util.regex.Pattern;
026:
027: import net.sourceforge.squirrel_sql.fw.preferences.IQueryTokenizerPreferenceBean;
028: import net.sourceforge.squirrel_sql.fw.sql.IQueryTokenizer;
029: import net.sourceforge.squirrel_sql.fw.sql.ITokenizerFactory;
030: import net.sourceforge.squirrel_sql.fw.sql.QueryTokenizer;
031: import net.sourceforge.squirrel_sql.fw.util.log.ILogger;
032: import net.sourceforge.squirrel_sql.fw.util.log.LoggerController;
033:
034: /**
035: * This class is loaded by the Oracle Plugin and registered with all Oracle
036: * Sessions as the query tokenizer if the plugin is loaded. It handles some
037: * of the syntax allowed in SQL-Plus scripts that would be hard to parse in a
038: * generic way for any database. It handles create statements for stored
039: * procedures, triggers, functions and anonymous procedure blocks. It can also
040: * handle "/" as the statement terminator in leiu of or in addition to the
041: * default statement separator which is ";". This class is not meant to fully
042: * replicate all of the syntax available in the highly expressive and venerable
043: * SQL-Plus reporting tool.
044: *
045: * @author manningr
046: */
047: public class OracleQueryTokenizer extends QueryTokenizer implements
048: IQueryTokenizer {
049: /** Logger for this class. */
050: private final static ILogger s_log = LoggerController
051: .createLogger(OracleQueryTokenizer.class);
052:
053: private static final String PROCEDURE_PATTERN = "^\\s*CREATE\\s+PROCEDURE.*|^\\s*CREATE\\s+OR\\s+REPLACE\\s+PROCEDURE\\s+.*";
054:
055: private static final String FUNCTION_PATTERN = "^\\s*CREATE\\s+FUNCTION.*|^\\s*CREATE\\s+OR\\s+REPLACE\\s+FUNCTION\\s+.*";
056:
057: private static final String TRIGGER_PATTERN = "^\\s*CREATE\\s+TRIGGER.*|^\\s*CREATE\\s+OR\\s+REPLACE\\s+TRIGGER\\s+.*";
058:
059: private static final String PACKAGE_PATTERN = "^\\s*CREATE\\s+PACKAGE.*|^\\s*CREATE\\s+OR\\s+REPLACE\\s+PACKAGE\\s+.*";
060:
061: private static final String DECLARE_PATTERN = "^\\s*DECLARE\\s*.*";
062:
063: private static final String BEGIN_PATTERN = "^\\s*BEGIN\\s*.*";
064:
065: /** Finds any "\n/" (slash) characters on their own line (no sep) */
066: private static final String SLASH_PATTERN = ".*\\n/\\n.*";
067:
068: /** Finds any "\n/" (slash) characters on their own line (no sep) */
069: private static final String SLASH_SPLIT_PATTERN = "\\n/\\n";
070:
071: private final String SET_COMMAND_PATTERN = "^\\s*SET\\s+\\w+\\s+\\w+\\s*$";
072:
073: private Pattern procPattern = Pattern.compile(PROCEDURE_PATTERN,
074: Pattern.DOTALL);
075:
076: private Pattern funcPattern = Pattern.compile(FUNCTION_PATTERN,
077: Pattern.DOTALL);
078:
079: private Pattern triggerPattern = Pattern.compile(TRIGGER_PATTERN,
080: Pattern.DOTALL);
081:
082: private Pattern packagePattern = Pattern.compile(PACKAGE_PATTERN,
083: Pattern.DOTALL);
084:
085: private Pattern declPattern = Pattern.compile(DECLARE_PATTERN,
086: Pattern.DOTALL);
087:
088: private Pattern beginPattern = Pattern.compile(BEGIN_PATTERN,
089: Pattern.DOTALL);
090:
091: private Pattern slashPattern = Pattern.compile(SLASH_PATTERN,
092: Pattern.DOTALL);
093:
094: private Pattern setPattern = Pattern.compile(SET_COMMAND_PATTERN,
095: Pattern.DOTALL);
096:
097: private static final String ORACLE_SCRIPT_INCLUDE_PREFIX = "@";
098:
099: private IQueryTokenizerPreferenceBean _prefs = null;
100:
101: public OracleQueryTokenizer(IQueryTokenizerPreferenceBean prefs) {
102: super (prefs.getStatementSeparator(), prefs.getLineComment(),
103: prefs.isRemoveMultiLineComments());
104: _prefs = prefs;
105: }
106:
107: public void setScriptToTokenize(String script) {
108: super .setScriptToTokenize(script);
109:
110: removeSqlPlusSetCommands();
111:
112: // Since it is likely to have "/" on it's own line, and it is key to
113: // letting us know that proceeding statements form a multi-statement
114: // procedure or function, it deserves it's own place in the _queries
115: // arraylist. If it is followed by other procedure or function creation
116: // blocks, we may fail to detect that, so this just goes through the
117: // list and breaks apart statements on newline so that this cannot
118: // happen.
119: breakApartNewLines();
120:
121: // Oracle allows statement separators in PL/SQL blocks. The process
122: // of tokenizing above renders these procedure blocks as separate
123: // statements, which is invalid for Oracle. Since "/" is the way
124: // in SQL-Plus to denote the end of a procedure or function, we
125: // re-assemble any create procedure/function/trigger statements that we
126: // find. This should be done before expanding file includes. Otherwise,
127: // any create sql found in files will already be joined, causing this to
128: // find create SQL without matching "/". The process of
129: // expanding 'file includes' already joins the sql fragments that it
130: // finds.
131: joinFragments(procPattern, false);
132: joinFragments(funcPattern, false);
133: joinFragments(triggerPattern, false);
134: joinFragments(packagePattern, false);
135: joinFragments(declPattern, false);
136: joinFragments(beginPattern, true);
137:
138: expandFileIncludes(ORACLE_SCRIPT_INCLUDE_PREFIX);
139:
140: removeRemainingSlashes();
141:
142: _queryIterator = _queries.iterator();
143: }
144:
145: /**
146: * Bug #1902611: Don't fail on "set" commands in SQL script
147: * SQL-Plus allows various "SET ... " commands that have nothing to do with SQL, but customize the behavior
148: * of SQL-Plus. For example, you can "SET TIMING ON" to print the time that every sql statement took to
149: * execute after executing it. We may want support a subset of these commands in the future, but for now,
150: * just strip them out so that they don't get sent to Oracle.
151: */
152: private void removeSqlPlusSetCommands() {
153: ArrayList<String> tmp = new ArrayList<String>();
154: for (Iterator<String> iter = _queries.iterator(); iter
155: .hasNext();) {
156: String next = iter.next();
157: String[] parts = next.split("\\n");
158: StringBuilder noCommandStr = new StringBuilder();
159: for (String part : parts) {
160: if (!setPattern.matcher(part.toUpperCase()).matches()) {
161: noCommandStr.append(part).append("\n");
162: }
163: }
164: tmp.add(noCommandStr.toString());
165: }
166: _queries = tmp;
167: }
168:
169: /**
170: * Sets the ITokenizerFactory which is used to create additional instances
171: * of the IQueryTokenizer - this is used for handling file includes
172: * recursively.
173: */
174: protected void setFactory() {
175: _tokenizerFactory = new ITokenizerFactory() {
176: public IQueryTokenizer getTokenizer() {
177: return new OracleQueryTokenizer(_prefs);
178: }
179: };
180: }
181:
182: /**
183: * This is to take care of scripts that have no statement separators.
184: * Like :
185: *
186: * select * from sometable1
187: * /
188: *
189: * select * from sometable2
190: * /
191: *
192: * SQL-Plus allows "/" to terminate statements as well as multiple statement
193: * blocks, so someone will probably have written a script that does this.
194: */
195: private void removeRemainingSlashes() {
196:
197: ArrayList<String> tmp = new ArrayList<String>();
198: boolean foundEOLSlash = false;
199: for (Iterator<String> iter = _queries.iterator(); iter
200: .hasNext();) {
201: String next = iter.next();
202: if (slashPattern.matcher(next).matches()) {
203: foundEOLSlash = true;
204: String[] parts = next.split(SLASH_SPLIT_PATTERN);
205: for (int i = 0; i < parts.length; i++) {
206: String part = parts[i];
207: if (slashPattern.matcher(part).matches()) {
208: int lastIndex = part.lastIndexOf("/");
209: tmp.add(part.substring(0, lastIndex));
210: } else {
211: if (part.endsWith("/")) {
212: part = part.substring(0, part
213: .lastIndexOf("/"));
214: }
215: tmp.add(part);
216: }
217: }
218: } else if (next.endsWith("/")) {
219: foundEOLSlash = true;
220: int lastIndex = next.lastIndexOf("/");
221: tmp.add(next.substring(0, lastIndex));
222: } else {
223: tmp.add(next);
224: }
225: }
226: if (foundEOLSlash == true) {
227: _queries = tmp;
228: }
229:
230: }
231:
232: /**
233: * This will loop through _queries and break apart lines that look like
234: *
235: * /\n\ncreate proc...
236: * into
237: *
238: * /
239: * create proc...
240: */
241: private void breakApartNewLines() {
242: ArrayList<String> tmp = new ArrayList<String>();
243: String sep = _prefs.getProcedureSeparator();
244: for (Iterator<String> iter = _queries.iterator(); iter
245: .hasNext();) {
246: String next = iter.next();
247: if (next.startsWith(sep)) {
248: tmp.add(sep);
249: String[] parts = next.split(sep + "\\n+");
250: for (int i = 0; i < parts.length; i++) {
251: if (!"".equals(parts[i]) && !sep.equals(parts[i])) {
252: tmp.add(parts[i]);
253: }
254: }
255: } else {
256: tmp.add(next);
257: }
258: }
259: _queries = tmp;
260: }
261:
262: /**
263: * This will scan the _queries list looking for fragments matching the
264: * specified pattern and will combine successive fragments until the "/" is
265: * indicating the end of the code block. This is Oracle-specific.
266: *
267: * @param skipStraySlash if we find a slash before matching a pattern and
268: * this is true, we will exclude it from our list of
269: * sql queries.
270: */
271: private void joinFragments(Pattern pattern, boolean skipStraySlash) {
272:
273: boolean inMultiSQLStatement = false;
274: StringBuffer collector = null;
275: ArrayList<String> tmp = new ArrayList<String>();
276: String sep = _prefs.getProcedureSeparator();
277: for (Iterator<String> iter = _queries.iterator(); iter
278: .hasNext();) {
279: String next = iter.next();
280: if (pattern.matcher(next.toUpperCase()).matches()) {
281: inMultiSQLStatement = true;
282: collector = new StringBuffer(next);
283: collector.append(";");
284: continue;
285: }
286: if (next.startsWith(sep)) {
287: inMultiSQLStatement = false;
288: if (collector != null) {
289: tmp.add(collector.toString());
290: collector = null;
291: } else {
292: if (skipStraySlash) {
293: // Stray sep - or we failed to find pattern
294: if (s_log.isDebugEnabled()) {
295: s_log
296: .debug("Detected stray proc separator("
297: + sep + "). Skipping");
298: }
299: } else {
300: tmp.add(next);
301: }
302: }
303: continue;
304: }
305: if (inMultiSQLStatement) {
306: collector.append(next);
307: collector.append(";");
308: continue;
309: }
310: tmp.add(next);
311: }
312: _queries = tmp;
313: }
314: }
|