Database schema for structured query language (SQL) server
Saved in:
| Title: | Database schema for structured query language (SQL) server |
|---|---|
| Patent Number: | 7,653,652 |
| Publication Date: | January 26, 2010 |
| Appl. No: | 10/919682 |
| Application Filed: | August 17, 2004 |
| Abstract: | A schema for a SQL (structured query language) database defines classes, properties, methods, and associations. |
| Inventors: | Kagalwala, Raxit A. (Issaquah, WA, US); Thompson, John Patrick (Seattle, WA, US) |
| Assignees: | Microsoft Corporation (Redmond, WA, US) |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 3. A computer as recited in claim 2 , wherein permissions are modeled as association classes between database objects class and the users and roles. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Claim: | 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. |
| Current U.S. Class: | 707/103R |
| Patent References Cited: | 5295256 March 1994 Bapat 5499371 March 1996 Henninger et al. 5504885 April 1996 Alashqur 5569207 October 1996 Gisselberg et al. 5596745 January 1997 Lai et al. 5596746 January 1997 Shen et al. 5692129 November 1997 Sonderegger et al. 5742813 April 1998 Kavanagh et al. 5765159 June 1998 Srinivasan 5794030 August 1998 Morsi 5799309 August 1998 Srinivasan 5832498 November 1998 Exertier 5850544 December 1998 Parvathaneny et al. 5873093 February 1999 Williamson et al. 5893108 April 1999 Srinivasan et al. 5937402 August 1999 Pandit 5937409 August 1999 Wetherbee 5956725 September 1999 Burroughs et al. 5956730 September 1999 Burroughs et al. 5963939 October 1999 McCann et al. 6012067 January 2000 Sarkar 6067584 May 2000 Hayles et al. 6076090 June 2000 Burroughs et al. 6081808 June 2000 Blackman et al. 6085198 July 2000 Skinner et al. 6101502 August 2000 Heubner et al. 6112210 August 2000 Nori et al. 6122641 September 2000 Williamson et al. 6125363 September 2000 Buzzeo et al. 6134559 October 2000 Brumme et al. 6157928 December 2000 Sprenger et al. 6163776 December 2000 Periwal 6169993 January 2001 Shutt et al. 6170005 January 2001 Meandzija 6192371 February 2001 Schultz 6243709 June 2001 Tung 6279008 August 2001 Tung Ng et al. 6289339 September 2001 Weber 6317748 November 2001 Menzies et al. 6330555 December 2001 Weber 6356946 March 2002 Cleggs et al. 6374252 April 2002 Althoff et al. 6374253 April 2002 Weider et al. 6374256 April 2002 Ng et al. 6405202 June 2002 Britton et al. 6418448 July 2002 Sarkar 6437559 August 2002 Zajac et al. 6487552 November 2002 Lei et al. 6493719 December 2002 Booth et al. 6496833 December 2002 Goldberg et al. 6529910 March 2003 Fleskes 6569207 May 2003 Sundaresan et al. 6571232 May 2003 Goldberg et al. 6574635 June 2003 Stauber et al. 6591272 July 2003 Williams 6631519 October 2003 Nicholson et al. 6665677 December 2003 Wotring et al. 6681383 January 2004 Pastor et al. 6701321 March 2004 Tsai 6704744 March 2004 Williamson et al. 6704747 March 2004 Fong 6711582 March 2004 Aldridge et al. 6754670 June 2004 Lindsay et al. 6769124 July 2004 Schoening et al. 6850983 February 2005 Rezaiifar et al. 7024656 April 2006 Ahad 7058958 June 2006 Shutt et al. 7072934 July 2006 Helgeson et al. 7089583 August 2006 Mehra et al. 7127474 October 2006 Williamson et al. 7152070 December 2006 Musick et al. 2001/0034733 October 2001 Prompt et al. 2002/0023261 February 2002 Goodwin et al. 2002/0049603 April 2002 Mehra et al. 2002/0049749 April 2002 Helgeson et al. 2002/0059293 May 2002 Hirsch 2002/0107872 August 2002 Hudis 2002/0116385 August 2002 Kagalwala et al. 2002/0116698 August 2002 Lurie et al. 2002/0120859 August 2002 Lipkin et al. 2002/0156790 October 2002 Kagalwala et al. 2002/0161734 October 2002 Stauber et al. 2002/0194155 December 2002 Aldridge et al. 2003/0088576 May 2003 Hattori et al. 2003/0120675 June 2003 Stauber et al. 2003/0212705 November 2003 Williamson et al. 2006/0020586 January 2006 Prompt et al. 2006/0173873 August 2006 Prompt et al. 2007/0094302 April 2007 Williamson et al. |
| Other References: | “Oracle Database Administration, The Essential Reference” by David C. Kreines, Brian Laskey from Safari Tech Books Online (pub. Date Apr. 1999, pp. 1-577—ISBN: 1-56592-516-5. cited by examiner “Definition of derived classes in OODBs”—Samos et al.—Database Engineering and Applications Symposium, 1998, Jul. 8-10, 1998 (pp. 150-158). cited by examiner “Data model issues for object-oriented applications”—Banerjee et al.—ACM transactions on Information Systems (TOIS), vol. 5, Issues 1, Jan. 1987 (pp. 3-26). cited by examiner “A study of three alternative Workstation-server Architectures for Object-Oriented database Systems”—DeWitt et al.—The graduate School of the University of Wisconsin , Proceedings of 16th international conference, Mar. 1990 (pp. 1-35). cited by examiner “Final Office Action”, U.S. Appl. No. 09/789,804, filed Apr. 29, 2009,28 pages. cited by other “Advisory Action”, U.S. Appl. No. 09/789,804, (Aug. 10, 2009),3 pages. cited by other |
| Assistant Examiner: | Ly, Anh |
| Primary Examiner: | Fleurantin, Jean B |
| Accession Number: | edspgr.07653652 |
| Database: | USPTO Patent Grants |
| Abstract: | A schema for a SQL (structured query language) database defines classes, properties, methods, and associations. |
|---|