proper.database
Class Joiner

java.lang.Object
  proper.core.ProperObject
      proper.database.ConnectorObject
          proper.database.ExecutorObject
              proper.database.Joiner

All Implemented Interfaces:

ProperInterface

public class Joiner
extends ExecutorObject

This class can perform different kinds of joins:

• NATURAL, LEFT OUTER and RIGHT OUTER
  the two tables are joined via some keys like in SQL
• BEST
  before the join is performed the class checks whether a LEFT OUTER or a RIGHT OUTER join is better

Version:

$Revision: 1.3 $

Author:

FracPete

Field Summary

static int	BESTOUTER_JOIN the best possible join (left outer or right outer)
static java.lang.String	JOIN_PREFIX prefix (+ number from ID) for ambigious columns in a join
static int	LEFTOUTER_JOIN the left outer join
static int	NATURAL_JOIN the default join
static int	RIGHTOUTER_JOIN the right outer join

Fields inherited from interface proper.core.ProperInterface

VERSION

Constructor Summary

Joiner(Connector conn)
initializes the object

Method Summary

java.util.HashSet	ambigiousColumns(java.lang.String table1, java.lang.String table2, java.util.Vector joinColumns) fills a hashset with the names of ambigious columns (actually instances of the class Column, not just the names), i.e.
java.lang.String	createJoin(java.lang.String table1, java.lang.String prefix1, java.lang.String table2, java.lang.String prefix2, java.util.Vector joinColumns, int type) creates the join statement with the given values, if no prefixes are given then there's one added automatically for ambigious columns
java.lang.String	createJoin(java.lang.String table1, java.lang.String table2, java.util.Vector joinColumns, int type) creates the join statement with the given values
java.lang.String	createJoin(java.lang.String table1, java.lang.String prefix1, java.util.Vector cols1, java.lang.String table2, java.lang.String prefix2, java.util.Vector cols2, java.util.Vector joinColumns, int type) creates the join statement with the given values, if no prefixes are given then there's one added automatically for ambigious columns
int	determineBestJoin(java.lang.String table1, java.lang.String table2, java.util.Vector joinColumns) determines the best join to perform over the two given tables and columns
int	determineBestJoin(java.lang.String table1, java.lang.String table2, java.util.Vector joinColumns, java.lang.String classField) determines the best join to perform over the two given tables and columns, where it takes into account not to loose any classes, if the class field is given
java.util.Vector	determineColumns(java.lang.String table1, boolean onlyIndexes1, java.lang.String table2, boolean onlyIndexes2) determines the columns to use in the join (the returned vector contains instances of "Join").
java.util.Vector	determineColumns(java.lang.String table1, java.lang.String table2) determines the columns to use in the join over all fields, not just indexes (the returned vector contains instances of "Table")
int	getClassCount(java.lang.String table1, java.lang.String table2, java.util.Vector joinColumns, java.lang.String classField, int joinType) returns the number of classes resulting from the given join
java.lang.String	getInsteadOfNull() returns the values (commalist of the format "name=value") to be used instead of NULL in the columns of a OUTER JOIN where NULL was inserted
int	getJoinType() returns the current join type
int	getRowsWithClassCount(java.lang.String table1, java.lang.String table2, java.util.Vector joinColumns, java.lang.String classField, int joinType) returns the number of rows with a class label resulting from the given join
java.lang.String	getTableCreateFromSelect(java.lang.String table, java.lang.String sql) generates a CREATE TABLE statement from the given select statement.
boolean	getUseForeignKeys() returns whether foreign keys are used for discovering relations between tables or only matching column names
boolean	join(java.lang.String table1, java.lang.String table2, java.lang.String resultTable) joins the two tables over all common columns, not just indexes
boolean	join(java.lang.String table1, java.lang.String prefix1, java.lang.String table2, java.lang.String prefix2, java.util.Vector joinColumns, java.lang.String resultTable) joins the two tables over the given columns
boolean	join(java.lang.String table1, java.lang.String prefix1, java.lang.String table2, java.lang.String prefix2, java.util.Vector joinColumns, java.lang.String resultTable, java.lang.String classField) joins the two tables over the given columns, prefixes have to be given for both tables, otherwise they are ignored
boolean	join(java.lang.String table1, java.lang.String table2, java.util.Vector joinColumns, java.lang.String resultTable) joins the two tables over the given columns
boolean	join(java.lang.String table1, java.lang.String table2, java.util.Vector joinColumns, java.lang.String resultTable, java.lang.String classField) joins the two tables over the given columns
boolean	join(java.lang.String table1, java.lang.String prefix1, java.util.Vector cols1, java.lang.String table2, java.lang.String prefix2, java.util.Vector cols2, java.util.Vector joinColumns, java.lang.String resultTable, java.lang.String classField) joins the two tables over the given columns, prefixes have to be given for both tables, otherwise they are ignored
static int	parseType(java.lang.String type) parses the given string to get the type from, it also takes the short forms like 'leftouter', 'rightouter', 'bestouter' and 'natural'.
void	setInsteadOfNull(java.lang.String insteadOfNull) sets the values (commalist of the format "name=value") to be used instead of NULL in the columns of a OUTER JOIN where NULL was inserted
void	setJoinType(int joinType) sets the join type
void	setUseForeignKeys(boolean useForeignKeys) whether to use foreign keys or matching column names for discovering table relations
static java.lang.String	typeToString(int joinType) returns a string representation of the join type

Methods inherited from class proper.database.ExecutorObject

getExecutor, getLastException, getLastStatement

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

NATURAL_JOIN

public static final int NATURAL_JOIN

the default join

See Also:

Constant Field Values

LEFTOUTER_JOIN

public static final int LEFTOUTER_JOIN

the left outer join

See Also:

Constant Field Values

RIGHTOUTER_JOIN

public static final int RIGHTOUTER_JOIN

the right outer join

See Also:

Constant Field Values

BESTOUTER_JOIN

public static final int BESTOUTER_JOIN

the best possible join (left outer or right outer)

See Also:

Constant Field Values

JOIN_PREFIX

public static final java.lang.String JOIN_PREFIX

prefix (+ number from ID) for ambigious columns in a join

See Also:

Constant Field Values

Constructor Detail

Joiner

public Joiner(Connector conn)

initializes the object

Method Detail

typeToString

public static java.lang.String typeToString(int joinType)

returns a string representation of the join type

parseType

public static int parseType(java.lang.String type)

parses the given string to get the type from, it also takes the short forms like 'leftouter', 'rightouter', 'bestouter' and 'natural'. it defaults to BESTOUTER_JOIN

See Also:

typeToString(int)

setJoinType

public void setJoinType(int joinType)

sets the join type

getJoinType

public int getJoinType()

returns the current join type

setInsteadOfNull

public void setInsteadOfNull(java.lang.String insteadOfNull)

sets the values (commalist of the format "name=value") to be used instead of NULL in the columns of a OUTER JOIN where NULL was inserted

getInsteadOfNull

public java.lang.String getInsteadOfNull()

returns the values (commalist of the format "name=value") to be used instead of NULL in the columns of a OUTER JOIN where NULL was inserted

setUseForeignKeys

public void setUseForeignKeys(boolean useForeignKeys)

whether to use foreign keys or matching column names for discovering table relations

Parameters:

useForeignKeys - whether to use foreign keys or matching column names

getUseForeignKeys

public boolean getUseForeignKeys()

returns whether foreign keys are used for discovering relations between tables or only matching column names

Returns:

whether foreign keys or matching column names are used for discovering table relations

ambigiousColumns

public java.util.HashSet ambigiousColumns(java.lang.String table1,
                                          java.lang.String table2,
                                          java.util.Vector joinColumns)

fills a hashset with the names of ambigious columns (actually instances of the class Column, not just the names), i.e. columns that appear in the first AND the second table

Parameters:

table1 - the first table in the join

table2 - the second table in the join

joinColumns - the fields in the select statement

See Also:

Column

createJoin

public java.lang.String createJoin(java.lang.String table1,
                                   java.lang.String table2,
                                   java.util.Vector joinColumns,
                                   int type)

creates the join statement with the given values

Parameters:

table1 - the first table in the join

table2 - the second table in the join

joinColumns - the columns to do the join over

type - the type of join to perform

createJoin

public java.lang.String createJoin(java.lang.String table1,
                                   java.lang.String prefix1,
                                   java.lang.String table2,
                                   java.lang.String prefix2,
                                   java.util.Vector joinColumns,
                                   int type)

creates the join statement with the given values, if no prefixes are given then there's one added automatically for ambigious columns

Parameters:

table1 - the first table in the join

prefix1 - the prefix for the the first table

table2 - the second table in the join

prefix2 - the prefix for the the second table

joinColumns - the columns to do the join over

type - the type of join to perform

createJoin

public java.lang.String createJoin(java.lang.String table1,
                                   java.lang.String prefix1,
                                   java.util.Vector cols1,
                                   java.lang.String table2,
                                   java.lang.String prefix2,
                                   java.util.Vector cols2,
                                   java.util.Vector joinColumns,
                                   int type)

creates the join statement with the given values, if no prefixes are given then there's one added automatically for ambigious columns

Parameters:

table1 - the first table in the join

prefix1 - the prefix for the the first table

cols1 - the columns of the first table to use

table2 - the second table in the join

prefix2 - the prefix for the the second table

cols2 - the columns of the second table to use

joinColumns - the column pairs to do the join over (Join)

type - the type of join to perform

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

join

public boolean join(java.lang.String table1,
                    java.lang.String table2,
                    java.lang.String resultTable)

joins the two tables over all common columns, not just indexes

Parameters:

table1 - the first table in the join

table2 - the second table in the join

Returns:

returns TRUE if the join succeeds

join

public boolean join(java.lang.String table1,
                    java.lang.String table2,
                    java.util.Vector joinColumns,
                    java.lang.String resultTable)

joins the two tables over the given columns

Parameters:

table1 - the first table in the join

table2 - the second table in the join

joinColumns - the column to use in the join

Returns:

returns TRUE if the join succeeds

join

public boolean join(java.lang.String table1,
                    java.lang.String prefix1,
                    java.lang.String table2,
                    java.lang.String prefix2,
                    java.util.Vector joinColumns,
                    java.lang.String resultTable)

joins the two tables over the given columns

Parameters:

table1 - the first table in the join

prefix1 - the prefix for the columns of the first table

table2 - the second table in the join

prefix2 - the prefix for the columns of the second table

joinColumns - the column to use in the join

Returns:

returns TRUE if the join succeeds

join

public boolean join(java.lang.String table1,
                    java.lang.String table2,
                    java.util.Vector joinColumns,
                    java.lang.String resultTable,
                    java.lang.String classField)

joins the two tables over the given columns

Parameters:

table1 - the first table in the join

table2 - the second table in the join

joinColumns - the column to use in the join

classField - the column to check not to loose any instances

Returns:

returns TRUE if the join succeeds

join

public boolean join(java.lang.String table1,
                    java.lang.String prefix1,
                    java.lang.String table2,
                    java.lang.String prefix2,
                    java.util.Vector joinColumns,
                    java.lang.String resultTable,
                    java.lang.String classField)

joins the two tables over the given columns, prefixes have to be given for both tables, otherwise they are ignored

Parameters:

table1 - the first table in the join

prefix1 - the prefix for the columns of the first table

table2 - the second table in the join

prefix2 - the prefix for the columns of the second table

joinColumns - the column to use in the join

classField - the column to check not to loose any instances

Returns:

returns TRUE if the join succeeds

join

public boolean join(java.lang.String table1,
                    java.lang.String prefix1,
                    java.util.Vector cols1,
                    java.lang.String table2,
                    java.lang.String prefix2,
                    java.util.Vector cols2,
                    java.util.Vector joinColumns,
                    java.lang.String resultTable,
                    java.lang.String classField)

joins the two tables over the given columns, prefixes have to be given for both tables, otherwise they are ignored

Parameters:

table1 - the first table in the join

prefix1 - the prefix for the columns of the first table

cols1 - the columns to use from the first table

table2 - the second table in the join

prefix2 - the prefix for the columns of the second table

cols2 - the columns to use from the second table

joinColumns - the column to use in the join

classField - the column to check not to loose any instances

Returns:

returns TRUE if the join succeeds

determineBestJoin

public int determineBestJoin(java.lang.String table1,
                             java.lang.String table2,
                             java.util.Vector joinColumns)

determines the best join to perform over the two given tables and columns

getClassCount

public int getClassCount(java.lang.String table1,
                         java.lang.String table2,
                         java.util.Vector joinColumns,
                         java.lang.String classField,
                         int joinType)

returns the number of classes resulting from the given join

getRowsWithClassCount

public int getRowsWithClassCount(java.lang.String table1,
                                 java.lang.String table2,
                                 java.util.Vector joinColumns,
                                 java.lang.String classField,
                                 int joinType)

returns the number of rows with a class label resulting from the given join

determineBestJoin

public int determineBestJoin(java.lang.String table1,
                             java.lang.String table2,
                             java.util.Vector joinColumns,
                             java.lang.String classField)

determines the best join to perform over the two given tables and columns, where it takes into account not to loose any classes, if the class field is given

determineColumns

public java.util.Vector determineColumns(java.lang.String table1,
                                         java.lang.String table2)

determines the columns to use in the join over all fields, not just indexes (the returned vector contains instances of "Table")

Parameters:

table1 - the first table in the join

table2 - the second table in the join

Returns:

the common column names

See Also:

Table

determineColumns

public java.util.Vector determineColumns(java.lang.String table1,
                                         boolean onlyIndexes1,
                                         java.lang.String table2,
                                         boolean onlyIndexes2)

determines the columns to use in the join (the returned vector contains instances of "Join").
Note: does not work with tables that have several foreign keys pointing to the same table (e.g. columns "husband" and "wife" both point to "person"). In this case it only grabs the first one.

Parameters:

table1 - the first table in the join

onlyIndexes1 - whether to use only the indexes from the 1. table

table2 - the second table in the join

onlyIndexes2 - whether to use only the indexes from the 2. table

Returns:

the common column names (stored in Join objects)

See Also:

Table