Re: [sqlite] Bi-directional unique

[Kees Nuyt]

On Sat, 12 Feb 2011 19:14:51 +, Simon Slavin
 wrote:

>
>On 12 Feb 2011, at 11:43am, Kees Nuyt wrote:
>
>> There are just three cases:
>> - i < j
>> - i > j
>> - i and j are the same.
>> 
>> If  j < i just swap the values.
>
> and given what real-world situation the data reflects,
> the third case can't happen.

You're right. In my implementation it changes the table constraint
from:
,   CONSTRAINT key_order CHECK (i <= j)
to:
,   CONSTRAINT key_order CHECK (i < j)

and
INSERT INTO v (i,j) VALUES (4,4);
will fail.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bi-directional unique

[Simon Slavin]

On 12 Feb 2011, at 11:43am, Kees Nuyt wrote:

> There are just three cases:
> - i < j
> - i > j
> - i and j are the same.
> 
> If  j < i just swap the values.

and given what real-world situation the data reflects, the third case can't 
happen.

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

Re: [sqlite] Bi-directional unique

[Kees Nuyt]

On Wed, 9 Feb 2011 18:12:32 +, "Black, Michael (IS)"
 wrote:

>I have a need to create a unique bi-directional relationship.
>You can think of it as pairings of people who eat dinner together.
>
>create table t(i int, j int);
>
>insert into t(1,2);
>insert into t(2,1); << should give an error because the pairing of 1-2 already 
>exists.
>insert into t(3,2); << OK
>insert into t(3,1); << OK
>insert into t(1,3); << should be error
>
>You can't guarantee that one column is less than the other so there's no win 
>there.

There are just three cases:
- i < j
- i > j
- i and j are the same.

If  j < i just swap the values.


>Speed is of the utmost concern here so fast is really important (how many ways 
>can I say that???).
>
>Is there anything clever here that can be done with indexes or such?

This is Darren Duncan's solution in SQL:

CREATE TABLE t (
i INTEGER
,   j INTEGER
,   CONSTRAINT key_order CHECK (i <= j)
,   PRIMARY KEY (i,j) 
);

CREATE VIEW v AS
  SELECT i,j FROM t;

CREATE TRIGGER i
 INSTEAD OF INSERT ON v 
 FOR EACH ROW
 BEGIN
 INSERT INTO t (i,j) 
 VALUES (
CASE WHEN NEW.i > NEW.j THEN NEW.j ELSE NEW.i END
,   CASE WHEN NEW.i > NEW.j THEN NEW.i ELSE NEW.j END
);
 END;

-- update triggers left to your imagination.

INSERT INTO v (i,j) VALUES (1,2);
INSERT INTO v (i,j) VALUES (2,1); 
-- Error: near line xx: columns i, j are not unique
INSERT INTO v (i,j) VALUES (3,2);
INSERT INTO v (i,j) VALUES (3,1);
INSERT INTO v (i,j) VALUES (1,3); 
-- Error: near line yy: columns i, j are not unique
INSERT INTO v (i,j) VALUES (4,4);
SELECT i,j FROM t ORDER BY i,j;

1|2
1|3
2|3
4|4

I know you're not fond of triggers because of speed.
I don't think this construct will hurt much in this case.
You'll have to benchmark it.

HTH
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bi-directional unique

[Samuel Adam]

On Wed, 09 Feb 2011 20:14:19 -0500, Igor Tandetnik   
wrote:

> On 2/9/2011 7:29 PM, Samuel Adam wrote:
[…snip garbage…]
>
> It seems (1, 2) and (2, 1) would result in distinct xk values, thus
> defeating the point of the exercise. It is again possible to insert two
> pairs that differ only in order.

You are right.  I’ve now devised two different back-of-the-envelope proofs  
that I was trying to achieve the mathematically impossible.  (Now watch  
this:  For strike three, somebody will show that it is not only possible,  
but trivial.)

Incidentally, I believe I just provided an unintentional object lesson in  
the merit of being just a bit formal sometimes.  What I was trying to do  
(which may or may not have been obvious to Mr. Tandetnik, et al.) is to  
find

k = F(x, y) = F′(y, x)

such that k would retain the information of whether F or F′ was used.   
Q.E.D. (and affix palm to forehead).

On the bright side, this bungling on my part led me into an interesting  
general problem with sets and permutations.  That’s not topical, however,  
as I have already showed for Mr. Black’s purpose that extra information  
cannot be stored in k without breaking the equality.  Plus as I’ve said  
before[1] and yesterday quite well demonstrated, I am bad at math.

Very truly,

Samuel Adam ◊ 
763 Montgomery Road ◊ Hillsborough, NJ  08844-1304 ◊ United States
Legal advice from a non-lawyer: “If you are sued, don’t do what the
Supreme Court of New Jersey, its agents, and its officers did.”
http://www.youtube.com/watch?v=iT2hEwBfU1g

[1] http://www.mail-archive.com/sqlite-users@sqlite.org/msg56438.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bi-directional unique

[Darren Duncan]

Black, Michael (IS) wrote:
> I have a need to create a unique bi-directional relationship.
> 
> You can think of it as pairings of people who eat dinner together.
> 
> You can't guarantee that one column is less than the other so there's no win 
> there.

Why can't you have a well-known ordering for your values?  It doesn't have to 
be 
meaningful, and it should be very easy.  Do a sort on the binary 
representations 
if you don't have a better one.  As long as you have that, put the value that 
orders first or equal in the same column all the time.  Then have a unique key 
constraint over the pair of columns.  Problem solved.

> Speed is of the utmost concern here so fast is really important (how many 
> ways can I say that???).
> 
> Is there anything clever here that can be done with indexes or such?

Just what I said will do what you want and it is the simplest solution plus 
most 
efficient in both performance and disk usage.

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

Re: [sqlite] Bi-directional unique

[Igor Tandetnik]

On 2/9/2011 7:29 PM, Samuel Adam wrote:
> In pseudo-C:
>
>   int32_t x, y;
>   int64_t xk; /* k lives in the low bits of xk */
>   
>   if (x<  0 || y<  0) ;   /* Return an error. */
>   
>   xk = (int64_t)x<<32 | x^y;
>   /* Weird integer concatenation; yes, precedence is right. */

It seems (1, 2) and (2, 1) would result in distinct xk values, thus 
defeating the point of the exercise. It is again possible to insert two 
pairs that differ only in order.
-- 
Igor Tandetnik

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

Re: [sqlite] Bi-directional unique

[Samuel Adam]

On Wed, 09 Feb 2011 17:54:37 -0500, Igor Tandetnik   
wrote:

> On 2/9/2011 5:49 PM, Jim Wilcoxson wrote:
>> I assumed you could generate k in a procedural language outside of SQL,  
>> but
>> if you want to do this purely in SQL, I think you can just say:
>>
>> create table t(k int primary key)
>> insert into t values (min(?,?)<<32 | max(?,?))
>>
>> and bind i,j,i,j to the parameters.
>
> Or make it
>
> insert into t values (min(:first,:second)<<32 | max(:first,:second))
>
> and bind two parameters like you always did.

If the order of (i, j) versus (j, i) is unimportant, then this is superior  
on most points to my corrected bit-XOR design.  Normalizing away that  
order may be desired, so discarding it could be an advantage.  But the XOR  
preserves that order, and that order may be part of the data; so it really  
depends on application requirements.  And the XOR could also be done in  
pure SQL with SQLite patched to have a ^ operator.

Due credit:  I of course cribbed from the quoted idea to correct my  
previous error.

Very truly,

Samuel Adam ◊ 
763 Montgomery Road ◊ Hillsborough, NJ  08844-1304 ◊ United States
Legal advice from a non-lawyer: “If you are sued, don’t do what the
Supreme Court of New Jersey, its agents, and its officers did.”
http://www.youtube.com/watch?v=iT2hEwBfU1g
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bi-directional unique

[Samuel Adam]

On Wed, 09 Feb 2011 18:51:45 -0500, Samuel Adam   
wrote:

> On Wed, 09 Feb 2011 18:39:14 -0500, David Bicking 
> wrote:
>
>> I don't think this will work. xor(6,1) = 7 and xor(4,3) = 7, so you
>> would fail to insert proper pairs. Or am I missing something?  (At least
>> I assume that the integers are not limited to just 1 2 or 3 as in the
>> examples.
>
> You are right, as xoring on my fingers would have verified.  In polite
> terms, evidently I just demonstrated publicly math as not my forté ||
> today as not my day.  Apologies for the noise.

At risk of worsening my today’s foot-in-mouth syndrome, I believe that the  
following design would keep *some* of the advantages I outlined at cost of  
the following:

(a) It would only work for 31-bit integers (or 32-bit integers with  
additional tricks to store two 32-bit unsigneds in a 64-bit signed).

(b) Increased computational cost.

However, it adds one advantage (in addition to being correct!):

(c) It would collapse the full informational content of "x" and "y" 
into  
the same column "xk" (viz., the all-important INTEGER PRIMARY KEY).  And  
still, no additional indices are required.

In pseudo-C:

int32_t x, y;
int64_t xk; /* k lives in the low bits of xk */

if (x < 0 || y < 0) ;   /* Return an error. */

xk = (int64_t)x<<32 | x^y;
/* Weird integer concatenation; yes, precedence is right. */

If you don’t mind living dangerously, use union or pointer tricks to  
coerce uint32_t x, y into an sqlite3_int64.

Upon the foregoing, only one column ("xk" INTEGER PRIMARY KEY) is required  
to actually store the data.  With the foregoing pseudo-C placed in an SQL  
user function, however, this is also possible:

CREATE TABLE "" (
"xk" INTEGER PRIMARY KEY, "x" INTEGER, "y" INTEGER,
CHECK ("xk" IS compose_xk("x", "y"))
);

I think Shannon bit me before, but he’s on my side now.

Very truly,

Samuel Adam ◊ 
763 Montgomery Road ◊ Hillsborough, NJ  08844-1304 ◊ United States
Legal advice from a non-lawyer: “If you are sued, don’t do what the
Supreme Court of New Jersey, its agents, and its officers did.”
http://www.youtube.com/watch?v=iT2hEwBfU1g


> Very truly,
>
> Samuel Adam ◊ 
> 763 Montgomery Road ◊ Hillsborough, NJ  08844-1304 ◊ United States
> Legal advice from a non-lawyer: “If you are sued, don’t do what the
> Supreme Court of New Jersey, its agents, and its officers did.”
> http://www.youtube.com/watch?v=iT2hEwBfU1g
>
>
>> David
>>
>> On 02/09/2011 05:58 PM, Samuel Adam wrote:
>>> On Wed, 09 Feb 2011 13:12:32 -0500, Black, Michael (IS)
>>>   wrote:
>>>
>>>
 I have a need to create a unique bi-directional relationship.

 You can think of it as pairings of people who eat dinner together.

>>> Two questions come to mind:
>>>
>>> (a) Do you trust app-level code to maintain data integrity, or do you
>>> need SQLite to do this?
>>>
>>> (b) How much relational rigor do you need?  Will the values be used  
>>> for
>>> some kind of relational algebra, or is SQLite simply serving as an ACID
>>> reliability layer?
>>>
>>> Since you’ve been considering the bit-math tricks suggested by Mr.
>>> Wilcoxson, the answers to these questions may let you consider some XOR
>>> cleverness.  Unfortunately, I halfway wrote this up before I realized
>>> that
>>> SQLite lacks a ^ operator[1]; so you will need a trivial SQL user
>>> function
>>> and/or app-land code.  Still, with the following, you can store any
>>> pairs
>>> of 63-bit integers>= 0.  In pure SQL:
>>>
>>> CREATE TABLE "" ("k" INTEGER PRIMARY KEY, "x" INTEGER);
>>> -- WRONG: INSERT INTO "" ("k", "x") VALUES (:x ^ :y, :x);
>>> INSERT INTO "" ("k", "x") VALUES (xor(:x, :y), :x);
>>> -- Faster on the app level; you understand.
>>> SELECT "x", xor("k", "x") AS "y" FROM "";
>>>
>>> (Add NOT NULL or CHECK(typeof("x") IS 'integer') and salt to taste.
>>> N.b.,
>>> I *think* the above binding scenario will work but have not tested it.)
>>>
>>> [1] 2009·12·15 thread with reference to ^ patch by Will Clark:
>>> Message-ID:
>>> 
>>> http://www.mail-archive.com/sqlite-users@sqlite.org/msg49112.html
>>>
>>> Key points:
>>>
>>> * Pair uniqueness is enforced for free.  At least, I think it’s really
>>> for free because SQLite always requires a unique rowid.  Somebody  
>>> please
>>> correct me if there is any penalty for user-selected rowids, which  
>>> would
>>> make the performance impact nonzero.
>>>
>>> * Order of (x, y) versus (y, x) pairings is preserved.  Sorts on y  
>>> will
>>> be a pain, though.
>>>
>>> * No extra indices are required.
>>>
>>> * I don’t see a reasonable way to stop arbitrary data from being
>>> stuffed
>>> in from 

Re: [sqlite] Bi-directional unique

[Samuel Adam]

On Wed, 09 Feb 2011 18:39:14 -0500, David Bicking   
wrote:

> I don't think this will work. xor(6,1) = 7 and xor(4,3) = 7, so you
> would fail to insert proper pairs. Or am I missing something?  (At least
> I assume that the integers are not limited to just 1 2 or 3 as in the
> examples.

You are right, as xoring on my fingers would have verified.  In polite  
terms, evidently I just demonstrated publicly math as not my forté ||  
today as not my day.  Apologies for the noise.

Very truly,

Samuel Adam ◊ 
763 Montgomery Road ◊ Hillsborough, NJ  08844-1304 ◊ United States
Legal advice from a non-lawyer: “If you are sued, don’t do what the
Supreme Court of New Jersey, its agents, and its officers did.”
http://www.youtube.com/watch?v=iT2hEwBfU1g


> David
>
> On 02/09/2011 05:58 PM, Samuel Adam wrote:
>> On Wed, 09 Feb 2011 13:12:32 -0500, Black, Michael (IS)
>>   wrote:
>>
>>
>>> I have a need to create a unique bi-directional relationship.
>>>
>>> You can think of it as pairings of people who eat dinner together.
>>>
>> Two questions come to mind:
>>
>>  (a) Do you trust app-level code to maintain data integrity, or do you
>> need SQLite to do this?
>>
>>  (b) How much relational rigor do you need?  Will the values be used for
>> some kind of relational algebra, or is SQLite simply serving as an ACID
>> reliability layer?
>>
>> Since you’ve been considering the bit-math tricks suggested by Mr.
>> Wilcoxson, the answers to these questions may let you consider some XOR
>> cleverness.  Unfortunately, I halfway wrote this up before I realized  
>> that
>> SQLite lacks a ^ operator[1]; so you will need a trivial SQL user  
>> function
>> and/or app-land code.  Still, with the following, you can store any  
>> pairs
>> of 63-bit integers>= 0.  In pure SQL:
>>
>>  CREATE TABLE "" ("k" INTEGER PRIMARY KEY, "x" INTEGER);
>>  -- WRONG: INSERT INTO "" ("k", "x") VALUES (:x ^ :y, :x);
>>  INSERT INTO "" ("k", "x") VALUES (xor(:x, :y), :x);
>>  -- Faster on the app level; you understand.
>>  SELECT "x", xor("k", "x") AS "y" FROM "";
>>
>> (Add NOT NULL or CHECK(typeof("x") IS 'integer') and salt to taste.   
>> N.b.,
>> I *think* the above binding scenario will work but have not tested it.)
>>
>> [1] 2009·12·15 thread with reference to ^ patch by Will Clark:
>> Message-ID:
>> 
>> http://www.mail-archive.com/sqlite-users@sqlite.org/msg49112.html
>>
>> Key points:
>>
>>  * Pair uniqueness is enforced for free.  At least, I think it’s really
>> for free because SQLite always requires a unique rowid.  Somebody please
>> correct me if there is any penalty for user-selected rowids, which would
>> make the performance impact nonzero.
>>
>>  * Order of (x, y) versus (y, x) pairings is preserved.  Sorts on y will
>> be a pain, though.
>>
>>  * No extra indices are required.
>>
>>  * I don’t see a reasonable way to stop arbitrary data from being  
>> stuffed
>> in from within SQLite, even with a user function; for although :y is  
>> being
>> bound on INSERT, a CHECK constraint has no way to touch it.  But see  
>> below
>> for a modified table with a different set of tradeoffs.
>>
>>  * Since two small integers XORed will be another small integer, you do
>> not suffer the loss of variable-length integer storage as spoken of by
>> Messrs. Vlasov and Tandetnik.
>>
>>  * XOR is *fast*.  And the number of integers is kept to a bare minimum
>> (for keeping up to 63 bits for each), cutting cache pressure at all
>> levels—from SQLite’s page-cache to the processor caches.  I am no expert
>> in optimization, but the foregoing practically begs to be benchmarked.
>>
>>  * If for some reason you can’t use xor("k", "x") for all your SQL needs
>> (foreign keys come to mind), add another explicit "y" column.  You then
>> lose some of the foregoing advantages.  But then, a trivial (and  
>> probably
>> quite fast) pure-SQL constraint could then be used to enforce some
>> integrity:
>>
>>  CREATE TABLE "" (
>>  "k" INTEGER PRIMARY KEY, "x" INTEGER, "y" INTEGER,
>>  CHECK ("k" IS xor("x", "y")) -- NOT NULL for free!
>>  );
>> i
>>  * If you try to use negative integers, your database will trigger a HCF
>> instruction.  At the cost of some more performance, CHECK("x">= 0 AND
>> xor("k", "x")>= 0) will *partially* solve that.  I say “partially”
>> because per the foregoing, SQLite cannot guarantee that "y" = "k"^"x"
>> unless you use the modified table, anyway.
>>
>> Bear in mind, this suggestion stems from a personal bias toward clever  
>> XOR
>> tricks; at that, I once wrote a set of endian-swab functions with no
>> (explicit) temporary variables, purely using XOR-swap and shifts.  I  
>> found
>> it the most pleasant way to satisfy aliasing rules; yet I am to this day
>> uncertain whether the result 

Re: [sqlite] Bi-directional unique

[David Bicking]

I don't think this will work. xor(6,1) = 7 and xor(4,3) = 7, so you 
would fail to insert proper pairs. Or am I missing something?  (At least 
I assume that the integers are not limited to just 1 2 or 3 as in the 
examples.

David

On 02/09/2011 05:58 PM, Samuel Adam wrote:
> On Wed, 09 Feb 2011 13:12:32 -0500, Black, Michael (IS)
>   wrote:
>
>
>> I have a need to create a unique bi-directional relationship.
>>
>> You can think of it as pairings of people who eat dinner together.
>>  
> Two questions come to mind:
>
>   (a) Do you trust app-level code to maintain data integrity, or do you
> need SQLite to do this?
>
>   (b) How much relational rigor do you need?  Will the values be used for
> some kind of relational algebra, or is SQLite simply serving as an ACID
> reliability layer?
>
> Since you’ve been considering the bit-math tricks suggested by Mr.
> Wilcoxson, the answers to these questions may let you consider some XOR
> cleverness.  Unfortunately, I halfway wrote this up before I realized that
> SQLite lacks a ^ operator[1]; so you will need a trivial SQL user function
> and/or app-land code.  Still, with the following, you can store any pairs
> of 63-bit integers>= 0.  In pure SQL:
>
>   CREATE TABLE "" ("k" INTEGER PRIMARY KEY, "x" INTEGER);
>   -- WRONG: INSERT INTO "" ("k", "x") VALUES (:x ^ :y, :x);
>   INSERT INTO "" ("k", "x") VALUES (xor(:x, :y), :x);
>   -- Faster on the app level; you understand.
>   SELECT "x", xor("k", "x") AS "y" FROM "";
>
> (Add NOT NULL or CHECK(typeof("x") IS 'integer') and salt to taste.  N.b.,
> I *think* the above binding scenario will work but have not tested it.)
>
> [1] 2009·12·15 thread with reference to ^ patch by Will Clark:
> Message-ID:
> 
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg49112.html
>
> Key points:
>
>   * Pair uniqueness is enforced for free.  At least, I think it’s really
> for free because SQLite always requires a unique rowid.  Somebody please
> correct me if there is any penalty for user-selected rowids, which would
> make the performance impact nonzero.
>
>   * Order of (x, y) versus (y, x) pairings is preserved.  Sorts on y will
> be a pain, though.
>
>   * No extra indices are required.
>
>   * I don’t see a reasonable way to stop arbitrary data from being stuffed
> in from within SQLite, even with a user function; for although :y is being
> bound on INSERT, a CHECK constraint has no way to touch it.  But see below
> for a modified table with a different set of tradeoffs.
>
>   * Since two small integers XORed will be another small integer, you do
> not suffer the loss of variable-length integer storage as spoken of by
> Messrs. Vlasov and Tandetnik.
>
>   * XOR is *fast*.  And the number of integers is kept to a bare minimum
> (for keeping up to 63 bits for each), cutting cache pressure at all
> levels—from SQLite’s page-cache to the processor caches.  I am no expert
> in optimization, but the foregoing practically begs to be benchmarked.
>
>   * If for some reason you can’t use xor("k", "x") for all your SQL needs
> (foreign keys come to mind), add another explicit "y" column.  You then
> lose some of the foregoing advantages.  But then, a trivial (and probably
> quite fast) pure-SQL constraint could then be used to enforce some
> integrity:
>
>   CREATE TABLE "" (
>   "k" INTEGER PRIMARY KEY, "x" INTEGER, "y" INTEGER,
>   CHECK ("k" IS xor("x", "y")) -- NOT NULL for free!
>   );
> i
>   * If you try to use negative integers, your database will trigger a HCF
> instruction.  At the cost of some more performance, CHECK("x">= 0 AND
> xor("k", "x")>= 0) will *partially* solve that.  I say “partially”
> because per the foregoing, SQLite cannot guarantee that "y" = "k"^"x"
> unless you use the modified table, anyway.
>
> Bear in mind, this suggestion stems from a personal bias toward clever XOR
> tricks; at that, I once wrote a set of endian-swab functions with no
> (explicit) temporary variables, purely using XOR-swap and shifts.  I found
> it the most pleasant way to satisfy aliasing rules; yet I am to this day
> uncertain whether the result qualifies as abstract art.
>
> P.S.:  Consider the foregoing a real-life use case in support of adding a
> bitwise ^ operator to SQLite.
>
> Very truly,
>
> Samuel Adam ◊
> 763 Montgomery Road ◊ Hillsborough, NJ  08844-1304 ◊ United States
> Legal advice from a non-lawyer: “If you are sued, don’t do what the
> Supreme Court of New Jersey, its agents, and its officers did.”
> http://www.youtube.com/watch?v=iT2hEwBfU1g
>
>
>
>> create table t(i int, j int);
>>
>> insert into t(1,2);
>>
>> insert into t(2,1);<<  should give an error because the pairing of 1-2
>> already exists.
>>
>> insert into t(3,2);<<  OK
>>
>> insert into t(3,1);<<  OK
>>
>> insert into 

Re: [sqlite] Bi-directional unique

[Samuel Adam]

On Wed, 09 Feb 2011 13:12:32 -0500, Black, Michael (IS)  
 wrote:

> I have a need to create a unique bi-directional relationship.
>
> You can think of it as pairings of people who eat dinner together.

Two questions come to mind:

(a) Do you trust app-level code to maintain data integrity, or do you  
need SQLite to do this?

(b) How much relational rigor do you need?  Will the values be used for 
 
some kind of relational algebra, or is SQLite simply serving as an ACID  
reliability layer?

Since you’ve been considering the bit-math tricks suggested by Mr.  
Wilcoxson, the answers to these questions may let you consider some XOR  
cleverness.  Unfortunately, I halfway wrote this up before I realized that  
SQLite lacks a ^ operator[1]; so you will need a trivial SQL user function  
and/or app-land code.  Still, with the following, you can store any pairs  
of 63-bit integers >= 0.  In pure SQL:

CREATE TABLE "" ("k" INTEGER PRIMARY KEY, "x" INTEGER);
-- WRONG: INSERT INTO "" ("k", "x") VALUES (:x ^ :y, :x);
INSERT INTO "" ("k", "x") VALUES (xor(:x, :y), :x);
-- Faster on the app level; you understand.
SELECT "x", xor("k", "x") AS "y" FROM "";

(Add NOT NULL or CHECK(typeof("x") IS 'integer') and salt to taste.  N.b.,  
I *think* the above binding scenario will work but have not tested it.)

[1] 2009·12·15 thread with reference to ^ patch by Will Clark:
Message-ID:  

http://www.mail-archive.com/sqlite-users@sqlite.org/msg49112.html

Key points:

* Pair uniqueness is enforced for free.  At least, I think it’s really  
for free because SQLite always requires a unique rowid.  Somebody please  
correct me if there is any penalty for user-selected rowids, which would  
make the performance impact nonzero.

* Order of (x, y) versus (y, x) pairings is preserved.  Sorts on y will 
 
be a pain, though.

* No extra indices are required.

* I don’t see a reasonable way to stop arbitrary data from being 
stuffed  
in from within SQLite, even with a user function; for although :y is being  
bound on INSERT, a CHECK constraint has no way to touch it.  But see below  
for a modified table with a different set of tradeoffs.

* Since two small integers XORed will be another small integer, you do  
not suffer the loss of variable-length integer storage as spoken of by  
Messrs. Vlasov and Tandetnik.

* XOR is *fast*.  And the number of integers is kept to a bare minimum  
(for keeping up to 63 bits for each), cutting cache pressure at all  
levels—from SQLite’s page-cache to the processor caches.  I am no expert  
in optimization, but the foregoing practically begs to be benchmarked.

* If for some reason you can’t use xor("k", "x") for all your SQL needs 
 
(foreign keys come to mind), add another explicit "y" column.  You then  
lose some of the foregoing advantages.  But then, a trivial (and probably  
quite fast) pure-SQL constraint could then be used to enforce some  
integrity:

CREATE TABLE "" (
"k" INTEGER PRIMARY KEY, "x" INTEGER, "y" INTEGER,
CHECK ("k" IS xor("x", "y")) -- NOT NULL for free!
);
i
* If you try to use negative integers, your database will trigger a HCF 
 
instruction.  At the cost of some more performance, CHECK("x" >= 0 AND  
xor("k", "x") >= 0) will *partially* solve that.  I say “partially”  
because per the foregoing, SQLite cannot guarantee that "y" = "k"^"x"  
unless you use the modified table, anyway.

Bear in mind, this suggestion stems from a personal bias toward clever XOR  
tricks; at that, I once wrote a set of endian-swab functions with no  
(explicit) temporary variables, purely using XOR-swap and shifts.  I found  
it the most pleasant way to satisfy aliasing rules; yet I am to this day  
uncertain whether the result qualifies as abstract art.

P.S.:  Consider the foregoing a real-life use case in support of adding a  
bitwise ^ operator to SQLite.

Very truly,

Samuel Adam ◊ 
763 Montgomery Road ◊ Hillsborough, NJ  08844-1304 ◊ United States
Legal advice from a non-lawyer: “If you are sued, don’t do what the
Supreme Court of New Jersey, its agents, and its officers did.”
http://www.youtube.com/watch?v=iT2hEwBfU1g


> create table t(i int, j int);
>
> insert into t(1,2);
>
> insert into t(2,1); << should give an error because the pairing of 1-2  
> already exists.
>
> insert into t(3,2); << OK
>
> insert into t(3,1); << OK
>
> insert into t(1,3); << should be error
>
>
>
> You can't guarantee that one column is less than the other so there's no  
> win there.
>
>
>
> Speed is of the utmost concern here so fast is really important (how  
> many ways can I say that???).
>
>
>
> Is there anything clever here that can be done with indexes or such?
>
>
>
>
>
> Michael D. Black
>
> Senior 

Re: [sqlite] Bi-directional unique

[Igor Tandetnik]

On 2/9/2011 5:49 PM, Jim Wilcoxson wrote:
> I assumed you could generate k in a procedural language outside of SQL, but
> if you want to do this purely in SQL, I think you can just say:
>
> create table t(k int primary key)
> insert into t values (min(?,?)<<32 | max(?,?))
>
> and bind i,j,i,j to the parameters.

Or make it

insert into t values (min(:first,:second)<<32 | max(:first,:second))

and bind two parameters like you always did.
-- 
Igor Tandetnik

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

Re: [sqlite] Bi-directional unique

[Jim Wilcoxson]

On Wed, Feb 9, 2011 at 5:25 PM, Simon Slavin  wrote:

>
> On 9 Feb 2011, at 7:32pm, Black, Michael (IS) wrote:
>
> > Didn't we just determine a couple of days ago that triggers were
> performance killers?
> >
> > That's one reason I want to avoid those.
>
> Okay, then since your program knows the logic of how that table will be
> used, it can do it for itself.  Just do two INSERTS.
>
> Alternatively, if your language is capable of it, simply well-order the two
> personIDs, so that the one in one column is always smaller than the one in
> the other column.  Then change your SELECT logic so that it returns the
> UNION of SELECTing on both columns.
>
> One system speeds up the INSERTs, the other speeds up the SELECTs.  It
> depends which is the  more important to you.
>

I assumed you could generate k in a procedural language outside of SQL, but
if you want to do this purely in SQL, I think you can just say:

create table t(k int primary key)
insert into t values (min(?,?)<<32 | max(?,?))

and bind i,j,i,j to the parameters.

For the select, same thing:

select * from t where k=min(?,?)<<32 | max(?,?)
and bind i,j,i,j

I don't see the need to do 2 selects or 2 inserts, but maybe I'm not
understanding something.

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

Re: [sqlite] Bi-directional unique

[Simon Slavin]

On 9 Feb 2011, at 7:32pm, Black, Michael (IS) wrote:

> Didn't we just determine a couple of days ago that triggers were performance 
> killers?
> 
> That's one reason I want to avoid those.

Okay, then since your program knows the logic of how that table will be used, 
it can do it for itself.  Just do two INSERTS.

Alternatively, if your language is capable of it, simply well-order the two 
personIDs, so that the one in one column is always smaller than the one in the 
other column.  Then change your SELECT logic so that it returns the UNION of 
SELECTing on both columns.

One system speeds up the INSERTs, the other speeds up the SELECTs.  It depends 
which is the  more important to you.

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

Re: [sqlite] Bi-directional unique

[Igor Tandetnik]

On 2/9/2011 4:36 PM, Black, Michael (IS) wrote:
> This then begs the question of what happens when an update needs a bigger int?

Same thing that happens when an update to a text field inserts longer text.
-- 
Igor Tandetnik

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

Re: [sqlite] Bi-directional unique

[Ian Hardingham]

My apologies if this is stupid, or it's already been discussed.

There's a way of choosing an ordering on anything, even strings.

Have two tables - one where members of column A are "larger", one where 
members of column B are "larger".  Only insert into the correct table 
(O(1) operation).  Insert with a time and when doing the select choose 
the earlier time.

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

Re: [sqlite] Bi-directional unique

[Black, Michael (IS)]

I like this one...but I tested it a bit and the bad part is that doing a select 
on t requires a complete table scan.  So finding these values is too slow.
I did have to modify a bit to get it to work correctly...now I'll try one of 
the other suggestions.

sqlite> create table t (nm integer primary key);
sqlite> create view tv as select (nm & 1073741823) as n, nm >> 30 as m from t;
sqlite> create trigger tv_ins instead of insert on tv begin
   ...> select case when typeof(NEW.n) != 'integer' then raise(fail,'n is not 
an integer') end;
   ...> select case when typeof(NEW.m) != 'integer' then raise(fail,'m is not 
an integer') end;
   ...> select case when NEW.m > (1 << 30) then raise(fail, 'n is too large') 
end;
   ...> select case when NEW.n < 0 then raise(fail, 'n must be non-negative') 
end;
   ...> select case when NEW.m < 0 then raise(fail, 'm must be non-negative') 
end;
   ...> select case (select count(*) from t where (nm = (NEW.m << 30) | NEW.n) 
or (nm = (New.n << 3
) | New.m)) when 1 then raise(fail, 'This relation already exists!') end;
   ...> insert into t select New.n | (New.m << 30);
   ...> end;
sqlite> insert into tv (n,m) values(1,2);
sqlite> insert into tv (n,m) values(1,2);
Error: This relation already exists!
sqlite> insert into tv(n,m) values(3,1);
sqlite> insert into tv(n,m) values(3,2);
sqlite> insert into tv(n,m) values(5,4);
sqlite> insert into tv(n,m) values(4,5);
Error: This relation already exists!
sqlite> select * from tv;
3|1
1|2
3|2
5|4

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Nico Williams [n...@cryptonector.com]
Sent: Wednesday, February 09, 2011 2:28 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Bi-directional unique

On Wed, Feb 9, 2011 at 2:00 PM, Nico Williams <n...@cryptonector.com> wrote:
> This way an insert into t requires one lookup and update in t_idx and
> one lookup and update in the table's implied rowid index.  I.e., twice
> the work of an insert without this additional index and trigger.

Also, you get double the DB size this way.

Using Jim's suggestion you get to avoid those problems:

sqlite> create table t (nm integer primary key);
sqlite> create view tv as select nm >> 30 as n, (nm & (1 << 30)) >> 30
as m from t;
sqlite> create trigger tv_ins instead of insert on tv begin
   ...> select case when typeof(NEW.n) != 'integer' then raise(fail,
'n is not an integer') end;
   ...> select case when typeof(NEW.m) != 'integer' then raise(fail,
'm is not an integer') end;
   ...> select case when NEW.n > (1 << 30) then raise(fail, 'n is too
large') end;
   ...> select case when NEW.m > (1 << 30) then raise(fail, 'm is too
large') end;
   ...> select case when NEW.n < 0 then raise(fail, 'n must be
non-negative') end;
   ...> select case when NEW.m < 0 then raise(fail, 'm must be
non-negative') end;
   ...> select case (select count(*) from t where nm = NEW.n << 30 |
NEW.m) when 1 then raise(fail, 'This relation already exists!') end;
   ...> insert into t select NEW.m << 30 | NEW.n;
   ...> end;
sqlite> insert into tv (n, m) values (1, 2);
sqlite> insert into tv (n, m) values (1, 2);
Error: PRIMARY KEY must be unique
sqlite> insert into tv (n, m) values (3, 1);
sqlite> insert into tv (n, m) values (3, 2);
sqlite> insert into tv (n, m) values (1, 3);
Error: This relation already exists!
sqlite>

This still requires two rowid index lookups for every insert, but only
one update.

Nico
--
___
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] Bi-directional unique

[Black, Michael (IS)]

This then begs the question of what happens when an update needs a bigger int?
Doesn't that cause a fair bit more overhead than just keeping it 4 bytes?  
Fragging the database?
I suppose for embedded use that might be important for most applications 
needing 2 bytes or less usually.


Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [itandet...@mvps.org]
Sent: Wednesday, February 09, 2011 3:19 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] EXT :Re: Bi-directional unique

On 2/9/2011 4:13 PM, Black, Michael (IS) wrote:
> And what's the advantage of packing like this?

SQLite uses variable-length encoding for integers - smaller values
(nearer to zero, to be precise) get packed into fewer bytes. So if you
have two 1 values to store, combining them in the way Max Vlasov sugests
would produce 2 (takes one byte in the database) rather than 0x00010001
(takes three, maybe four bytes).

> So what if you're storing 0x0001 and 0x0001.
> Same space

No, not the same space. Whether the benefit is worth the complexity is a
question, of course.
--
Igor Tandetnik

___
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] Bi-directional unique

[Nico Williams]

On Wed, Feb 9, 2011 at 2:00 PM, Nico Williams  wrote:
> This way an insert into t requires one lookup and update in t_idx and
> one lookup and update in the table's implied rowid index.  I.e., twice
> the work of an insert without this additional index and trigger.

Also, you get double the DB size this way.

Using Jim's suggestion you get to avoid those problems:

sqlite> create table t (nm integer primary key);
sqlite> create view tv as select nm >> 30 as n, (nm & (1 << 30)) >> 30
as m from t;
sqlite> create trigger tv_ins instead of insert on tv begin
   ...> select case when typeof(NEW.n) != 'integer' then raise(fail,
'n is not an integer') end;
   ...> select case when typeof(NEW.m) != 'integer' then raise(fail,
'm is not an integer') end;
   ...> select case when NEW.n > (1 << 30) then raise(fail, 'n is too
large') end;
   ...> select case when NEW.m > (1 << 30) then raise(fail, 'm is too
large') end;
   ...> select case when NEW.n < 0 then raise(fail, 'n must be
non-negative') end;
   ...> select case when NEW.m < 0 then raise(fail, 'm must be
non-negative') end;
   ...> select case (select count(*) from t where nm = NEW.n << 30 |
NEW.m) when 1 then raise(fail, 'This relation already exists!') end;
   ...> insert into t select NEW.m << 30 | NEW.n;
   ...> end;
sqlite> insert into tv (n, m) values (1, 2);
sqlite> insert into tv (n, m) values (1, 2);
Error: PRIMARY KEY must be unique
sqlite> insert into tv (n, m) values (3, 1);
sqlite> insert into tv (n, m) values (3, 2);
sqlite> insert into tv (n, m) values (1, 3);
Error: This relation already exists!
sqlite>

This still requires two rowid index lookups for every insert, but only
one update.

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

Re: [sqlite] Bi-directional unique

[Black, Michael (IS)]

That works too..except you need

create UNIQUE index t_idx on t(m,n);
Otherwise duplicate inserts can exist.

I'll give this one a try too.

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Nico Williams [n...@cryptonector.com]
Sent: Wednesday, February 09, 2011 2:00 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Bi-directional unique

sqlite> create table t(n integer not null check (typeof(n) =
'integer'), m integer not null check (typeof(m) = 'integer'));
sqlite> create index t_idx on t (n, m);
sqlite> explain query plan select count(*) != 0 from t t2 where t2.n =
? and t2.m = ?;
0|0|TABLE t AS t2 WITH INDEX t_idx
sqlite> create trigger t_ins before insert on t begin
   ...> select case when (select count(*) != 0 from t t2 where t2.n =
NEW.m and t2.m = NEW.n) then raise(fail, 'That relation is already in
t!') end;
   ...> end;
sqlite> insert into t values (1,2);
sqlite> insert into t values (2,1);
Error: That relation is already in t!
sqlite> insert into t values (3,2);
sqlite> insert into t values (3,1);
sqlite> insert into t values (1,3);
Error: That relation is already in t!
sqlite>

This way an insert into t requires one lookup and update in t_idx and
one lookup and update in the table's implied rowid index.  I.e., twice
the work of an insert without this additional index and trigger.

Nico
--
___
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] Bi-directional unique

[Nico Williams]

sqlite> create table t(n integer not null check (typeof(n) =
'integer'), m integer not null check (typeof(m) = 'integer'));
sqlite> create index t_idx on t (n, m);
sqlite> explain query plan select count(*) != 0 from t t2 where t2.n =
? and t2.m = ?;
0|0|TABLE t AS t2 WITH INDEX t_idx
sqlite> create trigger t_ins before insert on t begin
   ...> select case when (select count(*) != 0 from t t2 where t2.n =
NEW.m and t2.m = NEW.n) then raise(fail, 'That relation is already in
t!') end;
   ...> end;
sqlite> insert into t values (1,2);
sqlite> insert into t values (2,1);
Error: That relation is already in t!
sqlite> insert into t values (3,2);
sqlite> insert into t values (3,1);
sqlite> insert into t values (1,3);
Error: That relation is already in t!
sqlite>

This way an insert into t requires one lookup and update in t_idx and
one lookup and update in the table's implied rowid index.  I.e., twice
the work of an insert without this additional index and trigger.

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

Re: [sqlite] Bi-directional unique

[Black, Michael (IS)]

Didn't we just determine a couple of days ago that triggers were performance 
killers?

That's one reason I want to avoid those.

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Jim Morris [jmor...@bearriver.com]
Sent: Wednesday, February 09, 2011 12:42 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Bi-directional unique

If you don't care about the order then use and instead of trigger to
force the ordering then you will get the collisions you expect.

On 2/9/2011 10:12 AM, Black, Michael (IS) wrote:
> I have a need to create a unique bi-directional relationship.
>
> You can think of it as pairings of people who eat dinner together.
>
>
>
> create table t(i int, j int);
>
> insert into t(1,2);
>
> insert into t(2,1);<<  should give an error because the pairing of 1-2 
> already exists.
>
> insert into t(3,2);<<  OK
>
> insert into t(3,1);<<  OK
>
> insert into t(1,3);<<  should be error
>
>
>
> You can't guarantee that one column is less than the other so there's no win 
> there.
>
>
>
> Speed is of the utmost concern here so fast is really important (how many 
> ways can I say that???).
>
>
>
> Is there anything clever here that can be done with indexes or such?
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
> ___
> 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] Bi-directional unique

[Jim Wilcoxson]

If you don't care about the order and the integers are smallish, like 31
bits or less, I'd do this:

create table t(k int primary key);

i = whatever
j = whatever
if i < j:
   k = i<<32 | j
else:
   k = j<<32 | i
insert k into table

To see if a pair is in the table, do the same steps and lookup k.

If you do care about the order, you can add k as primary key to the table
layout you mentioned with i and j.

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://www.hashbackup.com


On Wed, Feb 9, 2011 at 1:12 PM, Black, Michael (IS)
wrote:

> I have a need to create a unique bi-directional relationship.
>
> You can think of it as pairings of people who eat dinner together.
>
>
>
> create table t(i int, j int);
>
> insert into t(1,2);
>
> insert into t(2,1); << should give an error because the pairing of 1-2
> already exists.
>
> insert into t(3,2); << OK
>
> insert into t(3,1); << OK
>
> insert into t(1,3); << should be error
>
>
>
> You can't guarantee that one column is less than the other so there's no
> win there.
>
>
>
> Speed is of the utmost concern here so fast is really important (how many
> ways can I say that???).
>
>
>
> Is there anything clever here that can be done with indexes or such?
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
> ___
> 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] Bi-directional unique

[Jim Morris]

If you don't care about the order then use and instead of trigger to 
force the ordering then you will get the collisions you expect.

On 2/9/2011 10:12 AM, Black, Michael (IS) wrote:
> I have a need to create a unique bi-directional relationship.
>
> You can think of it as pairings of people who eat dinner together.
>
>
>
> create table t(i int, j int);
>
> insert into t(1,2);
>
> insert into t(2,1);<<  should give an error because the pairing of 1-2 
> already exists.
>
> insert into t(3,2);<<  OK
>
> insert into t(3,1);<<  OK
>
> insert into t(1,3);<<  should be error
>
>
>
> You can't guarantee that one column is less than the other so there's no win 
> there.
>
>
>
> Speed is of the utmost concern here so fast is really important (how many 
> ways can I say that???).
>
>
>
> Is there anything clever here that can be done with indexes or such?
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
> ___
> 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] Bi-directional unique

[Jay A. Kreibich]

On Wed, Feb 09, 2011 at 06:12:32PM +, Black, Michael (IS) scratched on the 
wall:
> I have a need to create a unique bi-directional relationship.

> You can't guarantee that one column is less than the other so there's
> no win there.

  Not sure what you mean by that.  If the values are not equal,
  *everything* sorts somehow.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bi-directional unique

[Simon Slavin]

On 9 Feb 2011, at 6:12pm, Black, Michael (IS) wrote:

> I have a need to create a unique bi-directional relationship.
> 
> You can think of it as pairings of people who eat dinner together.
> 
> 
> 
> create table t(i int, j int);
> 
> insert into t(1,2);
> 
> insert into t(2,1); << should give an error because the pairing of 1-2 
> already exists.
> 
> insert into t(3,2); << OK
> 
> insert into t(3,1); << OK
> 
> insert into t(1,3); << should be error

Make a trigger that when you insert a record into the table inserts one with 
the columns swapped.  Use "INSERT OR FAIL" so you don't get recursive looping.

The other way to do it would be to create a view that views the actual table 
both ways around (try UNION).  That will be faster for creating the data and 
slower for consulting it.

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

[sqlite] Bi-directional unique

[Black, Michael (IS)]

I have a need to create a unique bi-directional relationship.

You can think of it as pairings of people who eat dinner together.



create table t(i int, j int);

insert into t(1,2);

insert into t(2,1); << should give an error because the pairing of 1-2 already 
exists.

insert into t(3,2); << OK

insert into t(3,1); << OK

insert into t(1,3); << should be error



You can't guarantee that one column is less than the other so there's no win 
there.



Speed is of the utmost concern here so fast is really important (how many ways 
can I say that???).



Is there anything clever here that can be done with indexes or such?





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate


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