fails to display table with foreign keys about sqlite-web HOT 12 CLOSED

The fix involved ensuring that dependent tables are specified when updating the cache with a newly-added table. This is fixed in peewee master branch. I will push a new release (will be 3.3.4) shortly.

from sqlite-web.

Is there any table that has a foreign key to sample?

from sqlite-web.

Looking more closely it looks like you have a table named expression that has a foreign key to sample. Can you share the full schema? You can do this by opening sqlite shell and running ".schema" at the prompt.

from sqlite-web.

yes, there is

create table expression(sample varchar(80)
                      , gene_symbol varchar(40)
                      , tpm double
                      , log2_tpm double
                      , PRIMARY KEY (sample, gene_symbol)
                      , FOREIGN KEY (gene_symbol) references gene(gene_symbol)
                      , FOREIGN KEY (sample) references sample(sample))

But the error occurs also when I click on expression and there is no table referencing expression.

from sqlite-web.

Ah, actually I just noticed:

• at the time the error occured, expression was not yet generated
• now, as expression exists, I can click on sample without error.
• Clicking on expression still produces the error (there is no table referencing expression)

from sqlite-web.

Are you using sqlite-web to load data? What do you mean "generated"?

I'd like to be able to reproduce the issue.

from sqlite-web.

by "generating" I mean executing the create table in the sqlite-web query interface.

To load data, I use an R script, although the issue occured also when the tables were still empty. (at least sample and expression were empty).

from sqlite-web.

Cool. Can you share the queries you executed? So I can replicate the issue.

from sqlite-web.

I don't have time right now, but I will try to set-up a full reproducible example during the next week.

from sqlite-web.

I'm going to close this and will re-open after you've commented. You will still be able to comment when you have time, even though it's closed. I'll reopen it at that point.

from sqlite-web.

Here we go!

Steps to reproduce.

1. I created a clean environment using anaconda

conda create -n sqlite-web-test
source activate sqlite-web-test
pip install sqlite-web
python --version

Python 3.6.5 :: Anaconda custom (64-bit)

2. create a database with a single table

sqlite3 test.sqlite "create table gene(gene_symbol varchar(40) primary key);"

3. run sqlite-web

sqlite_web test.sqlite

4. open the web interface, click on the gene table. Click on Query.

5. create the table using Query in the web interface.

create table expression(sample varchar(80)
, gene_symbol varchar(40)
, tpm double
, foreign key (gene_symbol) references gene(gene_symbol))

6. click on the expression table. Experience an Internal Server Error.

from sqlite-web.

Traceback:

Traceback (most recent call last):
  File "/home/charles/pypath/flask/app.py", line 2292, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/charles/pypath/flask/app.py", line 1815, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/charles/pypath/flask/app.py", line 1718, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/home/charles/pypath/flask/app.py", line 1813, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/charles/pypath/flask/app.py", line 1799, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "./sqlite_web.py", line 202, in inner
    return fn(table, *args, **kwargs)
  File "./sqlite_web.py", line 218, in table_structure
    ds_table = dataset[table]
  File "/home/charles/pypath/playhouse/dataset.py", line 63, in __getitem__
    self.update_cache(table)
  File "/home/charles/pypath/playhouse/dataset.py", line 92, in update_cache
    literal_column_names=True)
  File "/home/charles/pypath/playhouse/reflection.py", line 598, in generate_models
    literal_column_names=literal_column_names)
  File "/home/charles/pypath/playhouse/reflection.py", line 586, in introspect
    related_name=related_names.get(src))
  File "/home/charles/pypath/playhouse/reflection.py", line 120, in set_foreign_key
    self.rel_model = model_names[foreign_key.dest_table]
KeyError: u'gene'

After restarting the application, the table loads up fine, so this looks to be a cache-invalidation type of issue.

from sqlite-web.