Skip to content

Update official datasetteproject/datasette Docker container to SQLite 3.26.0 #397

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.

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

Closed
claes opened this issue Jan 8, 2019 · 3 comments
Closed
Labels

Comments

@claes
Copy link

claes commented Jan 8, 2019

I try to start datasette on a database that contains the below view

It fails in a way that makes me think it does not support the window functions SQL syntax.

create view general_ledger as
select transactions.account_number, strftime("%Y-%m-%d", verifications.verification_date) as verification_date, verifications.verification_number, verifications.verification_text, 
case when transactions.centi_amount >= 0 and verifications.verification_number > 0 then printf("%.2f", (transactions.centi_amount/100.0))
end as debit,
case when transactions.centi_amount <= 0 and verifications.verification_number > 0 then printf("%.2f", (transactions.centi_amount/100.0)) 
end as credit,
printf("%.2f", sum(transactions.centi_amount) over (partition by transactions.account_number 
    order by verifications.verification_number range between unbounded preceding and current row)/100.0)
from verifications inner join transactions on transactions.verification_id = verifications.id 
order by transactions.account_number, verifications.verification_number;
docker run -p 8001:8001 -v `pwd`:/mnt     datasetteproject/datasette     datasette -p 8001 -h 0.0.0.0 /mnt/ledger.db
Serve! files=('/mnt/ledger.db',) on port 8001
Traceback (most recent call last):
  File "/usr/local/bin/datasette", line 11, in <module>
    sys.exit(cli())
  File "/usr/local/lib/python3.6/site-packages/click/core.py", line 722, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/click/core.py", line 697, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.6/site-packages/click/core.py", line 1066, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.6/site-packages/click/core.py", line 895, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.6/site-packages/click/core.py", line 535, in invoke
    return callback(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/datasette/cli.py", line 375, in serve
    ds.inspect()
  File "/usr/local/lib/python3.6/site-packages/datasette/app.py", line 308, in inspect
    "views": inspect_views(conn),
  File "/usr/local/lib/python3.6/site-packages/datasette/inspect.py", line 30, in inspect_views
    return [v[0] for v in conn.execute('select name from sqlite_master where type = "view"')]
sqlite3.DatabaseError: malformed database schema (general_ledger) - near "over": syntax error

@simonw
Copy link
Owner

simonw commented Jan 10, 2019

What version of SQLite are you seeing in Datasette? You can tell by hitting http://localhost:8001/-/versions - e.g. here: https://latest.datasette.io/-/versions

My best guess is that your Python SQLite module is running an older version that doesn't support window functions.

One way you can fix that is with the pysqlite3 module - try running this in your virtual environment:

pip install git+git://github.com/karlb/pysqlite3

That's using a fork of the official module that embeds a full recent SQLite. See this issue thread for more details: coleifer/pysqlite3#2

@simonw
Copy link
Owner

simonw commented Jan 10, 2019

Oh I just saw you're using the official Datasette docker package - yeah, that's not bundled with a recent SQLite at the moment. We should update that:

datasette/Dockerfile

Lines 9 to 11 in 5b02611

RUN wget "https://www.sqlite.org/2018/sqlite-autoconf-3230100.tar.gz" && tar xzf sqlite-autoconf-3230100.tar.gz \
&& cd sqlite-autoconf-3230100 && ./configure --disable-static --enable-fts5 --enable-json1 CFLAGS="-g -O2 -DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_FTS4=1 -DSQLITE_ENABLE_RTREE=1 -DSQLITE_ENABLE_JSON1" \
&& make && make install

@simonw simonw changed the title Window functions in view fails Update official datasetteproject/datasette Docker container to SQLite 3.26.0 Jan 10, 2019
@simonw simonw added the small label Jan 10, 2019
@simonw simonw closed this as completed in 50d8d82 Jan 11, 2019
@simonw
Copy link
Owner

simonw commented Jan 11, 2019

If you pull the latest image you should get the right SQLite version now:

docker pull datasetteproject/datasette
docker run -p 8001:8001 \
    datasetteproject/datasette \
    datasette -p 8001 -h 0.0.0.0

http://0.0.0.0:8001/-/versions now gives me:

        "version": "3.26.0"

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

2 participants