proper.relaggs
Class SqlEngine

java.lang.Object
  proper.core.ProperObject
      proper.database.ConnectorObject
          proper.relaggs.SqlEngine

All Implemented Interfaces:

ProperInterface

public class SqlEngine
extends ConnectorObject

Title: SqlEngine Description: Connection to database system.
Changelog:

• FracPete: LEFT OUTER JOIN introduced (optional)
  see setLeftOuterJoin
• FracPete: can use any index instead of primary key (optional)
  see setUseAnyIndex
• FracPete: can use specific index for aggregation (optional)
  see setUseIndex
• FracPete: list of tables to exclude from aggregation possible (optional)
  see setExcludedTables
• FracPete (2004-05-27): broke down STDDEV into aggregate functions, since implementations differ (MySQL: population, PostgreSQL: sample)
  see getStddev, USE_BUILTIN_STDDEV, USE_SAMPLE_STDDEV
• FracPete (2004-05-28):
  • removed limitation for tables with no schema
  • getTables returns only tables now (with PostgreSQL it also returned the Indices)
  • uses DB-independent DATA_TYPE instead TYPE_NAME, see RelaggsAttribute
  • LIMIT %ROWS% implemented, see getLimit
  • implemented method that generates a CREATE TABLE statement from a SELECT, since "CREATE TABLE ... SELECT ..." is not ANSI compatible, see getTableCreateFromSelect
  • made aggregateAttributes more ANSI compliant, i.e. removed the INDEX generation from the TABLE creation
• FracPete (2005-05-15): use of foreign keys instead column names for disocovering table relations.

Since:

Nov 2002

Version:

$Revision: 1.2 $

Author:

Florian Heidenreich, Susanne Streuer, FracPete

See Also:

RelaggsAttribute, RelaggsDatabase, RelaggsTable

Field Summary

static java.lang.String	UNKNOWN_TYPE used for unknown column types
static boolean	USE_BUILTIN_STDDEV whether to use the database dependent STDDEV function
static boolean	USE_SAMPLE_STDDEV whether to use sample or population STDDEV

Fields inherited from interface proper.core.ProperInterface

VERSION

Constructor Summary

SqlEngine(Connector conn)
Creates the interface between database and userinterface.

Method Summary

void	addAggregatedAttributes(RelaggsTable dest_table, RelaggsTable source_table, RelaggsStructure struct, Join join) Adds the aggregated attributes to the relaggs table. FracPete: - introduced HashSet to skip duplicate columns and also that there are not too many columns inserted - ADD COLUMN split into single statements (necessary for ANSI SQL!)
java.lang.String	aggregateAttributes(RelaggsTable main, RelaggsTable aggr, Join join, int number) Combines two tables 'main' and 'aggr' by adding the aggregated columns of table 'aggr' to table main. FracPete: can perform LEFT OUTER JOIN, CREATE TABLE statement now more ANSI compliant
void	dropTable(java.lang.String table) Drops a table from the database.
RelaggsStructure	getAttributes(RelaggsTable table, RelaggsAttribute attribute) Reads the attributes of the given table into the RelaggsStructure.
java.util.Vector	getDatabases() Returns the available databases.
boolean	getLeftOuterJoin() returns whether we use natural or left outer joins during aggregation
java.lang.String	getLimit() returns the LIMIT statement (unprocessed)
java.lang.String	getLimit(int rows) returns the LIMIT statement for the given number of rows
int	getMaxColumnCount() returns the max number of columns for table, -1 if error
java.util.Vector	getPrimaryKeys(java.lang.String table) Returns a Vector containing the primary keys of the table.
static java.lang.String	getStddev(java.lang.String colName) returns the stddev statement for the given column
RelaggsTable	getTable(java.lang.String name) Creates a RelaggsTable object containing the table information (attributes, primary keys).
java.lang.String	getTableCreateFromSelect(java.lang.String table, java.lang.String sql) generates a CREATE TABLE statement from the given select statement.
java.util.Vector	getTables() Deprecated. replaced by SqlEngine.getMetaData()
java.lang.String	getTargetField() returns the field used as class in Weka
java.lang.String	getTargetTable() returns the table that contains the column used as class in Weka
int	getTmpNumber() Returns the number for a new temporary table.
static java.lang.String	getTypeString(int type, int size) returns the definition of a type, used in CREATE TABLE
boolean	getUseAnyIndex() returns whether we use the primary key or any index for determining the relation between the tables
boolean	getUseForeignKeys() returns whether to use "real" foreign key relations or base the table relation on column names
java.lang.String	getUseIndex() returns the index to use (primary = "", name otherwise)
void	incTmpNumber() Increments the number for a new temporary table by 1.
boolean	indexMatches(java.lang.String index) checks whether the given index matches the index we want to use; if useIndex = "" then all indices match
void	info() Displays information about the server, the driver, etc.
void	renameTable(java.lang.String old_name, java.lang.String new_name) Renames a table of the database.
void	setColumns(RelaggsTable table) sets the columns in this table
void	setExcludedTables(java.lang.String tables) sets the tables (comma separated) that shall not be included in the aggregation process
void	setLeftOuterJoin(boolean leftOuterJoin) whether to use natural or left outer joins during aggregation.
void	setTargetField(java.lang.String targetField) sets the field used as class in Weka
void	setTargetTable(java.lang.String targetTable) sets the table that contains the column used as class in Weka
void	setTmpNumber(int value) Sets the number for a new temporary table.
void	setUseAnyIndex(boolean use) whether to use the primary key or any index for determining the relation between the tables
void	setUseForeignKeys(boolean use) whether to use "real" foreign key relations or base the table relation on column names
void	setUseIndex(java.lang.String index) whether to use the primary key (= "") or a special one (!= "").

Methods inherited from class proper.database.ConnectorObject

getConnector

Methods inherited from class proper.core.ProperObject

addListener, addOutput, classInVerbose, debugprint, debugprint, debugprint, debugprint, debugprintln, debugprintln, debugprintln, debugprintln, debugprintln, getListeners, getRuntime, getVerbose, getVerboseLevel, getVerboseLevelForClass, getVerboseString, getVerboseStringForClass, notifyListener, notifyListeners, print, print, print, print, println, println, println, println, println, printMemory, removeListener, setOutput, setVerbose, setVerboseLevel, setVerboseString, toString

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, wait, wait, wait

Field Detail

USE_BUILTIN_STDDEV

public static final boolean USE_BUILTIN_STDDEV

whether to use the database dependent STDDEV function

See Also:

Constant Field Values

USE_SAMPLE_STDDEV

public static final boolean USE_SAMPLE_STDDEV

whether to use sample or population STDDEV

See Also:

Constant Field Values

UNKNOWN_TYPE

public static final java.lang.String UNKNOWN_TYPE

used for unknown column types

See Also:

getTypeString(int, int), Constant Field Values

Constructor Detail

SqlEngine

public SqlEngine(Connector conn)

Creates the interface between database and userinterface. [e.g. new SqlEngine("org.gjt.mm.mysql.Driver","jdbc:mysql:///","","");]

Parameters:

conn - the connection to the database

Method Detail

info

public void info()

Displays information about the server, the driver, etc.

setTmpNumber

public void setTmpNumber(int value)

Sets the number for a new temporary table.

Parameters:

value - the new number

incTmpNumber

public void incTmpNumber()

Increments the number for a new temporary table by 1.

getTmpNumber

public int getTmpNumber()

Returns the number for a new temporary table.

setLeftOuterJoin

public void setLeftOuterJoin(boolean leftOuterJoin)

whether to use natural or left outer joins during aggregation. it automatically sets useAnyIndex to TRUE

See Also:

setUseAnyIndex(boolean)

getLeftOuterJoin

public boolean getLeftOuterJoin()

returns whether we use natural or left outer joins during aggregation

setUseAnyIndex

public void setUseAnyIndex(boolean use)

whether to use the primary key or any index for determining the relation between the tables

getUseAnyIndex

public boolean getUseAnyIndex()

returns whether we use the primary key or any index for determining the relation between the tables

setUseForeignKeys

public void setUseForeignKeys(boolean use)

whether to use "real" foreign key relations or base the table relation on column names

getUseForeignKeys

public boolean getUseForeignKeys()

returns whether to use "real" foreign key relations or base the table relation on column names

setUseIndex

public void setUseIndex(java.lang.String index)

whether to use the primary key (= "") or a special one (!= ""). setting it to an index implies setting useAnyIndex to TRUE

See Also:

setUseAnyIndex(boolean)

getUseIndex

public java.lang.String getUseIndex()

returns the index to use (primary = "", name otherwise)

indexMatches

public boolean indexMatches(java.lang.String index)

checks whether the given index matches the index we want to use; if useIndex = "" then all indices match

Parameters:

index - the index to check

Returns:

whehter the index matches

setTargetTable

public void setTargetTable(java.lang.String targetTable)

sets the table that contains the column used as class in Weka

getTargetTable

public java.lang.String getTargetTable()

returns the table that contains the column used as class in Weka

setTargetField

public void setTargetField(java.lang.String targetField)

sets the field used as class in Weka

getTargetField

public java.lang.String getTargetField()

returns the field used as class in Weka

setExcludedTables

public void setExcludedTables(java.lang.String tables)

sets the tables (comma separated) that shall not be included in the aggregation process

getMaxColumnCount

public int getMaxColumnCount()

returns the max number of columns for table, -1 if error

setColumns

public void setColumns(RelaggsTable table)

sets the columns in this table

getLimit

public java.lang.String getLimit()

returns the LIMIT statement (unprocessed)

getLimit

public java.lang.String getLimit(int rows)

returns the LIMIT statement for the given number of rows

getDatabases

public java.util.Vector getDatabases()

Returns the available databases.

Returns:

Vector containing the names of the databases

getTables

public java.util.Vector getTables()

Deprecated. replaced by SqlEngine.getMetaData()

Returns all available tables.

Returns:

Vector tables

getPrimaryKeys

public java.util.Vector getPrimaryKeys(java.lang.String table)

Returns a Vector containing the primary keys of the table.

Parameters:

table - the name of the table

Returns:

Vector containing the names of the primary keys

getTable

public RelaggsTable getTable(java.lang.String name)

Creates a RelaggsTable object containing the table information (attributes, primary keys).

Parameters:

name - the name of the table

Returns:

RelaggsTable object containing the information

getTypeString

public static java.lang.String getTypeString(int type,
                                             int size)

returns the definition of a type, used in CREATE TABLE

Parameters:

type - the JDBC type

size - the display size

See Also:

UNKNOWN_TYPE

getTableCreateFromSelect

public java.lang.String getTableCreateFromSelect(java.lang.String table,
                                                 java.lang.String sql)
                                          throws java.lang.Exception

generates a CREATE TABLE statement from the given select statement. it assumes that there is no "LIMIT x" (or similar) already contained

Parameters:

table - the name of the table to create

sql - the select statement to retrieve the column definitions from

Throws:

java.lang.Exception - throws an exception if something with the select-statement went wrong

aggregateAttributes

public java.lang.String aggregateAttributes(RelaggsTable main,
                                            RelaggsTable aggr,
                                            Join join,
                                            int number)

Combines two tables 'main' and 'aggr' by adding the aggregated columns of table 'aggr' to table main.
FracPete: can perform LEFT OUTER JOIN, CREATE TABLE statement now more ANSI compliant

Parameters:

main - the table whose columns remain untouched

aggr - the table whose aggregated columns should be added

join - the foreign key relation

number - the actual number of the next temporary table

Returns:

the name of the table that contains the combined data

getStddev

public static java.lang.String getStddev(java.lang.String colName)

returns the stddev statement for the given column

See Also:

USE_BUILTIN_STDDEV, USE_SAMPLE_STDDEV

getAttributes

public RelaggsStructure getAttributes(RelaggsTable table,
                                      RelaggsAttribute attribute)
                               throws java.lang.IllegalArgumentException

Reads the attributes of the given table into the RelaggsStructure. (includes condition attribute processing and creating all aggregation values)

Parameters:

table - the table that should be aggregated

attribute - the foreign key attribute that is used for the aggregation

Returns:

the RelaggsStructure containing the aggregated values

Throws:

java.lang.IllegalArgumentException

addAggregatedAttributes

public void addAggregatedAttributes(RelaggsTable dest_table,
                                    RelaggsTable source_table,
                                    RelaggsStructure struct,
                                    Join join)

Adds the aggregated attributes to the relaggs table.
FracPete:
- introduced HashSet to skip duplicate columns and also that there are not too many columns inserted
- ADD COLUMN split into single statements (necessary for ANSI SQL!)

Parameters:

dest_table - the relaggs table in which the aggregated attributes should be inserted

source_table - the table from which the aggregated attributes come

struct - the RelaggsStructure containing the aggregation values

key_attribute - the foreign key attribute of the source table

dropTable

public void dropTable(java.lang.String table)

Drops a table from the database.

Parameters:

table - the name of table to drop

renameTable

public void renameTable(java.lang.String old_name,
                        java.lang.String new_name)

Renames a table of the database.

Parameters:

old_name - the old name of the table

new_name - the new name of the table