Daily Archives: 01/05/2010

PHPBB: Update template file by purging cache

Scenario: You’re trying to update a template file in your PHPBB 3.0.x forum, for example to include Google Adsense code into overall_header.html file to display banner on top of all the pages. But after modifying and uploading the file nothing changes, board still displays old template.

What is happening – PHPBB displays cached version of the template. Forum software caches commonly used files to improve performance. The solution is to purge cache.
Purgin Cache in PHPBB 3.0.x board
Login to your forum Administration Control Panel and click Run Now in Purge the cache section. After purging is complete changes in your template take effect.

T-SQL: Automatically Upgrade NVARCHAR(N) field to NVARCHAR(MAX)

If you’re writing an SQL upgrade script that updates a field in your table from NVARCHAR(n) data type to NVARCHAR(max) and the script is designed to run many times – an easy way to avoid multiple table alteration is to check for max character length for that field, i.e.:

IF (SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MY_TABLE' AND COLUMN_NAME = 'MY_COLUMN')<>-1
BEGIN
	ALTER TABLE [dbo].[MY_TABLE] ALTER COLUMN [MY_COLUMN] [nvarchar](max) NOT NULL
END

This code checks length of the field which is specific number for standard NVARCHAR and -1 for NVARCHAR(max), and alters column type only if it hasn’t already been altered.

T-SQL: Add column with default values to a table

Let’s say you need to add a column to an existing table that already contains some rows. And this new column needs to be prepopulated with a default value. For example integer nullable column NEW_COLUMN is added to table MY_TABLE. Common approach is to check for column existence, and if it doesn’t exist – create and populate it:

if not (exists(SELECT * FROM SYSCOLUMNS WHERE ID = OBJECT_ID('[dbo].[MY_TABLE]') AND Name = 'NEW_COLUMN'))
BEGIN
	ALTER TABLE [dbo].[MY_TABLE] ADD [NEW_COLUMN] [int] NULL
	UPDATE [dbo].[MY_TABLE] SET [NEW_COLUMN] = 1
END

But this doesn’t work – you get “Invalid column name” error. The trick is – you don’t need a separate UPDATE statement. ALTER TABLE … ADD statement has a DEFAULT parameter. You can modify previous code like this:

if not (exists(SELECT * FROM SYSCOLUMNS WHERE ID = OBJECT_ID('[dbo].[MY_TABLE]') AND Name = 'NEW_COLUMN'))
BEGIN
	ALTER TABLE [dbo].[MY_TABLE] ADD [NEW_COLUMN] [int] NULL DEFAULT 1 WITH VALUES
END

This ALTER statement assigns default value of 1 to the new column. WITH VALUES parameter is important, it populates existing rows with the default value (without it it will be populated by NULLs).