ActiveRecord/sqlite3 truncation strategy & sqlite_sequence

[earnold]

I'm using sqlite and ActiveRecord and the truncation strategy, and I noticed there was some funny behavior with sqlite_sequence not getting cleared. (I have some seed data with hard coded IDs, thus the problem.) I can't think of any reason why this table shouldn't be cleared, and likewise, it DOES get cleared if you use the transaction strategy. I think these two strategies should have the same effective result.

I created a small work-around for this:

earnold@98d324a

Does it seem appropriate for you?

Thanks,

Evan

[bmabey]

On 7/11/11 11:35 AM, earnold wrote:

I'm using sqlite and ActiveRecord and the truncation strategy, and I noticed there was some funny behavior with sqlite_sequence not getting cleared. (I have some seed data with hard coded IDs, thus the problem.) I can't think of any reason why this table shouldn't be cleared, and likewise, it DOES get cleared if you use the transaction strategy. I think these two strategies should have the same effective result.

I created a small work-around for this:

earnold@98d324a

Does it seem appropriate for you?

Thanks,

Evan

I'm not too familiar with sqlite.. what is special about the
sqlite_sequence table?

[earnold]

In short, "SQLite keeps track of the largest ROWID that a table has ever held using the special SQLITE_SEQUENCE table."

Therefore, if you have an entity called lawyers and you create a new one, Sqlite gives it a new ID +1 over the number on the sqlite_sequence table. When you truncate this table, it effectively says that no entities have ever been created (which is more or less true, given the total truncation).

Full details: http://www.sqlite.org/autoinc.html

[earnold]

Sorry! Didn't mean to close this!

[jhollinger]

I second this! In my apps I have been monkey-patching database_cleaner with the following code to get the expected behavior (i.e. table ids being reset to 1, like MySQL's TRUNCATE command does):

class DataMapper::Adapters::SqliteAdapter
  alias_method :broken_truncate_table, :truncate_table
  def truncate_table(table_name)
    broken_truncate_table(table_name)
    execute("DELETE FROM sqlite_sequence") # Reset the table's ids
  end
end

Since SQLite has no TRUNCATE command, the only way to duplicate MySQL's (desirable) behavior is to delete a table's row from sqlite_sequence.

I'd be happy to submit a patch, though since it would only be one line, it's probably just as easy for anyone to do it.

[sobrinho]

@bmabey this issue can be closed due to #84 ;)

[bmabey]

Thanks!