Rails way to reset seed on id field

Question

I have found the "pure SQL" answers to this question. Is there a way, in Rails, to reset the id field for a specific table?
Why do I want to do this? Because I have tables with constantly moving data - rarely more than 100 rows, but always different. It is up to 25k now, and there's just no point in that. I intend on using a scheduler internal to the Rails app (rufus-scheduler) to run the id field reset monthly or so.

Answer 1

One problem is that these kinds of fields are implemented differently for different databases- sequences, auto-increments, etc.

You can always drop and re-add the table.

Answer 2

No there is no such thing in Rails. If you need a nice ids to show the users then store them in a separate table and reuse them.

Answer 3

You could only do this in rails if the _ids are being set by rails. As long as the _ids are being set by your database, you won't be able to control them without using SQL.

Side note: I guess using rails to regularly call a SQL procedure that resets or drops and recreates a sequence wouldn't be a purely SQL solution, but I don't think that is what you're asking...

EDIT:

Disclaimer: I don't know much about rails.

From the SQL perspective, if you have a table with columns id first_name last_name and you usually insert into table (first_name, last_name) values ('bob', 'smith') you can just change your queries to insert into table (id, first_name, last_name) values ([variable set by rails], 'bob', 'smith') This way, the _id is set by a variable, instead of being automatically set by SQL. At that point, rails has entire control over what the _ids are (although if it is a PK you need to make sure you don't use the same value while it's still in there).

If you are going to leave the assignment up to the database, you have to have rails run (on whatever time schedule) something like:

DROP SEQUENCE MY_SEQ;
CREATE SEQUENCE MY_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 1;

to whatever sequence controls the ids for your table. This will get rid of the current sequence, and create a new one. This is the simplest way I know of you 'reset' a sequence.

Answer 4

You never mentioned what DBMS you're using. If this is postgreSQL, the ActiveRecord postgres adapter has a reset_pk_sequences! method that you could use:

ActiveRecord::Base.connection.reset_pk_sequence!('table_name')

Answer 5

I assume you don't care about the data:

def self.truncate!
  connection.execute("truncate table #{quoted_table_name}")
end

Or if you do, but not too much (there is a slice of time where the data only exists in memory):

def self.truncate_preserving_data!
  data = all.map(&:clone).each{|r| raise "Record would not be able to be saved" unless r.valid? }
  connection.execute("truncate table #{quoted_table_name}")
  data.each(&:save)
end

This will give new records, with the same attributes, but id's starting at 1.

Anything belongs_toing this table could get screwy.