Data type limitations of different FirstSpirit supported SQL database systems

Author: isenberg
Publication Date: 10/10/2014 10:31

The FirstSpirit relational database abstraction layer offers the choice between 6 different RDBMS:

• Postgresql
• Oracle
• IBM DB2
• Microsoft SQL
• MySQL
• Apache Derby

The specific supported versions of each database system for each FirstSpirit version are given in the documentation FirstSpirit Technical Datasheet. Apache Derby is embedded in the FirstSpirit server and should be used for tests and early project developments only, not for production systems.

With the FirstSpirit project export/import function a project can be migrated from one database system to another without requiring any changes within the FirstSpirit project. Though for large FirstSpirit infrastructures with several projects and references between the projects it is recommended to decide about which database system to use before beginning before the actual implementation process and during the project architecture definition phase.

FirstSpirit templates and code using the FirstSpirit API refers to database objects via the FirstSpirit object model with its own data types. The FirstSpirit types are comparable to SQL types, but are mapped for each database system to its own SQL types. As each SQL type is managed differently by the database system, some performance variances are notifiable, especially with different sizes of the STRING type.

The limitation in row length per table of each database system should be checked against the FirstSpirit project database architecture specification, especially if many languages are used within a project as each multi-lingual FirstSpirit input component on a datasource (table) requires one column per language. The actual used row length is the sum of the byte-sizes of all rows within a table. Primary datatypes like INT, DOUBLE and LONG use 4 or 8 bytes, the FirstSpirit text data types are stored in variable types within SQL and those require about 6 to 36 bytes for their pointers as the data itself is stored outside of the internal table objects. MySQL as exception, requires 768 bytes for each FirstSpirit text object.

From a technical point of view, Postgresql compiled with 32k blocksize offers the best support for the wide tables used in FirstSpirit projects with many multi-lingual input components on datasources. Though this special Postgresql version is not available from any Linux distributor and must be compiled by the hosting service. Among the other supported SQL systems, Oracle offers the best planning stability, as it guarantees 1000 columns, not subject to any other restrictions.

The following table shows the data type mapping between FirstSpirit data types and SQL types on each system and their restrictions in field lengths. The variable length FirstSpirit type STRING with a given maximum length of n within the FirstSpirit template is mapped to different SQL types as a function of n when the table is create by FirstSpirit.

notes:

• Mysql STRING default: length n is read from property jdbc.MAXSTRINGLENGTH and Mysql type is selected according to n
• FirstSpirit STRING data type: in template forms used for CMS_INPUT_TEXT, CMS_INPUT_TEXTAREA
• FirstSpirit EDITOR data type: in template forms used for CMS_INPUT_DOM with maximum length 65535
• large limits like n MByte or n GByte usually mean n MByte - 1 Byte
• Oracle CLOB and BLOB maximum length: at least 8 TByte, can be increased with larger DB_BLOCK_SIZE to 128 TByte
• All lengths are measured in Bytes. A single character requires 1 Byte for ASCII symbols and 2 - 4 Bytes for international Umlauts and symbols when using UTF-8 encoding which is the default for FirstSpirit.

Postgresql

columns per row: 250 - 1600 depending on column types

row length: 400 GByte

field length: 1 GByte

table size: 32 TByte

rows per table: unlimited

database size: unlimited (32 TB databases exist)

FirstSpirit projects using many languages (40+) and many (40+) input components per datasource (table) might reach the maximum number of columns per row as FirstSpirit creates dedicated columns for each language of a multi-lingual input component on datasources. If restructuring the database scheme during the project design phase is no option, i.e. normalizing all wide tables with many columns to narrower tables with less columns and additional relation tables, the database blocksize can be changed to other values than the default 8192 bytes by setting parameter --with-blocksize during compiliation of the postgresql server. With using 32kByte as blocksize, the number of usable columns are quadrupled.

The actual row length in bytes of tables in an existing database can be checked with the following SQL command:

SELECT pg_column_size(tablename.*) FROM tablename

details: http://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database.3F

compiling postgresql with 32k blocksize: https://manual.limesurvey.org/Instructions_for_increasing_the_maximum_number_of_columns_in_PostgreSQL_on_Linux

Microsoft SQL

columns per row: 1024

row length: 8060 bytes

database size: 524272 TByte

FirstSpirit projects using many languages (30+) and many (30+) input components per datasource (table) might reach the maximum number of columns per row as FirstSpirit creates dedicated columns for each language of a multi-lingual input component on datasources. A solution during the project designing phase is restructuring the database scheme, i.e. normalizing all wide tables with many columns to narrower tables with less columns and additional relation tables.

details: http://msdn.microsoft.com/de-de/library/ms143432

Oracle

colums per row: 1000

row length: unlimited

rows per table: unlimited

FirstSpirit projects using many languages (30+) and many (30+) input components per datasource (table) might reach the maximum number of columns per row as FirstSpirit creates dedicated columns for each language of a multi-lingual input component on datasources. A solution during the project designing phase is restructuring the database scheme, i.e. normalizing all wide tables with many columns to narrower tables with less columns and additional relation tables.

details: http://docs.oracle.com/cd/B28359_01/server.111/b28320/limits.htm

MySQL

columns per row: max 1000, though practical limit much lower

row length:

• 8000 Bytes for all fixed length types, but including 768 bytes of each VARCHAR, TEXT, BLOB. Limit caused by storage engine InnoDB.
• 4 GByte for sum of all VARCHAR, TEXT, BLOB

This limit of 8000 Bytes can easily be reached in FirstSpirit projects with multiple languages as each language requires an additional column for each STRING or TEXT(DOM) input field! For instance, a project using 10 languages would reach that limit with only one FirstSpirit-Editor input component (DOM) in a datasource. Or a project with 5 languages and 2 editor input components. If you are limited to Mysql, a solution during the project design phase would be to use a different database scheme architecture, i.e. normalizing all wide tables with many columns to narrower tables with less columns and additional relation tables. Another solution is using Postgresql, Microsoft SQL or Oracle.

As FirstSpirit uses database transactions the storage engine InnoDB is required as default engine for the database used with FirstSpirit.

Properties for FirstSpirit Database Layer Definition:

• jdbc.MAXSTRINGLENGTH: default value 1024

defails:
http://dev.mysql.com/doc/refman/5.1/en/column-count-limit.html

http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html

http://dev.mysql.com/doc/refman/5.1/de/innodb-restrictions.html (limit of 8000 Bytes per row)

DB2

columns per row with default pagesize (4kByte): 500

columns per row with 8kByte pagesize and larger: 1012

row length: 

• 4005 Bytes with 4 kByte pagesize
• 8101 Bytes with 8 kByte pagesize
• 16293 Bytes with 16 kByte pagesize
• 32677 Bytes with 32 kByte pagesize
• FirstSpirit uses VARCHAR, CLOB and each of those objects require about 10 Bytes for their pointers within each table row for the typical object sizes used by FirstSpirit.

Multi-lingual FirstSpirit projects can easily reach the 500 columns per row limit as FirstSpirit creates dedicated columns for each language of a multi-lingual input component on datasources. A solution during the production phase of a project is, to migrate the data to a DB2 database with larger pagesize. FirstSpirit projects using many languages (30+) and many (30+) input components per datasource (table) might even reach the maximum number of 1012 columns for larger pagesizes. The FirstSpirit Manual for Administrators suggests using 32k pagesize. Another solution during the project designing phase is restructuring the database scheme, i.e. normalizing all wide tables with many columns to narrower tables with less columns and additional relation tables.

Properties for FirstSpirit Database Layer Definition:

• jdbc.MAXSTRINGLENGTH: default 1024, max 32672
• jdbc.db2.CLOBSIZE: default 16 MByte, max 2 GByte - 1. The actual limit of the used database engine must be set here.
• jdbc.db2.BLOBSIZE: default 16 MByte, max 2 GByte - 1. The actual limit of the used database engine must be set here.

details:

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0001029.html?cp=SSEPGG_9.7.0%2F2-10-8

FirstSpirit Database Documentation

Standard documentation about using databases for FirstSpirit projects:

Manual for Administrators

ADMI_DE_FirstSpirit_Administrators.pdf

section: 4.8 Database connection

Manual for Developers (Basics)

DEVB_EN_FirstSpirit_DeveloperBasics.pdf

section: 2.12 Database schemata

Technical Datasheet

TDAT_DE_FirstSpirit_TechnicalDatasheet.pdf

section: 3.5 Database

Tags: database, jdbc, limits, or-mapper, restrictions, sql, types