weka.core.converters

## Class DatabaseLoader

- java.lang.Object
- - weka.core.converters.AbstractLoader
  - - weka.core.converters.DatabaseLoader

- All Implemented Interfaces:
    java.io.Serializable, BatchConverter, DatabaseConverter, IncrementalConverter, Loader, EnvironmentHandler, OptionHandler, RevisionHandler

      public class DatabaseLoader
      extends AbstractLoader
      implements BatchConverter, IncrementalConverter, DatabaseConverter, OptionHandler, EnvironmentHandler

  Reads Instances from a Database. Can read a database in batch or incremental mode.
  In inremental mode MySQL and HSQLDB are supported.
  For all other DBMS set a pseudoincremental mode is used:
  In pseudo incremental mode the instances are read into main memory all at once and then incrementally provided to the user.
  For incremental loading the rows in the database table have to be ordered uniquely.
  The reason for this is that every time only a single row is fetched by extending the user query by a LIMIT clause.
  If this extension is impossible instances will be loaded pseudoincrementally. To ensure that every row is fetched exaclty once, they have to ordered.
  Therefore a (primary) key is necessary.This approach is chosen, instead of using JDBC driver facilities, because the latter one differ betweeen different drivers.
  If you use the DatabaseSaver and save instances by generating automatically a primary key (its name is defined in DtabaseUtils), this primary key will be used for ordering but will not be part of the output. The user defined SQL query to extract the instances should not contain LIMIT and ORDER BY clauses (see -Q option).
  In addition, for incremental loading, you can define in the DatabaseUtils file how many distinct values a nominal attribute is allowed to have. If this number is exceeded, the column will become a string attribute.
  In batch mode no string attributes will be created.

  Valid options are:

       -url <JDBC URL>
        The JDBC URL to connect to.
        (default: from DatabaseUtils.props file)

       -user <name>
        The user to connect with to the database.
        (default: none)

       -password <password>
        The password to connect with to the database.
        (default: none)

       -Q <query>
        SQL query of the form
         SELECT <list of columns>|* FROM <table> [WHERE]
        to execute.
        (default: Select * From Results0)

       -P <list of column names>
        List of column names uniquely defining a DB row
        (separated by ', ').
        Used for incremental loading.
        If not specified, the key will be determined automatically,
        if possible with the used JDBC driver.
        The auto ID column created by the DatabaseSaver won't be loaded.

       -I
        Sets incremental loading

  Version:
    $Revision: 12418 $
  Author:
    Stefan Mutter (mutter@cs.waikato.ac.nz)
  See Also:
    `Loader`, Serialized Form

- - ### Nested Class Summary

    - ### Nested classes/interfaces inherited from interface weka.core.converters.Loader

      `Loader.StructureNotReadyException`

  - ### Field Summary

    - ### Fields inherited from interface weka.core.converters.Loader

      `BATCH, INCREMENTAL, NONE`

  - ### Constructor Summary

    Constructors
    Constructor and Description
    `DatabaseLoader()` Constructor

  - ### Method Summary

    All Methods Static Methods Instance Methods Concrete Methods
    Modifier and Type | Method and Description
    --- | ---
    `void` | `connectToDatabase()` Opens a connection to the database
    `java.lang.String` | `customPropsFileTipText()` The tip text for this property.
    `java.io.File` | `getCustomPropsFile()` Returns the custom properties file in use, if any.
    `Instances` | `getDataSet()` Return the full data set in batch mode (header and all intances at once).
    `java.lang.String` | `getKeys()` Gets the key columns' name
    `Instance` | `getNextInstance(Instances structure)` Read the data set incrementally---get the next instance in the data set or returns null if there are no more instances to get.
    `java.lang.String[]` | `getOptions()` Gets the setting
    `java.lang.String` | `getPassword()` Returns the database password
    `java.lang.String` | `getQuery()` Gets the query to execute against the database
    `java.lang.String` | `getRevision()` Returns the revision string.
    `boolean` | `getSparseData()` Gets whether data is to be returned as a set of sparse instances
    `Instances` | `getStructure()` Determines and returns (if possible) the structure (internally the header) of the data set as an empty set of instances.
    `java.lang.String` | `getUrl()` Gets the URL
    `java.lang.String` | `getUser()` Gets the user name
    `java.lang.String` | `globalInfo()` Returns a string describing this Loader
    `java.lang.String` | `keysTipText()` the tip text for this property
    `java.util.Enumeration<Option>` | `listOptions()` Lists the available options
    `static void` | `main(java.lang.String[] options)` Main method.
    `java.lang.String` | `passwordTipText()` the tip text for this property
    `java.lang.String` | `queryTipText()` the tip text for this property
    `void` | `reset()` Resets the Loader ready to read a new data set using set options
    `void` | `resetOptions()` Resets the Loader to the settings in either the default DatabaseUtils.props or any property file that the user has specified via setCustomPropsFile().
    `void` | `resetStructure()` Resets the structure of instances
    `void` | `setCustomPropsFile(java.io.File value)` Sets the custom properties file to use.
    `void` | `setEnvironment(Environment env)` Set the environment variables to use.
    `void` | `setKeys(java.lang.String keys)` Sets the key columns of a database table
    `void` | `setOptions(java.lang.String[] options)` Sets the options.
    `void` | `setPassword(java.lang.String password)` Sets user password for the database
    `void` | `setQuery(java.lang.String q)` Sets the query to execute against the database
    `void` | `setSource()` Sets the database url using the DatabaseUtils file
    `void` | `setSource(java.lang.String url)` Sets the database url
    `void` | `setSource(java.lang.String url, java.lang.String userName, java.lang.String password)` Sets the database url, user and pw
    `void` | `setSparseData(boolean s)` Sets whether data should be encoded as sparse instances
    `void` | `setUrl(java.lang.String url)` Sets the database URL
    `void` | `setUser(java.lang.String user)` Sets the database user
    `java.lang.String` | `sparseDataTipText()` Returns the tip text for this property
    `java.lang.String` | `urlTipText()` the tip text for this property
    `java.lang.String` | `userTipText()` the tip text for this property

    - ### Methods inherited from class weka.core.converters.AbstractLoader

      `setRetrieval, setSource, setSource`

    - ### Methods inherited from class java.lang.Object

      `equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait`

- - ### Constructor Detail

    - #### DatabaseLoader

          public DatabaseLoader()
                         throws java.lang.Exception

      Constructor
      Throws:
        `java.lang.Exception` - if initialization fails

  - ### Method Detail

    - #### globalInfo

          public java.lang.String globalInfo()

      Returns a string describing this Loader
      Returns:
        a description of the Loader suitable for displaying in the explorer/experimenter gui

    - #### setEnvironment

          public void setEnvironment(Environment env)

      Set the environment variables to use.
      Specified by:
        `setEnvironment` in interface `EnvironmentHandler`
      Parameters:
        `env` - the environment variables to use

    - #### resetOptions

          public void resetOptions()

      Resets the Loader to the settings in either the default DatabaseUtils.props or any property file that the user has specified via setCustomPropsFile().

    - #### reset

          public void reset()

      Resets the Loader ready to read a new data set using set options
      Specified by:
        `reset` in interface `Loader`
      Overrides:
        `reset` in class `AbstractLoader`
      Throws:
        `java.lang.Exception` - if an error occurs while disconnecting from the database

    - #### resetStructure

          public void resetStructure()

      Resets the structure of instances

    - #### setQuery

          public void setQuery(java.lang.String q)

      Sets the query to execute against the database
      Parameters:
        `q` - the query to execute

    - #### getQuery

          @OptionMetadata(displayName="Query",
                          description="The query to execute",
                          displayOrder=4)
          public java.lang.String getQuery()

      Gets the query to execute against the database
      Returns:
        the query

    - #### queryTipText

          public java.lang.String queryTipText()

      the tip text for this property
      Returns:
        the tip text

    - #### setKeys

          public void setKeys(java.lang.String keys)

      Sets the key columns of a database table
      Parameters:
        `keys` - a String containing the key columns in a comma separated list.

    - #### getKeys

          @OptionMetadata(displayName="Key columns",
                          description="Specific key columns to use if a primary key cannot be automatically detected. Used in incremental loading.",
                          displayOrder=5)
          public java.lang.String getKeys()

      Gets the key columns' name
      Returns:
        name of the key columns'

    - #### keysTipText

          public java.lang.String keysTipText()

      the tip text for this property
      Returns:
        the tip text

    - #### setCustomPropsFile

          public void setCustomPropsFile(java.io.File value)

      Sets the custom properties file to use.
      Parameters:
        `value` - the custom props file to load database parameters from, use null or directory to disable custom properties.

    - #### getCustomPropsFile

          @OptionMetadata(displayName="DB config file",
                          description="The custom properties that the user can use to override the default ones.",
                          displayOrder=8)
           @FilePropertyMetadata(fileChooserDialogType=0,
                                directoriesOnly=false)
          public java.io.File getCustomPropsFile()

      Returns the custom properties file in use, if any.
      Returns:
        the custom props file, null if none used

    - #### customPropsFileTipText

          public java.lang.String customPropsFileTipText()

      The tip text for this property.
      Returns:
        the tip text

    - #### setUrl

          public void setUrl(java.lang.String url)

      Sets the database URL
      Specified by:
        `setUrl` in interface `DatabaseConverter`
      Parameters:
        `url` - string with the database URL

    - #### getUrl

          @OptionMetadata(displayName="Database URL",
                          description="The URL of the database",
                          displayOrder=1)
          public java.lang.String getUrl()

      Gets the URL
      Specified by:
        `getUrl` in interface `DatabaseConverter`
      Returns:
        the URL

    - #### urlTipText

          public java.lang.String urlTipText()

      the tip text for this property
      Returns:
        the tip text

    - #### setUser

          public void setUser(java.lang.String user)

      Sets the database user
      Specified by:
        `setUser` in interface `DatabaseConverter`
      Parameters:
        `user` - the database user name

    - #### getUser

          @OptionMetadata(displayName="Username",
                          description="The user name for the database",
                          displayOrder=2)
          public java.lang.String getUser()

      Gets the user name
      Specified by:
        `getUser` in interface `DatabaseConverter`
      Returns:
        name of database user

    - #### userTipText

          public java.lang.String userTipText()

      the tip text for this property
      Returns:
        the tip text

    - #### setPassword

          public void setPassword(java.lang.String password)

      Sets user password for the database
      Specified by:
        `setPassword` in interface `DatabaseConverter`
      Parameters:
        `password` - the password

    - #### getPassword

          @OptionMetadata(displayName="Password",
                          description="The database password",
                          displayOrder=3)
           @PasswordProperty
          public java.lang.String getPassword()

      Returns the database password
      Returns:
        the database password

    - #### passwordTipText

          public java.lang.String passwordTipText()

      the tip text for this property
      Returns:
        the tip text

    - #### sparseDataTipText

          public java.lang.String sparseDataTipText()

      Returns the tip text for this property
      Returns:
        tip text for this property suitable for displaying in the explorer/experimenter gui

    - #### setSparseData

          public void setSparseData(boolean s)

      Sets whether data should be encoded as sparse instances
      Parameters:
        `s` - true if data should be encoded as a set of sparse instances

    - #### getSparseData

          @OptionMetadata(displayName="Create sparse instances",
                          description="Return sparse rather than normal instances",
                          displayOrder=6)
          public boolean getSparseData()

      Gets whether data is to be returned as a set of sparse instances
      Returns:
        true if data is to be encoded as sparse instances

    - #### setSource

          public void setSource(java.lang.String url,
                                java.lang.String userName,
                                java.lang.String password)

      Sets the database url, user and pw
      Parameters:
        `url` - the database url
        `userName` - the user name
        `password` - the password

    - #### setSource

          public void setSource(java.lang.String url)

      Sets the database url
      Parameters:
        `url` - the database url

    - #### setSource

          public void setSource()
                         throws java.lang.Exception

      Sets the database url using the DatabaseUtils file
      Throws:
        `java.lang.Exception` - if something goes wrong

    - #### connectToDatabase

          public void connectToDatabase()

      Opens a connection to the database

    - #### getStructure

          public Instances getStructure()
                                 throws java.io.IOException

      Determines and returns (if possible) the structure (internally the header) of the data set as an empty set of instances.
      Specified by:
        `getStructure` in interface `Loader`
      Specified by:
        `getStructure` in class `AbstractLoader`
      Returns:
        the structure of the data set as an empty set of Instances
      Throws:
        `java.io.IOException` - if an error occurs

    - #### getDataSet

          public Instances getDataSet()
                               throws java.io.IOException

      Return the full data set in batch mode (header and all intances at once).
      Specified by:
        `getDataSet` in interface `Loader`
      Specified by:
        `getDataSet` in class `AbstractLoader`
      Returns:
        the structure of the data set as an empty set of Instances
      Throws:
        `java.io.IOException` - if there is no source or parsing fails

    - #### getNextInstance

          public Instance getNextInstance(Instances structure)
                                   throws java.io.IOException

      Read the data set incrementally---get the next instance in the data set or returns null if there are no more instances to get. If the structure hasn't yet been determined by a call to getStructure then method does so before returning the next instance in the data set.
      Specified by:
        `getNextInstance` in interface `Loader`
      Specified by:
        `getNextInstance` in class `AbstractLoader`
      Parameters:
        `structure` - the dataset header information, will get updated in case of string or relational attributes
      Returns:
        the next instance in the data set as an Instance object or null if there are no more instances to be read
      Throws:
        `java.io.IOException` - if there is an error during parsing

    - #### getOptions

          public java.lang.String[] getOptions()

      Gets the setting
      Specified by:
        `getOptions` in interface `OptionHandler`
      Returns:
        the current setting

    - #### listOptions

          public java.util.Enumeration<Option> listOptions()

      Lists the available options
      Specified by:
        `listOptions` in interface `OptionHandler`
      Returns:
        an enumeration of the available options

    - #### setOptions

          public void setOptions(java.lang.String[] options)
                          throws java.lang.Exception

      Sets the options. Valid options are:

           -url <JDBC URL>
            The JDBC URL to connect to.
            (default: from DatabaseUtils.props file)

           -user <name>
            The user to connect with to the database.
            (default: none)

           -password <password>
            The password to connect with to the database.
            (default: none)

           -Q <query>
            SQL query of the form
             SELECT <list of columns>|* FROM <table> [WHERE]
            to execute.
            (default: Select * From Results0)

           -P <list of column names>
            List of column names uniquely defining a DB row
            (separated by ', ').
            Used for incremental loading.
            If not specified, the key will be determined automatically,
            if possible with the used JDBC driver.
            The auto ID column created by the DatabaseSaver won't be loaded.

           -I
            Sets incremental loading

      Specified by:
        `setOptions` in interface `OptionHandler`
      Parameters:
        `options` - the options
      Throws:
        `java.lang.Exception` - if options cannot be set

    - #### getRevision

          public java.lang.String getRevision()

      Returns the revision string.
      Specified by:
        `getRevision` in interface `RevisionHandler`
      Returns:
        the revision

    - #### main

          public static void main(java.lang.String[] options)

      Main method.
      Parameters:
        `options` - the options