From 4b39b0f89dfd47dc9f5ba948e564c2afbbd44fef Mon Sep 17 00:00:00 2001 From: Sean Anderson Date: Sat, 7 Nov 2020 19:52:04 -0500 Subject: Support SQLite WITHOUT ROWID tables This adds support for creating tables WITHOUT ROWID in the SQLite dialect. WITHOUT ROWID tables were introduced in SQLite version 3.8.2 (2013-12-06). They do not use an implicit rowid column as the primary key. This may result in space and performance savings for tables without INTEGER primary keys and tables with composite primary keys. For more information about this feature, see the sqlite documentation [1]. [1] https://www.sqlite.org/withoutrowid.html Fixes: #5685 ### Checklist This pull request is: - [x] A new feature implementation - please include the issue number, and create an issue if none exists, which must include a complete example of how the feature would look. - Please include: `Fixes: #` in the commit message - please include tests. Closes: #5686 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/5686 Pull-request-sha: 2b44782d1b3d858e31ce1ff8e08e197af37344d8 Change-Id: Ifcf727b0c07c90e267b79828a8e3fd7a8260a074 --- lib/sqlalchemy/dialects/sqlite/base.py | 28 +++++++++++++++++++++++++++- 1 file changed, 27 insertions(+), 1 deletion(-) (limited to 'lib/sqlalchemy/dialects/sqlite') diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index 5efd0d9c9..aead99147 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -583,6 +583,21 @@ or on a per-:class:`_engine.Engine` basis:: When using the per-:class:`_engine.Engine` execution option, note that **Core and ORM queries that use UNION may not function properly**. +SQLite-specific table options +----------------------------- + +One option for CREATE TABLE is supported directly by the SQLite +dialect in conjunction with the :class:`_schema.Table` construct: + +* ``WITHOUT ROWID``:: + + Table("some_table", metadata, ..., sqlite_with_rowid=False) + +.. seealso:: + + `SQLite CREATE TABLE options + `_ + """ # noqa import datetime @@ -1259,6 +1274,11 @@ class SQLiteDDLCompiler(compiler.DDLCompiler): return text + def post_create_table(self, table): + if table.dialect_options["sqlite"]["with_rowid"] is False: + return "\n WITHOUT ROWID" + return "" + class SQLiteTypeCompiler(compiler.GenericTypeCompiler): def visit_large_binary(self, type_, **kw): @@ -1466,7 +1486,13 @@ class SQLiteDialect(default.DefaultDialect): isolation_level = None construct_arguments = [ - (sa_schema.Table, {"autoincrement": False}), + ( + sa_schema.Table, + { + "autoincrement": False, + "with_rowid": True, + }, + ), (sa_schema.Index, {"where": None}), ( sa_schema.Column, -- cgit v1.2.1