Re: [Django] #20516: Allow use of prepared statements

[Django]

#20516: Allow use of prepared statements
-+-
 Reporter:  est  |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  db, prepared | Triage Stage:  Accepted
  statement  |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Jeongsoo, Park):

 * cc: Jeongsoo, Park (added)


-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/061.524887b4655a4961d996be19b4a29d87%40djangoproject.com.

Re: [Django] #20516: Allow use of prepared statements

[Django]

#20516: Allow use of prepared statements
-+-
 Reporter:  est  |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  db, prepared | Triage Stage:  Accepted
  statement  |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by aron45):

 * cc: aron45 (added)


--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/061.be04fc5a5fa0445f85590c9d89bdfe42%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #20516: Allow use of prepared statements

[Django]

#20516: Allow use of prepared statements
-+-
 Reporter:  est  |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  db, prepared | Triage Stage:  Accepted
  statement  |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by shaib):

 On Oracle, the concept of prepared statements is linked to a connection-
 level statement cache. Prepared statements are not handled with different
 SQL, but with different API calls -- specifically, cx_Oracle provides a
 [http://cx-oracle.readthedocs.org/en/latest/cursor.html#Cursor.prepare
 prepare] method on its cursors. This method is not currently used by
 Django's Oracle backend.

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/061.4ccd01df69f3d931115d62326c8346bd%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #20516: Allow use of prepared statements

[Django]

#20516: Allow use of prepared statements
-+-
 Reporter:  est  |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  db, prepared | Triage Stage:  Accepted
  statement  |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by thedrow):

 akaariai says we can just subclass the Query object and replace the
 parameters with the prepared statements placeholders.

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/061.31f16b03c5a6a86023f3d110a73c8608%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #20516: Allow use of prepared statements

[Django]

#20516: Allow use of prepared statements
-+-
 Reporter:  est  |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  db, prepared | Triage Stage:  Accepted
  statement  |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by thedrow):

 As apollo13 mentioned on IRC, prepared statements are at the session level
 on PostgresSQL so we can just store them on the connection object.

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/061.bd07c5110e0b1b680bdce3178cea8a3a%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #20516: Allow use of prepared statements

[Django]

#20516: Allow use of prepared statements
-+-
 Reporter:  est  |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  db, prepared | Triage Stage:  Accepted
  statement  |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by thedrow):

 I investigated about implementing it today and it turns out that the Query
 class has a method called sql_with_params() which returns the SQL query
 without concatenating the parameters.

 MySQL uses the following syntax:

 {{{#!sql
 PREPARE usrrptplan FROM "SELECT * FROM users u, logs l WHERE u.usrid=? AND
 u.usrid=l.usrid
 AND l.date = ?;"
 EXECUTE usrrptplan USING 1, NOW()
 }}}

 PostgreSQL uses the following syntax:

 {{{#!sql
 PREPARE usrrptplan (int) AS
 SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid
 AND l.date = $2;
 EXECUTE usrrptplan(1, NOW());
 }}}

 It seems that when you call prepare() you need to switch the query
 entirely with the EXECUTE syntax.
 Moreover, at least in PostgreSQL you have to check if the statement is
 already prepared and there's no SQL syntax to do that.
 You need to query the pg_prepared_statements table in order to verify that
 it doesn't exist.
 Since a prepared statement requires a name we can either randomize one or
 have one provided for us.
 I think that providing both options is a good idea.

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/061.bf6507b274c56ca32c953d9304cc2ee2%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #20516: Allow use of prepared statements

[Django]

#20516: Allow use of prepared statements
-+-
 Reporter:  est  |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:
 Severity:  Normal   | Triage Stage:  Accepted
 Keywords:  db, prepared |  Needs documentation:  0
  statement  |  Patch needs improvement:  0
Has patch:  0|UI/UX:  0
  Needs tests:  0|
Easy pickings:  0|
-+-

Comment (by the_drow):

 The way I see it a PreparedQuerySet should support the following:
 * Preparing on execution of a query for the entire session:

 {{{
 qs = list(MyModel.objects.filter(foo__bar=1).prepare())
 qs2 = list(MyModel.objects.filter(foo__bar=2)) # Already prepared for this
 session (that is the connection)
 }}}

 The query parameters are inferred from the query itself.
 This is very convenient and doesn't require anyone to learn the new
 syntax.

 * Preparing for a certain scope and deallocating:

 {{{
 with MyModel.objects.filter(foo__bar=Param('foobar')).prepare() as qs:
  results = qs(1)
  # Some logic
  results = qs(2)
  # Some logic
 }}}

 When the PreparedQuerySet enters the context the query is prepared. When
 it exists the prepared query is deallocated.
 It's not always a good idea to prepare queries for a very long time for
 rapidly changing tables (small tables don't need to use indexes for
 example).

 * Preparing a query for usage in a Manager or QuerySet:

 {{{
 class MyQuerySet(QuerySet):
   foobars = MyModel.objects.filter(foo__bar=Param('foobar')).prepare()
 }}}

 This will turn foobars into a callable with an argument named foobar.
 The query will be prepared once the first query using it will be executed.
 You can prepare ahead of time:
 {{{
 class MyQuerySet(QuerySet):
   foobars =
 MyModel.objects.filter(foo__bar=Param('foobar')).prepare(ahead=True) # I
 need a better name for the argument here
 }}}
 You can also prepare a query that will be deallocated after a TTL:
 {{{
 class MyQuerySet(QuerySet):
   foobars =
 MyModel.objects.filter(foo__bar=Param('foobar')).prepare(ttl=6)
 }}}

 Explicitly deallocating a prepared query should also be possible:
 {{{
 qs = MyModel.objects.filter(foo__bar=1).prepare()
 results = list(qs)
 qs2 = list(MyModel.objects.filter(foo__bar=2)) # Already prepared for this
 session (that is the connection)
 qs.deallocate()
 results = list(qs) # the query is prepared again
 }}}
 The query will be prepared again once the first query is executed.

 What do you guys think?

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/061.3d8ef936905df9af59bbafc6e7556c9a%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #20516: Allow use of prepared statements

[Django]

#20516: Allow use of prepared statements
-+-
 Reporter:  est  |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:
 Severity:  Normal   | Triage Stage:  Accepted
 Keywords:  db, prepared |  Needs documentation:  0
  statement  |  Patch needs improvement:  0
Has patch:  0|UI/UX:  0
  Needs tests:  0|
Easy pickings:  0|
-+-

Comment (by FunkyBob):

 Could you elaborate on how you see context manager working with this
 feature?

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/061.691c4fb2fb29623c493534b39bc961e6%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #20516: Allow use of prepared statements

[Django]

#20516: Allow use of prepared statements
-+-
 Reporter:  est  |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:
 Severity:  Normal   | Triage Stage:  Accepted
 Keywords:  db, prepared |  Needs documentation:  0
  statement  |  Patch needs improvement:  0
Has patch:  0|UI/UX:  0
  Needs tests:  0|
Easy pickings:  0|
-+-

Comment (by the_drow):

 PostgresSQL also supports prepare statements.

 @FunkyBob: That syntax looks great but the PreparedQuerySet object should
 support a context manager.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/061.09e9dcafdc70a8256bcb6d49904d99e9%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #20516: Allow use of prepared statements

[Django]

#20516: Allow use of prepared statements
-+-
 Reporter:  est  |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:
 Severity:  Normal   | Triage Stage:  Accepted
 Keywords:  db, prepared |  Needs documentation:  0
  statement  |  Patch needs improvement:  0
Has patch:  0|UI/UX:  0
  Needs tests:  0|
Easy pickings:  0|
-+-

Comment (by david@…):

 This isn't just for MySQL.  If possible, it would be really nice to have
 some facility for using server-side prepare statements not created by
 Django.  https://github.com/dimitri/preprepare is an example of such a use
 case

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/061.e967700e7bf1f7a97159b1f7f1b379e0%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #20516: Allow use of prepared statements

[Django]

#20516: Allow use of prepared statements
-+-
 Reporter:  est  |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:
 Severity:  Normal   | Triage Stage:  Accepted
 Keywords:  db, prepared |  Needs documentation:  0
  statement  |  Patch needs improvement:  0
Has patch:  0|UI/UX:  0
  Needs tests:  0|
Easy pickings:  0|
-+-

Comment (by FunkyBob):

 One idea that struck me the other day-

 A prepared statement could yield a function with keyword arguments
 matching its bound parameters.

 Something like:

 s = MyModel.objects.filter(foo__bar=Param('able'),
 baz=Param('baker')).exclude(foo_quux=7)

 Though perhaps an explicit "prepare()" would be better, the Param
 arguments mark where to bind parameters, and what to call them.

 The, you can execute the query with:

 for my in s(able=1, baker="wibble")

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/061.830f419bfd0796a363a7eb851ef28cb4%40djangoproject.com.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [Django] #20516: Allow use of prepared statements

[Django]

#20516: Allow use of prepared statements
-+-
 Reporter:  est  |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:
 Severity:  Normal   | Triage Stage:  Accepted
 Keywords:  db, prepared |  Needs documentation:  0
  statement  |  Patch needs improvement:  0
Has patch:  0|UI/UX:  0
  Needs tests:  0|
Easy pickings:  0|
-+-

Comment (by FunkyBob):

 Surely the security only improves if the DB driver is faulty?  Django
 already passes parameterised queries to the DB driver to handle escaping
 [or pass through parameterised, as some do].

 But in general, I'm happy to throw my brain behind this effort :)

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/061.c08f1ddb12472949838a86ffd3758271%40djangoproject.com.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [Django] #20516: Allow use of prepared statements

[Django]

#20516: Allow use of prepared statements
-+-
 Reporter:  est  |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:
 Severity:  Normal   | Triage Stage:  Accepted
 Keywords:  db, prepared |  Needs documentation:  0
  statement  |  Patch needs improvement:  0
Has patch:  0|UI/UX:  0
  Needs tests:  0|
Easy pickings:  0|
-+-

Comment (by anonymous):

 `with` statements seem to be the correct way to handle this from an API
 standpoint.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/061.9ed3f6218f99ae87c1163114cb9469dd%40djangoproject.com.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [Django] #20516: Allow use of prepared statements

[Django]

#20516: Allow use of prepared statements
-+-
 Reporter:  est  |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:
 Severity:  Normal   | Triage Stage:  Accepted
 Keywords:  db, prepared |  Needs documentation:  0
  statement  |  Patch needs improvement:  0
Has patch:  0|UI/UX:  0
  Needs tests:  0|
Easy pickings:  0|
-+-
Changes (by mpessas):

 * cc: mpessas@… (added)


-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/061.310e88dd2f25e4f0043c9b4fd728b0e0%40djangoproject.com.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [Django] #20516: Allow use of prepared statements (was: Prepared statement for MySQL)

[Django]

#20516: Allow use of prepared statements
-+-
 Reporter:  est  |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:
 Severity:  Normal   | Triage Stage:  Accepted
 Keywords:  db, prepared |  Needs documentation:  0
  statement  |  Patch needs improvement:  0
Has patch:  0|UI/UX:  0
  Needs tests:  0|
Easy pickings:  0|
-+-
Changes (by akaariai):

 * needs_better_patch:   => 0
 * needs_tests:   => 0
 * version:  1.6-alpha-1 => master
 * keywords:  db, mysql, prepared statement => db, prepared statement
 * needs_docs:   => 0
 * stage:  Unreviewed => Accepted


Comment:

 Yes, I think addition of prepared statements is something to consider. No
 idea of what would be a nice API, or if this could be somehow delegated to
 third party apps. The latter option means some hooks are needed somewhere
 in the query execution path. One such point is execute_sql() where it
 would be fairly straightforward to change the used SQL to prepared
 statements if the query is set to use prepared statements.

 FWIW there are use cases where prepared statements can give easily over
 50% speedup. Such cases are simple to execute queries which retrieve just
 a few rows of data, but which have a massive amount of joins (so that
 planning overhead is big). Using .select_related() and complicated
 .filter() clauses are easy ways to produce such queries.

 I don't see any reason to restrict this to MySQL only.

 (This feature seems to need at least these parts:
   - API for setting given queryset to use prepared statements - using them
 automatically is generally a bad idea
   - Convert the query's execution to use prepared statement instead of
 SQL. This consists of:
  - A check if the given query is already prepared for the used
 connection
  - If so, use the correct prepared statement
  - If not, create a new prepared statement and use that)

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/061.8684f050e9a1ea629264a9b73ada0766%40djangoproject.com.
For more options, visit https://groups.google.com/groups/opt_out.