Tag Archives: Change Views

InterBase – IoTA Internet of things awards Winner 2015

InterBase - IoTA winner 2015

Embarcadero – InterBase
Most Innovative Use of Data

It was with great pleasure that I attended the 2nd Annual IoTA award dinner yesterday, part of the 17th Annual Total Telecom World Communication Awards (WCA).

Hundreds of senior executives gathered at the iconic Wembley Stadium to see the winners named. An incredible C-Level audience represented the communications industry from across the globe, with representatives from every continent attended.

The awards, (one for which InterBase was a finalist), were sponsored by leading players from the Telco industry, including ADVA, AsiaInfo, CITIC Telecom CPC, IDT Telecom, PCCW Global, Tata Communications, TeliaSonera International Carrier and Vodafone.

Left to right: Adam Oliver, (CEO, Institute of Telecom Professionals); Marc Brunel-Walker, (Director of International Marketing, Embarcadero), Stephen Ball (InterBase Product Manager – Embarcadero), Scott Mills (Radio 1 DJ and host)

As you can see from the photo above, InterBase beat off the competition to win “most innovative use of data” in part thanks to the patent pending technology “Change Views” that we developed and brought to market back in December 2014 with InterBase XE7.

InterBase, the home of Change Views.

Change Views is dramatically changing the way data is moved to and from mobile and the edge of IoT by allowing developers to easily identify delta’s; By retrieving only the deltas, it enables network traffic and cost to be slashed, ensuring a much richer end user experience.  Additionally the enhanced granular encryption and role based security model offered by InterBase enables true enterprise data best practice to exist everywhere through the development life cycle.

For more on InterBase, please visit http://www.embarcadero.com/products/interbase

Thank you’s

There was no chance on the night to do a speech and offer our thanks so I’ve decided to decant it here. Firstly I want to thank all the InterBase users out there for your loyal support and amazing stories you share with us. This go a long way to helping InterBase win such amazing awards.

I want to thank Jason Vokes, John Thomas, Marco Cantú, James Pitts and Michael Swindell for their support and guidance when we initially dreamt up Change Views and needed a plan to bring it from development through to market. Marc Brunel-Walker for helping with the award submission. Dmitry Arefiev for his amazing FireDAC integration helping developers get to the technology faster, and similarly Jeff Overcash for IBX; My wife Charlotte for all her amazing support; But most importantly today to my InterBase team, especially Charlie Caro and Sriram Balasubramanian, with whom I spent 3 long, but fun days in 2014 planning and discussing use cases and scenarios as we set off to give birth to this amazing technology.

Finally a big thank you to Rob Chambers and his team at Total Telecom for a wonderful memorable evening; It is by far the best awards event I have been to in my 4 years at Embarcadero, and I hope we get the chance to attend (and hopefully win another award) next year.

InterBase XE7 – Update 1 Released

InterBase XE7 Update 1 Overview

InterBase XE7, update 1 went live on Friday and brings with it SQL language enhancements for Change View, and some updates to IBConsole and the availability of InterBase ToGo for XE7

Full details about the update are available on the InterBase DocWiki

Change View Updates

There are 2 major additions to InterBase Change Views in Update 1

Drop Subscription

Prior to update 1 you had to manually drop a subscription via an SQL statement. This was a temporary solution and has now been replaced with the correct SQL syntax

DROP SUBSCRIPTION <subscription name> [RESTRICT | CASCADE]

If you want to remove a subscription that has subscribers you must use the CASCADE command as RESTRICT is the default.

Change View SQL Extensions

InterBase SQL now includes additional language to support querying about the changed state of specific fields via the <FIELD> IS [NOT] {CHANGED | INSERTED | UPDATED | DELETED} clause.

Take the following example.  Lets imagine we have two employee records that have been modified.

  • EMP_NO 39 has their DEPT_NO changed
  • EMP_NO 109 has their SALARY changed.

At run time you want to select only those that have had a SALARY change prior to processing staff payments. To achieve this first, a Change View subscription is set active (making any SQL statement subsequently run only return the delta for that subscriber). Then SQL where clause then filters further where the SALARY IS UPDATED.

SET SUBSCRIPTION sub_employee_changes at 'MyDeviceID' ACTIVE;
SELECT EMP_NO, DEPT_NO, SALARY FROM EMPLOYEE WHERE SALARY IS UPDATED;

EMP_NO     DEPT_NO           SALARY
--------   ----------        ----------
109        600               75000

Regardless of what you select, the commit will still bring

InterBase IBConsole Updates

InterBase IBConsole has a number of new features, introduced in Update 1 designed to get you started quicker.

IBConsole Start Here
IBConsole Start Here

Getting started with InterBase

When you open IBConsole now you find the new InterBase – Start Here page as a tab at the top of the screen. The start here page includes containing details about Community and featured posts, as well as training videos and direct links to the documentation. While an offline page ships with IBConsole, If you want to see this page now, please visit http://www.embarcadero.com/starthere/interbase/index.html

Having this page online allows us to improve our communication with our customers about critical information and appropriate news.

Recent Databases

There is also a new feature in the bottom left of the screen (see image above or below) enabling you to quickly connect back to the recent database you have been working on.

This windows shows you the database name, server and also the when you last accessed the database via IBConsole.

Change Views

Change View IBConsole

IBConsole also now includes access to work with subscriptions.

InterBase ToGo XE7

InterBase XE7 ToGo trial edition is now available for download – more details to come soon about ToGo.

InterBase Change Views Demo and Video

InterBase Change Views Example Demo

This week I hosted a Skill Sprints session looking at InterBase Change Views with an example set of code working around a Pharmacy needing to keep centralised drug information updated at distributed pharmacies.

The example code will ship with the next major release of the RAD Studio products – which you can get for free with the current RAD offer

This InterBase Change Views Example demo shows off this powerful way to track on the server what data is changing in a very lightweight and scalable way.

This Skill Sprint Video for InterBase Change Views follows on from the recent blog posts I have done on Change Views which are available here:

InterBase Change Views blogs

InterBase Change Views in Brazilian Portuguese

Last week CodeRage III Brazil ran with a host of great sessions in Portuguese, covering RTTI, App Tethering, DataSnap and FireDAC and also InterBase. Back in December I hosted the product Launch of InterBase XE7 at CodeRage 9, and it has been great working with Dormevilly and Fernando to get an updated Brazilian Portuguese version done, including some of the great capabilities we have today with Change Views. Great job guys 🙂

InterBase Change Views – Part 3 – Using a change view

In Part 1 I explored the concept of change views, before how to create a Change View ready for use in Part 2. In Part 3 we are going to explore how to use a Change View to collect data that has changed in your subscription.

Using an InterBase Change View to fetch data deltas

Once a Change View Subscription has been created and access to it has been granted (to either users or roles), then its time to start using your Change View!

The following steps are the high level overview of using a Change View.

  1. Start a Transaction in SnapShot isolation mode.
  2. Set the Subscription active
  3. Run your select statement to fetch you delta
  4. Once you have collected the delta commit your transaction to bring your Change View up to date.

SnapShot Mode

OK a bit of background as we all love to know how things work..

InterBase is powered by a multi-generational architecture which is exceptionally useful when data integrity is important. imagine you need to run multiple financial reports while the system is live;  Imagine a connection drops part way through a big data change how do you know what has changed? Multi-generational architectures and transactions makes this possible.

Using a transaction set to SnapShot mode we have a stable point in time that means even if records are updated at the same time as your transaction, they are not missed. When you start your next transaction you will locate those concurrently made changes! – imagine trying to do that with date time stamp tracking!

This means you can have multiple users updating the data at the same time safely without having to lock the tables 🙂

Setting the subscription active

At this point, lets pretend we have a STOCK table and we run the following statement

Select * from STOCK

the data returned would be all the STOCK records.

How do we get from here to just getting changed data deltas? Well, first we need to activate the subscription and provide an ID that we are going to track against.

set subscription sub_stock at 'DeviceID' active;

With the subscription “sub_stock” active, running the initial statement will again return all records as its the first time data has been requested in the subscription.

At this point there are two possible next steps: Commit or Rollback.

Rollback of the transaction will ensure the data is provided again, Commit will bring the Subscription up to date for the start of the transaction.

Running the Select statement again in an active transaction with the subscription active will now return only delta’s

Preventing you data changes showing in your Change View delta

So we have covered selecting data, but what about updating data? How do you stop the changes to data you make appearing in your subscription yet still available to others? Well simply, make the changes while your subscription is active.

  1. Start a Transaction in SnapShot isolation mode.
  2. Set the Subscription active (using your ID)
  3. Run your update statement to modify the data
  4. Commit your transaction.

Simples!

Introduction to Change Views Video on Embarcadero website

Introduction to InterBase Change Views

 

To see a Change View code example see the InterBase XE7 FirstLook webinar

InterBase Change Views – Part 2 – Creating a Change View

If you have ever spent time planning how to identify what data has changed, then change views is about to simplify the way you develop for ever!

If you haven’t already read Part 1 of InterBase Change Views this provides a high level overview of the technology.

Steps for creating a Change View

Rather than having to create and manage triggers to add records into log tables or planning additional date fields into your metadata (and hoping date & time changes don’t effect you) Change View are easily added to a project once the simplified architecture is complete.

There are two essential steps in creating a successful change view:

  1. Defining the purpose of the Change View and the data you want to track
  2. Defining who can subscribe to the change view

1) Creating a change view

Lets start with a simple example. We want to keep our sales fulfilment application up-to-date with the latest stock and suppliers information. This data is stored in two tables, INVENTORY and SUPPLIERS.

Example 1 – Multiple Tables

CREATE SUBSCRIPTION 
  sub_stock
ON 
  INVENTORY FOR ROW (INSERT, UPDATE, DELETE),
  SUPPLIERS FOR ROW (INSERT, UPDATE, DELETE)
DESCRIPTION 'Track stock and supplier changes';

Subscriptions are created with the CREATE SUBSCRIPTION statement followed by a subscription name that you will need to use in SQL later on.

The simple example tracks all columns in the tables INVENTORY and SUPPLIERS for Inserts, Updates and Deletes.

The description allows easy identification of the subscriptions purpose later on.

Example 2 – Specifying Columns

We may have a requirement to track product name changes for our application. We can do this also with a change view.

CREATE SUBSCRIPTION 
  sub_stockname
ON 
  INVENTORY(ITEM_NAME) FOR 
    ROW (INSERT, UPDATE, DELETE)
DESCRIPTION 'Track stock name changes';

Example 2 is more granular than example 1 and tracks only a single column ITEM_NAME in the INVENTORY table. This is done by providing a comma separated list of field names for the table that you want to track.

Example 3 – Tracking Deletes

We may also want to track deletions from a specific table, lets say ORDERS. A Change Views just tracking DELETE activity on  a table will provide a way to recover data if it is deleted

CREATE SUBSCRIPTION 
  sub_orderDeletes
ON 
  ORDERS FOR 
    ROW (DELETE)
DESCRIPTION 'Track Order Deletions';

Using a Change View to track deletes is a powerful feature as you can completely recover the record at its state when it was deleted.

2) Defining who can use a change view / subscription

Once a subscription is created, you need to grant SUBSCRIBE access to the subscription. To do this use the standard GRANT syntax. e.g. (for user SYSDBA)

GRANT SUBSCRIBE ON SUBSCRIPTION sub_stock TO SYSDBA;

InterBase Documentation

For more on Change Views visit the InterBase DocWiki

In my next blog, I’ll look at using a change view

InterBase Change Views – Part 1 – What is a change view

This is the first post of a series on InterBase Change Views and is intended to be a high level overview of the new powerful change view technology that is patent pending and part of InterBase. Following this post I plan to show more posts taking the concept of Change Views into the real world with some real application examples.

InterBase Change Views

InterBase XE7 introduces a new way to track data changes to the database called Change Views.

Change Views are a new “subscription based” model allowing you to “subscribe” to data; once subscribed you are able to ask the database at a later time, spanning connections, for what has changed.

This is an especially cool, low cost way to reduce network traffic, mobile data costs and development time when tasked with keeping multiple remote database caches up to date. (especially if you have large tables to keep up dated).

Change Views are simple to use and reduce the upfront planning needed for building in data tracking that history has shown to be inherently complex and error prone.

Best of all change views has zero impact on database performance regardless of the number of subscribers!

Subscription & Subscribers

A core concept to change views is that of Subscriptions and Subscribers.  InterBase Change view allows multiple subscribers to a subscription.

A subscription is defined once and then can be connected to by multiple users. Once defined, you can control who can subscribe thanks to the InterBase inbuilt user security.

Subscriptions work at field level and can be defined to track either an entire table or multiple tables. Subscriptions can also be defined to track Inserts, Updates or Deletes (or a mix of)

Multiple devices per subscriber

In addition to allowing specific users to subscribe to data, you can use a single user to have multiple destinations that are subscribing. This allows a user to subscribe for different devices such as their Phone, Tablet, Laptop or for a single user to be used programatically to distinguish multiple sites – e.g. Office 1, Office 2 etc.

This is done using the “at” verb when connecting to the subscription.

What Change Views are not!

Change Views are not an auditing system. While you can track deleted records, Change View are about identifying what has changed without keeping every value that it has been. If you want to do that, then continue to use Triggers and logging tables for the values you need to audit.

Video – Change Views

An overview of Change Views is available in the InterBase product address from CodeRage 9. I will be digging into more code based versions through this series. This covers the syntax used

Documentation – Change View

There is a great introduction to InterBase Change Views in the release notes for InterBase XE7
http://docwiki.embarcadero.com/InterBase/XE7/en/What’s_New_in_InterBase_XE7#Change_Views_Feature