Easily queriable software repositories

• US 8,145,673 B2
• Filed: 05/16/2007
• Issued: 03/27/2012
• Est. Priority Date: 02/16/2007
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Bloesch, Anthony C., Minium, Dennis W., Short, Keith W.
• Primary Examiner(s): Lee, Wilson
• Assistant Examiner(s): SHECHTMAN, CHERYL MARIA
• Application Number:
  US11/749,616
• Publication Number:
  US 20080201355A1
• Time in Patent Office: 1,777 Days
• Field of Search: 707/4, 707/10, 707/100, 707/203, 707/736, 707/737, 707/790, 707/796
• US Class Current: 707/796
• CPC Class Codes: G06F 16/284 Relational databases

Abstract

In one embodiment, tables are created in such a way that allows rich querying using standard database routines and other tools. This is accomplished by providing developers and repository users with a set of schema guidelines that describe how the software related items are to be categorized in the tables and how to use such tables for rich querying. For example, one such guideline provides for course-grained versioning of items (e.g., artifacts, metadata, etc.)—as opposed to the fine grained object principle of unit change found in most repository systems such as the entity-property-value scheme. The developers or providers then use these guidelines to optimally categorize, in a natural way, their metadata and other software related items for storing copies thereof in the repository.

Claims

1. In a repository environment, a method of providing entities the ability to create, manage, and/or store fine-grained metadata, artifacts, or other software related items of a domain by providing a relational model that stores these items in a way that allows rich querying using database routines and other tools, the method comprising:

exposing, via an interface stored in memory of a computer system a set of schema guidelines that describe how software related items of a schematized model of a domain are to be categorized in query tables of a repository, the software related items including both executables and metadata that describes the executables, wherein the set of schema guidelines includes two or more of;

naming guidelines;

script file guidelines;

table guidelines;

indexing guidelines;

viewing guidelines;

procedure and function guidelines;

foreign key guidelines;

query guidelines;

or cursor use guidelines;

receiving from one or more entities software related items that are organized according to one or more schema guidelines in the set of schema guidelines, the software related items being received by the repository; and

storing the organized software related items into a plurality of query tables of the repository, wherein the organized software related items are stored in rows of the query tables, wherein each row includes a container version ID field storing a container version ID that identifies a container to which the organized software related items stored in the row pertain, and wherein the containers are versioned based on when changes to the organized software related items the containers contain are made.

2. The method of claim 1, wherein a plurality of the containers are further loosely coupled by one or more soft links that represents a relationship between two fine-grained software items.

3. The method of claim 2, wherein the plurality of the containers are different versions of the software related items and the soft link is used to determine the appropriate version at query time.

4. The method of claim 1, wherein the naming guidelines include one or more of:

using lower-case characters for T-SQL keywords;

quoting identifiers with [ . . . ] style quotes;

using schema qualified names to refer to database objects;

using .Net naming conventions;

using [Id] as the name of a primary key;

using plurals for table and view names;

appending Table to repository item table names;

using singular role names for join columns;

using singular schema names;

explicitly naming constraints;

or using separate schema for each domain.

5. The method of claim 4, wherein the naming guidelines further include one or more of:

using [PK_TableName] for primary-key names;

using [Check_TableName_Column1_Column2_Description] for check constraint names;

using [Unique_TableName_KeyColumn1_KeyColumn2] for unique constraint names;

using [FK_SourceTable_SourceColumn_TargetTableName] for foreign key names;

using [IR_KeyColumn1_KeyColumn2_LeafColumn1] for relational index names;

using [IX_Column_Kind] for XML index names where Kind is one of;

Primary, Value, Path or Property;

or using [Schema_Area] for full-text index catalog names.

6. The method of claim 1, wherein the script file guidelines include using two script files per domain.

7. The method of claim 1, wherein the schema guidelines include using extended properties to copyright and conversion table domains or schema.

8. The method of claim 1, wherein the table guidelines include one or more of:

using normalized table designs;

explicitly naming primary keys, foreign keys, or indexes;

using surrogate keys;

using a big or large integer type for identity columns of the software related items where an ID will vary across instances thereof;

using a unique identifier type ID columns of repository items where the ID does not vary across repository instances;

vertically partitioning large or infrequent accessed columns into separate tables;

using explicit integrity constraints;

using XML columns based on a schema collection for hierarchical or ordered data that will be fetched as a unit;

adding a container version column to each domain table;

adding a tombstone table and associated delete trigger for each domain table;

using a xml;

lang attribute for XML entities including culture specific content;

avoiding using string types for enumerations;

storing locale specific text as references to a table and retrieve the strings with a scalar function;

or do not use text, ntext, or image column types;

restricting access to base tables to the login less users.

9. The method of claim 1, wherein the index guidelines include one or more of:

indexing join columns;

indexing foreign key columns;

indexing columns that appear in where clauses;

adding leaf columns to indexes;

not using index columns with a small number of distinct values;

creating a new full-text catalog for each group of related values;

not using schema binding;

or creating security views.

10. The method of claim 1, wherein the procedure and function guidelines include one or more of:

using updatable view instead of basic database functions;

not using schema binding;

avoiding cursors;

not allowing for change of primary key columns;

not altering identity or timestamp columns;

or using column lists in insert statements.

11. The method of claim 1, further comprising one or more of the following:

wherein foreign key guidelines include using delete triggers to enforce non-primary cascade delete actions;

wherein the query guidelines include using table aliases composed of the initial capital letters in the table'"'"'s name;

or wherein the cursor use guidelines include avoiding the user of cursors, or if cursors are used, using a standard pattern for cursors.

12. The method of claim 1, further comprising:

storing the plurality of software related items in the software repository using a universal entity-property-value schema; and

upon receiving a request to process an application associated with the domain, retrieving the plurality of software related items using the universal entity-property-value schema.

13. One or more computer readable storage media storing computer executable instructions which when executed by a processor in a computer system perform a method of creating, managing, and/or storing fine-grained metadata, artifacts, or other software related items of a domain by providing a relational model that stores these items in a way that allows rich querying using database routines and other tools, the method comprising:

exposing, via an interface stored in memory of a computer system a set of schema guidelines that describe how software related items of a schematized model of a domain are to be categorized in query tables, the software related items including both executables and metadata that describes the executables, wherein the set of schema guidelines includes two or more of;

naming guidelines;

script file guidelines;

table guidelines;

indexing guidelines;

viewing guidelines;

procedure and function guidelines;

foreign key guidelines;

query guidelines;

or cursor use guidelines;

receiving from one or more entities software related items that are organized according to one or more schema guidelines in the set of schema guidelines, the software related items being received by the repository; and

storing the organized software related items into a plurality of query tables of the repository, wherein the organized software related items are stored in rows of the query tables, wherein each row includes a container version ID field storing a container version ID that identifies a container to which the organized software related items stored in the row pertain, and wherein the containers are versioned based on when changes to the organized software related items the containers contain are made.

14. The one or more computer readable storage media of claim 13, wherein a plurality of the containers are further loosely coupled by one or more soft links that represents a relationship between two fine-grained software items.

15. The one or more computer readable storage media of claim 14, wherein the plurality of the containers are different versions of the software related items and the soft link is used to determine the appropriate version at query time.

16. The one or more computer readable storage media of claim 13, further comprising:

storing the plurality of software related items in the software repository using a universal entity-property-value schema; and

upon sending a request to process an application associated with the domain, receiving the plurality of software related items using the universal entity-property-value schema.