[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Berker Peksag]

Berker Peksag added the comment:


New changeset 0e6cb2ea624570ed08c354f1ed1f595dab4192d6 by Berker Peksag (Aviv 
Palivoda) in branch 'master':
bpo-26187: Test that set_trace_callback() is not called multiple times (GH-461)
https://github.com/python/cpython/commit/0e6cb2ea624570ed08c354f1ed1f595dab4192d6


--

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Donald Stufft]

Changes by Donald Stufft :


--
pull_requests: +920

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Berker Peksag]

Berker Peksag added the comment:


New changeset 86a670543ff97d52fd9b8ca0477f8b6d27ee946d by Berker Peksag (Aviv 
Palivoda) in branch 'master':
bpo-9303: Migrate sqlite3 module to _v2 API to enhance performance (#359)
https://github.com/python/cpython/commit/86a670543ff97d52fd9b8ca0477f8b6d27ee946d


--

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Larry Hastings]

Changes by Larry Hastings :


--
pull_requests:  -583

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Larry Hastings]

Changes by Larry Hastings :


--
pull_requests: +583

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Berker Peksag]

Berker Peksag added the comment:

I will add commit information manually: 
https://github.com/python/cpython/commit/86a670543ff97d52fd9b8ca0477f8b6d27ee946d

Thanks!

--

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Berker Peksag]

Changes by Berker Peksag :


--
resolution: postponed -> fixed
stage: patch review -> resolved
status: open -> closed

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Aviv Palivoda]

Aviv Palivoda added the comment:

I changed the patch to use SQLITE_VERSION_NUMBER and it looks way better. 
Thanks Serhiy

--

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Serhiy Storchaka]

Serhiy Storchaka added the comment:

> sqlite3_open_v2 - 2007-09-04 (3.5.0) alpha
> sqlite3_prepare_v2 - 2007-01-04 (3.3.9)
> sqlite3_close_v2 - 2012-09-03 (3.7.14)

There are compile-time checks for supporting SQLite older than 3.2.2.

I think it is better to use the preprocessor rather of autotools for 
conformance with the rest of code.

> What should I do for this to work on windows?

On Windows manually written PC/pyconfig.h is used rather of generated by 
configure pyconfig.h. If you change pyconfig.h, you should update PC/pyconfig.h 
too. But I think this is not needed in this case. Just use 
SQLITE_VERSION_NUMBER.

--

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Aviv Palivoda]

Aviv Palivoda added the comment:

from https://www.sqlite.org/changes.html:
sqlite3_open_v2 - 2007-09-04 (3.5.0) alpha
sqlite3_prepare_v2 - 2007-01-04 (3.3.9)
sqlite3_close_v2 - 2012-09-03 (3.7.14)

In issue 29355 Ma Lin says that RHEL6 comes with SQLite 3.6.x. I think that 
removing that removing the check for sqlite3_prepare_v2 and sqlite3_open_v2 is 
fine but sqlite3_close_v2 need to stay.

> Don't forget about Windows where autotools are not used.

What should I do for this to work on windows?

--

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Serhiy Storchaka]

Serhiy Storchaka added the comment:

Is detection in the configure script needed? What are SQLite versions in which 
_v2 APIs were added? What is minimal supported SQLite version?

Don't forget about Windows where autotools are not used.

--
nosy: +serhiy.storchaka
versions: +Python 3.7 -Python 3.6

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Aviv Palivoda]

Aviv Palivoda added the comment:

I opened a PR. This actually is a bugfix in addition to an enhancement as it 
solves issue 26187 as well.

--

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Aviv Palivoda]

Changes by Aviv Palivoda :


--
pull_requests: +306

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Dima Tisnek]

Dima Tisnek added the comment:

Can someone review Aviv's patch?

--

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Aviv Palivoda]

Aviv Palivoda added the comment:

I made a new patch to fix this issue. I left a fallback to the old API as Jim 
suggested. In addition to the changes in Robin`s patch I changed sqlite3_close 
to sqlite3_close_v2 if available.

This solves issue 26187 as well.

--
nosy: +palaviv
Added file: http://bugs.python.org/file42663/9303.patch

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Mark Lawrence]

Changes by Mark Lawrence :


--
nosy:  -BreamoreBoy

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Berker Peksag]

Changes by Berker Peksag :


--
nosy: +berker.peksag
type: performance -> enhancement

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Gerhard Häring]

Gerhard Häring added the comment:

The externally maintained version of the sqlite3 module has now been switched 
to the v2 statement API. pysqlite is for Python 2.7 only. I'd suggest to 
revisit this for Python 3.6 and then try to port most fixes from pysqlite to 
the sqlite3 module.

--
resolution:  - postponed
versions: +Python 3.6 -Python 3.5

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue9303
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[STINNER Victor]

Changes by STINNER Victor victor.stin...@gmail.com:


--
nosy: +haypo

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue9303
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Dima Tisnek]

Dima Tisnek added the comment:

Is there any hope?
Surely sqlite backwards compatibility is not an issue any longer...

--
nosy: +Dima.Tisnek

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue9303
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Mark Lawrence]

Changes by Mark Lawrence breamore...@yahoo.co.uk:


--
nosy: +BreamoreBoy

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue9303
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Gerhard Häring]

Gerhard Häring added the comment:

ok, i will have to look into this

--

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue9303
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Antoine Pitrou]

Changes by Antoine Pitrou pit...@free.fr:


--
versions: +Python 3.5 -Python 3.4

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue9303
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Antoine Pitrou]

Antoine Pitrou added the comment:

This would be nice to have in 3.4. When were the _v2 APIs introduced?

See also issue13773 which uses sqlite3_open_v2() to pass some flags.

--
nosy: +larry, pitrou
versions: +Python 3.4 -Python 3.3

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue9303
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Petri Lehtinen]

Petri Lehtinen pe...@digip.org added the comment:

What Sqlite version are you targeting? Some systems use quite old Sqlite 
versions, see #14572 for example.

--
nosy: +petri.lehtinen

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue9303
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Antoine Pitrou]

Changes by Antoine Pitrou pit...@free.fr:


--
stage: needs patch - patch review
versions: +Python 3.3 -Python 3.2

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue9303
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Arfrever Frehtes Taifersar Arahesis]

Changes by Arfrever Frehtes Taifersar Arahesis arfrever@gmail.com:


--
nosy: +Arfrever

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue9303
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Robin Schreiber]

Robin Schreiber robin.schrei...@me.com added the comment:

I have now submitted a patch, which swapped out all the necessary calls. Tests 
are all running as expected. I will now try to remove some backwards 
compatibility code.

--
keywords: +patch
Added file: http://bugs.python.org/file25211/sqlite.patch

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue9303
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Robin Schreiber]

Changes by Robin Schreiber robin.schrei...@me.com:


Removed file: http://bugs.python.org/file25211/sqlite.patch

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue9303
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Robin Schreiber]

Changes by Robin Schreiber robin.schrei...@me.com:


Added file: http://bugs.python.org/file25212/sqlite.patch

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue9303
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Jim Jewett]

Jim Jewett jimjjew...@gmail.com added the comment:

I can't speak for GSoC or Gerhard, but it strikes me as a reasonable first 
step.  An alternatives woube be writing it with fallbacks (so older sqlite can 
still be used, though less efficiently).  It would also be nice to clean up at 
least one call with compatibility cruft.  That said, don't hesitate to submit a 
patch that does *something*, and then replace it with more comprehensive 
patches later.

--
nosy: +Jim.Jewett

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue9303
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Robin Schreiber]

Robin Schreiber robin.schrei...@me.com added the comment:

Apparently this issue has not been dealt with for quite some time now. As a 
prospective GSoC student, I still need to submit a patch to pass final 
screening and I thought, that the needed patch here would be quite suitable for 
a beginner. I plan to submit a patch, which simply replaces the deprecated 
method calls with the new ones. 
Maybe we can also remove some parts of the module code, because of the new 
semantics of prepare_v2(), however I would first like to hear Gerhards opinion 
on that :-)

--
nosy: +Robin.Schreiber

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue9303
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Michael Schwarz]

New submission from Michael Schwarz michi.schw...@gmail.com:

The Python sqlite module currently uses some deprecated API [0] of SQLite. 
These are functions that have a counterpart with _v2 appended to their name.

The SQLite query planner will not use certain optimizations when using the old 
API. For example, as documented in [1], using a statement with a GLOB or LIKE 
operator where the pattern is parametrized will only use an appropriate index 
if sqlite3_prepare_v2() is used instead of sqlite3_prepare().

Following is an example of such a query. When executed, table 'test' is scanned 
row by row, which requires all data in the table to be loaded from disk.

cursor.execute('create table test(a text)')
cursor.execute('create index test_index on test(a)')
# insert some data here
cursor.execute('select * from test where a glob ?', ['prefix*'])

When the same query is executed in the sqlite3 command line utility, the index 
'test_index' is used instead.

sqlite create table test(a text);
sqlite create index test_index on test(a);
sqlite explain query plan select * from test where a glob 'prefix*';
order   fromdetail
0   0   TABLE test WITH INDEX test_index

The query in this example is not parametrized as parameters can't be filled in 
when using the sqlite3 command line utility. This is just to show that the 
schema and the query allow the index to be used with certain pattern strings.

[0]: http://www.sqlite.org/c3ref/funclist.html
[1]: http://www.sqlite.org/optoverview.html#like_opt

Michael

--
components: Extension Modules
messages: 110739
nosy: feuermurmel
priority: normal
severity: normal
status: open
title: Migrate sqlite3 module to _v2 API to enhance performance
type: performance
versions: Python 3.1

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue9303
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Antoine Pitrou]

Changes by Antoine Pitrou pit...@free.fr:


--
assignee:  - ghaering
nosy: +ghaering
stage:  - needs patch
versions: +Python 3.2 -Python 3.1

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue9303
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Gerhard Häring]

Gerhard Häring g...@ghaering.de added the comment:

Yes, the sqlite module uses the old API, and is written to work with older 
SQLite releases and their respective bugs as well.

Using the new API will mean requiring newer SQLite releases.

If we do this, then this is the chance to remove all the obscure backwards 
compatibility code with older SQLite releases.

--

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue9303
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com