idea for joining a complex view

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

idea for joining a complex view

E.Pasma
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users