Given a path, say:
/subversion/bindings/swig/java/org/tigris/subversion/client/
One would like to decompose it into all its components, say:
/subversion/
/subversion/bindings/
/subversion/bindings/swig/
/subversion/bindings/swig/java/
/subversion/bindings/swig/java/org/
/subversion/bindings/swig/java/org/tigris/
/subversion/bindings/swig/java/org/tigris/subversion/
/subversion/bindings/swig/java/org/tigris/subversion/client/
Let further assume one would like to use only SQLite's build-in mechanism to
achieve this.
Any clever way to achieve this?
FWIW, here is a rather weak attempt, using recursive CTE:
with
DataSet
as
(
select '/subversion/bindings/swig/java/org/tigris/subversion/client/' as path
),
Component( path, leftover, component, position )
as
(
select path,
substr( path, instr( substr( path, 2 ), '/' ) + 1 ) as leftover,
substr( path, 2, instr( substr( path, 2 ), '/' ) - 1 ) as component,
1 as position
from DataSet
union all
select Component.path as path,
substr( Component.leftover, instr( substr( Component.leftover, 2 ),
'/' ) + 1 ) as leftover,
substr( Component.leftover, 2, instr( substr( Component.leftover, 2
), '/' ) - 1 ) as component,
Component.position + 1 as position
from Component
where Component.leftover != '/'
)
select path,
component,
position,
(
select '/' || group_concat( self.component, '/' ) || '/'
from Component self
where self.path = Component.path
and self.position <= Component.position
group by self.path
) as component_path
from Component
order by path, position;
While this work for one path, it doesn’t quite scale to multiple of them as the
CTE gets re-executed over and over. One could cache the CTE in a temp table,
and decompose the query into separated steps, but that would be rather
inconvenient altogether.
Thoughts? Suggestions? Alternatives?
Thanks.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
