Forum: Ruby on Rails in-memory SQLite for testing

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
7223c62b7310e164eb79c740188abbda?d=identicon&s=25 Xavier Noria (Guest)
on 2005-12-22 23:12
(Received via mailing list)
I am following the rails book, and arrived to the section about
testing. The test database is configured as:

test:
   adapter: sqlite3
   database: ":memory:"

and I get this error with the simple product test:

% ruby test/unit/product_test.rb
Loaded suite test/unit/product_test
Started
E/usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.1.0/lib/sqlite3/
errors.rb:94:in `check': cannot rollback - no transaction is active
(SQLite3::SQLException)
         from /usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.1.0/
lib/sqlite3/resultset.rb:76:in `check'
         from /usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.1.0/
lib/sqlite3/resultset.rb:68:in `commence'
         from /usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.1.0/
lib/sqlite3/resultset.rb:61:in `initialize'
         from /usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.1.0/
lib/sqlite3/statement.rb:158:in `execute'
         from /usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.1.0/
lib/sqlite3/database.rb:211:in `execute'
         from /usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.1.0/
lib/sqlite3/database.rb:186:in `prepare'
         from /usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.1.0/
lib/sqlite3/database.rb:210:in `execute'
         from /usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.1.0/
lib/sqlite3/database.rb:620:in `rollback'
          ... 11 levels...
         from /usr/local/lib/ruby/1.8/test/unit/autorunner.rb:200:in
`run'
         from /usr/local/lib/ruby/1.8/test/unit/autorunner.rb:13:in
`run'
         from /usr/local/lib/ruby/1.8/test/unit.rb:285
         from test/unit/product_test.rb:7

Any ideas?

-- fxn
C0e0b0489d676615d8e7122018baf483?d=identicon&s=25 Daniel Hackney (Guest)
on 2005-12-25 14:42
(Received via mailing list)
I have not been able to get the 'sqlite3' adapter to work, just using
'sqlite' should work. In addition, the sqlite adapter needs the
'dbfile:' argument rather than 'database:'. Lastly, use ":memory"
instead of ":memory:", omit the second colon. That made it work for
me.

Dan
5b9fe87ec1faa67a4599782930f45ec9?d=identicon&s=25 Sam Stephenson (Guest)
on 2005-12-25 19:16
(Received via mailing list)
Hi,

On 12/25/05, Daniel Hackney <chrono325@gmail.com> wrote:
> In addition, the sqlite adapter needs the
> 'dbfile:' argument rather than 'database:'.

On Rails 1.0 and Rails trunk, you can use either "dbfile" or "database".
See changeset [2825] for details.

> Lastly, use ":memory"
> instead of ":memory:", omit the second colon. That made it work for
> me.

No, this tells SQLite to use a file on your filesystem named ":memory".

| $ ls
| $ sqlite :memory: 'CREATE TABLE test (id INTEGER)'
| $ ls
| $ sqlite :memory 'CREATE TABLE test (id INTEGER)'
| $ ls
| :memory

--
sam
C0e0b0489d676615d8e7122018baf483?d=identicon&s=25 Daniel Hackney (Guest)
on 2005-12-25 23:37
(Received via mailing list)
yeah, whoops. I just assumed when it stopped giving me errors that it
had worked. I guess I should examine things more carefully next time.
This means that I still do not have it working.

Does anyone have any insights?

Thanks and sorry for any confusion,
Daniel Hackney
C0e0b0489d676615d8e7122018baf483?d=identicon&s=25 Daniel Hackney (Guest)
on 2005-12-25 23:58
(Received via mailing list)
The problem occurs when Rails attempts to rollback the transaction. It
seems like sqlite may be dropping the database (i.e. removing it from
memory) earlier than it should and thus when it comes time to close
the transaction, the database is already gone.

Could this be it?

Daniel Hackney
Ccaa63b09f5168501b194266b9a1e7cc?d=identicon&s=25 Scott Willson (Guest)
on 2005-12-29 18:51
(Received via mailing list)
Also keep in mind that SQLite does not support nested transactions.
So if your test starts a transaction (the default behavior) and then
your production code under test starts another transaction, the test
transaction rollback probably won't work.

Scott
7223c62b7310e164eb79c740188abbda?d=identicon&s=25 Xavier Noria (Guest)
on 2005-12-29 20:24
(Received via mailing list)
On Dec 29, 2005, at 18:50, Scott Willson wrote:

> Also keep in mind that SQLite does not support nested transactions.
> So if your test starts a transaction (the default behavior) and
> then your production code under test starts another transaction,
> the test transaction rollback probably won't work.

Ah, this might be relevant.

My test is a pretty simple unit test for the Product model of Depot,
but maybe ActiveRecord is using some transaction behind the scenes.

If that was the case, why surprises me is that SQLite in-memory for
test is what rails(1) generates. If it does not work even in the
simplest of the tests it shouldn't be there.

-- fxn
Af9447156f5c756806f1c0a0444c1ff6?d=identicon&s=25 Andrew Semprebon (Guest)
on 2006-01-03 19:16
(Received via mailing list)
But it might work if you don't use transactional fixtures. Try changing
the
line in test/test_helper.rb to

self.use_transactional_fixtures = false

Since this works with SQLite-on-disk.
55428cbf149e35dd4b65f1d019d04139?d=identicon&s=25 Matthew Palmer (Guest)
on 2006-01-03 22:59
(Received via mailing list)
On Tue, Jan 03, 2006 at 01:02:29PM -0500, Andrew Semprebon wrote:
> But it might work if you don't use transactional fixtures. Try changing the
> line in test/test_helper.rb to
>
> self.use_transactional_fixtures = false

As several people have reported in the past, *this* *does* *not* *work*.
The problem isn't the transactions, it's the complete lack of tables in
the
in-memory database.  Please stop suggesting it as a solution.

- Matt
65ecb55dae5b54e18c6147bbc39b15ad?d=identicon&s=25 Filip de Waard (Guest)
on 2006-01-04 02:41
Matthew Palmer wrote:
> On Tue, Jan 03, 2006 at 01:02:29PM -0500, Andrew Semprebon wrote:
>> But it might work if you don't use transactional fixtures. Try changing the
>> line in test/test_helper.rb to
>>
>> self.use_transactional_fixtures = false
>
> As several people have reported in the past, *this* *does* *not* *work*.
> The problem isn't the transactions, it's the complete lack of tables in
> the
> in-memory database.  Please stop suggesting it as a solution.
>
> - Matt

What do you mean by 'the complete lack of tables in the in-memory
database'? SQLite in-memory databases do support tables...

For an article about SQLite in-memory databases from the PHP
perspective:
http://www.filipdewaard.com/21_SQLite_inmemory_dat...
Af9447156f5c756806f1c0a0444c1ff6?d=identicon&s=25 Andrew Semprebon (Guest)
on 2006-01-04 18:12
(Received via mailing list)
So as I understand it, because the test database is generated in a
separate
rake task, and it disappears once that task completes, the in-memory
database you get when you tests start to run is in fact a new, empty
database.

It would appear that the in-memory sqlite database example in
database.ymlis just a cruel hoax...
616226cee3466e91febcd38f75c69194?d=identicon&s=25 Helmut Sedding (Guest)
on 2006-01-04 18:14
(Received via mailing list)
Am 04.01.2006 um 16:11 schrieb Andrew Semprebon:

>  It would appear that the in-memory sqlite database example in
> database.yml is just a cruel hoax...

I assume if it worked, the speed wouldn't be better. The OS caches
filesystem accesses anyway.

One alternative: use a ramdisk.

regards,
Helmut
3a83969376c805ef5b6042191fdb0ff3?d=identicon&s=25 Andreas S. (andreas)
on 2006-01-04 18:20
Helmut Sedding wrote:
> Am 04.01.2006 um 16:11 schrieb Andrew Semprebon:
>
>>  It would appear that the in-memory sqlite database example in
>> database.yml is just a cruel hoax...
>
> I assume if it worked, the speed wouldn't be better. The OS caches
> filesystem accesses anyway.

No, SQLite calls fsync() when a transaction is committed.

> One alternative: use a ramdisk.

That improves performance considerably.
7223c62b7310e164eb79c740188abbda?d=identicon&s=25 Xavier Noria (Guest)
on 2006-01-04 18:35
(Received via mailing list)
On Jan 4, 2006, at 18:20, Andreas S. wrote:

>
>> One alternative: use a ramdisk.
>
> That improves performance considerably.

Nevertheless the thread is deviating here.

We are not talking about performance, the summary is:

     1. rails generated database.yml has :memory: for test

     2. Nobody has been known to succeed using that

Therefore,

     1. Either we are missing something,

     2. or else that should NOT be generated at all

-- fxn
This topic is locked and can not be replied to.