WebDAV Store

Create a store for publishing documents using the WebDAV protocol.

NOTE: You don't actually create any menu items with this component. You enter the name of the stored procedure in application variable 'WebDAVStoredProcedure'. You must also specify application variable 'WebDAVURL' to make it work.

The documents are then accessed at the URL <webdavurl>/yourfilepath.txt. The url can be prefixed with a URI scheme to make a certain program open the file (for example word uses: "ms-word:ofe|u|<webdavurl>/filex.docx") for document editing.

Server Configuration

NOTE: IIS 7 and windows integrated authentication is required for WebDAV support.

To enable WebDAV in your system you must perform the following configuration steps on the server:

  1. Convert the webdav folder to an application, or setup a separate site for it.
  2. The application pool must be in integrated pipeline mode, and run as a user that is allowed to communicate with Active Directory (for example Network Service).
  3. Disable 'anonymous access' and enable 'Windows integrated authentication' on the webdav folder.
  4. Configure a connection string for the new application in softadmin.config. The application name will be <SystemName>/webdav.
  5. Configure application varables WebDAVURL and WebDAVStoredProcedure.

Client Configuration

The following keys must be added to registry to support saving from Microsoft Office 2007 or earlier applications:

[HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Common\Internet]
"OpenDocumentsReadWriteWhileBrowsing"=dword:00000001

[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Common\Internet]
"OpenDocumentsReadWriteWhileBrowsing"=dword:00000001

On Windows XP the following may be required:

  1. Run webfldrs.msi (included with Windows)
  2. Click "Select Reinstall Type"
  3. Check all boxes except the second one
  4. Click OK
  5. Click Reinstall
  6. Restart the computer

SQL

SQL Call: Get info (mandatory)

Retrieves information about a file or folder.

May modify database: No

Parameters

@Action string
Set to "GetInfo"
@Path string
Path to file or folder, relative to the WebDAV root. For example "documents/secret/report.docx".
@UserID int
User ID

Resultset: File or folder information (optional)

Returns information about the file or folder. Return an empty resultset to indicate that the file does not exist.
Table count: repeated zero or one time
Row count: zero or one row
Columns
ContentSize optional int
Content size (files only).
ContentType optional string
Content type (files only).
Deny optional bit
If the user is allowed to access the object. You may return deny=1 even if the object does not exist.
DenyListing mandatory bit
If the user is forbidden from retrieving a list of the files that exist in the folder. He may still be allowed to access files in the folder as long as he knows their names. (Folders only).
IsFolder optional bit
If the object is a folder.
UTCLastModified mandatory datetime
The last time the object was modified, in Coordinated Universal Time.

SQL Call: Read file (mandatory)

Downloads a file.

May modify database: No

Parameters

@Action string
Set to "Read"
@Path string
Path to file, relative to the WebDAV root. For example "documents/secret/report.docx".
@UserID int
User ID

Resultset: File contents

The contents of the file. Return no rows if the file can not be found. A Get info call will always be made before a Read file call, so if you need to deny the user access to the file then do that in the Get info call.
Table count: repeated exactly once
Row count: zero or one row
Columns
<column with ordinal 0> mandatory binary
File contents. Must not be NULL.

SQL Call: List files (mandatory)

Retrieves information about the files and folders in a folder.

May modify database: No

Parameters

@Action string
Set to "ListFiles"
@Path string
Path to folder, relative to the WebDAV root. For example "documents/secret/".
@UserID int
User ID

Resultset: Files

Information about the files in the folder.
Table count: repeated exactly once
Row count: zero or more rows
Columns
ContentSize optional bit
Content size.
ContentType optional string
Content type.
Filename mandatory string
File name. If the folder path is "documents/secret" and the file name is "report.docx" then the file must be accessable at the path "documents/secret/report.docx".
UTCLastModified optional datetime
The last time the object was modified, in Coordinated Universal Time.

Resultset: Folders

Information about the subfolders in the folder.
Table count: repeated exactly once
Row count: zero or more rows
Columns
Foldername mandatory string
Folder name. If the parent folder path is "documents" and the folder name is "secret" then the folder must be accessable at the path "documents/secret".
UTCLastModified optional datetime
The last time the object was modified, in Coordinated Universal Time.

SQL Call: CanWrite (mandatory)

Tests if user is allowed to insert/update a file.

May modify database: No

Parameters

@Action string
Set to "CanWrite"
@LockGUID uniqueidentifier
May be NULL. If present, a lock that the user has (or claims to have) on the file.
@Path string
Path to file relative to the WebDAV root. For example "documents/secret/report.docx".
@UserID int
User ID

Resultset: Write Permissions (optional)

Return an empty resultset (or 0 in all columns) if the user can write.
Table count: repeated zero or more times
Row count: zero or one row
Columns
ConflictingLockExists optional bit
Set to 1 if a lock is preventing the user from saving.
Deny optional bit
Set to 1 if the user is not allowed to save here/update this file.
FolderDoesNotExist optional bit
Set to 1 if the user tried to save in a folder that does not exist.
IsFolder optional bit
Set to 1 if the user tried to overwrite a folder with his file.

SQL Call: Write (mandatory)

Inserts or updates a file.

May modify database: Yes

Parameters

@Action string
Set to "Write"
@Content binary
File content.
@ContentType string
Content type.
@LockGUID uniqueidentifier
May be NULL. If present, a lock that the user has (or claims to have) on the file.
@Path string
Path to file relative to the WebDAV root. For example "documents/secret/report.docx".
@UserID int
User ID

Resultset: Write result

One of the status bits must be set to 1.
Table count: repeated exactly once
Row count: exactly one row
Columns
ConflictingLockExists optional bit
Set to 1 if a lock is preventing the user from saving.
Created optional bit
Set to 1 if the user created a new file.
Deny optional bit
Set to 1 if the user is not allowed to save here/update this file.
FolderDoesNotExist optional bit
Set to 1 if the user tried to save in a folder that does not exist.
IsFolder optional bit
Set to 1 if the user tried to overwrite a folder with his file.
Updated optional bit
Set to 1 if the user updated an existing file.

SQL Call: Get properties (mandatory)

Gets the WebDAV properties for a file.

May modify database: No

Parameters

@Action string
Set to "GetProperties"
@Path string
Path to file or folder, relative to the WebDAV root. For example "documents/secret/report.docx".
@UserID int
User ID

Resultset: Properties

NOTE: You must return a row in this resultset even if you have no record for the file in the database, otherwise the file will be opened as read-only. (Does not apply for Windows XP clients)
Table count: repeated exactly once
Row count: exactly one row
Columns
DAVProperties mandatory xml
The properties
Deny mandatory bit
Set to 1 if the user is not allowed to read the file's properties.

SQL Call: Set properties (mandatory)

Updates the DAV properties of a file. Updating a file's properties should NOT affect its "LastChangedDatetime".

May modify database: Yes

Parameters

@Action string
Set to "SetProperties"
@DAVProperties xml
The properties
@Path string
Path to file or folder, relative to the WebDAV root. For example "documents/secret/report.docx".
@UserID int
User ID

Resultset: Update result (optional)

The result of the update operation.
Table count: repeated zero or one time
Row count: zero or one row
Columns
Deny optional bit
Set to 1 if the user was not allowed to update the properties.
Updated optional bit
Set to 1 if the properties were updated.

SQL Call: Lock file (mandatory)

This call is made when a user attempts to lock a file.

If the file does not exist or if the user attempts to lock a folder then you must return deny=1.

May modify database: No

Parameters

@Action string
Set to "GetInfo"
@IsExclusive bit
If the user wants an exclusive or a shared lock on the file.

Shared locks may be granted to many users at the same time. An exclusive lock may only be granted if no other locks (including shared locks) exists on the resource.
@LockGUID uniqueidentifier
A unique token identifying the lock.
@LockOwner xml
Save this value without messing with it.
@LockRoot string
Save this value without messing with it.
@Path string
Path to file or folder, relative to the WebDAV root. For example "documents/secret/report.docx".
@Timeout int
Timeout (in seconds) before the lock expires. This is merely a request: you may assign the lock a lower timeout if you need to.
@UserID int
User ID

Resultset: Lock information (optional)

Returns information about the lock.
Table count: repeated zero or one time
Row count: zero or one row
Columns
ConflictingLockExists optional bit
A conflicting lock already exists on the file.
Default: If the column is not specified the lock is assumed to be granted.
Deny optional bit
Set this flag if the user is not authorized to lock the file.
Default: If not specified the user is assumed to be authorized.
IsExclusive optional bit
Specifies that the aquired lock is exclusive.
Default: If not specified the lock is assumed to be exclusive.
LockGuid mandatory uniqueidentifier
The token identifying the lock. This must be the same token as in the @LockGuid parameter.
LockOwner mandatory string
A token identifying the owner of the lock. This should be the same as in the @LockOwner parameter from the 'Lock file' sql call.
LockRoot mandatory string
This must be the same as in the @LockRoot parameter from the 'Lock file' sql call.
Timeout mandatory int
The number of seconds until the lock will automatically expire unless refreshed by the client.

You should always remove the lock as soon as possible after the timeout period has expired.

SQL Call: Refresh Lock (mandatory)

The client requests that his lock be extended for an additional time period.

May modify database: Yes

Parameters

@Action string
Set to 'RefreshLock' for this call.
@LockGuid uniqueidentifier
Token identifying the lock to be refreshed.
@Timeout int
The request lock period.

NOTE: This is just a request from the client, you may specify another lock period if suitable.

Resultset: Lock information

Returns information about the lock.

If an attempt was made to refresh an expired lock you should return zero rows from this call to indicate an error.
Table count: repeated exactly once
Row count: zero or one row
Columns
IsExclusive optional bit
Specifies that the aquired lock is exclusive.
Default: If not specified the lock is assumed to be exclusive.
LockGuid mandatory uniqueidentifier
The token identifying the lock. This must be the same token as in the @LockGuid parameter.
LockOwner mandatory string
A token identifying the owner of the lock. This should be the same as in the @LockOwner parameter from the 'Lock file' sql call.
LockRoot mandatory string
This must be the same as in the @LockRoot parameter from the 'Lock file' sql call.
Timeout mandatory int
The number of seconds until the lock will automatically expire unless refreshed by the client.

You should always remove the lock as soon as possible after the timeout period has expired.

SQL Call: Unlock file (mandatory)

May modify database: Yes

Parameters

@Action string
Set to 'Unlock'.
@LockGuid uniqueidentifier
A unique token identifying the lock.
@Path string
Path to file or folder, relative to the WebDAV root. For example "documents/secret/report.docx".

Resultset: Unlock resultset (optional)

Table count: repeated zero or one time
Row count: zero or one row
Columns
Deny optional bit
Unlock was not approved by database

Examples

Sample WebDav procedure

CREATE OR ALTER PROCEDURE WebDav.WebDavHandler
	@Action varchar(50),
	@UserId int,
	@Path varchar(max) = NULL,

	-- Lock properties.
	@IsExclusive		bit = 1,
	@LockGuid			uniqueidentifier = NULL,
	@LockOwner			nvarchar(max) = NULL,
	@LockRoot			nvarchar(max) = NULL,
	@Timeout			int = NULL,

	-- File properties.
	@DavProperties		xml = NULL,
	@ContentType		varchar(300) = NULL,
	@Content			varbinary(max) = NULL
AS
BEGIN
	SET XACT_ABORT ON;

	DECLARE @DocumentId int = (SELECT DocumentId FROM WebDav.Document WHERE DocumentFilename = @Path);

	IF @Action = 'GetInfo'
	BEGIN
		IF 0 = WebDav.Document_UserCanRead(@DocumentId, @UserId)
		BEGIN
			SELECT 1 AS [Deny];
			RETURN;
		END;

		SELECT
			DATALENGTH(Document) AS ContentSize,
			DocumentContentType AS ContentType
		FROM
			WebDav.Document D
		WHERE
			D.DocumentId = @DocumentId;

		RETURN;
	END;

	IF @Action = 'Read'
	BEGIN
		SELECT
			D.Document
		FROM
			WebDav.Document D
		WHERE
			D.DocumentId = @DocumentId;

		RETURN;
	END;

	IF @Action = 'CanWrite'
	BEGIN
		IF 0 = WebDav.Document_UserCanWrite(@DocumentId, @UserId)
		BEGIN
			SELECT 1 AS [Deny];
			RETURN;
		END;

		IF EXISTS (SELECT * FROM WebDav.DocumentLock WHERE DocumentId = @DocumentId AND (@LockGuid IS NULL OR LockGuid <> @LockGuid))
		BEGIN
			SELECT 1 AS [ConflictingLockExists];
		END;

		RETURN;
	END;

	IF @Action = 'Write'
	BEGIN
		UPDATE WebDav.Document SET
			Document = @Content,
			DocumentContentType = @ContentType
		WHERE
			DocumentId = @DocumentId;

		SELECT 1 AS [Updated];
		RETURN;
	END;
	
	IF @Action = 'GetProperties'
	BEGIN
		SELECT
			D.Props
		FROM
			WebDav.Document D
		WHERE
			D.DocumentId = @DocumentId;

		RETURN;
	END;

	IF @Action = 'SetProperties'
	BEGIN
		UPDATE WebDav.Document SET
			Props = @DavProperties
		WHERE
			DocumentId = @DocumentId;

		RETURN;
	END;

	IF @Action = 'Lock'
	BEGIN
		-- Harvest old locks.
		DELETE WebDav.DocumentLock WHERE ExpiresDatetimeUtc < SYSUTCDATETIME();

		IF 0 = WebDav.Document_UserCanWrite(@DocumentId, @UserID)
		BEGIN
			SELECT 1 AS [Deny];
			RETURN;
		END;

		SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
		BEGIN TRANSACTION;

		-- Ensure that conflicting lock does not exist.
		IF EXISTS (SELECT * FROM WebDav.DocumentLock WHERE DocumentId = @DocumentId)
		BEGIN
			SELECT 1 AS ConfictingLockExists;
			COMMIT TRANSACTION;
			RETURN;
		END;

		-- Insert lock.
		INSERT WebDav.DocumentLock
		(
			DocumentId,
			UserId,
			LockGuid,
			LockDatetimeUtc,
			LockOwner,
			LockRoot,
			TimeoutSeconds
		)
		VALUES
		(
			@DocumentId,
			@UserId,
			@LockGuid,
			SYSUTCDATETIME(),
			@LockOwner,
			@LockRoot,
			@Timeout
		);

		SELECT
			@LockGuid AS LockGuid,
			@LockRoot AS LockRoot,
			@LockOwner AS LockOwner,
			@Timeout AS Timeout;

		COMMIT TRANSACTION;
		RETURN;
	END;

	IF @Action = 'RefreshLock'
	BEGIN
		UPDATE WebDav.DocumentLock SET
			@LockRoot = LockRoot, -- Read existing lock root.
			LockDatetimeUtc = SYSUTCDATETIME(),
			TimeoutSeconds = @Timeout
		WHERE
			LockGuid = @LockGuid;
		
		IF @@ROWCOUNT > 0
		BEGIN
			SELECT
				@LockGuid AS LockGuid,
				@LockRoot AS LockRoot,
				@LockOwner AS LockOwner,
				@Timeout AS Timeout;
		END;

		RETURN;
	END;

	IF @Action = 'Unlock'
	BEGIN
		DELETE WebDav.DocumentLock
		WHERE
			LockGuid = @LockGuid AND
			UserId = @UserId;
		RETURN;
	END;

	RETURN;
END;