github: commits: 3 rows where "author_date" is on date 2017-11-05

3 rows where "author_date" is on date 2017-11-05

Search:

descending

sha ▼	message	author_date	committer_date	raw_author	raw_committer	repo	author	committer
186c513a61a091b9f83d788e25b08f41a84ed9a3	Support parameterized SQL and block potentially harmful queries You can now call arbitrary SQL like this: /flights?sql=select%20%20from%20airports%20where%20country%20like%20:c&c=iceland Unescaped, those querystring params look like this: sql = select from airports where country like :c c = iceland So SQL can be constructed with named parameters embedded in it, which will then be read from the querystring and correctly escaped. This means we can aggressively filter the SQL parameter for potentially dangerous syntax. For the moment we enforce that it starts with a SELECT statement and we ban the sequence "pragma" from it entirely. If you need to use pragma in a query, you can use the new named parameter mechanism. Fixes #39	2017-11-05T02:49:18Z	2017-11-05T02:49:18Z	Simon Willison 2946d096d0cdefdc017559e6b57e87658736e843	Simon Willison 2946d096d0cdefdc017559e6b57e87658736e843	datasette 107914493	simonw 9599	simonw 9599
1fc75809a6aa17860944b4cc3a4d7175cd53b1f4	Refactored everything into a factory function I now call a factory function to construct the Sanic app: app = app_factory(files) This allows me to pass additional arguments to it, e.g. the files to serve. Also refactored my class-based views to accept jinja as an argument, e.g: app.add_route( TableView.as_view(jinja), '/<db_name:[^/]+>/<table:[^/]+?><as_json:(.jsono?)?$>' )	2017-11-05T02:13:44Z	2017-11-05T02:13:44Z	Simon Willison 2946d096d0cdefdc017559e6b57e87658736e843	Simon Willison 2946d096d0cdefdc017559e6b57e87658736e843	datasette 107914493	simonw 9599	simonw 9599
31b21f5c5e15fc3acab7fabb170c1da71dc3c98c	Moved all SQLite queries to threads SQLite operations are blocking, but we're running everything in Sanic, an asyncio web framework, so blocking operations are bad - a long-running DB operation could hold up the entire server. Instead, I've moved all SQLite operations into threads. These are managed by a concurrent.futures ThreadPoolExecutor. This means I can run up to X queries in parallel, and I can continue to queue up additional incoming HTTP traffic while the threadpool is busy. Each thread is responsible for managing its own SQLite connections - one per database. These are cached in a threadlocal. Since we are working with immutable, read-only SQLite databases it should be safe to share SQLite objects across threads. On this assumption I'm using the check_same_thread=False option. Opening a database connection looks like this: conn = sqlite3.connect( 'file:filename.db?immutable=1', uri=True, check_same_thread=False, ) The following articles were helpful in figuring this out: * https://pymotw.com/3/asyncio/executors.html * https://marlinux.wordpress.com/2017/05/19/python-3-6-asyncio-sqlalchemy/ Closes #45. Refs #38.	2017-11-05T02:21:44Z	2017-11-05T02:21:44Z	Simon Willison 2946d096d0cdefdc017559e6b57e87658736e843	Simon Willison 2946d096d0cdefdc017559e6b57e87658736e843	datasette 107914493	simonw 9599	simonw 9599

Advanced export

JSON shape: default, array, newline-delimited, object

CREATE TABLE [commits] (
   [sha] TEXT PRIMARY KEY,
   [message] TEXT,
   [author_date] TEXT,
   [committer_date] TEXT,
   [raw_author] TEXT REFERENCES [raw_authors]([id]),
   [raw_committer] TEXT REFERENCES [raw_authors]([id]),
   [repo] INTEGER REFERENCES [repos]([id]),
   [author] INTEGER REFERENCES [users]([id]),
   [committer] INTEGER REFERENCES [users]([id])
);
CREATE INDEX [idx_commits_committer]
                ON [commits] ([committer]);
CREATE INDEX [idx_commits_author]
                ON [commits] ([author]);
CREATE INDEX [idx_commits_repo]
                ON [commits] ([repo]);
CREATE INDEX [idx_commits_raw_committer]
                ON [commits] ([raw_committer]);
CREATE INDEX [idx_commits_raw_author]
                ON [commits] ([raw_author]);