I don't understand the error message generated by the following
schema/query:
CREATE TABLE x(
id integer
);
CREATE TABLE y(
id integer
);
CREATE TABLE y_sequence (
seq INTEGER PRIMARY KEY AUTOINCREMENT
);
WITH
x
AS
(SELECT
0 AS name,
0 as rows
UNION ALL
SELECT
'sqlite_sequence' AS "name",
COUNT(*) AS rows
FROM
sqlite_sequence
UNION ALL
SELECT
'y_sequence' AS "name",
COUNT(*) AS rows
FROM
y_sequence
UNION ALL
SELECT
'y' AS "name",
COUNT(*) AS rows
FROM
y
UNION ALL
SELECT
'x' AS "name",
COUNT(*) AS rows
FROM
x
)
SELECT
sm.name AS name,
x.rows AS rows,
SUM(CASE
WHEN
sm2.type="index"
THEN
1
ELSE
0
END) AS "indexes",
SUM(CASE
WHEN
sm2.type="trigger"
THEN
1
ELSE
0
END) AS "triggers",
SUM(CASE
WHEN
sm2.type="trigger" AND sm2.sql LIKE "%BEFORE INSERT%"
THEN
1
ELSE
0
END) AS "bi",
SUM(CASE
WHEN
sm2.type="trigger" AND sm2.sql LIKE "%AFTER INSERT%"
THEN
1
ELSE
0
END) AS "ai",
SUM(CASE
WHEN
sm2.type="trigger" AND sm2.sql LIKE "%BEFORE UPDATE%"
THEN
1
ELSE
0
END) AS "bu",
SUM(CASE
WHEN
sm2.type="trigger" AND sm2.sql LIKE
"%AFTER UPDATE%"
THEN
1
ELSE
0
END) AS "au",
SUM(CASE
WHEN
sm2.type="trigger" AND sm2.sql LIKE "%BEFORE DELETE%"
THEN
1
ELSE
0
END) AS "bd",
SUM(CASE
WHEN
sm2.type="trigger" AND sm2.sql LIKE "%AFTER DELETE%"
THEN
1
ELSE
0
END) AS "ad"
FROM
sqlite_master sm
LEFT JOIN
sqlite_master sm2
ON
sm2.tbl_name = sm.tbl_name
LEFT JOIN
x
ON
x.name = sm.tbl_name
WHERE
sm.tbl_name NOT LIKE ? AND sm.type = ?
GROUP BY
sm.name
ORDER BY
sm.name
;
DBD::SQLite::db prepare failed: recursive aggregate queries not supported
at /usr/lib/perl5/site_perl/5.22/DBIx/ThinSQL.pm line 250.
I don't quite see where the recursive aggregate is. Can anyone
enlighten me?
--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
