Re: [sqlite] Can I automatically create a 'disambiguation' number in a second field of a primary key?
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote
Sent: Monday, March 03, 2008 6:38 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Can I automatically create a 'disambiguation' number
in a second field of a primary key?
Paul Hilton wrote:
>
> Thanks for the suggestion,
>
> However it doesn't solve my problem, perhaps because of something I failed
> to say.
>
> I am intending to use these 'Slot' numbers to schedule experiments between
> talkers and listeners.
> The 'Slot' refers to a time slot.
> I would like the experiments within each group to go on simultaneously.
> So the first experiment in group 1 occurs in the same time slot as the
first
> experiment in all the other groups.
>
Then you need to get fancier. :-)
The following SQL shows how to use a trigger to update a separate table
that stores the slot values to be inserted into your communications
table. The trace at the end shows my results.
create table talker (id integer primary key, "group" integer);
create table listener (id integer primary key, "group" integer);
create table communications (id integer primary key,
"group" integer, slot integer, talker integer, listener integer);
create table slots ("group" integer primary key, slot integer);
create trigger in_trig after insert on communications
begin
insert or replace into slots select new."group",
coalesce((select slot from slots where "group" = new."group") +
1, 1);
update communications
set slot = (select slot from slots where "group" = new."group")
where id = new.id;
end;
insert into talker values (11, 1);
insert into talker values (22, 1);
insert into talker values (34, 1);
insert into talker values (47, 2);
insert into talker values (15, 2);
insert into talker values (37, 2);
insert into listener values (11, 1);
insert into listener values (12, 1);
insert into listener values (22, 1);
insert into listener values (47, 2);
insert into listener values (15, 2);
delete from slots;
delete from communications;
insert into communications
select null, t."group", 0, t.id, l.id
from talker as t
join listener as l
where l."group" = t."group"
and l.id != t.id
order by t."group", t.id, l.id;
.mode column
.header on
select * from communications;
select slot, "group", talker, listener
from communications order by slot, "group";
SQLite version 3.5.6
sqlite> create table talker (id integer primary key, "group" integer);
snip...
sqlite> select * from communications;
id group slottalker listener
-- -- -- -- --
1 1 1 11 12
2 1 2 11 22
3 1 3 22 11
4 1 4 22 12
5 1 5 34 11
6 1 6 34 12
7 1 7 34 22
8 2 1 15 47
9 2 2 37 15
10 2 3 37 47
11 2 4 47 15
sqlite> select slot, "group", talker, listener
...> from communications order by slot, "group";
slotgroup talker listener
-- -- -- --
1 1 11 12
1 2 15 47
2 1 11 22
2 2 37 15
3 1 22 11
3 2 37 47
4 1 22 12
4 2 47 15
5 1 34 11
6 1 34 12
7 1 34 22
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Dennis,
That helps a lot, and I think I understand it.
I've not used triggers before, nor a select without a from, nor the coalesce
function.
I presume that if I don't care about the order of records then I don't need
the order by clauses.
I obviously blundered by using the field name "group" in adapting field
names.
Thank you,
Paul Hilton
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I automatically create a 'disambiguation' number in a second field of a primary key?
"Paul Hilton" <[EMAIL PROTECTED]> wrote: > > Here is the problem: I want Slot created to disambiguate the Primary Key, So > that for every value of Group the value of Slot starts at 1 and counts up. These are two different things: (1) Slot needs to disambiguate the PRIMARY KEY (2) Slot needs to start at 1 and count upwards Do you really need (2)? If not - if (1) is all you really need - then one approach is to fill the slot with a random number. Depending on how many slots you have and how often you fill them, you might be able to get away with always using a random 64-bit integer and never checking for collisions because collisions will be much so rare that random computer explosions are much more likely. Whether or not this is true depends on your application, the reliability of your hardware, and the consequences of a collision. Do the math. If in doubt, you might use a 128-bit or longer random blob instead of an integer. -- D. Richard Hipp <[EMAIL PROTECTED]> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I automatically create a 'disambiguation' number in a second field of a primary key?
Paul Hilton wrote:
>
> Thanks for the suggestion,
>
> However it doesn't solve my problem, perhaps because of something I failed
> to say.
>
> I am intending to use these 'Slot' numbers to schedule experiments between
> talkers and listeners.
> The 'Slot' refers to a time slot.
> I would like the experiments within each group to go on simultaneously.
> So the first experiment in group 1 occurs in the same time slot as the first
> experiment in all the other groups.
>
Then you need to get fancier. :-)
The following SQL shows how to use a trigger to update a separate table
that stores the slot values to be inserted into your communications
table. The trace at the end shows my results.
create table talker (id integer primary key, "group" integer);
create table listener (id integer primary key, "group" integer);
create table communications (id integer primary key,
"group" integer, slot integer, talker integer, listener integer);
create table slots ("group" integer primary key, slot integer);
create trigger in_trig after insert on communications
begin
insert or replace into slots select new."group",
coalesce((select slot from slots where "group" = new."group") +
1, 1);
update communications
set slot = (select slot from slots where "group" = new."group")
where id = new.id;
end;
insert into talker values (11, 1);
insert into talker values (22, 1);
insert into talker values (34, 1);
insert into talker values (47, 2);
insert into talker values (15, 2);
insert into talker values (37, 2);
insert into listener values (11, 1);
insert into listener values (12, 1);
insert into listener values (22, 1);
insert into listener values (47, 2);
insert into listener values (15, 2);
delete from slots;
delete from communications;
insert into communications
select null, t."group", 0, t.id, l.id
from talker as t
join listener as l
where l."group" = t."group"
and l.id != t.id
order by t."group", t.id, l.id;
.mode column
.header on
select * from communications;
select slot, "group", talker, listener
from communications order by slot, "group";
SQLite version 3.5.6
sqlite> create table talker (id integer primary key, "group" integer);
snip...
sqlite> select * from communications;
id group slottalker listener
-- -- -- -- --
1 1 1 11 12
2 1 2 11 22
3 1 3 22 11
4 1 4 22 12
5 1 5 34 11
6 1 6 34 12
7 1 7 34 22
8 2 1 15 47
9 2 2 37 15
10 2 3 37 47
11 2 4 47 15
sqlite> select slot, "group", talker, listener
...> from communications order by slot, "group";
slotgroup talker listener
-- -- -- --
1 1 11 12
1 2 15 47
2 1 11 22
2 2 37 15
3 1 22 11
3 2 37 47
4 1 22 12
4 2 47 15
5 1 34 11
6 1 34 12
7 1 34 22
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I automatically create a 'disambiguation' number in a second field of a primary key?
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Monday, March 03, 2008 4:17 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Can I automatically create a 'disambiguation' number in a second field of a primary key? Paul Hilton wrote: > Hello, > > I have source tables Talker and Listener, each with fields ID (PK, Integer) > and Group (Integer): > > CREATE TABLE Talker (ID INTEGER, Group INTEGER, Primary Key (ID)); > Ditto Listener > > I would like to make a table Communications with fields Group (PK, Integer), > Slot(PK, Integer) TalkerID (Integer), ListenerID (Integer): > > CREATE TABLE Communications (Group INTEGER, Slot INTEGER, TalkerID INTEGER, > ListenerID INTEGER, PRIMARY KEY (Group, Slot)); > > I want all combinations of Talker and Listener where > Talker.Group=Listener.Group and Talker.ID!=ListenerID > > Here is the problem: I want Slot created to disambiguate the Primary Key, So > that for every value of Group the value of Slot starts at 1 and counts up. I > don't actually care which TalkerID / ListenerID which value of Slot > corresponds to. > > E.g. > INSERT INTO Communications (Group, TalkerID, ListenerID) > SELECT Talker.Group, Talker.ID, Listener.ID > FROM Talker, Listener > WHERE Talker.Group=Listener.Group AND Talker.ID!=Listener.ID; > > Tries to insert all the records, but doesn't make a Slot value to > disambiguate. > > A numerical illustration of what I want: > > Talker > ID Group > 11 1 > 22 1 > 34 1 > 47 2 > 15 2 > 37 2 > > Listener > ID Group > 11 1 > 12 1 > 22 1 > 47 2 > 15 2 > > Should Yield Communications > Group SlotTalkerIDListenerID > 1 1 11 12 > 1 2 11 22 > 1 3 22 11 > 1 4 22 12 > 1 5 34 11 > 1 6 34 12 > 1 7 34 22 > 2 1 47 15 > 2 2 15 47 > 2 3 37 47 > 2 4 37 15 > > Thanks for any help. > Paul Hilton > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > It's not quite the same but you could try this instead. create table talker (id integer primary key, group integer); create table listener (id integer primary key, group integer); create table communications (id integer primary key, group integer, talker integer, listener integer); After you insert your data into talker and listener. insert into talker ... insert into listener ... You run the following commands to build the communications table. delete from communications; insert into communications select null, t.group, t.id, l.id from talker as t join lister as l where l.group = t.group and l.id != t.id order by t.group, t.id, l.id; This should produce the following table with a unique unambiguous id for each communication. id group talker listener 11 11 12 21 11 22 31 22 11 41 22 12 51 34 11 61 34 12 71 34 22 82 15 47 92 37 15 10 2 37 47 11 2 47 15 HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Thanks for the suggestion, However it doesn't solve my problem, perhaps because of something I failed to say. I am intending to use these 'Slot' numbers to schedule experiments between talkers and listeners. The 'Slot' refers to a time slot. I would like the experiments within each group to go on simultaneously. So the first experiment in group 1 occurs in the same time slot as the first experiment in all the other groups. Paul Hilton ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I automatically create a 'disambiguation' number in a second field of a primary key?
Paul Hilton wrote: > Hello, > > I have source tables Talker and Listener, each with fields ID (PK, Integer) > and Group (Integer): > > CREATE TABLE Talker (ID INTEGER, Group INTEGER, Primary Key (ID)); > Ditto Listener > > I would like to make a table Communications with fields Group (PK, Integer), > Slot(PK, Integer) TalkerID (Integer), ListenerID (Integer): > > CREATE TABLE Communications (Group INTEGER, Slot INTEGER, TalkerID INTEGER, > ListenerID INTEGER, PRIMARY KEY (Group, Slot)); > > I want all combinations of Talker and Listener where > Talker.Group=Listener.Group and Talker.ID!=ListenerID > > Here is the problem: I want Slot created to disambiguate the Primary Key, So > that for every value of Group the value of Slot starts at 1 and counts up. I > don't actually care which TalkerID / ListenerID which value of Slot > corresponds to. > > E.g. > INSERT INTO Communications (Group, TalkerID, ListenerID) > SELECT Talker.Group, Talker.ID, Listener.ID > FROM Talker, Listener > WHERE Talker.Group=Listener.Group AND Talker.ID!=Listener.ID; > > Tries to insert all the records, but doesn't make a Slot value to > disambiguate. > > A numerical illustration of what I want: > > Talker > ID Group > 11 1 > 22 1 > 34 1 > 47 2 > 15 2 > 37 2 > > Listener > ID Group > 11 1 > 12 1 > 22 1 > 47 2 > 15 2 > > Should Yield Communications > Group SlotTalkerIDListenerID > 1 1 11 12 > 1 2 11 22 > 1 3 22 11 > 1 4 22 12 > 1 5 34 11 > 1 6 34 12 > 1 7 34 22 > 2 1 47 15 > 2 2 15 47 > 2 3 37 47 > 2 4 37 15 > > Thanks for any help. > Paul Hilton > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > It's not quite the same but you could try this instead. create table talker (id integer primary key, group integer); create table listener (id integer primary key, group integer); create table communications (id integer primary key, group integer, talker integer, listener integer); After you insert your data into talker and listener. insert into talker ... insert into listener ... You run the following commands to build the communications table. delete from communications; insert into communications select null, t.group, t.id, l.id from talker as t join lister as l where l.group = t.group and l.id != t.id order by t.group, t.id, l.id; This should produce the following table with a unique unambiguous id for each communication. id group talker listener 11 11 12 21 11 22 31 22 11 41 22 12 51 34 11 61 34 12 71 34 22 82 15 47 92 37 15 10 2 37 47 11 2 47 15 HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
