Package net.pricefx.formulaengine

Class TableContext

Object
TableContext
All Implemented Interfaces:
AutoCloseable
public class TableContext extends Object implements AutoCloseable
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
The in-memory database is not preserved when it is persisted as a calculation result or as part of the api.global map in a CalculationFlow. Instead, when serialized, a String of the form "TableContext[m/n]" is returned with m = #tables and n = total #rows in the TableContext at that moment in time.

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 Details

    • objService

      protected final PersistedObjectService objService

    • schemaService

      protected final DMSchemaService schemaService

    • conn

      protected final Connection conn

  • Constructor Details

  • Method Details

    • isClosed

      public boolean isClosed()

    • close

      public void close()
      Specified by:
      close in interface AutoCloseable

    • 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

    • createTableFromSearch

      public int createTableFromSearch(String tableName, Search search, String entityName) throws Exception
      Throws:
      Exception

    • 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 Pricefx 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 int loadRows(String tableName, PublicGroovyAPI.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 int loadRows(String tableName, StreamResults results) throws Exception
      Throws:
      Exception

    • 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

    • toString

      public String toString()
      Overrides:
      toString in class Object