So... I guess something like this works; other than the triplication
of the initial path string. works for read-only access... doesn't
return where it failed (although can with leaf == 0 )
option4_map ( option_id,parent_option_id,name_id )
option4_names ( name_id, name )
option4_values ( option_id, string_segment, segment_number )
-- rpath is remaining path
with option (option_id,path,rpath, leaf) as ( \
select option_id \
,'./DEFAULT/jackpot_sign/sack/PSI/Frame border/Height' as path \
, substr( './DEFAULT/jackpot_sign/sack/PSI/Frame border/Height' \
, instr( './DEFAULT/jackpot_sign/sack/PSI/Frame
border/Height', '/' ) + 1 ) as rpath \
, 0 as leaf \
from option4_map \
join option4_name on option4_map.name_id=option4_name.name_id \
where option_id='00000000-0000-0000-0000-000000000000' \
and name=substr(path,0,instr(path,'/')) \
union all select option4_map.option_id, name, ''as path, substr(
rpath, instr( rpath, '/' ) + 1 ) \
, instr(rpath,'/') == 0 as leaf \
from option4_map \
join option4_name on option4_map.name_id=option4_name.name_id \
join option on option.option_id=option4_map.parent_option_id \
where option4_map.parent_option_id != option4_map.option_id \
and ( name= substr( rpath, 0, instr( rpath, '/' )
) or name = rpath ) \
) \
select * from option
join option4_values on option.option_id=option4_values.option_id
where leaf=1 -- added leaf indicator to just select final value.
order by segment_number
-- Yes; given such a large name_id, it would less data to just store
the name usually in the option4_map entry... used to just be an int
but it evolved... guess I should revise a version 5...
CREATE TABLE `option4_name` (
`name_id` char(36) NOT NULL
,`name` varchar(255) NOT NULL default ''
CONSTRAINT `name` UNIQUE)
index,sqlite_autoindex_option4_name_1,option4_name,3,NULL
(index on name_id)
CREATE TABLE `option4_map` (
`option_id` char(36) NOT NULL
,`parent_option_id` char(36) NOT NULL default '0'
,`name_id` char(36) NOT NULL default '0'
,`description` tinytext
,CONSTRAINT `parent_key2` UNIQUE
(`parent_option_id`,`name_id`) ON CONFLICT REPLACE
,CONSTRAINT `FK_map_map` FOREIGN KEY (`parent_option_id`)
REFERENCES `option4_map`(`option_id`)ON UPDATE
CASCADE ON DELETE CASCADE
,CONSTRAINT `FK_name_map` FOREIGN KEY (`name_id`)
REFERENCES `option4_name`(`name_id`)ON UPDATE
CASCADE ON DELETE CASCADE)
index,sqlite_autoindex_option4_map_1,option4_map,5,NULL
(index on option_id)
CREATE TABLE `option4_values` (
`option_id` char(36) default '0'
,`string` varchar(100) default NULL
,`segment` int(11) default 0
,CONSTRAINT `value_id` UNIQUE (`option_id`,`segment`) ON
CONFLICT REPLACE
,CONSTRAINT `FK_map_values` FOREIGN KEY (`option_id`)
REFERENCES `option4_map`(`option_id`)ON UPDATE
CASCADE ON DELETE CASCADE)
index,sqlite_autoindex_option4_values_1,option4_values,11,NULL
(index on option_id)
On Thu, Nov 12, 2015 at 8:35 AM, J Decker <d3ck0r at gmail.com> wrote:
> On Thu, Nov 12, 2015 at 7:16 AM, E.Pasma <pasma10 at concepts.nl> wrote:
>> 12 nov 2015, om 07:02, J Decker:
>>
>>
>>> So I've used CTE to solve a simple problem... I'm tempted to use it to
>>> fix more problems... but I'm wondering how to select different values
>>> at different levels. I know there's like 'select * from table where
>>> column in ( set,of,things) ' but can I index into the set? is there
>>> some sort of way I can specify an array of values?
>>>
>>> would like to do something like ' select value from option_map where
>>> name is ["system","device","com port", "1" ] ' where the program
>>> statement would look more like
>>>
>>> GetOptionValue ( "/system/device/com port/1" ) where the name is
>>> really sort of registry like and variable in length...
>>>
>>> I could probably do some sort of indexing passing that exact string
>>> through and parsing in SQL the substrings of interest based on the
>>> level of the query... but would rather preparse the string.
>>
>>
>>
>> Below is another possible answer. This uses a recursive cte to split an
>> input full path name into seperate names.
>> create table map (id, name, parent);
>> insert into map values
>> (0, '',''), -- level 0
>> (1, 'system', 0), -- level 1
>> (2, 'device', 1), -- level 2
>> (3, 'com port', 2), -- level 3
>> (4, '1', 3), -- level 4
>> (5, '2', 3) -- level 4
>> ;
>> with walk as (
>> select 0 as parent, --root
>> '/system/device/com port/1' as path ,
> 0 as level
>> union
>> select id,
>> substr(path,length(name)+2) ,
> level+1 as level
>> from walk
>> join map using(parent)
>> where name = substr(path,2,instr(substr(path,2)||'/','/')-1) --oef
>> )
>> select parent as id from walk
>> where path='' -- nothing left
>> ;
>
> (something like that....)
>
> was hoping to not have to do the substr part in the query....
> and would like the path to be more on the external usage of 'walk' in
> this case than inside the expression
>
> would be better than building up the address to get a comparison at
> the end since the whole map would have to be walked.
>
> TO answer other's questions; 'level' refers to the dept of the tree
> searched... level indiciators added as comments to the insert above...
