Databases

K8 Web Kit reads and writes the data through PDO. Besides the standard database MySQL / MariaDB the data of an element can also be in MS SQL Server or PostgreSQL. The database is selected per data definition by a connector. This page describes the databases, the connector configuration, what is different and how to write SQL which is easy to move.

Supported databases
Connectors
Connector in the data definition
What is different
Quoting of names: backticks and double quotes
Character set and collation
Column types
Writing SQL in a data definition
Limits when you use several databases
Moving an app to another database

Supported databases

Database	Connector `type`	PHP extension	Use
MySQL / MariaDB	`mysql`	`pdo_mysql`	Standard database. Contains the K8 system tables (login, rights, menu, data definitions, uploads, languages). All elements of the framework use it.
MS SQL Server	`sqlsrv`	`pdo_sqlsrv` and the Microsoft ODBC driver	Data of other systems, e.g. the ERP databases of the page Sage 100.
PostgreSQL	`pgsql` (also `postgre`, `postgres`)	`pdo_pgsql`	Data of an app. Tested with all functions of the duty roster app (list, form, master detail, upload, foreign keys check).

Rule

The system tables of K8 Web Kit are always in the default connector (k8db, MySQL / MariaDB). Only this database is checked at the first connect for the table k8login ("not prepared for K8 Web Kit").
The tables of your own elements can be in every connector. A relation between a table in the other database and the system tables (e.g. the user userid) is not checked by the database, see Limits.
A database which is not listed here is not supported. Oracle (type oracle) is in the code but not tested; it is only part of the table of the column types.

Connectors

The connectors are defined in config/_init.php in $GLOBALS['domain_connectors']. The name of the connector is free. The connector named by $GLOBALS['config']['defaultconnector'] is used for all data definitions without own connector.

parameter

Key	Meaning
`server`	host name or IP address; SQL Server: `HOST\INSTANCE` for a named instance
`port`	optional (MySQL 3306, PostgreSQL 5432, SQL Server 1433)
`database`	name of the database
`username`, `password`	the database user; it gets its rights from the database server. Use a user with only the rights the app needs, not an administrator (`sa`, `root`) except on a test system.
`charset`	MySQL only, default `utf8`; use `utf8mb4` for new databases
`trustservercertificate`	SQL Server only, default `true` (accepts a self signed certificate of the server). Set `false` if the server has a certificate of a CA.

rights_default (optional)

Rights which replace the rights of every data definition of this connector. It is used when domain_rightmode is 4 (Sage mandant), see the page Sage 100.

Connector in the data definition

A data definition uses another database with the property connector (name and type of the connector). Without it the default connector is used.

The record of the data definition (k8datadefinitions) has the field connector too; the button Create datadefID reads the table structure from this database. The tables childs[] and the detail elements get their own connector (the same or another).

Also in the connector

The connection is opened once per request and connector; two data definitions of the same connector share it.
A foreign key of the backend is checked in the database of the referenced data definition, see the doc foreignkeys.
The page Admin > Foreign keys check reads the tables of every connector which is used.

What is different

The framework takes care of the following points. You only need to know them when you write own SQL or own PHP.

	MySQL / MariaDB	MS SQL Server	PostgreSQL
Quoting of names	`name`	`[name]`	`"name"`
Upper / lower case	not significant	not significant (default collation)	a name without quotes is folded to lower case. A column `ID`, `creatorID` or `companyID` must be quoted: `w."ID"`. The framework quotes the columns it generates; in your own SQL you do it.
Paging	`LIMIT n OFFSET m`	`OFFSET m ROWS FETCH NEXT n ROWS ONLY` (needs `ORDER BY`; the framework adds the key)	`LIMIT n OFFSET m`
New ID after insert	`AUTO_INCREMENT`	`IDENTITY`	identity / serial column; the framework asks the catalog for the sequence
Date format	-	the session gets `SET DATEFORMAT ymd`, so `YYYY-MM-DD` works with every server language	-
Column types	the types are mapped to the field types of K8 Form (text, number, date, datetime, time, textarea)	same; `TIMESTAMP` (row version) is skipped	same
Current time	`NOW()`	`CAST(GETDATE() AS datetime2(0))`	`NOW()::timestamp(0)`

Quoting of names: backticks and double quotes

A name which is quoted is taken exactly as written. The quote character depends on the database:

MySQL / MariaDB: backticks, `ID`. A name is not case sensitive, so quotes are only needed for reserved words and special characters. A double quote is a text ("ID" is the string ID, not the column): WHERE "ID"=5 does not fail but gives a wrong result.
SQL Server: square brackets, [ID] (double quotes work too, because the driver sets QuotedId).
PostgreSQL: double quotes, "ID". A name without quotes is converted to lower case. A column which has upper case letters in its name (ID, creatorID, companyID) is stored with them only if it was created with quotes, and from then on it has to be quoted every time: w."ID". w.ID means w.id and fails with column w.id does not exist.

Recommendation for PostgreSQL tables

Create the columns which K8 Web Kit names with capitals (ID, creatorID, companyID, clientID) with quotes, as in MySQL: "ID" integer GENERATED BY DEFAULT AS IDENTITY.
Write all other columns in lower case (employeeid, locationid, datefrom). Then only these few columns need quotes in your own SQL.

Who quotes?

The framework quotes the names in the SQL it builds: column lists, the key, ORDER BY, filters, INSERT / UPDATE / DELETE, and the result names of sql_additionals.select (as "<key>", so a key like employeeName keeps its capitals).
You quote in your own SQL: sql_additionals (expressions and on), sql_derived, sql_statement, searchcolumn and the SQL in PHP files.

In a JSON file the double quotes are escaped: "on": "e.\"ID\"=dpshift.employeeid".

In PHP use $this->dbclass->backtick('ID') (in the data class) or gsbacktick('ID',$type): the quote characters of the database of the connector are set automatically (the name backtick is historical).

The same condition in the three databases

Character set and collation

K8 Web Kit and the browser work with UTF-8. The database has to store the text in a way which keeps all characters. The settings are different in every database.

	MySQL / MariaDB	SQL Server	PostgreSQL
Connection	Connector parameter `charset`, default `utf8` (this is `utf8mb3`, 3 bytes per character: no emoji). Set `"charset"=>"utf8mb4"`.	The driver `pdo_sqlsrv` sends and reads UTF-8 (its default); the framework does not change it. There is no `charset` parameter.	The client encoding is by default the encoding of the database. No parameter.
Where the text is stored	Per database, table and column: character set and collation. Use `utf8mb4` (the K8 tables and the duty roster tables use it).	Depends on the column type and the collation: `nvarchar`, `nchar`, `ntext` store Unicode. `varchar`, `char`, `text` store text in the code page of the collation, e.g. `Latin1_General_CI_AS` = Windows-1252 ("ANSI").	Per database: `CREATE DATABASE k8test ENCODING 'UTF8'`. It can not be changed afterwards. All text types hold Unicode.
Case	The usual collations (`..._ci`) are not case sensitive: `like 'abc%'` finds `ABC`.	The usual collations (`..._CI_...`) are not case sensitive.	`like` is case sensitive: a header filter `JAPADA` does not find `japadabaDu` (checked with the table `pgtest`). In own SQL use `ilike` or `lower(col) like lower(...)`.

SQL Server with ANSI columns (varchar)

Many databases, e.g. the ERP databases, are older and use varchar with an ANSI collation. K8 Web Kit works with them:

The text is converted between the code page of the column and UTF-8 by the driver and the server. German umlauts, ß and € are in the code page Windows-1252 and are stored and read correctly.
A character which is not in the code page (e.g. Polish ł, Cyrillic, Greek, emoji) is replaced by ? when it is saved. There is no error message. The lost character can not be restored.
Use the internal type NVARCHAR for new text columns if the users may type any language: the Table definition creates nvarchar(n) in SQL Server (NVARCHAR(MAX) above 4000 characters) and NVARCHAR2 in Oracle; in MySQL and PostgreSQL it is a varchar, which holds Unicode with the character set utf8mb4 / the encoding UTF8. The type VARCHAR creates the ANSI type varchar. A column which exists already can be changed in SQL Server: ALTER TABLE t ALTER COLUMN name nvarchar(100); the Table definition never changes an nvarchar column into varchar. From SQL Server 2019 a collation with _UTF8 lets varchar keep UTF-8.
A text constant in own SQL with such characters needs the prefix N for Unicode columns: WHERE name = N'Łódź', otherwise the constant is converted to the code page of the connection first.
A join or compare of columns with different collations (e.g. a table of another database) fails with Cannot resolve the collation conflict. Add COLLATE DATABASE_DEFAULT to one side.

Look at the settings

Test after the first connect: save a record with ä ö ü ß € ł in a text field and read it again. If the ł turns into ?, the column is an ANSI column.

Column types

K8 Web Kit reads the structure of the table from the database (button Create datadefID, Table definition in Elements online) and converts the native type of every column to an internal type. The internal type decides the form field (text, number, date, date and time, text area) and the column of the list. The Table definition uses the same table in the other direction to create or change columns in the database of the connector.

Internal	MySQL / MariaDB	SQL Server	PostgreSQL	Oracle
`BOOLEAN`	`(TINYINT(1))`	`BIT`	`BOOLEAN`	`NUMBER(1)`
`TINYINT`	`TINYINT`	`TINYINT`	`—`	`NUMBER(3)`
`SMALLINT`	`SMALLINT`	`SMALLINT`	`SMALLINT`	`NUMBER(5)`
`MEDIUMINT`	`MEDIUMINT`			`NUMBER(7)`
`INT`	`INT / INTEGER`	`INT`	`INTEGER / INT`	`NUMBER(10)`
`BIGINT`	`BIGINT`	`BIGINT`	`BIGINT`	`NUMBER(19)`
`DECIMAL`	`DECIMAL(p,s)`	`DECIMAL(p,s)`	`NUMERIC(p,s)`	`NUMBER(p,s)`
`FLOAT`	`FLOAT`	`REAL`	`REAL`	`FLOAT`
`DOUBLE`	`DOUBLE`	`FLOAT(53)`	`DOUBLE PRECISION`	`BINARY_DOUBLE`
`CHAR`	`CHAR(n)`	`CHAR(n)`	`CHAR(n)`	`CHAR(n)`
`VARCHAR`	`VARCHAR(n)`	`VARCHAR(n)`	`VARCHAR(n)`	`VARCHAR2(n)`
`NVARCHAR`	`VARCHAR(n) (utf8mb4)`	`NVARCHAR(n)`	`VARCHAR(n)`	`NVARCHAR2(n)`
`TEXT`	`TEXT, MEDIUMTEXT, LONGTEXT`	`VARCHAR(MAX)`	`TEXT`	`CLOB`
`—`	`BINARY(n)`	`BINARY(n)`	`BYTEA`	`RAW(n)`
`—`	`VARBINARY(n)`	`VARBINARY(n)`	`BYTEA`	`RAW(n)`
`—`	`BLOB, LONGBLOB`	`VARBINARY(MAX)`	`BYTEA`	`BLOB`
`DATE`	`DATE`	`DATE`	`DATE`	`DATE`
`TIME`	`TIME`	`TIME`	`TIME`	`DATE`
`DATETIME`	`DATETIME`	`DATETIME2(0)`	`TIMESTAMP(0)`	`TIMESTAMP`
`DATETIMEM`	`DATETIME(3)`	`DATETIME (with milliseconds), DATETIME2`	`TIMESTAMP`	`TIMESTAMP(3)`
`TIMESTAMP`	`—`	`ROWVERSION (formerly timestamp)`	`—`	`—`
`TIMESTAMPTZ`	`TIMESTAMP`	`DATETIMEOFFSET`	`TIMESTAMP WITH TIME ZONE`	`TIMESTAMP WITH TIME ZONE`
`YEAR`	`YEAR`	`SMALLINT`	`SMALLINT`	`NUMBER(4)`
`—`	`CHAR(36) or BINARY(16)`	`UNIQUEIDENTIFIER`	`UUID`	`RAW(16) or UUID (23c+)`
`—`	`JSON`	`NVARCHAR(MAX) + JSON functions`	`JSON, JSONB`	`JSON (21c+)`
`—`	`—`	`XML`	`XML`	`XMLTYPE`

A row with — in the column Internal is a native type which K8 Web Kit does not support: it is read as an unknown type, so it can not be used as a field of a form or as a column of a list. Use it only in your own SQL (sql_additionals), e.g. cast(x as varchar).

Hints

Types which are not in the table (e.g. uuid, json, jsonb, xml, uniqueidentifier, varbinary, bytea, datetimeoffset, interval) are read as UNKNOWN. Such a column can be part of your own SQL (cast(x as varchar) in sql_additionals), but it has no internal type, so do not use it as a field of the form.
Boolean: use smallint / tinyint with 0 and 1, as the duty roster does (active). A real boolean is true / false in PostgreSQL and does not compare with =1.
Date and time: a datetime in SQL Server is rounded to .000, .003 or .007 seconds, so datetime2 is better. Values are saved and shown with seconds; the time zone of the database is not converted. timestamp with time zone (PostgreSQL) and datetimeoffset (SQL Server) are not recommended.
Money and numbers: use decimal(p,s) for amounts, not float. The list shows DECIMAL, FLOAT and DOUBLE with the formatter money and the decimals of the type.

Key column

The key is an automatic number: AUTO_INCREMENT (MySQL), IDENTITY (SQL Server), GENERATED ... AS IDENTITY or serial (PostgreSQL).
The column is marked as key (PRI) and is not written by the form.

Example: the table of the duty roster

Writing SQL in a data definition

The properties sql_additionals, sql_derived, sql_statement and the clauses of the filters are passed to the database unchanged. They are written in the SQL of the database of the connector. A data definition therefore belongs to one database; to use the same app on another database the SQL must be translated (see below).

Functions which differ

Task	MySQL / MariaDB	PostgreSQL	SQL Server
text join	`concat(a,b)`	`a \|\| b` or `concat(a,b)`	`a + b` or `concat(a,b)`
list of values in one cell	`group_concat(x ORDER BY x SEPARATOR ', ')`	`string_agg(x, ', ' ORDER BY x)`	`string_agg(x, ', ')`
format time	`date_format(d,'%H:%i')`	`to_char(d,'HH24:MI')`	`format(d,'HH:mm')`
seconds between two datetimes	`timestampdiff(SECOND,a,b)`	`extract(epoch from (b-a))`	`datediff(second,a,b)`
date of a datetime	`date(d)`	`d::date` or `cast(d as date)`	`cast(d as date)`
null replacement	`ifnull(a,b)`	`coalesce(a,b)`	`isnull(a,b)`
cast to text	`cast(a as char)`	`a::text`	`cast(a as varchar)`

Portable parts

coalesce(), cast(... as date), concat() (concat_ws() too) and round() run on all three databases.
The placeholders §userID, §roles, §clientID, §companyID and §select are replaced by the framework, in every database.
The filters of the lists (filters[]) are built by the framework for the database of the connector.
Do not use a like filter on a calculated number column in PostgreSQL (numeric ~~ unknown); use =8 or >=8.

Example: shift list of the duty roster (sql_additionals.select of dpshift)

Limits when you use several databases

No join between two databases. A list can not join a table of PostgreSQL with k8login in MySQL. Copy the value you need into your table (the duty roster keeps username in dpemployee) or read the second table in a second request.
No foreign key between two databases. The relation gets the status other database on the page Foreign keys check. Check the value in the validation of the data definition (include validate), for example that the userid exists.
No transaction over two databases. A save writes into the database of the connector of the element. The save of a master with childs[] needs the same connector for all.
Own PHP: use the PDO connection of the data class ($this->db->prepare(...) in a _validate.php or _savechildbeforedelete.php) with bound parameters; the names of the columns are written in the SQL of the connector.

Moving an app to another database

The duty roster app was developed on PostgreSQL to test the PostgreSQL support and then moved to the standard database (MySQL). The steps are the same for every pair of databases:

Create the tables in the new database (types, NOT NULL and default values, indexes). Keep the names of the tables and columns. An index with a condition (PostgreSQL: CREATE UNIQUE INDEX ... WHERE workplaceid IS NULL) does not exist in MySQL; the validation of the data definition then checks the rule.
Copy the data with the IDs, so that the relations stay right. Check the numbers of rows and some sums on both sides. Check the counter of the next ID.
Translate the SQL of the data definitions (table above): sql_additionals, sql_derived, the SQL of the PHP files for the validation. Remove the quotes of the names ("ID" becomes ID) when you move to MySQL.
Remove or change the connector in every data definition of the app (also in childs[] and the detail elements). Without it the default connector is used.
Test the lists, the forms and the validations with a test record which you delete afterwards. Test with a user of a normal role too, not only with the superuser.