Skip to content

Commit a874391

Browse files
author
James William Pye
committed
Implement Cursor supporting SQL DECLARE.
When possible, pg_driver will use protocol level cursors. However, this is only reasonable inside a transaction as when in auto-commit mode, the bound cursor will disappear after the transaction ends. This limited lifetime forced pg_proboscis to fetch all rows on Bind(). The new cursor implementation will now choose how to bind the cursor based upon the connection's state and the parameters given to the instantiation method. Notably, scroll and hold are options only available to SQL DECLARE'd cursors. Side-effects and other fixes or changes: . Normalize descriptive attributes on statements, cursors, procedures, and connections: _pq_xact instead of _xact, _output_formats instead of _oformats. . Change ResultHandle to be _init() and _fini() based. . Make extract_command and extract_count a methods on the Complete() class. . Refer to bytes instead of str for finding copy lines. . Make cursor read-ahead configurable based on the fetchcount attribute. . Move Cursor.seek into pg_api; base it on scroll() and move() . Remove the unnecessary "bufsize" component from Cursor._state . Remove PreparedStatement sub-class. Too little difference to justify it. . Fix typio resolve; it wasn't breaking the loop when it found the IO pair. . Fix StoredProcedure; descend the query from the SP . Make connector's text snapshot obscure the password. . Use a generator for client3.Transaction.reverse... . Fix FixedOffset implementation; breakage was hidden by broken typio resolution. . Fix array_typio usage. Wrong parameters were being passed in.
1 parent ae8d9fd commit a874391

5 files changed

Lines changed: 907 additions & 598 deletions

File tree

‎postgresql/api.py‎

Lines changed: 106 additions & 36 deletions
Original file line numberDiff line numberDiff line change
@@ -130,9 +130,11 @@ class InterfaceElement(metaclass = ABCMeta):
130130
WARNING
131131
-------
132132
133-
Many of these APIs are used to support features that users are *not* expected
134-
to use. Almost everything on `InterfaceElement` is subject to deprecation.
133+
Many of these APIs are used to support features that users are *not*
134+
expected to use. Almost everything on `InterfaceElement` is subject to
135+
deprecation.
135136
"""
137+
ife_object_title = "<untitled>"
136138

137139
@apdoc
138140
@abstractproperty
@@ -409,23 +411,59 @@ class Cursor(
409411
"""
410412
ife_label = 'CURSOR'
411413
ife_ancestor = None
414+
_seek_whence_map = {
415+
0 : 'ABSOLUTE',
416+
1 : 'RELATIVE',
417+
2 : 'LAST',
418+
}
412419

420+
@apdoc
421+
@abstractproperty
422+
def cursor_id(self) -> str:
423+
"""
424+
The cursor's identifier.
425+
"""
426+
427+
@apdoc
428+
@abstractproperty
429+
def parameters(self) -> (tuple, None):
430+
"""
431+
The parameters bound to the cursor. `None`, if unknown.
432+
"""
433+
434+
@apdoc
413435
@abstractproperty
414-
def withscroll(self) -> bool:
436+
def query(self) -> ("PreparedStatement", None):
437+
"""
438+
The query object used to create the cursor. `None`, if unknown.
439+
"""
440+
441+
@apdoc
442+
@abstractproperty
443+
def insensitive(self) -> bool:
444+
"""
445+
Whether or not the cursor is insensitive. Extant versions of PostgreSQL
446+
only support insensitive cursors.
447+
"""
448+
449+
@apdoc
450+
@abstractproperty
451+
def with_scroll(self) -> bool:
415452
"""
416453
Whether or not the cursor is scrollable.
417454
"""
418455

456+
@apdoc
419457
@abstractproperty
420-
def withhold(self) -> bool:
458+
def with_hold(self) -> bool:
421459
"""
422460
Whether or not the cursor will persist across transactions.
423461
"""
424462

425463
@abstractmethod
426464
def read(self,
427465
quantity : "Number of rows to read" = None
428-
) -> "List of Rows":
466+
) -> [()]:
429467
"""
430468
Read the specified number of rows and return them in a list.
431469
This advances the cursor's position.
@@ -444,7 +482,26 @@ def __next__(self) -> "Row":
444482
"""
445483

446484
@abstractmethod
447-
def seek(self, offset, whence = 0):
485+
def scroll(self, number_of_rows : int):
486+
"""
487+
Set the cursor's position relative to the current position.
488+
Negative numbers can be used to scroll backwards.
489+
490+
This is a convenient interface to `seek` with a relative whence(``1``).
491+
492+
When `number_of_rows` is zero, there is no effect on the cursor.
493+
"""
494+
495+
@abstractmethod
496+
def move(self, position_in_cursor : int):
497+
"""
498+
Move the cursor's pointer to the specified position, `position_in_cursor`.
499+
The position is absolute, from which a negative position indicates
500+
relative to the end where a positive position indicate relative to the
501+
beginning.
502+
"""
503+
504+
def seek(self, offset, whence = 'ABSOLUTE'):
448505
"""
449506
Set the cursor's position to the given offset with respect to the
450507
whence parameter.
@@ -457,18 +514,21 @@ def seek(self, offset, whence = 0):
457514
Relative.
458515
``2``
459516
Absolute from end.
460-
"""
461517
462-
@abstractmethod
463-
def scroll(self, number_of_rows : int):
464-
"""
465-
Set the cursor's position relative to the current position.
466-
Negative numbers can be used to scroll backwards.
467-
468-
This is a convenient interface to `seek` with a relative whence(``1``).
469-
470-
When `number_of_rows` is zero, there is no effect on the cursor.
518+
(seek is not an abstractmethod and is implemented using `move` and `scroll`)
471519
"""
520+
rwhence = self._seek_whence_map.get(whence, whence)
521+
if rwhence is None or rwhence.upper() not in self._seek_whence_map.values():
522+
raise TypeError(
523+
"unknown whence parameter, %r" %(whence,)
524+
)
525+
rwhence = rwhence.upper()
526+
if rwhence == 'RELATIVE':
527+
return self.scroll(offset)
528+
elif rwhence == 'ABSOLUTE':
529+
return self.move(offset, count = count)
530+
else:
531+
return self.move(-offset, count = count)
472532

473533
class PreparedStatement(
474534
InterfaceElement,
@@ -488,6 +548,13 @@ class PreparedStatement(
488548
"""
489549
ife_label = 'QUERY'
490550

551+
@apdoc
552+
@abstractproperty
553+
def statement_id(self) -> str:
554+
"""
555+
The statment's identifier.
556+
"""
557+
491558
@apdoc
492559
@abstractproperty
493560
def string(self) -> str:
@@ -1019,10 +1086,11 @@ def statement(self,
10191086
title : "The query's name, used in tracebacks when available" = None
10201087
) -> PreparedStatement:
10211088
"""
1022-
Create a `PreparedStatement` object that was already prepared on the server.
1023-
The distinction between this and a regular query is that it must be
1024-
explicitly closed if it is no longer desired, and it is instantiated using
1025-
the statement identifier as opposed to the SQL statement itself.
1089+
Create a `PreparedStatement` object that was already prepared on the
1090+
server. The distinction between this and a regular query is that it
1091+
must be explicitly closed if it is no longer desired, and it is
1092+
instantiated using the statement identifier as opposed to the SQL
1093+
statement itself.
10261094
10271095
If no ``title`` keyword is given, it will default to the statement_id.
10281096
"""
@@ -1032,13 +1100,14 @@ def cursor(self,
10321100
cursor_id : "The cursor's identification string."
10331101
) -> Cursor:
10341102
"""
1035-
Create a `Cursor` object from the given `cursor_id` that was already declared
1036-
on the server.
1103+
Create a `Cursor` object from the given `cursor_id` that was already
1104+
declared on the server.
10371105
1038-
`Cursor` objects created this way must *not* be closed when the object is garbage
1039-
collected. Rather, the user must explicitly close it for the server
1040-
resources to be released. This is in contrast to `Cursor` objects that
1041-
are created by invoking a `PreparedStatement` or a SRF `StoredProcedure`.
1106+
`Cursor` objects created this way must *not* be closed when the object
1107+
is garbage collected. Rather, the user must explicitly close it for
1108+
the server resources to be released. This is in contrast to `Cursor`
1109+
objects that are created by invoking a `PreparedStatement` or a SRF
1110+
`StoredProcedure`.
10421111
"""
10431112

10441113
@abstractmethod
@@ -1054,8 +1123,8 @@ def proc(self,
10541123
>>> p = pg_con.proc('version()')
10551124
>>> p()
10561125
'PostgreSQL 8.3.0'
1057-
1058-
>>> pg_con.query("select oid from pg_proc where proname = 'generate_series'").first()
1126+
>>> qstr = "select oid from pg_proc where proname = 'generate_series'"
1127+
>>> pg_con.query(qstr).first()
10591128
1069
10601129
>>> generate_series = pg_con.proc(1069)
10611130
>>> list(generate_series(1,5))
@@ -1067,15 +1136,16 @@ def reset(self) -> None:
10671136
"""
10681137
Reset the connection into it's original state.
10691138
1070-
Issues a ``RESET ALL`` to the database. If the database supports removing
1071-
temporary tables created in the session, then remove them. Reapply
1072-
initial configuration settings such as path. If inside a transaction
1073-
block when called, reset the transaction state using the `reset`
1074-
method on the connection's transaction manager, `xact`.
1139+
Issues a ``RESET ALL`` to the database. If the database supports
1140+
removing temporary tables created in the session, then remove them.
1141+
Reapply initial configuration settings such as path. If inside a
1142+
transaction block when called, reset the transaction state using the
1143+
`reset` method on the connection's transaction manager, `xact`.
10751144
1076-
The purpose behind this method is to provide a soft-reconnect method that
1077-
re-initializes the connection into its original state. One obvious use of this
1078-
would be in a connection pool where the connection is done being used.
1145+
The purpose behind this method is to provide a soft-reconnect method
1146+
that re-initializes the connection into its original state. One
1147+
obvious use of this would be in a connection pool where the connection
1148+
is done being used.
10791149
"""
10801150

10811151
class Connector(InterfaceElement):

0 commit comments

Comments
 (0)