Hello,
On 2018-05-04 20:02, Roman Fleysher wrote:
For some reason, I did not receive email from Cezary, only comments on it.
About 50% of e-mails from <sqlite-users@mailinglists.sqlite.org> is
marked as SPAM by my server for unknown reason. Independently on an author.
But, most importantly, could you elaborate more on how it works. I agree it is
n-to-n problem. But the solution merges all data into a single cell with all
pairs, which is counter to relational solution.
Please, do not mind ``a single cell''. This is for the bijective mapping
STRING <=> TABLE, which is caused by the fact that a recursive CTEs'
state is a row/record and a whole table must be packed into one row for
an ``one query'' solution. However, do not do it at home ;) You do not
need (and should not) do it in an ``one query''. I'm sorry for
disturbing you.
Ad. solution: AFAIK your tables are created ad hoc and do not exist
permanently. For each relation ``points'' my solution builds related
entities (coordinates or Xs/Ys):
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.
I ask for details, if possible, because the actual problem that I have to
solve is a bit more complicated: I have two of such lists good(x,y) and
bad(x,y) with a coupling condition that if x is removed from one list, it must
be removed from the other. This is easy to add for ones who understand how it
works.
It does complicate nearly nothing. AFAIU good/bad are points on the same
``image'' (implied by a coupling). Simply, add a column: points(x, y,
is_good), or add a relational table to have two tables: points_good(x,
y) and points_bad(x, y) with the same properties as the original
``points(x, y)'' -- that's all -- nothing more.
If you do not want to lose your original data use Simon's solution (most
effective), i.e. add ``deleted'' column to ``points'' and ``n_shadow''
column to ``*axis'' and modify/restore those columns instead of actual
DELETEing/UPDATEing ``n''; alternatively (most obvious but not so
effective in case of big sets of data) make copies of tables/databases.
(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).
I do not want to provide specified DDL statements as they would require
a bit of testing -- unfortunately I cannot test SQLite now.
-- best regards
Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
