Adaptive multi-client saas database

Abstract

Methods and systems for Elastic Parallel Relational Database Management Systems. More particularly, systems, methods, technologies and mechanisms are provided herein for providing virtualization of data in a database through the mechanism of adaptive multi-client database objects. Applications define databases and objects that are either client-private, multi-client or system-wide. Transparent manipulation of these objects is provided including addition, querying, deletion, and truncation of data, and seamless manipulation of the objects themselves through creation, modification, and deletion. Schema decorations and query modification techniques ensure each client application only sees the data that it should be seeing.

1 . A database management system comprising:
 a. a network interface, for receiving database queries from two or more client applications, the client applications operating on two or more user client computers, the system providing a least one connection into the system for each such client application,   b. a group of two or more operational nodes for executing the queries as database operations, each operational node implemented as a logical collection of software components that execute on one or more physical machines,   c. the system further storing user data in tables, and grouping the tables into databases,   d. the tables being further assigned as multi-client tables, system-wide tables and client-private tables,   e. the system further using one or more list(s) of known-tables and known-databases to track the tables and databases stored by client applications in the database management system,   f. wherein each list of known-databases is associated with a namespace, there being at least one namespace serving as a global namespace that is system-wide among all databases,   g. data from multiple client applications is stored together in a single multi-client table along with schema-decorations to distinguish data from each client application,   h. a state is associated with each connection to the system, and   i. one aspect of the state is the currently chosen database.   
     
     
         2 . The system of  claim 1  wherein the queries comprise a ‘CREATE DATABASE’ command, and ‘USE <DATABASE>’ command and further wherein
 a. the system provides a mechanism by which a client application is able to create a new database, or specify the database to use for subsequent query processing, 
 b. database creation involves creating an entry in the list of known-databases, and 
 c. the ‘USE’ command chooses a database for the current database connection, and the database choice is recorded on the state associated with the connection. 
 
     
     
         3 . The system of  claim 1  and further comprising aspects of specification of database for a table wherein
 a. if a table is qualified with a database name, then the explicit database specification is validated against the list of known-databases, and 
 b. the current-database associated with the connection is used to qualify a table name if none is provided. 
 
     
     
         4 . The system of  claim 1  wherein creation of a table is further such that:
 a. a client application issues a request to create a table, 
 b. the request to create a table optionally provides an indication of whether the table is to be a client-private, multi-client or system-wide table, and 
 c. a default behavior for assignment as a client-private, multi-client, or system-wide table is assumed if the request does not specify whether the table is to be either client-private, multi-client or system-wide table. 
 
     
     
         5 . The system of  claim 1  wherein creation of a multi-client table is such that
 a. the system inspects the list of known-tables in the associated namespace to determine if there is an existing table with a compatible schema that can be used to store data for the multi-client table to be created, 
 b. and if such a table is found then an entry is made in the list of known tables and the user data is stored in that multi-client table, 
 c. and if not then a new multi-client table is created, 
 d. and where the successful completion of an operation to create the table is recorded in the list of known-tables in the associated namespace. 
 
     
     
         6 . The system of  claim 1  wherein alteration of a multi-client table is further such that
 a. the system attempts to find an existing multi-client table with a compatible schema to the proposed new schema, and if a suitable multi-client table with a compatible schema exists, then the data is stored in that multi-client table, 
 b. and if not a new multi-client table with the proposed schema is created. 
 
     
     
         7 . The system of  claim 1  whereby the system further inspects the schemas of two tables and determines whether they constitute compatible schemas or not, and where that inspection is based on a comparison of the columns, their names, and other attributes of the table metadata. 
     
     
         8 . The system of  claim 1  further wherein the system interprets queries to be either related to one specified client application, one or more specified client applications, or all client applications. 
     
     
         9 . The system of  claim 8  further wherein the system rewrites a query when referencing a multi-client table including the possible addition of restrictions and filters, based on schema decorations, to only access data for the appropriate client application(s). 
     
     
         10 . The system of  claim 8  further comprising truncation of data in a multi-client table whereby the system deletes data for the current client application in the specified multi-client table. 
     
     
         11 . The system of  claim 8  further comprising dropping a multi-client table whereby the system deletes all data for the appropriate client(s) in the specified multi-client table, or deletes the appropriate entry in the list of known-tables in the associated namespace. 
     
     
         12 . The system of  claim 1  whereby wherein alteration of a multi-client table is further such that the system attempts to determine that at least a minimum number of candidate tables with compatible schemas exist before data is stored together in a multi-client table. 
     
     
         13 . The system of  claim 1  whereby an EPRDBMS implements the system and associate the storage of tables with storage nodegroups. 
     
     
         14 . The system of  claim 13  whereby compatibility of two schemas is determined based upon whether or not the candidate tables share the same storage nodegroup. 
     
     
         15 . The system of  claim 1  further wherein the system converts a table in the list of known-tables from a multi-client table to a system-wide table or a client-private table, or a system-wide table to a multi-client table or a client-private table, or a client-private table to a multi-client table or a system-wide table. 
     
     
         16 . A database management system comprising:
 a. a network interface, for receiving database queries from two or more client applications, the client applications operating on two or more user client computers, the system providing a least one connection into the system for each such client application,   b. a group of two or more operational nodes for executing the queries as database operations, each operational node implemented as a logical collection of software components that execute on one or more physical machines,   c. the system further storing user data in tables, and grouping the tables into databases,   d. the tables being further assigned as multi-client tables, system-wide tables and client-private tables,   e. the system further using one or more list(s) of known-tables and known-databases to track the tables and databases stored by client applications in the database management system,   f. wherein each list of known-databases is associated with a namespace, there being at least one namespace serving as a global namespace that is system-wide among all databases,   g. data from multiple client applications is stored together in a single multi-client table along with schema-decorations to distinguish data from each client application,   h. a state is associated with each connection to the system and one aspect of the state is the currently chosen database,   i. wherein the queries comprise a ‘CREATE DATABASE’ command, and ‘USE <DATABASE>’ command by which a client application can create a new database, or can specify the database to use for subsequent query processing, wherein database creation involves creating an entry in the list of known-databases, and specifying a database chooses a database for the current database connection, and the database choice is recorded on the state associated with the connection   j. wherein the system specifies a database for a table such that a table is qualified with a database name, and if so an explicit database specification is validated against the list of known-databases, and the current-database associated with the connection is used to qualify a table name if none is provided,   k. wherein creation of a table is further such that   a client application issues a request to create a table, the request to create a table provides an indication of whether the table is to be a client-private, multi-client or system-wide table, and a default behavior for assignment as a client-private, multi-client, or system-wide table is assumed if the request does not specify whether the table is to be either client-private, multi-client or system-wide table,   l. wherein creation of a multi-client table is such that the system inspects the list of known-tables in the associated namespace to determine if there is an existing table with a compatible schema that is able to store data for the multi-client table to be created, and if such a table is found then an entry is made in the list of known tables and the user data is stored in that multi-client table, and if not then a new multi-client table is created, and where successful completion of an operation to create the table is recorded in the list of known-tables in the associated namespace,   m. wherein alteration of a multi-client table is further such that the system attempts to find an existing multi-client table with a compatible schema to the proposed new schema, and if a suitable multi-client table with a compatible schema exists, then the data is stored in that multi-client table, and if not a new multi-client table with the proposed schema is created,   n. wherein the system further inspects the schemas of two tables and determines whether they constitute compatible schemas or not, and where that inspection is based on a comparison of the columns, their names, and other attributes of the table metadata,   o. wherein the system interprets queries to be either related to one specified client application, one or more specified client applications, or all client applications, and
 i.) further rewriting a query when referencing a multi-client table including the possible addition of restrictions and filters, based on schema decorations, to only access data for the appropriate client application(s) 
 ii.) further truncating data in a multi-client table whereby the system delete data for the current client application in the specified multi-client table 
 iii.) further dropping a multi-client table whereby the system delete all data for the appropriate client(s) in the specified multi-client table, or delete the appropriate entry in the list of known-tables in the associated namespace, 
   p. wherein the system further determine that at least a minimum number of candidate tables with compatible schemas exist before data is stored together in a multi-client table,   q. wherein an EPRDBMS implements the system and associates the storage of tables with storage nodegroups,   r. wherein the compatibility of two schemas depends upon whether or not the candidate tables share the same storage nodegroup, and   s. wherein the system converts a table in the list of known-tables from a multi-client table to a system-wide table or a client-private table, or a system-wide table to a multi-client table or a client-private table, or a client-private table to a multi-client table or a system-wide table.   
     
     
         17 . A method for operating a database management system comprising:
 a. receiving database queries from two or more client applications via a network interface, the client applications operating on two or more user client computers, thereby providing a least one connection into the system for each such client application,   b. executing the queries as database operations on a group of two or more operational nodes, each operational node implemented as a logical collection of software components that execute on one or more physical machines,   c. storing user data in tables, and grouping the tables into databases,   d. assigning the tables as multi-client tables, system-wide tables and client-private tables,   e. tracking the tables and databases stored by client applications via one or more list(s) of known-tables and known-databases,   f. associating each list of known-databases with a namespace, there being at least one namespace serving as a global namespace that is system-wide among all databases,   g. storing data from multiple client applications in a single multi-client table along with schema-decorations thereby distinguishing data from each client application,   h. associating a state with each connection to the system, and   i. wherein one aspect of the state is the currently chosen database.   
     
     
         18 . The method of  claim 17  wherein the queries comprise a ‘CREATE DATABASE’ command, and ‘USE <DATABASE>’ command and further comprising:
 a. a client application requesting creation of a new database, or specifying the database to use for subsequent query processing, 
 b. the ‘CREATE DATABASE’ command creating an entry in the list of known-databases, 
 c. the ‘USE’ command comprises choosing a database for the current database connection, and recording the database choice on the state associated with the connection. 
 
     
     
         19 . The method of  claim 17  and further comprising aspects of specifying a database for a table further comprising:
 a. qualifying a table with a database name, and if so, validating an explicit database specification against the list of known-databases, and 
 b. using the current-database associated with the connection to qualify a table name if none is provided. 
 
     
     
         20 . The method of  claim 17  further comprising:
 a. receiving a request from a client application to create a table, 
 b. the request to create a table indicating whether the table is to be a client-private, multi-client or system-wide table, and 
 c. assuming a default behavior for assignment as a client-private, multi-client, or system-wide table if the request does not specify whether the table is to be either client-private, multi-client or system-wide table. 
 
     
     
         21 . The method of  claim 17  further comprising creating a multi-client table by the steps of:
 a. inspecting the list of known-tables in the associated namespace to determine if there is an existing table with a compatible schema that be used to store data for the multi-client table to be created, 
 b. and if such a table is found then making an entry in the list of known tables and storing the user data in that multi-client table, 
 c. and if not then creating a new multi-client table, 
 d. and recording the successful completion of an operation to create the table in the list of known-tables in the associated namespace. 
 
     
     
         22 . The method of  claim 17  further comprising altering a multi-client table by the steps of:
 a. attempting to find an existing multi-client table with a compatible schema to the proposed new schema, and if a suitable multi-client table with a compatible schema exists, then storing the data in that multi-client table, 
 b. and if not then creating a new multi-client table with the proposed schema. 
 
     
     
         23 . The method of  claim 17  further comprising:
 inspecting the schemas of two tables and determining whether they constitute compatible schemas or not, and where that inspecting is based on comparing the columns, their names, and other attributes of the table metadata. 
 
     
     
         24 . The method of  claim 17  further comprising:
 interpreting queries to be either related to one specified client application, one or more specified client applications, or all client applications. 
 
     
     
         25 . The method of  claim 24  further comprising:
 query rewriting when referencing a multi-client table including the possible adding of restrictions and filters, based on schema decorations, to only access data for the appropriate client application(s). 
 
     
     
         26 . The method of  claim 24  further comprising:
 truncating data in a multi-client table including deleting data for the current client application in the specified multi-client table. 
 
     
     
         27 . The method of  claim 24  further comprising:
 dropping a multi-client table including deleting all data for the appropriate client(s) in the specified multi-client table, or deleting the appropriate entry in the list of known-tables in the associated namespace. 
 
     
     
         28 . The method of  claim 17  further comprising:
 determining that at least a minimum number of candidate tables with compatible schemas exist before storing data together in a multi-client table. 
 
     
     
         29 . The method of  claim 17  further comprising:
 implementing the system with an EPRDBMS and associating the storage of tables with storage nodegroups. 
 
     
     
         30 . The method of  claim 29  further comprising:
 determining compatibility of two schemas based upon whether or not the candidate tables share the same storage nodegroup. 
 
     
     
         31 . The method of  claim 17  further comprising:
 converting a table in the list of known-tables from a multi-client table to a system-wide table or a client-private table, or a system-wide table to a multi-client table or a client-private table, or a client-private table to a multi-client table or a system-wide table.