The invention pertains to databases and their configurations.
The invention is an approach for run-time database redirection for systems such as enterprise building automation systems.
Enterprises need flexible database schemas in order to satisfy the many and varied operational, performance, and corporate IT requirements.
Current systems are relatively inflexible in terms of the database partitioning that is allowable. Once configured, the database schema tends to be fixed and inflexible. As the enterprise grows or needs change, reconfiguring the database schema may be something that is no longer possible without the original developers.
For example, if trendlog data, alarm history data, and operator activity log data are all located in the same database when the installation is designed, splitting these different features out into separate databases as the enterprise grows is not generally possible.
The new database redirection, or “database mapping” feature of the Envision for BACtalk 2.5 System, solves this problem by dividing up the databases into separate “sub-schemas” that can be individually re-mapped at application startup.
This mapping occurs via a “database mapping table”, located in the configuration database, which contains mapping information—for each sub-schema—as to which physical database, server, authentication information, and so on, is to be used for that sub-schema.
For example, the “Alarm History” sub-schema could be located on a server in New York using Microsoft Access/Jet while the “Trendlog” sub-schema could be located on a local server in Chicago using SQL Server.
The definition of which database technology to use and the server/database to use is under the control of the controls engineer designing the specific application for a job site. This is different from current industry offerings in that those decisions are typically in the realm of the software designer rather than the controls engineer or corporate IT specialist.
For each database technology, the application, which defines the partitions as sub-schemas, may contain embedded drivers for that technology that include: 1) Defining the type of the database (such as SQL Server, Microsoft™ Access/Jet, and SQL Lite); 2) Determining the existence of the database; 3) Creating the database; 4) Creating the sub-schema including tables, columns, relationships, and so forth; 5) Upgrading the sub-schema from previous or other versions; 6) Migrating legacy data from previous or other versions; and 7) Backing up certain types of databases.
In addition, the entire application is written on top of a database abstraction layer that is, for the most part, database type-agnostic. This means that the same code that uses SQL Server also works with Microsoft Access/Jet.
Special abstraction classes are used that allow SQL command strings to be generated in the correct format depending on the database technology used.
One of the unique parts of this design is that, for example, the end user or controls engineer can, on any given day decide to move the entire trendlog sub-schema from the SQL Server database in Chicago to an Access database in Miami by simply changing a table entry in the database mapping table and resetting a flag in the configuration file for the application.
In this example, the operator activity sub-schema (DBID=8) is re-mapped to a SQL Server (DbType=3) machine (named SECURE-SERVER-3) and uses SSL encryption for all communications with that server. The user ID that is used for login is “E200000” and the password is provided by Windows using Windows Authentication. The real-time trendlog data (DBID=14) is logged to a very fast and high-capacity server named FAST-SERVER-4. Event log data (DBID=17) is re-mapped back to a local Access database (DbType=1) named “LocalAccessEventlog.mdb” in the local job directory (DbPathType=2). It may use the standard “admin” user for the Jet/Access database.
Background information is provided herein to help in interpreting the mapping table example in
Sub-schema versus database may be noted. DBID's may represent sub-schemas. There may be one or more sub-schemas per database. For example, one SQL Server database may have several sub-schemas in it (such as DBID_PROJSETUP, DBID_USERPROFILE, and so forth). Each sub-schema (DBID) present in the database may have its row in the version table (
Re-mapping databases may be noted as the following. The DBID_PROJDATABASES sub-schema allows remapping of sub-schemas to different databases (i.e., mix′ n′ match within certain limits). The re-mapping may allow for SQL server performance optimization. Each time a new ADO (ActiveX Data Objects) connection is opened, it may be checked against cached data from a TBL_DATABASES table in the DBID_PROJDATABASES to see where the database actually resides and what type of database it is. The TBL_DATABASES cache may be initialized when the EBT is started. Subsequent changes to TBL_DATABASES generally will be ignored until the next time that the EBT is started. Internal tables may be initialized from TBL_DATABASES when the EBT starts up. Subsequent changes to the TBL_DATABASES will have no effect until the EBT restarts.
Block or layer 63 is an enterprise layer and block or layer 62 is a common data layer. Layer 63 indicates a data layer 64 (BtManagedBL) and database operations 65 (BtDatabase). Under operations 65 are SQL Server database operations 66 (BtSqlServer) and Access/Jet database operations 67.
Under layer 63 is layer 62 which indicates a data layer 68 (btobjdblib) and a shared data access support layer 69 (btdb, btdbdef). Under support layer are an ADO database access 71 (btwado), ADO database schema 72 (BtAdoSchema), ADO connection pool 73 (AdoConnectionPool), and user profile database operations 74 (tri-mode support: SQL Server, Access/Jet, SQL Lite).
Databases may be opened and closed. An IBTAdoConnectionPtr class may exist in EBT, encapsulate ADO connection and associated operations and can specify a database using a BACtalk connection handle, database ID and an optional parameter. The parameter can represent a device instance for PointData, trendlog ID for trendlog data, or an energy log number for virtually every log data. The class may also allow sharing an ADO connection with IBtAdoSchema object, and automatically close (or cache) a database connection when the IBTAdoConnectionPtr object goes out of scope.
Creating tables, fields and indexes may be noted. The IBtAdoSchema class may act as an interface class to abstract the SQL server and access classes (BtAdoSchemaBase, BtJetSchema, and BtMsSqlSchema) that implement their respective operations. The class may now be an abstract class representing either SQL server or Jet schema capability, for example, depending on the database type. The IBtAdoSchema class may permit creation of tables, fields, indexes, and so on. For SQL server clean, a CSQLString may provide an ability to parameterize to a database at runtime without a need of hard code. Proper syntax may be provided at runtime. The database mapping table is a key to runtime applying of databases to different localities. The table may configured by a user to meet certain needs. An application may be a breakup or a partition of a database into sub-schemas. Sub-schemas may be abstracted out with the database mapping table. Several things can drive a partition of a database into schemas. Scalability with partitioning may allow growing a database beyond its original size and its components for holding the database. Partitioning may enable encryption for some portions of the database and not for other portions. Partitioning may let a user to put a portion of a database in a certain database server type, such as Access/Jet, for a third party such as a customer to use, but prevent the party from having access to the remaining portion or portions of the database. Partitioning a database into sub-schemas permits certain data to be on a high bandwidth server database and other data to be on a less expensive low bandwidth server database.
To recap, the invention is a system for database redirection, having a first machine, a database stored on the first machine and a mechanism for partitioning the database into sub-schemas and moving one or more sub-schemas to be stored at one or more other machines, such as at another location external to the first machine. A machine may be a computer, server, or the like. Partitioning and moving may be effected by a user according to application design of a task to be performed with the first machine. Partitioning the database and moving the one or more sub-schemas may occur during run-time of the first machine. Partitioning and/or moving may be done over a network.
At one or more sub-schemas on the first machine may be stored as a first type of database. One or more sub-schemas may be stored as another type of database on the same or another machine.
A configuration database may be stored on the first machine. A database mapping table, for containing mapping information for each sub-schema, may be situated in the configuration database. Mapping information for a sub-schema can be entered in the configuration database via the database mapping table. Partitioning and moving sub-schemas may be effected by making entries in the database mapping table. The mapping information may be entered before or at an application start-up.
The partitioning the database into sub-schema may be according to one or more criteria. These criteria may include performance, capacity or size, availability, location, security, technology type, and other properties. A sub-schema may be moved to and stored at a machine according to or meeting the one or more criteria designated for the respective sub-schema. Some technology types may include SQL, Jet/Access, SQL Lite, and other types.
The database mapping table may have at least one entry selected from a group of items containing a sub-schema ID, a database name, a password, a server, a path type, a technology type, encryption use, authentication, and/or other entry.
In the present specification, some of the matter may be of a hypothetical or prophetic nature although stated in another manner or tense.
Although the invention has been described with respect to at least one illustrative example, many variations and modifications will become apparent to those skilled in the art upon reading the present specification. It is therefore the intention that the appended claims be interpreted as broadly as possible in view of the prior art to include all such variations and modifications.