Am 31.07.2006 um 17:21 schrieb Michael Stone:
On Mon, Jul 31, 2006 at 05:06:00PM +0200, Axel Rau wrote:
Please reconsider your proposals with the above
I'm not sure what you're getting at; could you be more specific?
Let's see...
Am 31.07.2006 um 15:30 schrieb Michael Stone:
And then what happens if you try something like SELECT
X.name,X.children
FROM (SELECT [rtrim]P.path,(SELECT count(*) FROM bacula.file F
The file table is the biggest one, because it contains one row per
backup job and file (see my column description).
You need the filename table here.
WHERE F.pathid = P.pathid
LIMIT 2) > 1
FROM bacula.path P
WHERE P.path ~ '^%@/[^/]*/$'
UNION
SELECT FN.name,0
FROM bacula.path P, bacula.file F, bacula.filename FN
WHERE
P.path = '%@/' AND
P.pathid = F.pathid AND
F.filenameid = FN.filenameid
) AS X
WHERE X.name <> ''
GROUP BY X.name
Tweaking your query and omitting the RTRIM/REPLACE stuff, I get:
-------------------------------
SELECT X.path,X.children
FROM (SELECT P.path,(SELECT count(*) FROM bacula.file F,
bacula.filename FN WHERE F.pathid =
P.pathid AND F.filenameid = FN.filenameid
LIMIT 2) > 1 AS children
FROM bacula.path P
WHERE P.path ~ '^/Users/axel/ports/[^/]*/$'
UNION
SELECT FN.name,0=1
FROM bacula.path P, bacula.file F, bacula.filename FN
WHERE
P.path = '/Users/axel/ports/' AND
P.pathid = F.pathid AND
F.filenameid = FN.filenameid
) AS X
WHERE X.path <> ''
GROUP BY X.path, X.children ;
path | children
------------------------------+----------
.cvsignore | f
/Users/axel/ports/CVS/ | t
/Users/axel/ports/archivers/ | t
INDEX | f
Makefile | f
README | f
(6 rows)
Time: 35.221 ms
-------------------------------
While my version returns:
-------------------------------
name | children
------------+----------
.cvsignore | f
archivers | t
CVS | t
INDEX | f
Makefile | f
README | f
(6 rows)
Time: 30.263 ms
------------+----------
How would you complete your version?
Axel
Axel Rau, ☀Frankfurt , Germany +49-69-951418-0
