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 <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. > ... > >> 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 <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. > > > 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
