Advance / Increment Sequence in sqlite3

Question

I'm trying to increment a sequence in sqlite3.

Basically, I'm using the sequence as a key into another system, and I have multiple DB's which are generating conflicts.

i.e. I have 2 DB's both creating entries with the same sequence number. They both create a 15, which I use as a key to another system.

I'd like to advance one of the DB's sequences by, say 10 000, so that I stop getting conflicts.

Disclaimer: before you start jumping all over me, I know this is not a good design, and it's only being used for early prototyping. I plan to 'upgrade' to using a UUID generated from the sequence and timestamp as my key in the future. However, until I'm out of 'demo mode' I just want to fix my problem with a line of SQL if I can.

I've tried update sqlite_sequence set seq = 2000 where name = 'XXXX'; but it doesn't seem to do the trick. I'm new to sqlite3, maybe it's something else?

@MPelletier I think that would be a valid approach, but seems a bit more complicated than I was thinking it would be. Since I'm only worried about conflicts, I think I should only have to change 1 of the databases -- the other can keep counting from where it is. I really just want a way to tell the sequence 'start counting from 10000'. — John Hinnegan, Nov 28 '10 at 16:05

score 1 · Answer 1 · answered Jan 18 '11 at 15:30

This issue seems to be getting a lot of attention, so I thought I would post the actual code I'm using with minimal scrubbing. This is in my seeds.rb file.

min_sequence = 1000

case ActiveRecord::Base.connection.adapter_name 
when 'SQLite'
  select_current_sequence_number_sql = "select seq from sqlite_sequence where name = 'businesses';"
  set_min_sequence_to_ten_sql = "update sqlite_sequence set seq = #{min_sequence} where name = 'businesses';"
  result = ActiveRecord::Base.connection.execute(select_current_sequence_number_sql)
  result.nil? || result.empty? ? current_sequence_number = 0 : current_sequence_number = result[0]['seq'].to_i
  if current_sequence_number < min_sequence 
    ActiveRecord::Base.connection.execute(set_min_sequence_to_ten_sql)
  end 
when 'PostgreSQL'
...
else 
  raise "Task not implemented for this DB adapter"
end

I've extended and edited this code in my answer to this similar question: http://stackoverflow.com/questions/2097052/rails-way-to-reset-seed-on-id-field/7814519#7814519 — kikito, Oct 18 '11 at 22:27

score 0 · Answer 2 · answered Nov 27 '10 at 06:42

Let me get this straight (and no, I'm not judging). This would be your sequence of events:

Connect to DB1
Connect to (or attach) DB2
Manually force seq in sqlite_sequence in DB1 to some new value (which you know is unique)
Do the same for sqlite_sequence in DB2
Insert in autoincrement table in DB1
Insert in autoincrement table in DB2

If I am mistaken, please let me know, and I will work from there.

score 0 · Accepted Answer · answered Dec 01 '10 at 16:59

0

So, not sure what I was doing wrong before, but the code I originally put in my question is working for me now.

update sqlite_sequence set seq = 2000 where name = 'XXXX';

answered Dec 01 '10 at 16:59

John Hinnegan

5,864
2
48
64

score 0 · Answer 4 · edited Dec 24 '11 at 22:24

0

Alter sequence sqlite_sequence
increment by 10000;

edited Dec 24 '11 at 22:24

answered Dec 22 '10 at 15:22

Pritam

1

Advance / Increment Sequence in sqlite3

4 Answers4

Linked