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

A tool for the creation of SQL scripts which create and fill database tables with product, enumeration and table 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>23.6</version>

It depends on a matching version (23.6.1.release) of the Faktor-IPS runtime. The Product data must also be created with Faktor-IPS 23.6.1.release 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

Maven

When using the command line tool with Maven, especially if the project uses Spring and therefore has lots of bundles on its classpath, it may be necessary to configure Maven to use a long classpath:

<plugin>
  <groupId>org.codehaus.mojo</groupId>
  <artifactId>exec-maven-plugin</artifactId>
  <version>3.0.0</version>
  <executions>
    <execution>
      <id>generate-productinfo</id>
      <phase>compile</phase>
      <goals>
        <goal>exec</goal>
      </goals>
    </execution>
  </executions>
  <configuration>
    <executable>java</executable>
    <longClasspath>true</longClasspath>(1)
    <arguments>
      <argument>-classpath</argument>
      <classpath />(2)
      <argument>org.faktorips.productinfodb.tool.ProductInfoSqlCreator</argument>
      <argument>path/to/productinfo.json</argument>
    </arguments>
  </configuration>
</plugin>
1 Use long classpath
2 Provide the Maven dependencies on the classpath

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

  • booleanRepresentation : a mapping of boolean values to their representation in the database; defaults to 'TRUE'/'FALSE' if not configured

  • dateFormat : a mapping of LocalDate.class and LocalTime.class to their representation in the database; defaults to '"yyyy-MM-dd" and "HH:mm:ss" if not configured

  • drop : a template string for a database table drop statement; must contain the replacement parameter ${tableName}; a CASCADE CONSTRAINTS or equivalent statement may be required for smooth deletion in your database

  • 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

    • prefixTable : the prefix prepended to all IPS-table tables

    • maxLength : defines the maximum table name length including the prefix, 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.

It is also possible to include headers and/or footers for the drop/create/insert files. To do so, instead of a simple template string, a template structure containing the property statement as well as header and/or `footer`can be configured.

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": "NUMBER(1,0)",
    "boolean": "NUMBER(1,0)",
    "java.lang.Number": "DECIMAL(20, 2)",
    "java.lang.Integer": "INT",
    "java.time.LocalDate": "DATE",
    "java.time.LocalTime": "TIME",
    "java.time.LocalDateTime": "TIMESTAMP"
  },
  "booleanRepresentation": {
    "true": "1",
    "false": "0"
  },
  "dateFormat": {
    "java.time.LocalDate": "dd-MM-YYYY",
    "java.time.LocalTime": "HH:mm"
  },
  "drop": "DROP TABLE IF EXISTS ${tableName} CASCADE CONSTRAINTS;",
  "create" : {
    "statement" : "CREATE TABLE ${tableName}(${columns});",
    "header" : "-- header comment",
    "footer" : "-- footer comment"
  },
  "tableNames": {
    "prefixProduct": "PROD_",
    "prefixEnum": "ENUM_",
    "prefixTable": "TABLE_",
    "maxLength": 30
  },
  "columnNames": {
    "maxLength": 30
  },
  "exclude": [
    "org.faktorips.productinfodb.test.model.enums.ExcludedEnum"
  ]
}

Annotations

The tool takes all products and table contents from the configured repository and looks for their respective product component types and table structures. 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 of a product component type are written in the same table, unless they are themselves annotated with @IpsProductInfoTable - only then may they add their own attributes (see below). All the table contents of an annotated table structure are written in the same table as well.

@IpsProductCmptType(name = "base.Product")
@IpsAttributes({ "intAttribute", "decimalAttribute", "dateAttribute", "timeAttribute", "dateTimeAttribute", "notMapped",
        "superAttribute", "booleanAttribute", "primitiveBooleanAttribute", "superAbstractEnumAttribute",
        "multiValueEnum", "multiValueString", "multiValueInteger" })
@IpsConfigures(Policy.class)
@IpsTableUsages({ "SingleContentTable" })
@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). For table structures, a column named TABLE_CONTENT_NAME is created to store the name of the table content. Further columns can be defined by annotating the getter methods for product attributes/table columns with @IpsProductInfoTableColumn. As mentioned before, the name for the column can be given as an argument to the annotation, or be derived from the attributes/columns 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 or columns that specify an enum as type, a foreign key constraint will be automatically created on the product component type table or table structure table. The prerequisite for this is that the attribute or column 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, Function<Boolean, String> booleanRepresentation,
            Map<Class<?>, String> dateFormat,
            Template dropStatementTemplate, Template createStatementTemplate, Template insertStatementTemplate,
            TableNameConfig tableNames, ColumnNameConfig columnNames, List<EnumType> excludedEnums) {