how to perform a merge between two tables?

corchi72 · 2011-03-16 15:02:41

Hello I am developing a program to work with databases on a server and a db with the same tables on a client, I perform a merge of the data tables by executing filtering by user, is it possible? What should I write to perform a merge of records between tables?

Thanks

ab · 2011-03-16 17:49:53

You could add records after checking that they are not existing...

In short, you could use some TSQLRestClientDB on the Clients, then a classic Client/Server connection to send the new records to the server.

corchi72 · 2011-03-21 14:30:51

ok Thanks

I saw that executing the function:

        FromUser.Groups.FillMany(AFromDatabase, FromUser.ID);
        while FromUser.Groups.FillOne do
        begin
            if AToDatabase.Retrieve(FromUser.Groups.Dest.ID,AGruppo) then
            begin
              if AFromDatabase.Retrieve(FromUser.Groups.Dest.ID,FromGruppo) then
              begin
                 if not AGruppo.SameValues(FromGruppo) then
                 AToDatabase.Update(FromGruppo);
              end;
            end
            else
              begin
                if AFromDatabase.Retrieve(FromUser.Groups.Dest.ID,FromGruppo) then
                 AToDatabase.Add(FromGruppo,true);
              end;
             ToUser.Groups.ManyAdd(AToDatabase,FromUser.Groups.Source.ID,FromUser.Groups.Dest.ID,true);
        end;

        FromUser.Roles.FillMany(AFromDatabase, FromUser.ID);
        while FromUser.Roles.FillOne do
        begin
            if AToDatabase.Retrieve(FromUser.Roles.Dest.ID,ARole) then
            begin
              if AFromDatabase.Retrieve(FromUser.Roles.Dest.ID,FromRole) then
              begin
                 if not ARole.SameValues(FromRole) then
                 AToDatabase.Update(FromRole);
              end;
            end
            else
              begin
                if AFromDatabase.Retrieve(FromUser.Roles.Dest.ID,FromRole) then
                 AToDatabase.Add(FromRole,true);
              end;
            ToUser.Roles.ManyAdd(AToDatabase,FromUser.Roles.Source.ID,FromUser.Roles.Dest.ID,true);
        end;

        FromUser.Groups.FillMany(AFromDatabase, FromUser.ID);
        while FromUser.Groups.FillOne do
        begin
            if AFromDatabase.Retrieve(FromUser.Groups.Dest.ID,FromGruppo) then
            begin
              if AToDatabase.Retrieve(FromUser.Groups.Dest.ID,AGruppo) then
              begin
                FromGruppo.Roles.DestGet(AFromDatabase, FromGruppo.ID, fIds);
                for i := 0 to Length(fIds) - 1 do
                begin
                    AGruppo.Roles.ManyAdd(AToDatabase, AGruppo.ID, fIds[i],True);
                end;
              end;
            end;
        end;

is not maintained in the source ID is created, but again, so I decided to cancallare always the destination table and copy all records ever in the source table, what should I write to perform INSERT and DELETE * FROM table for each record?

Can you help?

ab · 2011-03-21 15:57:58

I'm not sure I understand your question...

corchi72 · 2011-03-21 16:59:46

excuse me explain.
I have 3 tables in the server and three tables on the client I want to keep in line without having to check if they are different because I always want to delete the three tables of the client and overwrite them with the 3 tables on the server. What should I write?

ab · 2011-03-21 18:46:04

Could you explain the typical steps of this architecture?

Because I'm not sure that the best idea is to clear tables often...

corchi72 · 2011-03-22 08:33:59

Certainly, I have three tables:
1) users
2) groups
3) roles

and three other reports many to many
1) user groups
2) users roles
3) groups roles

The three tables residing in a sqlite file that I put in a server.
So I created a 'client / server application.
The program is used by agents but are not always present in the company so I have to replicate the tables also in their PC with all the reports.
In addition, each agent should contain only information relating to its user, so it must have its user, only groups to which it belongs, and the roles to which it is enabled.

Managing users, groups, and roles, is made by an administrator in the sqlite file on the server, and only when each user connects to the server attempts to synchronize the 6 tables.

the 6 tables are the ones I described above, three more tables with their relations

I thank you in advance for your willingness

ab · 2011-03-22 19:00:28

So you have two databases, one of the Server side, one for each client.

I guess you could have one TSQLRestServer on the server, then one TSQLRestClientDB and one TSQLite3HttpClient on the client side.

The TSQLRestClientDB will manage some client-side data, available also off line.

And the TSQLite3HttpClient will be able to retrieve the data from the server, to refresh its own copy: use a TSQLRecord.CreateAndFillPrepare() to retrieve all data with a WhereSQL statement to get only the data which is interesting the Client.

mORMot Open Source

#1 2011-03-16 15:02:41

how to perform a merge between two tables?

#2 2011-03-16 17:49:53

Re: how to perform a merge between two tables?

#3 2011-03-21 14:30:51

Re: how to perform a merge between two tables?

#4 2011-03-21 15:57:58

Re: how to perform a merge between two tables?

#5 2011-03-21 16:59:46

Re: how to perform a merge between two tables?

#6 2011-03-21 18:46:04

Re: how to perform a merge between two tables?

#7 2011-03-22 08:33:59

Re: how to perform a merge between two tables?

#8 2011-03-22 19:00:28

Re: how to perform a merge between two tables?

Board footer