The ability to track changes in your database can be a time consuming and tedious process that can in certain circumstances impact the performance of your database and your users. InterBase offers an easy to implement solution to tracking those changes.
What are Change Views?
Change Views are a patented subscription model used to subscribe to data and identify what data has changed in the database.
You create a subscription that covers different tables and columns and give users the rights to subscribe to changes in the data. There are no log tables or external files so it is a very clean setup that tracks internally, meaning that your changes are safe inside your database not hanging around inside files that anyone can access.
Only the users who are subscribed to those data changes can actually see the changes made available to/for them.
Subscriptions can run both during a connection and spanning a connection. So during a connection, you can get an alert and then just fetch the deltas. So if you were to fetch a few thousand records and then get a message that says “there have been changes to the table” you can use change views to get the delta instead of having to refresh the whole query again. Reducing the time and data movement over your network.
For spanning a connection, a really cool thing is that you don’t actually have to be connected for it to record the changes. Once you start a database transaction you can have a subscription set then disconnect from your database and then start a new connection with a new transaction at future point in time.
When you make changes to your data, you fetch that data whenever you apply the updates. Using InterBases’ change views, you can now identify specifically what deltas have changed the the field level, so using that you only need to package and move the data that has changed. Some of the benefits to using Change Views are
- Reduce costs and disk I/O by minimizing data syncs on mobile devices
- Little impact on performance
- No external log tables
- Scalability of users
- Track changes the way you want
When Change Views sync, only the changes between the local and server databases move, keepinging data accurate and current!
How to set up Change Views
There are two major steps that you need to follow to utilize change views, set up subscriptions on the server and add a few components to your application.
- Create a subscription
- Specify the data at the table, column/field level.
- Grant subscription access
Sample for CREATE SUBSCRIPTION
CREATE SUBSCRIPTION sub_employee_changes ON
EMPLOYEE (EMP_NO, DEPT_NO, SALARY)
DESCRIPTION 'Subscribe to changes in EMPLOYEE table';
CREATE SUBSCRIPTION sub_customer_deletes ON
CUSTOMER FOR ROW (DELETE)
DESCRIPTION 'Subscribe to deletes in CUSTOMER table';
CREATE SUBSCRIPTION sub_various_changes ON
EMPLOYEE FOR ROW (INSERT, UPDATE, DELETE),
CUSTOMER FOR ROW (INSERT, UPDATE, DELETE),
SALES FOR ROW (UPDATE),
DEPARTMENT (LOCATION) FOR ROW (UPDATE)
DESCRIPTION 'Subscribe to various changes on multiple tables';
GRANT SUBSCRIBE ON SUBSCRIPTION <subscription_name> TO <user_name>;
Once subscriptions are set up on the server, you can add/activate them in your applications.
- Start the Transaction in SnapShot isolation mode.
- Set your subscription to active by using a query to execute a set subscription active to True
- Run a select statement to fetch the deltas. This could be a simple select statement.
- Once you have grabbed your delta(s), commit your transaction to bring your Change Views up to date in the application.
Change Views documentation : https://docwiki.embarcadero.com/InterBase/2020/en/Getting_Started_with_Change_Views
FireDAC Data Change Notifications: docwiki.embarcadero.com/RADStudio/Sydney/en/Data_Change_Notifications_(FireDAC)#Data_Change_Notifications_for_Specific_DBMS