[svn.haxx.se] · SVN Dev · SVN Users · SVN Org · TSVN Dev · TSVN Users · Subclipse Dev · Subclipse Users · this month's index

Re: SQL to get WORKING from NODES

From: Greg Stein <gstein_at_gmail.com>
Date: Fri, 24 Sep 2010 10:10:17 -0400

On Fri, Sep 24, 2010 at 07:05, Philip Martin <philip.martin_at_wandisco.com> wrote:
> Philip Martin <philip.martin_at_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
Received on 2010-09-24 16:11:00 CEST

This is an archived mail posted to the Subversion Dev mailing list.