Faktor-IPS Tool for Product and Enum Info in Relational Databases

A tool for the creation of SQL scripts which create and fill database tables with product and enumeration details from a Faktor-IPS repository.

Usage

The tool is meant to be run on the command line and is configured by a JSON file.

How to get it

Download the JAR with maven:

        <groupId>org.faktorips.productinfo-db</groupId>
        <artifactId>productinfo-db-tool</artifactId>
        <version>20.12</version>

It depends on a matching version (20.12.0) of the Faktor-IPS runtime. The Product data must also be created with Faktor-IPS 20.12.0 or newer.

Command Line

To run the tool, you need Java 8 or newer. Make sure the Faktor-IPS repositories you want to export and the productinfo-db-tool.jar are on the classpath and provide the path to the configuration file as a parameter. Several SQL files will be written in the same location as the configuration file, using the configuration file’s name and suffixes to indicate their use: “<config>_drop.sql”, “<config>_create.sql” and “<config>_insert.sql”.

java -cp "path/to/product.jar;path/to/model.jar;path/to/productinfo-db-tool.jar" \
org.faktorips.productinfodb.tool.ProductInfoSqlCreator\
path/to/productinfo.json

Configuration

The configuration file uses the JSON format and contains the following keys:

  • repository : the path to a table-of-contents file (which must be on the classpath) and optionally one or more references repositories (for example a line-of-business independent base product or model repository)

  • datatypeMapping : a mapping of Java classes to database column definitions; superclasses like java.lang.Number may be used to assign the same data type to all subclasses

  • drop : a template string for a database table drop statement; must contain the replacement parameter ${tableName}

  • create : a template string for a database table create statement; must contain the replacement parameters ${tableName} and ${columns}

  • insert : a template string for a database table insert statement; must contain the replacement parameters ${tableName} and ${values} (optional; the default is INSERT INTO ${tableName} VALUES(${values});)

  • tableNames : defines how table names are generated:

    • prefixProduct : the prefix prepended to all product tables

    • prefixEnum : the prefix prepended to all enum tables

    • maxLength : defines the maximum table name length, defaults to 128 characters

  • columnNames : defines how column names are generated:

    • maxLength : defines the maximum column name length, defaults to 128 characters

  • exclude : a list of class names of enums that should not be exported

The template strings are used to create the individual statements for each table and should be specific to the syntax of your database.
Example:
{
  "repository": {
    "toc": "org/faktorips/productinfodb/test/products/internal/faktorips-repository-toc.xml",
    "referencedRepositories": [
      {
        "toc": "org/faktorips/productinfodb/test/model/internal/faktorips-repository-toc.xml"
      }
    ]
  },
  "datatypeMapping": {
    "java.lang.String": "VARCHAR(255)",
    "java.lang.Boolean": "BOOLEAN",
    "java.lang.Number": "DECIMAL(20, 2)",
    "java.lang.Integer": "INT",
    "java.time.LocalDate": "DATE",
    "java.time.LocalTime": "TIME",
    "java.time.LocalDateTime": "TIMESTAMP"
  },
  "drop": "DROP TABLE IF EXISTS ${tableName} CASCADE;",
  "create": "CREATE TABLE ${tableName}(${columns});",
  "tableNames": {
    "prefixProduct": "PROD_",
    "prefixEnum": "ENUM_",
    "maxLength": 25
  },
  "columnNames": {
    "maxLength": 20
  },
  "exclude": [
    "org.faktorips.productinfodb.test.model.enums.ExcludedEnum"
  ]
}

Annotations

The tool takes all products from the configured repository and looks for their product component types. If it finds an annotation @IpsProductInfoTable on the class, a table will be created for that class. The name of the table can be given as the value of the annotation. If no name is given, a name is created from the class name, converting from CamelCase to ALL_UPPER_CASE_WITH_UNDERSCORES (ProductPart → PRODUCT_PART), optionally with a prefix if configured. All subclasses are written in the same table, unless they are themselves annotated with @IpsProductInfoTable - only then may they add their own attributes (see below).

@IpsProductCmptType(name = "base.Product")
@IpsAttributes({ "intAttribute", "decimalAttribute", "dateAttribute", "timeAttribute", "dateTimeAttribute", "notMapped",
        "superAttribute", "booleanAttribute", "superAbstractEnumAttribute", "multiValueEnum", "multiValueString",
        "multiValueInteger" })
@IpsConfigures(Policy.class)
@IpsDocumented(bundleName = "org.faktorips.productinfodb.test.model.model-label-and-descriptions", defaultLocale = "en")
@IpsProductInfoTable(Product.TABLE_NAME)
public class Product extends AbstractProduct {

In addition, all non-abstract enum classes from the repository are also exported to separate tables. @IpsProductInfoTable annotations can be used to specify table names but are not required for enums. To exclude specific enums from the data base, use the exclude configuration.

For each annotated product class, one column named ID is created for the runtime ID. For enums, the columns ID and NAME are created for their default identifier and display name attributes (these attributes must not be annotated with @IpsProductInfoTableColumn and will always use these fixed column names). Further columns can be defined by annotating the getter methods for product attributes with @IpsProductInfoTableColumn. As mentioned before, the name for the column can be given as an argument to the annotation, or be derived from the attribute’s name. Annotated attributes from superclasses are included, but it is not possible to add further attributes in subclasses (unless the subclass defines its own table).

    /**
     * Returns the value of intAttribute.
     *
     * @generated
     */
    @IpsAttribute(name = "intAttribute", kind = AttributeKind.CONSTANT, valueSetKind = ValueSetKind.AllValues)
    @IpsProductInfoTableColumn
    @Generated
    public int getIntAttribute() {
        return intAttribute;
    }

    /**
     * Returns the value of dateAttribute.
     *
     * @generated
     */
    @IpsAttribute(name = "dateAttribute", kind = AttributeKind.CONSTANT, valueSetKind = ValueSetKind.AllValues)
    @IpsProductInfoTableColumn("DATE")
    @Generated
    public LocalDate getDateAttribute() {
        return dateAttribute;
    }
To configure Faktor-IPS so that these annotations are retained when the code generator runs, add them to the "Retain annotations" code generator property: <Property name="retainAnnotations" value="IpsProductInfoTable;IpsProductInfoTableColumn"/>

Enums as Product Attributes

For attributes that specify an enum as type, a foreign key constraint will be automatically created on the product component type table, provided that the attribute is annotated as @IpsProductInfoTableColumn and both classes are annotated as @IpsProductInfoTable. This allows for efficient join operations.

SELECT * FROM PRODUCTS LEFT JOIN ENUMS ON PRODUCTS.ENUM_ATTRIBUTE=ENUMS.ID;

API

The ProductInfoDbConfig also provides a constructor using a predetermined IRuntimeRepository. You can use this to integrate the ProductInfoSqlCreator into your own tools or pipeline.

    public ProductInfoDbConfig(IRuntimeRepository repository, Map<Class<?>, String> datatypeMapping,
            String dropStatementTemplate, String createStatementTemplate, String insertStatementTemplate,
            TableNameConfig tableNames, ColumnNameConfig columnNames, List<EnumType> excludedEnums) {