Package net.pricefx.formulaengine

Class 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 Detail

      • objService

        protected final PersistedObjectService objService

      • schemaService

        protected final DMSchemaService schemaService

    • Method Detail

      • close

        public void close()

      • 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

      • 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)

      • parseQuery

        protected Query parseQuery​(String queryString)

      • loadRows

        public int 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,
                     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