In-memory SQLite forgets DDL transaction

[wsanchez]

I've got some code that creates a SQLite DB, populates a schema, then writes some data.

If the SQLite DB is a file, this seems to work as expected:

2018-06-08T16:10:40-0700 [__main__.Index#info] Importing transmissions...
2018-06-08T16:10:40-0700 [__main__.Index#info] Initializing SQLite DB...
2018-06-08T16:10:40-0700 [stdout#info] 2018-06-08 16:10:40,028 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2018-06-08T16:10:40-0700 [stdout#info] 2018-06-08 16:10:40,029 INFO sqlalchemy.engine.base.Engine ()
2018-06-08T16:10:40-0700 [stdout#info] 2018-06-08 16:10:40,029 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2018-06-08T16:10:40-0700 [stdout#info] 2018-06-08 16:10:40,029 INFO sqlalchemy.engine.base.Engine ()
2018-06-08T16:10:40-0700 [stdout#info] 2018-06-08 16:10:40,030 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("TRANSMISSION")
2018-06-08T16:10:40-0700 [stdout#info] 2018-06-08 16:10:40,030 INFO sqlalchemy.engine.base.Engine ()
2018-06-08T16:10:40-0700 [stdout#info] 2018-06-08 16:10:40,031 INFO sqlalchemy.engine.base.Engine INSERT INTO "TRANSMISSION" ("STATION", "SYSTEM", "CHANNEL") VALUES (?, ?, ?)
2018-06-08T16:10:40-0700 [stdout#info] 2018-06-08 16:10:40,031 INFO sqlalchemy.engine.base.Engine (('Radio 1', 'A', 'Ops'), ('Radio 4', 'B', 'Talk'))
2018-06-08T16:10:40-0700 [stdout#info] 2018-06-08 16:10:40,032 INFO sqlalchemy.engine.base.Engine COMMIT
2018-06-08T16:10:40-0700 [-] Main loop terminated.

However, if the SQLite DB is an in-memory DB, it fails, complaining that the table being written to doesn't exist:

2018-06-08T16:12:34-0700 [__main__.Index#info] Importing transmissions...
2018-06-08T16:12:34-0700 [__main__.Index#info] Initializing SQLite DB...
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,912 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,912 INFO sqlalchemy.engine.base.Engine ()
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,913 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,913 INFO sqlalchemy.engine.base.Engine ()
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,914 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("TRANSMISSION")
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,914 INFO sqlalchemy.engine.base.Engine ()
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,914 INFO sqlalchemy.engine.base.Engine 
2018-06-08T16:12:34-0700 [stdout#info] CREATE TABLE "TRANSMISSION" (
2018-06-08T16:12:34-0700 [stdout#info] 	"STATION" VARCHAR NOT NULL, 
2018-06-08T16:12:34-0700 [stdout#info] 	"SYSTEM" VARCHAR NOT NULL, 
2018-06-08T16:12:34-0700 [stdout#info] 	"CHANNEL" VARCHAR NOT NULL
2018-06-08T16:12:34-0700 [stdout#info] )
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,915 INFO sqlalchemy.engine.base.Engine ()
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,915 INFO sqlalchemy.engine.base.Engine COMMIT
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,916 INFO sqlalchemy.engine.base.Engine INSERT INTO "TRANSMISSION" ("STATION", "SYSTEM", "CHANNEL") VALUES (?, ?, ?)
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,916 INFO sqlalchemy.engine.base.Engine (('Radio 1', 'A', 'Ops'), ('Radio 4', 'B', 'Talk'))
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,916 INFO sqlalchemy.engine.base.Engine ROLLBACK
2018-06-08T16:12:34-0700 [-] main function encountered error
	Traceback (most recent call last):
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/twisted/internet/defer.py", line 500, in errback
	    self._startRunCallbacks(fail)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/twisted/internet/defer.py", line 567, in _startRunCallbacks
	    self._runCallbacks()
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/twisted/internet/defer.py", line 653, in _runCallbacks
	    current.result = callback(current.result, *args, **kw)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/twisted/internet/defer.py", line 1442, in gotResult
	    _inlineCallbacks(r, g, deferred)
	--- <exception caught here> ---
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/twisted/internet/defer.py", line 1384, in _inlineCallbacks
	    result = result.throwExceptionIntoGenerator(g)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/twisted/python/failure.py", line 422, in throwExceptionIntoGenerator
	    return g.throw(self.type, self.value, self.tb)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/mixer/alch_test.py", line 94, in addTransmissions
	    for transmission in transmissions
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/alchimia/engine.py", line 33, in container
	    result = work(*args, **kwargs)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2075, in execute
	    return connection.execute(statement, *multiparams, **params)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
	    return meth(self, multiparams, params)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
	    return connection._execute_clauseelement(self, multiparams, params)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
	    compiled_sql, distilled_params
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
	    context)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
	    exc_info
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
	    reraise(type(exception), exception, tb=exc_tb, cause=cause)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
	    raise value.with_traceback(tb)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1170, in _execute_context
	    context)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 505, in do_executemany
	    cursor.executemany(statement, parameters)
	sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: TRANSMISSION [SQL: 'INSERT INTO "TRANSMISSION" ("STATION", "SYSTEM", "CHANNEL") VALUES (?, ?, ?)'] [parameters: (('Radio 1', 'A', 'Ops'), ('Radio 4', 'B', 'Talk'))] (Background on this error at: http://sqlalche.me/e/e3q8)
	
2018-06-08T16:12:34-0700 [-] Main loop terminated.

The difference between these two runs is whether line 71 or line 72 is commented out.

[wsanchez]

IIRC, according to @glyph, the issue here is that SQLite doesn't work at all with Alchmia, because SQLite doesn't get along with multiple threads.

So… either Alchmia should prevent the use of threads when one is using SQLite, or, the example code on the README file should stop using SQLite as an example, because presently its lying.

[glyph]

IIRC, according to @glyph, the issue here is that SQLite doesn't work at all with Alchmia, because SQLite doesn't get along with multiple threads.

Wait, what? SQLite works fine with Alchimia. There are lots of unit tests showing that it does. SQLite's in-memory store can't easily be accessed by multiple threads, because there's no way that I'm aware of to get discrete "Connection" objects pointing at the same data structure.

[glyph]

So… either Alchmia should prevent the use of threads when one is using SQLite, or, the example code on the README file should stop using SQLite as an example, because presently its lying.

The example code should probably be modified to use a disk file, yeah.