Hi Keith,
Indeed just removing the CTE creation of the DIGITS makes Dan's version up
to speed.
Would the "wholenumber" external SQLite module help :
- to make SQLite code cleaner ? (like "generate_series" of Postgresql, or
"dual" of Oracle)
- still provide the same speed-up ?
Portfolio of typical Sudokus
-- easy (0 sec)
'53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79'
-- medium (2 sec)
'1....7.9..3..2...8..96..5....53..9...1..8...26....4...3......1..4......7..7...3..'
-- hard (200 s)
'8..........36......7..9.2...5...7.......457.....1...3...1....68..85...1..9....4..'
WITH RECURSIVE input(sud) AS (
VALUES(
'53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79'
)
),
/* A table filled with digits 1..9, inclusive. */
digits(z, lp) AS (
VALUES('1', 1),('2', 2) ,('3', 3),('4', 4),('5', 5),('6', 6),('7',
7),('8', 8),('9', 9)
),
/* The tricky bit. */
x(s, ind) AS (
SELECT sud, instr(sud, '.') FROM input
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;
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
