Drupal Clean URL's with IIS7

Drupal can work with clean URL's (or URL write). The most talked about solution is using Helicon's ISAPI _Rewrite ISAPI add-in, which can be found here (http://www.isapirewrite.com/) if you are interested. But here I want to show you how you can accomplish clean url's using a free alternative by Ionics called IIRF.

Perform the following steps to get clean URL's in Drupal 5.3 in IIS7:

1) Download Ionics Isapi Rewrite Filter
At the moment of writing this is the only quite reliable freeware URL rewriter for IIS. You can get it here:
http://www.codeplex.com/IIRF/Release/ProjectReleases.aspx?ReleaseId=5018
Download the most recent "-bin.zip" version.

2) Install Ionics Isapi Rewrite Filter
- Extract IsapiRewrite4.dll to c:\inetpub\wwwroot (or your www root folder)
- Extract \examples\DrupalRules.ini to c:\inetpub\wwwroot (or your www root folder) as IsapiRewrite4.ini

If you haven't installed Drupal in the webroot, but, like me, in a subfolder, then you have to perform the following updates to DrupalRules.ini. I use http://localhost/drupal, so I had to add
add "/drupal" to almost every RewriteRule. In stead of "/drupal" you can use any folder or level of subfolders that is necessary in your case, e.g. for http://localhost/davy/dozy/tich you have to replace the bold "/drupal" string with "/davy/dozy/tich".

Here are some examples for each of the sections defined in DrupalRules.ini:

RewriteRule ^/misc/(.*)$ /misc/$1 [I,L]
RewriteRule ^/modules/tinymce/(.*)$ /modules/tinymce/$1 [I,L]
RewriteRule ^/cron\.php$ /cron.php [I,L]
RewriteRule /(.*)\?(.*)$ /index.php\?q=$1&$2 [I,L]
RewriteRule ^/(.*)$ /index.php?q=$1 [I,L]

become

RewriteRule ^/drupal/misc/(.*)$ /drupal/misc/$1 [I,L]
RewriteRule ^/drupal/modules/tinymce/(.*)$ /drupal/modules/tinymce/$1 [I,L]
RewriteRule ^/drupal/cron\.php$ /drupal/cron.php [I,L]
RewriteRule /drupal/(.*)\?(.*)$ /drupal/index.php\?q=$1&$2 [I,L]
RewriteRule ^/drupal/(.*)$ /drupal/index.php?q=$1 [I,L]

The following RewriteRule has to be added to the first RewriteRule-section If you also use Gallery2:

RewriteRule ^/gallery2/(.*)$ /gallery2/$1 [I,L]

or if you use http://localhost/drupal

RewriteRule ^/drupal/gallery2/(.*)$ /drupal/gallery2/$1 [I,L]

The RewriteRule function uses regular expressions to transform clean url's back to their non-clean equivalents.

3) Add ISAPI filter to IIS7
- Start IIS.msc
- Select Default Web Site (not your virtual directory or application!)
- Double-click the ISAPI filters icon
- Add...

Filtername: IsapiRewrite4
Executable: C:\inetpub\wwwroot\IsapiRewrite4.dll

4) Set Drupal to use Clean URL's
- Start Drupal in your web browser
- Administer -> Clean URLs
- Click "Test for Clean URL's" (it is somewhat hidden in the paragraph text)
- Select enable

In my case, the enable check box was still greyed out. To set Clean URL's manually:

- Remove write-protection from the c:\inetpub\wwwroot\drupal\sites\default\settings.php file
- Edit the file and add:

# Force Clean urls
$conf['clean_url']=1;

- Set write-protection back.

Hopefully this quick and rudimentary instruction helps you setting up URL rewriting for you Drupal installation.

Liger

I saw a documentary today on National Geographic about the cross-breeding product of a tiger and lion. The hybrid is called a liger. I didn't know that such a beast exists.

You can read more about it here:
http://en.wikipedia.org/wiki/Liger

Fix upload of images in Drupal

Solution to messages like

warning: move_uploaded_file() [function.move-uploaded-file]: Unable to move 'c:\progra~1\php\upload_tmp\php525F.tmp' to '' in C:\inetpub\wwwroot\drupal\includes\file.inc on line 244."

when trying to upload images to Drupal 5.3.

Perform or check these steps (I'm using Vista and IIS7):

1) Create temporary upload folder for php
php must have an upload folder to be able to upload files. Create one called upload_tmp under the install root of php. So if php is installed here:

c:\progra~1\php\

then the full path to the upload folder will be:

c:\progra~1\php\upload_tmp

2) Change php.ini
Change the settings to the following values:
file_uploads = On
upload_tmp_dir = "c:\progra~1\php\upload_tmp"
upload_max_filesize = 2M

3) Create temporary preview and upload folder for Drupal
Drupal must also have an upload and preview folder to be able to upload and preview files.

Create the following folders under the drupal root.
files
files\tmp

So if drupal is installed here:

C:\inetpub\wwwroot\drupal\

then the full path to the upload and preview folders will be:

C:\inetpub\wwwroot\drupal\files
C:\inetpub\wwwroot\drupal\files\tmp

In my case it worked without setting permissions on these folders, but if it doesn't, you can try giving the web service user access.

Give user IUSR permission to read/write on these folders:

- Right click folder- Properties -> security -> edit -> add -> advanced -> find now
- Select IUSR, ok
- Select full control, ok, ok

4a) Change drupal settings (upload module)
Activate the upload module:

In Administer -> Site building -> Modules, activate module Upload

4b) Change drupal settings (folders)
In Administer -> Site configuration -> File system, set the following values:

File system path: files
Temporary directory: files/tmp

The temporary directory must be a subfolder of the file system path folder!

Now the upload should work.

Install Windows Components from command-line

If you want to start the add/remove Windows Components dialog without using the add/remove programs interface, then use the following command:

Sysocmgr.exe /i:sysoc.ini

IE7 enable tabbed browsing

You can enable tabbed browsing in Internet Explorer 7 by using a menu command:

Tools -> Internet Options -> Tab: settings -> select Enable Tabbed Browsing.

However, sometimes this is not enough, for example because the domain administrator has "disabled" tabbed browsing using a registry key. You can enable it by putting the next registry entry in a .reg text file (e.g. c:\tabbrowse.reg) and executing it:

Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer]"ClassicShell"=dword:00000000

... or you can set the DWord keyvalue to 0 by hand.

If the key value gets restored every time you log in to your system, you could add the execution of registry file to the startup folder:

- create a bat file, e.g. tabbrowse.bat
- add the following line to the bat file: regedit /s c:\tabbrowse.reg
- add the bat file to the startup folder

Dynamic transparent color

The default value of most color properties is "Transparent". If you want to dynamically set a color property, you can use the IIF-statement. This statement has the following signature:

=IIF([boolean],[return value if true],[return value if false])

A problem lies in the fact that both the true and the false part are obligatory. For setting the color the following IIF-statement can be used:

=IIF(ReportItems!txtBox.Value=5, "Palegreen", "Transparent")

But, the value Transparent does not exist! You cannot set it! Visual Studio throws the following warning:

The value of the background color property for the textbox ‘textbox’ is “Transparent”, which is not a valid background color.

It turns out that this property is not exported to the generated XML file, called rdl, if it is not explicitly set. The IIF-statement forces you to set it, but the value Transparent cannot be used. How to dynamically set the color property then?

You could use the value White instead. However, the background color for example gets rendered after the borders, so a white background covers part of a visible cell border. This is clearly visible on the report. Very annoying...

The only solution I could come up with is using a function that does not have an explicit return type. Then, if there is no return value, a non-existing object is returned.

Like this:

function Rank(val1)

if val1=5 then Rank = "Palegreen"

end function

Can you have the IIF-statement return a null (uninitialized object) value? The System.DBNull value that has replace the null value is just a type, not a value. So, this doesn't work.

Truncate staging tables

Before creating a backup of the database, you could clean out all staging tables to safe some space in the backup and to reduce the time it takes to make the backup. You could use the following simple SQL-loop statement:

EXEC sp_MSforeachtable "IF LEFT('?',11)='[dbo].[STG_' BEGIN PRINT '?' TRUNCATE TABLE ? END"

The statement expects a strict naming convention: all staging table names should start with STG_ and this prefix should not be used for any other table types. Also make sure you do not have any "permanent" data in your staging environment.

Note:
The PRINT-statement can be left out. It could help you debug the statement. Also the truncate statement does not work if there are foreign keys referencing the table.

Windows Update fix

Sometimes Windows Update gets corrupted. Some of its components or previously downloaded updates get mangled during the download or installation process. Here's a quick fix to clean out the folders that it uses (put the script in a batch file):

@echo off

NET STOP "Automatic Updates"

SET D=%windir%\SoftwareDistribution\DownloadDEL /q %D%
FOR /F %%f IN ('dir %D% /b /A:D') DO RD /s /q %D%%%f

SET D=%windir%\SoftwareDistribution\DataStoreDEL /q %D%
FOR /F %%f IN ('dir %D% /b /A:D') DO RD /s /q %D%%%f

SET D=%windir%\system32\CatRoot2DEL /q %D%
FOR /F %%f IN ('dir %D% /b /A:D') DO RD /s /q %D%%%f

NET START "Automatic Updates"

On execution, it wil re-download the main components and give you a "fresh" Windows Update installation.

Cannot decrypt the symmetric key...

Yesterday I got a Windows update of my .Net framework 2.0. After the update the system needed to be rebooted, so I did that. After that, my reportmanager in Internet Explorer returned with the following error:

The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content and then restart the service. Check the documentation for more information. (rsReportServerDisabled)

The Reporting Services service (RSS) uses an asymmetric key to decrypt a symmetric key. This symmetric key is used to communicate with its repository in SQL Server. The asymmetric key is tied to the account with which the RSS runs. Change the account and the key is invalidated. Apparently, the update did something to this account, although it was "LocalSystem" and still is "LocalSystem".

Fortunately, the problem is easily solved by issuing the following statement at the command prompt I found on Kate Gregory's blog posted by a guy named Derek:

rsactivate -r -c"C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config"

It re-creates the assymetric key for the service account and stores it again in the rsreportserver.config file.

Return the size of pre-selected tables

A stored procedure I use a lot to get a resultset containing the size of a selection of tables in the database:

CREATE procedure sp_TableSize as
begin
SET ANSI_WARNINGS OFF

CREATE TABLE #temp
(
TABLENAME varchar(100),
ROWS int,
RESERVED varchar(25),
DATA varchar(25),
INDEX_SIZE varchar(25),
UNUSED varchar(25),
)

DECLARE @table nvarchar(100)

-- Select the tables for which to retrieve sizes
DECLARE cDWHTables cursor read_only for
SELECT name FROM sysobjects
WHERE LEFT(name, 4) in ('STG_','ODS_','DIM_','SYS_','FCT_','RPT_') OR
LEFT(name,3) = 'MD_'

OPEN cDWHTables

-- Store each record retrieved from sp_spaceused in temp table
FETCH NEXT FROM cDWHTables INTO @table
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
insert #temp exec sp_spaceused @table
END
FETCH NEXT FROM cDWHTables INTO @table
END

UPDATE #temp SET
reserved = left(reserved, len(reserved) - 3),
data = left(data, len(data) - 3),
index_size = left(index_size, len(index_size) - 3),
unused = left(unused, len(unused) - 3)

INSERT #temp
SELECT 'TOTAL' as tablename, sum(rows) as rows,
sum(convert(int,reserved)) as reserved,
sum(convert(int,data)) as data,
sum(convert(int,index_size)) as index_size,
sum(convert(int,unused)) as unused
FROM #temp

--Return the results as data set
SELECT
TABLENAME = tablename,
ROWS = convert(int,rows),
CONVERT(int,reserved) as RESERVED,
convert(int,data) as DATA,
convert(int,index_size) as INDEX_SIZE,
convert(int,unused) as UNUSED,
AVG_DATA = convert(numeric(18,2),case when rows>0 then convert(float, data) / convert(float, [rows]) else 0 end),
TOTAL = convert(int,data) + convert(int,index_size),
TOTAL_MB = (convert(int,data) + convert(int,index_size))/1024
FROM #temp

-- Clean up
DROP TABLE #temp
CLOSE cDWHTables
DEALLOCATE cDWHTables

end

Replace the bold part to set the tables for which to return the size. After all records have been returned it adds a final total row to the set.

Note:
The insert of the total row can alternatively be written using the COMPUTE-clause on the main select statement.

Backup to network share

You can use the following TSQL statement to backup a database to a network share in SQL Server 2000:

BACKUP DATABASE TO DISK = '\\unc\folder\filename.bak'
WITH RETAINDAYS=0, INIT

This will create a full backup each time it is executed. The RETAINDAYS=0 option makes sure that the previous backup will always get overwritten. Not the safest thing, but can be needed if you are in a tight spot regarding disk space.

This backup file will also appear then in Enterprise Manager. Select Tools, Backup database and check the destination listbox.

To check if the backup is complete and readable:

RESTORE VERIFYONLY FROM DISK = '\\unc\folder\filename.bak'

This way you can have more control over your backup procedure than using maintenance plans, e.g. by putting them in a Execute SQL Task in a DTS package.

Alternatively, using Enterprise Manager, you can add a backup device via management\backup and have in point to \\unc\folder\filename.bak. Right-click it, select "backup a database" and click "Ok".

Note:
It is not recommended to backup directly to a network share, but sometimes there is no alternative.

Note 2:
Setting the RETAINDAYS property to e.g. 5 days means it will be impossible to overwrite the backup file during the next 5 days! Beware of that!

Concatenating text files

Jamie Thomson has a post on concatenating multiple text files (using the ForEach Loop) and subsequently loading this concatenated file in one go. You can find the post here:

http://blogs.conchango.com/jamiethomson/archive/2006/06/22/4116.aspx

I use to do this with a small batch file. The batch file is called concat.bat and takes in the following parameters: %1 for the extension of the files to concatenate, e.g. txt and %2 for the folder in which the files are stored.

The body of the batch file is as follows:

set I=
for /F %%f in ('dir "%2"*.%1 /B /O:N /A:-D') do set I=%%f
if not defined I goto EXIT
copy "%2"*.%1 "%2"\backup
copy "%2"*.%1 "%2"%I:~0,5%concat.tmp
if exist "%2"%I:~0,5%concat.tmp del "%2"*.%1 > NUL
ren "%2"*concat.tmp *.%1
:EXIT

Example of the usage of the batch file:
The batch files is called with %1 = .txt and %2 = c:\temp. Say there are three files in c:\temp called test1.txt, test2.txt, and test3.txt with create dates 2006/01/01, 2006/01/02, and 2006/01/03 respectively. The result is one textfile called text1.txt containing the content of all three files.

Explanation of each line of code:

Set I=
dimensions the variable I.

for /F %%f in ('dir "%2"*.%1 /B /O:N /A:-D') do set I=%%f
iterates over all files in folder c:\temp with extension ".txt", ordered descending by create date. Each iteration "do set I=%%f" assigns the filename to variable I. After iteration I contains the value of the last filename in the list. This is the file created first according to create date, since the list is sorted decendantly.

if not defined I goto EXIT
checks if I has a value, if not, the batch ends. I.e. there where no .txt files in the folder.

copy "%2"*.%1 "%2"\backup
copies the three sourcefiles to a backup folder. This is done for auditing purposes.

copy "%2"*.%1 "%2"%I:~0,5%concat.tmp
concatenates all .txt files to one file with the filename stored in I. So, a file called test1concat.tmp is created, containing the union/concatenation of the three files.

if exist "%2"%I:~0,5%concat.tmp del "%2"*.%1 > NUL
does a check to ensure the .tmp file has been created and deletes the three source files, so the inbox in emptied.

ren "%2"*concat.tmp *.%1
renames the .tmp file to the final filename, in this case text1.txt.

EXIT:
exits the batch file.

Currently it only works if all files have the same filename length, so 5 (="textX") in the case of the example. Of course this can be optimized, e.g. search for the first dot and take the left part of the file name.

I have no idea if this approach is better with regard to performance. I haven't tested it on thousands of files and have not done a comparison to the approach used in the post by Jamie. On the other hand, this approach will also work nicely in DTS, where there is no ForEach Loop.

Note:
The "for /F" functionality is not available in all Windows versions. It is available in Windows 2003 server and Windows XP.

Strange HAVING-clause behaviour

Say you want to return a resultset containing the last two days per week. First, construct the demo table:

CREATE TABLE TEST_TIME (WEEKCODE INT, ISODATE CHAR(8))
INSERT INTO TEST_TIME VALUES (1, '20060101')
INSERT INTO TEST_TIME VALUES (1, '20060102')
INSERT INTO TEST_TIME VALUES (1, '20060103')
INSERT INTO TEST_TIME VALUES (2, '20060108')
INSERT INTO TEST_TIME VALUES (2, '20060109')
INSERT INTO TEST_TIME VALUES (2, '20060110')
INSERT INTO TEST_TIME VALUES (2, '20060111')

The following SQL query will give the desired resultset:

SELECT t.WEEKCODE, t.ISODATE FROM TEST_TIME t
WHERE t.ISODATE >=
(
SELECT TOP 1 ISODATE FROM
(
SELECT TOP 2 ISODATE
FROM TEST_TIME t2
WHERE t.WEEKCODE = t2.WEEKCODE
ORDER BY ISODATE DESC
) t1
ORDER BY ISODATE ASC
)
ORDER BY t.WEEKCODE

It returns 4 records, as espected:

1 20060102
1 20060103
2 20060110
2 20060111

Just for fun, I tried the same thing using a GROUP BY and HAVING-clause like this:

SELECT t.WEEKCODE, t.ISODATE FROM TEST_TIME t
GROUP BY t.WEEKCODE, t.ISODATE
HAVING t.ISODATE >=
(
SELECT TOP 1 ISODATE FROM
(
SELECT TOP 2 ISODATE
FROM TEST_TIME t2
WHERE t.WEEKCODE = t2.WEEKCODE
ORDER BY ISODATE DESC
) t1
ORDER BY ISODATE ASC
)
ORDER BY t.WEEKCODE

Far less efficient, but one would expect it to return the same resultset. But it does not! In fact it returns 28 records:

1 20060102
1 20060102
1 20060102
1 20060102
1 20060102
1 20060102
1 20060102
1 20060103
1 20060103
1 20060103
1 20060103
1 20060103
1 20060103
1 20060103
2 20060110
2 20060110
2 20060110
2 20060110
2 20060110
2 20060110
2 20060110
2 20060111
2 20060111
2 20060111
2 20060111
2 20060111
2 20060111
2 20060111

That is even more than there are in the table! This is very strange behaviour. Stripping the HAVING-clause returns 7 records, as expected. But adding it results in a cartesian product of the 7 source records by the 4 top-2 records (2 for week 1 and 2 for week 2) resulting in 7x4=28 records.

From BOL:
"The HAVING clause is then applied to the rows in the result set that are produced by grouping. Only the groups that meet the HAVING conditions appear in the query output. You can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function."

So, first the grouping, THEN an extra filtering on that set. How can filtering 7 records result in 28 records? Furthermore, the 28 records are not distinct records. Something you would expect when using a GROUP BY-clause...

Conclusion is that in case of a correlated having-clause query the having-clause is evaluated before the group by-clause.