Re: [sqlite] Bi-directional unique

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

Re: [sqlite] Bi-directional unique

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.

Re: [sqlite] Bi-directional unique

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);

Re: [sqlite] Bi-directional unique

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

Re: [sqlite] Bi-directional unique

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

Re: [sqlite] Bi-directional unique

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;

Re: [sqlite] Bi-directional unique

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

Re: [sqlite] Bi-directional unique

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?

Re: [sqlite] Bi-directional unique

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 >

Re: [sqlite] Bi-directional unique

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

Re: [sqlite] Bi-directional unique

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

Re: [sqlite] Bi-directional unique

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

Re: [sqlite] Bi-directional unique

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

Re: [sqlite] Bi-directional unique

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.

Re: [sqlite] Bi-directional unique

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

Re: [sqlite] Bi-directional unique

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

Re: [sqlite] Bi-directional unique

g] 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 int

Re: [sqlite] Bi-directional unique

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.

Re: [sqlite] Bi-directional unique

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,

Re: [sqlite] Bi-directional unique

...@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 (typeo

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>

Re: [sqlite] Bi-directional unique

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

Re: [sqlite] Bi-directional unique

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

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

Re: [sqlite] Bi-directional unique

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

Re: [sqlite] Bi-directional unique

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

[sqlite] Bi-directional unique

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); <<