[Squirrel-sql-commits] sql12/plugins/netezza/src/net/sourceforge/squirrel_sql/plugins/netezza/tab
A Java SQL client for any JDBC compliant database
Brought to you by:
colbell,
gerdwagner
From: Rob M. <man...@us...> - 2009-12-28 23:43:09
|
Update of /cvsroot/squirrel-sql/sql12/plugins/netezza/src/net/sourceforge/squirrel_sql/plugins/netezza/tab In directory sfp-cvsdas-3.v30.ch3.sourceforge.com:/tmp/cvs-serv22539/plugins/netezza/src/net/sourceforge/squirrel_sql/plugins/netezza/tab Modified Files: ViewSourceTab.java ProcedureSourceTab.java Added Files: SynonymDetailsTab.java I18NStrings.properties SequenceDetailsTab.java SynonymSourceTab.java NetezzaProcedureFormator.java Log Message: Added source tab for synonyms and stored procedures. Can now properly tokenize scripts that contain stored proc statements with the plugin. --- NEW FILE: SequenceDetailsTab.java --- package net.sourceforge.squirrel_sql.plugins.netezza.tab; /* * Copyright (C) 2009 Rob Manning * man...@us... * * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public * License as published by the Free Software Foundation; either * version 2.1 of the License, or (at your option) any later version. * * This library is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public * License along with this library; if not, write to the Free Software * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ import java.sql.PreparedStatement; import java.sql.SQLException; import net.sourceforge.squirrel_sql.client.session.ISession; import net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.BasePreparedStatementTab; import net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo; import net.sourceforge.squirrel_sql.fw.util.StringManager; import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory; import net.sourceforge.squirrel_sql.fw.util.log.ILogger; import net.sourceforge.squirrel_sql.fw.util.log.LoggerController; /** * This class will display the details for an Netezza sequence. */ public class SequenceDetailsTab extends BasePreparedStatementTab { private static final StringManager s_stringMgr = StringManagerFactory.getStringManager(SequenceDetailsTab.class); /** Logger for this class. */ private final static ILogger s_log = LoggerController.createLogger(SequenceDetailsTab.class); /** * This interface defines locale specific strings. This should be * replaced with a property file. */ private interface i18n { // i18n[SequenceDetailsTab.title=Details] String TITLE = s_stringMgr.getString("SequenceDetailsTab.title"); // i18n[SequenceDetailsTab.hint=Display sequence details] String HINT = s_stringMgr.getString("SequenceDetailsTab.hint"); } /** SQL that retrieves the data. */ private static final String SQL = "SELECT SEQUENCE_CATALOG,SEQUENCE_SCHEMA, " + "CURRENT_VALUE,INCREMENT,IS_GENERATED,REMARKS " + "FROM INFORMATION_SCHEMA.SEQUENCES " + "WHERE SEQUENCE_SCHEMA = ? " + "AND SEQUENCE_NAME = ? "; public SequenceDetailsTab() { super(i18n.TITLE, i18n.HINT, true); } protected PreparedStatement createStatement() throws SQLException { ISession session = getSession(); IDatabaseObjectInfo doi = getDatabaseObjectInfo(); if (s_log.isDebugEnabled()) { s_log.debug("Sequence details SQL: "+SQL); s_log.debug("Sequence schema: "+doi.getSchemaName()); s_log.debug("Sequence name: "+doi.getSimpleName()); } PreparedStatement pstmt = session.getSQLConnection().prepareStatement(SQL); pstmt.setString(1, doi.getSchemaName()); pstmt.setString(2, doi.getSimpleName()); return pstmt; } } Index: ViewSourceTab.java =================================================================== RCS file: /cvsroot/squirrel-sql/sql12/plugins/netezza/src/net/sourceforge/squirrel_sql/plugins/netezza/tab/ViewSourceTab.java,v retrieving revision 1.1 retrieving revision 1.2 diff -C2 -d -r1.1 -r1.2 *** ViewSourceTab.java 25 Dec 2009 03:02:36 -0000 1.1 --- ViewSourceTab.java 28 Dec 2009 23:43:01 -0000 1.2 *************** *** 21,24 **** --- 21,26 ---- import net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.FormattedSourceTab; import net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo; + import net.sourceforge.squirrel_sql.fw.util.StringManager; + import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory; /** *************** *** 27,41 **** public class ViewSourceTab extends FormattedSourceTab { /** * Constructor * - * @param hint - * what the user sees on mouse-over tool-tip * @param stmtSep * the string to use to separate SQL statements */ ! public ViewSourceTab(String hint, String stmtSep) { ! super(hint); super.setupFormatter(stmtSep, null); super.setCompressWhitespace(true); --- 29,50 ---- public class ViewSourceTab extends FormattedSourceTab { + private static final StringManager s_stringMgr = + StringManagerFactory.getStringManager(ViewSourceTab.class); + + public static interface i18n + { + // i18n[ViewSourceTab.hint=Shows the source of the selected view] + String hint = s_stringMgr.getString("ViewSourceTab.hint"); + } + /** * Constructor * * @param stmtSep * the string to use to separate SQL statements */ ! public ViewSourceTab(String stmtSep) { ! super(i18n.hint); super.setupFormatter(stmtSep, null); super.setCompressWhitespace(true); --- NEW FILE: I18NStrings.properties --- ProcedureSourceTab.hint=Shows the source of the selected procedure SynonymDetailsTab.hint=Display synonym details SynonymDetailsTab.title=Details SynonymSourceTab.hint=Shows the source of the selected synonym ViewSourceTab.hint=Shows the source of the selected view --- NEW FILE: SynonymDetailsTab.java --- package net.sourceforge.squirrel_sql.plugins.netezza.tab; /* * Copyright (C) 2009 Rob Manning * man...@us... * * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public * License as published by the Free Software Foundation; either * version 2.1 of the License, or (at your option) any later version. * * This library is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public * License along with this library; if not, write to the Free Software * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ import java.sql.PreparedStatement; import java.sql.SQLException; import net.sourceforge.squirrel_sql.client.session.ISession; import net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.BasePreparedStatementTab; import net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo; import net.sourceforge.squirrel_sql.fw.util.StringManager; import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory; import net.sourceforge.squirrel_sql.fw.util.log.ILogger; import net.sourceforge.squirrel_sql.fw.util.log.LoggerController; /** * This class will display the details for an Netezza synonym. */ public class SynonymDetailsTab extends BasePreparedStatementTab { private static final StringManager s_stringMgr = StringManagerFactory.getStringManager(SynonymDetailsTab.class); /** Logger for this class. */ private final static ILogger s_log = LoggerController.createLogger(SynonymDetailsTab.class); /** * This interface defines locale specific strings. This should be replaced with a property file. */ private interface i18n { // i18n[SynonymDetailsTab.title=Details] String TITLE = s_stringMgr.getString("SynonymDetailsTab.title"); // i18n[SynonymDetailsTab.hint=Display synonym details] String HINT = s_stringMgr.getString("SynonymDetailsTab.hint"); } /** SQL that retrieves the data. */ private static final String SQL = "SELECT " + "SYNONYM_NAME, " + "refobjname as Referenced_Object, " + "refdatabase as Referenced_Database , " + "refdatabase || '.' || synonym_name as Qualified_Name " + "FROM _v_synonym " + "where synonym_name = ? " + "and refschema = ? "; public SynonymDetailsTab() { super(i18n.TITLE, i18n.HINT, true); } protected PreparedStatement createStatement() throws SQLException { ISession session = getSession(); IDatabaseObjectInfo doi = getDatabaseObjectInfo(); if (s_log.isDebugEnabled()) { s_log.debug("Synonym details SQL: " + SQL); s_log.debug("Synonym name: " + doi.getSimpleName()); s_log.debug("Synonym schema: " + doi.getSchemaName()); } PreparedStatement pstmt = session.getSQLConnection().prepareStatement(SQL); pstmt.setString(1, doi.getSimpleName()); pstmt.setString(2, doi.getSchemaName()); return pstmt; } } Index: ProcedureSourceTab.java =================================================================== RCS file: /cvsroot/squirrel-sql/sql12/plugins/netezza/src/net/sourceforge/squirrel_sql/plugins/netezza/tab/ProcedureSourceTab.java,v retrieving revision 1.1 retrieving revision 1.2 diff -C2 -d -r1.1 -r1.2 *** ProcedureSourceTab.java 25 Dec 2009 03:02:36 -0000 1.1 --- ProcedureSourceTab.java 28 Dec 2009 23:43:01 -0000 1.2 *************** *** 20,43 **** */ import net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.FormattedSourceTab; import net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo; /** * This class provides the necessary information to the parent tab to display the source for a Netezza ! * procedure. */ public class ProcedureSourceTab extends FormattedSourceTab { /** * Constructor * - * @param hint - * what the user sees on mouse-over tool-tip * @param stmtSep * the string to use to separate SQL statements */ ! public ProcedureSourceTab(String hint, String stmtSep) { ! super(hint); ! super.setupFormatter(stmtSep, null); super.setCompressWhitespace(true); // Netezza procedure definitions include the statement separator. --- 20,55 ---- */ import net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.FormattedSourceTab; + import net.sourceforge.squirrel_sql.fw.codereformat.ICodeReformator; import net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo; + import net.sourceforge.squirrel_sql.fw.util.StringManager; + import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory; /** * This class provides the necessary information to the parent tab to display the source for a Netezza ! * procedure. It uses a custom formatter class (NetezzaProcedureFormator) that can handle formatting Netezza ! * stored procedures (and not much else). */ public class ProcedureSourceTab extends FormattedSourceTab { + private static final StringManager s_stringMgr = + StringManagerFactory.getStringManager(ProcedureSourceTab.class); + + public static interface i18n + { + // i18n[ProcedureSourceTab.hint=Shows the source of the selected procedure] + String hint = s_stringMgr.getString("ProcedureSourceTab.hint"); + } + /** * Constructor * * @param stmtSep * the string to use to separate SQL statements */ ! public ProcedureSourceTab(String stmtSep) { ! super(i18n.hint); ! ICodeReformator formator = new NetezzaProcedureFormator(stmtSep); ! super.setupFormatter(formator, stmtSep, null); super.setCompressWhitespace(true); // Netezza procedure definitions include the statement separator. *************** *** 72,84 **** return new String[] { doi.getSchemaName(), doi.getSimpleName() }; } - - /** - * @see net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.FormattedSourceTab#processResult(java.lang.StringBuilder) - */ - @Override - protected String processResult(StringBuilder buf) - { - return buf.toString().replace("/n", "\\n"); - } } --- 84,87 ---- --- NEW FILE: SynonymSourceTab.java --- package net.sourceforge.squirrel_sql.plugins.netezza.tab; /* * Copyright (C) 2009 Rob Manning * man...@us... * * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public * License as published by the Free Software Foundation; either * version 2.1 of the License, or (at your option) any later version. * * This library is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public * License along with this library; if not, write to the Free Software * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ import net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.FormattedSourceTab; import net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo; import net.sourceforge.squirrel_sql.fw.util.StringManager; import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory; /** * This class provides the necessary information to the parent tab to display the source for a Netezza * synonym. */ public class SynonymSourceTab extends FormattedSourceTab { private static final StringManager s_stringMgr = StringManagerFactory.getStringManager(SynonymSourceTab.class); public static interface i18n { // i18n[SynonymSourceTab.hint=Shows the source of the selected synonym] String hint = s_stringMgr.getString("SynonymSourceTab.hint"); } /** * Constructor * * @param hint * what the user sees on mouse-over tool-tip * @param stmtSep * the string to use to separate SQL statements */ public SynonymSourceTab(String stmtSep) { super(i18n.hint); super.setupFormatter(stmtSep, null); super.setCompressWhitespace(true); } /** * @see net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.table.PSFormattedSourceTab#getSqlStatement() */ @Override protected String getSqlStatement() { return "SELECT " + "'create synonym ' || synonym_name || ' for ' || refobjname " + "FROM _v_synonym " + "where refdatabase = ? " + "and refschema = ? " + "and synonym_name like ? "; } /** * Overridden as the super implementation binds just schemaname rather than both catalogname and schemaName * as is used in Netezza. * * @return a String array of bind variable values */ @Override protected String[] getBindValues() { final IDatabaseObjectInfo doi = getDatabaseObjectInfo(); return new String[] { doi.getCatalogName(), doi.getSchemaName(), doi.getSimpleName() }; } } --- NEW FILE: NetezzaProcedureFormator.java --- /* * Copyright (C) 2009 Rob Manning * man...@us... * * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public * License as published by the Free Software Foundation; either * version 2.1 of the License, or (at your option) any later version. * * This library is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public * License along with this library; if not, write to the Free Software * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ package net.sourceforge.squirrel_sql.plugins.netezza.tab; import java.util.ArrayList; import java.util.List; import net.sourceforge.squirrel_sql.fw.codereformat.ICodeReformator; /** * The goal of this class is to format the stored procedure source code which comes from Netezza. For example, * this nicely formatted stored procedure: <code> * CREATE OR REPLACE PROCEDURE num() RETURNS BOOL LANGUAGE NZPLSQL AS * BEGIN_PROC * DECLARE * n NUMERIC; * BEGIN * n := 2147483647; * RAISE NOTICE 'n is %', n; * n := 2147483647 + 1; * RAISE NOTICE 'n is %', n; * n := 2147483647::numeric + 1; * RAISE NOTICE 'n is %', n; * n := 2147483647::bigint + 1; * RAISE NOTICE 'n is %', n; * n := 2147483647; * n := n + 1; * RAISE NOTICE 'n is %', n; * END; * END_PROC; * * comes out of the proceduresource column of the _v_procedure system tablelooking like this: * * CREATE OR REPLACE PROCEDURE num() RETURNS BOOL LANGUAGE NZPLSQL AS BEGIN_PROC /nDECLARE/n n NUMERIC; * BEGIN/n n := 2147483647;RAISE NOTICE 'n is %', n;n := 2147483647 + 1;RAISE NOTICE * 'n is %', n;n := 2147483647::numeric + 1;RAISE NOTICE 'n is %', n;n := 2147483647::bigint + 1;RAISE NOTICE * 'n is %', n;n := 2147483647;n := n + 1;RAISE NOTICE 'n is %', n;END;END_PROC; * * (Just one long line) */ public class NetezzaProcedureFormator implements ICodeReformator { private final String stmtSep; public NetezzaProcedureFormator(String stmtSep) { this.stmtSep = stmtSep; } /** * @see net.sourceforge.squirrel_sql.fw.codereformat.ICodeReformator#reformat(java.lang.String) */ @Override public String reformat(String in) { StringBuilder result = new StringBuilder(); String newLinesStripped = in.replace("/n", " "); List<String> parts = breakApartNewLines(newLinesStripped.split("\\s+"), stmtSep); boolean foundAs = false; boolean inDeclaration = false; boolean inMainSection = false; boolean inCreateSection = true; for (String part : parts) { if (inCreateSection && !part.equalsIgnoreCase("AS")) { result.append(part); result.append(" "); continue; } if (part.equalsIgnoreCase("AS") && !foundAs) { foundAs = true; inCreateSection = false; result.append("AS\n"); continue; } if (part.equalsIgnoreCase("BEGIN_PROC") && !inMainSection) { result.append("BEGIN_PROC\n"); continue; } if (part.equalsIgnoreCase("DECLARE")) { result.append("DECLARE\n"); inDeclaration = true; continue; } if (inDeclaration && !part.equalsIgnoreCase("BEGIN")) { if (!part.endsWith(stmtSep)) { result.append("\t"); } result.append(part); if (part.endsWith(stmtSep)) { result.append("\n"); } else { result.append(" "); } continue; } if (inDeclaration && part.equalsIgnoreCase("BEGIN")) { result.append("BEGIN\n\t"); inDeclaration = false; inMainSection = true; continue; } if (inMainSection) { if (part.equalsIgnoreCase("END"+stmtSep) || part.equalsIgnoreCase("END")) { inMainSection = false; // remove the previously added tab character result.setLength(result.length() - 1); result.append(part); if (!part.endsWith(stmtSep)) { result.append(stmtSep); } result.append("\n"); } else { // At this point, we have one long line of code that ends approximately with END;END_PROC with // statement separators interspersed. result.append(part); result.append(" "); if (part.endsWith(stmtSep)) { result.append("\n\t"); } } continue; } result.append(part); result.append(stmtSep); result.append("\n"); } return result.toString(); } private List<String> breakApartNewLines(String[] in, String stmtSep) { ArrayList<String> result = new ArrayList<String>(); for (String part : in) { if (part.contains(stmtSep)) { String[] subparts = part.split(stmtSep); int i = 1; for (String subpart : subparts) { if (i++ < subparts.length) { result.add(subpart + stmtSep); } else { result.add(subpart); } } } else { result.add(part); } } return result; } } |