how to create pgsql sequence from controller?

(Frenchy, sorry =))

Hi everyone, i'm working on a web browser based game:
every player have got some ants and i want every ants of each player
to have a unique name:

Player 1:
ant 1
ant 2
ant 3
Player 2:
ant 1
ant 2
ant 3
ant 4
...

To do that i need to create player1_seq and player2_seq and increment
the player sequence whenever the player create an ant so i need to
create sequence whenever a player subscribe.

How to do that?

Thanks.

Why not just use the id of the ant?
If you need them to start from 1 for each player then when you make an
new ant look up the highest number for that player's ants and add one
to that.

Colin

The simple answer is it would be trivial to have ants have unique names use ant id.

THe simples i can see is to use player.ant.count (select + update )

do a count of ants for player before each batch creation

I thought of 4 other ways but this is the simplest.

The simple answer is it would be trivial to have ants have unique names use
ant id.

THe simples i can see is to use player.ant.count (select + update )

do a count of ants for player before each batch creation

Using count will not work. Suppose there were three, numbers 1, 2 and
3. Suppose number 2 is deleted leaving 1 and 3. If a new one were
created it would get 3 again if count were used. It is necessary to
find the current highest and increment that so the new one will be 4.

Colin

I agree Colin, i can't use count...
and i can't use ant's id because every ants of every players got a
unique id, so, this may be possible:
Player 1:
ant 1, id = 1
ant 2, id = 2
ant 3, id = 5
Player 2:
ant 1, id = 3
ant 2, id = 4
ant 3, id = 6
ant 4, id = 7.

And the Colin's solution isn't good too, suppose:
Player 1:
ant 1, id = 1, name = 1
ant 2, id = 2, name = 2
ant 3, id = 3, name = 3

Player2 subscribe and create 3ants:
on the first ant's creation i just put the name to 1 and the twice
follower just got the previously name+1, so:
ant 1, id = 4, name = 1
ant 2, id = 5, name = 2
ant 3, id = 6, name = 3

Player1 attacks Player2. The Player2's ant which has got the id = 6,
name = 3 dies.
Player2 creates a new ant the "highest number for that player's ants"
will be the ant which has got the id = 5, name = 2 so the next name
will be 3. Again.

Is there noway to create sequence from controller? I can also just set
a new column with the last used name. But i don't want my ant's table
to be "BIG"(don't know how to say clearly what i don't want).

I can also just set
a new column with the last used name. But i don't want my ant's table
to be "BIG"(don't know how to say clearly what i don't want).

I meant a new column in the player's city's table, which can't be
delete.

I agree Colin, i can't use count...
and i can't use ant's id because every ants of every players got a
unique id, so, this may be possible:
Player 1:
ant 1, id = 1
ant 2, id = 2
ant 3, id = 5
Player 2:
ant 1, id = 3
ant 2, id = 4
ant 3, id = 6
ant 4, id = 7.

Your initial post did not say that there would be any problems with
the numbering above

And the Colin's solution isn't good too, suppose:
Player 1:
ant 1, id = 1, name = 1
ant 2, id = 2, name = 2
ant 3, id = 3, name = 3

Player2 subscribe and create 3ants:
on the first ant's creation i just put the name to 1 and the twice
follower just got the previously name+1, so:
ant 1, id = 4, name = 1
ant 2, id = 5, name = 2
ant 3, id = 6, name = 3

Player1 attacks Player2. The Player2's ant which has got the id = 6,
name = 3 dies.
Player2 creates a new ant the "highest number for that player's ants"
will be the ant which has got the id = 5, name = 2 so the next name
will be 3. Again.

Again, your initial post did not say that you must not use the same
number again. In that case you need a column in the players table
keeping the last used number for each player and use that to determine
the next ant number.

Is there noway to create sequence from controller?

I don't understand what you mean. If the requirement is that each
player's ants must start from 1 and increment for each ant, and a
number must not be re-used if an ant is destroyed then the only way of
achieving this is to keep a record for each player of the last used
number, and there is no sensible place to store it other than in the
players record.

Colin

He asked for unique it is true

If you want todo this with posygresl - you may be bettrr off using psql and a trigger and a sequence and adking in the plsql forum.

AND then please post what you doo!!

Ok, i just find out the perfect solution. In my controller, i just do:

ActiveRecord::Base.connection.execute("CREATE SEQUENCE " +
current_user.username + "_id_seq")

And my player_id_seq is created, just need now to do something like:

ActiveRecord::Base.connection.execute("SELECT nextval( " +
current_user.username + "_id_seq)")

whenever an ant is created to get the next ant's name =)

If someone see any problems i can encounter, please tell me.