About managed schema in MSSQL databases
In an MSSQL database, schema defines the database structure that is used to organize tables, stored procedures, views, and other entities, to support application functionality. Schema that is delivered with the Infor portal content is called Managed Schema. Each set of managed schema delivered by the content is a named Data Store Type. The name of the data store type indicates what variety of data and/or functionality the schema will handle.
When a new MSSQL database is created for the IPF portals, there may be managed schema requirements, depending on the types of information the database will store. For example, if it will hold portal emails, tasks, or knowledge content, which are all features that require schema that is delivered with the content, then you must enable schema management for the database and specify all of the appropriate data store types for the database to handle.
Because IPF portals can be run in the cloud, and cloud users do not have access to databases, the schema delivered by data store types is applied to databases and managed by the IPF architecture. The Infor portal content versions contain the data store types with appropriate scripts to create the necessary schema on behalf of the user. The architecture knows which scripts to apply based on the data store types that are selected for the database.
This is the general process:
- Create a new MSSQL data source.
- After selecting or creating a database for the data source, click Manage Application Schema.
- Enable schema management and specify the type(s) of data to store in the database. If you are creating a repository database, you only need to select from the provided IPF repository types. If you are creating a knowledge content database, you would specify an originator and content version and then add the data store type that is in the content version.
- Activate the portal content version. This is when the scripts in the content version create the necessary schema for the specified data stores. You are prompted to apply schema to the data sources.
Updating managed schema
When a new version of the content needs to be developed, the scripts for managed schema may require changes. These changes are handled through the use of level-based and revision-based schema.
Level-based schema is used primarily for the scripts that build tables. So the level 1 schema creates a table, and as your portal is used, data is added to that table. In an updated content version, level 2 schema may add a column to a table, but because your current version has data in it, you cannot simply add all new schema, or the data would be lost. Instead, IPF uses a tracking table in the database to monitor the data store types in the database and the associated script levels that have been applied in connection with it. For level-based schema, IPF applies only the changes to managed-schema content. So IPF would add the new table column; it would not delete the entire table and add a new one. No data is lost. The tracking table retains all script levels and it records which ones have been run in your environment. With each new content version, all levels are delivered, but IPF applies only levels that have not already been applied to your database.
Revision-based schema is normally used for stored procedures. In this case, when new content versions are released, the old stored procedures can by replaced by the new versions, because no data is involved. The tracking table keeps the name of the script and version last applied. When the content is activated, all scripts are checked. If IPF finds that one was not applied or that one is not the expected revision, it gets applied. Otherwise, it is skipped.
You can manage level- and revision-based schema files in the Portal Manager under Content Development > Managed Schemas. The files can be edited and added in only content versions that are open for development. For each data store type, the list of schemas and the order in which they will be applied is displayed. You can add or remove managed schemas and change the order in which they are run.
Applying managed schema to the database
Any time content is activated for a portal, IPF checks the databases associated with the portal. If IPF detects changes to managed schemas, the user is prompted to apply schema to the data source. Before any schema changes are applied, the user is presented with a dialog for each data source and data store type, showing the list of level- and revision-based schemas and the order in which they will be applied. The user can select which ones to apply; however, generally users other than developers are not expected to inspect the individual schema scripts. The expectation is that administrative users will confirm that appropriate databases are affected and appropriate data store types are used and then will apply all of the requested schema changes.
The managed schema architecture does not support downgrading. In other words, if schema from a later content version is applied to a database, and then subsequently the database is used with an earlier version of the content that expects an earlier version of the schema, the content might not work correctly.
Similarly, if the database is used with multiple portals at the same time, the user must make sure that all portals are using compatible schemas. In practice, this means each portal should have the same content versions (although the features activated might be different).