Database schema for structured query language (SQL) server

• US 7,653,652 B2
• Filed: 08/17/2004
• Issued: 01/26/2010
• Est. Priority Date: 11/17/2000
• Status: Expired due to Fees

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Thompson, John Patrick, Kagalwala, Raxit A.
• Primary Examiner(s): Fleurantin; Jean B
• Assistant Examiner(s): Ly; Anh
• Application Number:
  US10/919,682
• Publication Number:
  US 20050027725A1
• Time in Patent Office: 1,988 Days
• Field of Search: 707/10, 707100-1041, 717/100
• US Class Current: 1/1
• CPC Class Codes: G06F 16/211 Schema design and management
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure

Abstract

A schema for a SQL (structured query language) database defines classes, properties, methods, and associations.

Claims

1. A computer comprising:

memory configured to store an operating system, the operating system having an object-oriented management service to monitor, configure, and control systems, services, and applications represented by objects; and

a schema for a structured query language (SQL) database maintained by the object-oriented service of the operating system, the schema being configured to represent a SQL database system for the object-oriented management service and to permit manipulation of classes to model the objects that represent systems, services, and applications, the classes include;

database objects class that represents components of the SQL database system;

application system class that represents installation parameters of the SQL database system;

security class that represents security features pertaining to use of the SQL database system;

physical storage class that represents physical files and file groups used by the SQL database system to store data;

bulk data class that represents bulk data operations with the data in the SQL database.

2. A computer as recited in claim 1, wherein:

the database objects class comprises multiple classes representing tables, views, stored procedures, indexes, constraints, and keys;

the application system class comprises multiple classes representing settings, services, and transaction and error logs used by the database system; and

the security class comprises multiple classes representing users, roles, authentication login, and permissions for the users and the roles.

3. A computer as recited in claim 2, wherein permissions are modeled as association classes between database objects class and the users and roles.

4. A computer comprising:

memory configured to store an operating system, the operating system having an object-oriented management service to monitor, configure and control systems, services, and applications that are represented as objects; and

a schema for a structured query language (SQL) database maintained by the object-oriented service of the operating system, the schema being configured to represent the SQL database for the object-oriented management service and to permit manipulation of classes to model the objects that represent systems, services, and applications, the classes include;

a SQL setting class to represent settings that are used to configure an installation of the database;

a SQL database setting class to represent operational settings for the database;

a SQL configuration value class to represent configuration values;

a SQL language setting class to expose properties of a language record;

a SQL registry setting class to represent the installation and run-time parameters stored in the registry;

a SQL server connection setting class to represent default connection settings;

a SQL objects class to represent objects in a SQL database;

a SQL database class to represent instances of the SQL database;

a SQL server class to represent instances of a SQL server;

a SQL server configuration value class to associate a SQL server installation and the configured value settings for the installation;

a SQL server language setting class to associate a SQL server installation and its language settings;

a SQL server registry setting class to associate a SQL server installation and its registry setting; and

a SQL server database class to associate a SQL server installation and a database that is part of the installation.

5. A computer as recited in claim 4, wherein the SQL objects class comprises at least one of the following properties:

a name property that defines a label by which an object in the objects class is known; and

a status property to indicate a current status of the object.

6. A computer as recited in claim 4, wherein the SQL database class comprises at least one of the following properties:

a collation property to specify a column-level collation of a string datatype in the database;

a create-for-attach property to control database file creation;

a database status property to indicate a current operational status on the database;

a name property to define a label by which an object is known;

a primary file path property to return a path and name of an operating system directory containing a primary file for the database;

a size property to expose a total size of the database;

a space available property that returns an amount of disk resource allocated and unused; and

a status property to indicate a current status of the object.

7. A computer as recited in claim 4, wherein the SQL server class comprises at least one of the following properties:

a collation property to specify a column-level collation of a string datatype in the database;

a name property to define a label by which an object is known;

a status property to indicate a current status of the object; and

a user profile property to return a high-level role description for a login used by a current connection.

8. A computer comprising:

memory configured to store an operating system having an object-oriented management service to monitor, configure and control systems, services, and applications represented by objects; and

a schema for a structured query language (SQL) database maintained by the object-oriented service of the operating system, the schema being configured to represent the SQL database for the object-oriented management system in an object-oriented manner and to manipulate classes included in the SQL database to model the objects that represent systems, services, and applications to the operating system, the classes include;

a SQL error log class to represent at least one error log;

a SQL error log entry class to represent entries in the error log;

a SQL transaction log to represent a transaction log in the SQL database;

a SQL objects class to represent objects in the SQL database;

a SQL database class to represent instances of the SQL database;

a

SQL server class to represent instances of a SQL Server;

a SQL error log error log entry class to represent an association between the error log and an entry in the error log;

a SQL server error log class to represent an association between a server installation and the error log used by the installation;

a SQL error log data file class to represent an association between the error log and an operating system file used to store the error log;

a SQL transaction log data file class to represent an association between the transaction log and an operating system file that is used to store the log;

a SQL database transaction log class to represent an association between the database and the transaction log for the database; and

a SQL server database class to associate a SQL server installation and a database that is part of the installation.

9. A computer as recited in claim 8, wherein the SQL error log class comprises at least one of the following properties:

a last modified property to indicate a time and date that the error log was last modified; and

a status property to indicate a current status of an object in the database.

10. A computer as recited in claim 8, wherein the SQL objects class comprises at least one of the following properties:

a property that defines a label by which an object in the objects class is known; and

a status property to indicate a current status of the object.

11. A computer as recited in claim 8, wherein the SQL database class comprises at least one of the following properties:

a collation property to specify a column-level collation of a string datatype in the database;

a create-for-attach property to control database file creation;

a database status property to indicate a current operational status on the database;

a name property to define a label by which an object is known;

a primary file path property to return a path and name of an operating system directory containing a primary file for the database;

a size property to expose a total size of the database;

a space available property that returns an amount of disk resource allocated and unused; and

a status property to indicate a current status of the object.

12. A computer comprising:

an operating system having an object-oriented management service to monitor, configure and control systems, services and applications represented by objects;

memory to store the operating system; and

a schema for a structured query language (SQL) database maintained by the object-oriented service of the operating system, the schema being configured to represent the SQL database for the object-oriented management service and to manipulate classes included in the SQL database to model the objects that represent the control systems, services, and applications, the classes include;

a SQL objects class to represent objects in a database;

a SQL database class to represent instances of a SQL database;

a SQL table class to represent at least one table in the SQL database;

a SQL column class to represent a column in the table;

a SQL trigger class to represent a trigger to be executed when a specified data modification is attempted on the table;

a SQL user defined function class to represent a user defined function in the SQL database;

a SQL stored procedure class to represent stored procedures defined in the SQL database;

a SQL view class to represent view tables in the SQL database;

a SQL stored procedure parameter class to represent input and output parameters of a stored procedure;

a SQL table column class to represent an association between the table and the column contained in the table;

a SQL table trigger class to represent an association between the table and the trigger defined for the table;

a SQL database table class to represent an association between the database and the table contained in the SQL database;

a SQL database user defined function class to represent an association between the database and the user-defined function defined within the SQL database;

a SQL database view class to represent an association between the SQL database and the view;

a SQL database stored procedure class to represent an association between the database and a stored procedure defined within the database; and

a SQL stored procedure stored procedure parameter class associates a stored procedure to a parameter used in the stored procedure.

13. A computer as recited in claim 12, wherein the SQL objects class comprises at least one of the following properties:

a name property that defines a label by which an object in the objects class is known; and

a status property to indicate a current status of the object.

14. A computer as recited in claim 12, wherein the SQL database class comprises at least one of the following properties:

a collation property to specify a column-level collation of a string datatype in the database;

a create-for-attach property to control database file creation;

a database status property to indicate a current operational status on the database;

a name property to define a label by which an object is known;

a primary file path property to return a path and name of an operating system directory containing a primary file for the database;

a size property to expose a total size of the database;

a space available property that returns an amount of disk resource allocated and unused; and

a status property to indicate a current status of the object.

15. A computer as recited in claim 12, wherein the SQL table class comprises at least one of the following properties:

an attributes property to indicate various aspects of the table;

a database name property to indicate a name of the database of which the table is part;

a data space used property to report storage space used by rows of the table;

an index space used property that returns a quantity of disk resource used to store indexes;

a rows property that returns a number of rows in the table; and

a status property to indicate a current status of the table.

16. A computer as recited in claim 12, wherein the SQL column class comprises at least one of the following properties:

a computed property that indicates whether the column is computed based on other values in the database;

a datatype property that indicates a datatype for the column;

an identity property that indicates whether the column is an identity column for the table;

a length property that indicates a maximum number of characters or bytes accepted by the column;

a table name property that indicates a name of the table that a key is defined in; and

a status property to indicate a current status of the table.

17. A computer comprising:

an operating system having an object-oriented management service to monitor, configure and control systems, services and applications represented by objects, the operating system being configured to execute on one or more processors included in the computer; and

a schema for a structured query language (SQL) database maintained by the object-oriented service of the operating system, the schema being configured to represent the SQL database for the object-oriented management service and to permit manipulation of classes to model the objects that represent systems, services, and applications, the classes that include;

a SQL objects class to represent objects in a database;

a SQL column class to represent a column in the table;

a SQL index class to represent an index for a table; and

a SQL index column class to represent an association between the index and the column.

18. A computer comprising:

an operating system having an object-oriented management service to monitor, configure and control systems, service, and applications represented by objects, the operating system being stored in memory included in the computer; and

a schema for a structured query language (SQL) database maintained by the object-oriented service of the operating system, the schema being configured to represent the SQL database for the object-oriented management and to permit manipulation of classes to model the objects that represent systems, services, and applications, the classes include;

a SQL objects class to represent objects in a database;

a SQL constraint class to represent constraints defined in the SQL database;

a SQL table class to represent at least one table in the SQL database;

a SQL default class to represent attributes of a single SQL server default;

a SQL column class to represent a column in the table;

a SQL database class to represent instances of a SQL database;

a SQL datatype class to represent datatypes defined in a SQL server installation, the SQL datatype class having two subclasses that represent user data and system data;

a SQL check class to represent check attributes of a SQL server integrity constraint;

a SQL rule class to represent a single data-integrity rule;

a SQL table check class to represent an association between the table and the check attributes defined for the table;

a SQL user datatype default class to represent an association between a user-defined datatype and a rule bound to the column;

a SQL database default class to represent an association between the database and the defaults defined within the database;

a SQL column rule class to represent an association between the column and the rule bound to the column;

a SQL column datatype class to associate the column with its data type;

a SQL database rule class to represent an association between the database and the rules defined within the database;

a SQL user datatype rule class represents an association between the user defined datatype and the rule bound to the column;

a SQL database datatype class associates the database to the datatypes defined within the database;

a SQL base datatype class represents an association between the user-defined datatype and the system datatype; and

a SQL column default class to associate the column to the default for the column.

19. A computer comprising:

an operating system having an object-oriented management service to monitor, configure and control systems, services, and applications represented by objects, the operating system being configured to execute on one or more processors included in the computer; and

a schema for a structured query language (SQL) database maintained by the object-oriented service of the operating system, the schema being configured to represent the SQL database to the object-oriented management service and to permit manipulation of classes to model the objects that represent systems, services, and applications, the classes include;

a SQL objects class to represent objects in a database;

a SQL constraint class to represent constraints defined in the SQL database;

a SQL table class to represent at least one table in the SQL database;

a SQL column class to represent a column in the table;

a SQL database class to represent instances of a SQL database;

a SQL key class to represent keys defined for the table, the SQL key class having two subclasses that represent candidate keys in the table and foreign keys in the table, the candidate key subclass having two subclasses that represent unique keys and primary keys;

a SQL key column class to represent an association between a key and the column that is part of the key;

a SQL table key class to represent an association between the table and the key defined for the table;

a SQL referenced table class to represent an association between the foreign key and the table that contains the primary key referenced by the foreign key;

a SQL database candidate key class to represent an association between the database and the candidate key that is present in one of the tables in the database; and

a SQL referenced key class to represent an association between the foreign key and the candidate key that the foreign key references.

20. A computer comprising:

an operating system having an object-oriented management service to monitor, configure and control systems, services, and applications represented by objects;

memory configured to store the operating system; and

a schema for a structured query language (SQL) database maintained by the object-oriented service of the operating system, the schema being configured to represent the SQL database to the object-oriented management system and to permit manipulation of classes to model the objects that represent systems, services, and applications, the classes include;

a SQL setting class to represent settings that are used to configure an installation of the database;

a SQL objects class to represent objects in a SQL database;

a SQL database class to represent instances of the SQL database;

a SQL server class to represent instances of a SQL server;

a SQL integrated security setting class to represent integrated security settings;

a SQL DBMS user object class to represent objects related to user authentication;

a SQL user class to represent users of the SQL database;

a SQL login class to represent logins of the SQL database;

a SQL role class to represent groups of users with similar security attributes of the SQL database, the SQL role class having two subclasses to represent a SQL server security role not constrained to operation within a single database and to represent properties of a SQL server database role;

a SQL server integrated security setting class to represent an association between a SQL server installation and its security settings;

a SQL server login class to represent an association between the SQL server and a login defined within the SQL Server;

a SQL server role class to represent an association between the SQL server and server roles defined within the SQL Server;

a SQL server user class to represent an association between the SQL server and a database user;

a SQL database login class to represent an association between a database and a login that is mapped to a user defined in the database;

a SQL database owner login class to represent an association between a database and the login mapped to the user that owns the database;

a SQL user login class to represent an association between a database user and the login used to authenticate the user;

a SQL member login class to represent an association between a SQL Server role and a login that is a member of the role;

a SQL login default database class to represent an association between a login and the default database for the login;

a SQL member user class to represent an association between a database role and a user that is a member of the role;

a SQL DBMS object owner class to represent an association between a SQL server database object and the user who owns the object;

a SQL database user class to represent an association between a database and a user defined for the database;

a SQL member database role class to associate two database roles; and

a SQL database role class to associate a database role to the database within which the role is defined.

21. A computer comprising:

memory to store an operating system, the operating system having an object-oriented management service to monitor, configure and control systems, services, and applications represented by objects; and

a schema for a structured query language (SQL) database maintained by the object-oriented service of the operating system, the schema being configured to represent the SQL database for the object-oriented management service and to permit manipulation of classes to model the objects that represent systems, services, and application, the classes include;

a SQL objects class to represent objects in a SQL database;

a SQL database class to represent instances of the SQL database;

a SQL stored procedure class to represent stored procedures defined in the SQL database;

a SQL table class to represent at least one table in the SQL database;

a SQL user defined function class to represent a user defined function in the SQL database

a SQL view class to represent view tables in the SQL database;

a SQL DBMS user object class to represent objects related to user authentication;

a SQL user class to represent users of the SQL database;

a SQL role class to represent groups of users with similar security attributes of the SQL database, the SQL role class having two subclasses to represent a SQL server security role not constrained to operation within a single database and to represent properties of a SQL server database role;

a SQL database role database permission class to represent permissions that a database role has for the database in which it is defined;

a SQL user database permission class to represent permissions granted to a user for a database;

a SQL user stored procedure permission class to represent permissions granted to a user for a stored procedure;

a SQL database role stored procedure permission class to represent permissions that a database role has for a stored procedure;

a SQL user table permission class to represent permissions granted to a user for a table;

a SQL database role table permission to represent the permissions that a database role has for a table;

a SQL user defined function permission to represent permissions granted to a user for a stored procedure;

a SQL database role user defined function permission to represent permissions that a database role has for a table; and

a SQL user view permission to represent permissions granted to a user for a view.

22. A computer comprising:

an operating system having an object-oriented management service to monitor, configure and control systems, services, and applications represented by objects; and

a schema for a structured query language (SQL) database maintained by the object-oriented service of the operating system, the schema being configured to represent the SQL database for the object-oriented management service and to permit manipulation of classes to model the objects that represent systems, services, and applications, the classes include;

a SQL database class to represent instances of the SQL database;

a SQL table class to represent at least one table in the SQL database;

a SQL index class to represent an index for a table; and

a SQL key class to represent keys defined for the table, the SQL key class having a subclass that represents candidate keys in the table;

a SQL file group class to represent attributes of a SQL server file group;

a SQL extension class to represent extensions made via associations to a managed system element;

a SQL extends class to associate a first class with a second class that extends the first class by defining new properties and methods;

a SQL file group database file class to associate a database file group to operating system files that are part of the group;

a SQL index file group class to represent an association between an index and a file group that stores the index;

a SQL key file group class to represent an association between a key and a file group used to store the key;

a SQL table file group class to represent an association between a table and file groups used to store the table;

a SQL table text file group class to associate a table with a file group; and

a SQL database file group class to represent an association between a database and a file group that contains operating system files that store data for the database.

23. One or more computer-readable storage media comprising an operating system that is executable to provide:

an object-oriented management service to monitor, configure, and control systems, services, and applications represented by objects;

an object-oriented database schema for a SQL (structured query language) database maintained by the object-oriented service, the an object-oriented database schema being configured to represent the systems, services, and applications as objects and to permit manipulation of classes to model the objects that represent the systems, services, and application, the classes include;

database objects classes that represent components of a SQL database;

application system classes that represent settings, services, and transaction and error logs used by the SQL database;

security classes that represent users, roles, authentication login, and permissions for the users and the roles to use the SQL database; and

physical storage classes that represent physical files and file groups used by the database system to store data.

24. One or more computer-readable media as recited in claim 23, wherein:

the database objects class comprises multiple classes representing tables, views, stored procedures, indexes, constraints, and keys;

the application system class comprises multiple classes representing settings, services, and transaction and error logs used by the database system; and

the security class comprises multiple classes representing users, roles, authentication login, and permissions for the users and the roles.

25. One or more computer-readable media as recited in claim 24, wherein permissions are modeled as association classes between database objects class and the users and roles.

26. One or more computer-readable media as recited in claim 23, wherein the database objects classes comprises at least one of the following classes:

an objects class to represent objects in the SQL database;

a database class to represent instances of the SQL database;

a server class to represent instances of a SQL server;

a table class to represent at least one table in the SQL database;

a column class to represent a column in the table;

a key class to represent keys defined for the table;

a user defined function class to represent a user defined function in the SQL database;

a stored procedure class to represent stored procedures defined in the SQL database; and

a view class to represent view tables in the SQL database.

27. One or more computer-readable media as recited in claim 23, wherein the application system classes comprises at least one of the following classes:

a setting class to represent settings that are used to configure an installation of the SQL database;

a database setting class to represent operational settings for the SQL database;

a configuration value class to represent configuration values;

a language setting class to expose properties of a language record;

a registry setting class to represent the installation and run-time parameters stored in the registry; and

a server connection setting class to represent default connection settings.

28. One or more computer-readable media as recited in claim 23, wherein the security classes comprises at least one of the following classes:

an integrated security setting class to represent integrated security settings;

a user class to represent users of the SQL database;

a login class to represent logins of the SQL database; and

a role class to represent groups of users with similar security attributes of the SQL database.

29. One or more computer-readable media as recited in claim 23, wherein the physical storage classes comprises at least one of the following classes:

a file group class to represent attributes of a SQL server file group;

a extension class to represent extensions made via associations to a managed system element;

a extends class to associate a first class with a second class that extends the first class by defining new properties and methods;

a file group database file class to associate a database file group to operating system files that are part of the group;

a index file group class to represent an association between an index and a file group that stores the index;

a key file group class to represent an association between a key and a file group used to store the key;

a table file group class to represent an association between a table and file groups used to store the table;

a table text file group class to associate a table with a file group; and

a database file group class to represent an association between a database and a file group that contains operating system files that store data for the database.