Hello, I still want to mention an idea for joining a complex view that I used
to for the sudoku solver
https://www.sqlite.org/lang_with.html#sudoku
It is a virtual table with a single row and a column that just echos the value
it gets passed in. It is named magnet here. The view is as below. The source of
the extension is not included.
Drawbacks:
- need to compile an extension
- a unique sort is applied to the result set
- current version only deals with a single column
E. Pasma
.load sqlite_magnet
CREATE VIEW sudsol
AS
SELECT m1.value AS sud, m2.value AS sol
FROM magnet m1, magnet m2
WHERE m2.value IN (
WITH RECURSIVE
digits(z, lp) AS (
VALUES('1', 1)
UNION ALL SELECT
CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
),
x(s, ind) AS (
SELECT m1.value AS sud, instr(m1.value, '.')
UNION ALL
SELECT
substr(s, 1, ind-1) || z || substr(s, ind+1),
instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
FROM x, digits AS z
WHERE ind>0
AND NOT EXISTS (
SELECT 1
FROM digits AS lp
WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)
OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1)
OR z.z = substr(s, (((ind-1)/3) % 3) * 3
+ ((ind-1)/27) * 27 + lp
+ ((lp-1) / 3) * 6, 1)
)
)
SELECT s FROM x WHERE ind=0
)
;
SELECT sol FROM sudsol WHERE sud=
'53.'||'.7.'||'...'
|| '6..'||'195'||'...'
|| '.98'||'...'||'.6.'
|| '8..'||'.6.'||'..3'
|| '4..'||'8.3'||'..1'
|| '7..'||'.2.'||'..6'
|| '.6.'||'...'||'28.'
|| '...'||'419'||'..5'
|| '...'||'.8.'||'.79'
;
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
