Re: [sqlite] Using a customized collate function to mimic integer array type.

[big stone]

Even if  SQLite was already implementing a bigger subset of the array type
of SQL2003 standard, your users would still have a few more words to learn :

'ROW', 'MEMBER OF', 'UNNEST'

"C > 100 (match 158)"  => " max(UNNEST(C)) > 100 "
 "C < 100 (match 42 and 76)"  => " min(UNNEST(C)) < 100 "
"C = 42 (match 42)" => "ROW(42) MEMBER OF C"

(reference : to http://farrago.sourceforge.net/design/CollectionTypes.html)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using a customized collate function to mimic integer array type.

[James K. Lowden]

On Fri, 21 Mar 2014 09:35:19 -0500
Ben Peng  wrote:

> I have an application where data stored in columns can be lists of
> integers (e.g. 158;42;76). I cannot really split such record into
> multiple records (one for 158, one for 42 etc) and I am currently
> storing them as VARCHAR because they represent a complete piece of
> information. However, when I select records, I would like to compare
> or match any of the values. 

In relational terms, your column with "158;42;76" is not in 1NF.

You want to match "any" of the values: an OR condition.  That
tells you they are distinct, and should each be on their own row, viz., 

158;42;76   158
158;42;7642
158;42;7676

Create a table like that, and join it to the one you have.  

Someone else mentioned using a VTF to accomplish that on the fly.  That
would work, too.  A better solution (that is not a SQLite feature yet)
would be a table-valued function.  

Today, SQLite supports two kinds of functions, scalar and aggregate.
Using R for the number of rows and C for the number of columns, these
can be described as

Scalar:  1 x C -> 1 x 1 output
Aggregate: R x C  -> 1 x 1 output 

A table-valued function is

Table: R x C -> N x M output

which looks a lot like an ordinary join, doesn't it?  

IOW, there's no fixed relationship between the number of input rows and
columns, and the number of output rows and columns.  Using your
example, 

Example: R x 1 -> 3R x 2

and your SQL becomes

select t.*, tf.output
from existing_table as t 
join tf(t.values) as tf
on t.values = tf.values;

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using a customized collate function to mimic integer array type.

[Max Vlasov]

On Fri, Mar 21, 2014 at 8:06 PM, Ben Peng  wrote:
>
> I guess I will have to take the longer route, namely define a customized
> comparison function and translate user input internally.
>

There's an also virtual table method, probably not so easy to wrap the
head around, but this one allows using comma (or other symbols
splitted) lists stored in fields even in joins. See my reply about it
at https://www.mail-archive.com/sqlite-users@sqlite.org/msg63453.html.

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using a customized collate function to mimic integer array type.

[Ben Peng]

Thanks Dan,

I think I get your point. COLLATE not only provides a new way to compare
values (what is what I need), but also gives new appearances to existing
values by which they are sorted or grouped. So 158;42;76 can not appear as
158, 42, and 76 at the same time (158;42;76 == 42, 158;42;76 == 76, does
not imply 42 == 76).

I guess I will have to take the longer route, namely define a customized
comparison function and translate user input internally.

Thanks again for all the help, I am glad that I asked before I write any
code.

Bo



On Fri, Mar 21, 2014 at 10:44 AM, Dan Kennedy  wrote:

> On 03/21/2014 10:33 PM, Ben Peng wrote:
>
>> Hi, Tristan,
>>
>> Your solution definitely works (we have defined a few custom functions)
>> but
>> our application hides databases from users but allows users to use simple
>> conditions to retrieve results. To use this function, we would have to
>>
>> 1. teach users use this function, which is hard to do because it is
>> application specific and they need to know which fields need to use this
>> function.
>>
>> 2. translate user input to use this function internally. We will need to
>> use a separate table to record the fields that need translation, and
>> change
>> user input accordingly.
>>
>> A COLLATE function seems to mark the columns directly and save us from the
>> trouble of translating user input, so it might be a better solution in
>> this
>> case.
>>
>
> SQLite assumes that collation sequences are internally consistent. From
> the docs:
>
>  1. If A==B then B==A.
>  2. If A==B and B==C then A==C.
>  3. If AA.
>  4. If A
> But this would not be true of the proposed collation sequence. So although
> some queries will appear to work, you'll be in trouble if SQLite ever
> decides to create an automatic index based on the collation sequence. Or if
> anybody ever uses an ORDER BY or GROUP BY clause that uses it. Various
> other problems can likely arise if the optimizer starts commuting
> expressions and so on, which it sometimes does.
>
> Of course I don't know exactly what kinds of queries you are expecting,
> but this seems like the kind of thing that will come back and bite you to
> me.
>
> Dan.
>
>
>
>  On Fri, 2014-03-21 at 09:35 -0500, Ben Peng wrote:
>>>
 Dear sqlite experts,

>>> I'm far from an "sqlite expert", others should be able to
>>> provide a more authoritive answer
>>>
>>> I think what you want is rather to simply define your own custom
>>> function to implement a custom match.
>>>
>>> I think using COLLATE is wrong in this case, because the use case of
>>> COLLATE is generally to implement ORDER BY, or perhaps to build a custom
>>> index (to replace the regular strcmp() like behavior) but what you want
>>> is a rather loose matching algorithm.
>>>
>>> If you create a function using sqlite3_create_function(), similar to how
>>> you might define a REGEX function, you might execute a query with:
>>>
>>>SELECT * FROM table WHERE custom_match (table.column, 42) = 0;
>>>
>>> And then, lets assume that "custom_match" uses sqlite3_result_int()
>>> to set the result to 0 if there is a match, or -1 if there is no match,
>>> then you should get a match for any row where table.column = 158;42;76
>>>
>>> You can of course give more parameters to "custom_match", so that you
>>> can use invocations like this:
>>>custom_match (GREATER, table.column, 42)
>>>custom_match (EQUALS, table.column, 42)
>>>custom_match (LESSER, table.column, 42)
>>>
>>> Make sense ?
>>>
>>> Cheers,
>>>  -Tristan
>>>
>>>  I have an application where data stored in columns can be lists of

>>> integers
>>>
 (e.g. 158;42;76). I cannot really split such record into multiple
 records
 (one for 158, one for 42 etc) and I am currently storing them as VARCHAR
 because they represent a complete piece of information. However, when I
 select records, I would like to compare or match any of the values. For
 example, the record could be selected by either

 C > 100 (match 158)
 C < 100 (match 42 and 76)
 C = 42 (match 42)

  From what I have read so far, I could define a special collate function

>>> for
>>>
 these columns (sqlite3_create_collation etc), but before I jump into the
 details, does anyone know

 1. Is this the correct way to proceed?
 2. Has anyone done anything similar so that I do not have to reinvent
 the
 wheel?

 Thank you very much,
 Bo
 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>  ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> 

Re: [sqlite] Using a customized collate function to mimic integer array type.

[Dan Kennedy]

On 03/21/2014 10:33 PM, Ben Peng wrote:

Hi, Tristan,

Your solution definitely works (we have defined a few custom functions) but
our application hides databases from users but allows users to use simple
conditions to retrieve results. To use this function, we would have to

1. teach users use this function, which is hard to do because it is
application specific and they need to know which fields need to use this
function.

2. translate user input to use this function internally. We will need to
use a separate table to record the fields that need translation, and change
user input accordingly.

A COLLATE function seems to mark the columns directly and save us from the
trouble of translating user input, so it might be a better solution in this
case.


SQLite assumes that collation sequences are internally consistent. From 
the docs:


 1. If A==B then B==A.
 2. If A==B and B==C then A==C.
 3. If AA.
 4. If ABut this would not be true of the proposed collation sequence. So 
although some queries will appear to work, you'll be in trouble if 
SQLite ever decides to create an automatic index based on the collation 
sequence. Or if anybody ever uses an ORDER BY or GROUP BY clause that 
uses it. Various other problems can likely arise if the optimizer starts 
commuting expressions and so on, which it sometimes does.


Of course I don't know exactly what kinds of queries you are expecting, 
but this seems like the kind of thing that will come back and bite you 
to me.


Dan.



On Fri, 2014-03-21 at 09:35 -0500, Ben Peng wrote:

Dear sqlite experts,

I'm far from an "sqlite expert", others should be able to
provide a more authoritive answer

I think what you want is rather to simply define your own custom
function to implement a custom match.

I think using COLLATE is wrong in this case, because the use case of
COLLATE is generally to implement ORDER BY, or perhaps to build a custom
index (to replace the regular strcmp() like behavior) but what you want
is a rather loose matching algorithm.

If you create a function using sqlite3_create_function(), similar to how
you might define a REGEX function, you might execute a query with:

   SELECT * FROM table WHERE custom_match (table.column, 42) = 0;

And then, lets assume that "custom_match" uses sqlite3_result_int()
to set the result to 0 if there is a match, or -1 if there is no match,
then you should get a match for any row where table.column = 158;42;76

You can of course give more parameters to "custom_match", so that you
can use invocations like this:
   custom_match (GREATER, table.column, 42)
   custom_match (EQUALS, table.column, 42)
   custom_match (LESSER, table.column, 42)

Make sense ?

Cheers,
 -Tristan


I have an application where data stored in columns can be lists of

integers

(e.g. 158;42;76). I cannot really split such record into multiple records
(one for 158, one for 42 etc) and I am currently storing them as VARCHAR
because they represent a complete piece of information. However, when I
select records, I would like to compare or match any of the values. For
example, the record could be selected by either

C > 100 (match 158)
C < 100 (match 42 and 76)
C = 42 (match 42)

 From what I have read so far, I could define a special collate function

for

these columns (sqlite3_create_collation etc), but before I jump into the
details, does anyone know

1. Is this the correct way to proceed?
2. Has anyone done anything similar so that I do not have to reinvent the
wheel?

Thank you very much,
Bo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using a customized collate function to mimic integer array type.

[Ben Peng]

Hi, Tristan,

Your solution definitely works (we have defined a few custom functions) but
our application hides databases from users but allows users to use simple
conditions to retrieve results. To use this function, we would have to

1. teach users use this function, which is hard to do because it is
application specific and they need to know which fields need to use this
function.

2. translate user input to use this function internally. We will need to
use a separate table to record the fields that need translation, and change
user input accordingly.

A COLLATE function seems to mark the columns directly and save us from the
trouble of translating user input, so it might be a better solution in this
case.

Thanks,
Bo



On Fri, Mar 21, 2014 at 10:05 AM, Tristan Van Berkom <
tris...@upstairslabs.com> wrote:

> On Fri, 2014-03-21 at 09:35 -0500, Ben Peng wrote:
> > Dear sqlite experts,
>
> I'm far from an "sqlite expert", others should be able to
> provide a more authoritive answer
>
> I think what you want is rather to simply define your own custom
> function to implement a custom match.
>
> I think using COLLATE is wrong in this case, because the use case of
> COLLATE is generally to implement ORDER BY, or perhaps to build a custom
> index (to replace the regular strcmp() like behavior) but what you want
> is a rather loose matching algorithm.
>
> If you create a function using sqlite3_create_function(), similar to how
> you might define a REGEX function, you might execute a query with:
>
>   SELECT * FROM table WHERE custom_match (table.column, 42) = 0;
>
> And then, lets assume that "custom_match" uses sqlite3_result_int()
> to set the result to 0 if there is a match, or -1 if there is no match,
> then you should get a match for any row where table.column = 158;42;76
>
> You can of course give more parameters to "custom_match", so that you
> can use invocations like this:
>   custom_match (GREATER, table.column, 42)
>   custom_match (EQUALS, table.column, 42)
>   custom_match (LESSER, table.column, 42)
>
> Make sense ?
>
> Cheers,
> -Tristan
>
> >
> > I have an application where data stored in columns can be lists of
> integers
> > (e.g. 158;42;76). I cannot really split such record into multiple records
> > (one for 158, one for 42 etc) and I am currently storing them as VARCHAR
> > because they represent a complete piece of information. However, when I
> > select records, I would like to compare or match any of the values. For
> > example, the record could be selected by either
> >
> > C > 100 (match 158)
> > C < 100 (match 42 and 76)
> > C = 42 (match 42)
> >
> > From what I have read so far, I could define a special collate function
> for
> > these columns (sqlite3_create_collation etc), but before I jump into the
> > details, does anyone know
> >
> > 1. Is this the correct way to proceed?
> > 2. Has anyone done anything similar so that I do not have to reinvent the
> > wheel?
> >
> > Thank you very much,
> > Bo
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using a customized collate function to mimic integer array type.

[Tristan Van Berkom]

On Fri, 2014-03-21 at 09:35 -0500, Ben Peng wrote:
> Dear sqlite experts,

I'm far from an "sqlite expert", others should be able to
provide a more authoritive answer

I think what you want is rather to simply define your own custom
function to implement a custom match.

I think using COLLATE is wrong in this case, because the use case of
COLLATE is generally to implement ORDER BY, or perhaps to build a custom
index (to replace the regular strcmp() like behavior) but what you want
is a rather loose matching algorithm.

If you create a function using sqlite3_create_function(), similar to how
you might define a REGEX function, you might execute a query with:

  SELECT * FROM table WHERE custom_match (table.column, 42) = 0;

And then, lets assume that "custom_match" uses sqlite3_result_int()
to set the result to 0 if there is a match, or -1 if there is no match,
then you should get a match for any row where table.column = 158;42;76

You can of course give more parameters to "custom_match", so that you
can use invocations like this:
  custom_match (GREATER, table.column, 42)
  custom_match (EQUALS, table.column, 42)
  custom_match (LESSER, table.column, 42)

Make sense ?

Cheers,
-Tristan

> 
> I have an application where data stored in columns can be lists of integers
> (e.g. 158;42;76). I cannot really split such record into multiple records
> (one for 158, one for 42 etc) and I am currently storing them as VARCHAR
> because they represent a complete piece of information. However, when I
> select records, I would like to compare or match any of the values. For
> example, the record could be selected by either
> 
> C > 100 (match 158)
> C < 100 (match 42 and 76)
> C = 42 (match 42)
> 
> From what I have read so far, I could define a special collate function for
> these columns (sqlite3_create_collation etc), but before I jump into the
> details, does anyone know
> 
> 1. Is this the correct way to proceed?
> 2. Has anyone done anything similar so that I do not have to reinvent the
> wheel?
> 
> Thank you very much,
> Bo
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Using a customized collate function to mimic integer array type.

[Ben Peng]

Dear sqlite experts,

I have an application where data stored in columns can be lists of integers
(e.g. 158;42;76). I cannot really split such record into multiple records
(one for 158, one for 42 etc) and I am currently storing them as VARCHAR
because they represent a complete piece of information. However, when I
select records, I would like to compare or match any of the values. For
example, the record could be selected by either

C > 100 (match 158)
C < 100 (match 42 and 76)
C = 42 (match 42)

>From what I have read so far, I could define a special collate function for
these columns (sqlite3_create_collation etc), but before I jump into the
details, does anyone know

1. Is this the correct way to proceed?
2. Has anyone done anything similar so that I do not have to reinvent the
wheel?

Thank you very much,
Bo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users