<<  Overview

Storage Mode SQL: Create Database

For every Application in InnoList you need a separate database in a database management system (DBMS).

But the creation of a database is quick and easy. This page assists you in doing this, for every Database Management System the procedure is slightly different.

Please use the encoding UTF-8 for databases used by InnoList. Further down in this text there is a description for which database tables are created by InnoList in a database.

A precondition is of course a installed Database Management System.

Create Database in MySQL/MariaDB

The procedure for MySQL and MariaDB is the same.

The easiest way to create a database is with the console, also shipped with the databases
mysql (Linux/Mac) or mysql.exe (Windows).

The installation of MySQL/MariaDB contains a subdirectory bin/, it contains:

bin/mysqld or bin/mysqld.exe Starts MySQL/MariaDB
bin/mysql or bin/mysql.exe Console for execution of commands (also 'create database')
The console also needs the login information for the user that is allowed to create databases (Default: root).

Using
bin/mysql -u root
or
bin/mysql -u root -p

you start the console and log in as the user root. The Parameter -p is necessary if a password is set for the user. In this case you will be asked for the password.

Now using this command you can create a database named datenbank1:

mysql> create database datenbank1;

The creation of the database is finished. If InnoList is accessing the database with a different database user, the access/right to add tables for the database needs to be granted to the database user.

Create Database in PostgreSQL

If PostgreSQL is executed, pgAdmin (https://www.pgadmin.org) is a very helpful Management Tool for the next steps.

During installation a user in PostgreSQL should have been created, if not this can also be done in pgAdmin.

Beside a user you need a Database in PostgreSQL. For this select the server on the left and the node for the databases. Using right mouse button click a new database can be created there.

Give a name to the database and choose a user that shall be allowed to access the database.

As encoding you should use UTF-8.

By creating the database the configuration of PostgreSQL is finished.

Create Database in Microsoft SQL Server

Important: Please also consider the description for the Installation/Configuration of Microsoft SQL Server
(TCP/IP-Access needs to be enabled).

For managing Microsoft SQL Server we suggest to install Microsoft SQL Server Management Studio. This is a software that is provided by Microsoft free of charge.

It allows the comfortable modification of the contents of Microsoft SQL Server. After the login dialog the Object Explorer is opened, click right on Databases and create a new database.

For the creation of the database no special settings are necessary, the default settings can be used.

As soon as the database is created in Microsoft SQL Server Management Studio, it is usable by InnoList.

Description of database tables

Now follows a description, which database tables for the system are created by InnoList and what contents they have.

Name of Table Information Stored
s_access_history Opened/Changed recently
s_attachment Assigment of attachments (located in file system) to records
s_conditional_colors Conditional Formatting
s_db_lock Locking-Table for database changes (Liquibase)
s_db_update Update-Table for database modifications (Liquibase)
s_history Change History
s_licenses License Usage
s_navigation Navigation Items
s_notification System Notifications
s_order_info Order of records
s_show_details Settings for Detail Views
s_show_list Settings for Tables Views
s_user_actions User Actions
s_user_project_settings User Project Settings
s_user_role Roles (Rights Management)
s_userpref User Settings
s_users Users (Rights Management)
s_usersetting Annotations, Comments, Coloring
s_view Views

In InnoList created "tables" are also created as tables in the database.
The technical name in InnoList is used, if this is not possible, a prefix X_ is added.

SQL Data Types for Field Types

According to the field type specific table columns are created in the database tables. The following summary shows the data types used for the field types.

Field Type SQL Data Type
Text (Single Line)
Text (Multi Line)
Link
Multi Selection (Checkboxes)
Multi Selection (List)
Text List
Image/File Upload
Images/Files Collection
Text
Selection (Dropdown)
Selection (Radio Buttons)
Selection (Buttons)
Icon
Varchar(500)
Date
Time
Date
Rating
Slider
Float/Double
Checkbox Boolean
Comments
Text/Link (replaced)
Heading/Text
ID-Display
Changed (auto)
Created (auto)
Deleted (auto)
<None>


Related Links