how to create pgsql sequence from controller?

rubyonrails-talk
1

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

2

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

3

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.

4

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

5

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

6

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.

7

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

8

He asked for unique it is true

9

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!!

10

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.