On 30.07.2013 14:49, fnoyan...@yahoo.com wrote:
Yes, it is slow actually. But I could not do it with SQL. I wish I could do
more with SQL than code, which would give a good performance improvement.
From overall application point of view, I may be considering using threads to
read from database, so that the performance will be improved a bit.
On 30/07/2013, at 4:26 PM, "Stadin, Benjamin"
<benjamin.sta...@heidelberg-mobil.com> wrote:
That can work, if performance is of no concern. Otherwise it will become
miserably slow.
Von: Fehmi Noyan ISI <fnoyan...@yahoo.com>
Antworten an: Fehmi Noyan ISI <fnoyan...@yahoo.com>
Datum: Dienstag, 30. Juli 2013 01:54
An: Benjamin Stadin <benjamin.sta...@heidelberg-mobil.com>, General Discussion of
SQLite Database <sqlite-users@sqlite.org>
Betreff: Re: [sqlite] Comparing two tables column by column
The approach I am using to compare tableA_old and tableA_new is;
typedef struct container_t {
// a structure to pass parameters into callbacks
} container;
static int callback_2(){
// compare two values in the resulting table if they are different
// write them into a resulting file
}
static int callback_1(){
for (each column){
char sql[256];
sprintf(sql,"select %s from tableA_old where
pkey=%s",columnName,primarykey);
sqlite3_exec(db,sql,callback_2,,)
}
}
int main(){
// import CS file into DB
// for each pair of files (each file is a table and files have
// the same column names in the same order). Actually, these are
// records for different dates
char sql[256] = "select * from tableA_new;";
container c;
sqlite3_exec(db,sql,callback_1,(void*)c,null)
}
From: "Stadin, Benjamin" <benjamin.sta...@heidelberg-mobil.com>
To: Fehmi Noyan ISI <fnoyan...@yahoo.com>; General Discussion of SQLite Database
<sqlite-users@sqlite.org>
Sent: Tuesday, July 30, 2013 9:00 AM
Subject: Re: [sqlite] Comparing two tables column by column
If you like ruby, I have another idea to get you going (maybe without
needing to write much code):
- Use a registered function to SQLite to create MD5 or SHA1 keys for rows
in the table. Here is a ruby snippet that registers a SHA1 function:
http://www.copiousfreetime.org/articles/2009/01/10/writing-sql-functions-in
-ruby.html
- Join both tables by your primary key and select those where the SHA keys
don't match
- go threw each column programmatically in your ruby code and simply
compare column values with each other
If you then need performance and get by some means a trigger for your
newly inserted records, add a sha1 field to all of your tables and
precompute and index the sha1 for each table.
Benjamin Stadin
Am 30.07.13 01:07 schrieb "Fehmi Noyan ISI" unter <fnoyan...@yahoo.com>:
EXCEPT query gave the different rows in tables, but what I am after is
the different values for existing records.
The column names are exactly the same, however the number of rows may
differ (with most of the records are the same).
________________________________
From: Simon Slavin <slav...@bigfraud.org>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Monday, July 29, 2013 9:10 PM
Subject: Re: [sqlite] Comparing two tables column by column
On 29 Jul 2013, at 12:36pm, Fabian Klebert
<f.kleb...@klebert-engineering.de> wrote:
Wouldn't
SELECT * FROM table1
EXCEPT
SELECT * FROM table2
solve this problem?
I think it does for the example provided. Not sure if it would work in
real-world environment.
There are two elements: making sure the same rows are present, and making
sure the contents of the rows match. I would probably use EXCEPT
commands to find out entries in one database which weren't in the other,
then use INTERSECT command to check that the fields in the rows which
were in both. But that's just at first glance.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
a) Store your table backups ordered by a primary key (not rowid but
integer primary key).
b) Make a whole table select ordered by the same primary key for the
current table.
c) Merge both tables.
This will be faster cause you won't need to trace through the BTREE
(B+TREE) for each recordset.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
