001: package net.sourceforge.squirrel_sql.plugins.oracle;
002:
003: /*
004: * Copyright (C) 2002-2003 Colin Bell
005: * colbell@users.sourceforge.net
006: *
007: * This library is free software; you can redistribute it and/or
008: * modify it under the terms of the GNU Lesser General Public
009: * License as published by the Free Software Foundation; either
010: * version 2.1 of the License, or (at your option) any later version.
011: *
012: * This library is distributed in the hope that it will be useful,
013: * but WITHOUT ANY WARRANTY; without even the implied warranty of
014: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
015: * Lesser General Public License for more details.
016: *
017: * You should have received a copy of the GNU Lesser General Public
018: * License along with this library; if not, write to the Free Software
019: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
020: */
021:
022: import java.io.File;
023: import java.io.IOException;
024: import java.sql.Driver;
025: import java.sql.PreparedStatement;
026: import java.sql.ResultSet;
027: import java.sql.SQLException;
028: import java.sql.Statement;
029: import java.util.ArrayList;
030: import java.util.Collection;
031: import java.util.Hashtable;
032: import java.util.Iterator;
033:
034: import javax.swing.SwingUtilities;
035:
036: import net.sourceforge.squirrel_sql.client.IApplication;
037: import net.sourceforge.squirrel_sql.client.action.ActionCollection;
038: import net.sourceforge.squirrel_sql.client.gui.db.ISQLAliasExt;
039: import net.sourceforge.squirrel_sql.client.gui.db.SQLAlias;
040: import net.sourceforge.squirrel_sql.client.gui.db.aliasproperties.IAliasPropertiesPanelController;
041: import net.sourceforge.squirrel_sql.client.gui.session.ObjectTreeInternalFrame;
042: import net.sourceforge.squirrel_sql.client.gui.session.SQLInternalFrame;
043: import net.sourceforge.squirrel_sql.client.plugin.DefaultSessionPlugin;
044: import net.sourceforge.squirrel_sql.client.plugin.PluginException;
045: import net.sourceforge.squirrel_sql.client.plugin.PluginQueryTokenizerPreferencesManager;
046: import net.sourceforge.squirrel_sql.client.plugin.PluginResources;
047: import net.sourceforge.squirrel_sql.client.plugin.PluginSessionCallback;
048: import net.sourceforge.squirrel_sql.client.plugin.gui.PluginGlobalPreferencesTab;
049: import net.sourceforge.squirrel_sql.client.plugin.gui.PluginQueryTokenizerPreferencesPanel;
050: import net.sourceforge.squirrel_sql.client.preferences.IGlobalPreferencesPanel;
051: import net.sourceforge.squirrel_sql.client.session.IAllowedSchemaChecker;
052: import net.sourceforge.squirrel_sql.client.session.IObjectTreeAPI;
053: import net.sourceforge.squirrel_sql.client.session.ISQLPanelAPI;
054: import net.sourceforge.squirrel_sql.client.session.ISession;
055: import net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.INodeExpander;
056: import net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.DatabaseObjectInfoTab;
057: import net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.IObjectTab;
058: import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.CellComponentFactory;
059: import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DTProperties;
060: import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeTimestamp;
061: import net.sourceforge.squirrel_sql.fw.dialects.DialectFactory;
062: import net.sourceforge.squirrel_sql.fw.gui.GUIUtils;
063: import net.sourceforge.squirrel_sql.fw.id.IIdentifier;
064: import net.sourceforge.squirrel_sql.fw.preferences.IQueryTokenizerPreferenceBean;
065: import net.sourceforge.squirrel_sql.fw.sql.DatabaseObjectType;
066: import net.sourceforge.squirrel_sql.fw.sql.ISQLConnection;
067: import net.sourceforge.squirrel_sql.fw.sql.SQLDatabaseMetaData;
068: import net.sourceforge.squirrel_sql.fw.util.StringManager;
069: import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory;
070: import net.sourceforge.squirrel_sql.fw.util.Utilities;
071: import net.sourceforge.squirrel_sql.fw.util.log.ILogger;
072: import net.sourceforge.squirrel_sql.fw.util.log.LoggerController;
073: import net.sourceforge.squirrel_sql.fw.xml.XMLBeanReader;
074: import net.sourceforge.squirrel_sql.fw.xml.XMLBeanWriter;
075: import net.sourceforge.squirrel_sql.plugins.oracle.SGAtrace.NewSGATraceWorksheetAction;
076: import net.sourceforge.squirrel_sql.plugins.oracle.dboutput.NewDBOutputWorksheetAction;
077: import net.sourceforge.squirrel_sql.plugins.oracle.exception.OracleExceptionFormatter;
078: import net.sourceforge.squirrel_sql.plugins.oracle.expander.DefaultDatabaseExpander;
079: import net.sourceforge.squirrel_sql.plugins.oracle.expander.InstanceParentExpander;
080: import net.sourceforge.squirrel_sql.plugins.oracle.expander.OracleTableParentExpander;
081: import net.sourceforge.squirrel_sql.plugins.oracle.expander.PackageExpander;
082: import net.sourceforge.squirrel_sql.plugins.oracle.expander.ProcedureExpander;
083: import net.sourceforge.squirrel_sql.plugins.oracle.expander.SchemaExpander;
084: import net.sourceforge.squirrel_sql.plugins.oracle.expander.SessionParentExpander;
085: import net.sourceforge.squirrel_sql.plugins.oracle.expander.TableExpander;
086: import net.sourceforge.squirrel_sql.plugins.oracle.expander.TriggerParentExpander;
087: import net.sourceforge.squirrel_sql.plugins.oracle.expander.UserParentExpander;
088: import net.sourceforge.squirrel_sql.plugins.oracle.explainplan.ExplainPlanExecuter;
089: import net.sourceforge.squirrel_sql.plugins.oracle.invalidobjects.NewInvalidObjectsWorksheetAction;
090: import net.sourceforge.squirrel_sql.plugins.oracle.prefs.OraclePluginPreferencesPanel;
091: import net.sourceforge.squirrel_sql.plugins.oracle.prefs.OraclePreferenceBean;
092: import net.sourceforge.squirrel_sql.plugins.oracle.sessioninfo.NewSessionInfoWorksheetAction;
093: import net.sourceforge.squirrel_sql.plugins.oracle.tab.IndexColumnInfoTab;
094: import net.sourceforge.squirrel_sql.plugins.oracle.tab.IndexDetailsTab;
095: import net.sourceforge.squirrel_sql.plugins.oracle.tab.InstanceDetailsTab;
096: import net.sourceforge.squirrel_sql.plugins.oracle.tab.ObjectSourceTab;
097: import net.sourceforge.squirrel_sql.plugins.oracle.tab.OptionsTab;
098: import net.sourceforge.squirrel_sql.plugins.oracle.tab.SequenceDetailsTab;
099: import net.sourceforge.squirrel_sql.plugins.oracle.tab.SessionDetailsTab;
100: import net.sourceforge.squirrel_sql.plugins.oracle.tab.SessionStatisticsTab;
101: import net.sourceforge.squirrel_sql.plugins.oracle.tab.SnapshotSourceTab;
102: import net.sourceforge.squirrel_sql.plugins.oracle.tab.TriggerColumnInfoTab;
103: import net.sourceforge.squirrel_sql.plugins.oracle.tab.TriggerDetailsTab;
104: import net.sourceforge.squirrel_sql.plugins.oracle.tab.TriggerSourceTab;
105: import net.sourceforge.squirrel_sql.plugins.oracle.tab.UserDetailsTab;
106: import net.sourceforge.squirrel_sql.plugins.oracle.tab.ViewSourceTab;
107: import net.sourceforge.squirrel_sql.plugins.oracle.tokenizer.OracleQueryTokenizer;
108: import net.sourceforge.squirrel_sql.plugins.oracle.types.OracleXmlTypeDataTypeComponentFactory;
109:
110: /**
111: * Oracle plugin class.
112: *
113: * @author <A HREF="mailto:colbell@users.sourceforge.net">Colin Bell</A>
114: */
115: public class OraclePlugin extends DefaultSessionPlugin {
116: /**
117: * Logger for this class.
118: */
119: private final static ILogger s_log = LoggerController
120: .createLogger(OraclePlugin.class);
121:
122: /**
123: * Internationalized strings for this class.
124: */
125: private static final StringManager s_stringMgr = StringManagerFactory
126: .getStringManager(OraclePlugin.class);
127:
128: private PluginResources _resources;
129:
130: /**
131: * A list of Oracle sessions that are open so we'll know when none are left
132: */
133: private ArrayList<ISession> oracleSessions = new ArrayList<ISession>();
134: /**
135: * SQL to find schemas to which the logged in user has access
136: */
137: private static String SCHEMA_ACCESS_SQL = "SELECT DISTINCT OWNER FROM ALL_OBJECTS";
138: /**
139: * SQL to determine whether or not this account is a DBA account
140: */
141: private static String DBA_ROLE_SQL = "SELECT GRANTED_ROLE FROM USER_ROLE_PRIVS";
142:
143: private static final String ORACLE_ALIAS_PREFS_FILE = "oracleAliasPrefs.xml";
144:
145: private Hashtable<IIdentifier, OracleAliasPrefs> _oracleAliasPrefsByAliasIdentifier = new Hashtable<IIdentifier, OracleAliasPrefs>();
146:
147: /** manages our query tokenizing preferences */
148: private PluginQueryTokenizerPreferencesManager _prefsManager = null;
149:
150: interface i18n {
151: // i18n[OraclePlugin.title=Oracle]
152: String title = s_stringMgr.getString("OraclePlugin.title");
153:
154: // i18n[OraclePlugin.hint=Preferences for Oracle]
155: String hint = s_stringMgr.getString("OraclePlugin.hint");
156:
157: //i18n[OraclePlugin.timestampWarning=The setting to use string literals
158: //for timestamps may result in the inability to edit tables containing
159: //these columns. If this problem occurs, open
160: //Global Preferences -> Data Type Controls and set Timestamps to use
161: // "JDBC standard escape format"]
162: String timestampWarning = s_stringMgr
163: .getString("OraclePlugin.timestampWarning");
164: }
165:
166: /**
167: * Return the internal name of this plugin.
168: *
169: * @return the internal name of this plugin.
170: */
171: public String getInternalName() {
172: return "oracle";
173: }
174:
175: /**
176: * Return the descriptive name of this plugin.
177: *
178: * @return the descriptive name of this plugin.
179: */
180: public String getDescriptiveName() {
181: return "Oracle Plugin";
182: }
183:
184: /**
185: * Returns the current version of this plugin.
186: *
187: * @return the current version of this plugin.
188: */
189: public String getVersion() {
190: return "0.20";
191: }
192:
193: /**
194: * Returns the authors name.
195: *
196: * @return the authors name.
197: */
198: public String getAuthor() {
199: return "Colin Bell";
200: }
201:
202: /**
203: * Returns a comma separated list of other contributors.
204: *
205: * @return Contributors names.
206: */
207: public String getContributors() {
208: return "Alexander Buloichik, Rob Manning";
209: }
210:
211: /**
212: * @see net.sourceforge.squirrel_sql.client.plugin.IPlugin#getChangeLogFileName()
213: */
214: public String getChangeLogFileName() {
215: return "changes.txt";
216: }
217:
218: /**
219: * @see net.sourceforge.squirrel_sql.client.plugin.IPlugin#getHelpFileName()
220: */
221: public String getHelpFileName() {
222: return "readme.html";
223: }
224:
225: /**
226: * @see net.sourceforge.squirrel_sql.client.plugin.IPlugin#getLicenceFileName()
227: */
228: public String getLicenceFileName() {
229: return "licence.txt";
230: }
231:
232: /**
233: * Create panel for the Global Properties dialog.
234: *
235: * @return properties panel.
236: */
237: public IGlobalPreferencesPanel[] getGlobalPreferencePanels() {
238: PluginQueryTokenizerPreferencesPanel _prefsPanel = new OraclePluginPreferencesPanel(
239: _prefsManager);
240:
241: PluginGlobalPreferencesTab tab = new PluginGlobalPreferencesTab(
242: _prefsPanel);
243:
244: tab.setHint(i18n.hint);
245: tab.setTitle(i18n.title);
246:
247: return new IGlobalPreferencesPanel[] { tab };
248: }
249:
250: public void initialize() throws PluginException {
251: try {
252: super .initialize();
253:
254: final IApplication app = getApplication();
255:
256: _resources = new OracleResources(
257: "net.sourceforge.squirrel_sql.plugins.oracle.oracle",
258: this );
259:
260: ActionCollection coll = app.getActionCollection();
261: coll.add(new NewDBOutputWorksheetAction(app, _resources));
262: coll.add(new NewInvalidObjectsWorksheetAction(app,
263: _resources));
264: coll
265: .add(new NewSessionInfoWorksheetAction(app,
266: _resources));
267: coll.add(new NewSGATraceWorksheetAction(app, _resources));
268:
269: app.getSessionManager().addAllowedSchemaChecker(
270: new IAllowedSchemaChecker() {
271: public String[] getAllowedSchemas(
272: ISQLConnection con, ISQLAliasExt alias) {
273: return onGetAllowedSchemas(con, alias);
274: }
275: });
276:
277: File f = getGlobalPrefsFile();
278:
279: if (f.exists()) {
280: XMLBeanReader xbr = new XMLBeanReader();
281: xbr.load(f, getClass().getClassLoader());
282:
283: for (Iterator<Object> i = xbr.iterator(); i.hasNext();) {
284: OracleAliasPrefs buf = (OracleAliasPrefs) i.next();
285: _oracleAliasPrefsByAliasIdentifier.put(buf
286: .getAliasIdentifier(), buf);
287: }
288:
289: } else {
290: _oracleAliasPrefsByAliasIdentifier = new Hashtable<IIdentifier, OracleAliasPrefs>();
291: }
292: _prefsManager = new PluginQueryTokenizerPreferencesManager();
293: _prefsManager.initialize(this , new OraclePreferenceBean());
294:
295: /* Register custom DataTypeComponent factory for Oracles XMLType */
296: CellComponentFactory.registerDataTypeFactory(
297: new OracleXmlTypeDataTypeComponentFactory(), 2007,
298: "SYS.XMLTYPE");
299: } catch (Exception e) {
300: throw new RuntimeException(e);
301: }
302: }
303:
304: public void load(IApplication app) throws PluginException {
305: super .load(app);
306: }
307:
308: private File getGlobalPrefsFile() throws IOException {
309: return new File(getPluginUserSettingsFolder().getPath()
310: + File.separator + ORACLE_ALIAS_PREFS_FILE);
311: }
312:
313: public void unload() {
314: try {
315: File f = getGlobalPrefsFile();
316:
317: XMLBeanWriter xbw = new XMLBeanWriter();
318:
319: Collection<OracleAliasPrefs> set = _oracleAliasPrefsByAliasIdentifier
320: .values();
321: if (set.size() > 0) {
322: xbw.addIteratorToRoot(set.iterator());
323: }
324:
325: xbw.save(f);
326: } catch (Exception e) {
327: throw new RuntimeException(e);
328: }
329:
330: }
331:
332: /**
333: * Create Alias prefs panel.
334: */
335: public IAliasPropertiesPanelController[] getAliasPropertiesPanelControllers(
336: SQLAlias alias) {
337: if (false == isOracle(alias)) {
338: return new IAliasPropertiesPanelController[0];
339: }
340:
341: OracleAliasPrefs aliasPrefs = _oracleAliasPrefsByAliasIdentifier
342: .get(alias.getIdentifier());
343:
344: if (null == aliasPrefs) {
345: aliasPrefs = new OracleAliasPrefs();
346: aliasPrefs.setAliasIdentifier(alias.getIdentifier());
347: _oracleAliasPrefsByAliasIdentifier.put(alias
348: .getIdentifier(), aliasPrefs);
349: }
350:
351: return new OracleAliasPrefsPanelController[] { new OracleAliasPrefsPanelController(
352: aliasPrefs) };
353: }
354:
355: public void aliasCopied(SQLAlias source, SQLAlias target) {
356: if (false == isOracle(source) || false == isOracle(target)) {
357: return;
358: }
359:
360: OracleAliasPrefs sourcePrefs = _oracleAliasPrefsByAliasIdentifier
361: .get(source.getIdentifier());
362:
363: if (null != sourcePrefs) {
364: OracleAliasPrefs targetPrefs = (OracleAliasPrefs) Utilities
365: .cloneObject(sourcePrefs, getClass()
366: .getClassLoader());
367: targetPrefs.setAliasIdentifier(target.getIdentifier());
368: _oracleAliasPrefsByAliasIdentifier.put(targetPrefs
369: .getAliasIdentifier(), targetPrefs);
370: }
371: }
372:
373: public void aliasRemoved(SQLAlias alias) {
374: _oracleAliasPrefsByAliasIdentifier
375: .remove(alias.getIdentifier());
376: }
377:
378: /**
379: * Called when a session shutdown.
380: *
381: * @param session The session that is ending.
382: */
383: public void sessionEnding(ISession session) {
384: super .sessionEnding(session);
385: oracleSessions.remove(session);
386: }
387:
388: public PluginSessionCallback sessionStarted(final ISession session) {
389: if (!isOracle(session)) {
390: return null;
391: }
392: IQueryTokenizerPreferenceBean prefs = _prefsManager
393: .getPreferences();
394: if (prefs.isInstallCustomQueryTokenizer()) {
395: session.setQueryTokenizer(new OracleQueryTokenizer(prefs));
396: }
397:
398: if (((OraclePreferenceBean) prefs).isShowErrorOffset()) {
399: OracleExceptionFormatter formatter = new OracleExceptionFormatter();
400: formatter.setSession(session);
401: session.setExceptionFormatter(formatter);
402: }
403:
404: GUIUtils.processOnSwingEventThread(new Runnable() {
405: public void run() {
406: addActions(session);
407: }
408: });
409:
410: oracleSessions.add(session);
411:
412: checkTimestampSetting(session);
413:
414: PluginSessionCallback ret = new PluginSessionCallback() {
415: public void sqlInternalFrameOpened(
416: SQLInternalFrame sqlInternalFrame, ISession sess) {
417: onSQLInternaFrameOpened(sqlInternalFrame, sess);
418: }
419:
420: public void objectTreeInternalFrameOpened(
421: ObjectTreeInternalFrame objectTreeInternalFrame,
422: ISession sess) {
423: onObjectTreeInternalFrameOpened(objectTreeInternalFrame);
424: }
425:
426: };
427:
428: SwingUtilities.invokeLater(new Runnable() {
429: public void run() {
430: ISQLPanelAPI sqlPaneAPI = session.getSessionSheet()
431: .getSQLPaneAPI();
432: sqlPaneAPI.addExecutor(new ExplainPlanExecuter(session,
433: sqlPaneAPI));
434: updateObjectTree(session.getSessionSheet()
435: .getObjectTreePanel());
436: }
437: });
438:
439: return ret;
440: }
441:
442: /**
443: * This will check the setting for using timestamps in where clauses and
444: * display a warning message to the user if string literal - which is known
445: * not to work correctly in Oracle - is set to be used.
446: */
447: private void checkTimestampSetting(ISession session) {
448:
449: String tsClassName = DataTypeTimestamp.class.getName();
450: String timeStampWhereClauseUsage = DTProperties.get(
451: tsClassName, DataTypeTimestamp.WHERE_CLAUSE_USAGE_KEY);
452: if (timeStampWhereClauseUsage != null) {
453: int timeStampWhereClauseUsageInt = Integer
454: .parseInt(timeStampWhereClauseUsage);
455: if (DataTypeTimestamp.USE_STRING_FORMAT == timeStampWhereClauseUsageInt) {
456: session.showWarningMessage(i18n.timestampWarning);
457: s_log.warn(i18n.timestampWarning);
458: }
459: }
460:
461: }
462:
463: @Override
464: protected boolean isPluginSession(ISession session) {
465: return DialectFactory.isOracle(session.getMetaData());
466: }
467:
468: private void addActions(ISession session) {
469: ActionCollection coll = getApplication().getActionCollection();
470: session.addSeparatorToToolbar();
471: session
472: .addToToolbar(coll
473: .get(NewDBOutputWorksheetAction.class));
474: session.addToToolbar(coll
475: .get(NewInvalidObjectsWorksheetAction.class));
476: session.addToToolbar(coll
477: .get(NewSessionInfoWorksheetAction.class));
478: session
479: .addToToolbar(coll
480: .get(NewSGATraceWorksheetAction.class));
481:
482: session.getSessionInternalFrame().addToToolsPopUp(
483: "oracleoutput",
484: coll.get(NewDBOutputWorksheetAction.class));
485: session.getSessionInternalFrame().addToToolsPopUp(
486: "oracleinvalid",
487: coll.get(NewInvalidObjectsWorksheetAction.class));
488: session.getSessionInternalFrame().addToToolsPopUp("oracleinfo",
489: coll.get(NewSessionInfoWorksheetAction.class));
490: session.getSessionInternalFrame().addToToolsPopUp("oraclesga",
491: coll.get(NewSGATraceWorksheetAction.class));
492:
493: }
494:
495: private void onSQLInternaFrameOpened(
496: SQLInternalFrame sqlInternalFrame, final ISession session) {
497: final ISQLPanelAPI panel = sqlInternalFrame.getSQLPanelAPI();
498: panel.addExecutor(new ExplainPlanExecuter(session, panel));
499: }
500:
501: private void onObjectTreeInternalFrameOpened(
502: ObjectTreeInternalFrame objectTreeInternalFrame) {
503: final IObjectTreeAPI objTree = objectTreeInternalFrame
504: .getObjectTreeAPI();
505: updateObjectTree(objTree);
506: }
507:
508: /**
509: * Return a node expander for the object tree for a particular default node type.
510: * <p/> A plugin could return non null here if they wish to override the default node
511: * expander bahaviour. Most plugins should return null here.
512: */
513: public INodeExpander getDefaultNodeExpander(ISession session,
514: DatabaseObjectType type) {
515: boolean isOracle = isOracle(session.getAlias());
516: boolean isOracleWithFlashBack = isOracleWithFlashBack(session);
517: if ((type == DatabaseObjectType.PROC_TYPE_DBO) && isOracle) {
518: return new ProcedureExpander();
519: }
520: if (type == DatabaseObjectType.DATABASE_TYPE_DBO && isOracle) {
521: return new DefaultDatabaseExpander(session);
522: }
523: if (type == DatabaseObjectType.TABLE_TYPE_DBO
524: && isOracleWithFlashBack) {
525: OraclePreferenceBean prefs = (OraclePreferenceBean) _prefsManager
526: .getPreferences();
527: return new OracleTableParentExpander(prefs);
528: }
529: return null;
530: }
531:
532: private boolean isOracleWithFlashBack(ISession session) {
533: boolean result = false;
534: if (DialectFactory.isOracle(session.getMetaData())) {
535: // Not all Oracle's, just 10g and above.
536: try {
537: int version = session.getMetaData()
538: .getDatabaseMajorVersion();
539: if (version >= 10) {
540: result = true;
541: }
542: } catch (SQLException e) {
543: s_log.error(
544: "Unexpected exception while attempting to get "
545: + "the database version", e);
546: }
547: }
548: return result;
549: }
550:
551: private boolean isOracle(ISession session) {
552: boolean result = false;
553: if (DialectFactory.isOracle(session.getMetaData())) {
554: result = true;
555: }
556: return result;
557: }
558:
559: private boolean isOracle(ISQLAliasExt alias) {
560: IIdentifier driverIdentifier = alias.getDriverIdentifier();
561: Driver jdbcDriver = getApplication().getSQLDriverManager()
562: .getJDBCDriver(driverIdentifier);
563:
564: if (null == jdbcDriver) {
565: return false;
566: }
567:
568: return jdbcDriver.getClass().getName().startsWith("oracle.");
569: }
570:
571: private String[] onGetAllowedSchemas(ISQLConnection con,
572: ISQLAliasExt alias) {
573: if (isOracle(alias)) {
574: OracleAliasPrefs prefs = _oracleAliasPrefsByAliasIdentifier
575: .get(alias.getIdentifier());
576:
577: if (null == prefs) {
578: prefs = new OracleAliasPrefs();
579: prefs.setAliasIdentifier(alias.getIdentifier());
580: _oracleAliasPrefsByAliasIdentifier.put(prefs
581: .getAliasIdentifier(), prefs);
582: }
583:
584: return getAccessibleSchemas(prefs, con);
585: } else {
586: return null;
587: }
588: }
589:
590: /**
591: * Returns an array of schema names that represent schemas in which there
592: * exist tables that the user associated with the specified session has
593: * privilege to access.
594: *
595: * @param session the session to retrieve schemas for
596: * @param con
597: * @return an array of strings representing the names of accessible schemas
598: */
599: private String[] getAccessibleSchemas(OracleAliasPrefs aliasPrefs,
600: ISQLConnection con) {
601: String[] result = null;
602: ResultSet rs = null;
603: Statement stmt = null;
604: SQLDatabaseMetaData md = con.getSQLMetaData();
605: String currentUserName = null;
606: try {
607: if (hasSystemPrivilege(con)
608: || aliasPrefs.isLoadAllSchemas()) {
609: result = md.getSchemas();
610: } else {
611: currentUserName = md.getUserName();
612: stmt = con.getConnection().createStatement();
613: rs = stmt.executeQuery(SCHEMA_ACCESS_SQL);
614: ArrayList<String> tmp = new ArrayList<String>();
615: while (rs.next()) {
616: tmp.add(rs.getString(1));
617: }
618: if (currentUserName != null
619: && !tmp.contains(currentUserName)) {
620: tmp.add(currentUserName);
621: }
622:
623: tmp.remove("SYS");
624:
625: if (aliasPrefs.isLoadAccessibleSchemasAndSYS()) {
626: tmp.add("SYS");
627: }
628:
629: result = tmp.toArray(new String[tmp.size()]);
630: }
631: } catch (SQLException e) {
632: // i18n[DefaultDatabaseExpander.error.retrieveschemaprivs=Unable to retrieve schema privileges]
633: String msg = s_stringMgr
634: .getString("DefaultDatabaseExpander.error.retrieveschemaprivs");
635: s_log.error(msg, e);
636: } finally {
637: if (rs != null)
638: try {
639: rs.close();
640: } catch (SQLException e) {
641: }
642: if (stmt != null)
643: try {
644: stmt.close();
645: } catch (SQLException e) {
646: }
647: }
648: return result;
649: }
650:
651: /**
652: * Checks whether or not the user associated with the specified session has
653: * been granted the DBA privilege.
654: *
655: * @param session the session to check
656: * @return true if the user has the DBA privilege; false otherwise.
657: */
658: private boolean hasSystemPrivilege(ISQLConnection con) {
659: boolean result = false;
660: Statement stmt = null;
661: ResultSet rs = null;
662: try {
663: stmt = con.createStatement();
664: rs = stmt.executeQuery(DBA_ROLE_SQL);
665: while (rs.next()) {
666: String role = rs.getString(1);
667: if ("DBA".equalsIgnoreCase(role)) {
668: result = true;
669: break;
670: }
671: }
672: } catch (SQLException e) {
673: // i18n[DefaultDatabaseExpander.error.retrieveuserroles=Unable to retrieve user roles]
674: String msg = s_stringMgr
675: .getString("DefaultDatabaseExpander.error.retrieveuserroles");
676: s_log.error(msg, e);
677: } finally {
678: if (rs != null)
679: try {
680: rs.close();
681: } catch (SQLException e) {
682: }
683: if (stmt != null)
684: try {
685: stmt.close();
686: } catch (SQLException e) {
687: }
688: }
689: return result;
690: }
691:
692: /**
693: * Check if we can run query.
694: *
695: * @param session session
696: * @param query query text
697: * @return true if query works fine
698: */
699: public static boolean checkObjectAccessible(final ISession session,
700: final String query) {
701: PreparedStatement pstmt = null;
702: ResultSet rs = null;
703: try {
704: pstmt = session.getSQLConnection().prepareStatement(query);
705: rs = pstmt.executeQuery();
706: return true;
707: } catch (SQLException ex) {
708: return false;
709: } finally {
710: try {
711: if (rs != null) {
712: rs.close();
713: }
714: if (pstmt != null) {
715: pstmt.close();
716: }
717: } catch (SQLException ex) {
718: }
719: }
720: }
721:
722: private void updateObjectTree(final IObjectTreeAPI objTree) {
723: ISession session = objTree.getSession();
724: addDetailTab(objTree, DatabaseObjectType.SESSION,
725: new OptionsTab());
726: addDetailTab(objTree, IObjectTypes.CONSUMER_GROUP,
727: new DatabaseObjectInfoTab());
728: addDetailTab(objTree, DatabaseObjectType.FUNCTION,
729: new DatabaseObjectInfoTab());
730: addDetailTab(objTree, DatabaseObjectType.INDEX,
731: new DatabaseObjectInfoTab());
732: addDetailTab(objTree, DatabaseObjectType.INDEX,
733: new IndexColumnInfoTab());
734: addDetailTab(objTree, DatabaseObjectType.INDEX,
735: new IndexDetailsTab());
736: addDetailTab(objTree, IObjectTypes.LOB,
737: new DatabaseObjectInfoTab());
738: addDetailTab(objTree, DatabaseObjectType.SEQUENCE,
739: new DatabaseObjectInfoTab());
740: addDetailTab(objTree, DatabaseObjectType.TRIGGER,
741: new DatabaseObjectInfoTab());
742: addDetailTab(objTree, IObjectTypes.TRIGGER_PARENT,
743: new DatabaseObjectInfoTab());
744: addDetailTab(objTree, IObjectTypes.TYPE,
745: new DatabaseObjectInfoTab());
746:
747: // Expanders.
748: addExpander(objTree, DatabaseObjectType.SCHEMA,
749: new SchemaExpander());
750: addExpander(objTree, DatabaseObjectType.TABLE,
751: new TableExpander());
752: addExpander(objTree, IObjectTypes.PACKAGE,
753: new PackageExpander());
754: addExpander(objTree, IObjectTypes.USER_PARENT,
755: new UserParentExpander(session));
756: addExpander(objTree, IObjectTypes.SESSION_PARENT,
757: new SessionParentExpander());
758: addExpander(objTree, IObjectTypes.INSTANCE_PARENT,
759: new InstanceParentExpander());
760: addExpander(objTree, IObjectTypes.TRIGGER_PARENT,
761: new TriggerParentExpander());
762:
763: addDetailTab(objTree, DatabaseObjectType.PROCEDURE,
764: new ObjectSourceTab("PROCEDURE",
765: "Show stored procedure source"));
766: addDetailTab(objTree, DatabaseObjectType.FUNCTION,
767: new ObjectSourceTab("FUNCTION", "Show function source"));
768: addDetailTab(objTree, IObjectTypes.PACKAGE,
769: new ObjectSourceTab("PACKAGE", "Specification",
770: "Show package specification"));
771: addDetailTab(objTree, IObjectTypes.PACKAGE,
772: new ObjectSourceTab("PACKAGE BODY", "Body",
773: "Show package body"));
774: addDetailTab(objTree, IObjectTypes.TYPE, new ObjectSourceTab(
775: "TYPE", "Specification", "Show type specification"));
776: addDetailTab(objTree, IObjectTypes.TYPE, new ObjectSourceTab(
777: "TYPE BODY", "Body", "Show type body"));
778: addDetailTab(objTree, IObjectTypes.INSTANCE,
779: new InstanceDetailsTab());
780: addDetailTab(objTree, DatabaseObjectType.SEQUENCE,
781: new SequenceDetailsTab());
782: addDetailTab(objTree, IObjectTypes.SESSION,
783: new SessionDetailsTab());
784: addDetailTab(objTree, IObjectTypes.SESSION,
785: new SessionStatisticsTab());
786: addDetailTab(objTree, DatabaseObjectType.TRIGGER,
787: new TriggerDetailsTab());
788: addDetailTab(objTree, DatabaseObjectType.TRIGGER,
789: new TriggerSourceTab());
790: addDetailTab(objTree, DatabaseObjectType.TRIGGER,
791: new TriggerColumnInfoTab());
792: addDetailTab(objTree, DatabaseObjectType.USER,
793: new UserDetailsTab(session));
794:
795: addDetailTab(objTree, DatabaseObjectType.VIEW,
796: new ViewSourceTab());
797: addDetailTab(objTree, DatabaseObjectType.TABLE,
798: new SnapshotSourceTab());
799:
800: // This fixes the issue where the tree is getting constructed prior to
801: // the Oracle plugin expanders being registered.(USERS, SESSIONS,
802: // INSTANCES nodes have no children until the tree is refreshed). Even
803: // though this is a hack, it doesn't seem to negatively impact
804: // performance even when loading all schemas.
805: GUIUtils.processOnSwingEventThread(new Runnable() {
806: public void run() {
807: objTree.refreshTree();
808: }
809: });
810: }
811:
812: private void addExpander(final IObjectTreeAPI objTree,
813: final DatabaseObjectType dboType, final INodeExpander exp) {
814: objTree.addExpander(dboType, exp);
815: }
816:
817: private void addDetailTab(final IObjectTreeAPI objTree,
818: final DatabaseObjectType dboType, final IObjectTab tab) {
819: objTree.addDetailTab(dboType, tab);
820: }
821: }
|