Database Profiles: Oracle, MS SQL, and MySQL

Description

In your LogicNets application you can use database parts to read data from and write data to databases. LogicNets uses the concept of virtual database tables, in which you can model your application interaction with the database. When your application triggers these interactions the LogicNets platform uses the definition of the virtual data tables to generate the SQL statements for the target database. 

Supported Database Systems

LogicNets supports integration with the following database systems: MS SQL server, Oracle, and MySQL. For each of these databases the LogicNets platform has created an overridable database profile that defines the high-level settings to control the interaction between the LogicNets platform and the database system. These settings are system-level settings that will apply to all of the LogicNets applications within your LogicNets installation.

The database profiles include the following settings:

• auto_increment: 1 | 0
  • 1: This means that LogicNets assumes that the database will create the ID for the newly inserted record, so LogicNets omits the ID column from the INSERT statement.
  • 0: This means that LogicNets needs to generate the new ID for the newly inserted record. For that, LogicNets uses the database sequence object that is associated with the given table.
• use_ln_generated_sequences: 1 | 0
  • 1: This means that LogicNets will use its own sequence objects to retrieve the IDs for the new records. Note: You must ensure that LogicNets has the correct privileges within the database schema to create sequence objects.
  • 0: This means that Logicnets will not use its own sequence objects and you must specify the names of the sequence objects to be used. You can specify these in the part editor of the database parts. 
• data-type mappings: The LogicNets platform uses two mapping tables to perform the back-and-forth translation of database-type to LogicNets data-object types.
  • The ‘ln_to_db’ mapping table defines the mapping of LogicNets data types to database types. For example, the add_records part uses this table to map the data that is to be written to the database to the database types of the target database.
  • The ‘db_to_ln’ mapping table defines the mapping of database types to LogicNets data object types. For example, when you import an external database tables into your LogicNets project, LogicNets uses this mapping to set the appropriate column types of the resulting virtual data table.
• reserved_words_mapping: The reserved_words_mapping table is used to resolve naming conflicts in cases where you have named a column in your virtual database using a reserved word in the target database. With this table the column name is mapped to another name, one that is not a reserved word. NOTE: The reserved-words mapping is case insensitive. Enter all values in the Key column in lower case.

• max_char_size: In some queries LogicNets needs to assign a maximum size for a character field that can be passed to the database. You can control this size using the max_char_size parameter.

Database Profile Override

For customizability, customers can override default database profiles at the system level or application level.

System-Level Settings

To override the database profile at the system level, you can do one of the following:

• Using the System Configuration Application: In this application your Application Administrator is able to configure the database settings. 
• Using the ‘update_settings’-part in Post-Installation Jobs: Using the ‘update_settings’ part, application modelers can model a post-installation job that programmatically configures database profile settings. Note: The use of the ‘update_settings’ part is only allowed under very specific conditions and requires a special license. Please contact LogicNets if you wish to use this part in your application.

Changes at the system level are stored to the settings.cfg file of your installation, and you can find them in that file under the ‘_session.DB_CONFIG’ section. LogicNets supports different database systems, so overrides for each must go in database-specific sections; for example, MS SQL Server settings must go into ‘_session.DB_CONFIG. mssql’, overrides for Oracle must go into ‘_session.DB_CONFIG. oracle’, and overrides for MySQL must go into ‘_session.DB_CONFIG. mysql’.

New Record ID

The following table shows the database profile settings that control the generation of the ID for newly insert records.

Notes:

• Sequences are only supported by Oracle and Microsoft SQL server databases. For MySQL databases LogicNets always assume auto-increment.
• For ORACLE, when auto-increment is set tot 1, the add_record and add_records parts are not able to return the ID of the newly added recorded, because it is not possible for LogicNets to automatically retrieve this ID from the database after the insertion of a new record.

Data Mapping

Data mapping goes into ‘data_mapping_ln_to_db’ and ‘data_type_mapping_db_to_ln’. By default, the following mappings are defined in the system:

  data_type_mapping_ln_to_db = { -- LN 2 ORACLE
              ["collection"] = "CLOB",
              ["number"] = "NUMBER",
              ["text"] =  "VARCHAR2(4000)",
              ["date"] = "DATE",
          },

  data_type_mapping_db_to_ln = { -- ORACLE 2 LN
              ["char"] = "text",
              ["varchar2"] = "text",
              ["nchar"] = "text",
              ["nvarchar2"] = "text",
              ["clob"] = "collection",
              ["long"] = "number",
              ["number"] = "number",
              ["date"] = "date",
          },

  data_type_mapping_ln_to_db = { -- LN 2 MSSQL
              ["collection"] = "NVARCHAR(MAX)",
              ["number"] = "FLOAT",
              ["text"] = "NVARCHAR(MAX)",
              ["date"] = "DATETIME",
          },

  data_type_mapping_db_to_ln = { -- MSSQL 2 LN
              ["int"] = "number",
              ["decimal"] = "number",
              ["numeric"] = "number",
              ["float"] = "number",
              ["real"] = "number",
              ["datetime"] = "date",
              ["smalldatetime"] = "date",
              ["char"] = "text",
              ["varchar"] = "text",
              ["text"] = "text",
              ["nchar"] = "text",
              ["nvarchar"] = "text",
              ["ntext"] = "text",
          },

  data_type_mapping_ln_to_db = { -- LN 2 MYSQL
              ["collection"] = "blob",
              ["number"] = "FLOAT",
              ["text"] = "varchar(4000)",
              ["date"] = "DATE",
          },

  data_type_mapping_db_to_ln = { -- MYSQL 2 LN
              ["int"] = "number",
              ["integer"] = "number",
              ["decimal"] = "number",
              ["dec"] = "number",
              ["float"] = "number",
              ["double"] = "number",
              ["double precision"] = "number",
              ["date"] = "date",
              ["datetime"] = "date",
              ["timestamp"] = "date",
              ["time"] = "date",
              ["char"] = "text",
              ["varchar"] = "text",
              ["text"] = "text",
          },

If you need to override the database mapping for “text” fields in Oracle, you can add the following to your settings file:

 _session.DB_CONFIG.oracle.data_type_mapping_ln_to_db = { ["text"] = "VARCHAR(4096)"}

Reserved Keywords

Reserved keywords go into ‘reserved_words_mapping’. By default, the following mappings are defined in the system:

  reserved_words_mapping = {
              ["comment"] = "CMT",
              ["session"] = "SES",
              ["where"] = "where",
              ["_sqltype"] = "_sqltype",
              ["keys"] = "keys",
              ["place"] = "place",
              ["type"] = "type"
          },  

For example, if you need to add an additional keyword mapping to your Oracle database configuration, you can add the following to your settings file:

_session.DB_CONFIG.oracle.reserved_words_mapping  = { ["CLUSTER"] = "CLUSTER_GROUP"}

Application-Level Settings

Database Parts

Within an application the application modeler can override the ‘auto-increment’ and ‘sequences’ settings using the part editor of the database parts. 

Auto Increment

When this parameter is unset it takes the system defaults.  By setting this parameter you can explicitly override the system defaults.

Sequence Name

When a sequence name is specified, the LogicNets-generated sequence is overridden. When auto-increment is set to 0, the system uses sequences to retrieve the a next ID for the newly inserted record. Note: When auto increment is 0 and the system-level setting ‘use_ln_generated_sequences’ is 0, the sequence name is required, otherwise the add record action results in an error.

Workflow

It is also possible to override the auto-increment and sequence settings at the application level for workflow tables. You can configure these settings on the Framework settings page of your StarterApp-framework-based project: