2008/8/8 Daniel Hellsson <[EMAIL PROTECTED]> > 2008/8/8 Igor Tandetnik <[EMAIL PROTECTED]> > > "Daniel Hellsson" >> <[EMAIL PROTECTED]> wrote in >> message news:[EMAIL PROTECTED]<[EMAIL PROTECTED]> >> > I am writing unit tests for my database functions. >> > In one test I'm opening the database. >> > Then I open the database again with a new pointer. >> > With that pointer I create the table with >> > >> > "CREATE TABLE IF NOT EXISTS foo \ >> > ( id INTEGER PRIMARY KEY, \ >> > bar_id INTEGER, \ >> > hoopy INTEGER, \ >> > frood_id INTEGER )" >> > >> > then I close that connection (with sqlite3_close) and with the old >> > pointer I do an sqlite3_prepare_v2 with >> > >> > "SELECT frood_id,hoopy FROM ingredients WHERE bar_id=1" >> > >> > This is where I get an SQLITE_SCHEMA error (from prepare) with the >> > error message: table 'foo' does not exist. >> >> What is the relation between 'foo' and 'ingredients' ? The statement >> never mentions foo, as far as I can tell. >> >> Assuming it's a typo, this is a known issue. SQLite caches the database >> schema when opening the database, and re-reads it every time it detects >> the schema has changed. But sqlite3_prepare* works off of the cached >> schema: it would be too expensive to actually read the file on disk >> every time a statement is prepared. >> >> So changes to the schema made on a different connection aren't visible >> until the database file is read by any statement. If you want to make >> sure you pick up the latest changes, run something like "select * from >> sqlite_master where 0;" >> >> Igor Tandetnik >> >> > Sorry for the confusion. I was trying to obfuscate what I was doing (this > is a commercial non-open-source project). :-) > > > > Ingredients was supposed to be 'foo', so it is indeed a typo. of sorts. :) > > > > Ah, ok, so this is a known problem. It was surprising to me since > everything else worked so nicely and only this one thing did not work. I > will probably have to rewrite my tests so that they open new connections to > the database instead of using existing connections like they do now. It is > only for convenience that I do that now. > > > A thousand thanks to you for your quick reply! > > > > /Daniel > Update: I tested your "empty" select statement as provided above, and the cache was not updated. Not until I actually selected something (type='table') was the cache updated. Isn't there any proper way to invalidate the cache? Like a sqlite3_refresh() or something.
/Daniel _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
