On Wed, Nov 18, 2015 at 11:10:47PM +0100, E.Pasma wrote:
> op 18-11-2015 21:23 schreef Richard Hipp op drh at sqlite.org:
> > The "sqldiff.exe" command-line utility has an (undocumented and
> > unsupported) "--debug 2" option that will show you the SQL that it
> > uses to do a diff between two tables. Maybe look at that and tweak it
> > for your use?
> >
> Does that look like this:
> SELECT * FROM
> (
> SELECT 'added' AS what, a.* FROM a
> UNION ALL
> SELECT 'deleted', b.* FROM b
> )
> GROUP BY <all columns from a or b>
> HAVING COUNT (*)<>2
> ;
No, it looks like this when the columns are all in the primary key:
SELECT A.a, A.b, 2 -- deleted row
FROM main.toy A
WHERE NOT EXISTS(SELECT 1 FROM aux.toy B
WHERE A.a=B.a AND A.b=B.b)
UNION ALL
SELECT B.a, B.b, 3 -- inserted row
FROM aux.toy B
WHERE NOT EXISTS(SELECT 1 FROM main.toy A
WHERE A.a=B.a AND A.b=B.b)
ORDER BY 1, 2;
Else it looks like this when not all of them are in the PK:
SELECT B.a, 1, -- changed row
A.b IS NOT B.b, B.b
FROM main.toy A, aux.toy B
WHERE A.a=B.a
AND (A.b IS NOT B.b)
UNION ALL
SELECT A.a, 2, -- deleted row
NULL, NULL
FROM main.toy A
WHERE NOT EXISTS(SELECT 1 FROM aux.toy B
WHERE A.a=B.a)
UNION ALL
SELECT B.a, 3, -- inserted row
1, B.b
FROM aux.toy B
WHERE NOT EXISTS(SELECT 1 FROM main.toy A
WHERE A.a=B.a)
ORDER BY 1;
It's one UNION ALL when all columns are part of the PK, two UNION ALLs
otherwise. sqldiff wasn't smart enough to recognize that a UNIQUE INDEX
exists (other than the PK) on all columns when it does exist, in which
case is should use the first query (one UNION ALL).
Nico
--
