Schema Upgrade Scripts
From AlfrescoWiki
Contents |
[edit] Introduction
As of 1.4.0, upgrade scripts will be executed automatically by the server when starting up against an existing database, provided that an applicable script can be located.
It is possible to execute the appropriate scripts manually prior to starting the server.
This page describes the location of existing scripts and how to add scripts for different databases. The reader should first familiarize him/herself with the configuration mechanisms available in Alfresco (New Installations).
[edit] Components
Property: <configRoot>/alfresco/repository.properties#db.schema.update Controls whether the system bootstrap should create or upgrade the database schema automatically.
Bean: <configRoot>/alfresco/bootstrap-context.xml#schemaBootstrap This bean is the first bean to execute during system bootstrap. It detects whether the server is starting against an empty schema or an existing schema and takes the appropriate action. The applyUpdateScriptPatches property contains a list of script patches that need to be applied or need to have been applied before the server bootstrap commences.
File: <configRoot>/alfresco/patch/patch-services-context.xml Any number of SchemaUpgradeScriptPatch instances may be found here. The scriptUrl property contains the location of a the script files that apply to the particular Hibernate dialect being used. The way the script file is searched for is described later.
Folder: <alfresco.war>/WEB-INF/classes/alfresco/dbscripts/create This folder contains schema post-creation scripts stored by version applicability and Hibernate Dialect or Dialect-derived fully qualified class names.
Folder: <alfresco.war>/WEB-INF/classes/alfresco/dbscripts/upgrade This folder contains schema upgrade scripts stored by version applicability and Hibernate Dialect or Dialect-derived fully qualified class names.
[edit] Manually Upgrading
Sometimes, the permissions granted to the 'alfresco' database user are not sufficient to allow
- database metadata retrieval, or
- schema updates
In this case, we recommend setting up a test environment and performing the upgrade on there. The names of the scripts are dumped out in the logs, and in V2.1, a collation of all executed statements is dumped to help with this process. The script or scripts should be tested again, but can eventually be applied directly to the production data. Turn off any schema introspection or manipulation using:
'<extension-config/alfresco/extension/custom-repository.properties
db.schema.update=false
[edit] Script Execution
The following properties control the generation and execution of SQL statements to modify the Alfresco schema and data (defaults shown):
db.schema.update=true db.schema.update.lockRetryCount=24 db.schema.update.lockRetryWaitSeconds=5
- db.schema.update=true:
When this is off, Alfresco will not generate or execute any SQL statements. This can be used to completely avoid any metadata queries between upgrades.
- db.schema.update.lockRetryCount and db.schema.update.lockRetryWaitSeconds:
If schema updates are on and some modifying SQL has been generated, then the system attempts to get a lock against the database by creating a lock table alf_bootstrap_lock. If the table already exists, the system will wait and repeat the process of metadata examination and script generation. If the lock table cannot be created after all the retry attempts have been exhausted, the system will post a message and exit:
A previous schema upgrade failed or was not completed. Revert to the original database before attempting the upgrade again.
With the DB lock table in place, the SQL statements will be executed. Failure of schema modification scripts is terminal. There is no recovery for the current data. The DB has to be restored, the source of the problem identified and the process repeated. Once the SQL statements have all be run, the lock table will be removed and a message shown:
All executed statements written to file ...
Once all the SQL statements have been executed and the lock table has been removed, the system enters the data bootstrap or patch application phase. These changes can be rolled back so no locks are required.
[edit] Script Loading
A schema script patch may be
<bean id="patch.schemaUpdateScript-V1.4-1" class="org.alfresco.repo.admin.patch.impl.SchemaUpgradeScriptPatch" parent="basePatch">
<property name="id"><value>patch.schemaUpdateScript-V1.4-1</value></property>
<property name="description"><value>patch.schemaUpgradeScript.description</value></property>
<property name="fixesFromSchema"><value>0</value></property>
<property name="fixesToSchema"><value>19</value></property>
<property name="targetSchema"><value>20</value></property>
<property name="scriptUrl">
<value>classpath:alfresco/dbscripts/upgrade/1.4/${db.script.dialect}/AlfrescoSchemaUpdate-1.4-1.sql</value>
</property>
</bean>
The administrator will normally override the Hibernate dialect to be used. The schema bootstrap utility will substitute the fully qualified name of the dialect class into the ${db.script.dialect} placeholder. In practice, some dialects are similar to each other, or the SQL required by the script will be common to several database vendors. For this reason, the schema bootstrap attempts to find a relevant script by walking up the dialect class hierarchy, terminating after trying the org.hibernate.dialect.Dialect class.
An example post-creation script called sample.sql has been included to demonstrate the search algorithm. The schemaBootstrap has the following:
<property name="postCreateScriptUrls">
<list>
<value>classpath:alfresco/dbscripts/create/1.4/${db.script.dialect}/sample.sql</value>
</list>
</property>
With the following structure on the classpath:
WEB-INF/classes/alfresco/dbscripts/create/V1.4/
org.hibernate.dialect.Dialect
sample.sql
org.hibernate.dialect.MySQLInnoDBDialect
sample.sql
The search order for dialect org.hibernate.dialect.OracleDialect is:
classpath:alfresco/dbscripts/create/1.4/org.hibernate.dialect.OracleDialect/sample.sql <NOT FOUND> classpath:alfresco/dbscripts/create/1.4/org.hibernate.dialect.Oracle9Dialect/sample.sql <NOT FOUND> classpath:alfresco/dbscripts/create/1.4/org.hibernate.dialect.Dialect/sample.sql <FOUND>
The search order for dialect org.hibernate.dialect.MySQLInnoDBDialect is:
classpath:alfresco/dbscripts/create/1.4/org.hibernate.dialect.MySQLInnoDBDialect/sample.sql <FOUND>
The same logic applies when searching for upgrade scripts.
[edit] Writing and Testing Scripts
[edit] Script Encoding and Syntax
The following rules are true for all auto-executed SQL scripts:
- All statements within the script are executed in a single transaction using the same database connection. Some databases do not support transactional modification of tables.
- Scripts must be UTF8 encoded.
- Statements must be terminated by ";" or ";(optional)". The latter indicates that the statement may fail without causing the script to stop.
- Lines starting with "--", "//" or "/*" are ignored.
- Leading and trailing spaces are stripped from all statements.
[edit] Script Locations
Once a script has been translated, it can be dropped into the Alfresco extensions location on the server or classpath in order to be picked up. More details of the extension locations can be found at Repository_Configuration#Location_of_Extension_Configuration_Files.
[edit] Notes on Writing Scripts
- Pay particular attention to the statements that insert entries into the alf_applied_patch table. The patch mechanisms and checks use these entries to ensure that scripts aren't repeatedly executed.
- Please contribute any missing scripts back to Alfresco with the following info:
- Databases tested on (e.g. Oracle 10g and MySQL 5.0)
- Schemas tested against (e.g. Tested against a 1.3.1 database that was originally upgraded from 1.2.1)
- Version of Alfresco tested with (e.g. V1.4 Preview War)
