Re: [sqlite] decomposing a path into its components?

[Klaas V]

On 3 May 2014, at 3:47pm, Petite Abeille  wrote:

>> Let further assume one would like to use only SQLite's build-in mechanism

>Where Simon answered:  "There are two kinds of programmers ..."

I tend to disagree, in fact there are at least three kinds
1 Application programmers
2 System programmers
3 Those kind of programmers who do a bit of both kinds of programming

 

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V, freelance CIO & ICT-consultant, BCNarTOSit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] decomposing a path into its components?

[Petite Abeille]

On May 3, 2014, at 5:39 PM, Simon Slavin  wrote:

> 
> On 3 May 2014, at 3:47pm, Petite Abeille  wrote:
> 
>> Let further assume one would like to use only SQLite's build-in mechanism
> 
> There are two kinds of programmers …

Indeed: drunk and not yet drunk :D

Turns out that this group_concat scalar was really not needed at all. D'oh. 

So, all-in-one now:

with
DataSet
as
(
  select  '/subversion/bindings/swig/java/org/tigris/subversion/client/' as path
),
Component( path, leftover, component, component_path, position )
as
(
  select  path,
  substr( path, instr( substr( path, 2 ), '/' ) + 1 ) as leftover,
  substr( path, 2, instr( substr( path, 2 ), '/' ) - 1 ) as component,
  '/' || substr( path, 2, instr( substr( path, 2 ), '/' ) - 1 ) as 
component_path,
  1 as position
  fromDataSet

  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.component_path || '/' || substr( Component.leftover, 2, 
instr( substr( Component.leftover, 2 ), '/' ) - 1 )  as component_path,
  Component.position + 1 as position
  fromComponent
  where   Component.leftover != '/'
)
selectposition,
  component,
  component_path
from  Component

order by  path, position;


position|component|component_path
1|subversion|/subversion
2|bindings|/subversion/bindings
3|swig|/subversion/bindings/swig
4|java|/subversion/bindings/swig/java
5|org|/subversion/bindings/swig/java/org
6|tigris|/subversion/bindings/swig/java/org/tigris
7|subversion|/subversion/bindings/swig/java/org/tigris/subversion
8|client|/subversion/bindings/swig/java/org/tigris/subversion/client



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] decomposing a path into its components?

[Simon Slavin]

On 3 May 2014, at 3:47pm, Petite Abeille  wrote:

> Let further assume one would like to use only SQLite's build-in mechanism

There are two kinds of programmers ...

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] decomposing a path into its components?

[Petite Abeille]

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
  fromDataSet

  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
  fromComponent
  where   Component.leftover != '/'
)
selectpath, 
  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users