These problems, and many more, all conspire to make your job as a developer much more difficult than it needs to be.
Because of these types of problems, Gnosis Solutions, of Auckland, New Zealand, developed a new tool for SQL Server called Gnosis Transaction Manager, to help overcome them.
What is Gnosis Transaction Manager?
Gnosis Transaction Manager is a unique product, unlike most SQL Server development tools you may have used. In fact, it is hard to describe in a short article like this.
Probably the best way to describe it is as a development tool that is designed to create and manage the backend SQL Server database portion of a system. It extends the capabilities of the database portion to include transaction and business rule management, which have previously been programmed in the application or middleware. The result is that the user application or middleware development is considerably simplified, which in turn reduces development costs and risk.
Business rules are defined in what is called the Business Rule Engine, which greatly extends the capabilities of conventional relational database constraints, with features such as constraints on views and constraint specific error messages. In addition, the Gnosis architecture provides other benefits–such as improved user workflow, like the ability to pass work-in-progress from one user to another–and decreased risk of loss of work due to system crashes.
Gnosis Transaction Manager creates and manages the database schema from the definition loaded into the Business Rule Engine. A powerful code generator creates stored procedure, table and view objects, which comprise the Gnosis layer, through which applications interact with the database.
Key Features and Benefits of Gnosis Transaction Manager
Gnosis Transaction Manager has the ability to perform tasks that are either very difficult to code, or are virtually impossible to code using conventional programming methods. Some of these include:
Transaction Workflow Features and Benefits
- Multiple transactions can be started concurrently by a single user.
- A transaction can be passed from one user to another.
- Transactions don’t have to be completed in real-time. A transaction can be started today, but not completed until later. This could be later the same day, tomorrow, or even next week.
- The risk of loss of updates are reduced significantly due to network or client software failure. This is because updates are written to the database as they are made.
- Multiple users can work together to create one very large data set under a single transaction.
- Transactions can be controlled in great detail. For example, save points and rollbacks to any of the save points is easy to accomplish.
- Built-in record locking architecture prevents more than one user from making changes to the same data.
- Transactions can be of virtually unlimited size.
Business Rule Management Features and Benefits
- Your application’s business rules are stored in the Business Rule Engine, without the need for programming.
- Business rules are enforced by the database, not your application.
- Business rules can be changed, without affecting the application.
- Custom business rule error messages can be created.
- Business rules, such as column uniqueness, foreign key reference, and column checking, can be defined on tables and views, and between tables and views.
- Business rules can be created and enforced based on a user’s activity (as the user is entering data), or on the commit of the transaction (when all the data has been entered).
- Business rules are incorporated into a stored procedure interface which is automatically built for you using a powerful database code generator.
Administration and Performance Features and Benefits
- Transaction data merge and rollback processing is offloaded to a background task, reducing processor load on the server, and boosting performance.
- Virtually all operations are processed on SQL Server, which provides superior performance over the standard client/server architecture. Clients can be very thin.
- Changes to the database are saved to the database as the user makes them, which is the safest place to store this data.
Application Development Features and Benefits
- Your application or middleware doesn’t have to manage transactions or business rules. This is handled completely by Gnosis Transaction Manager.
- Application enhancements can be made independently of the business rules.
- User updates are flushed to the database, so memory management is not as much of an issue.
- Very large applications can be broken down into smaller, easier to manage applications.
- Multi-language support is offered using SQL Server’s Unicode objects.
How Gnosis Transaction Manager Works
All definitions of data types, table structures, indexes, views, rules, defaults, constraints and error messages are loaded into the Business Rule Engine. The Business Rule Engine comprises a set of tables that define the database structure and business rules of the proposed database system. This definition must pass a series of checks before the database is generated.
For example, all tables must have a Data Key (which is a unique and unchanging row identifier, such as an identity or a uniqueidentifier) and all tables must have a Primary Key (which is a combination of columns that define business uniqueness). There are other checks that must also be passed.
Once the proposed database has passed all the checks, it can be generated. The database generator creates the base tables, and generates view & stored procedure objects which constitute the Gnosis layer, through which all program interaction must go. For each table there is a specific view object generated through which to see data, and insert, update, delete & get stored procedures to manage data.
The Gnosis Transaction Manager offers the ability to break complex applications (which may update many tables in one transaction) into multiple applications, which operate together in what is called an environment. Where the designer chooses to develop an environment of multiple applications, one of the applications or a separate application must manage the transaction. For standalone applications, each application manages its own transactions. From the programming perspective, an application updates tables through the stored procedure interface, and should flush changes to the database as they are made (rather than storing changes in memory).
The following script demonstrates using the Gnosis Layer on the table Location. Transactions are managed through stored procedures prefixed with sp_gnosis_..,
exec sp_gnosis_transaction_begin ‘Locations’
— Country
exec Location_Ins @Location_ID=0, @Name=’New Zealand’, @Parent_Location_ID=0
exec Location_Upd @Location_ID=0, @When_Begin=’7 Feb 1840′
exec sp_gnosis_transaction_save @save_point_name=’Country’
— Cities
exec Location_Ins @Location_ID=1, @Name=’Sydney’, @Parent_Location_ID=0
exec sp_gnosis_transaction_rollback @save_point_name=’Country’ — Sydney is in Australia!
— Cities
exec Location_Ins @Location_ID=1, @Name=’Auckland’, @Parent_Location_ID=0
Select * from [Location] — returns all Location rows
exec [Location Get] — returns all Location rows
exec sp_gnosis_transaction_commit ‘Locations’
Gnosis Transaction Manager is built on a temporal database architecture, and stores a history of transaction-time changes. A background task is configured to remove the history, or if you wish it may be retained. Storing transaction-time histories offer the ability to view a system as-it-was at a previous time, to examine changes to important pieces of data, and to run multiple repeatable queries (read consistency) at potentially any historical date.
Transaction histories can be used to migrate changes-only to other systems such as a data warehouse, and offer the solution to the stale client cache problem, both of which will provide significant network traffic savings. Gnosis Transactions can be used in the data warehouse refresh process and make a data warehouse (24×7) available for user queries while concurrently being refreshed. For these reasons (and more) I recommend you investigate this emerging new technology.
For More Information
To really understand and appreciate what Gnosis Transaction Manager can do for you, you must visit their website and read the detailed information it provides. If you want to start “thinking out of the box,” and try a new approach to getting your SQL Server-based applications written on time and on budget, you owe yourself to spend some time further investigating what Gnosis Transaction Manager can do for you and your company.