Community thoughts about data merging

Find out how to get things done with Servoy. Post how YOU get things done with Servoy

Community thoughts about data merging

Postby karel » Thu Feb 14, 2013 2:20 pm

Hello Servoyians!

For the following situation I like to know your input, ideas and how to's:

Most of you probably have a dedicated customer database (with only the data from 1 customer).
Then in other cases there are databases that contain data of multiple customers (saas).

Suppose you need to migrate data from the dedicated customer database to the saas database.. how would you do that?
(Please mention your tools, approach, etc. Also note that you cannot use the same primary keys when migrating your data into the saas database)

Looking forward to whatever you come up with :)
Karel Broer
SAN Partner
Stb Development - http://www.stb.nl / ServoyCamp - http://www.servoycamp.com
karel
 
Posts: 13
Joined: Mon Jan 30, 2012 6:50 pm

Re: Community thoughts about data merging

Postby mboegem » Thu Feb 14, 2013 4:51 pm

Hi Karel,

why using 1 database?
you can have multiple database connections and do a switchServer at login.
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image

Partner of Tower - The most powerful Git client for Mac and Windows
User avatar
mboegem
 
Posts: 1742
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: Community thoughts about data merging

Postby karel » Thu Feb 14, 2013 5:06 pm

mboegem wrote:Hi Karel,

why using 1 database?
you can have multiple database connections and do a switchServer at login.

Eeey Marc! These are the specs and that's the challenge we have to deal with :wink:
Karel Broer
SAN Partner
Stb Development - http://www.stb.nl / ServoyCamp - http://www.servoycamp.com
karel
 
Posts: 13
Joined: Mon Jan 30, 2012 6:50 pm

Re: Community thoughts about data merging

Postby patrick » Thu Feb 14, 2013 5:35 pm

What PK type are you using in these databases?
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: Community thoughts about data merging

Postby lwjwillemsen » Thu Feb 14, 2013 5:43 pm

Hi Karel,

why using 1 database?
you can have multiple database connections and do a switchServer at login.


Answer : Multi-tennant with shared tables...
Lambert Willemsen
Vision Development BV
lwjwillemsen
 
Posts: 680
Joined: Sat Mar 14, 2009 5:39 pm
Location: The Netherlands

Re: Community thoughts about data merging

Postby m.vanklink » Thu Feb 14, 2013 5:45 pm

Do you want this in a running SaaS server?
Suppose the SaaS has two databaseservers connected: user_data which contains the SaaS-database containing data from different customers and user_data_source which contains the data of the customer to be migrated to the SaaS-database.
Server user_data_source will only be accessed using rawSQL, so we can restore a complete database int it without having to flush anything or run update db sequences. Server user_data will be accessed using Servoy foundsets.
For every table and for every record in user_data_source: Keep creating (and deleting if not succesful) new records in user_data until you find an id that is also available in user_data_source. In user_data_source change id to this newly found id, not only in the record itself but also in all referenced tables where this id is used as a foreign key.
After this we have a user_data with a lot of empty placeholder records and a user_data_source that can be bulk copied to user_data using postgresql COPY FROM and COPY TO. Just before the COPY TO delete all the placeholder records.
Most difficult part is "not only in the table itself but also in all referenced tables".
Michel van Klink
Vision Development
m.vanklink
 
Posts: 70
Joined: Thu Feb 23, 2012 9:15 am
Location: The Netherlands

Re: Community thoughts about data merging

Postby ryanparrish » Thu Feb 14, 2013 5:48 pm

karel wrote:(Please mention your tools, approach, etc. Also note that you cannot use the same primary keys when migrating your data into the saas database)


Why can't you use the same primary keys?

We converted our DB from a single "customer" to a SaaS model rather easily by adding a "tenant_id" column to all our tables and putting addTableFilterParam in our solution open method. Since you are starting out from a single customer DB, all you would need to do is add the column and do a bulk update on all the tables setting the new tenant_id column to the same number, like tenant_id = 1.
Code: Select all
databaseManager.addTableFilterParam('cruz', null, 'id_tenant', '=', globals.core_g_tenant_id, 'tenant_filter');
ryanparrish
 
Posts: 162
Joined: Thu May 17, 2007 7:49 pm
Location: Miami, FL

Re: Community thoughts about data merging

Postby Harjo » Thu Feb 14, 2013 7:10 pm

The only right way todo this is to convert your solution to UUID PK's and FK's,

than you can migrate easily from a dedicated customer to a SAAS environment, without conflicting PK's (sequences)

you than also need to take care that every foundset and relation, will have a default sort!
because now Servoy defaults sorts on the PK, (so in practise this is the default creation-order)
with UUID, everything get's mixed up because of the sort on UUID. So you need to sort than on creationdate for example..
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: Community thoughts about data merging

Postby m.vanklink » Fri Feb 15, 2013 9:10 am

Harjo wrote:The only right way todo this is to convert your solution to UUID PK's and FK's


True, but what is the impact on performance? A normal id is an integer field in the database, a uuid id is a text field. And we have to generate new id's ourselves when creating a new record (admit it, small problem). On the other hand, we never have to run update db sequences again...
Michel van Klink
Vision Development
m.vanklink
 
Posts: 70
Joined: Thu Feb 23, 2012 9:15 am
Location: The Netherlands

Re: Community thoughts about data merging

Postby Harjo » Fri Feb 15, 2013 9:46 am

m.vanklink wrote:
Harjo wrote:The only right way todo this is to convert your solution to UUID PK's and FK's


True, but what is the impact on performance? A normal id is an integer field in the database, a uuid id is a text field. And we have to generate new id's ourselves when creating a new record (admit it, small problem). On the other hand, we never have to run update db sequences again...


Performance: we know several companies, running it this way, and there the performance is minimal, maybe others can collaborate on that or have exact figures
generate id's: Servoy has it's own UUD generator! :) so you don't have to think about it.
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: Community thoughts about data merging

Postby m.vanklink » Fri Feb 15, 2013 3:13 pm

Nice, but still have some caveats. How could we get our current tables transformed to tables with a uuid field as primary key? And all the relations we have defined? And how do we get this deployed to our current customers?
Michel van Klink
Vision Development
m.vanklink
 
Posts: 70
Joined: Thu Feb 23, 2012 9:15 am
Location: The Netherlands

Re: Community thoughts about data merging

Postby karel » Fri Feb 15, 2013 3:27 pm

Doing great Servoyians! Besides the fact that a couple of very interresting approaches are posted here, I'm definetly looking forward for more idea's, tools and suggestions!
And... who knows.. at the end of this year.. we could demo and share some community stuff live.. (but keep this a secret!) :wink:
Karel Broer
SAN Partner
Stb Development - http://www.stb.nl / ServoyCamp - http://www.servoycamp.com
karel
 
Posts: 13
Joined: Mon Jan 30, 2012 6:50 pm

Re: Community thoughts about data merging

Postby Harjo » Fri Feb 15, 2013 3:33 pm

karel wrote:And... who knows.. at the end of this year.. we could demo and share some community stuff live.. (but keep this a secret!) :wink:


:roll: :roll: :lol:
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands


Return to How To

Who is online

Users browsing this forum: No registered users and 7 guests