Re: [sqlite] Is there a function that return an autoimcremented value?

[Igor Korot]

Igor,


On Fri, Oct 25, 2013 at 3:26 PM, Igor Tandetnik  wrote:

> On 10/25/2013 5:53 PM, Igor Korot wrote:
>
>> If I do something like this:
>>
>> CREATE TRIGGER AFTER INSERT
>> {
>> SELECT max( current_rank ) AS a FROM leagueplayers WHERE leagueid = 1;
>> UPDATE leagueplayers SET current_rank = a WHERE leagueid = 1 AND
>> current_rank IS NULL;
>> }
>>
>
> Something along these lines might work, but the syntax is all wrong. You
> are thinking of something like this:
>
> CREATE TRIGGER leagueplayers_insert AFTER INSERT on leagueplayers
> BEGIN
>   UPDATE leagueplayers SET current_rank = 1 + (
> select max(current_rank) from leagueplayers WHERE leagueid = 1)
>   WHERE rowid = new.rowid and  leagueid = 1;
> END;


Thank you for correcting the syntax. Yes, that's what I was thinking.


I will try to make the changes and let you know if it will work.

Thank you.


>
> --
> 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] Is there a function that return an autoimcremented value?

[Igor Tandetnik]

On 10/25/2013 5:53 PM, Igor Korot wrote:

If I do something like this:

CREATE TRIGGER AFTER INSERT
{
SELECT max( current_rank ) AS a FROM leagueplayers WHERE leagueid = 1;
UPDATE leagueplayers SET current_rank = a WHERE leagueid = 1 AND
current_rank IS NULL;
}


Something along these lines might work, but the syntax is all wrong. You 
are thinking of something like this:


CREATE TRIGGER leagueplayers_insert AFTER INSERT on leagueplayers
BEGIN
  UPDATE leagueplayers SET current_rank = 1 + (
select max(current_rank) from leagueplayers WHERE leagueid = 1)
  WHERE rowid = new.rowid and  leagueid = 1;
END;

--
Igor Tandetnik

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

Re: [sqlite] Is there a function that return an autoimcremented value?

[Igor Korot]

Hi, guys,


On Fri, Oct 25, 2013 at 6:19 AM, Igor Tandetnik  wrote:

> On 10/24/2013 10:48 PM, Igor Korot wrote:
>
>> INSERT INTO leagueplayers SELECT players.playerid, %d, ... ORDER BY
>> players.rank;
>>
>> Now my language of choice is C++ and what I'm looking for is a way to
>> populate the current_rank and original_rank in the leagueplayers table.
>> They should come up as auto-incremented values.
>> So in the "INSERT INTO" query instead of "..." I should have some kind of
>> function or something that will give me values of 1,2,3,4 and so forth.
>>
>
> INSERT INTO leagueplayers
> SELECT p1.playerid, :leagueId, count(*), count(*)
> FROM players p1 JOIN players p2 ON (p2.rank < p1.rank or (p2.rank =
> p1.rank and p2.playerid <= p1.playerid))
> GROUP BY p1.playerid;
>

If I do something like this:

CREATE TRIGGER AFTER INSERT
{
SELECT max( current_rank ) AS a FROM leagueplayers WHERE leagueid = 1;
UPDATE leagueplayers SET current_rank = a WHERE leagueid = 1 AND
current_rank IS NULL;
}

Will this work? Is "INSERT INTO... SELECT()" will work with such trigger?
Then I will not need to change the code of my program...

Thank you.


> --
> 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] Is there a function that return an autoimcremented value?

[Igor Tandetnik]

On 10/24/2013 10:48 PM, Igor Korot wrote:

INSERT INTO leagueplayers SELECT players.playerid, %d, ... ORDER BY
players.rank;

Now my language of choice is C++ and what I'm looking for is a way to
populate the current_rank and original_rank in the leagueplayers table.
They should come up as auto-incremented values.
So in the "INSERT INTO" query instead of "..." I should have some kind of
function or something that will give me values of 1,2,3,4 and so forth.


INSERT INTO leagueplayers
SELECT p1.playerid, :leagueId, count(*), count(*)
FROM players p1 JOIN players p2 ON (p2.rank < p1.rank or (p2.rank = 
p1.rank and p2.playerid <= p1.playerid))

GROUP BY p1.playerid;

--
Igor Tandetnik

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

Re: [sqlite] Is there a function that return an autoimcremented value?

[Clemens Ladisch]

Stephan Beal wrote:
> On Fri, Oct 25, 2013 at 12:51 PM, Clemens Ladisch wrote:
>> CREATE TEMP TABLE t(playerid, leagueid, auto_rank INTEGER PRIMARY KEY);
>> INSERT INTO t(playerid, leagueid) SELECT players.playerid, %d FROM ...;
>> INSERT INTO leagueplayers(playerid, leagueid, current_rank, original_rank)
>>   SELECT playerid, leagueid, auto_rank, auto_rank FROM t;
>> DROP TABLE t;
>
> That will lead to dupe IDs on subsequent transactions, won't it?

The OP asked for "rank" values beginning at 1.
It's a one-time schema conversion.


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

Re: [sqlite] Is there a function that return an autoimcremented value?

[Stephan Beal]

On Fri, Oct 25, 2013 at 12:51 PM, Clemens Ladisch wrote:

> CREATE TEMP TABLE t(playerid, leagueid, auto_rank INTEGER PRIMARY KEY);
> INSERT INTO t(playerid, leagueid) SELECT players.playerid, %d FROM ...;
> INSERT INTO leagueplayers(playerid, leagueid, current_rank, original_rank)
>   SELECT playerid, leagueid, auto_rank, auto_rank FROM t;
> DROP TABLE t;
>

That will lead to dupe IDs on subsequent transactions, won't it?

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Since tyranny's the only guaranteed byproduct of those who insist on a
perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a function that return an autoimcremented value?

[Clemens Ladisch]

Igor Korot wrote:
> On Fri, Oct 25, 2013 at 2:12 AM, Clemens Ladisch  wrote:
>> Igor Korot wrote:
>>> what I'm looking for is a way to populate the current_rank and
>>> original_rank in the leagueplayers table. They should come up as
>>> auto-incremented values.

>>> Is there a way to do that or I will have to change the query to do a
>>> transactional routine?
>>
>> You could create a temporary table with such a column.
>
> And then how do I use it?

CREATE TEMP TABLE t(playerid, leagueid, auto_rank INTEGER PRIMARY KEY);
INSERT INTO t(playerid, leagueid) SELECT players.playerid, %d FROM ...;
INSERT INTO leagueplayers(playerid, leagueid, current_rank, original_rank)
  SELECT playerid, leagueid, auto_rank, auto_rank FROM t;
DROP TABLE t;


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

Re: [sqlite] Is there a function that return an autoimcremented value?

[Igor Korot]

Hi, Clemens,


On Fri, Oct 25, 2013 at 2:12 AM, Clemens Ladisch  wrote:

> Igor Korot wrote:
> > what I'm looking for is a way to populate the current_rank and
> > original_rank in the leagueplayers table. They should come up as
> > auto-incremented values.
>
> Autoincrementing works only for INTEGER PRIMARY KEY columns.
>

Well it will not be defined as auto-increment. Just the values in the
column will be 1,2,3,4 etc.
i.e. incremented by 1 starting from 1 for every single league.


> > Is there a way to do that or I will have to change the query to do a
> > transactional routine?
>
> You could create a temporary table with such a column.
>

OK.
And then how do I use it?

Basically I'm looking for a way to implement it with the minimal changes to
the code/query
As I wrote I have INSERT INTO ... SELECT.
Are you saying that I can create a temp table with autoincrement value and
all other data from the SELECT part and then use it for INSERT query?

Thank you.


>
> Doing this in your code might be simpler.
>
>
> Regards,
> Clemens
> ___
> 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] Is there a function that return an autoimcremented value?

[Clemens Ladisch]

Igor Korot wrote:
> what I'm looking for is a way to populate the current_rank and
> original_rank in the leagueplayers table. They should come up as
> auto-incremented values.

Autoincrementing works only for INTEGER PRIMARY KEY columns.

> Is there a way to do that or I will have to change the query to do a
> transactional routine?

You could create a temporary table with such a column.

Doing this in your code might be simpler.


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

[sqlite] Is there a function that return an autoimcremented value?

[Igor Korot]

Hi, ALL,
Consider the following schema:

CREATE TABLE players(
playerid INTEGER PRIMARY KEY,
name TEXT,
rank INTEGER);

CREATE TABLE leagueplayers(
playerid INTEGER,
leagueid INTEGER,
current_rank INTEGER
original_rank INTEGER);

The fields rank, current_rank and original_rank are recent addition to the
schema.
Now the players table is populated with data and I wrote the following
query to populate the leagueplayers table:

INSERT INTO leagueplayers SELECT players.playerid, %d, ... ORDER BY
players.rank;

Now my language of choice is C++ and what I'm looking for is a way to
populate the current_rank and original_rank in the leagueplayers table.
They should come up as auto-incremented values.
So in the "INSERT INTO" query instead of "..." I should have some kind of
function or something that will give me values of 1,2,3,4 and so forth.

Is there a way to do that or I will have to change the query to do a
transactional routine?

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