How to upgrade active objects columns from varchar to nvarchar in SQL Server
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Purpose
In Confluence 5.8 the Active Objects library was changed to use the nvarchar type for new columns. However when upgrading from Confluence 5.7 or earlier, existing active objects tables are not upgraded from varchar to nvarchar. See
CONF-38232
-
Getting issue details...
STATUS
for more details.
Solution
This has been fixed as an upgrade task in Confluence 5.9, but customers who want to upgrade from Confluence 5.7 to Confluence 5.8 can use the scripts provided below to manually upgrade all affected Active Objects tables.
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
The upgrade sequence is:
- Shut down Confluence 5.7
- Back up your database
- Generate queries using the scripts below for:
- foreign keys to drop and recreate
- primary keys to drop and recreate
- unique indexes to drop and recreate
- non unique indexes to drop and recreate
- altering columns from varchar to nvarchar
- Run the scripts you created in the below order (you can either run the .sql scripts, or copy the queries in them back into SQL Server Management Studio, whichever you find more convenient)
Execute
1-dropfks.sql- drop foreign key constraintsExecute
2-droppks.sql- drop primary key constraintsExecute
3-dropuniqueidx.sql- drop unique indexesExecute
4-dropnonuniqueidx.sql- drop non-unique indexesExecute
5-altercolumntype.sql- alter data type for all varchar columns in Active Objects tablesExecute
6-a- recreate non-unique indexesddnonuniqueidx.sqlExecute
7-adduniqueidx.sql- recreate unique indexesExecute
8-addpks.sql- recreate primary keys constraintsExecute
9-addfks.sql- recreate foreign keys
- Back up your database again, in case you need to roll back to this point during the upgrade
- Upgrade to Confluence 5.8 in the usual way
Foreign keys scripts generation
-- copy the first column of the results to a file called '1-dropfks.sql'
-- copy the second column of the results to a file called '9-addfks.sql'
SELECT
'ALTER TABLE ' + tab1.name + ' DROP CONSTRAINT ' + obj.name + ';',
'ALTER TABLE ' + tab1.name + ' ADD CONSTRAINT ' + obj.name + ' FOREIGN KEY ' + '('+ col1.name + ')' + ' references ' + tab2.name + '(' + col2.name + ');',
obj.name AS FK_NAME,
sch.name AS [schema_name],
tab1.name AS [table],
col1.name AS [column],
tab2.name AS [referenced_table],
col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
WHERE tab1.name like 'AO_%';
Primary keys
-- copy the first column of the results to a file called '2-droppks.sql'
-- copy the second column of the results to a file called '8-addpks.sql'
SELECT
'ALTER TABLE ' + '"' + T.Name + '"' + ' DROP CONSTRAINT ' + '"' + I.Name + '";',
'ALTER TABLE ' + T.Name + ' ADD PRIMARY KEY (' + C.Name + ');'
FROM sys.indexes I
INNER JOIN sys.index_columns IC
ON I.object_id = IC.object_id AND I.index_id = IC.index_id
INNER JOIN sys.columns C
ON IC.object_id = C.object_id and IC.column_id = C.column_id
INNER JOIN sys.tables T
ON I.object_id = T.object_id
INNER JOIN sys.types ST
ON ST.system_type_id = C.system_type_id -- join on system type to create not null on nvarchar primary keys
WHERE I.is_primary_key = 1
and T.Name like 'AO_%'
and ST.name = 'varchar'
ORDER BY T.Name, I.Name;
Unique indexes
-- copy the first column of the results to a file called '3-dropuniqueidx.sql'
-- copy the second column of the results to a file called '7-adduniqueidx.sql'
SELECT
'ALTER TABLE ' + '"' + T.Name + '"' + ' DROP CONSTRAINT ' + '"' + I.Name + '";',
'CREATE UNIQUE '
+ cast (I.type_desc as varchar(512)) collate SQL_Latin1_General_CP1_CI_AS
+ ' INDEX ' + I.Name + ' ON ' + '"' + T.Name + '"' + '(' + '"' +C.Name+'"' + ');'
FROM sys.indexes I
INNER JOIN sys.index_columns IC
ON I.object_id = IC.object_id AND I.index_id = IC.index_id
INNER JOIN sys.columns C
ON IC.object_id = C.object_id and IC.column_id = C.column_id
INNER JOIN sys.tables T
ON I.object_id = T.object_id
WHERE T.Name like 'AO_%'
and I.is_primary_key = 0
and I.is_unique = 'true'
ORDER BY T.Name, I.Name;
Non unique indexes
-- copy the first column of the results to a file called '4-dropnonuniqueidx.sql'
-- copy the second column of the results to a file called '6-addnonuniqueidx.sql'
SELECT
'DROP INDEX ' + '"' + T.Name + '"' + '.' + '"' + I.Name + '";',
'CREATE '
+ cast (I.type_desc as varchar(512)) collate SQL_Latin1_General_CP1_CI_AS
+ ' INDEX ' + I.Name + ' ON ' + '"' + T.Name + '"' + '(' + '"' +C.Name+'"' + ');'
FROM sys.indexes I
INNER JOIN sys.index_columns IC
ON I.object_id = IC.object_id AND I.index_id = IC.index_id
INNER JOIN sys.columns C
ON IC.object_id = C.object_id and IC.column_id = C.column_id
INNER JOIN sys.tables T
ON I.object_id = T.object_id
WHERE T.Name like 'AO_%'
and I.is_primary_key = 0
and I.is_unique = 'false'
ORDER BY T.Name, I.Name;
Alter the column type
-- copy the results to a file called '5-altercolumntype.sql'
SELECT 'ALTER TABLE ' + syo.name + ' ALTER COLUMN ' + '"' + syc.name + '"' + ' nvarchar' +
'(' +
CASE
WHEN COL_LENGTH ( syo.name , syc.name ) <> -1 THEN
CONVERT(varchar(10), COL_LENGTH ( syo.name , syc.name ))
ELSE 'max' -- -1
END +
')' +
CASE
WHEN COLUMNPROPERTY(OBJECT_ID(syo.name),syc.name,'AllowsNull') = 1
THEN ' NULL'
ELSE ' NOT NULL'
END + ';'
FROM sysobjects syo
JOIN syscolumns syc ON
syc.id = syo.id
JOIN systypes syt ON
syt.xtype = syc.xtype
WHERE syt.name = 'varchar'
and syo.name like 'AO_%'
ORDER by syo.name, syc.name;
Example output
This is an example of output of these blocks, ordered by description.