net.pricefx.formulaengine

Class TableContext

Object

TableContext

public class TableContext
extends Object

This class is the central starting point for in-memory data table operations. The idea behind it is that you can load bigger chunks of data from PriceAnalyzer or PriceBuilder (or via the List-of-Maps loader really any data) into an in-memory database. From there you can then use normal database semantics (SELECT, JOINS, etc...) to easily pick the data that you really need. Obviously the purpose is to load the bigger data chunk once in the context of e.g. a list calculation then reuse it multiple times or easily combine/join different data structures.

Some notes on the inner workings and the limitations of this feature:

• The total number of rows you can store in the in-memory database is limited. Default is 100k rows, but this setting may vary. The number of tables is not limited. The counter is only decreased if you drop entire tables.
• The in-memory database hinges at the variable you get back from api.getTableContext(). So if that variable goes out of scope the database is lost. Hence for reusing put it into api.global for example.
• A database in api.global will NOT survive a variable pass-on between two passes of a list calculation (e.g. from first pass to dirty pass calculation)
• In a distributed calculation every calculation thread has its own database. These databases are not shared in any way.
• You can easily convert results of a query of the in-memory databases tables into a ResultMatrix. @see net.pricefx.server.util.Matrix2D#toResultMatrix

Some example code:

 
 def tableContext = api.getTableContext()
 
 def fields1 = ["ID":DataType.LONG,
   			  "Name":DataType.STRING,
                "Value":DataType.STRING]
 
 def fields2 = ["Name":DataType.STRING,
                "Multiplier":DataType.NUMBER]
 
 def values1 = [
 				   ["ID":1, "Name":"A", "Value":"1"],
                 ["ID":2, "Name":"B", "Value":"2"],
                 ["ID":3, "Name":"C", "Value":"3"]
               ]
 
 def values2 = [
 				   ["Name":"A", "Multiplier":0.5],
                 ["Name":"B", "Multiplier":2],
                 ["Name":"C", "Multiplier":5]
               ]
 tableContext.createTable("Table1", fields1, null)
 tableContext.loadRows("Table1",values1)
 
 tableContext.createTable("Table2", fields2, null)
 tableContext.loadRows("Table2",values2)
 
 api.trace("Tables",null,tableContext.printTablesDefinitions("%"))
 api.trace("Tables 1 Count",null,tableContext.count("Table1"))
 api.trace("Tables 2 Count",null,tableContext.count("Table2"))
 
 return tableContext.executeQuery("SELECT t1.Name, Value, Multiplier FROM Table1 t1 JOIN Table2 t2 ON (t1.Name = t2.Name)").toResultMatrix()
 
 

Field Summary

Fields

Modifier and Type	Field and Description
protected static String	DBDT_LONG
protected static int	maxRowsCount

Constructor Summary

Constructors

Constructor and Description
TableContext(FormulaEngineContext context)

Method Summary

Modifier and Type	Method and Description
void	close()
int	count(String tableName) Get the row count of a in-memory table
void	createTable(String tableName, Map<String,DataType> fields, List<String> indexFields) Creates a new in-memory table - eventually dropping an already existing table with same name - based on the passed field list No data is loaded yet.
void	createTable(String tableName, Object input) Creates a new in-memory table - eventually dropping an already existing table with same name
void	createTableFromCustomerExtension(String tableName, String cxName, Collection<String> fields) Creates a new in-memory table - eventually dropping an already existing table with same name - based on the customer master extension meta data.
void	createTableFromCustomerMaster(String tableName, Collection<String> fields) Creates a new in-memory table - eventually dropping an already existing table with same name - based on the customer master meta data.
void	createTableFromLookupTable(String tableName, Long lookupTableId, Collection<String> fields) Creates a new in-memory table - eventually dropping an already existing table with same name - based on a lookup table/ pricing parameter table
void	createTableFromProductExtension(String tableName, String pxName, Collection<String> fields) Creates a new in-memory table - eventually dropping an already existing table with same name - based on the product master extension meta data.
void	createTableFromProductMaster(String tableName, Collection<String> fields) Creates a new in-memory table - eventually dropping an already existing table with same name - based on the product master meta data.
void	createTableFromQuery(String tableName, DatamartContext.Query query) Creates a new in-memory table - eventually dropping an already existing table with same name - based on the query definition fields.
void	createTableFromQueryResult(String tableName, DatamartQueryResultImpl queryResult) Creates a new in-memory table - eventually dropping an already existing table with same name - based on the query result fields.
void	dropTable(String tableName) Drops an existing in-memory table.
Matrix2D	executeQuery(Query query)
Matrix2D	executeQuery(String queryString)
int	executeUpdate(String sql) Execute non-select queries like UPDATE,INSERT, etc
protected DataContext	getDataContext()
Schema	getDefaultSchema()
Table	getTable(String tableName)
List<Table>	getTables()
int	getTotalRows() Returns the total row count over all used in-memory tables.
void	loadRows(String tableName, AbstractProducer.ResultIterator stream) Load data into an existing in-memory table.
void	loadRows(String tableName, List<Map<String,Object>> rowsAsListOfMaps) Load data into an existing in-memory table.
void	loadRows(String tableName, StreamResults results)
Query	newQuery(String tableName)
Query	newQuery(Table table)
protected Query	parseQuery(String queryString)
String	printTablesDefinitions(String tablePattern) Returns a debug string that contains the complete schema definition for the temporary in-memory database.
Matrix2D	select(String selectSql) Deprecated.
String	toDBDataType(DataType dataType) Converts a Price f(x) datatype into a in-memory database datatype.

Methods inherited from class Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

DBDT_LONG

protected static final String DBDT_LONG

See Also:

Constant Field Values

maxRowsCount

protected static final int maxRowsCount

Constructor Detail

TableContext

public TableContext(FormulaEngineContext context)
             throws SQLException

Throws:

SQLException

Method Detail

close

public void close()
           throws SQLException

Throws:

SQLException

getTotalRows

public int getTotalRows()

Returns the total row count over all used in-memory tables. This value is used to determine if the maximum row count for storage is exceeded or not

Returns:

The row count

dropTable

public void dropTable(String tableName)
               throws SQLException

Drops an existing in-memory table. Note: Only by calling this method (and not an executeUpdate query with a DROP statement) the internal row counter is decreased.

Parameters:

tableName - The in-memory table name to drop

Throws:

SQLException

createTable

public void createTable(String tableName,
                        Object input)
                 throws SQLException

Creates a new in-memory table - eventually dropping an already existing table with same name

Parameters:

tableName - The in-memory table name

input - Accepts two types: String and DatamartQueryResultImpl. If a string is passed it needs to be a valid SELECT sql clause based on an already existing in-memory database. Basically a "CREATE TABLE x AS SELECT ..." command is called. In case of DatamartQueryResultImpl the method behaves as createTableFromQueryResult

Throws:

SQLException

createTableFromQueryResult

public void createTableFromQueryResult(String tableName,
                                       DatamartQueryResultImpl queryResult)
                                throws SQLException

Creates a new in-memory table - eventually dropping an already existing table with same name - based on the query result fields. It then immediately populates the in-memory table with the result data.

Parameters:

tableName - The in-memory table name

queryResult - The result set of a datamart query

Throws:

SQLException

createTableFromQuery

public void createTableFromQuery(String tableName,
                                 DatamartContext.Query query)

Creates a new in-memory table - eventually dropping an already existing table with same name - based on the query definition fields. No data is loaded, nor is the datamart query executed

Parameters:

tableName - The in-memory table name

query -

createTableFromProductMaster

public void createTableFromProductMaster(String tableName,
                                         Collection<String> fields)
                                  throws SQLException

Creates a new in-memory table - eventually dropping an already existing table with same name - based on the product master meta data. No data is loaded yet.

Parameters:

tableName - The in-memory table name

fields - The fields to pick form the product master, or null for all fields

Throws:

SQLException

createTableFromCustomerMaster

public void createTableFromCustomerMaster(String tableName,
                                          Collection<String> fields)
                                   throws SQLException

Creates a new in-memory table - eventually dropping an already existing table with same name - based on the customer master meta data. No data is loaded yet.

Parameters:

tableName - The in-memory table name

fields - The fields to pick form the customer master, or null for all fields

Throws:

SQLException

createTableFromProductExtension

public void createTableFromProductExtension(String tableName,
                                            String pxName,
                                            Collection<String> fields)
                                     throws SQLException

Creates a new in-memory table - eventually dropping an already existing table with same name - based on the product master extension meta data. No data is loaded yet.

Parameters:

tableName - The in-memory table name

pxName - The extension name

fields - The fields to pick form the product master extension, or null for all fields

Throws:

SQLException

createTableFromCustomerExtension

public void createTableFromCustomerExtension(String tableName,
                                             String cxName,
                                             Collection<String> fields)
                                      throws SQLException

Creates a new in-memory table - eventually dropping an already existing table with same name - based on the customer master extension meta data. No data is loaded yet.

Parameters:

tableName - The in-memory table name

cxName - The extension name

fields - The fields to pick form the customer master extension, or null for all fields

Throws:

SQLException

createTableFromLookupTable

public void createTableFromLookupTable(String tableName,
                                       Long lookupTableId,
                                       Collection<String> fields)
                                throws SQLException

Creates a new in-memory table - eventually dropping an already existing table with same name - based on a lookup table/ pricing parameter table

Parameters:

tableName - The in-memory table name

lookupTableId - The id of the lookup table

fields - The fields to pick form the customer master extension, or null for all fields

Throws:

SQLException

createTable

public void createTable(String tableName,
                        Map<String,DataType> fields,
                        List<String> indexFields)
                 throws SQLException

Creates a new in-memory table - eventually dropping an already existing table with same name - based on the passed field list No data is loaded yet.

Parameters:

tableName - The in-memory table name

fields - Mandatory. Map of fieldName => DataType of the table. Supported DataType constants are: STRING, NUMBER, DATE, BOOLEAN, DATETIME, LONG

indexFields - Optional, can be null. List of fieldNames for which an index should be created

Throws:

SQLException

select

@Deprecated
public Matrix2D select(String selectSql)
                            throws SQLException

Deprecated.

Please use executeQuery() instead

Parameters:

selectSql -

Returns:

Throws:

SQLException

count

public int count(String tableName)

Get the row count of a in-memory table

Parameters:

tableName - The in-memory table

Returns:

The total row count of that table

executeUpdate

public int executeUpdate(String sql)
                  throws SQLException

Execute non-select queries like UPDATE,INSERT, etc

Parameters:

sql - A valid SQL statement

Returns:

The row count of affected rows or 0

Throws:

SQLException

printTablesDefinitions

public String printTablesDefinitions(String tablePattern)
                              throws SQLException

Returns a debug string that contains the complete schema definition for the temporary in-memory database. Useful for developing and debugging. Do NOT use in a real production logic (i.e. comment it outonce done with debugging)

Parameters:

tablePattern - a table name pattern; must match the table name as it is stored in the database. You can use wildcards like %

Returns:

The formatted schema description

Throws:

SQLException

toDBDataType

public String toDBDataType(DataType dataType)

Converts a Price f(x) datatype into a in-memory database datatype.

Parameters:

dataType -

Returns:

getDataContext

protected DataContext getDataContext()

getDefaultSchema

public final Schema getDefaultSchema()

getTables

public List<Table> getTables()

getTable

public Table getTable(String tableName)

newQuery

public Query newQuery(Table table)
               throws SQLException

Throws:

SQLException

newQuery

public Query newQuery(String tableName)
               throws SQLException

Throws:

SQLException

parseQuery

protected Query parseQuery(String queryString)

executeQuery

public Matrix2D executeQuery(String queryString)
                      throws SQLException

Throws:

SQLException

executeQuery

public Matrix2D executeQuery(Query query)
                      throws SQLException

Throws:

SQLException

loadRows

public void loadRows(String tableName,
                     AbstractProducer.ResultIterator stream)
              throws SQLException

Load data into an existing in-memory table. This method can be used in combination with api.stream like:

 
 def pxResults = api.stream("PX", null, Filter.equal("name", "MySamplePX"))
 tableContext.loadRows("PX_T1", pxResults)
 
 

Parameters:

tableName - The target in-memory table

stream - The result of an api.stream call

Throws:

SQLException

loadRows

public void loadRows(String tableName,
                     StreamResults results)
              throws SQLException

Throws:

SQLException

loadRows

public void loadRows(String tableName,
                     List<Map<String,Object>> rowsAsListOfMaps)
              throws SQLException

Load data into an existing in-memory table. This method can be used in combination with api.find

 
 def pxResults = api.find("PX", null, Filter.equal("name", "MySamplePX"))
 tableContext.loadRows("PX_T1", pxResults)
 
 

Parameters:

tableName - The target in-memory table

rowsAsListOfMaps - The result of an api.find call or any custom list with map elements

Throws:

SQLException