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?

Answer 1

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

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

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

Answer 2

Alter sequence sqlite_sequence
increment by 10000;

Answer 3

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 

Answer 4

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';