Filestream data storage attribute

• US 20040148308A1
• Filed: 01/29/2003
• Published: 07/29/2004
• Est. Priority Date: 01/29/2003
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Raman, Balan Sethu, Rajan, Rajeev B.
• Granted Patent: US 7,421,443 B2
• Time in Patent Office: Days
• US Class Current: 707/102
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

Large, unstructured objects can be manipulated within a relational database management system (RDBMS). A “filestream” data storage attribute is provided that can be used to tag a column in a relational table. The filestream attribute identifies the data for that column will be stored as a file in the file system in the operating system. The RDBMS manages the creation and deletion of the file in the file system. There is a 1:1 reference between the file in the file system and a cell (intersection of row and column). The data in the filestream column can be manipulated the same way as the data in other columns using a programming language, such as SQL or MICROSOFT®'"'"'s T-SQL.

Claims

1. A method of providing a database with a filestream attribute to handle large unstructured objects, comprising:

creating a filestream group associated with the database, the filestream group comprising at least one volume having filestream data associated with the database;

associating the filestream group with a table in the database, the table comprising a plurality of columns;

identifying a filestream column, from the plurality of columns in the table, that points to the filestream data; and

populating the cells in the filestream column with values associated with the large unstructured objects.

2. The method of claim 1, further comprising modifying the filestream group by one of adding and deleting a volume from the filestream group.

3. The method of claim 1, further comprising creating the database prior to creating the filestream group associated with the database.

4. The method of claim 1, wherein populating the cells comprises at least one of inserting a null value, inserting an empty value, inserting with data inline, and inserting with data copied from a pre-existing file.

5. The method of claim 1, further comprising storing the large unstructured objects as filestream data as a file in a file system external to the database.

6. The method of claim 5, wherein the file is a single contiguous file.

7. The method of claim 5, further comprising assigning a unique identifier to the file.

8. The method of claim 1, further comprising generating a pathname for each cell in the filestream column.

9. A computer-readable medium having stored thereon computer-executable instructions for performing a method of providing a database with a filestream attribute to handle large unstructured objects, the method comprising:

creating a filestream group associated with the database, the filestream group comprising at least one volume having filestream data associated with the database;

associating the filestream group with a table in the database, the table comprising a plurality of columns;

identifying a filestream column, from the plurality of columns in the table, that points to the filestream data; and

populating the cells in the filestream column with values associated with the large unstructured objects.

10. The computer-readable medium of claim 9, having further computer-executable instructions for modifying the filestream group by one of adding and deleting a volume from the filestream group.

11. The computer-readable medium of claim 9, having further computer-executable instructions for creating the database prior to creating the filestream group associated with the database.

12. The computer-readable medium of claim 9, wherein populating the cells comprises at least one of inserting a null value, inserting an empty value, inserting with data inline, and inserting with data copied from a pre-existing file.

13. The computer-readable medium of claim 9, having further computer-executable instructions for storing the large unstructured objects as filestream data as a file in a file system external to the database.

14. The computer-readable medium of claim 13, wherein the file is a single contiguous file.

15. The computer-readable medium of claim 13, having further computer-executable instructions for assigning a unique identifier to the file.

16. The computer-readable medium of claim 13, having further computer-executable instructions for generating a pathname for each cell in the filestream column.

17. A computer-readable medium having stored thereon a filestream attribute used in providing a database the ability to handle large unstructured objects, the filestream attribute identifying filestream data in a column in the database that is stored as a file, the column comprising values associated with filestream data and the large unstructured objects.

18. A computer-readable medium having stored thereon a filestream access module that stores large unstructured objects as filestream data as a file in a file system external to a database having a filestream attribute and provides an interface for manipulating the large unstructured objects in the database.

19. A database system for handling large unstructured objects, comprising:

a database server comprising at least one database having an associated filestream group, the filestream group comprising at least one volume having filestream data associated with the database, the database further having a table comprising a plurality of columns, one of said columns being a filestream column that points to filestream data; and

a filestream server which stores the large unstructured objects as the filestream data.

20. The database system of claim 19, wherein the filestream server grants and prevents access to the database.

21. The database system of claim 19, wherein the filestream server queries the database and receives a pathname for a cell in the database in response to the query.

22. The database system of claim 21, wherein the filestream server comprises a filestream agent that queries the database.

23. The database system of claim 19, further comprising a client machine which queries the database server for the filestream data.