You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@subversion.apache.org by Philip Martin <ph...@wandisco.com> on 2010/09/24 10:37:28 UTC
SQL to get WORKING from NODES
These two queries don't work:
-- STMT_SELECT_WORKING_NODE_CHILDREN_1
SELECT local_relpath FROM nodes
WHERE wc_id = ?1 AND parent_relpath = ?2
AND op_depth = (SELECT MAX(op_depth) FROM nodes
WHERE wc_id = ?1 AND parent_relpath = ?2 AND op_depth > 0);
-- STMT_COUNT_WORKING_NODE_CHILDREN_1
SELECT COUNT(*) FROM nodes
WHERE wc_id = ?1 AND parent_relpath = ?2
AND op_depth = (SELECT MAX(op_depth) FROM nodes
WHERE wc_id = ?1 AND parent_relpath = ?2 AND op_depth > 0);
The problem is that they determine the higest op_depth for the
children and return only the children with that op_depth. This
happens to work if all the children have the same highest op_depth but
that's just an accident.
I can fix the first one as follows:
-- STMT_SELECT_WORKING_NODE_CHILDREN_1
SELECT local_relpath FROM nodes
WHERE wc_id = ?1 AND parent_relpath = ?2 AND op_depth > 0
GROUP BY local_relpath;
but I don't know how to fix the second one. How do I count the number
of rows returned by that GROUP BY query?
This leads on to the problem of selecting just the highest op_depth
for each child. Is it possible to get one query to return just the
highest op_depth for each child?
--
Philip
Re: SQL to get WORKING from NODES
Posted by Greg Stein <gs...@gmail.com>.
On Fri, Sep 24, 2010 at 10:26, Philip Martin <ph...@wandisco.com> wrote:
> Greg Stein <gs...@gmail.com> writes:
>
>> Step back and look at that code.
>>
>> The gather_children() got a bit more complicated because I was trying
>> to get the list of children from BASE_NODE and WORKING_NODE, and union
>> those together (or skip the union altogether in certain cases). With
>> NODES, it becomes one simple query:
>>
>> SELECT DISTINCT local_relpath FROM nodes
>> WHERE wc_id = ?1 AND parent_relpath = ?2;
>>
>> Done.
>>
>> Pass that query to single_table_children() and return.
>>
>> count_children, add_children_to_hash, and union_children can all be
>> eliminated with SVN_WC__NODES_ONLY.
>
> Yes, but in the short term NODES is going to look like BASE/WORKING.
That's fine, but not for gather_children() when !base_only. By
definition, it is blending BASE and WORKING children, and that is
exactly what NODES does for you already.
>...
Cheers,
-g
Re: SQL to get WORKING from NODES
Posted by Philip Martin <ph...@wandisco.com>.
Greg Stein <gs...@gmail.com> writes:
> Step back and look at that code.
>
> The gather_children() got a bit more complicated because I was trying
> to get the list of children from BASE_NODE and WORKING_NODE, and union
> those together (or skip the union altogether in certain cases). With
> NODES, it becomes one simple query:
>
> SELECT DISTINCT local_relpath FROM nodes
> WHERE wc_id = ?1 AND parent_relpath = ?2;
>
> Done.
>
> Pass that query to single_table_children() and return.
>
> count_children, add_children_to_hash, and union_children can all be
> eliminated with SVN_WC__NODES_ONLY.
Yes, but in the short term NODES is going to look like BASE/WORKING.
>>> This leads on to the problem of selecting just the highest op_depth
>>> for each child. Is it possible to get one query to return just the
>>> highest op_depth for each child?
>>
>> I suspect we will still want to do this at some point.
>
> Maybe. The query that you have (a self join) should work but is quite
> expensive, so let's hope you won't need it often.
We need something. Status is too slow when it does per-node queries,
so we need to switch to per-dir queries. Do a per-dir query for
children, cache the data, and then iterate over the children to
produce the status. That was the only way my prototype for single-db
was fast enough. Now I suppose we could get all children at all
op_depths and discard some, but we really only want the zero and max
op_depths.
--
Philip
Re: SQL to get WORKING from NODES
Posted by Greg Stein <gs...@gmail.com>.
On Fri, Sep 24, 2010 at 07:05, Philip Martin <ph...@wandisco.com> wrote:
> Philip Martin <ph...@wandisco.com> writes:
>
>> but I don't know how to fix the second one. How do I count the number
>> of rows returned by that GROUP BY query?
>
> From IRC the following was suggested
>
> -- STMT_SELECT_WORKING_NODE_CHILDREN_1
> SELECT DISTINCT local_relpath FROM nodes
> WHERE wc_id = ?1 AND parent_relpath = ?2 AND op_depth > 0;
>
> -- STMT_COUNT_WORKING_NODE_CHILDREN_1
> SELECT COUNT(*) FROM (SELECT DISTINCT local_relpath FROM nodes
> WHERE wc_id = ?1 AND parent_relpath = ?2
> AND op_depth > 0);
>
> In the longer term if/when we switch to per-dir queries we can
> probably eliminate the counting step in some or all cases.
When switching to NODES, the counting step is not required and the
children fetching algorithm should simply be changed.
Step back and look at that code.
The gather_children() got a bit more complicated because I was trying
to get the list of children from BASE_NODE and WORKING_NODE, and union
those together (or skip the union altogether in certain cases). With
NODES, it becomes one simple query:
SELECT DISTINCT local_relpath FROM nodes
WHERE wc_id = ?1 AND parent_relpath = ?2;
Done.
Pass that query to single_table_children() and return.
count_children, add_children_to_hash, and union_children can all be
eliminated with SVN_WC__NODES_ONLY.
>> This leads on to the problem of selecting just the highest op_depth
>> for each child. Is it possible to get one query to return just the
>> highest op_depth for each child?
>
> I suspect we will still want to do this at some point.
Maybe. The query that you have (a self join) should work but is quite
expensive, so let's hope you won't need it often.
Cheers,
-g
Re: SQL to get WORKING from NODES
Posted by Philip Martin <ph...@wandisco.com>.
Philip Martin <ph...@wandisco.com> writes:
>> This leads on to the problem of selecting just the highest op_depth
>> for each child. Is it possible to get one query to return just the
>> highest op_depth for each child?
>
> I suspect we will still want to do this at some point.
This was suggested:
SELECT * FROM nodes as n
WHERE wc_id = ?1
AND parent_relpath = ?2
AND op_depth = (SELECT MAX(op_depth) FROM nodes as m
WHERE m.wc_id = n.wc_id
AND m.local_relpath = n.local_relpath
AND m.op_depth > 0);
which I think does what I want.
--
Philip
Re: SQL to get WORKING from NODES
Posted by Philip Martin <ph...@wandisco.com>.
Philip Martin <ph...@wandisco.com> writes:
> but I don't know how to fix the second one. How do I count the number
> of rows returned by that GROUP BY query?
>From IRC the following was suggested
-- STMT_SELECT_WORKING_NODE_CHILDREN_1
SELECT DISTINCT local_relpath FROM nodes
WHERE wc_id = ?1 AND parent_relpath = ?2 AND op_depth > 0;
-- STMT_COUNT_WORKING_NODE_CHILDREN_1
SELECT COUNT(*) FROM (SELECT DISTINCT local_relpath FROM nodes
WHERE wc_id = ?1 AND parent_relpath = ?2
AND op_depth > 0);
In the longer term if/when we switch to per-dir queries we can
probably eliminate the counting step in some or all cases.
> This leads on to the problem of selecting just the highest op_depth
> for each child. Is it possible to get one query to return just the
> highest op_depth for each child?
I suspect we will still want to do this at some point.
--
Philip
Re: SQL to get WORKING from NODES
Posted by Philip Martin <ph...@wandisco.com>.
Philip Martin <ph...@wandisco.com> writes:
> but I don't know how to fix the second one. How do I count the number
> of rows returned by that GROUP BY query?