Component: WebDAV Store

 Show all Hide all

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.
Supports custom errors: No
May modify database: No

Parameters:

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

Resultset: File or folder information

Returns information about the file or folder. Return an empty resultset to indicate that the file does not exist.
Repeat mode: 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.
Supports custom errors: No
May modify database: No

Parameters:

@Action mandatory string
Set to "Read"
@Path mandatory string
Path to file, relative to the WebDAV root. For example "documents/secret/report.docx".
@UserID mandatory 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.
Repeat mode: 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.
Supports custom errors: No
May modify database: No

Parameters:

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

Resultset: Files

Information about the files in the folder.
Repeat mode: 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.
Repeat mode: 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.
Supports custom errors: No
May modify database: No

Parameters:

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

Resultset: Write Permissions

Return an empty resultset (or 0 in all columns) if the user can write.
Repeat mode: 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.
Supports custom errors: No
May modify database: Yes

Parameters:

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

Resultset: Write result

One of the status bits must be set to 1.
Repeat mode: 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.
Supports custom errors: No
May modify database: No

Parameters:

@Action mandatory string
Set to "GetProperties"
@Path mandatory string
Path to file or folder, relative to the WebDAV root. For example "documents/secret/report.docx".
@UserID mandatory 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)
Repeat mode: 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".
Supports custom errors: No
May modify database: Yes

Parameters:

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

Resultset: Update result

The result of the update operation.
Repeat mode: 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.
Supports custom errors: No
May modify database: No

Parameters:

@Action mandatory string
Set to "GetInfo"
@IsExclusive mandatory 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 mandatory uniqueidentifier
A unique token identifying the lock.
@LockOwner mandatory xml
Save this value without messing with it.
@LockRoot mandatory string
Save this value without messing with it.
@Path mandatory string
Path to file or folder, relative to the WebDAV root. For example "documents/secret/report.docx".
@Timeout mandatory 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 mandatory int
User ID

Resultset: Lock information

Returns information about the lock.
Repeat mode: repeated zero or one time
Row count: zero or one row

Columns:

ConflictingLockExists optional bit
A conflicting lock already exists on the file.
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.
If not specified the user is assumed to be authorized.
IsExclusive optional bit
Specifies that the aquired lock is exclusive.
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.
Supports custom errors: No
May modify database: Yes

Parameters:

@Action mandatory string
Set to 'RefreshLock' for this call.
@LockGuid mandatory uniqueidentifier
Token identifying the lock to be refreshed.
@Timeout mandatory 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.
Repeat mode: repeated exactly once
Row count: zero or one row

Columns:

IsExclusive optional bit
Specifies that the aquired lock is exclusive.
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)

Supports custom errors: No
May modify database: Yes

Parameters:

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

Resultset: Unlock resultset

Repeat mode: 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;