Re: [sqlite] Best way to compare two databases
Thanks to all for the answers. I will analize then and work in it... 2006/9/5, John Stanton <[EMAIL PROTECTED]>: You could use brute force and ignorance. Keep a master copy of your database in SQL form and do a diff on the SQL form of the new DB, then export the changes as SQL to update the remote DBs. You may have deletions and update and would need to take them into account when you are doing the diff. Juan Perez wrote: > 2006/9/5, JP <[EMAIL PROTECTED]>: > >> Assumming that the clients will NOT change the original database, I can >> think of 2 methods: >> >> 1. Whole DB: drop all indices, then vacuum, then zip, then propagate. On >> the clients (suscribers), unzip, re-create all indices. >> >> 2. Incremental, add a field to stamp the date on all your records, then >> export to a separate database (zip it). On clients, apply the changes. >> >> 500k records don't say much - how big (in MB) is your DB? I can also >> suggesst changing just the FTP site to another ISP who has unlimited (or >> a very large GB monthly limit). > > > The database is downloaded zipped yet. The database Zipped has 100 > MB. The problem is that some commercials uses RTB connections, because > there are in route. Download of 100 MB is too long... > >> >> jp. >> >> Juan Perez wrote: >> > (excuse me for the other mail, i clicked accidentally the button...) >> > >> > Hi: >> > >> > I think that i explained me bad. >> > In my work i do next until now: >> > >> > Phase 1: I generate automatically a database from a CRM >> > Phase 2: I put the database in the FTP for the commercials of the >> > enterprise. They are located in different parts of the country. >> > Phase 2: The commercials uses my applicaction with the database to >> work. >> > >> > The problem is that now, the datasase is too big (and we pay the FTP >> > to an ISP for the used size and the consumed wide of band). So, i now >> > want to change the process to: >> > >> > Phase 1: I generate automatically a database from a CRM >> > Phase 2: As I already have the previous database, i will generate a >> > diff file in the format i explained in the previous mail. >> > Phase 3: I put the little diff file in the FTP ant the commercials >> > downloades it.. >> > Phase 4: The commercials brings up to date the database using my >> > application (it needs to be changed to do it). >> > Phase 5: The commercials can use my applicaction with the new database >> > to work. >> > >> > So, the question is ¿how to do, in the best way, the new phase 2? >> > >> > 2006/9/4, Juan Perez <[EMAIL PROTECTED]>: >> >> Hi: >> >> >> >> I think that i explained me bad. >> >> In my work i do next work: >> >> >> >> I generate automatically a database from a CRM >> >> >> >> 2006/9/4, Paul Smith <[EMAIL PROTECTED]>: >> >> > At 16:48 04/09/2006, you wrote: >> >> > >Hi all: >> >> > > >> >> > > I have developed a program that uses a sqlite database. >> >> > > Until now the users downloaded an entire new version of the >> >> > >database weekly from the FTP server. >> >> > > But now the database is too big (about 500.000 records) and i >> want >> >> > >to make a database actualization system. >> >> > > So, what is the best way (having the old database and the new >> one) >> >> > >to obtain a file with the differences. Something like this: >> >> > >> >> > Hmm, I don't think I'd do it that way. If you do that, then you need >> >> > to have a copy of the old & new database to compare. >> >> > >> >> > One way around it is to have a 'journal' table which just contains >> >> > all the SQL queries which have been actioned (you have to take care >> >> > if you use transactions) along with an incrementing serial number. >> >> > Then, the user's software can say 'I have all journal entries up to >> >> > 252376', and then you can just given them all the journal entries >> >> > after that number, and they can run the SQL on their end. which will >> >> give. >> >> > >> >> > You can make your routine which modifies the database just keep a >> >> > copy of the SQL used whenever the action succeeds, and store that in >> >> > the Journal table. >> >> > >> >> > >> >> > >> >> > PaulVPOP3 - Internet Email >> Server/Gateway >> >> > [EMAIL PROTECTED] http://www.pscs.co.uk/ >> >> > >> >> > >> >> > >> >> > >> >> >> - >> >> >> >> >> > To unsubscribe, send email to [EMAIL PROTECTED] >> >> > >> >> >> - >> >> >> >> >> > >> >> > >> >> >> > >> > >> - >> >> > >> > To unsubscribe, send email to [EMAIL PROTECTED] >> > >> - >> >> > >> > >> > >> > >> >> >> - >> >> To unsubscribe, send email to [EMAIL PROTECTED]
Re: [sqlite] Best way to compare two databases
You could use brute force and ignorance. Keep a master copy of your database in SQL form and do a diff on the SQL form of the new DB, then export the changes as SQL to update the remote DBs. You may have deletions and update and would need to take them into account when you are doing the diff. Juan Perez wrote: 2006/9/5, JP <[EMAIL PROTECTED]>: Assumming that the clients will NOT change the original database, I can think of 2 methods: 1. Whole DB: drop all indices, then vacuum, then zip, then propagate. On the clients (suscribers), unzip, re-create all indices. 2. Incremental, add a field to stamp the date on all your records, then export to a separate database (zip it). On clients, apply the changes. 500k records don't say much - how big (in MB) is your DB? I can also suggesst changing just the FTP site to another ISP who has unlimited (or a very large GB monthly limit). The database is downloaded zipped yet. The database Zipped has 100 MB. The problem is that some commercials uses RTB connections, because there are in route. Download of 100 MB is too long... jp. Juan Perez wrote: > (excuse me for the other mail, i clicked accidentally the button...) > > Hi: > > I think that i explained me bad. > In my work i do next until now: > > Phase 1: I generate automatically a database from a CRM > Phase 2: I put the database in the FTP for the commercials of the > enterprise. They are located in different parts of the country. > Phase 2: The commercials uses my applicaction with the database to work. > > The problem is that now, the datasase is too big (and we pay the FTP > to an ISP for the used size and the consumed wide of band). So, i now > want to change the process to: > > Phase 1: I generate automatically a database from a CRM > Phase 2: As I already have the previous database, i will generate a > diff file in the format i explained in the previous mail. > Phase 3: I put the little diff file in the FTP ant the commercials > downloades it.. > Phase 4: The commercials brings up to date the database using my > application (it needs to be changed to do it). > Phase 5: The commercials can use my applicaction with the new database > to work. > > So, the question is ¿how to do, in the best way, the new phase 2? > > 2006/9/4, Juan Perez <[EMAIL PROTECTED]>: >> Hi: >> >> I think that i explained me bad. >> In my work i do next work: >> >> I generate automatically a database from a CRM >> >> 2006/9/4, Paul Smith <[EMAIL PROTECTED]>: >> > At 16:48 04/09/2006, you wrote: >> > >Hi all: >> > > >> > > I have developed a program that uses a sqlite database. >> > > Until now the users downloaded an entire new version of the >> > >database weekly from the FTP server. >> > > But now the database is too big (about 500.000 records) and i want >> > >to make a database actualization system. >> > > So, what is the best way (having the old database and the new one) >> > >to obtain a file with the differences. Something like this: >> > >> > Hmm, I don't think I'd do it that way. If you do that, then you need >> > to have a copy of the old & new database to compare. >> > >> > One way around it is to have a 'journal' table which just contains >> > all the SQL queries which have been actioned (you have to take care >> > if you use transactions) along with an incrementing serial number. >> > Then, the user's software can say 'I have all journal entries up to >> > 252376', and then you can just given them all the journal entries >> > after that number, and they can run the SQL on their end. which will >> give. >> > >> > You can make your routine which modifies the database just keep a >> > copy of the SQL used whenever the action succeeds, and store that in >> > the Journal table. >> > >> > >> > >> > PaulVPOP3 - Internet Email Server/Gateway >> > [EMAIL PROTECTED] http://www.pscs.co.uk/ >> > >> > >> > >> > >> - >> >> > To unsubscribe, send email to [EMAIL PROTECTED] >> > >> - >> >> > >> > >> > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Best way to compare two databases
"Juan Perez" <[EMAIL PROTECTED]> wrote: > > I have developed a program that uses a sqlite database. > Until now the users downloaded an entire new version of the > database weekly from the FTP server. > But now the database is too big (about 500.000 records) and i want > to make a database actualization system. > So, what is the best way (having the old database and the new one) > to obtain a file with the differences. Something like this: > http://samba.anu.edu.au/rsync/ -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Best way to compare two databases
Hi Juan!
People give you a several advices about alternative solutions. But I try
to get back to your original question ("¿how to do, in the best way, the
new phase 2?")
I suppose that database structure isn't changed and we're talking about
tracking data differences only. I also suppose that your remote
customers use your database just as a some kind of dictionary - they do
not change data, only read them. Am I right?
So, my proposed solution is:
1. I use an external programming language, not only SQLite. In my
environment it is LuaSQLite, LUA scripting language with interface to
SQLite. Surely, you may choose something you like. 2. For every table
I'll add a autoincrement integer to have an obvious ID (if table already
do not have one, of course :) ). 3. Export all table data to text and
create diff-file with any text-analysing tool (my choice is Lua too) or
3a. Or open both databases and do it on the fly, as you wish Having a
unique record id created in (2) will help you perform step (3) by very
evident way
* bonus if you are using Lua too :) . I can send to you my code [export
sqltable to CSV-file] as a working sample :)
On customer side action when receive a diff-file will be:
1. Execute (LuaSqlite3) importing-tool, who will convert your entries
("+column1=value1;column2=value2") to something like 'INSERT OR REPLACE
...'
("-column1=value2;column2=value4") to sometjing like DELETE FROM tbl
WHERE 2. Wrap these inserts/deletes with BEGIN/END transaction,
execute command.
This way may be is not optimal but it is so clear, and after you realize
that there's a many point of future optimization. But if you implement
it you will start your idea from the dead point, get some result in
short time. I think it is important to see how big your diffile will be.
Hope it helps!
WBR, Denis.
-Original Message-
From: Juan Perez [mailto:[EMAIL PROTECTED]
Sent: Monday, September 04, 2006 9:17 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Best way to compare two databases
(excuse me for the other mail, i clicked accidentally the button...)
Hi:
I think that i explained me bad.
In my work i do next until now:
Phase 1: I generate automatically a database from a CRM
Phase 2: I put the database in the FTP for the commercials of the
enterprise. They are located in different parts of the country. Phase
2: The commercials uses my applicaction with the database to work.
The problem is that now, the datasase is too big (and we pay the FTP to
an ISP for the used size and the consumed wide of band). So, i now want
to change the process to:
Phase 1: I generate automatically a database from a CRM
Phase 2: As I already have the previous database, i will generate a
diff file in the format i explained in the previous mail. Phase 3: I
put the little diff file in the FTP ant the commercials downloades it..
Phase 4: The commercials brings up to date the database using my
application (it needs to be changed to do it). Phase 5: The commercials
can use my applicaction with the new database to work.
So, the question is ¿how to do, in the best way, the new phase 2?
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Best way to compare two databases
Have you tried exporting the whole database to a text file, zipping that file and sending it? The compression is hugely better on pure text rather than the database file. It's also quicker than doing a drop/vacuum although in theory that should give a similar sized zip file. Steve -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Juan Perez Sent: 05 September 2006 09:13 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Best way to compare two databases 2006/9/5, JP <[EMAIL PROTECTED]>: > Assumming that the clients will NOT change the original database, I can > think of 2 methods: > > 1. Whole DB: drop all indices, then vacuum, then zip, then propagate. On > the clients (suscribers), unzip, re-create all indices. > > 2. Incremental, add a field to stamp the date on all your records, then > export to a separate database (zip it). On clients, apply the changes. > > 500k records don't say much - how big (in MB) is your DB? I can also > suggesst changing just the FTP site to another ISP who has unlimited (or > a very large GB monthly limit). The database is downloaded zipped yet. The database Zipped has 100 MB. The problem is that some commercials uses RTB connections, because there are in route. Download of 100 MB is too long... > > jp. > > Juan Perez wrote: > > (excuse me for the other mail, i clicked accidentally the button...) > > > > Hi: > > > > I think that i explained me bad. > > In my work i do next until now: > > > > Phase 1: I generate automatically a database from a CRM > > Phase 2: I put the database in the FTP for the commercials of the > > enterprise. They are located in different parts of the country. > > Phase 2: The commercials uses my applicaction with the database to work. > > > > The problem is that now, the datasase is too big (and we pay the FTP > > to an ISP for the used size and the consumed wide of band). So, i now > > want to change the process to: > > > > Phase 1: I generate automatically a database from a CRM > > Phase 2: As I already have the previous database, i will generate a > > diff file in the format i explained in the previous mail. > > Phase 3: I put the little diff file in the FTP ant the commercials > > downloades it.. > > Phase 4: The commercials brings up to date the database using my > > application (it needs to be changed to do it). > > Phase 5: The commercials can use my applicaction with the new database > > to work. > > > > So, the question is ¿how to do, in the best way, the new phase 2? > > > > 2006/9/4, Juan Perez <[EMAIL PROTECTED]>: > >> Hi: > >> > >> I think that i explained me bad. > >> In my work i do next work: > >> > >> I generate automatically a database from a CRM > >> > >> 2006/9/4, Paul Smith <[EMAIL PROTECTED]>: > >> > At 16:48 04/09/2006, you wrote: > >> > >Hi all: > >> > > > >> > > I have developed a program that uses a sqlite database. > >> > > Until now the users downloaded an entire new version of the > >> > >database weekly from the FTP server. > >> > > But now the database is too big (about 500.000 records) and i want > >> > >to make a database actualization system. > >> > > So, what is the best way (having the old database and the new one) > >> > >to obtain a file with the differences. Something like this: > >> > > >> > Hmm, I don't think I'd do it that way. If you do that, then you need > >> > to have a copy of the old & new database to compare. > >> > > >> > One way around it is to have a 'journal' table which just contains > >> > all the SQL queries which have been actioned (you have to take care > >> > if you use transactions) along with an incrementing serial number. > >> > Then, the user's software can say 'I have all journal entries up to > >> > 252376', and then you can just given them all the journal entries > >> > after that number, and they can run the SQL on their end. which will > >> give. > >> > > >> > You can make your routine which modifies the database just keep a > >> > copy of the SQL used whenever the action succeeds, and store that in > >> > the Journal table. > >> > > >> > > >> > > >> > PaulVPOP3 - Internet Email Server/G
Re: [sqlite] Best way to compare two databases
2006/9/5, JP <[EMAIL PROTECTED]>: Assumming that the clients will NOT change the original database, I can think of 2 methods: 1. Whole DB: drop all indices, then vacuum, then zip, then propagate. On the clients (suscribers), unzip, re-create all indices. 2. Incremental, add a field to stamp the date on all your records, then export to a separate database (zip it). On clients, apply the changes. 500k records don't say much - how big (in MB) is your DB? I can also suggesst changing just the FTP site to another ISP who has unlimited (or a very large GB monthly limit). The database is downloaded zipped yet. The database Zipped has 100 MB. The problem is that some commercials uses RTB connections, because there are in route. Download of 100 MB is too long... jp. Juan Perez wrote: > (excuse me for the other mail, i clicked accidentally the button...) > > Hi: > > I think that i explained me bad. > In my work i do next until now: > > Phase 1: I generate automatically a database from a CRM > Phase 2: I put the database in the FTP for the commercials of the > enterprise. They are located in different parts of the country. > Phase 2: The commercials uses my applicaction with the database to work. > > The problem is that now, the datasase is too big (and we pay the FTP > to an ISP for the used size and the consumed wide of band). So, i now > want to change the process to: > > Phase 1: I generate automatically a database from a CRM > Phase 2: As I already have the previous database, i will generate a > diff file in the format i explained in the previous mail. > Phase 3: I put the little diff file in the FTP ant the commercials > downloades it.. > Phase 4: The commercials brings up to date the database using my > application (it needs to be changed to do it). > Phase 5: The commercials can use my applicaction with the new database > to work. > > So, the question is ¿how to do, in the best way, the new phase 2? > > 2006/9/4, Juan Perez <[EMAIL PROTECTED]>: >> Hi: >> >> I think that i explained me bad. >> In my work i do next work: >> >> I generate automatically a database from a CRM >> >> 2006/9/4, Paul Smith <[EMAIL PROTECTED]>: >> > At 16:48 04/09/2006, you wrote: >> > >Hi all: >> > > >> > > I have developed a program that uses a sqlite database. >> > > Until now the users downloaded an entire new version of the >> > >database weekly from the FTP server. >> > > But now the database is too big (about 500.000 records) and i want >> > >to make a database actualization system. >> > > So, what is the best way (having the old database and the new one) >> > >to obtain a file with the differences. Something like this: >> > >> > Hmm, I don't think I'd do it that way. If you do that, then you need >> > to have a copy of the old & new database to compare. >> > >> > One way around it is to have a 'journal' table which just contains >> > all the SQL queries which have been actioned (you have to take care >> > if you use transactions) along with an incrementing serial number. >> > Then, the user's software can say 'I have all journal entries up to >> > 252376', and then you can just given them all the journal entries >> > after that number, and they can run the SQL on their end. which will >> give. >> > >> > You can make your routine which modifies the database just keep a >> > copy of the SQL used whenever the action succeeds, and store that in >> > the Journal table. >> > >> > >> > >> > PaulVPOP3 - Internet Email Server/Gateway >> > [EMAIL PROTECTED] http://www.pscs.co.uk/ >> > >> > >> > >> > >> - >> >> > To unsubscribe, send email to [EMAIL PROTECTED] >> > >> - >> >> > >> > >> > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Best way to compare two databases
Assumming that the clients will NOT change the original database, I can think of 2 methods: 1. Whole DB: drop all indices, then vacuum, then zip, then propagate. On the clients (suscribers), unzip, re-create all indices. 2. Incremental, add a field to stamp the date on all your records, then export to a separate database (zip it). On clients, apply the changes. 500k records don't say much - how big (in MB) is your DB? I can also suggesst changing just the FTP site to another ISP who has unlimited (or a very large GB monthly limit). jp. Juan Perez wrote: (excuse me for the other mail, i clicked accidentally the button...) Hi: I think that i explained me bad. In my work i do next until now: Phase 1: I generate automatically a database from a CRM Phase 2: I put the database in the FTP for the commercials of the enterprise. They are located in different parts of the country. Phase 2: The commercials uses my applicaction with the database to work. The problem is that now, the datasase is too big (and we pay the FTP to an ISP for the used size and the consumed wide of band). So, i now want to change the process to: Phase 1: I generate automatically a database from a CRM Phase 2: As I already have the previous database, i will generate a diff file in the format i explained in the previous mail. Phase 3: I put the little diff file in the FTP ant the commercials downloades it.. Phase 4: The commercials brings up to date the database using my application (it needs to be changed to do it). Phase 5: The commercials can use my applicaction with the new database to work. So, the question is ¿how to do, in the best way, the new phase 2? 2006/9/4, Juan Perez <[EMAIL PROTECTED]>: Hi: I think that i explained me bad. In my work i do next work: I generate automatically a database from a CRM 2006/9/4, Paul Smith <[EMAIL PROTECTED]>: > At 16:48 04/09/2006, you wrote: > >Hi all: > > > > I have developed a program that uses a sqlite database. > > Until now the users downloaded an entire new version of the > >database weekly from the FTP server. > > But now the database is too big (about 500.000 records) and i want > >to make a database actualization system. > > So, what is the best way (having the old database and the new one) > >to obtain a file with the differences. Something like this: > > Hmm, I don't think I'd do it that way. If you do that, then you need > to have a copy of the old & new database to compare. > > One way around it is to have a 'journal' table which just contains > all the SQL queries which have been actioned (you have to take care > if you use transactions) along with an incrementing serial number. > Then, the user's software can say 'I have all journal entries up to > 252376', and then you can just given them all the journal entries > after that number, and they can run the SQL on their end. which will give. > > You can make your routine which modifies the database just keep a > copy of the SQL used whenever the action succeeds, and store that in > the Journal table. > > > > PaulVPOP3 - Internet Email Server/Gateway > [EMAIL PROTECTED] http://www.pscs.co.uk/ > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Best way to compare two databases
(excuse me for the other mail, i clicked accidentally the button...) Hi: I think that i explained me bad. In my work i do next until now: Phase 1: I generate automatically a database from a CRM Phase 2: I put the database in the FTP for the commercials of the enterprise. They are located in different parts of the country. Phase 2: The commercials uses my applicaction with the database to work. The problem is that now, the datasase is too big (and we pay the FTP to an ISP for the used size and the consumed wide of band). So, i now want to change the process to: Phase 1: I generate automatically a database from a CRM Phase 2: As I already have the previous database, i will generate a diff file in the format i explained in the previous mail. Phase 3: I put the little diff file in the FTP ant the commercials downloades it.. Phase 4: The commercials brings up to date the database using my application (it needs to be changed to do it). Phase 5: The commercials can use my applicaction with the new database to work. So, the question is ¿how to do, in the best way, the new phase 2? 2006/9/4, Juan Perez <[EMAIL PROTECTED]>: Hi: I think that i explained me bad. In my work i do next work: I generate automatically a database from a CRM 2006/9/4, Paul Smith <[EMAIL PROTECTED]>: > At 16:48 04/09/2006, you wrote: > >Hi all: > > > > I have developed a program that uses a sqlite database. > > Until now the users downloaded an entire new version of the > >database weekly from the FTP server. > > But now the database is too big (about 500.000 records) and i want > >to make a database actualization system. > > So, what is the best way (having the old database and the new one) > >to obtain a file with the differences. Something like this: > > Hmm, I don't think I'd do it that way. If you do that, then you need > to have a copy of the old & new database to compare. > > One way around it is to have a 'journal' table which just contains > all the SQL queries which have been actioned (you have to take care > if you use transactions) along with an incrementing serial number. > Then, the user's software can say 'I have all journal entries up to > 252376', and then you can just given them all the journal entries > after that number, and they can run the SQL on their end. which will give. > > You can make your routine which modifies the database just keep a > copy of the SQL used whenever the action succeeds, and store that in > the Journal table. > > > > PaulVPOP3 - Internet Email Server/Gateway > [EMAIL PROTECTED] http://www.pscs.co.uk/ > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Best way to compare two databases
Hi: I think that i explained me bad. In my work i do next work: I generate automatically a database from a CRM 2006/9/4, Paul Smith <[EMAIL PROTECTED]>: At 16:48 04/09/2006, you wrote: >Hi all: > > I have developed a program that uses a sqlite database. > Until now the users downloaded an entire new version of the >database weekly from the FTP server. > But now the database is too big (about 500.000 records) and i want >to make a database actualization system. > So, what is the best way (having the old database and the new one) >to obtain a file with the differences. Something like this: Hmm, I don't think I'd do it that way. If you do that, then you need to have a copy of the old & new database to compare. One way around it is to have a 'journal' table which just contains all the SQL queries which have been actioned (you have to take care if you use transactions) along with an incrementing serial number. Then, the user's software can say 'I have all journal entries up to 252376', and then you can just given them all the journal entries after that number, and they can run the SQL on their end. which will give. You can make your routine which modifies the database just keep a copy of the SQL used whenever the action succeeds, and store that in the Journal table. PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/ - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Best way to compare two databases
At 16:48 04/09/2006, you wrote: Hi all: I have developed a program that uses a sqlite database. Until now the users downloaded an entire new version of the database weekly from the FTP server. But now the database is too big (about 500.000 records) and i want to make a database actualization system. So, what is the best way (having the old database and the new one) to obtain a file with the differences. Something like this: Hmm, I don't think I'd do it that way. If you do that, then you need to have a copy of the old & new database to compare. One way around it is to have a 'journal' table which just contains all the SQL queries which have been actioned (you have to take care if you use transactions) along with an incrementing serial number. Then, the user's software can say 'I have all journal entries up to 252376', and then you can just given them all the journal entries after that number, and they can run the SQL on their end. which will give. You can make your routine which modifies the database just keep a copy of the SQL used whenever the action succeeds, and store that in the Journal table. PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Best way to compare two databases
Hi all: I have developed a program that uses a sqlite database. Until now the users downloaded an entire new version of the database weekly from the FTP server. But now the database is too big (about 500.000 records) and i want to make a database actualization system. So, what is the best way (having the old database and the new one) to obtain a file with the differences. Something like this: --- Difference file-- table1 +column1=value1;column2=value2 -column1=value2;column2=value4 table2 +column1=value5;column2=value6 -column1=value7;column2=value8 --- End of difference file-- Where the "+" indicates a new register in the database and the "-" indicates the deletion of the register. Thanks in advance. - To unsubscribe, send email to [EMAIL PROTECTED] -
