Data Tables in the Designer

Description

LogicNets allows you to associate data to any aspect of your data model. While data and data tables add to the functionality, there are many important concepts when dealing with data in the system, including the following:

• Connecting to tables/views in the Designer
• Defining table data
• Database sources
• Default (onstart) data
• Data migration on upgrade
• Database sources, aliases, and data migration
• Viewing table data in the Designer (separate article)

Data Source

Most applications created using LogicNets make use of data tables to display and store data, and LogicNets provides database parts that allow you to read data from and write data to your data tables (databases). Before you can put data into a table or get data from a table, however, you first have to define that table in your project. You have to add the definitions of the database table or data view to your project, and these go in your projects in the resources/data folder. This is your data source.

Defining Table Data

You add your data table definitions/data views to your project in one of two ways:

• Create a new table/view in the resources/data folder.
  • WARNING: Avoid creating table names with the same name as system tables, such as cases, project, media, topic, roles, package, template (full list can be seen here).
• Import an existing table/view into the resources/data folder.

You can create or import tables/views from multiple databases. However, you first need to configure the database in your system settings before it becomes visible as a database source in the dropdown box of the table designer.

Database Sources

By default, LogicNets supports the following database sources:

Source	Description
Read-only resources (current package scope)	Tables defined as Read-only resources are private and part of the package itself. At runtime these tables are read-only and cannot be modified. Typically, tables that are Read-only resources are look-up tables, which are created and populated when you design your application. As an example, these tables could contain a list of days of the week.
Package Data (current package scope) (Release 7.4 and up)	Tables defined as Package Data are private to the package. Unlike Read-only resources, these tables can be modified or written to during runtime, and all data is preserved when the package updated or reinstalled. Typically, Package Data tables are used to store package-specific data, like configurable user settings, results, or user answers.
Application Data (application scope) (Release 7.4 and up)	Application Data (application scope) data is data that is stored in an application-package-specific database. (An application package is the package that is started, an application package is the most derived package in a session). All packages in a session can define data tables that are stored in an application-package-specific database; for example, a framework can define a data table in this source to store data that is only shared between sessions that are started with the same application package. The data in this source is not touched when the system is updated.
Session (Session scope)	Tables defined in Session are private to the package and session. Namely, each session has its unique session database, in which these tables will be created. Typically, tables in Session are used for storing temporary data.
Data (company scope)	Tables defined as Data are accessible by all packages in the same company. The data is preserved during installation and upgrade.

Default Records

Each time a user starts an application, the LogicNets runtime checks for the existence of defined tables in the connected database sources. When a table does not, the system automatically creates the tables according to the definitions in the LogicNets project.

When the system first creates the table it is empty; however, starting with Release 7.4, it is possible to define data records that the system puts in the tables as soon as it creates the tables. These are default records. The system does not update the default records in the data source when default records are updated, deleted, or added in the Designer.

The one exception for these default records is for tables that are Read-only resources. This type of table does not support default data records. These tables are populated during the design of an application, so no defaults are applicable.

Updating Table Definitions and Data Migration with Upgrades

When you update a package, the database schema of the tables/views is automatically updated to the latest table definition. New columns are added, old columns are deleted, and views are recreated. The system also updates the default records accordingly.

Additionally, when you install a newer package of an application with the same category, the system copies the data from the application and package data databases of the existing package to those of the new package.

LogicNets Table Name vs. Database Table Name

The name of the table/view definition in your LogicNets project must be unique and is the logical name for the table/view in the database. By default, the table/view in the database has the same name, but it is possible to specify a different name, for example, in the following situations:

1. When the table/view exists in an external database management system. Typically, enterprise databases use namespaces or other structures to organize the tables/views. In such cases, the real table name may need to be prefixed by the namespace.
2. The logical table name is an alias for tables with different names in different databases.

External Database Sources

By default, LogicNets stores all data in SQLITE databases in the local installation. However, it is also possible for you to connect the application to external database management systems, such as MS SQL and Oracle. These connections must be configured by system administrators with rights on the server to create an ODBC configuration in the server operation system (in Windows via ODBC Data Source Administrator (32-bit) or in CentOS via unixODBC configuration). These administrators must also have the rights required to configure the ODBC connection via the LogicNets SystemConfiguration function.

IMPORTANT: In contrast to local SQLITE databases, LogicNets does not automatically create tables and views in external database management systems. This is a manual task for the database administrator since it may involve database access rights that are not given to the LogicNets application.

Database Sources and Aliases

You can access a database by using a database source, which is a text string that tells the LogicNets runtime how to access the database. A database source for external databases is an ODBC connection string prefaced with odbc:. For example: odbc:DRIVER={MySQL ODBC 8.0 ANSI Driver}; SERVER=localhost; DATABASE=mydb; UID=userid; PASSWORD=userpw.

LogicNets the following sources and aliases (predefined sources) for accessing the standard LogicNets databases:

Source/Alias	Description
<package-name>	This is used to access the read-only resources of a specific package.
package/<package-name>	Idem to <package-name>
data/packages/data	This is used to access the package-data-database of the current package in which the current logicnet is located.
data/packages/application/data	Use this to access the package-data-database of the current application package.
data/company/packages/<package-name>/data	Use this to access the package-data-database of <package-name>.
session	This is used to access the session database of the current running session.
data	Use this to access the company shared data database.
application	This is used to access the read-only resources of the current application package.
framework	Use this to access the read-only resources of the current framework.
parent	Use this to access the read-only resources of the parent package of the current active package in which the current logicnet is located.
package	This is used to access the read-only resources of the current active package in which the current logicnet is located.
Idem to "package"
:<library-alias>	This is used for accessing the read-only resources of the library of the current active package with the specific alias.

There are also some LogicNets internal aliases that are only used by LogicNets system packages and the LogicNets runtime.

In most cases the database source does not need to be set in the part parameters. The system retrieves that database source from the table definition in the project/package. Some parts do support a source (or db) parameter.