Repository: go
Issue: database/sql: Support for database, catalog, schema and table level metadata
Labels: NeedsInvestigation

by **glen.newton**:

<pre>database/sql does not offer the ability to dynamically peruse databases, catalogs,
schemas and tables and their underlying metadata at runtime. Without this, it is not
possible to make, for example, a Go program that can copy arbitrary tables from a
database, by examining their metadata at runtime.

What is needed is the equivalent of Java JDBC's DatabaseMetaData
<a href="http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html">http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html</a>

While JDBC's DatabaseMetaData may seem be overkill, being able to examine a table's
metadata in the same manner as the table's data is attractive. In order to support rich
and complex interactions with sql databases, emulating JDBC would not be a bad idea.


As an initial step however, I would suggest the following minimum extensions to the sql
package:

func (db *DB) GetSchemas() (*Rows, error)
JDBC equivalent &amp; explanation:
  ResultSet getSchemas()   
  <a href="http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getSchemas%28%29">http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getSchemas%28%29</a>

func (db *DB) GetCatalogs() (*Rows, error)
JDBC equivalent &amp; explanation:
  ResultSet     getCatalogs()
  <a href="http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getCatalogs%28%29">http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getCatalogs%28%29</a>


func (db *DB) GetTables(catalog String, schemaPattern String, tableNamePattern String,
types String[]) (*Rows, error)
JDBC equivalent &amp; explanation:
  ResultSet     getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types)
  <a href="http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getTables%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String">http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getTables%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String</a>[]%29


func (db *DB) getColumns(catalog String, schemaPattern String, tableNamePattern String,
columnNamePattern String) (*Rows, error)
JDBC equivalent &amp; explanation:
  ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)
  <a href="http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getColumns%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String%29">http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getColumns%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String%29</a>

The following are important and should also be considered:
getAttributes(...)
<a href="http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getAttributes(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)">http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getAttributes(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)</a>

getCrossReference(...)
<a href="http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getCrossReference%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String%29">http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getCrossReference%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String%29</a>

getIndexInfo(...)
<a href="http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getIndexInfo(java.lang.String,%20java.lang.String,%20java.lang.String,%20boolean,%20boolean)">http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getIndexInfo(java.lang.String,%20java.lang.String,%20java.lang.String,%20boolean,%20boolean)</a>

getPrimaryKeys(...)
<a href="http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getPrimaryKeys(java.lang.String,%20java.lang.String,%20java.lang.String)">http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getPrimaryKeys(java.lang.String,%20java.lang.String,%20java.lang.String)</a>


I would also suggest that bug <a href="https://golang.org/issue/5606">https://golang.org/issue/5606</a> be
solved in a similar and consistent fashion with something like
<a href="http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSetMetaData.html">http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSetMetaData.html</a>

Apologies for using Java as an example, but JDBC has done its homework and I think it
represents best practices. How it maps into a Go context is of course to be debated and
I am willing to accept that my very direct mapping suggestion may not be the best or the
most acceptable to the community.</pre>