fails to display table with foreign keys

[grst]

Using the latest version from pip, I have the following issue:

When I click on a table that contains a foreign key, an Internal Server Error is caused with the following Traceback:

[2018-04-26 10:56:55,863] ERROR in app: Exception on /expression/ [GET]
Traceback (most recent call last):
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/flask/app.py", line 1982, in wsgi_app
    response = self.full_dispatch_request()
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/flask/app.py", line 1614, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/flask/app.py", line 1517, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/flask/_compat.py", line 33,in reraise
    raise value
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/flask/app.py", line 1612, in full_dispatch_request
    rv = self.dispatch_request()
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/flask/app.py", line 1598, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/sqlite_web/sqlite_web.py", line 201, in inner
    return fn(table, *args, **kwargs)
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/sqlite_web/sqlite_web.py", line 217, in table_structure
    ds_table = dataset[table]
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/playhouse/dataset.py", line63, in __getitem__
    self.update_cache(table)
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/playhouse/dataset.py", line92, in update_cache
    literal_column_names=True)
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/playhouse/reflection.py", line 598, in generate_models
    literal_column_names=literal_column_names)
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/playhouse/reflection.py", line 586, in introspect
    related_name=related_names.get(src))
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/playhouse/reflection.py", line 120, in set_foreign_key
    self.rel_model = model_names[foreign_key.dest_table]
KeyError: 'sample'

The SQL of the table looks like

create table sample (sample varchar(80)
                     , patient varchar(80)
                     , cohort varchar(10)
                     , sample_type varchar(4)
                     , PRIMARY KEY (sample)
                     , FOREIGN KEY (cohort) references cohort(cohort)
                     , FOREIGN KEY (sample_type) references sample_type(sample_type))

The SQL of the referenced tables like

create table cohort (cohort varchar(10)
                     , name text
                     , PRIMARY KEY (cohort));

create table sample_type (numeric_code varchar(2)
                          , sample_type varchar(8)
                          , definition text
                          , PRIMARY KEY (sample_type)
                          , UNIQUE(numeric_code))

[coleifer]

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

[coleifer]

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.

[grst]

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.

[grst]

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)

[coleifer]

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

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

[grst]

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).

[coleifer]

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

[grst]

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

[coleifer]

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.

[grst]

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.

[coleifer]

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.

[coleifer]

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.