Please use the Apache issue tracking system for new NetBeans issues (https://issues.apache.org/jira/projects/NETBEANS0/issues) !!

Bug 154176

Summary: Show system tables separately
Product: db Reporter: Roman Mostyka <romanmostyka>
Component: CodeAssignee: Jaroslav Havlin <jhavlin>
Status: RESOLVED FIXED QA Contact: issues <issues.netbeans.org>
Priority: P3 CC: apireviews
Version: 6.xKeywords: API_REVIEW_FAST, NETFIX, PATCH_AVAILABLE
Target Milestone: 7.4   
Hardware: All   
OS: All   
Whiteboard:
Issue Type: ENHANCEMENT Exception Report:
Bug Depends on:    
Bug Blocks: 152816    
Attachments: proposed patch part1 v1
proposed patch part2 v1
proposed patch part3 v1

Description Roman Mostyka 2008-11-30 21:10:25 UTC
1. Connect to MySQL.
2. Expand DB's node and try to find system tables.

Result: System tables aren't shown separately, but it will be better if user is able to watch only system tables or work DB.
Comment 1 Jiri Rechtacek 2009-10-16 14:15:51 UTC
Reassigned to new owner.
Comment 2 matthias42 2013-02-14 20:26:44 UTC
I had a look at the bug and the source code. Separating tables and system tables would really be nice. My usecase is a big informix installation and administrators that like to place the table in the "informix" schema/user.

As far as I can see this involves two module changes - other modules might need adjustments:

- The Database Metadata Model - Module needs:
  a) an enhancement for recognizing system tables - the minimal change
     would most probably adding a boolean property (system/isSystem) to
     org.netbeans.modules.db.metadata.model.api.Table - additionally
     I would introduce three new methods:
     getSystemTables => system Tables
     getUserTables => non system tables
     getTables would still return all Tables.
     getTable could be overloaded with getTable(String name, Boolean system)

  b) add Types/API Elements for Synonyms/Aliases
     I'll have to take a closer look whether it's possible to query
     the target of the alias


- The Database Explorer needs to be adjusted to expose the new elements
  There are multiple ways (filtering UserTables and SystemTables might be
  doable via a filter node, while Synonyms and Aliases are structuraly different.
Comment 3 matthias42 2013-05-07 18:10:21 UTC
Created attachment 134199 [details]
proposed patch part1  v1
Comment 4 matthias42 2013-05-07 18:10:45 UTC
Created attachment 134200 [details]
proposed patch part2  v1
Comment 5 matthias42 2013-05-07 18:16:47 UTC
The attached patches (part1 + part2) implement the necessary changes to db metadata model and db explorer modules.

Remarks:

- Additionally sensible: add "SYSTEM" to String-Array in OracleSchema.java (line 88, getTables statement) -> this was ommited in change 101485 (only JDBCSchema was modified to display SYSTEM Tables)

- I see only an SPI breach, not an API breach (I only added a method to the API, while the SPI requires a new method to be implemented), the SPI breach could be circumvented by defining a default SPI implementation of isSystem as false

- this does not implement part b of my proposal, but that can be done later
Comment 6 Jaroslav Havlin 2013-05-20 16:44:26 UTC
Thank you for the patch, Matthias.
I have some problem with MySQL, it seems that it doesn't support "SYSTEM TABLE" table type. E.g. type of table "columns_priv" is just "TABLE".

What about showing "System Tables" node only for databases that are known to support "SYSTEM TABLE", e.g. with help of "DatabaseMetaData.getTableTypes()", or some whitelist? (I can implement it after the API review).
Comment 7 Jaroslav Havlin 2013-05-20 16:48:11 UTC
Please review the following API changes in friend-private module 
db.metadata.model.

New methods:
org.netbeans.modules.db.metadata.model.api.Table.isSystem()
org.netbeans.modules.db.metadata.model.spi.TableImplementation.isSystem()

Thank you.
Comment 8 matthias42 2013-05-20 18:28:10 UTC
Hey,

(In reply to comment #6)
> I have some problem with MySQL, it seems that it doesn't support "SYSTEM TABLE"
> table type. E.g. type of table "columns_priv" is just "TABLE".
> 
> What about showing "System Tables" node only for databases that are known to
> support "SYSTEM TABLE", e.g. with help of "DatabaseMetaData.getTableTypes()",
> or some whitelist? (I can implement it after the API review).

I'd consider this a bug in the mysql driver - please see this:

Code:
------------------------------------------------
[...]
DatabaseMetaData dmd = c.getMetaData();
System.out.println(dmd.getDriverName() + " => " + dmd.getDriverVersion());
System.out.println(dmd.getDatabaseProductName() + " => " + dmd.getDatabaseProductVersion());
System.out.println("========= TABLE TYPES ==========");
ResultSet rs = dmd.getTableTypes();
while(rs.next()) {
    System.out.println(rs.getString(1));
}
System.out.println("====== MYSQL-Information Schema =========");
rs = dmd.getTables("information_schema", null, "%", new String[]{"SYSTEM TABLE"});
while (rs.next()) {
    System.out.println(rs.getString("TABLE_NAME") + "\t" + rs.getString("TABLE_TYPE"));
}
[...]
------------------------------------------------

Result:
------------------------------------------------
MySQL-AB JDBC Driver => mysql-connector-java-5.1.18 ( Revision: tonci.grgin@oracle.com-20110930151701-jfj14ddfq48ifkfq )
MySQL => 5.5.31-0ubuntu0.12.04.1
========= TABLE TYPES ==========
TABLE
VIEW
LOCAL TEMPORARY
====== MYSQL-Information Schema =========
CHARACTER_SETS	SYSTEM TABLE
[A huge set of tables more]
------------------------------------------------

So it looks as if the driver is contradictory. 

I would not remove the node if no system table is found, as it would differ  from the way views + procedures are displayed.
Comment 9 matthias42 2013-05-20 18:56:17 UTC
(In reply to comment #8)
> I'd consider this a bug in the mysql driver - please see this:

And as a good OSS citizen I opened the relevant bug report:

http://bugs.mysql.com/bug.php?id=69290
Comment 10 Jaroslav Havlin 2013-05-28 09:31:56 UTC
Created attachment 134994 [details]
proposed patch part3 v1

> I would not remove the node if no system table is found, as it would differ 
> from the way views + procedures are displayed.
I've tested the changes with some common databases (Derby, MySQL, PostgreSQL, Oracle), and in most cases one of [System Tables, Tables] was empty.
In these cases it's IMHO more practical to have only one node. (+ Users may not be sure whether the table they are looking for is system table or not.)

I'm also afraid that it's too late for applying a change that may be controversial (after feature freeze).

Because separating system tables would be valuable only to certain users, I would prefer to make it configurable (as property of DB connection). Please see the patch applicable on top of your two patches. What do you thing?
Thank you.
Comment 11 matthias42 2013-05-28 20:06:50 UTC
Looks good and reasonable.
Comment 12 Jaroslav Havlin 2013-05-29 06:11:15 UTC
OK, thank you very much, Matthias, for your patches, review, and reporting the MySQL bug.

Unless there are any objections, I'll integrate the changes tomorrow.
Comment 13 Jaroslav Havlin 2013-05-31 10:29:50 UTC
Integrated:
http://hg.netbeans.org/core-main/rev/fdbb2079825c (module db.metadata.model)
http://hg.netbeans.org/core-main/rev/47df600c373a (module db)
Comment 14 Quality Engineering 2013-06-02 01:07:54 UTC
Integrated into 'main-golden', will be available in build *201306012301* on http://bits.netbeans.org/dev/nightly/ (upload may still be in progress)
Changeset: http://hg.netbeans.org/main-golden/rev/fdbb2079825c
User: Jaroslav Havlin <jhavlin@netbeans.org>
Log: #154176: Show system tables separately - db.metadata.model

Patch by Matthias42.
By use of this website, you agree to the NetBeans Policies and Terms of Use. © 2014, Oracle Corporation and/or its affiliates. Sponsored by Oracle logo