Cezary H. Noweta wrote:
CREATE TABLE points AS WITH cte(x,y,n) AS (SELECT (random() % 10 +
10) % 10 + 1, (random() % 10 + 10) % 10 + 1, 1 UNION ALL SELECT
(random() % 10 + 10) % 10 + 1, (random() % 10 + 10) % 10 + 1, n + 1
FROM cte WHERE n < 100) SELECT x, y FROM cte;
and
CREATE TABLE xaxis AS SELECT x, COUNT(x) AS n FROM points GROUP BY x;
CREATE TABLE yaxis AS SELECT y, COUNT(y) AS n FROM points GROUP BY y;
For example for two points (1,2); (1,-4)
xaxis(x, n):
1 2
yaxis(y, n):
-4 1
2 1
xaxis <== points(x, y) ==> yaxis:
1 -4
1 2
``points(x, y)'' could have (CASCADING) FOREIGN KEY x=>xaxis.x and
y=>yaxis.y and DELETE TRIGGERs which could adjust counters in
``*axis'' tables (DELETEing FROM xaxis WHERE x==OLD.x AND n==0 and
FROM yaxis WHERE y==OLD.y AND n==0; alternatively ``*axis'' could
have UPDATE TRIGGERs which could DELETE FROM points when ``n''
column had achieved 0 or less then nX/nY -- triggers must be
supressed while creating a ``*axis'' table in the latter case).
Then ``iteratively'' (not ``recusively'') DELETE from ``*axis''
WHERE n < threshold until there are no too small Xs/Ys or tables are
empty.
(1)
DELETE FROM xaxis WHERE n < nX;
DELETE FROM yaxis WHERE n < nY;
(2)
which fire CASCADE DELETE of relations FROM points
(3)
which fires DELETE TRIGGERs of points
(4)
which adjust referenced xaxis.n and y.axis.n
(4a)
and further DELETE FROM *axis WHERE n == 0
(4b)
and further DELETE FROM *axis WHERE n < nX/nY
(4c)
and further fire UPDATE TRIGGERs of *axis, which in turn further do
(4a) or (4b).
Cezary, how about this solution with a trigger only on xaxis and on
yaxis?
AFTER DELETE FROM xaxis:
- update yaxis set n=n-1 where y in (select y from points where x=old.x)
- delete points where x=old.x
- delete yaxis where n<ny
AFTER DELETE FROM yaxis:
(same with x and y reversed)
Now, with pragma recursive_triggers this might do the whole job if the
initial deletes(1) are executed. There is a limit on the recursion (/
iteration) depth. And this solution requires at most nx+ny iterations,
where nx is the number of xaxis rows and ny the number of yaxis rows,
On the other hand, if this is not a constest in purely sql
programming, why not have a programme loop with a single delete, and
no derived *axis tables. For instance using tclsh:
while 1 {
db eval {
DELETE FROM points
WHERE x IN (
SELECT x FROM points GROUP BY x HAVING COUNT(*)<(SELECT nx FROM
params)
)
OR y IN (
SELECT y FROM points GROUP BY y HAVING COUNT(*)<(SELECT ny FROM
params)
)
;
}
if {![db changes]} break
} ;# end loop
E.Pasma
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
