Difference between revisions of "Documentation/DevGuide/Database/Database Design"

Latest revision as of 15:01, 21 December 2020

Retrieving Information about a Database

The com.sun.star.sdbc.XDatabaseMetaData interface is implemented by SDBC drivers to provide information about their underlying database. It is used primarily by application servers and tools to determine how to interact with a given data source. Applications may also use XDatabaseMetaData methods to get information about a database. The com.sun.star.sdbc.XDatabaseMetaData interface includes over 150 methods, that are categorized according to the types of information they provide:

• General information about the database.
• If the database supports a given feature or capability.
• Database limits.
• What SQL objects the database contains and attributes of those objects.
• Transaction support offered by the data source.

Additionally, the com.sun.star.sdbc.XDatabaseMetaData interface uses a resultset with more than 40 possible columns as return values in many com.sun.star.sdbc.XDatabaseMetaData methods. This section presents an overview of the com.sun.star.sdbc.XDatabaseMetaData interface, and provides examples illustrating the categories of metadata methods. For a comprehensive listing, consult the SDBC API specification.

• Creating the XDatabaseMetaData objects

A com.sun.star.sdbc.XDatabaseMetaData object is created using the Connection method getMetaData(). Once created, it can be used to dynamically discover information about the underlying data source. The following code example creates a com.sun.star.sdbc.XDatabaseMetaData object and uses it to determine the maximum number of characters allowed for a table name.

  // xConnection is a Connection object
  XDatabaseMetaData dbmd = xConnection.getMetaData();
  int maxLen = dbmd.getMaxTableNameLength();

Retrieving General Information

Some com.sun.star.sdbc.XDatabaseMetaData methods are used to dynamically discover general information about a database, as well as details about its implementation. Some of the methods in this category are:

• getURL()
• getUserName()
• getDatabaseProductVersion(), getDriverMajorVersion() and getDriverMinorVersion()
• getSchemaTerm(), getCatalogTerm() and getProcedureTerm()
• nullsAreSortedHigh() and nullsAreSortedLow()
• usesLocalFiles() and usesLocalFilePerTable()
• getSQLKeywords()

Determining Feature Support

A large group of com.sun.star.sdbc.XDatabaseMetaData methods can be used to determine whether a given feature or set of features is supported by the driver or underlying database. Beyond this, some methods describe what level of support is provided. Some of the methods that describe support for individual features are:

• supportsAlterTableWithDropColumn()
• supportsBatchUpdates()
• supportsTableCorrelationNames()
• supportsPositionedDelete()
• supportsFullOuterJoins()
• supportsStoredProcedures()
• supportsMixedCaseQuotedIdentifiers()

Methods to describe the level of feature support include:

• supportsANSI92EntryLevelSQL()
• supportsCoreSQLGrammar()

Database Limits

Another group of methods provides the limits imposed by a given database. Some methods in this category are:

• getMaxRowSize()
• getMaxStatementLength()
• getMaxTablesInSelect()
• getMaxConnections()
• getMaxCharLiteralLength()
• getMaxColumnsInTable()

Methods in this group return the limit as an int. A return value of zero means there is no limit or the limit is unknown.

SQL Objects and their Attributes

Some methods provide information about the SQL objects that populate a given database. This group also includes methods to determine the attributes of those objects. Methods in this group return ResultSet objects in which each row describes a particular object. For example, the method getUDTs() returns a ResultSet object in which there is a row for each user defined type (UDT) that has been defined in the database. Examples of this category are:

• getSchemas() and getCatalogs()
• getTables()
• getPrimaryKeys()
• getColumns()
• getProcedures() and getProcedureColumns()
• getUDTs()

For example, to display the structure of a table that consists of columns and keys (primary keys, foreign keys), and also indexes defined on the table, the com.sun.star.sdbc.XDatabaseMetaData interface is required:

  XDatabaseMetaData dm = con.getMetaData();
  XResultSet rsTables = dm.getTables(null, "%", "SALES", null);
  XRow rowTB = (XRow)UnoRuntime.queryInterface(XRow.class, rsTables);
 
  while (rsTables.next()) {
      String catalog = rowTB.getString(1);
      if (rowTB.wasNull())
          catalog = null;
 
      String schema = rowTB.getString(2);
      if (rowTB.wasNull())
          schema = null;
 
      String table = rowTB.getString(3);
      String type = rowTB.getString(4);
      System.out.println("Catalog: " + catalog +
          " Schema: " + schema + " Table: " + table + "Type: " + type);
      System.out.println("------------------ Columns ------------------");
      XResultSet rsColumns = dm.getColumns(catalog, schema, table, "%");
      XRow rowCL = (XRow)UnoRuntime.queryInterface(XRow.class, rsColumns);
      while (rsColumns.next()) {
          System.out.println("Column: " + rowCL.getString(4) + 
          " Type: " + rowCL.getInt(5) + " TypeName: " + rowCL.getString(6) );
      }
  }

Another method often used when creating SQL statements is the method getIdentifierQuoteString(). This method is always used when table or column names need to be quoted in the SQL statement. For example:

  SELECT "Name", "Price" FROM "Sales"

In this case, the identifier quotation is the character ". The combination of XDatabaseMetaData methods in the following code fragment may be useful to know if the database supports catalogs and/or schemata.

  public static String quoteTableName(XConnection con, String sCatalog, String sSchema,
          String sTable) throws com.sun.star.uno.Exception {
      XDatabaseMetaData dbmd = con.getMetaData();
      String sQuoteString = dbmd.getIdentifierQuoteString();
      String sSeparator = ".";
      String sComposedName = "";
      String sCatalogSep = dbmd.getCatalogSeparator();
      if (0 != sCatalog.length() && dbmd.isCatalogAtStart() && 0 != sCatalogSep.length()) {
          sComposedName += sCatalog;
          sComposedName += dbmd.getCatalogSeparator();
      }
      if (0 != sSchema.length()) {
          sComposedName += sSchema;
          sComposedName += sSeparator;
          sComposedName += sTable;
      } else {
          sComposedName += sTable;
      }
      if (0 != sCatalog.length() && !dbmd.isCatalogAtStart() && 0 != sCatalogSep.length()) {
          sComposedName += dbmd.getCatalogSeparator();
          sComposedName += sCatalog;
      }
      return sComposedName;
  }