SQL Server File System Tools

Source code checked in, #29201

burthold — Thu, 15 Oct 2009 00:54:08 GMT

added bat file RELEASE Folder and files.txt for zipping up a relase with 7-Zip

Source code checked in, #28955

burthold — Mon, 12 Oct 2009 19:35:02 GMT

added a check for invalid FileId in the InsertTag stored procedure

Updated Release: SQL Server File System Tools 1.0 (Oct 08, 2009)

burthold — Mon, 12 Oct 2009 15:43:00 GMT

This is the first fully working release.
Documentation is included in the zip.
New installs, run the create scripts modify path for dll.
also includes a test sql script and a test text file.
UPDATE:
Assembly Install Script was incorrect, would fail on parameter match error. Fixed.
Added additional documentation.
Cleaned up test script.

Released: SQL Server File System Tools 1.0 (Oct 08, 2009)

Mon, 12 Oct 2009 15:43:00 GMT

Source code checked in, #28926

burthold — Mon, 12 Oct 2009 15:41:55 GMT

Cleaned up test script. Fixed Install Assemblies script Added additional documentation.

Updated Release: SQL Server File System Tools 1.0 (Oct 08, 2009)

burthold — Mon, 12 Oct 2009 15:16:55 GMT

Updated Release: SQL Server File System Tools 1.0 (Oct 08, 2009)

burthold — Mon, 12 Oct 2009 15:16:37 GMT

Source code checked in, #28838

burthold — Mon, 12 Oct 2009 01:09:53 GMT

Added Schema to proejct

Updated Release: SQL Server File System Tools 1.0 (Oct 08, 2009)

burthold — Mon, 12 Oct 2009 00:57:53 GMT

This is the first fully working release.
Documentation is included in the zip.
New installs, run the create scripts modify path for dll.
also includes a test sql script and a test text file.

Released: SQL Server File System Tools 1.0 (Oct 08, 2009)

Mon, 12 Oct 2009 00:57:53 GMT

This is the first fully working release.
Documentation is included in the zip.
New installs, run the create scripts modify path for dll.
also includes a test sql script and a test text file.

Updated Wiki: Stored Procedure Calls

burthold — Sat, 10 Oct 2009 19:43:45 GMT

Stored Procedure Usage

StorePassPhrase @Password nvarchar(255)
Password limited to 24 characters hashed and stored in the database to encrypt and decrypt files.

StoreFile @FileLocation ,@ReturnFileId OUT
Store file unencrypted and uncompressed into the database. @ReturnFileId is the id of the file inserted.

StoreFileEncrypted @FileLocation ,@returnFileId OUT
Store file encrypted and uncompressed into the database. @ReturnFileId is the id of the file inserted, uses the Passphrase stored in the database.

StoreFileCompressed @FileLocation ,@CompressionLevel,@returnFileId OUT
Store file unencrypted and compressed into the database. @CompressionLevel from 1-9 are valid. @ReturnFileId is the id of the file inserted

StoreFileEncryptedCompressed @FileLocation,@CompressionLevel,@returnFileId OUT
Store file encrypted and compressed into the database. @CompressionLevel from 1-9 are valid. @ReturnFileId is the id of the file inserted, uses the Passphrase stored in the database.

InsertTag @returnfileid,@TagList
Insert and associate a list of tags to a specific file.

Retrievefiledetails @FileId
Get details of a file stored in the database if @FileId returns all files.

ExtractFileToDisk @FileId ,@FileLocation
Extract file to disk no matter the storage method output to the @FileLocation

ExtractFileToDiskEncrypted @FileId ,@FileLocation
Extract file to disk raw from the database. Will only work on encrypted uncompressed files.

ExtractFileToDiskCompressed @retFileId ,@FileLocation
Extract file to disk raw from the database. Will only work on unencrypted compressed files.

ExtractFileToDiskEncryptedCompressed @retFileId, ,@FileLocation
Extract file to disk raw from the database. Will only work on encrypted compressed files.

ExtractFileToRecord @retFileId
Extract file to record set no matter the storage method.

ExtractFileToRecordEncrypted] @retFileId
Extract file to record set raw from the database. Will only work on encrypted uncompressed files.

ExtractFileToRecordCompressed] @retFileId
Extract file to record set raw from the database. Will only work on unencrypted compressed files.

ExtractFileToRecordEncryptedCompressed] @retFileId
Extract file to record set raw from the database. Will only work on encrypted compressed files.

Closed Feature: Tag and Search System [5079]

burthold — Sat, 10 Oct 2009 19:42:09 GMT

Add ability to tag files with keywords and search for files based on those keywords.
Think tag cloud.
Comments:

Closing this separating out into two tasks.

Updated Wiki: Stored Procedure Calls

burthold — Sat, 10 Oct 2009 19:39:12 GMT

!Stored Procedure Usage

StorePassPhrase @Password nvarchar(255)
Password limited to 24 characters hashed and stored in the database to encrypt and decrypt files.

StoreFile @FileLocation ,@ReturnFileId OUT
Store file unencrypted and uncompressed into the database. @ReturnFileId is the id of the file inserted.

StoreFileEncrypted @FileLocation ,@returnFileId OUT
Store file encrypted and uncompressed into the database. @ReturnFileId is the id of the file inserted, uses the Passphrase stored in the database.

StoreFileCompressed @FileLocation ,@CompressionLevel,@returnFileId OUT
Store file unencrypted and compressed into the database. @CompressionLevel from 1-9 are valid. @ReturnFileId is the id of the file inserted

StoreFileEncryptedCompressed @FileLocation,@CompressionLevel,@returnFileId OUT
Store file encrypted and compressed into the database. @CompressionLevel from 1-9 are valid. @ReturnFileId is the id of the file inserted, uses the Passphrase stored in the database.

InsertTag @returnfileid,@TagList
Insert and associate a list of tags to a specific file.

Retrievefiledetails @FileId
Get details of a file stored in the database if @FileId returns all files.

ExtractFileToDisk @FileId ,@FileLocation
Extract file to disk no matter the storage method output to the @FileLocation

ExtractFileToDiskEncrypted @FileId ,@FileLocation
Extract file to disk raw from the database. Will only work on encrypted uncompressed files.

ExtractFileToDiskCompressed @retFileId ,@FileLocation
Extract file to disk raw from the database. Will only work on unencrypted compressed files.

ExtractFileToDiskEncryptedCompressed @retFileId, ,@FileLocation
Extract file to disk raw from the database. Will only work on encrypted compressed files.

ExtractFileToRecord @retFileId
Extract file to record set no matter the storage method.

ExtractFileToRecordEncrypted] @retFileId
Extract file to record set raw from the database. Will only work on encrypted uncompressed files.

ExtractFileToRecordCompressed] @retFileId
Extract file to record set raw from the database. Will only work on unencrypted compressed files.

ExtractFileToRecordEncryptedCompressed] @retFileId
Extract file to record set raw from the database. Will only work on encrypted compressed files.

Updated Wiki: Home

burthold — Sat, 10 Oct 2009 19:38:23 GMT

SQL Server File System Tools

SQL Server File System Tools is a SQL Server CLR dll containing several useful stored procedures for getting files into and out of SQL Server databases.
This includes:

Insert file into the database
- with encryption
- with compression(zip)
- or both!
Select file from database output to disk
- decompressed and unencrypted to file system UNC or hard path
- compressed to UNC or hard path with .zip extention (fully compatiable with windows explorer)
Select file from database output to recordset
- decompressed and unencrypted to binary record
- compressed to binary record (fully compatiable with windows explorer)

I am using a slimmed down version of #ziplib and will be modifiying it even ferther to meet my needs.
i'm also planning using a faster algorithim if you don't need to save to file system in a zip compatiable format.

since this requires UNSAFE it also requries you to ALTER DATABASE SQLFileStore SET trustworthy ON.

The binary download has the dll plus the sql script to create the objects and load the DLL. Currently, it takes loading it in UNSAFE mode but I will be fixing up the code so it will only require EXTERNAL_ACCESS in a later release.

Stored Procedure Calls

About the Documentation

Documentation generated using http://submain.com/products/ghostdoc.aspx GhostDoc and http://www.stack.nl/~dimitri/doxygen/ Doxygen.

About the licsense:

Since #ziplib is using a modified version of the GPL v2 I'm licensing under the LGPL which should be compatiable.

Source code checked in, #28557

burthold — Sat, 10 Oct 2009 03:22:13 GMT

Created Release: SQL Server File System Tools 1.0 (Oct 08, 2009)

burthold — Thu, 08 Oct 2009 21:43:08 GMT

This is the first fully working release.
Documentation is included in the zip.
New installs, run the create scripts modify path for dll.
also includes a test sql script and a test text file.

Released: SQL Server File System Tools 1.0 (Oct 08, 2009)

Thu, 08 Oct 2009 21:43:08 GMT

This is the first fully working release.
Documentation is included in the zip.
New installs, run the create scripts modify path for dll.
also includes a test sql script and a test text file.

Source code checked in, #28340

burthold — Thu, 08 Oct 2009 21:39:18 GMT

Source code checked in, #28332

burthold — Thu, 08 Oct 2009 20:10:33 GMT

All found bugs now fixed. All procs function as designed.

Source code checked in, #28250

burthold — Thu, 08 Oct 2009 05:05:41 GMT

bug fixes for fileid and new columns breaking file retreval