Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

fails to display table with foreign keys #42

Closed
grst opened this issue Apr 26, 2018 · 12 comments
Closed

fails to display table with foreign keys #42

grst opened this issue Apr 26, 2018 · 12 comments

Comments

@grst
Copy link

grst commented Apr 26, 2018 •
edited
Loading

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
Copy link
Owner

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

@coleifer
Copy link
Owner

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
Copy link
Author

grst commented Apr 26, 2018

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
Copy link
Author

grst commented Apr 26, 2018

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
Copy link
Owner

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

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

@grst
Copy link
Author

grst commented Apr 26, 2018

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
Copy link
Owner

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

@grst
Copy link
Author

grst commented Apr 26, 2018

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

@coleifer
Copy link
Owner

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
Copy link
Author

grst commented May 4, 2018

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)
  1. create a database with a single table
sqlite3 test.sqlite "create table gene(gene_symbol varchar(40) primary key);"
  1. run sqlite-web
sqlite_web test.sqlite

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

  2. 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))
  1. click on the expression table. Experience an Internal Server Error.

@coleifer coleifer reopened this May 4, 2018
@coleifer
Copy link
Owner

coleifer commented May 4, 2018

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
Copy link
Owner

coleifer commented May 4, 2018

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.

@dependabot-preview dependabot-preview bot mentioned this issue May 7, 2018
Merged
This was referenced May 25, 2018
Merged
Closed
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@coleifer @grst