[sqlite] Array or set type or some other indexable data?
op 12-11-2015 17:35 schreef J Decker op d3ck0r at gmail.com: > On Thu, Nov 12, 2015 at 7:16 AM, E.Pasma 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. ... >> with walk as ( ... >> > > 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 > So something like "select value from option_map_view where path is "? 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. > would be better than building up the address to get a comparison at > the end since the whole map would have to be walked. >
[sqlite] Array or set type or some other indexable data?
On Thu, Nov 12, 2015 at 6:44 PM, J Decker wrote: > 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 ) > You can use multiple WITHs to get rid of some of that duplication, moving the duplicated values into "upper" WITHs. something like (untested): with jackpot(v) as ( select './DEFAULT/jackpot_sign/sack/PSI/Frame border/Height' ), -- don't remember if comma is needed between WITHs > with option (option_id,path,rpath, leaf) as (\ > select option_id \ > ,j.v as path\ > , substr( j.v\ > , instr( j.v, '/' ) + 1 ) as rpath \ > , 0 as leaf \ > from option4_map \ > , jackpot j > join option4_name on > option4_map.name_id=option4_name.name_id \ > ... -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
[sqlite] Array or set type or some other indexable data?
Having an "array of values" in a single field violates zero'th normal form and therefore cannot be represented nor manipulated efficiently in a algebraically correctly implemented Relational Database. You would need one with non-relational extensions which handle such non-relational data in the way that you wish to handle it. The only one I know of that handles such things in the manner you are asking is a product from the 1980's called NOMAD. Perhaps you can normalize your data (at least to fourth normal by the sounds of it) and that will let you work with it efficiently. > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of E.Pasma > Sent: Thursday, 12 November, 2015 16:14 > To: SQLite mailing list > Subject: Re: [sqlite] Array or set type or some other indexable data? > > op 12-11-2015 17:35 schreef J Decker op d3ck0r at gmail.com: > > On Thu, Nov 12, 2015 at 7:16 AM, E.Pasma 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. > ... > >> with walk as ( > ... > >> > > > > 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 > > > So something like "select value from option_map_view where path is 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. > > > would be better than building up the address to get a comparison at > > the end since the whole map would have to be walked. > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Array or set type or some other indexable data?
On Thu, Nov 12, 2015 at 4:52 PM, J Decker wrote: > > So something like "select value from option_map_view where path is > > 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... > I think this case wants something like a table-valued user function: https://www.sqlite.org/vtab.html#tabfunc2 as used by: https://www.sqlite.org/json1.html That's basically what you just said, except in a way which isn't kludging things together. [I think, apologies if I mis-read things.] -scott
[sqlite] Array or set type or some other indexable data?
> So something like "select value from option_map_view where path is 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...
[sqlite] Array or set type or some other indexable data?
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, '',''), (1, 'system', 0), (2, 'device', 1), (3, 'com port', 2), (4, '1', 3), (5, '2', 3) ; with walk as ( select 0 as parent, --root '/system/device/com port/1' as path union select id, substr(path,length(name)+2) fromwalk joinmap using(parent) where name = substr(path,2,instr(substr(path,2)||'/','/')-1) -- oef ) select parent as id from walk where path='' -- nothing left ;
[sqlite] Array or set type or some other indexable data?
On 2015/11/12 8:02 AM, J Decker wrote: > 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. I'm not sure exactly what you fancy happening here, specifically what you mean by "levels"... Selecting from an array-like list is not hard using a cte (or sub-query), this kind of thing will work fine: WITH NMT(Idx,Name) AS ( SELECT 0, 'system' UNION ALL SELECT 1, 'localhost' UNION ALL SELECT 2, 'Andy-PC' ), PRT(Idx,Port) AS ( SELECT 0, 'COM1' UNION ALL SELECT 1, 'COM3' UNION ALL SELECT 2, 'COM4' ) SELECT '/' || NMT.Name || '/' || PRT.Port || '/' || A.ID FROM NMT, PRT, (SELECT 1 AS ID) AS A WHERE NMT.Idx=0 AND PRT.Idx=1; which should yield: /system/COM3/1 etc. This example makes a lot of assumptions about what you meant and may be totally the wrong direction - some clearer question will greatly help us. Thanks! Ryan
[sqlite] Array or set type or some other indexable data?
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='----'\ 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 wrote: > On Thu, Nov 12, 2015 at 7:16 AM, E.Pasma 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 >>
[sqlite] Array or set type or some other indexable data?
On Thu, Nov 12, 2015 at 7:16 AM, E.Pasma 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 > fromwalk > joinmap 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...
[sqlite] Array or set type or some other indexable data?
On 11/12/2015 1:02 AM, J Decker wrote: > 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... Color me dense, but I for one have no idea what you are trying to achieve. Show table schema, sample data, and the desired outcome of the query when run against that data. -- Igor Tandetnik
[sqlite] Array or set type or some other indexable data?
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.
