Incremental Replicate Teamleader to SQL Server

Wiki (en)
, ,
1

Preparation

The following articles assumes for the aliases sqlserver and teamleader to work that you have a database definition in your settings.xml file such as:

<connection name="teamleader+sqlserver">
  <database
    order="0"
    provider="Teamleader"
    alias="teamleader"
    connectionString="api-group-authentication=false" 
  />
  <database
   alias="sqlserver"
   provider="SqlServer"
   connectionString="server=database-server-name" 
  />
</connection>

Full Copy of Companies

Using Invantive SQL you can copy data from a platform such as Teamleader to your own SQL Server database with solely one statement such as:

create or replace table tl_companies@sqlserver
as
select cpyd.*
from   teamleader.v1.companies@teamleader cpy
join   teamleader.v1.company(cpy.id)@teamleader cpyd

The SQL statement above creates a table on SQL Server with data types that are able to fit the Invantive SQL data types, adds indexes where justifiable based upon the data being loaded and then bulk inserts the data.

However, this approach retrieves all companies each time. The Teamleader API needs one data request per company in this scenario and the number of requests per minute is limited to 25 (V2). Therefore, already with 250 company the run-time can be excessive.

Incremental Copy of Companies

You can also use an incremental approach which just adds new data to your SQL Server database. Disadvantage is that when you delete records in Teamleader these deletes might go unnoticed.
The following Invantive SQL consists of two steps:

  • one-time create a table with the correct definitions,
  • repeatedly add new and/or changed rows to the table.

One-time Setup

The following statement creates a table with the correct definitions and initial data set:

create or replace table tl_companies@sqlserver
as
select cpy.date_added
,      cpy.date_edited
,      cpyd.*
,      sysdateutc sourced_date_utc
from   teamleader.v1.companies@teamleader cpy
join   teamleader.v1.company(cpy.id)@teamleader cpyd

Add new/changed rows to the table

The following statements merge all changed companies with the last available data set:

--
-- Create a version of the last copy in-memory to allow changing
-- the SQL Server table without impact on the query running.
--
create or replace table tl_old_companies@inmemorystorage
as
select * except rowid$
from   tl_companies@sqlserver

--
-- Create a new version with changes.
--
create or replace table tl_companies@sqlserver
as 
select cpy.date_added
,      cpy.date_edited
,      cpyd.*
,      sysdateutc sourced_date_utc
from   ( select max(date_edited) last_change_date from tl_old_companies@inmemorystorage ) d
join   teamleader.v1.companies@teamleader cpy
join   teamleader.v1.company(cpy.id)@teamleader cpyd
--
-- Companies has a last modified date.
--
where  cpy.date_edited >= d.last_change_date
union distinct on id 
select * except rowid$ 
from   tl_old_companies@inmemorystorage

Meer uitleg over union distinct is te vinden op Sieve your Data using the Union Distinct Set Operator.

Full Copy of Deal Items

A more complex example is deal items. Again, you copy data from a platform such as Teamleader to your own SQL Server database with solely one statement such as:

create or replace table tl_deal_items@sqlserver
as
select dimd.*
from   teamleader.v1.deals@teamleader del
join   teamleader.v1.deal_items(del.id)@teamleader dimd

The SQL statement above creates a table on SQL Server with data types that are able to fit the Invantive SQL data types, adds indexes where justifiable based upon the data being loaded and then bulk inserts the data.

However, this approach retrieves all deals and their items each time. The Teamleader API needs one data request per deal in this scenario and the number of requests per minute is limited to 25 (V2). Therefore, already with 250 deals the run-time can be excessive.

Incremental Copy of Deal Items

You can also use an incremental approach which just adds new data to your SQL Server database.

However, compared to the companies example mentioned earlier, the deals API has no value signaling when it was last edited, let alone when the deal items were last edited. Therefore we switch to a different strategy, which might not always work for your use case. The code below uses the four date timestamp on deals instead of using last edited date.

One-time Setup

The following statement creates a table with the correct definitions and initial data set:

create or replace table tl_deal_items@sqlserver
as
select del.id
,      dimd.*
,      sysdateutc sourced_date_utc
,      sysdate    sourced_date
from   teamleader.v1.deals@teamleader del
join   teamleader.v1.deal_items(del.id)@teamleader dimd
on     dimd.title is not null

Add new/changed rows to the table

The following statements merge all changed deals with the last available data set:

--
-- Create a version of the last copy in-memory to allow changing
-- the SQL Server table without impact on the query running.
--
create or replace table old_tl_deal_items@inmemorystorage
as
select * except rowid$
from   tl_deal_items@sqlserver

--
-- Create a new version with changes.
--
create or replace table tl_deal_items@sqlserver
as 
select del.id
,      dimd.*
,      sysdateutc sourced_date_utc
,      sysdate sourced_date
--
-- Determine time of last copy.
--
from   ( select max(sourced_date) last_change_date from old_tl_deal_items@inmemorystorage ) d
--
-- Retrieve all deals. Very fast.
--
join   teamleader.v1.deals@teamleader del
--
-- Retrieve the items of each individual deal.
--
join   teamleader.v1.deal_items(del.id)@teamleader dimd
on     dimd.title is not null
--
-- Deals has no last modified date. We only will consider something a change
-- when one of the other dates has a changed since the last change.
--
-- We assume that the local time zone sourced_date is the same time zone
-- as Teamleader uses. When you run this script in another time zone, like Greece,
-- you will need to offset last_change_date by the timezone difference.
--
where  del.entry_date >= d.last_change_date
       or
       del.latest_activity_date >= d.last_change_date
       or
       del.close_date >= d.last_change_date
       or
       del.date_lost >= d.last_change_date
--
-- Combine the new data with the old copy.
-- The new data with the same value for 'id'
-- takes preference as specified
-- by the 'distinct on id'.
--
union distinct on id 
select * except rowid$ 
from   old_tl_deal_items@inmemorystorage
2

Tip for correct incremental update:

  • Retrieve deals list
  • Join deal items with deal table so u can add updated time.
  • Create new table with with updated time <= date - X days
  • Retrieve deal items with update time >= date - X days
  • Union both tables
1 like