Hi group,
this is a directory tree query for a backup system (http://
sourceforge.net/projects/bacula).
You provide a path and get back the names of the children plus a
boolean telling if the child has itself children.
The %@ stands for the initial path:
On Mon, Jul 31, 2006 at 12:48:11PM +0200, Axel Rau wrote:
WHERE P.path ~ '^%@/[^/]*/$' ) AS NLPC
This can't be indexed. You might try something like
WHERE P.path LIKE '[EMAIL PROTECTED]' AND P.path ~ '^%@/[^/]*/$'
The schema could be a lot more intelligent here. (E.g., store
Am 31.07.2006 um 13:15 schrieb Michael Stone:
On Mon, Jul 31, 2006 at 12:48:11PM +0200, Axel Rau wrote:
WHERE P.path ~ '^%@/[^/]*/$' ) AS NLPC
This can't be indexed. You might try something like WHERE P.path
LIKE '[EMAIL PROTECTED]' AND P.path ~ '^%@/[^/]*/$'
Why does it
Am 31.07.2006 um 15:30 schrieb Michael Stone:
If I understand the intend of this SQL,
Let me show the tables first:
Table bacula.path( 65031 rows)
Column | Type | Modifiers
+-
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?
Mike Stone
---(end of broadcast)---
TIP 1: if posting/reading through
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
Am 31.07.2006 um 17:54 schrieb Axel Rau:
Tweaking your query and omitting the RTRIM/REPLACE stuff, I get:
My example did not cover the case of empty subdirectories, in which
case your simplified query fails:
---
path | children
Am 31.07.2006 um 15:53 schrieb Mark Lewis:
It seems like you might be able to avoid the expensive directory
lookups
entirely without changing the schema by defining an immutable function
dir_depth(path), which would just count the number of forward slashes.
Then create a functional index on
On Mon, Jul 31, 2006 at 05:54:41PM +0200, Axel Rau wrote:
The file table is the biggest one, because it contains one row per
backup job and file (see my column description).
I never saw a column description--that would certainly help. :) I saw a
schema, but not an explanation of what the
Am 31.07.2006 um 19:08 schrieb Michael Stone:
I never saw a column description--that would certainly help. :) I
saw a schema, but not an explanation of what the elements do. From
what I can understand of what you're saying, it is sounding as
though the bacula.file table contains an entry
10 matches
Mail list logo