> So something like "select value from option_map_view where path is <array or > set type>"? > A path name like '/system/device/com port/1' is used as an array of names > here. Only the indexing with intst and substr is laborious. Maybe some > future SQLite version includes a group_split function to make this easier. > It seems impossible to me in plain SQL to write an efficient view for a > query like this. As you say below the whole map need to be walked at each > query to find a match. > User defined functions, virtual tables or function based indexes may offer > an efficient solution. >
Hmm a user function could be interesting; hard to see a threadsafe version but could keep the array of values internally and and return them as 'getOption(n)' as I descend each layer... was thinking that 'select * from something where name in (a,b,c,d,e)' could be used...(syntactically semi-appealing, using the depth to select the member of the set) but there's no access to the 'where' parameters as input values for the CTE... suppose I can see a virtual table solution; to stuff level/option_name and join on that to get names... but as it is; would still be hard to use as a view even; since the select options are part of the select (value)... but still kinda useless given no stored procedures... which might make it more usable on other databases...
