Re: [sqlite] decomposing a path into its components?
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?
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?
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?
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